Query result set differences

In the Data Catalog, the metadata object’s properties have data types associated with them. For example, integer, decimal, Boolean or strings defined with a date or datetime format.

The result values can differ from the metadata-defined type. Query results are based upon the method that is used to run a query. For the Compass API, the values are also defined by the output type of CSV or new-line-delimited JSON.

The Compass query editor shows results in a grid. If you export the results to CSV, then the format values are set.

The Compass API returns results in CSV or JSON, depending on the result set format that is specified when the query is submitted. The Compass API result values that are returned for JSON and CSV output show values as strings using double quotes. The CSV query results return numbers in the general format of decimal (38,15). The CSV query results return dates and timestamps in UTC using the ISO8601 RFC3339 format. For CSV, the results do not convert the results to the metadata-defined data type, scale, and precision or date format. You can change the result format and values in future versions of Compass queries. The JSON result format returns numeric values according to their metadata-defined type, when applicable. Integers and bigints are returned, and decimal values are based on the metadata-defined precision and scale.

Boolean values are returned as true or false.

Date and datetime values are returned as timestamps in UTC using the ISO8601 RFC3339 format.

The JDBC result format can also be converted based on the SQL query tool’s format or localization settings. The JDBC result returns timestamps in UTC using the ISO8601 RFC3339 format. The query tool can convert the value to the local time zone. The query tool can convert the format. Check the query tool settings for data type controls and localization settings.