Databricks SQLステートメント実行API - パブリックプレビューを発表

Original Blog : Databricks SQL Statement Execution API – Announcing the Public Preview

翻訳： junichi.maruyama 

本日、AWSとAzureで利用可能なDatabricks SQLステートメント実行APIのパブリックプレビューを発表します。Databricks SQLウェアハウスにREST APIで接続し、Databricks Lakehouse Platformが管理するデータにアクセスし、操作することができるようになりました。

Databricks SQLステートメント実行APIは、データへのアクセスを簡素化し、ニーズに合わせたデータアプリケーションを容易に構築できるようにします。このAPIは非同期型であるため、JDBCやODBCのように接続を管理する必要がなくなります。さらに、最初にドライバをインストールすることなく、SQLウェアハウスに接続することができます。ステートメント実行APIを使用して、従来型およびクラウドベースのアプリケーション、サービス、デバイスをDatabricks SQLに接続することができます。また、選択したプログラミング言語用のカスタムクライアントライブラリを作成することも可能です。

このブログでは、パブリック・プレビューで利用できる主要な機能を確認し、ステートメント実行APIとJavaScriptを使用してスプレッドシートでデータを活用する方法を紹介します。

ステートメント実行APIの概要

ステートメント実行APIは、以下の操作のためにHTTPエンドポイントを介して、すべてのタイプのDatabricks SQLウェアハウス上でSQLステートメントの実行と結果データのフェッチを管理します：

例えば、データアプリケーションに表示するために、当年度の月次受注売上高を取得したいと仮定します。受注に関するデータはすでにレイクハウスで管理されていると仮定すると、SQL文は以下のようになります：

SELECT 
	month(o_orderdate) as Month, 
	sum(o_totalprice) as `Monthly Revenue` 
FROM `samples`.`tpch`.`orders` 
WHERE year(o_orderdate) == year(current_date()) 
GROUP BY 1
ORDER BY 1

/api/2.0/sql/statementsエンドポイントにPOSTリクエストを送信することで、SQL文の実行を開始することができます。SQLステートメントを表す文字列は、ステートメントの実行に使用されるSQL warehouseのIDとともに、リクエストボディのペイロードとして提供されます。HTTPリクエストには、DatabricksワークスペースURLのホストコンポーネントと、認証用のaccess tokenも含まれている必要があります。

POST /api/2.0/sql/statements HTTP/1.1
Host: <your_HOST>
Authorization: Bearer <your_access_token>
Content-Type: application/json
{
    "statement": "SELECT month(o_orderdate) as Month, sum(o_totalprice) as `Monthly Revenue` FROM `samples`.`tpch`.`orders` WHERE year(o_orderdate) == year(current_date()) GROUP BY 1 ORDER BY 1",
    "warehouse_id": "<your_SQL_warehouse_ID>"
}

ステートメントが素早く完了した場合、APIはPOSTリクエストに対する直接的な応答として結果を返します。以下はレスポンスの例です：

{
   "statement_id": "01ed9a57-ad5e-1333-8e76-8c718723abf2",
   "status": {
       "state": "SUCCEEDED"
   },
   "manifest": {
       "format": "JSON_ARRAY",
       "schema": {
           "column_count": 2,
           "columns": [
               {
                   "name": "Month",
                   "type_name": "INT",
                   "position": 0
               },
               {
                   "name": "Monthly Revenue",
                   "type_name": "DECIMAL",
                   "position": 1,
                   "type_precision": 28,
                   "type_scale": 2
               }
           ]
       }
   },
   "result": {
       "chunk_index": 0,
       "row_offset": 0,
       "row_count": 2,
       "data_array": [
           [
               "1",
               "14615808096.95"
           ],
           [
               "2",
               "945871268.15"
           ]
       ]
   }
}

ステートメントに時間がかかる場合、APIは非同期で続行します。この場合、レスポンスにはステートメントIDとステータスが含まれます。

{
    "statement_id": "01ed9a50-c9c9-178e-9be7-0ab52bc998b0",
    "status": {
        "state": "PENDING"
    }
}

ステートメントIDを使って実行状況を確認し、準備ができていれば /api/2.0/sql/statements/{statement_id}エンドポイントにGETリクエストを送信して結果を取得することができるのです

GET /api/2.0/sql/statements/<statement_id> HTTP/1.1
Host: <your_HOST>
Authorization: Bearer <your_access_token>

またステートメントIDを使用して /api/2.0/sql/statements/cancelエンドポイントにPOSTリクエストを送信して、リクエストをキャンセルすることができます。

POST /api/2.0/sql/statements/<statement_id>/cancel HTTP/1.1
Host: <your_HOST>
Authorization: Bearer <your_access_token>

APIは、リクエストをさらに設定することで、同期または非同期で動作するように設定することができます。詳しくは、チュートリアル（AWS | Azure）、ドキュメント（AWS | Azure）をご確認ください。

JavaScriptでDatabricks SQLステートメント実行APIを使う

Databricks SQL Statement Execution APIリクエストは、どのプログラミング言語からでも送信することができます。JavaScriptのFetch API、PythonのPython Requests、Goのnet/httpパッケージなどのメソッドを使用することができます。

ここでは、Spreadsheet AppからJavaScriptのFetch APIを使用して、Statement Execution APIを使用してGoogle Sheetに入力する方法を紹介します。

Spreadsheet appの例

例えば、受注データをスプレッドシートに入力するGoogleスプレッドシートアプリを開発する場合を考えてみましょう。ユーザーは、当月、当年、または開始日と終了日の間の月間受注高など、事前に定義した基準に基づいて月間受注高データを取得することができます。各基準について、対応するSQLステートメントを作成し、ステートメント実行APIを使用して実行のためにそれらを送信し、結果をフェッチして処理することができます。

次のセクションでは、この例を実装するための主な構成要素の概要を説明します。フォローするために、GitHubからスプレッドシートをダウンロードすることができます。

Spreadsheet Appの構築

SQL Statement APIを使用して実行したいステートメントがある場合、以下のexecuteStatement関数は、APIのデフォルトモードを処理するための全体的なロジックをキャプチャします。このモードでは、ステートメントの実行は同期的に開始され、デフォルトのタイムアウトである10秒後に非同期的に継続されます。

まず、submitStatement関数を使用して、実行するステートメントを送信します。ステートメントが定義されたタイムアウト内に完了した場合、handleResult関数を呼び出して結果をフェッチします。そうでない場合は、非同期で実行されます。つまり、完了するまで実行状況をポーリングする必要があり、checkStatus関数が必要なロジックをカバーしています。完了したら、同じhandleResult関数を使用して結果を取得します。

function executeStatement(statement) {
  var response = submitStatement(statement);
  if (response.status.state == "SUCCEEDED") {
   return handleResult(response.manifest, response.result);
  } else {
    response = checkStatus(response.statement_id)
    while (response.status.state == "PENDING" || response.status.state == "RUNNING"
) {
      response = checkStatus(response.statement_id)
    }
   return handleResult(response.manifest, response.result);
  }
}

submitStatement関数は、リクエストボディを定義し、待ち時間10秒（デフォルト）、実行モード、SQL文などの実行パラメータを設定します。さらに、HTTPリクエストを送信するための汎用的なfetchFromUrl関数を呼び出します。また、Databricksワークスペースユーザーのアクセストークンを渡すために、HTTP_REQUEST_BASE定数を定義しています。この定数は、これから行うすべてのHTTPリクエストで再利用します。

const HTTP_REQUEST_BASE = {
 headers: {
   Authorization: `Bearer ${AUTH_TOKEN}`,
 },
 contentType: "application/json",
 method: "GET",
 payload: null,
 muteHttpExceptions: true,
};


function submitStatement(statement) {
 let body = {
   "statement": statement,
   "warehouse_id": WAREHOUSE,
   "wait_timeout": "10s",
   "on_wait_timeout": "CONTINUE",
 };
 let request = Object.assign({}, HTTP_REQUEST_BASE, { method: "POST", payload: JSON.stringify(body) });
 let response = fetchFromUrl(`https://${HOST}/api/2.0/sql/statements`, request);
 if (response.status.state == "FAILED") {
     showError(`Submit request failed with unexpected state: ${response.status.state}`)
     return null;
 }
 return response;
}

fetchFromUrl 関数は、以下のように、最小限のエラー処理で HTTP リクエストを送信するための汎用関数です。

function fetchFromUrl(url, request) {
  try {
    let response = UrlFetchApp.fetch(url, request);
    let responseJson = JSON.parse(response);
    let statusCode = response.getResponseCode();
    switch (statusCode) {
      case 200:
        return responseJson;
      default:
        showError(`Error: code=${responseJson["error_code"]} message=${responseJson["message"]}`);
        return null;
    }
  } catch (error) {
    showError(`Error: error=${error}`);
    return null;
  }
}

checkStatus関数では、待機タイムアウトを超えた場合、サーバーをポーリングしてステートメントの実行状況を取得し、結果をフェッチする準備ができたかどうかを判断します。

function checkStatus(statement_id) {
 let response = fetchFromUrl(`https://${HOST}/api/2.0/sql/statements/${statement_id}`, HTTP_REQUEST_BASE);
 if (response.status.state == "FAILED") {
     let error = responseJson["status"]["error"]
     showError(`Fetch request failed: code=${error["error_code"]} message=${error["message"]}`);
     return null;
 }
 return response;
}

handleResult関数では、ステートメントが正常に完了し、結果が利用可能な場合、フェッチレスポンスは常に行の最初のチャンクを含んでいます。この関数は結果を処理し、利用可能であれば後続のチャンクをフェッチしようとします。

function handleResult(manifest, result) {
 var columnNames = manifest["schema"]["columns"].map(col => col["name"]);
 var chunks = [result.data_array];


 while (result["next_chunk_internal_link"]) {
   chunk = result["next_chunk_internal_link"];
   result = fetchFromUrl(`https://${HOST}${chunk}`, HTTP_REQUEST_BASE);
   chunks.push(result["data_array"]);
 }


 return [[columnNames]].concat(chunks).flat()
}

あとは、executeStatement 関数をさまざまなユーザーインターフェイスウィジェットの JavaScript イベントハンドラに接続し、ユーザーの選択に対応する SQL 文を渡すだけです。Google Apps Scriptのドキュメントには、返されたデータをスプレッドシートに入力する手順が記載されています。

Databricks SQLステートメント実行APIを始めるにあたって

The Databricks SQL Statement Execution API is available with the Databricks Premium and Enterprise tiers. If you already have a Databricks account, follow our tutorial (AWS | Azure), the documentation (AWS | Azure), or check our repository of code samples. If you are not an existing Databricks customer, sign up for a free trial.

The Databricks SQL Statement Execution API complements the wide range of options to connect to your Databricks SQL warehouse. Check our previous blog post to learn more about native connectivity to Python, Go, Node.js, the CLI, and ODBC/JDBC. Data managed by the Databricks Lakehouse Platform can truly be accessed from anywhere!

Join us at the Data + AI Summit 2023 to learn more about the Databricks SQL Statement Execution API and to get updates on what is coming next.

Databricks SQLステートメント実行APIは、Databricks PremiumおよびEnterprise Tierで利用できます。すでに Databricks アカウントをお持ちの方は、チュートリアル (AWS | Azure) やドキュメント (AWS | Azure) に従うか、コードサンプルのrepositoryを確認してください。Databricks の既存顧客でない場合は、free trialにサインアップしてください。

Databricks SQLステートメント実行APIは、Databricks SQLウェアハウスに接続するための幅広い選択肢を補完するものです。Python、Go、Node.js、CLI、ODBC/JDBCへのネイティブ接続については、以前のブログポストをご確認ください。Databricks Lakehouse Platformで管理されるデータは、本当にどこからでもアクセスできるようになります！

Data + AI Summit 2023に参加して、Databricks SQLステートメント実行APIについての詳細と、今後の最新情報を入手してください。