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 名でも完全修飾名を使用する場合は、2 つのフィールドを `order_items`.`customer.id` と `order_items`.`product.id` として参照します。（データベース識別子を参照するときにバッククォートを配置する場所については、データベース識別子のバッククォートを使用するをご覧ください）。
Open SQL インターフェースの設定
Open SQL インターフェースを使用するには、次の手順を行います。
- 要件を満たしていることを確認する。
- Open SQL インターフェース JDBC ドライバ ファイルをダウンロードする。
以降のセクションでは、これらの手順について説明します。
要件
Open SQL インターフェースを使用するには、次のコンポーネントが必要です。
- Looker によりホストされている Looker インスタンスで、Looker 23.18 以降を実行している。
- 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
OAuth をサポートする JDBC クライアントは、Looker インスタンスの OAuth サーバーを使用するように構成できます。次の手順で OAuth 認証を構成します。
- API Explorer 拡張機能を使用して JDBC OAuth クライアントを Looker インスタンスに登録し、Looker インスタンスが OAuth リクエストを認識できるようにします。手順については、OAuth クライアント アプリケーションの登録をご覧ください。
- OAuth を使用して Looker にログインし、アクセス トークンをリクエストします。例については、OAuth を使用してユーザーのログインを実行するをご覧ください。
- 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 ドライバに渡すことができるアクセス トークンを生成できます。
- 管理設定 - ユーザーのページに記載されている手順に沿って、Looker ユーザーの API キーを生成します。
Looker インスタンスの
loginAPI エンドポイントを使用します。レスポンスには、Authorization: token <access_token>形式のアクセス トークンが含まれます。このリクエストに使用できる curl コマンドの例を次に示します。curl -k -d "client_id=<client_id>&client_secret=<client_secret>" https://<looker_host>/login\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 インターフェースにクエリを送信する場合は、次の点に注意してください。
- 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演算子をサポートしていません。 - 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
次の表に、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」になります。[ユーザー] の値には、クエリを実行した 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]=sql_interface
サードパーティの依存関係のリポジトリ
次のリンクから、Looker JDBC ドライバで使用されるサードパーティ依存関係の Google ホスト型リポジトリにアクセスできます。
https://third-party-mirror.googlesource.com/looker_sql_interface/+/refs/heads/master/third_party/
Open SQL インターフェースに関するフィードバック
Open SQL インターフェースに関する質問や機能リクエストについては、looker-sql-interface@google.com にお問い合わせください。