CSVファイルをBigQueryに取り込む際の注意点
はじめに
こんにちは。PHR事業開発部の山中です。
業務の中でBigQueryを使用することが何度かあったのですが、そのデータ移行作業中にデータの取り込みに失敗したパターンをまとめました。
スキーマの生成について
BigQueryのスキーマの生成方法は大まかに以下4パターンあります。
手動での指定
json形式のスキーマファイルの読み込み
プログラムから指定する
csv, jsonファイルからの自動検出
csvファイルはスキーマの自動検出が可能なのですが、自動検出でスキーマを生成すると想定したものにならなかったため、GCPのコンソールからjson形式でスキーマを指定しました。
json形式のスキーマを生成する際は、以下のようにExcel等のスプレッドシートで各項目を列挙し、スキーマファイル用のjsonの形式に沿って結合/生成すると手間が少なくスキーマを生成できます。
生成したE列の"["から"]"までを
BigQueryのテーブルを作成 -> スキーマのテキストとして編集
をオンにすると表示される欄にコピペしてテーブルを作成すると定義に沿ってテーブルを生成してくれます。
ただし、この方法だとNumeric型のprecisionやscaleの定義はできないようでした。そのため、対応していない指定値についてはテキストとして指定するのではなく手動で指定する必要がありそうです。また、nameの値については、英数字（a-z, A-Z, 0-9）とアンダースコア（_）のみ使用可能で名前の先頭に数字は使用できません。
文字コードについて
文字コードがUTF-8以外のcsvファイルをBigQueryに読み込むと、文字化けを起こしてしまうため、取り込む対象は事前にUTF-8に変換しておく必要があります。
日付について
BigQuery内の日付は以下の形式である必要があります。
DATE: 'YYYY-[M]M-[D]D'
ex. 2022-8-24, 2022-08-24
DATETIME: 'YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.SSSSSS]]'
ex. 2022-08-24 19:47:32.483716
// YYYY: 4桁で年を指定
// [M]M: 1桁または2桁で月を指定
// [D]D: 1桁または2桁で日を指定
// ( |T): スペースまたはTを指定
// [H]H: 1桁または2桁で時間を指定
// [M]M: 1桁または2桁で分を指定
// [S]S: 1桁または2桁で秒を指定
// [.SSSSSS]: 小数第6位まで秒を指定Nullと空文字
BigQueryに取り込むcsvファイルの空文字とNullの指定は以下となります。
ダブルクォーテーションで囲まれている場合(ex. ,"",)は空文字
ダブルクォーテーションで囲まれていない場合(ex. ,,)はNull
取込み等の制限について
BigQueryへのデータの読み込みについて制限があります。
さいごに
csvファイルからBigQueryへの取り込みにを行う中で引っかかった部分をまとめてみました。内容とは関係ないですが、スキーマの生成や定義の管理作業を行う中でExcelは便利だなぁと改めて感じました。