ステートメント実行API: ウェアハウスでSQLを実行する

重要

Databricks REST APIsにアクセスするには、認証する必要があります。

このチュートリアルでは、Databricks SQL ステートメント実行 API 2.0 を使用して、Databricks SQLウェアハウスから SQL ステートメントを実行する方法を説明します。

Databricks SQL ステートメント実行 API 2.0 リファレンスを表示するには、 「ステートメントの実行」を参照してください。

始める前に

このチュートリアルを開始する前に、次のものが揃っていることを確認してください。

  • 次のように、Databricks CLI バージョン 0.205 以降、またはcurlのいずれか:

    • Databricks CLI は、Databricks REST API の要求と応答を送受信するためのコマンドライン ツールです。 Databricks CLI バージョン 0.205 以降を使用する場合は、Databricks ワークスペースで認証するように構成する必要があります。 「Databricks CLI のインストールまたは更新」および「Databricks CLI の認証」を参照してください。

      たとえば、Databricks 個人用アクセストークン認証で認証するには、「ワークスペース ユーザー向けの個人用アクセストークンDatabricks」の手順に従います。

      次に、Databricks CLI を使用して個人のアクセス権用の Databricks 構成プロファイルを作成するには、次の手順を実行します。

      注：

      次の手順では、Databricks CLIを使用して、 DEFAULTという名前のDatabricks構成プロファイルを作成します。すでにDEFAULT構成プロファイルをお持ちの場合、この手順により既存のDEFAULT構成プロファイルが上書きされます。

      DEFAULT構成プロファイルが既に存在するかどうかを確認し、存在する場合はこのプロファイルの設定を表示するには、Databricks CLI を使用してコマンドdatabricks auth env --profile DEFAULTを実行します。

      DEFAULT以外の名前で構成プロファイルを作成するには、次のdatabricks configureコマンドの--profile DEFAULTのDEFAULT部分を構成プロファイルの別の名前に置き換えます。

      1. この を使用して、 個人用アクセストークン認証を使用する DatabricksCLIという名前のDatabricks 構成プロファイル を作成します。DEFAULTDatabricksこれを行うには、次のコマンドを実行します。

        databricks configure --profile DEFAULT

      2. プロンプト Databricks Host に、Databricks ワークスペース インスタンスの URL を入力します (例: https://1234567890123456.7.gcp.databricks.com)。

      3. プロンプトの Personal Access Tokenに、ワークスペースの Databricks 個人用アクセストークンを入力します。

      このチュートリアルの Databricks CLI の例では、次の点に注意してください。

      • このチュートリアルでは、ローカル開発マシンに環境変数DATABRICKS_SQL_WAREHOUSE_IDがあることを前提としています。 この環境変数は、Databricks SQL ウェアハウスの ID を表します。 この ID は、ウェアハウスのHTTP パスフィールドの/sql/1.0/warehouses/に続く文字と数字の文字列です。 ウェアハウスのHTTP パス値を取得する方法については、 「Databricks コンピュート リソースの接続の詳細を取得する」を参照してください。

      • Unix、Linux、または macOS のコマンド シェルの代わりに Windows コマンド シェルを使用する場合は、 \を^に置き換え、 ${...} %...%に置き換えます。

      • Unix、Linux、または macOS のコマンド シェルの代わりに Windows コマンド シェルを使用する場合は、JSON ドキュメント宣言で、開始と終了の' "に置き換え、内側の"を\"に置き換えます。

    • curl は、REST API の要求と応答を送受信するためのコマンドライン ツールです。 「curl のインストール」も参照してください。または、このチュートリアルの curl の例を HTTPie などの同様のツールで使用できるように適応させます。

      このチュートリアルのcurlの例では、次の点に注意してください。

      • --header "Authorization: Bearer ${DATABRICKS_TOKEN}"の代わりに .netrc ファイル。 .netrc ファイルを使用する場合は、--header "Authorization: Bearer ${DATABRICKS_TOKEN}" を --netrcに置き換えてください。

      • Unix、Linux、または macOS のコマンド シェルの代わりに Windows コマンド シェルを使用する場合は、 \を^に置き換え、 ${...} %...%に置き換えます。

      • Unix、Linux、または macOS のコマンド シェルの代わりに Windows コマンド シェルを使用する場合は、JSON ドキュメント宣言で、開始と終了の' "に置き換え、内側の"を\"に置き換えます。

      また、このチュートリアルのcurlの例では、ローカル開発マシンに次の環境変数があることを前提としています。

      注：

      セキュリティのベスト プラクティスとして、自動化されたツール、システム、スクリプト、アプリを使用して認証する場合、 Databricksでは、ワークスペース ユーザーではなく、サービス プリンシパルに属する個人のアクセス トークンを使用することをお勧めします。 サービスプリンシパルのトークンを作成するには、 「サービスプリンシパルのトークンの管理」を参照してください。

      Databricks個人用アクセストークンを作成するには、ワークスペース ユーザー向けの個人用アクセストークンDatabricksの要領に従ってください。

      警告

      この機密情報はバージョン管理システムを通じてプレーン テキストで公開される可能性があるため、Databricks ではスクリプトに情報をハードコーディングしないことを強くお勧めします。 Databricks では、代わりに開発マシンに設定する環境変数などのアプローチを使用することをお勧めします。 このようなハードコーディングされた情報をスクリプトから削除すると、スクリプトの移植性も高まります。

  • このチュートリアルでは、JSON 応答ペイロードをクエリするためのコマンドライン プロセッサであるjqも持っていることを前提としています。これは、Databricks SQL Statement Execution API を呼び出すたびに Databricks SQL Statement Execution API から返されます。 「jq のダウンロード」を参照してください。

  • SQL ステートメントを実行できるテーブルが少なくとも 1 つ必要です。 このチュートリアルは、 samplesカタログ内のtpchスキーマ (データベースとも呼ばれます) のlineitemテーブルに基づいています。 ワークスペースからこのカタログ、スキーマ、またはテーブルにアクセスできない場合は、このチュートリアル全体でこれらを独自のものに置き換えてください。

ステップ 1: SQL ステートメントを実行し、データ結果を JSON として保存する

次のコマンドを実行すると、次のことが行われます。

  1. 指定された SQL ウェアハウスと、 curlを使用している場合は指定されたトークンを使用して、 samplesカタログ内のtcphスキーマにあるlineitemテーブルの最初の 2 行から 3 つの列をクエリします。

  2. 応答ペイロードを現在の作業ディレクトリ内のsql-execution-response.jsonという名前のファイルに JSON 形式で保存します。

  3. sql-execution-response.jsonファイルの内容を印刷します。

  4. SQL_STATEMENT_IDという名前のローカル環境変数を設定します。 この変数には、対応する SQL ステートメントの ID が含まれます。 この SQL ステートメント ID を使用して、後で必要に応じてそのステートメントに関する情報を取得できます。これについては、ステップ 2 で説明します。この SQL ステートメントを表示して、Databricks SQL コンソールのクエリ履歴セクションからステートメント ID を取得することも、または呼び出しによってステートメント ID を取得することもできます。投稿ー履歴 API 。

  5. JSON データの次のチャンクを取得するための API URL フラグメントを含む、 NEXT_CHUNK_EXTERNAL_LINKという名前の追加のローカル環境変数を設定します。 応答データが大きすぎる場合、Databricks SQL ステートメント実行 API は応答をチャンクで提供します。 この API URL フラグメントを使用して、データの次のチャンクを取得できます。これについては、ステップ 2 で説明します。次のチャンクがない場合、この環境変数はnullに設定されます。

  6. SQL_STATEMENT_IDおよびNEXT_CHUNK_INTERNAL_LINK環境変数の値を出力します。

databricks api post /api/2.0/sql/statements \
--profile <profile-name> \
--json '{
  "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'",
  "catalog": "samples",
  "schema": "tpch",
  "statement": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem WHERE l_extendedprice > :extended_price AND l_shipdate > :ship_date LIMIT :row_limit",
  "parameters": [
    { "name": "extended_price", "value": "60000", "type": "DECIMAL(18,2)" },
    { "name": "ship_date", "value": "1995-01-01", "type": "DATE" },
    { "name": "row_limit", "value": "2", "type": "INT" }
  ]
}' \
> 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \
&& export 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

<profile-name> を認証用の Databricks 構成プロファイルの名前に置き換えます。

curl --request POST \
https://${DATABRICKS_HOST}/api/2.0/sql/statements/ \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}" \
--header "Content-Type: application/json" \
--data '{
  "warehouse_id": "'"$DATABRICKS_SQL_WAREHOUSE_ID"'",
  "catalog": "samples",
  "schema": "tpch",
  "statement": "SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem WHERE l_extendedprice > :extended_price AND l_shipdate > :ship_date LIMIT :row_limit",
  "parameters": [
    { "name": "extended_price", "value": "60000", "type": "DECIMAL(18,2)" },
    { "name": "ship_date", "value": "1995-01-01", "type": "DATE" },
    { "name": "row_limit", "value": "2", "type": "INT" }
  ]
}' \
--output 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export SQL_STATEMENT_ID=$(jq -r .statement_id 'sql-execution-response.json') \
&& export 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

上記の要求では、次のようになります。

  • パラメータ化されたクエリは、コロンが前に付いた各クエリの名前 (例: :extended_price ) と、 parameters配列内の一致するnameおよびvalueオブジェクトで構成されます。 オプションのtypeも指定できます。指定しない場合は、デフォルト値のSTRINGが使用されます。

    警告

    Databricks では、SQL ステートメントのベスト プラクティスとしてパラメーターを使用することを強くお勧めします。

    SQL を動的に生成するアプリケーションで Databricks SQL ステートメント実行 API を使用すると、SQL インジェクション攻撃が発生する可能性があります。 たとえば、ユーザー インターフェイスでのユーザーの選択に基づいて SQL コードを生成し、適切な措置を講じなかった場合、攻撃者は悪意のある SQL コードを挿入して最初のクエリのロジックを変更し、それによって機密データの読み取り、変更、または削除が行われる可能性があります。

    パラメーター化されたクエリは、入力引数を SQL コードの残りの部分とは別に処理し、これらの引数をリテラル値として解釈することで、SQL インジェクション攻撃から保護するのに役立ちます。 パラメーターはコードの再利用にも役立ちます。

  • デフォルトでは、返されるデータはすべて JSON 配列形式であり、SQL ステートメントのデータ結果のデフォルトの場所は応答ペイロード内にあります。 この動作を明示的にするには、要求ペイロードに "format":"JSON_ARRAY","disposition":"INLINE" を追加します。 応答ペイロードで 25 MiB を超えるデータ結果を返そうとすると、失敗ステータスが返され、SQL ステートメントがキャンセルされます。 25 MiB を超えるデータ結果の場合は、ステップ 3 で示すように、応答ペイロードで返そうとする代わりに、外部リンクを使用できます。

  • このコマンドは、応答ペイロードの内容をローカル ファイルに保存します。 ローカル データ ストレージは、Databricks SQL ステートメント実行 API では直接サポートされていません。

  • デフォルトでは、10 秒後、ウェアハウスを介した SQL ステートメントの実行がまだ終了していない場合、Databricks SQL ステートメント実行 API はステートメントの結果ではなく、SQL ステートメント ID とその現在のステータスのみを返します。 この動作を変更するには、要求に "wait_timeout" を追加し、 "<x>s"に設定します。ここで、 <x> は 5 秒から 50 秒 ( "50s"など) です。 SQL ステートメント ID とその現在のステータスをすぐに返すには、 wait_timeoutを0sに設定します。

  • デフォルトでは、タイムアウト期間に達しても SQL ステートメントは実行を継続します。 タイムアウト期間に達した場合に SQL ステートメントをキャンセルするには、リクエスト ペイロードに"on_wait_timeout":"CANCEL"を追加します。

  • 返されるバイト数を制限するには、要求に "byte_limit" を追加し、バイト数 ( 1000など) に設定します。

  • 返される行数を制限するには、statementに LIMIT 句を追加する代わりに、要求に "row_limit" を追加し、行数 ("statement":"SELECT * FROM lineitem","row_limit":2など) に設定します。

  • 結果が指定された byte_limit または row_limitよりも大きい場合、応答ペイロードの truncated フィールドは true に設定されます。

待機タイムアウトが終了する前にステートメントの結果が使用可能な場合、応答は次のようになります。

{
  "manifest": {
    "chunks": [
      {
        "chunk_index": 0,
        "row_count": 2,
        "row_offset": 0
      }
    ],
    "format": "JSON_ARRAY",
    "schema": {
      "column_count": 3,
      "columns": [
        {
          "name": "l_orderkey",
          "position": 0,
          "type_name": "LONG",
          "type_text": "BIGINT"
        },
        {
          "name": "l_extendedprice",
          "position": 1,
          "type_name": "DECIMAL",
          "type_precision": 18,
          "type_scale": 2,
          "type_text": "DECIMAL(18,2)"
        },
        {
          "name": "l_shipdate",
          "position": 2,
          "type_name": "DATE",
          "type_text": "DATE"
        }
      ]
    },
    "total_chunk_count": 1,
    "total_row_count": 2,
    "truncated": false
  },
  "result": {
    "chunk_index": 0,
    "data_array": [
      [
        "2",
        "71433.16",
        "1997-01-28"
      ],
      [
        "7",
        "86152.02",
        "1996-01-15"
      ]
    ],
    "row_count": 2,
    "row_offset": 0
  },
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "SUCCEEDED"
  }
}

ステートメントの結果が使用可能になる前に待機タイムアウトが終了した場合、応答は次のようになります。

{
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "PENDING"
  }
}

ステートメントの結果データが大きすぎる場合 (たとえば、この場合、 SELECT l_orderkey, l_extendedprice, l_shipdate FROM lineitem LIMIT 300000を実行するなど)、結果データはチャンク化され、代わりに次のようになります。 簡潔にするために、ここでは "...": "..." 結果を省略していることに注意してください。

{
  "manifest": {
    "chunks": [
      {
        "chunk_index": 0,
        "row_count": 188416,
        "row_offset": 0
      },
      {
        "chunk_index": 1,
        "row_count": 111584,
        "row_offset": 188416
      }
    ],
    "format":"JSON_ARRAY",
    "schema": {
      "column_count":3,
      "columns": [
        {
          "...": "..."
        }
      ]
    },
    "total_chunk_count": 2,
    "total_row_count": 300000,
    "truncated": false
  },
  "result": {
    "chunk_index": 0,
    "data_array": [
      [
        "2",
        "71433.16",
        "1997-01-28"
      ],
      [
        "..."
      ]
    ],
    "next_chunk_index": 1,
    "next_chunk_internal_link": "/api/2.0/sql/statements/00000000-0000-0000-0000-000000000000/result/chunks/1?row_offset=188416",
    "row_count": 188416,
    "row_offset": 0
  },
  "statement_id": "00000000-0000-0000-0000-000000000000",
  "status": {
    "state": "SUCCEEDED"
  }
}

ステップ 2: ステートメントの現在の実行ステータスとデータ結果を JSON として取得する

SQL ステートメントの ID を使用して、そのステートメントの現在の実行ステータスを取得し、実行が成功した場合はそのステートメントの結果を取得できます。 ステートメントの ID を忘れた場合は、Databricks SQL コンソールのクエリ履歴セクションから取得するか、書き込みー履歴 APIを呼び出して取得できます。 たとえば、このコマンドをポーリングし続けて、実行が成功したかどうかを毎回チェックすることができます。

SQL ステートメントの現在の実行ステータスを取得し、実行が成功した場合はそのステートメントの結果と、JSON データの次のチャンクを取得するための API URL フラグメントを取得するには、次のコマンドを実行します。 このコマンドは、ローカル開発マシンにSQL_STATEMENT_IDという名前の環境変数があり、前のステップの SQL ステートメントの ID の値に設定されていることを前提としています。 もちろん、次のコマンドの${SQL_STATEMENT_ID} SQL ステートメントのハードコードされた ID に置き換えることもできます。

databricks api get /api/2.0/sql/statements/${SQL_STATEMENT_ID} \
--profile <profile-name> \
> '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

<profile-name> を認証用の Databricks 構成プロファイルの名前に置き換えます。

curl --request GET \
https://${DATABRICKS_HOST}/api/2.0/sql/statements/${SQL_STATEMENT_ID} \
--header "Authorization: Bearer ${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

NEXT_CHUNK_INTERNAL_LINKがnull以外の値に設定されている場合は、たとえば次のコマンドを使用して、それを使用して次のデータ チャンクを取得することができます。

databricks api get /${NEXT_CHUNK_INTERNAL_LINK} \
--profile <profile-name> \
> 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

<profile-name> を認証用の Databricks 構成プロファイルの名前に置き換えます。

curl --request GET \
https://${DATABRICKS_HOST}${NEXT_CHUNK_INTERNAL_LINK} \
--header "Authorization: Bearer ${DATABRICKS_TOKEN}" \
--output 'sql-execution-response.json' \
&& jq . 'sql-execution-response.json' \
&& export NEXT_CHUNK_INTERNAL_LINK=$(jq -r .next_chunk_internal_link 'sql-execution-response.json') \
&& echo NEXT_CHUNK_INTERNAL_LINK=$NEXT_CHUNK_INTERNAL_LINK

前述のコマンドを何度も実行し続けて、次のチャンクを取得するなどできます。 最後のチャンクがフェッチされるとすぐに、SQL ステートメントが閉じられることに注意してください。 このクローズの後は、そのステートメントの ID を使用して現在のステータスを取得したり、それ以上のチャンクをフェッチしたりすることはできません。

ステップ 4: SQL ステートメントの実行をキャンセルする

まだ成功していない SQL ステートメントをキャンセルする必要がある場合は、次のコマンドを実行します。

databricks api post /api/2.0/sql/statements/${SQL_STATEMENT_ID}/cancel \
--profile <profile-name> \
--json '{}'

<profile-name> を認証用の Databricks 構成プロファイルの名前に置き換えます。

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

セキュリティのベストプラクティス

Databricks SQL ステートメント実行 API は、エンドツーエンドのトランスポート層セキュリティ (TLS) 暗号化と署名付き URL などの有効期間の短い資格情報を使用して、データ転送のセキュリティを強化します。

Databricks では、Databricks SQL Statement Execution API をEXTERNAL_LINKS処理とともに使用して大規模なデータ セットを取得する場合は常に、次のセキュリティのベスト プラクティスを推奨します。

  • Google Cloud Storage リクエストの Databricks 認証ヘッダーを削除する

  • 署名付きURLの保護

EXTERNAL_LINKSの廃棄は、サポートケースを作成することで、要求に応じて無効にすることができます。「サポート」を参照してください。

Google Cloud Storage リクエストの Databricks 認証ヘッダーを削除する

curlを使用する Databricks SQL ステートメント実行 API へのすべての呼び出しには、Databricks アクセス資格情報を含むAuthorizationヘッダーを含める必要があります。 Google Cloud Storage からデータをダウンロードするときは、このAuthorizationヘッダーを含めないでください。 このヘッダーは必須ではなく、Databricks アクセス資格情報が意図せず公開される可能性があります。

署名付きURLの保護

EXTERNAL_LINKS処理を使用するたびに、有効期間が短い署名付き URL が生成され、呼び出し元は TLS を使用して Google Cloud Storage から直接結果をダウンロードできます。 この署名付きURLには有効期間の短い資格情報が埋め込まれているため、URLを保護する必要があります。