Query

Create and manage queries.

Part of the API reference collection

This page is part of the Crunchy Bridge API reference, and primarily meant to act as an exhaustive guide for technical integrations which are already in progress. To understand the basics of using the API, see API concepts and getting started.

The Query API resource

A query and its result.

Content type: application/json

NameNullableTypeDescription
idstring in EID format in UUID format

Unique ID of the query.

allow_writeboolean

Whether the query is allowed to write to the database.

asyncboolean

Indicates whether this is a query running asynchronously (created by specifying the async parameter).

cluster_idstring in EID format in UUID format

The ID of cluster that the query is being executed on.

database_name✔string

Name of the database to query. Only populated if not default.

finished_at✔string of date/time formatted as RFC 3339 in EID format in UUID format

Time at which the query finished, either successfully or in error.

is_blockedboolean

Set to true when a pending query will not be scheduled for run because either its account or cluster is already at its simultaneously running query limit, or another query or its associated saved query is already running.

last_error_message✔string

In case of a failure, the message of the last error that occurred. If the query's status is set to failure then the failure is permanent. If status is pending or running then it may be set to the message of the last error that occurred, but which is considered intermittent so the query's been scheduled to run again (pending) or is already running again (running). Always nil for synchronous queries -- errors on synchronous queries are returned as non-`200`` API errors instead.

last_run_duration_seconds✔number

The total number of seconds that it took to perform the query's last run. Includes fractional seconds as well. This will be populated for either a query that succeeded or one that failed, so a caller may also want to check status.

next_run_at✔string of date/time formatted as RFC 3339 in EID format in UUID format

The next time at which an asynchronous query is scheduled to run. Queries are eligible to be run immediately when they're first created, but may be rescheduled in the future in case of failure. Next run time indicates only when a query is next eligible to be run, and queries aren't guaranteed to start running immediately at this time -- their actual start time may depend on worker capacity and scheduler run timing. null for synchronous queries and asynchronous queries that are finished.

num_failures✔integer

The number of failures in the case of an asynchronous query. Always null for a synchronous query.

resultarray of array

The result of the query as a two-dimensional array of the returned rows and each field value within each.

result_fieldsarray of array

Metadata on each field returned in the results.

sqlstring

SQL being executed as part of the query.

statusenum string

The status of the query. Clients should wait until this field reads failed or succeeded, interpret the result, and stop polling.

Enum canceled, failed, pending, running, or succeeded.

Example

{
    "allow_write": false,
    "async": false,
    "cluster_id": "rvf73a77ozfsvcttryebfrnlem",
    "database_name": null,
    "finished_at": "2021-07-11T01:02:03Z",
    "id": "agbk2tous2pmi2ehnjbvkxo7gi",
    "is_blocked": false,
    "last_error_message": null,
    "last_run_duration_seconds": 0.1,
    "next_run_at": null,
    "num_failures": null,
    "result": [
        [
            1
        ],
        [
            2
        ],
        [
            3
        ]
    ],
    "result_fields": [
        {
            "name": "unnest"
        }
    ],
    "sql": "SELECT unnest(array[1,2,3]);",
    "status": "succeeded"
}

Create query

Create (start executing) a query on a cluster.

The functionality of this endpoint is currently limited to performing in-band queries which are constrained by a 25 second statement timeout, and an API request's normal timeout of 30 second beyond that. Future functionality will include asynchronous queries and caching.

Queries default to readonly to avoid accidental mutation. Set allow_write to true to make queries read/write.

POST /queries

Request body schema

Content type: application/json

NameRequiredTypeDescription
sqlstring

SQL being executed as part of the query.

allow_writeboolean

Set to true to allow a query to be read/write. Queries default to readonly to avoid accidental mutation.

asyncboolean

Specify true to run the query asynchronously. No results are available immediately, but its completion or error status can be polled via the get API until they're available.

cluster_idstring in EID format in UUID format

Unique ID of the cluster to query.

database_namestring

Name of the database to query.

Example request body

{
    "allow_write": false,
    "async": false,
    "cluster_id": "rvf73a77ozfsvcttryebfrnlem",
    "database_name": null,
    "sql": "SELECT 1;"
}

cURL example

curl -X POST https://api.crunchybridge.com/queries
    -H "Authorization: Bearer $CRUNCHY_API_KEY"
    -H "Content-Type: application/json"
    -d '{"allow_write":false,"async":false,"cluster_id":"rvf73a77ozfsvcttryebfrnlem","database_name":null,"sql":"SELECT 1;"}'

Response

Status: 201

Responds with the standard Query API resource.

Get query

Retrieve a query. Only queries that were created with async set to true can be retrieved after they're initially created. This endpoint should be polled to get the status of asynchronous queries until their finished_at field has been set and their results are available.

Query results are available for a maximum of 30 days.

GET /queries/{query_id}

Request

Path parameters

  • query_id: Unique ID of the query to retrieve.

cURL example

curl -X GET https://api.crunchybridge.com/queries/{query_id}
    -H "Authorization: Bearer $CRUNCHY_API_KEY"

Response

Status: 200

Responds with the standard Query API resource.