Statement Execution 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 の個人アクセスウイルス認証で認証するには、次のように個人アクセスウイルスを作成します。

      1. Databricksワークスペースで、上部バーにあるDatabricksユーザー名をクリックし、ドロップダウンから [ユーザー設定] を選択します。

      2. [ 開発者] をクリックします。

      3. [アクセス許可]の横にある[管理]をクリックします。

      4. 「新しいトークンの生成」をクリックします。

      5. (任意)今後このトークンを識別するのに役立つコメントを入力し、トークンのデフォルトの有効期間である90日を変更します。有効期間のないトークンを作成するには(非推奨)、[有効期間 (日) ] ボックスを空白のままにしてください。

      6. [生成] をクリックします。

      7. 表示されたトークンを安全な場所にコピーし、[完了] をクリックします。

      注:

      コピーしたトークンは必ず安全な場所に保存してください。 コピーしたトークンを他の人と共有しないでください。 コピーしたトークンを紛失した場合、まったく同じトークンを再生成することはできません。 代わりに、この手順を繰り返して新しいトークンを作成する必要があります。 コピーしたトークンを紛失した場合、またはトークンが侵害されたと思われる場合は、Databricks では、アクセス許可ページのトークンの横にあるゴミ箱 (取り消し) アイコンをクリックして、ワークスペースからそのトークンを直ちに削除することを強くお勧めします。

      ワークスペースでトークンを作成または使用できない場合は、ワークスペース管理者がトークンを無効にしたか、トークンを作成または使用する権限を与えていないことが原因である可能性があります。ワークスペース管理者に問い合わせるか、以下をご覧ください。

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

      注:

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

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

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

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

        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の例をPostmanHTTPieなどの同様のツールで使用できるように調整することもできます。

      このチュートリアルの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 の個人アクセスを作成するには、次の手順を実行します。

      1. Databricksワークスペースで、上部バーにあるDatabricksユーザー名をクリックし、ドロップダウンから [ユーザー設定] を選択します。

      2. [ 開発者] をクリックします。

      3. [アクセス許可]の横にある[管理]をクリックします。

      4. 「新しいトークンの生成」をクリックします。

      5. (任意)今後このトークンを識別するのに役立つコメントを入力し、トークンのデフォルトの有効期間である90日を変更します。有効期間のないトークンを作成するには(非推奨)、[有効期間 (日) ] ボックスを空白のままにしてください。

      6. [生成] をクリックします。

      7. 表示されたトークンを安全な場所にコピーし、[完了] をクリックします。

      注:

      コピーしたトークンは必ず安全な場所に保存してください。 コピーしたトークンを他の人と共有しないでください。 コピーしたトークンを紛失した場合、まったく同じトークンを再生成することはできません。 代わりに、この手順を繰り返して新しいトークンを作成する必要があります。 コピーしたトークンを紛失した場合、またはトークンが侵害されたと思われる場合は、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_timeout0sに設定します。

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

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

  • 返される行数を制限するには、statementLIMIT 句を追加する代わりに、要求に "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_LINKnull以外の値に設定されている場合は、たとえば次のコマンドを使用して、それを使用して次のデータ チャンクを取得することができます。

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

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