はじめに
GoogleのBIツールであるLooker Studioではいろいろなサービスやデータベースをデータソースとして扱うことができます。
もちろんSpannerもデータソースとして使うことができるのですが、いくつかハマりどころがあったのでメモしておきます。
2024-12-27追記
以下のハマりどころは、BigQueryのEXTERNAL_QUERY()を用いた連携クエリ経由で利用することによって、すべて解決することを確認しました。
Spannerをデータソースとして設定する
たとえばBigQueryだと、テーブルを直接指定したり、カスタムクエリを書くことでデータソースを設定することができます。
一方、Spannerの場合は、テーブルを直接指定することはできず、クエリを書いてその結果をデータソースとして扱うことしかできません。
ただし、この画面では最低限のシンタックスハイライトはされるものの、補完が効かなかったりエラーも表示されないため、実際にクエリを書く際にはSpanner Studioや他のテキストエディタなどを使うことになると思います。
ハマる
書いたクエリでエラーなく欲しいデータを取得できることを、Spanner Studioやspanner-cliで確認します。そこそこ複雑なクエリを書いたかもしれません。そんなに複雑じゃないクエリかもしれません。とにかくLooker Studioのデータソースとして使うため、クエリを貼り付けましょう。
さっきまで問題なかったクエリがうまくいきません、、、
ハマりどころ1: ディメンションの数は最大10個まで？
Looker StudioのデータソースとしてSpannerを使う際、ディメンションの数が10個を超えることはできません。
select
'01' as col01,
'02' as col02,
'03' as col03,
'04' as col04,
'05' as col05,
'06' as col06,
'07' as col07,
'08' as col08,
'09' as col09,
'10' as col10,
'11' as col11,
上記のクエリを貼り付けて、ディメンションを追加していくと、10個追加した時点で通常表示されている追加ボタンが表示されなくなりドラッグ&ドロップでの追加もできなくなります。
1つのグラフに対して10個までの制限のようなので、たとえば表に10個以上のディメンションを表示したい場合などは、別の表に表示してクロスフィルタリングを設定することで回避することができます。
ハマりどころ2: 使える数値型に制限あり？
float32型とnumeric型は使えません。一見エラーなくデータソースの設定ができたように見えますが、フィールドとして認識されません。
select
1 as literal,
cast(1 as int64) as i64,
cast(1 as float32) as f32,
cast(1 as float64) as f64,
cast(1 as numeric) as n,
使えないのなら何かエラーを表示してほしい、、、
ハマりどころ3: WITH句によるCTEが使えない？
WITH句によるCTEは使えません。CTEをサブクエリに置き換えたクエリを書く必要があります。
with
t as (
select 1 as col01
)
select
col01
from
t
これも、一見データソースを設定できてフィールドも正しく追加できたように見えますが、グラフのデータソースとして設定した途端、あまりにも不親切なエラーが表示されます。まぁ、エラーが表示されるだけマシなのですが、、、
複雑なクエリだとサブクエリは書きたくないですよね。
ハマりどころ4: カラム名と他のディメンションの組み合わせによって値が表示されない？
カラム名と他のディメンションおよびディメンションの値の組み合わせによって値が表示されないことがあります。
select
cast('AAA' as string) as p1,
cast(null as int64) as c2,
このクエリでp1だけをディメンションすると'AAA'が表示されますが、c2もディメンションに追加すると、p1の値が表示されなくなります。また、c2の値がnullでない場合はp1の値が表示されます。
ところが、カラム名p1をc1に変更するとc1の値'AAA'は表示されます。
select
cast('AAA' as string) as c1,
cast(null as int64) as c2,
さっぱり意味がわかりません。
こちらのissueで報告してみましたが、いつ改善されるかわかりません。https://issuetracker.google.com/issues/367562656
ハマりどころ5: nullを取りうるディメンションがあると他のディメンションの値がゼロ値になる？
nullを取りうるカラムをディメンションとして追加した場合、そのカラムの値がnullの場合、同一レコードの他のカラムの値がゼロ値になることがあります。詳細な再現条件はまだはっきりしておらず、最小限の再現するクエリを提示できません。coalesce関数を使ってnullを空文字列に変換することで回避できることがありました。
まとめ
Looker StudioでSpannerをデータソースとして使う際、これまで私がハマったことがあるケースをいくつか紹介しました。
- そもそもLooker StudioでSpannerを使うユースケースはあまり想定されていない
- これはSpannerの問題ではなく、Looker Studioの一般的な問題である可能性がある
- Spannerの仕組み上仕方ない制限の可能性がある
など、いろいろな要因が考えられますが、何かご存知の方はご指摘いただけるとうれしいです。
また、同じようなことでハマる人がいないように、この記事が少しでも役に立てば幸いです。
未検証ですが、BigQueryの外部クエリや外部データセットを使って、一度BigQueryを経由したデータソースにすると問題ないかもしれませんね。
Spanner 連携クエリ | BigQuery | Google Cloud
Spanner 外部データセットを作成する | BigQuery | Google Cloud