BigQueryでJSONLのデータを読み込む外部テーブルを作ってみた

BigQueryでJSONLのデータを読み込む外部テーブルを作る際、スキーマ自動検出でスキーマを決めてから、そのスキーマで外部テーブルを作ると楽でした。

#Google Cloud (GCP)

#Google BigQuery

nayu.t.s

2023.07.22

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

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"]
);

以下のように検索できました。

クエリ結果1

非常に便利なのですが、スキーマの推測はクエリ実行の度に行われると思われ、JSONLスキーマ変更の変更を吸収して上手く動いてしまう可能性があります。Google Cloud Storageとのインターフェースの仕様が定まらないというのはリスクもあり、後続で特定のスキーマを前提としたELT処理が控えている場合には注意が必要そうです。

例えば、ある日突然処理でエラーが発生したものの処理内容自体に不備はなく、よくよく調べてみると外部テーブルで読み取っているデータのスキーマ変更が原因だったということが起こるかもしれません。

スキーマ自動検出を使わない場合

CREATE EXTERNAL TABLE句で手動でスキーマを定義することもできます。

Data definition language (DDL) statements in GoogleSQL CREATE EXTERNAL TABLE statement | BigQuery | Google Cloud

スキーマが手で作成できるくらい簡単な場合は良いのですが、非常に複雑な場合は、ローカルからサンプルデータを取り込んでテーブルを作ることで、スキーマ自動検出を使ってスキーマの特定ができました。

まず、テーブルを作成していい適当なデータセットで、テーブルを作成を押しました。

テーブルを作成を開く

JSONLファイル形式を指定し、自動検出にチェックを入れて、アップロードしたファイルからテーブルを作成しました。

テーブルの作成

このようにテーブルが作成されました。

作成されたテーブル

INFORMATION_SCHEMAからCREATE TABLE 文が取得できるため、取得した定義からスキーマ情報を抜き出して外部テーブルの定義に移し替えます。

以下のようにSQLを実行しました。

SELECT
  table_name, ddl
FROM
  sample_dataset.INFORMATION_SCHEMA.TABLES
 WHERE
  table_name = 'upload_jsonl_table'

DDLの取得

　取得した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"]
);

作成した外部テーブルから、以下のようにデータを検索できました。

スキーマを指定して取得したデータ