Run SQL statements on Databricks SQL warehouses with the Databricks SQL Statement Execution REST API

Important

This feature is in Public Preview.

Important

To access Databricks REST APIs, you must authenticate.

This tutorial shows how to use the Databricks SQL Statement Execution API 2.0 to run SQL statements from Databricks SQL warehouses.

To view and to download an OpenAPI Specification-compatible version of the Databricks SQL Statement Execution API 2.0 reference, see Statement Execution API 2.0.

Before you start this tutorial, be sure that you have:

The workspace instance name, for example adb-1234567890123456.7.azuredatabricks.net, for your Azure Databricks workspace. This tutorial assumes that you have an environment variable on your local development machine named DATABRICKS_HOST, which is set to this value. To learn how to set environment variables, see your operating system鈥檚 documentation.

Warning

Databricks strongly discourages hard-coding information into your scripts, as this sensitive information can be exposed in plain text through version control systems. Databricks recommends that you use approaches such as environment variables that you set on your development machine instead. Removing such hard-coded information from your scripts helps to make those scripts more portable as well.

An Azure Databricks personal access token or Azure Active Directory (Azure AD) token for your Azure Databricks workspace user. This tutorial assumes that you have an environment variable on your local development machine named DATABRICKS_TOKEN, which is set to the value of your token.

Note

As a security best practice, when authenticating with automated tools, systems, scripts, and apps, Databricks recommends you use access tokens belonging to service principals instead of workspace users. To create access tokens for service principals, see Manage access tokens for a service principal.

At least one table that you can execute SQL statements against. This tutorial is based on the lineitem table in the tpch schema (also known as a database) within the samples catalog. If you do not have access to this catalog, schema, or table from your workspace, substitute them throughout this tutorial with your own.

curl, a command-line tool for sending and receiving REST API requests and responses. See also Install curl. Alternatively, you can adapt this tutorial鈥檚 examples for use with similar tools such as Postman or HTTPie.

For each of the curl examples used in this article:

jq, a command-line processor for querying JSON response payloads, which the Databricks SQL Statement Execution API returns to you after each call that you make to the Databricks SQL Statement Execution API. See also Download jq.

A Databricks SQL warehouse. This tutorial assumes that you have an environment variable on your local development machine named DATABRICKS_SQL_WAREHOUSE_ID, which is the string of letters and numbers following /sql/1.0/warehouses/ in the HTTP path field for your warehouse. To learn how to get your warehouse鈥檚 HTTP path value, see Get connection details for a SQL warehouse.

The Databricks SQL warehouse must be set to use the Preview channel in Advanced options. Note that switching a warehouse from using the Current channel to the Preview channel will cause the warehouse to automatically restart. This upgrade could take several minutes.

Run the following command, which does the following:

In the preceding request:

If the statement鈥檚 result is available before the wait timeout ends, the response is as follows:

If the wait timeout ends before the statement鈥檚 result is available, the response looks like this instead:

If the statement鈥檚 result data is too large (for example in this case, by running SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 3000000), the result data is chunked and looks like this instead. Note that "...": "..." indicates omitted results here for brevity:

You can use a SQL statement鈥檚 ID to get that statement鈥檚 current execution status and, if the execution succeeded, that statement鈥檚 result. If you forget the statement鈥檚 ID, you can get it from the query history section of the Databricks SQL console, or by calling the Query History API 2.0. For example, you could keep polling this command, checking each time to see if the execution has succeeded.

To get a SQL statement鈥檚 current execution status and, if the execution succeeded, that statement鈥檚 result and an API URL fragment for getting any next chunk of JSON data, run the following command. This command assumes that you have an environment variable on your local development machine named SQL_STATEMENT_ID, which is set to the value of the ID of the SQL statement from the previous step or otherwise provided. Of course, you can substitute ${SQL_STATEMENT_ID} in the following command with the hard-coded ID of the SQL statement.

If the NEXT_CHUNK_INTERNAL_LINK is set to a non-null value, you can use it to get the next chunk of data, and so on, for example with the following command:

You can keep running the preceding command, over and over again, to get the next chunk and so on. Note that as soon as the last chunk is fetched, the SQL statement is closed. After this closure, you cannot use that statement鈥檚 ID to get its current status or to fetch any more chunks.

This section demonstrates an optional configuration that uses the EXTERNAL_LINKS disposition to retrieve large data sets. The default location (disposition) for the SQL statement result data is within the response payload, but these results are limited to 16 MiB. By setting the disposition to EXTERNAL_LINKS, the response contains URLs you can use to fetch the chunks of the results data with standard HTTP. The URLs point to your workspace鈥檚 internal DBFS, where the result chunks are temporarily stored.

Warning

Databricks strongly recommends that you protect the URLs and tokens that are returned by the EXTERNAL_LINKS disposition.

When you use the EXTERNAL_LINKS disposition, a shared access signature (SAS) URL is generated, which can be used to download the results directly from Azure storage. As a short-lived SAS token is embedded within this SAS URL, you should protect both the SAS URL and the SAS token.

Because SAS URLs are already generated with embedded temporary SAS tokens, you must not set an Authorization header in the download requests.

The EXTERNAL_LINKS disposition can be disabled upon request. To make this request, create a support case. See _.

See also Security best practices.

Note

The response payload output format and behavior, once they are set for a particular SQL statement ID, cannot be changed.

In this mode, the API enables you to store result data only in Apache Arrow format that must be queried separately with HTTP. Also, when using this mode, it is not possible to inline the result data within the response payload.

The following command demonstrates using EXTERNAL_LINKS. Use this pattern instead of the similar query demonstrated in step 1:

The response is as follows:

If the request times out, the response looks like this instead:

To get that statement鈥檚 current execution status and, if the execution succeeded, that statement鈥檚 result, run the following command:

If the response is large enough (for example in this case, by running SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem with no row limit), the response will have multiple chunks, as in the following example below. Note that "...": "..." indicates omitted results here for brevity:

To download the stored content鈥檚 results, you can run the following curl command, using the URL in the external_link object and specifying where you want to download the file. Do not include your Azure Databricks token in this command:

To download a specific chunk of a streamed content鈥檚 results, first use the chunk_index and row_offset values from the response payload. For example, to get the chunk with a chunk_index of 41 and a row_offset of 28755596 from the previous response, run the following command:

Note

Running the preceding command returns a new SAS URL.

Arbitrary row_offset values cannot be specified. You can only use row_offset values that are contained within the manifest object.

To download the stored chunk, use the URL in the external_link object.

For more information about the Apache Arrow format, see:

If you need to cancel a SQL statement that has not yet succeeded, run the following command:

The Databricks SQL Statement Execution API increases the security of data transfers by using end-to-end transport layer security (TLS) encryption and short-lived credentials such as SAS tokens.

There are several layers in this security model. At the transport layer, it is only possible to communicate with the Databricks SQL Statement Execution API by using TLS 1.2 or above. Also, callers of the Databricks SQL Statement Execution API must be authenticated with a valid Azure Databricks personal access token or Azure Active Directory (Azure AD) token that maps to a user who has the entitlement to use Databricks SQL. This user must have Can Use access for the specific SQL warehouse that is being used, and access can be restricted with IP access lists. This applies to all requests to the Databricks SQL Statement Execution API. Furthermore, for executing statements, the authenticated user must have permission to the data objects (such as tables, views, and functions) that are used in each statement. This is enforced by existing access control mechanisms in Unity Catalog or by using table ACLs; see the Data governance guide. This also means that only the user who executes a statement can make fetch requests for the statement鈥檚 results.

Databricks recommends the following security best practices whenever you use the Databricks SQL Statement Execution API along with the EXTERNAL_LINKS disposition to retrieve large data sets:

The EXTERNAL_LINKS disposition can be disabled upon request by creating a support case. To make this request, create a support case. See _.

All calls to the Databricks SQL Statement Execution API must include an Authorization header that contains Azure Databricks access credentials. Do not include this Authorization header whenever you download data from Azure storage. This header is not required and might unintentionally expose your Azure Databricks access credentials.

Whenever you use the EXTERNAL_LINKS disposition, a short-lived SAS URL is generated, which the caller can use to download the results directly from Azure storage by using TLS. As a short-lived SAS token is embedded within this SAS URL, you should protect both the SAS URL and SAS token.