Open SQL インターフェース

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

LookML モデルは、Looker からリクエストされるすべてのデータの基盤となります。リクエストは、Looker UI の Looker Explore インターフェース、会社のポータルや他のサードパーティ アプリケーションに埋め込まれたビジュアライゼーション、Looker API で開発されたカスタム アプリケーションからのものであるかどうかに関係なく、LookML モデルが基盤となります。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 名でも完全修飾名を使用する場合は、2 つのフィールドを `order_items`.`customer.id` と `order_items`.`product.id` として参照します。（データベース識別子を参照するときにバッククォートを配置する場所については、データベース識別子のバッククォートを使用するをご覧ください）。

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

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

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

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

要件

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

• 使用するサードパーティ アプリケーション（Tableau、ThoughtSpot、カスタマイズされたアプリケーションなど）が Looker インスタンスに接続できる必要があります。Open SQL インターフェースは、サードパーティ アプリケーションが Looker インスタンスにアクセスできるように Looker インスタンスがネットワークに接続されている限り、セルフホスト型 Looker インスタンスで使用できます。
• Google BigQuery 接続のデータを使用する LookML プロジェクト。（LookML プロジェクトには、connection パラメータで Google BigQuery 接続を指定するモデルファイルが必要です）。
• Open SQL インターフェースでアクセスする LookML モデルに対する explore 権限を含む Looker ユーザーロール。

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://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
• API キーを使用してアクセス トークンを生成する
• API キー

OAuth

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

1. API Explorer 拡張機能を使用して JDBC OAuth クライアントを Looker インスタンスに登録し、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 演算子をサポートしています。
• Open SQL インターフェースでは、measure（バッククォートを含む）を特別な関数 AGGREGATE() でラップして、クエリに含まれる LookML measure を指定する必要があります。AGGREGATE() で LookML measure を指定するセクションをご覧ください。

LookML の制限事項

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

• Open SQL インターフェースのクエリで WHERE 句を使用して、always_filter 値と conditionally_filter 値を LookML モデルに渡すことができます。

SQL の制限事項

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

• Open SQL インターフェースは、SELECT クエリのみをサポートします。Open SQL インターフェースは、UPDATE ステートメントと DELETE ステートメント、またはその他のデータ定義言語（DDL）、データ操作言語（DML）またはデータ制御言語（DCL）のステートメントをサポートしていません。
• Open SQL インターフェースは、JOIN 演算子をサポートしていません。
  • JOIN 演算子を含むクエリを Open SQL インターフェースに送信して、同じ Explore 内または 2 つの異なる Explore 間で結合を作成することはできません。
  • データベース内の 2 つのテーブル間の結合を作成する場合は、LookML モデルで　LookML プロジェクトのモデルファイル内の探索定義内に 1 つ以上のビューへの結合を作成します。
• Open SQL インターフェースは、ウィンドウ関数の呼び出しをサポートしていません。
• Open SQL インターフェースは、サブクエリをサポートしていません。
• Open SQL インターフェースは、タイムゾーンの変換をサポートしていません。LookML モデルの日時は、設定で定義されているタイムゾーン（ユーザーのタイムゾーン、アプリケーションのタイムゾーン、データベースのタイムゾーンの設定）で DATETIME 型になります。
• Open SQL インターフェースは、BigQuery のデータ型 geography、JSON、時間をサポートしていません。

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

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`)

LookML メジャーは、SELECT 句、HAVING 句、ORDER BY 句のいずれにあっても、AGGREGATE() 関数でラップする必要があります。

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

例

ディメンションとメジャーの両方を使用したクエリの例を次に示します。このクエリでは、顧客ビューから状態ディメンションと都市のディメンションを取得し、[注文] ビューから合計金額 メジャーを取得します。これらのビューはどちらも、ecommerce モデルの 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
• DatabaseMetaData.getTables

• DatabaseMetaData.getColumns

DatabaseMetadata.getSchemas

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

DatabaseMetadata.getTables

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

DatabaseMetadata.getColumns

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

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

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

• クエリ管理ページでは、Open SQL インターフェースからのクエリの　Sourceの値は「SQL Interface」になります。[ユーザー] の値には、クエリを実行した Looker ユーザーの名前が表示されます。クエリの [詳細] ボタンをクリックすると、クエリに関する追加情報が表示されます。[詳細] ダイアログで [SQL インターフェースのクエリ] をクリックすると、Open SQL インターフェースから Looker に送信された SQL クエリを確認できます。

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

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

サードパーティの依存関係のリポジトリ

次のリンクから、Looker JDBC ドライバで使用されるサードパーティ依存関係の Google ホスト型リポジトリにアクセスできます。

https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/