この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
さがらです。
LookerでUNIONを使用したい場合、基本的にはSQL RunnerでUNIONのクエリを書いて、その内容をLookMLのderived_table
パラメータとして出力する流れかと思います。SQL派生テーブルを使う方法ですね。
なのですが、ふと「すでにLookMLで定義したviewをUNIONしたい場合はどうすればいいんだっけ…?」と感じたこともあり、view関係で色々検証してみました。
その検証結果を本エントリでまとめてみます。
検証結果まとめ
まず、検証した結果のまとめから行きます。
- dimensionパラメータやmeasureパラメータで定義されたフィールドを用いたUNIONは不可
- 派生テーブルを用いたUNIONならば、参照元のテーブルにないフィールドを派生テーブルで定義していてもUNION可能
- 派生テーブルを使わずともsql_table_nameパラメータでUNIONは可能
- ※ただし、sql_table_nameでのUNIONは基本的に非推奨です。
以下、それぞれの内容について詳細に説明します。
dimensionパラメータやmeasureパラメータで定義されたフィールドを用いたUNIONは不可
これは「当たり前でしょ!」と思われる方もいるかもしれませんが、改めて説明していきます。
例えば、下記のようなviewを定義していたとします。
view: users_union_no1 { # users_union_no2も同じ内容で作り、UNIONさせます
sql_table_name: "PUBLIC"."USERS" ;;
dimension: id {
primary_key: yes
type: number
sql: ${TABLE}."ID" ;;
}
dimension: city {
type: string
sql: ${TABLE}."CITY" ;;
}
dimension: id_and_city {
type: string
sql: ${id} || ${city};;
}
measure: count {
type: count
}
}
このviewを用いて、以下のようなUNION処理を記述した派生テーブルを動かしてみると、エラーになります。
view: users_union {
derived_table: {
sql:
SELECT id,id_and_city FROM ${users_union_no1.SQL_TABLE_NAME}
UNION ALL
SELECT id,id_and_city FROM ${users_union_no2.SQL_TABLE_NAME}
;;
}
dimension: id {
type: number
}
dimension: id_and_city {
type: string
}
}
なぜエラーとなるのか、その理由は${LookMLで定義された一般的なview名.SQL_TABLE_NAME}が参照するのは、参照先のviewファイルで定義されたsql_table_name
パラメータだからです。
つまり、元のテーブルに存在しないフィールドはSELECTする方法がないんですね。
実際に発行されているクエリを見ると、FROM句には「"PUBLIC"."USERS"」が入っていますね。これは元のviewのsql_table_nameパラメータの値そのものです。
派生テーブルを用いたUNIONならば、参照元のテーブルにないフィールドを派生テーブルで定義していてもUNION可能
一方で、Looker内で独自のフィールドを定義して、そのフィールドが存在するviewをUNIONさせたい場合はどうするのか?
これは、派生テーブルならば可能です。実際の例を用いて説明していきます。
先程の説明で使用したusers_union_no1
を、派生テーブルを使って書いてみると下記のようになります。
view: users_union_no1 { # users_union_no2も同じ内容で作り、UNIONさせます
derived_table: {
sql:
SELECT
id
,city
,id || city AS id_and_city
,count(*)
FROM
"PUBLIC"."USERS"
GROUP BY
id
,city
,id || city
;;
}
}
こちらの派生テーブルを使って、以下のようにUNION処理を記述した派生テーブルを動かしてみると、今度は問題なく動くことが確認できます。
view: users_union {
derived_table: {
sql:
SELECT id,id_and_city FROM ${users_union_no1.SQL_TABLE_NAME}
UNION ALL
SELECT id,id_and_city FROM ${users_union_no2.SQL_TABLE_NAME}
;;
}
dimension: id {
type: number
}
dimension: id_and_city {
type: string
}
}
なぜ、派生テーブルだと上手くいくのか?それは、${派生テーブルで定義されたview名.SQL_TABLE_NAME}が参照するのは、派生テーブルのsqlパラメータに記述されたクエリが返す結果を持つ一時的なテーブルだからです。
つまり、元のテーブルにないフィールドであろうが、派生テーブルで定義されたフィールドならば参照できてしまうんですね。
実際に発行されているクエリを見ると、WITH句でUNIONに使用している派生テーブルの内容が定義されているのがわかるかと思います。
SQL派生テーブルを使わずともsql_table_nameパラメータでUNIONは可能 ※非推奨事項
まず、下記のようなUNIONを行うSQL派生テーブルがあったとします。
view: users_union {
derived_table: {
sql:
SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
UNION ALL
SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
;;
}
dimension: id {
type: number
}
}
このsql
パラメータ内に書かれているクエリを、そのままsql_table_name
に書くとエラーになります。
view: users_union {
sql_table_name:
SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
UNION ALL
SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
;;
dimension: id {
type: number
}
}
しかし!!なんと、()を前後に入れてあげるだけで、エラーが出なくなります。
view: users_union {
sql_table_name:
(
SELECT id FROM ${users_union_no1.SQL_TABLE_NAME}
UNION ALL
SELECT id FROM ${users_union_no2.SQL_TABLE_NAME}
)
;;
dimension: id {
type: number
}
}
なのですが、このsql_table_nameでUNIONを書く方法は非推奨です。
主な理由としては、派生テーブルの場合は永続化など拡張性がありますが、sql_table
_name
の場合はこれ以上何も出来ず、ただ可読性を悪くするだけだからです。
UNIONしたい場合には、派生テーブルを使いましょう!!
最後に
いかがでしたでしょうか。
LookerでUNION処理を行おうとすると少し癖がありますが、UNIONさせたいviewの定義も、UNION処理を行うviewの定義も、基本的に派生テーブルを使うということを押さえて頂ければ問題ないかと思います!