Google スプレッドシートで複雑な CSV ファイルを BigQuery に読み込む
![https://storage.googleapis.com/gweb-cloudblog-publish/images/citrix-1.max-700x700_THc3l8B.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/citrix-1.max-700x700_THc3l8B.png)
Google Cloud Japan Team
※この投稿は米国時間 2021 年 1 月 15 日に、Google Cloud blog に投稿されたものの抄訳です。
Google スプレッドシートを仲介して ELT パイプラインを作成する
BigQuery には、ウェブ ユーザー インターフェースとコマンドラインの両方から CSV ファイルを迅速にインポートする機能があります。
自動検出とインポートの制限事項
上記の方法は簡素な CSV ファイルでは機能しますが、複雑な CSV ファイルでは失敗する可能性があります。失敗するファイルの例として、Kaggle で提供されている Airbnb によるニューヨーク市の宿泊データのデータセットを取り上げます。このデータセットには列が 16 個ありますが、列の 1 つは大部分が自由形式のテキストで構成されています。つまり、絵文字や改行文字などが含まれている可能性があります…
![https://storage.googleapis.com/gweb-cloudblog-publish/images/new_characters.max-800x800_GpAmL4X.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/new_characters.max-800x800_GpAmL4X.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/new_characters.max-800x800_GpAmL4X.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/new_characters.max-800x800_GpAmL4X.png)
このファイルを BigQuery で実際に開いてみましょう。
次のようにエラーが表示されます。
これは、1 つの行が改行文字により複数の行(ライン)に分けられるので、1 行の開始引用符が閉じられないためです。この問題を解決するのは容易ではありません。多くのツールは、セルの中に改行が含まれる CSV ファイルをうまく処理できません。
スプレッドシートによる前処理
一方、Google スプレッドシートは、はるかに優れた CSV インポート メカニズムを備えています。Google スプレッドシートを開いて、CSV ファイルをインポートすれば完了です。
![https://storage.googleapis.com/gweb-cloudblog-publish/images/sheets.max-800x800.max-800x800_5rOFzMj.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/sheets.max-800x800.max-800x800_5rOFzMj.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/sheets.max-800x800.max-800x800_5rOFzMj.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/sheets.max-800x800.max-800x800_5rOFzMj.png)
この処理で素晴らしいのは、Google スプレッドシートを使用すると、インタラクティブにデータを準備してから BigQuery に読み込める点です。
まず、スプレッドシートから最初の行(ヘッダー)を削除します。データ処理には不要だからです。
Google スプレッドシートでの ELT
Google スプレッドシートにデータをインポートすると、BigQuery から Google スプレッドシートに直接クエリを実行するという便利な使い方ができます。そのためには、Google スプレッドシートを BigQuery のテーブルとして定義します。
![https://storage.googleapis.com/gweb-cloudblog-publish/images/create_table_sheets.max-800x800.max-800x800_YBfT1ms.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/create_table_sheets.max-800x800.max-800x800_YBfT1ms.png)
![https://storage.googleapis.com/gweb-cloudblog-publish/images/create_table_sheets.max-800x800.max-800x800_YBfT1ms.png](https://storage.googleapis.com/gweb-cloudblog-publish/images/create_table_sheets.max-800x800.max-800x800_YBfT1ms.png)
BigQuery UI での手順
データセットを選択し、[テーブルを作成] をクリックする
ソースとして [ドライブ] を選択し、Google スプレッドシートにドライブの URL を指定する
ファイル形式として [Google スプレッドシート] を設定する
テーブルに名前をつける(今回は「airbnb_raw_googlesheet」と入力)
スキーマを指定する
このテーブルはスプレッドシートからデータをコピーすることなく、スプレッドシートに対してリアルタイムでクエリを実行します。
ですから、データをそのまま BigQuery にコピーしましょう(もちろん、変換もしてもかまいません)。
自動化する方法
上記の手順は自動化が可能です。
Python を使用して CSV ファイルをスプレッドシートに読み込む方法については、こちらの記事をご覧ください。
それから、dataform.co または BigQuery スクリプトを使用して BigQuery テーブルを定義し、ELT を実行します。
複雑な CSV ファイルを BigQuery にインポートするには、Google スプレッドシートを仲介して ELT パイプラインを作成します。これにより、列に改行文字やその他の特殊文字が含まれる CSV ファイルを処理できます。ぜひお試しください。
-Google Cloud 分析および AI ソリューション部門責任者 Lak Lakshmanan