Metabase x Bigqueryを連携で java.util.ArrayList cannot be cast to java.lang.String エラーを解消

背景

5月から働いている会社では、 firebase x reactx typescript を利用してサービスの開発を行っています。

またマーケ等で使われるような BIツールとして metabase を使っていてデータの取り込みを Bigquery から行っています。

Firestore -> cloud storage -> Bigquery -> Metabase

リリース前に実際に使っていく中、、、気づく！

解決方法だけを見るときは下まで一気に進んでください :)

発覚

いくつかのデータテーブルが開くことが出来ない！

なんかエラーが出ているけどわからん。

java.util.ArrayList cannot be cast to java.lang.String

ということで、

ココらへんをポチポチしてなんでかを見て回ったら

どうにも Bigquery側で type: INTEGER x mode: REPEATED になっているとエラーになることがわかる。

簡単に言うと ARRAY 型のデータをMetabase側に渡すとエラーが発生することがわかる

１年前にも同じ問題が、、そして解消はされていない。
ということでいくつかやり方は有りましたが、取り急ぎすぐできそうなやつで対応していきます！

やり方候補

1. firebase functionsを使って データをbigqueryまで格納しているのでその間にfunctionsで int -> string に変更するようにする
2. BigqueryのViewで対応する

今回はもちろん手軽な 2 でいきます

解消方法手順

Bigquery側作業

1. Viewの作成 参考: ビューを作成する

変換方法１

対象のデータに対して ARRAY -> ARRAY に変換する

SELECT 
  ARRAY(
    SELECT 
      CAST(favoriteId AS string)
    FROM 
      UNNEST(favoriteIds) AS favoriteId
  ) AS favoriteIds,
FROM
  `project-name.project_database.table`

変換方法２

UDF(User Defined Functions) とやらを使えば View の中からでもファンクションを呼ぶことができるらしいと知ったので、ちょっとだけ挑戦してみた。
UDFでなくて TEMP functionだと View の中ではエラーになるので気をつけてください

UDFの作り方

CREATE OR REPLACE FUNCTION 
  `project-name.project_database.functionName`(intArr ARRAY<INT64>) 
RETURNS 
  ARRAY<STRING> 
  AS (
    ARRAY(
      SELECT CAST(value AS string) 
      FROM UNNEST(intArr) AS value
    )
  );

UDFの呼び方

SELECT 
  `project-name.project_database.functionName`(favoriteIds) AS favoriteIds
FROM
  `project-name.project_database.table`

Metabase側作業

Bigquery側でViewの作成が完了したら、あとはmetabaseだけなので簡単です!

1. Metabase Adminに入る
2. Database下のfirestoreを選択
3. Sync database schema now ＆Re-scan field values now をクリック

感想

まぁ今回は取り急ぎ感も出ましたが一応データをMetabaseで確認することができるという目的が達成できてよかったですが、直してみたい気持ちがあり見てみた Clojureで作られていた。

触ったことねーーーーー

ということで今回の記事は根本の解決策まで行きませんでしたが、最低限目的を達成することには何かしらの手助けができるのではないかと思います。

なにか他のいいやり方あったら教えて下さいーー