# Migrate to Query API V3 Use this guide to update integrations built on Query API V1 or V2 to the Data 360 Query API V3. Query API V3 introduces asynchronous and adaptive execution modes and standardizes error reporting. Review the considerations and SQL function mapping tables before migrating. ## Endpoint Mapping | V1 / V2 Endpoint | V3 Endpoint | Notes | | --------------------------------- | ------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------- | | `POST /api/v1/query` | `POST /api/v3/query` | V1 was synchronous only; V3 supports ASYNC and ADAPTIVE modes. | | `POST /api/v2/query` | `POST /api/v3/query` | Request body field name is `sql` in both versions. | | `GET /api/v2/query/{nextBatchId}` | `GET /api/v3/query/{queryId}/chunks/{chunkId}` (preferred) or `GET /api/v3/query/{queryId}/rows` | Replace `nextBatchId` with `queryId`. For chunks, use `chunkId` from `chunkCount`. For rows, add `?offset=0`. | | — | `GET /api/v3/query/{queryId}` | New in V3: poll for query completion status. | | — | `GET /api/v3/query/{queryId}/metadata` | New in V3: retrieve output schema without fetching data rows. | | — | `DELETE /api/v3/query/{queryId}` | New in V3: cancel a running query and free resources. | | - | `GET /api/v3/query/{queryId}/chunks/{chunkId}` | New in V3: retrieve a chunk of query data. | ## Considerations Review the following behavioral changes before migrating. ### No Synchronous Mode Query API V1 returned results inline in the POST response. Query API V3 doesn't support synchronous execution. Use ADAPTIVE mode to receive results immediately for fast queries, or ASYNC mode for long-running queries. In both modes, retrieve the `queryId` from the `x-hyperdb-status` response header to poll for status or fetch results. ### Error Response Format Query API V3 standardizes error responses using SQLSTATE codes and a rich error model. Update any code that parsed error strings. See [Query API V3 Error Handling](/docs/data/data-cloud-query-guide/references/data-cloud-query-api-reference/query-api-v3-post.md#error-handling) for the error format. ### Field Naming The Query API V3 HTTP endpoint uses `sql` for the query field. Confirm whether any Query API V1 integrations used a different field name. ### Column Name Casing Query API V3 changes how output column names are cased. - **Query API V1 and V2:** Output column name casing matches exactly what was written in the `SELECT` clause. - **V3 with alias:** Output column name uses the exact casing of the alias. Use quoted aliases to guarantee specific casing. - **V3 without alias:** Output column name reverts to the casing defined in the underlying table schema, regardless of how the column was written in the `SELECT` clause. This is a breaking change for downstream code that relies on exact column name matching for non-aliased columns. To guarantee output casing, use explicit quoted aliases. ### Auto-Generated Column Names In Query API V1 and V2, expressions without aliases (for example, `SELECT count(*), sum(x) FROM ...`) produced output column names `_col0`, `_col1`. In Query API V3, the same expressions produce `1`, `2`. ## SQL Function Changes The following functions are renamed, replaced, or removed. ### Date and Time Functions | V1/V2 | V3 | Notes | | ------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `DATE_FORMAT(date, format)` | [`TO_CHAR(date, format)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-char.md) | Format patterns differ: V1/V2 uses `'yyyy'`; V3 uses `'YYYY'`. | | `DATE_PARSE(string, format)` | [`TO_TIMESTAMP(text, text)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-timestamp-string.md) | Parses formatted text into a timestamp. | | `DATE_ADD(unit, value, date)` | [`DATE_ADD(unit, value, date)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/date-add.md) or `date + INTERVAL 'value' unit` | V3 example: `DATE_ADD('day', 3, TIMESTAMP '2025-01-01 00:00:00')`. See the linked `DATE_ADD` reference for supported units; HyperSQL compatibility depends on target product. | | `DATE_SUB(unit, value, date)` | `DATE_ADD(unit, value, date)` or `date - INTERVAL 'value' unit` | `DATE_SUB` is not supported. Use a negative value with `DATE_ADD` or interval subtraction. | | `DATE_DIFF(unit, date1, date2)` | [`DATEDIFF(unit, date1, date2)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/datediff.md) | Minor discrepancies are possible for day/month/year boundaries. | | `TODAY()` | [`CURRENT_DATE`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/current-date.md) | Function name change. | | `FROM_UNIXTIME(epoch)` | [`TO_TIMESTAMP(double precision)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-timestamp.md) | Converts Unix epoch seconds to a timestamp. `FROM_UNIXTIME` isn't supported. | | `TO_UNIXTIME(timestamp)` | [`EXTRACT(EPOCH FROM timestamp)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/extract.md) | | ### Timestamp Functions | V1/V2 | V3 | Notes | | ------------------------------------ | ----------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `FROM_ISO8601_TIMESTAMP(string)` | [`CAST(string AS timestamptz)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/type-conversion.md) | | | `TO_ISO8601(timestamp)` | [`TO_CHAR(timestamp, format)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-char.md) | See [`TO_CHAR`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-char.md) for DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE format patterns. | | `FORMAT_DATETIME(timestamp, format)` | [`TO_CHAR(timestamp, format)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/to-char.md) | `FORMAT_DATETIME` is not supported. Use `TO_CHAR` with an equivalent format pattern. | ### String Functions | V1/V2 | V3 | Notes | | ------------------------------ | ----------------------------------------------------------------------------------------------------------------------- | --------------------- | | `ARRAY_JOIN(array, delimiter)` | [`ARRAY_TO_STRING(array, delimiter)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-to-string.md) | Function name change. | ### Statistical and Aggregate Functions | V1/V2 | V3 | Notes | | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ----------------------------------------------------------------------------------------------------------------------- | | `APPROX_DISTINCT(column)` | [`APPROX_COUNT_DISTINCT(column)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/approx-count-distinct.md) | Function name change. | | `APPROX_PERCENTILE(column, p)` | [`PERCENTILE_CONT`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-cont.md) or [`PERCENTILE_DISC`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/percentile-disc.md) | Syntax change; uses `WITHIN GROUP (ORDER BY ...)`. | | `STDDEV(column)` | [`STDDEV` / `STDDEV_SAMP(column)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev.md) or [`STDDEV_POP(column)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/stddev-pop.md) | `STDDEV` and `STDDEV_SAMP` are aliases (sample standard deviation). Use `STDDEV_POP` for population standard deviation. | | `COUNT_IF(condition)` | [`COUNT(*) FILTER (WHERE condition)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/filter-clause.md) | Syntax change. | ### Conditional Functions | V1/V2 | V3 | | ------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------- | | `IF(condition, true_result, false_result)` | [`CASE WHEN condition THEN true_result ELSE false_result END`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/case.md) | ### Type Conversion | V1/V2 | V3 | | ------------------------------- | ------------------------------------------------------------------------------------------------------------------ | | `TRY(CAST(expression AS type))` | [`TRY_CAST(expression AS type)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/type-conversion.md) | | `CARDINALITY(array)` | [`ARRAY_LENGTH(array)`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/array-length.md) | ### Functions Not Supported in V3 | Function | Notes | | ---------------------- | ------------------------------------------------------------------------------------------------------------------------------------------- | | `ROW()` | Not supported. | | `COUNT(DISTINCT a, b)` | Multi-column `COUNT(DISTINCT ...)` is not supported. | | `UUID()` | Not supported. | | `RAND()` | Not supported. Use [`RANDOM()`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/random.md) instead. | | `XXHASH64()` | Not supported. | | `TRY()` | Not supported. Use [`TRY_CAST`](/docs/data/data-cloud-query-guide/references/dc-sql-reference/type-conversion.md) for safe type conversion. | ## Migration Example **Before (V2):** ```bash curl -X POST https://{dne_cdpInstanceUrl}/api/v2/query \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT Id FROM ssot__Individual__dlm LIMIT 5"}' ``` **After (V3):** ```bash # Submit query (ADAPTIVE mode by default) curl -X POST https://{dne_cdpInstanceUrl}/api/v3/query \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT Id FROM ssot__Individual__dlm LIMIT 5"}' # The queryId is returned in the `x-hyperdb-status` response header. # Poll for completion status. curl "https://{dne_cdpInstanceUrl}/api/v3/query/{queryId}" \ -H "Authorization: Bearer $TOKEN" # Retrieve paginated results curl "https://{dne_cdpInstanceUrl}/api/v3/query/{queryId}/rows?offset=0" \ -H "Authorization: Bearer $TOKEN" # Retrieve output schema only curl "https://{dne_cdpInstanceUrl}/api/v3/query/{queryId}/metadata" \ -H "Authorization: Bearer $TOKEN" # Cancel a query curl -X DELETE "https://{dne_cdpInstanceUrl}/api/v3/query/{queryId}" \ -H "Authorization: Bearer $TOKEN" ``` ## Related Resources - [Submit SQL Query](/docs/data/data-cloud-query-guide/references/data-cloud-query-api-reference/query-api-v3-post.md) - [Get SQL Query Status](/docs/data/data-cloud-query-guide/references/data-cloud-query-api-reference/query-api-v3-get-query.md) - [Get SQL Query Rows](/docs/data/data-cloud-query-guide/references/data-cloud-query-api-reference/query-api-v3-get-rows.md) - [Query Services Status Codes](/docs/data/data-cloud-query-guide/references/data-cloud-query-api-reference/c360a-api-queryservices-statuscodes.md)