Open SQL インターフェース

Looker の LookML セマンティック モデリング レイヤを使用すると、データ アナリストは SQL データベースでディメンション、集計、計算、データ関係を定義できます。LookML モデルは、コードの再利用性と Git の統合を備えています。適切に構造化された LookML モデルにより、ユーザーは独自のセルフサービスのデータ探索とレポートを実行できます。

LookML モデルは、Looker からのリクエストデータの基盤となります。そのリクエストが Looker UI の Looker の Explore インターフェースから送られてくる場合も、会社のポータルや別のサードパーティ アプリケーションに埋め込まれた可視化や、Looker API　で開発されたカスタム アプリケーションから取得されます。Open SQL インターフェースを使用すると、Java Database Connectivity（JDBC）をサポートするサードパーティ アプリケーションから LookML モデルにアクセスできます。アプリケーションは、データベースであるかのように LookML モデルに接続できるため、ユーザーは使い慣れたツールを使用して、LookML モデルのデータ アナリストが行ったすべての作業を利用できます。

Open SQL インターフェースが LookML プロジェクトの要素を表示する方法

Open SQL インターフェースが LookML プロジェクトの要素をどのように表示するかを理解するには、LookML プロジェクトの構造を理解する必要があります。

LookML　プロジェクトは、SQL クエリを Looker 内で実行するために使われるオブジェクト、データベース接続、ユーザー インターフェース要素を記述するファイルのコレクションです（詳しくは LookML の用語とコンセプトをご覧ください）。次の LookML プロジェクト コンセプトは、Open SQL インターフェースに関連しています。

  • LookML モデルは、データベース接続と 1 つ以上の Explore を指定します。Open SQL インターフェースは、モデルをデータベース スキーマとして表示します。
  • Explore は、1 つ以上のビューの論理グループとそのビュー間の結合関係です。Open SQL インターフェースは、Explore をデータベース テーブルとして表示します。
  • ビューでは、フィールド（ディメンションとメジャーの両方）の集合を定義します。ビューは通常、データベース内のテーブルまたは派生テーブルに基づいています。ビューには、基になるデータベースのテーブルの列と、エンドユーザーが必要とするカスタム ディメンションまたはメジャーを含めることができます。Open SQL インターフェースは、ビュー名とフィールド名の組み合わせをデータベース列名として表示します。たとえば、order_items ビューの id ディメンションは、Open SQL インターフェースによって order_items.id というデータベース列として表示されます。

Looker Explore では、複数のビュー間の結合関係を定義できます。あるビューは、別のビューのフィールドと同じ名前のフィールドを持つことがあるため、Open SQL インターフェースでは、列を参照する際にビュー名とフィールド名の両方が含まれます。そのため、次の形式を使用して、Open SQL インターフェースにクエリを送信するときに列名を参照します。

`<view_name>.<field_name>`

たとえば、customer というビューを product というビューに結合する order_items という名前の Explore があり、その両方のビューに id ディメンションがある場合、2 つの id フィールドをそれぞれ `customer.id` と `product.id` として参照します。Explore 名でも完全修飾名を使用する場合は、`order_items`.`customer.id` と `order_items`.`product.id` の 2 つのフィールドを参照します。（データベース識別子を参照するときにバッククォートを配置する場所については、データベース識別子のバッククォートを使用するをご覧ください）。

Open SQL インターフェースの設定

Open SQL インターフェースを使用するには、次の手順を行います。

  1. 要件を満たしていることを確認する。
  2. Looker インスタンスで Open SQL インターフェースを有効にする。
  3. Open SQL インターフェース JDBC ドライバ ファイルをダウンロードする。

以降のセクションで、これらの手順について説明します。

要件

Open SQL インターフェースを使用するには、次のコンポーネントが必要です。

Looker インスタンスで Open SQL インターフェースを有効にする

次の手順で、Open SQL インターフェースをインスタンスで有効にします。

Open SQL インターフェース JDBC ドライバをダウンロードする

Looker Open SQL インターフェース JDBC ドライバは avatica-<release_number>-looker.jar と呼ばれます。GitHub の https://github.com/looker-open-source/calcite-avatica/releases から最新バージョンをダウンロードします。

JDBC ドライバは次の URL 形式を想定しています。

jdbc:looker:url=https://your Looker instance URL

例:

jdbc:looker:url=https://myInstance.cloud.looker.com

JDBC ドライバのクラスは次のとおりです。

org.apache.calcite.avatica.remote.looker.LookerDriver

Open SQL インターフェースに対する認証

Open SQL インターフェースでは、次の 3 つの認証方法がサポートされています。

OAuth

OAuth をサポートする JDBC クライアントは、Looker インスタンスの OAuth サーバーを使用するように構成できます。次の手順に従って、OAuth 認証を構成します。

  1. API Explorer 拡張機能を使用して、Looker インスタンスに JDBC OAuth クライアントを登録し、Looker インスタンスが OAuth リクエストを認識できるようにします。手順については、OAuth クライアント アプリケーションの登録をご覧ください。
  2. OAuth で Looker にログインし、アクセス トークンをリクエストします。例については、OAuth を使用したユーザー ログインの実行をご覧ください。
  3. Open SQL インターフェースへの JDBC 接続を開くときに、OAuth 認証情報を渡すには、プロパティ オブジェクトを使用します。

DriverManager#getConnection(<String>, <Properties>）の例を次に示します。

String access_token = getAccessToken() //uses the Looker OAuth flow to get a token
String URL = "jdbc:looker:url=https://myInstance.cloud.looker.com"
Properties info = new Properties( );
info.put("token", access_token);
Connection conn = DriverManager.getConnection(URL, info);

API キーを使用してアクセス トークンを生成する

アクセス トークンの生成に標準の OAuth フローを使用する代わりに、次の手順に従って、Looker API を使用して Open SQL インターフェース JDBC ドライバに渡すことができるアクセス トークンを生成できます。

  1. 管理者の設定 - ユーザーのページの説明に従って、Looker ユーザーの API キーを生成します。

  2. Looker インスタンスに login API エンドポイントを使用します。レスポンスには、Authorization: token <access_token> という形式のアクセス トークンが含まれます。このリクエストを行う curl コマンドの例を次に示します。

      curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\

  3. Open SQL インターフェースへの JDBC 接続を開くときに OAuth 認証情報を渡すには、Properties オブジェクトでトークンとしてレスポンスの <access_token> 値を渡します。

API キー

API を使用して、ユーザー名とパスワードの代わりに認証を行うこともできます。API キーは OAuth よりも安全性が低いと判断され、Open SQL インターフェースのプレビュー中にのみ使用できます。Looker インスタンスの API キーの作成については、API キーをご覧ください。

Looker API キーのクライアント ID 部分をユーザー名として使用します。クライアント シークレットの部分はパスワードに使用します。

Open SQL インターフェースを使用したクエリの実行

Open SQL インターフェースを使用してクエリを実行する場合は、次のガイドラインに従ってください。

  • Open SQL インターフェースは、GoogleSQL 構文に準拠する SQL クエリを受け入れます。
  • Open SQL インターフェースでは、モデル、Explore、フィールド識別子をバッククォート（`）で囲む必要があります。詳細と例については、データベース識別子のバッククォートを使用するをご覧ください。
  • Open SQL インターフェースは、ほとんどの BigQuery 演算子をサポートしています。サポートされていない演算子が必要な場合は、looker-sql-interface@google.com にメール リクエストを送信してください。
  • Open SQL インターフェースでは、measure（バッククォートを含む）を特別な関数 AGGREGATE() でラップして、クエリに含まれる LookML measure を指定する必要があります。AGGREGATE() で LookML measure を指定するセクションをご覧ください。

LookML の制限事項

Open SQL インターフェースにクエリを送信する場合は、次の LookML の制限事項に注意してください。

  • Open SQL インターフェースは、Looker のディメンションとmeasureのみをサポートします。Open SQL インターフェースでは、LookML パラメータ filter または parameter はサポートされていません。
  • OpenSQL インターフェースでは、LookML モデルで定義されている always_filter と conditionally_filter の値をオーバーライドして使用することはできません。

SQL の制限事項

Open SQL インターフェースにクエリを送信する場合は、次の SQL の制限事項に注意してください。

データベース識別子のバッククォートを使用する

Open SQL インターフェースにクエリを送信する場合は、スキーマ、テーブル、列の識別子をバッククォートで囲みます。Looker の用語でバッククォートを使用してデータベース要素を指定する方法は次のとおりです。

  • スキーマ: `<model_name>`
  • テーブル: `<explore_name>`

  • 列: `<view_name>.<field_name>`

以下は、これらの要素を使用した SELECT ステートメント形式の例です。

SELECT `view.field`
  FROM `model`.`explore`
  LIMIT 10;

AGGREGATE() で LookML メジャーを指定する

データベース テーブルには通常、ディメンションのみが含まれ、テーブル内の行について単一の属性を記述するデータが含まれます。ただし、LookML プロジェクトではディメンションとメジャーの両方を定義できます。測定値は、SUM、AVG、MIN、MAX などの複数の行にあるデータの集計です。（他のタイプのメジャーもサポートされています。サポートされている LookML メジャータイプの完全なリストについては、メジャータイプページをご覧ください）。

Open SQL インターフェースでは、measure（バッククォートを含む）を特別な関数 AGGREGATE() でラップして、クエリに含まれる LookML measure を指定する必要があります。たとえば、orders ビューから count メジャーを指定するには、次のようにします。

AGGREGATE(`orders.count`)

SELECT 句、HAVING 句、または ORDER BY 句にあるかどうかに関係なく、LookML の測定値を AGGREGATE() 関数でラップする必要があります。

フィールドが LookML 測定値かどうかわからない場合は、DatabaseMetaData.getColumns メソッドを使用して LookML プロジェクトのメタデータにアクセスできます。IS_GENERATEDCOLUMN 列は、すべての LookML 測定値で YES を示し、LookML ディメンションでは NO を示します。詳細については、データベース メタデータへのアクセスをご覧ください。

例

以下は、ディメンションとメジャーの両方を使用するクエリの例です。このクエリでは、顧客ビューから状態ディメンションと都市のディメンションを取得し、[注文] ビューから合計金額 メジャーを取得します。これらのビューはどちらも、e コマースモデルの orders Explore に結合されます。注文が 10 件を超える都市の場合、このクエリ レスポンスで注文金額の上位 5 都市が表示されます。

SELECT `customers.state`, `customers.city`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

JSON_OBJECT を使用してフィルタ限定のフィールドとパラメータを指定する

Open SQL インターフェースは、パラメータとフィルタ専用フィールドをサポートしています。

Open SQL インターフェースを使用してクエリを実行する場合、次の形式で JSON_OBJECT コンストラクタ呼び出しを含めることで、パラメータとフィルタ限定フィールドをクエリに適用できます。

JSON_OBJECT(
    '<view>.<parameter name>', '<parameter value>',
    '<view>.<filter name>', '<Looker filter expression>'
)

JSON オブジェクトには、0 個以上のフィルタ Key-Value ペアと 0 個以上のパラメータ Key-Value ペアを含めることができます。

  • JSON_OBJECT コンストラクタのキーは、フィルタ限定フィールドまたはパラメータの名前にする必要があります。
  • フィルタ限定のフィールドの場合、各キーの値は Looker 文字列フィルタ式である必要があります。
  • パラメータの場合、各キーの値は、parameter 定義で定義されている書式なし値でなければなりません。

Open SQL インターフェースでパラメータとフィルタ限定のフィールドを使用する例については、以下のセクションをご覧ください。

パラメータの例

Open SQL インターフェースで parameter を使用する例として、customers ビューに Looker で次のように定義されたパラメータがあるとします。

parameter: segment {
  type: string
  allowed_value: {
    label: "Small (less than 500)"
    value: "small_customers"
  }
  allowed_value: {
    label: "Larger (greater than 10,000)"
    value: "large_customers"
  }
  allowed_value: {
    label: "Medium customers (Between 500 and 10,000)"
    value: "medium_customers"
  }
}

このクエリを Open SQL インターフェースに送信して、medium_customers の segment パラメータ値をクエリに適用できます。

SELECT `customers.segment_size`,
  AGGREGATE(`orders.total_amount`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'customers.segment', 'medium_customers'
))
GROUP BY `customers.state`, `customers.city`
HAVING AGGREGATE(`orders.count`) > 10
ORDER BY 3 DESC LIMIT 5;

Open SQL インターフェースは、このパラメータ値を Looker のクエリに渡し、Looker は segment パラメータを使用するように構成されている Explore のすべてのフィールドに medium_customers 値を適用します。Looker でのパラメータの仕組みについては、parameter のドキュメントをご覧ください。

フィルタ限定フィールドの例

Open SQL インターフェースでは filter フィールドを使用できます。たとえば、products ビューにディメンションとフィルタ限定フィールドが Looker で次のように定義されているとします。

filter: brand_select {
  type: string
  }

dimension: brand_comparitor {
  sql:
    CASE
      WHEN {% condition brand_select %} ${products.brand_name} {% endcondition %}
      THEN ${products.brand_name}
      ELSE "All Other Brands"
    END ;;
    }

次のようなクエリを送信することで、Open SQL インターフェースで brand_select フィルタを使用できます。

SELECT `products.brand_comparator`, `products.number_of_brands`,
  AGGREGATE(`products.total_revenue`)
FROM `ecommerce`.`orders`(JSON_OBJECT(
    'products.brand_select', '%Santa Cruz%'
))
GROUP BY `products.brand_comparator`
ORDER BY 3 DESC LIMIT 5;

Open SQL インターフェースは、Looker 文字列フィルタ式 %Santa Cruz% を Looker のクエリに適用します。Looker でのフィルタ限定フィールドの仕組みについては、filter のドキュメントをご覧ください。

データベース メタデータへのアクセス

Open SQL インターフェースは、基盤となるデータベースに関する情報の取得に使用される標準 JDBC DatabaseMetaData インターフェースのサブセットをサポートしています。DatabaseMetaData インターフェースの次のメソッドを使用して、LookML モデルに関する情報を取得できます。

DatabaseMetadata.getSchemas

次の表に、LookML モデルと標準データベース構造が、DatabaseMetadata.getSchemas インターフェース メソッドのレスポンスでどのように関連しているかを示します。

getSchemas レスポンス列 説明
TABLE_SCHEM LookML モデル名
TABLE_CATALOG （Null）

DatabaseMetadata.getTables

次の表に、LookML モデルとデータベース構造が、DatabaseMetaData.getTables インターフェース メソッドのレスポンスでどのように関連しているかを示します。レスポンスには、標準の JDBC メタデータと Looker 固有のメタデータが含まれます。

getTables レスポンス列 説明
JDBC 標準メタデータ
TABLE_CAT （Null）
TABLE_SCHEM LookML モデル名
TABLE_NAME LookML Explore の名前
TABLE_TYPE 常に値 TABLE_TYPE を返します。
Looker 固有のメタデータ
DESCRIPTION Explore 説明
LABEL Explore ラベル
TAGS Explore タグ

DatabaseMetadata.getColumns

次の表に、LookML モデルとデータベース構造が、DatabaseMetaData.getColumns インターフェース メソッドのレスポンスでどのように関連しているかを示します。レスポンスには、標準の JDBC メタデータと Looker 固有のメタデータが含まれます。

getColumns レスポンス列 説明
JDBC 標準メタデータ
TABLE_CAT （Null）
TABLE_SCHEM LookML モデル名
TABLE_NAME LookML Explore の名前
COLUMN_NAME `<view_name>.<field_name>` 形式の LookML フィールド名。例: `orders.amount`
DATA_TYPE 列の java.sql.Types コード。たとえば、Looker yesno フィールドは SQL 型コード 16（BOOLEAN）です。
ORDINAL_POSITION Explore 内のフィールドの 1 から始まる序数（ビュー名、フィールド名ごとにディメンションと measure をアルファベット順で混合）
IS_NULLABLE 常に値 YES を返します。
IS_GENERATEDCOLUMN measure の場合は YES、ディメンションの場合は NO
Looker 固有のメタデータ
DIMENSION_GROUP ディメンション グループの名前（フィールドがディメンション グループの一部である場合）。フィールドがディメンション グループの一部でない場合、null を返します。
DRILL_FIELDS ディメンションまたは measure に対して設定されたドリル フィールドのリスト（ある場合）
FIELD_ALIAS フィールドのエイリアス（ある場合）
FIELD_CATEGORY フィールドが dimension または measure かどうか
FIELD_DESCRIPTION フィールドの説明
FIELD_GROUP_VARIANT フィールドがグループラベルに表示されている場合、FIELD_GROUP_VARIANT はグループラベルの下に表示されるフィールドの短縮名を指定します。
FIELD_LABEL フィールド ラベル
FIELD_NAME ディメンションまたは measure の名前
HIDDEN Explore のフィールド ピッカーでフィールドが非表示であるか（TRUE）、または Explore のフィールド ピッカーにフィールドが表示されているか（FALSE）。
LOOKER_TYPE ディメンションまたは measure の LookML フィールド タイプ
REQUIRES_REFRESH_ON_SORT フィールドの値を再並べ替えするために SQL クエリを更新する必要があるか（TRUE）、または SQL クエリを更新を必要とせず、フィールドの値を再並べ替えできるかどうか（FALSE）。
SORTABLE フィールドを並べ替えられる（TRUE）か、並べ替えることができない（FALSE）か
TAGS フィールド タグ
USE_STRICT_VALUE_FORMAT フィールドで strict value formatを使用している（TRUE）かしていない（FALSE）か
VALUE_FORMAT フィールドの Value format 文字列
VIEW_LABEL フィールドの View label
VIEW_NAME フィールドが LookML プロジェクトで定義されているビューの名前

Looker UI での Open SQL インターフェース クエリの特定

Looker 管理者は Looker UI を使用して、Open SQL インターフェースから発信されたクエリを特定できます。

  • クエリ管理ページでは、Open SQL インターフェースからのクエリの　Sourceの値は「Sql Interface」になります。[User] の値には、クエリを実行した Looker ユーザーの名前が表示されます。

  • System Activity Explore の履歴 で、Open SQL インターフェースからのクエリの [Source] の値は「sql_interface」になります。[User Email] の値には、クエリを実行した Looker ユーザーのメールアドレスが表示されます。Looker インスタンスのアドレスをこの URL の先頭に挿入することで、「sql_interface」でフィルタリングされた履歴の Explore に直接移動できます。

    https://your Looker instance URL/explore/system__activity/history?fields=history.source,history.completed_date&f[history.source]=sql_interface

Open SQL インターフェースに関するフィードバック

Open SQL インターフェースに関する質問や機能リクエストについては、looker-sql-interface@google.com にお問い合わせください。