JSON を BigQuery に読み込む方法のご紹介
Google Cloud Japan Team
※この投稿は米国時間 2023 年 10 月 7 日に、Google Cloud blog に投稿されたものの抄訳です。
問題点
JSON ファイルを BigQuery に読み込もうとしたときに、ファイルが BigQuery で想定されている適切な改行区切り形式（各行が配列ではなくオブジェクト）になっておらず読み込めなかったという経験はございませんか。そうした場合でも、BigQuery だけを使用して（他のツールを必要とせずに）、ほんの少し工夫すればファイルを読み込むことができる場合があります。
以下に、ファイル形式を JSON として指定した場合（例: sourceFormat=NEWLINE_DELIMITED_JSON）に、BigQuery に読み込まれない JSON 形式の例を 2 つ示します。
- 改行区切りの JSON 配列:
以下は有効な改行区切りの JSON 形式ですが、ソース形式を JSON として指定した場合、各行は配列ではなく JSON オブジェクトである必要があるため、BigQuery に読み込まれません。
- プリティ プリントされた JSON オブジェクト:
以下は有効な JSON 形式ですが、改行区切りの JSON オブジェクトではなくプリティ プリントされた JSON オブジェクトであるため、ソース形式を JSON として指定した場合は BigQuery に読み込まれません。
解決策
では、上記の JSON の例をエラーにならないように読み込むにはどうすればよいでしょうか。
そのためには、JSON ファイルデータ全体を 1 列の CSV ファイル（例: sourceFormat=CSV）として読み込んで、BigQuery のネイティブの JSON 関数を使用して、データを適切な JSON データ型に解析します。以降のセクションでは、この方法について詳しく説明します。
注: BigQuery の行サイズは 100 MB までに制限されています。この読み込み手法では JSON ファイルのすべてのデータを 1 列の行に読み込むため、JSON ファイルのサイズが 100 MB を超えないようにしてください。
配列行を含む改行区切りの JSON の読み込み
1. 改行区切りの JSON 配列のファイルを Cloud Storage にアップロードする
- 「my_newline_json_arrays.json」という名前のファイルを作成し、以下に示すコンテンツをそのファイルにコピーします。
- ファイルを Cloud Storage バケットにコピーします。
2. Cloud Storage 内の JSON ファイルに外部テーブルを作成する
BigQuery コンソールで以下の SQL を実行して、Cloud Storage 内に作成した JSON ファイルに外部テーブルを作成します。
注: 以下の SQL 内のプレースホルダ（YOUR_PROJECT、YOUR_DATASET、YOUR_BUCKET）は、独自の値に置き換えてください。
3. 外部テーブルをクエリしてデータを読み込めることを確認する
プリティ プリントされた JSON ファイルの読み込み
1. プリティ プリントされた JSON を含むファイルを Cloud Storage にアップロードする
- 「my_pretty_printed_json_data.json」という名前のファイルを作成し、以下に示すコンテンツをそのファイルにコピーします。
- ファイルを Cloud Storage バケットにコピーします。
2. Cloud Storage 内の JSON ファイルに外部テーブルを作成する
BigQuery コンソールで以下の SQL を実行して、Cloud Storage 内に作成した JSON ファイルに外部テーブルを作成します。
注: 以下の SQL 内のプレースホルダ（YOUR_PROJECT、YOUR_DATASET、YOUR_BUCKET）は、独自の値に置き換えてください。
3. 外部テーブルをクエリしてデータを読み込めることを確認する
注: 次の SQL クエリにより、プリティ プリントされた JSON が 1 行に集約されます。そのため、JSON ファイルのサイズは、BigQuery の 1 行の上限である 100 MB 未満にする必要があります。
全体像
これで、すべての JSON データを簡単に読み込めるようになりました。読み込みが中断されて厄介な JSON ファイルを再フォーマットする方法を探すことがなくなり、データ分析情報を引き出すことに集中して取り組めるようになりました。また、データを探索する時間の節約に役立つさまざまな BigQuery の JSON 関数や、BigQuery で JSON データを操作するためのガイドも忘れずにご確認ください。この方法がお役に立てば幸いです。
ー Google Cloud、戦略的クラウド エンジニア Celia Ji
ー 戦略的クラウド エンジニア Daniel De Leo
