tl;dr

BigQueryのLOAD DATAが強化。タイムゾーン/日時形式の変換、複数NULL値の対応、CSVの列名マッチングがSQLだけで可能になりました。

BigQueryをお使いの皆さん、こんにちは！

BigQueryへデータを投入する際、ソースデータの形式が多種多様で頭を悩ませた経験はありませんか？これまでは、日時のフォーマットを変換したり、NULL値を整形したりするために、Dataflowなどで前処理パイプラインを組む必要があり、余計なコストと複雑さが課題でした。

そんな悩みを解決する、待望のアップデートのお知らせです！🎉

この度、BigQueryのデータロード機能を強化し、ユーザーの手間を大幅に削減する4つの新機能がリリースされました。これらの機能を活用すれば、これまで以上にスムーズかつ柔軟にデータをBigQueryに取り込めるようになります。

それでは、一つずつ見ていきましょう！

1. タイムゾーンのカスタムサポート (Custom Timezone Support)

これまで、LOAD DATAで日時データを読み込む際、タイムゾーンはUTC（協定世界時）として解釈されるのがデフォルトでした。そのため、日本時間（JST）など、特定のタイムゾーンのデータを正しく扱うには、一手間加える必要がありました。

今回のアップデートでtime_zoneオプションが追加され、ロード時にタイムゾーンを直接指定できるようになりました！これにより、手動での変換作業が不要になります。

✨ 使ってみよう

例えば、Asia/Tokyo（日本時間）として記録されたデータをロードする場合、以下のようにクエリを記述します。

SQL


LOAD DATA INTO mydataset.test_table
FROM FILES (
  uris = ['gs://mybucket/data.json'],
  format = 'JSON',
  time_zone = 'Asia/Tokyo'
);

time_zoneを指定しない場合以下のようにLoadされたデータが

time_zoneを'Asia/Tokyo'に指定してLoadすると以下のように変わります

2. タイムスタンプ形式のカスタムサポート (Custom Timestamp Support)

「このYYYY.MM.DD形式の日付、一回STRINGで読み込んでからパースしなきゃ…」なんて経験はありませんか？ BigQueryが標準でサポートする日時形式は限定的でした。

これからは、date_format、datetime_format、time_format、timestamp_formatといった新しいオプションを使って、独自フォーマットの日時データを直接、かつ正確に読み込めます。

✨ 使ってみよう

以下のような、ドット区切りの日時データを含むJSONファイルを考えます。

JSON


{"col_int":"1","col_date":"2024.01.01","col_datetime":"2024.01.01 01.00.00","col_time":"01.00.00","col_timestamp":"2024.01.01 01.00.00-08"}
{"col_int":"2","col_date":"2024.01.02","col_datetime":"2024.01.02 02.00.00","col_time":"02.00.00","col_timestamp":"2024.01.02 02.00.00-08"}

ロード用クエリ:

SQL


LOAD DATA INTO samples.test_table
FROM FILES (
  uris = ['gs://loadsql/test.json'],
  format = 'JSON',
  date_format = "YYYY.MM.DD",
  datetime_format = "YYYY.MM.DD HH24.MI.SS",
  time_format = "HH24.MI.SS",
  timestamp_format = "YYYY.MM.DD HH24.MI.SSTZH"
);

ロード後のテーブル:

このクエリにより、各文字列が正しく日時型にパースされてテーブルに格納されます。col_timestampの-08タイムゾーンも解釈され、UTCに変換されていることがわかります。

col_datetime	col_timestamp	col_time	col_date	col_int
2024-01-01 01:00:00	2024-01-01 09:00:00+00	01:00:00	2024-01-01	1
2024-01-02 02:00:00	2024-01-02 10:00:00+00	02:00:00	2024-01-02	2

3. 複数のNULLマーカー指定 (Multiple Null Markers)

データソースによって、NULL値を表す文字列は"null"だったり、"N/A"だったり、あるいは空文字だったりと様々です。これまでは、null_markerオプションで指定できるNULL値の表現は1つだけでした。

今回のアップデートで、null_markersオプションが配列を受け付けるようになり、複数の文字列を一度にNULLとして扱うことができるようになりました。

✨ 使ってみよう

例えば、"null"と"NA"という2つの文字列をNULLとして扱いたいCSVファイルがあるとします。

ソースデータ (gs://loadsql/test.csv):


id,name,comment
1,Tanaka,first
2,Sato,null
3,Suzuki,NA

ロード用クエリ:

SQL


LOAD DATA INTO samples.test_table (id INT64, name STRING, comment STRING)
FROM FILES (
  uris = ['gs://loadsql/test.csv'],
  format = "CSV",
  skip_leading_rows = 1,
  null_markers = ['null', 'NA']
);

これにより、comment列の"null"と"NA"の両方が、テーブル内ではNULL値として正しく格納されます。

4. CSVヘッダーによる列名の自動マッチング (Source Column Matching)

これは多くの方が待ち望んでいた機能ではないでしょうか？💡

CSVファイルをロードする際、特に列がすべてSTRING型の場合に、autodetectがヘッダー行を列名として正しく解釈してくれない問題がありました。また、ロード先テーブルとCSVファイルの列の順序が異なると、ロードに失敗するケースもありました。

新オプションsource_column_match = "NAME"は、この問題を一挙に解決します。このオプションを指定すると、BigQueryはCSVのヘッダー行の名前を元に、ロード先テーブルの列名と自動でマッチングしてくれます。列の順序がバラバラでも問題ありません！

✨ 使ってみよう

ターゲットテーブルのスキーマが(id INT64, name STRING, status STRING)であるとします。一方、ロードしたいCSVは列の順番が異なっています。

ソースデータ (gs://loadsql/test.csv):


name,status,id
Alice,active,1
Bob,inactive,2

ロード用クエリ:

SQL


LOAD DATA INTO samples.test_table
FROM FILES (
  uris = ["gs://loadsql/test.csv"],
  format = "CSV",
  skip_leading_rows = 1,
  source_column_match = "NAME"
);

このクエリを実行すれば、BigQueryがヘッダーのname, status, idを賢く読み取り、テーブルの正しい列にデータを投入してくれます。もう列の順番を揃えるためにファイルを修正する必要はありません！

まとめ

今回ご紹介した4つの新機能は、BigQueryへのデータロードにおける「かゆいところ」に手が届く、非常にパワフルなアップデートです。

カスタムタイムゾーン: 面倒な時差計算からの解放
カスタムタイムスタンプ: 多様な日時フォーマットにネイティブ対応
複数NULLマーカー: データクレンジングの手間を削減
列名マッチング: CSVの列順序を気にせずロード可能に

これまでデータ投入の前処理に苦労していた方は、ぜひこの機会にお試しください。データロードのパイプラインが、きっとシンプルで堅牢なものになるはずです！

Discussion