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 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)`	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)`

Timestamp Functions

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.

String Functions

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

Statistical and Aggregate Functions

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.

Conditional Functions

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

Type Conversion

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

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()` instead.
`XXHASH64()`	Not supported.
`TRY()`	Not supported. Use `TRY_CAST` for safe type conversion.

Migration Example

Before (V2):

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):

# 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"