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.

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.

Review the following behavioral changes before migrating.

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 status response header to poll for status or fetch results.

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 for the error format.

The Query API V3 HTTP endpoint uses sql for the query field. Confirm whether any Query API V1 integrations used a different field name.

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.

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.

The following functions are renamed, replaced, or removed.

V1/V2	V3	Notes
`DATE_FORMAT(date, format)`	`TO_CHAR(date, format)`	Format patterns differ: V1/V2 uses `'yyyy'`; V3 uses `'YYYY'`.
`DATE_PARSE(string, format)`	`TO_TIMESTAMP(string, format)`	Function name change.
`DATE_ADD(unit, value, date)`	`DATE_ADD(unit, value, date)` or `date + INTERVAL 'value' unit`	Not all units are available in HyperSQL.
`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)`	Minor discrepancies are possible for day/month/year boundaries.
`TODAY()`	`CURRENT_DATE`	Function name change.
`FROM_UNIXTIME(epoch)`	`TO_TIMESTAMP(epoch)`	`FROM_UNIXTIME` is not supported. Use `TO_TIMESTAMP` with a Unix epoch value.
`TO_UNIXTIME(timestamp)`	`EXTRACT(EPOCH FROM timestamp)`

V1/V2	V3	Notes
`FROM_ISO8601_TIMESTAMP(string)`	`CAST(string AS timestamptz)`
`TO_ISO8601(timestamp)`	`TO_CHAR(timestamp, format)`	See `TO_CHAR` for DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE format patterns.
`FORMAT_DATETIME(timestamp, format)`	`TO_CHAR(timestamp, format)`	`FORMAT_DATETIME` is not supported. Use `TO_CHAR` with an equivalent format pattern.

V1/V2	V3	Notes
`ARRAY_JOIN(array, delimiter)`	`ARRAY_TO_STRING(array, delimiter)`	Function name change.

V1/V2	V3	Notes
`APPROX_DISTINCT(column)`	`APPROX_COUNT_DISTINCT(column)`	Function name change.
`APPROX_PERCENTILE(column, p)`	`PERCENTILE_CONT` or `PERCENTILE_DISC`	Syntax change; uses `WITHIN GROUP (ORDER BY ...)`.
`STDDEV(column)`	`STDDEV` / `STDDEV_SAMP(column)` or `STDDEV_POP(column)`	`STDDEV` and `STDDEV_SAMP` are aliases (sample standard deviation). Use `STDDEV_POP` for population standard deviation.
`COUNT_IF(condition)`	`COUNT(*) FILTER (WHERE condition)`	Syntax change.

V1/V2	V3
`IF(condition, true_result, false_result)`	`CASE WHEN condition THEN true_result ELSE false_result END`

V1/V2	V3
`TRY(CAST(expression AS type))`	`TRY_CAST(expression AS type)`
`CARDINALITY(array)`	`ARRAY_LENGTH(array)`

Function	Notes
`ROW()`	Not supported.
`COUNT(DISTINCT a, b)`	Multi-column `COUNT(DISTINCT ...)` is not supported.
`UUID()`	Not supported.
`RAND()`	Not supported. Use `RANDOM()` instead.
`XXHASH64()`	Not supported.
`TRY()`	Not supported. Use `TRY_CAST` for safe type conversion.

Before (V2):

After (V3):