Execute SQL statements in Databricks SQL stores with the REST API to execute SQL statements Databricks - Azure Databricks - Databricks SQL (2023)

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

To view and download the OpenAPI compliant version of the Databricks SQL Statement Execution API 2.0 Reference, seeCommand Execution API 2.0.

before you start

Before starting this tutorial, make sure you have the following:

• TheThe name of the workspace instance, For exampleadb-1234567890123456.7.azuredatabricks.net, for your Azure Databricks workspace. This tutorial assumes you have an environment variable named on your local development machineDATABRICKS_HOST, which is set to this value. See your operating system documentation for information on setting environment variables.

  warning

  Databricks strongly discourages hardcoding information into your scripts as this sensitive information can be clearly exposed through version control systems. Databricks recommends using approaches such as:environmental variableswhich you place on your development machine instead. Removing such hard-coded information from your scripts also helps make those scripts more portable.

  (Video) Working with SQL using the DataFrame SQL API in Azure Databricks

• Ein Azure DatabricksPersonal access tokenorAzure Active Directory (Azure AD)-Tokenfor an Azure Databricks workspace user. This tutorial assumes you have an environment variable named on your local development machineDATABRICKS_TOKEN, which is set to the value of your token.

  See Also

  Secure your AKS deployments - microservice user authentication with Azure AD and OAuth 2 proxyNAT behavior of Azure FirewallAuthenticate to Azure AD for access

  Note

  As a security best practice, Databricks recommends using personal access tokens when authenticating using automated tools, systems, scripts, and applicationsservice managerinstead of workspace users. For information on creating tokens for service principals, seeManage personal access tokens for the service principal.

• Najmanje jedna tablica na kojoj možete pokrenuti SQL naredbe. Ovaj vodič temelji se naitemtable intpchA schema (also known as a database) withinpracticeCatalog. If you don't have access to this catalog, schematic, or table from your workspace, replace them with your own in this tutorial.

• Cuddle, a command-line tool for sending and receiving REST API requests and responses. See alsoInstall the curl. Alternatively, you can adapt the examples in this tutorial for use with similar tools, e.gpostmanorHTTPie.

  For each ofCuddleExamples used in this article:

  • Instead--header "Authorization: Holder ${DATABRICKS_TOKEN}", you can use a.netrcFile. If you use a.netrcfile, replace--header "Authorization: Holder ${DATABRICKS_TOKEN}"s--netrc.
  • If you are using a Windows shell instead of a Unix, Linux, or macOS shell, replace it\s^, and replace${...}s%...%.
  • When using the Windows shell instead of the Unix, Linux, or macOS shell in JSON document declarations, replace open and close's", and replace the inside"s\".

• jq, a command-line processor for querying the JSON responses that the Databricks SQL Statement Execution API returns to you after each call to the Databricks SQL Statement Execution API. See alsoDownload jq.

• Databricks SQL repository. This tutorial assumes you have an environment variable named on your local development machineDATABRICKS_SQL_WAREHOUSE_ID, which is the next sequence of letters and numbers/sql/1.0/skladišta/imHTTP putfield for your camp. Find out how to get your suppliesHTTP putvalue, seeGet connection details for SQL storage.

• A Databricks SQL repository must be set up to use itreview channelUExtended options. Note that switching the repository from using the Current channel to using the Preview channel will cause the repository to automatically restart. This upgrade may take several minutes.

Step 1: Run the SQL command and save the result data as JSON

Run the following command which does the following:

1. It uses the specified SQL store along with the specified token to query the three columns from the first two rowsitemtable intcphscheme withinpracticeCatalog.
2. Saves the response payload in JSON format to a file namedsql-execution-response.jsonin the current working directory.
3. Prints the contentsql-execution-response.jsonFile.
4. Sets the local environment variable namedSQL_STATEMENT_IDwhich contains the ID of the corresponding SQL command. You can use this SQL statement ID to retrieve information about this statement later if needed, as shown in step 2. You can also view this SQL statement and get its statement IDquery historysection of the Databricks SQL console or by callingQuery History API 2.0.
5. It sets an additional local environment variable namedNEXT_CHUNK_EXTERNAL_LINKwhich contains an API URL fragment to get the next block of JSON data. If the response data is too large, the Databricks SQL Statement Execution API returns the response in chunks. You can use this API URL fragment to get the next block of data, which is shown in step 2. If there is no next block, this environment variable is set toNull.
6. Prints the valuesSQL_STATEMENT_IDINEXT_CHUNK_INTERNAL_LINKenvironmental variables.

curl --request POST \https://${DATABRICKS_HOST}/api/2.0/sql/statements/ \--header "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--header "Content-Type: application/json" \ --data '{ "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'", "catalog": "uzorci", "schema": "tpch", "statement": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 2" }' \--izlaz 'sql-execution-response.json' \&& jq . 'sql-execution-response.json' \&& izvoz SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \&& izvoz NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution- Response.json') \&& echo SQL_STATEMENT_ID=$SQL_STATEMENT_ID \&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

In the previous request:

• By default, all returned data is in JSON array format, and the default location for all SQL statement data results is within the response payload. To make this behavior explicit, add„format“: „JSON_ARRAY“, „disposition“: „INLINE“to the payload of the request. If you attempt to return data results larger than 16 MiB in the response, an error status is returned and the SQL statement is terminated. For data results larger than 16 MiB, you can use external links instead of trying to return them in the response, as shown in step 3.
• Cuddlesaves the response content to a local file. The Databricks SQL Statement Execution API does not directly support local data storage.
• By default, after 10 seconds, if the repository has not yet completed executing the SQL statement, the Databricks SQL Statement Execution API returns only the ID of the SQL statement and its current status, not the result of the statement. To change this behavior, add"wait_timeout": "s“on the payload of the request, whereit can be in between5I50For example, seconds are included"wait_timeout": "50s". To immediately return the ID of the SQL statement and its current status, setwait_timeoutDo0er.
• By default, the SQL statement continues to run when the timeout expires. To instead terminate the SQL statement when it expires, add"on_wait_timeout": "OTKAZI"upon request payload.

If the result of the statement is available before the wait time expires, the response is as follows:

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "SUCCEEDED" }, "manifest": { "format": "JSON_ARRAY", "schema": { " columns_count": 3, "columns": [ { "name": "l_orderkey", "type_name": "LONG", "position": 0 }, { "name": "l_extendedprice", "type_name": "DECIMAL" , „position“: 1, „type_precision“: 18, „type_scale“: 2 }, { „name“: „l_shipdate“, „type_name“: „DATUM“, „position“: 2 } ] } }, „rezultat“ : { "chunk_index": 0, "row_offset": 0, "row_count": 2, "data_array": [ [ "15997987", "66516.00", "1992-02-12" ], [ "15997988", "53460.96 ", "1994-05-31" ] ] }}

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

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "NA ČEKANJU" }}

If the data about the result of the statement is too large (e.g. in this case by executingODABERITE l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 3000000), the result data is split into parts and looks like this instead. Please note that„…“: „…“indicates results omitted here for brevity:

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "SUCCEEDED" }, "manifest": { "format": "JSON_ARRAY", "schema": { " columns_count": 3, "columns": [ { "...": "..." } ] } }, "result": { "chunk_index": 0, "row_offset": 0, "row_count": 432500, „Next_chunk_index“: 1, „Next_chunk_internal_link“: „/api/2.0/sql/Statements/01ed92c5-3583-1F38-b21b-c6773e7c56b3/R98888888888887, 159, 159, 159, 159, 159, 150088887,„ 1500, 159887, „1500.43,“ 1598887. , „66516,00“, „1992-02-12“ ], [ „…“ ] ] }}

Step 2: Retrieve the current execution status and result data of the instruction as JSON

You can use the SQL command ID to get the current execution status of that command and, if the execution is successful, the result of that command. If you have forgotten your bank statement ID, you can get it fromquery historysection of the Databricks SQL console or by callingQuery History API 2.0. For example, you can keep querying this command and check whether the execution succeeds each time.

Run the following command to get the current execution status of the SQL command and, if successful, the result of that command and the API URL fragment to get the next JSON block of data. This command assumes you have an environment variable named on your local development machineSQL_STATEMENT_ID, which is set to the value of the SQL statement ID from the previous step or some other way. Of course you can substitute${SQL_STATEMENT_ID}in the following command with a hard-coded SQL command ID.

curl --request GET \https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID} \--header "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--output 'sql-execution-response .json' \&& jq . 'sql-execution-response.json' \&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .result.next_chunk_internal_link 'sql-execution-response.json') \&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

Such asNEXT_CHUNK_INTERNAL_LINKis set to no value.NullIf you provide a value, you can use it to get the next block of data, etc., for example with the following command:

curl --request GET \https://${DATABRICKS_HOST}${NEXT_CHUNK_INTERNAL_LINK} \--zaglavlje "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--izlaz 'sql-execution-response.json' \&& jq . 'sql-execution-response.json' \&& izvoz NEXT_CHUNK_INTERNAL_LINK=$(jq -r .next_chunk_internal_link 'sql-execution-response.json') \&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

You can always run the previous statement to fetch the next block, etc. Note that the SQL statement will close after the last block is fetched. After this completion, you can no longer use the ID of this instruction to obtain the current status or to obtain additional blocks.

Step 3: Get Rich Results Using External Links

This section demonstrates an optional configuration that uses thisEXTERNAL LINKSReadiness to retrieve large data sets. The default location (disposition) for SQL statement result data is within the response payload, but those results are limited to 16 MiB. By placingarrangementDoEXTERNAL LINKS, the response contains URLs that you can use to get pieces of result data using standard HTTP. The URLs point to your workspace's internal DBFS, where the result blocks are temporarily stored.

The following command demonstrates the usageEXTERNAL LINKS. Use this sample instead of the similar query shown in step 1:

curl --request POST \https://${DATABRICKS_HOST}/api/2.0/sql/statements/ \--header "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--header "Content-Type: application/json" \ --data '{ "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'", "catalog": "uzorci", "schema": "tpch", "format": "ARROW_STREAM", "disposition": "EXTERNAL_LINKS", "Anweisung": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 100000"}' \--output 'sql-execution-response.json' \&& jq . 'sql-execution-response.json' \&& izvoz SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \&& echo SQL_STATEMENT_ID=$SQL_STATEMENT_ID

The answer is as follows:

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "SUCCEEDED" }, "manifest": { "format": "ARROW_STREAM", "schema": { " columns_count": 3, "columns": [ { "name": "l_orderkey", "type_name": "LONG", "position": 0 }, { "name": "l_extendedprice", "type_name": "DECIMAL" , „position“: 1, „type_precision“: 18, „type_scale“: 2 }, { „name“: „l_shipdate“, „type_name“: „DATE“, „position“: 2 } ] }, „total_chunk_count“: 1, „chunks“: [ { „chunk_index“: 0, „row_offset“: 0, „row_count“: 100000 } ], „total_row_count“: 100000, „total_byte_count“: 2848312 }, „result“: { „external_links“: [ { „chunk_index“: 0, „row_offset“: 0, „row_count“: 100000, „external_link“: „", "request": "" } ] }}

If the request times out, the response will look like this:

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "NA ČEKANJU" }}

Run the following command to get the current execution status of this command and, if the execution was successful, the result of this command:

curl --request GET \https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID} \--header "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--output 'sql-execution-response .json' \&& jq . 'sql-execution-response.json'

If the response is large enough (in this case, for example, by running).SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitemno line limit), the response consists of several blocks, as in the following example below. Please note that„…“: „…“indicates results omitted here for brevity:

{ "statement_id": "01ed92c5-3583-1f38-b21b-c6773e7c56b3", "status": { "state": "SUCCEEDED" }, "manifest": { "format": "ARROW_STREAM", "schema": { " columns_count": 3, "columns": [ { "...": "..." } ] }, "total_chunk_count": 43, "chunks": [ { "chunk_index": 0, "row_offset": 0, "row_count": 737500 }, { "chunk_index": 1, "row_offset": 737500, "row_count": 737500 }, { "...": "..." }, { "chunk_index": 41, "row_offset ": 28755596, "row_count": 737500 }, { "chunk_index": 42, "row_offset": 29493096, "row_count": 506699 } ], "total_row_count": 29999795, "total_byte_count": 854409376 }, "rezultat": { „external_links“: [ { „chunk_index“: 0, „row_offset“: 0, „row_count“: 737500, „next_chunk_index“: 1, „next_chunk_internal_link“: „/api/2.0/sql/statements/01ed92c5-3583-1f38- b21b-c6773e7c56b3/result/chunks/1?row_offset=737500", "vanjska_veza": "", "request": "" } ] }}

To download the results of the saved content, you can run the followingCuddlecommand using url inexternal linkobject and specify where you want to download the file. Do not paste your Azure Databricks token into this command:

curl"" \--Exit "“

To download a specific portion of streaming results, first usepart_indexIline shiftValues ​​from the carrying capacity of the answer. For example to get a piece with apart_indexvon41i aline shiftvon28755596Based on the previous answer, run the following command:

curl --request GET \https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID}/result/chunks/41?row_offset=28755596 \--header "Autorizacija: nositelj ${DATABRICKS_TOKEN}" \--izlaz 'sql-execution-response.json' \&& jq. 'sql-execution-response.json'

To download a saved block, use the URL inexternal linkObject.

For more information about the Apache Arrow format, see:

• IPC-Streaming-Format
• Stream format for writing and reading
• Using streams

Step 4: Cancel the execution of the SQL statement

If you need to terminate an SQL statement that has not yet failed, run the following command:

curl --request POST \https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID}/cancel \--header "Autorisierung: Inhaber ${DATABRICKS_TOKEN}"

Best security practices

The Databricks SQL Statement Execution API increases data transfer security by using end-to-end TLS (Transport Layer Security) encryption and short-lived credentials such as SAS tokens.

This security model consists of several layers. At the transport layer, communication with the Databricks SQL Statement Execution API is only possible over TLS 1.2 or later. Also, callers of the Databricks SQL Statement Execution API must be authenticated with valid Azure DatabricksPersonal access tokenorAzure Active Directory (Azure AD)-TokenThis maps to a user who has permission to use Databricks SQL. This user must havecan useAccess for the specific SQL storage used and access can be restrictedIP access lists. This applies to all requests to the Databricks SQL Statement Execution API. In addition, to run statements, the authenticated user must have permissions on the data objects (such as tables, views, and functions) used in each statement. This is done through existing access control mechanisms in the Unity Catalog or by using ACL tables; to seeA Guide to Data Management. This also means that only the user executing the command can make requests to retrieve the results of the command.

Databricks recommends the following security best practices when using the Databricks SQL Statement Execution API in conjunction withEXTERNAL LINKSReadiness to retrieve large amounts of data:

• Remove the Databricks authorization header for Azure storage requests
• Protect SAS URLs and SAS tokens

TheEXTERNAL LINKSDelay can be disabled upon request by creating a support case. To make this request, create a support case. See _.

Remove the Databricks authorization header for Azure storage requests

All Databricks SQL Statement Execution API calls must include onelicenseA header containing the credentials to access Azure Databricks. Don't turn this onlicenseheader when retrieving data from Azure storage. This header is not required and could inadvertently reveal your Azure Databricks access credentials.

Protect SAS URLs and SAS tokens

Whenever you use itEXTERNAL LINKSDisposition generates a short-lived SAS URL that the caller can use to retrieve results directly from Azure storage using TLS. Because this SAS URL has an ephemeral SAS token embedded in it, you should protect both the SAS URL and the SAS token.