データ分析

Google スプレッドシートで複雑な CSV ファイルを BigQuery に読み込む

citrix

※この投稿は米国時間 2021 年 1 月 15 日に、Google Cloud blog に投稿されたものの抄訳です。

Google スプレッドシートを仲介して ELT パイプラインを作成する

BigQuery には、ウェブ ユーザー インターフェースとコマンドラインの両方から CSV ファイルを迅速にインポートする機能があります。

  bq load --source_format CSV --autodetect \
mydataset.mytable ./myfile.csv0

自動検出とインポートの制限事項

上記の方法は簡素な CSV ファイルでは機能しますが、複雑な CSV ファイルでは失敗する可能性があります。失敗するファイルの例として、Kaggle で提供されている Airbnb によるニューヨーク市の宿泊データのデータセットを取り上げます。このデータセットには列が 16 個ありますが、列の 1 つは大部分が自由形式のテキストで構成されています。つまり、絵文字や改行文字などが含まれている可能性があります…

new characters

このファイルを BigQuery で実際に開いてみましょう。

  bq load --source_format CSV --skip_leading_rows 1 \
   advdata.airbnb_nyc_2019 AB_NYC_2019.csv \
id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integer

次のようにエラーが表示されます。

  Error while reading data, error message: Error detected while
parsing row starting at position: 104942. Error: Missing close
double quote (") character.

これは、1 つの行が改行文字により複数の行(ライン)に分けられるので、1 行の開始引用符が閉じられないためです。この問題を解決するのは容易ではありません。多くのツールは、セルの中に改行が含まれる CSV ファイルをうまく処理できません

スプレッドシートによる前処理

一方、Google スプレッドシートは、はるかに優れた CSV インポート メカニズムを備えています。Google スプレッドシートを開いて、CSV ファイルをインポートすれば完了です。

sheets

この処理で素晴らしいのは、Google スプレッドシートを使用すると、インタラクティブにデータを準備してから BigQuery に読み込める点です。

まず、スプレッドシートから最初の行(ヘッダー)を削除します。データ処理には不要だからです。

Google スプレッドシートでの ELT

Google スプレッドシートにデータをインポートすると、BigQuery から Google スプレッドシートに直接クエリを実行するという便利な使い方ができます。そのためには、Google スプレッドシートを BigQuery のテーブルとして定義します。

create_table_sheets

BigQuery UI での手順

  • データセットを選択し、[テーブルを作成] をクリックする

  • ソースとして [ドライブ] を選択し、Google スプレッドシートにドライブの URL を指定する

  • ファイル形式として [Google スプレッドシート] を設定する

  • テーブルに名前をつける(今回は「airbnb_raw_googlesheet」と入力)

  • スキーマを指定する

  id:string,name:string,host_id:string,host_name:string,neighbourhood_group:string,neighbourhood:string,latitude:float,longitude:float,room_type:string,price:float,minimum_nights:integer,number_of_reviews:integer,last_review:string,reviews_per_month:float,calculated_host_listings_count:integer,availability_365:integer

このテーブルはスプレッドシートからデータをコピーすることなく、スプレッドシートに対してリアルタイムでクエリを実行します。

ですから、データをそのまま BigQuery にコピーしましょう(もちろん、変換もしてもかまいません)。

  CREATE OR REPLACE TABLE advdata.airbnb_nyc_2019 AS
SELECT * FROM advdata.airbnb_raw_googlesheet

自動化する方法

上記の手順は自動化が可能です。

  1. Python を使用して CSV ファイルをスプレッドシートに読み込む方法については、こちらの記事をご覧ください。

  2. それから、dataform.co または BigQuery スクリプトを使用して BigQuery テーブルを定義し、ELT を実行します。

複雑な CSV ファイルを BigQuery にインポートするには、Google スプレッドシートを仲介して ELT パイプラインを作成します。これにより、列に改行文字やその他の特殊文字が含まれる CSV ファイルを処理できます。ぜひお試しください。

-Google Cloud 分析および AI ソリューション部門責任者 Lak Lakshmanan