BigQueryでJSONLのデータを読み込む外部テーブルを作ってみた
データアナリティクス事業本部の鈴木です。
BigQueryの外部テーブルで、JSONLのデータを読み込みたいときがありました。
Cloud Storage 外部テーブルを作成するのガイドから、JSON（改行区切り）の形式のCloud Storageデータのクエリがサポートされていることが記載されています。
実際のサンプルを見たことがなかったのと、外部テーブルの定義に記載するJSONLのスキーマの決定が難しそうな印象があったので、ガイドに記載のサンプルデータで試してみました。
データの作成
以下のCloud Storage からの JSON データの読み込みのガイドに記載のデータを利用しました。
以下のファイルを作成しました。
{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}
{"id":"2","first_name":"Jane","last_name":"Doe","dob":"1980-10-16","addresses":[{"status":"current","address":"789 Any Avenue","city":"New York","state":"NY","zip":"33333","numberOfYears":"2"},{"status":"previous","address":"321 Main Street","city":"Hoboken","state":"NJ","zip":"44444","numberOfYears":"3"}]}
ファイルはGoogle Cloud Storageのバケットにアップロードしておきました。
外部テーブルの作成
2通りの外部テーブルの作成方法を考えてみたのでご紹介します。
スキーマ自動検出を使う場合
スキーマ自動検出を使うと、ややこしいJSONLのスキーマをDDLに書かなくてもよいので非常に便利です。
スキーマ自動検出の機能については、スキーマの自動検出の使用のガイドに説明があります。
スキーマ自動検出を使った場合の定義は以下のようになります。
-- データを配置したバケットは自分の環境のものに差し替えてください。 CREATE EXTERNAL TABLE `sample_dataset.sample-jsonl-table` OPTIONS( format="NEWLINE_DELIMITED_JSON", uris=["gs://データを配置したバケット/samle-jsonl/data.jsonl"] );
以下のように検索できました。
非常に便利なのですが、スキーマの推測はクエリ実行の度に行われると思われ、JSONLスキーマ変更の変更を吸収して上手く動いてしまう可能性があります。Google Cloud Storageとのインターフェースの仕様が定まらないというのはリスクもあり、後続で特定のスキーマを前提としたELT処理が控えている場合には注意が必要そうです。
例えば、ある日突然処理でエラーが発生したものの処理内容自体に不備はなく、よくよく調べてみると外部テーブルで読み取っているデータのスキーマ変更が原因だったということが起こるかもしれません。
スキーマ自動検出を使わない場合
CREATE EXTERNAL TABLE句で手動でスキーマを定義することもできます。
スキーマが手で作成できるくらい簡単な場合は良いのですが、非常に複雑な場合は、ローカルからサンプルデータを取り込んでテーブルを作ることで、スキーマ自動検出を使ってスキーマの特定ができました。
まず、テーブルを作成していい適当なデータセットで、テーブルを作成を押しました。
JSONLファイル形式を指定し、自動検出にチェックを入れて、アップロードしたファイルからテーブルを作成しました。
このようにテーブルが作成されました。
INFORMATION_SCHEMAからCREATE TABLE 文が取得できるため、取得した定義からスキーマ情報を抜き出して外部テーブルの定義に移し替えます。
以下のようにSQLを実行しました。
SELECT table_name, ddl FROM sample_dataset.INFORMATION_SCHEMA.TABLES WHERE table_name = 'upload_jsonl_table'
取得したDDLは例えば以下のようになっていました。
-- プロジェクト名は差し替えています。 CREATE TABLE `プロジェクト名.sample_dataset.upload_jsonl_table` ( addresses ARRAY<STRUCT<numberOfYears INT64, state STRING, city STRING, zip INT64, address STRING, status STRING>>, first_name STRING, dob DATE, last_name STRING, id INT64 );
スキーマ部分を外部テーブルの定義に差し込んで、以下のようにテーブルを作成しました。
-- データを配置したバケットは自分の環境のものに差し替えてください。 CREATE EXTERNAL TABLE `sample_dataset.sample-jsonl-table` ( addresses ARRAY<STRUCT<numberOfYears INT64, state STRING, city STRING, zip INT64, address STRING, status STRING>>, first_name STRING, dob DATE, last_name STRING, id INT64 ) OPTIONS( format="NEWLINE_DELIMITED_JSON", uris=["gs://データを配置したバケット/samle-jsonl/data.jsonl"] );
作成した外部テーブルから、以下のようにデータを検索できました。
この方法だと、外部テーブルでスキーマを決めているため、後続のバッチジョブなどが何かあったとしてもスキーマ変更の影響を受けにくいと考えられます。
最後に
BigQueryの外部テーブルで、JSONLのデータを読み込む際に、スキーマ自動検出の使用有無のそれぞれで、DDLについて例をご紹介しました。
特にJSONLファイルなど、外部テーブルのスキーマ決定になにかしら難しさがあるような場合に、参考になりましたら幸いです。
