今回はGCPが提供するGoogle BIgQueryに関する記事を書きます。データ読み込み・出力方法やクエリ検索まで備忘録も兼ねて記事を書きます。
- この記事の目的
- BigQueryとは
- BigQueryのメリット・デメリット
- クエリ備忘録
- 集計関数
- 条件付き関数
- 文字列の扱い
- 日付による結合
- サブクエリ
- データのインポート
- テーブルのエクスポート
この記事の目的
・BigQueryの特徴や優れている点を理解すること
・クエリを発行し、動かすことで理解を深めること
・データのインポート・エクスポートの方法を理解すること
BigQueryとは
BigQuery は、Google が2012年から提供しているサーバーレスでスケーラビリティに優れたエンタープライズ向けデータウェアハウスです。
BigQueryはレコード思考のストレージであるRDBとは異なり、カラム型のストレージです。つまり、データを列で保持しています。列で管理することでデータ型を統一させることが可能となり、効率的に圧縮させ、ファイルサイズ小さくすることができます。また、RDBのようにレコード型ではないので、インデックスも不要です。クエリを発行するとBigQueryは数千のワーカーを立ち上げ、並列分散処理を行うため大規模データを高速に処理することができます。
BigQueryのメリット・デメリット
BigQueryのメリットとデメリットについてまとめてみました。
メリット
- 高速処理の実現
BigQuery はペタバイト規模のアナリティクス データベースです。BigQuery は、ギガバイトからペタバイト規模のデータに対して SQL クエリを超高速で実行します。数百件のフルスキャンを数十秒で完了させることができます。クエリを実行すると何千ものワーカーを起動し並列処理を行います。
- コスト
データストレージは低コストで、クエリはデータの処理に応じて課金されます。
・ストレージ保持費用
・クエリ実行費用
つまり、利用した分だけ課金される仕組みです。他のDWHプロバイダでは実現できない低価格で提供されています。
- フルマネージドシステム
クラウドで管理しているのでオンプレ環境のインスタンスを構築する必要はありません。オペレーション担当者も不要です。
- テーブル定義がシンプル
後にテーブル定義を行う際にデータ型を定義しますが、非常にシンプルです。一般的にテーブル設計を行う場合、データ型をCHARやVARを指定する必要がありますが、BigQueryはシンプルに「String」で済みます。またカラム型なので、インデックスも不要です。
# データ型の種類 ・String ・Byte ・Integer ・Float ・Boolean ・Timestamp ・Recode # データモデルの種類 ・Nullable ・Required ・Repeated
デメリット
- データの更新処理
BigQueryはカラム型でデータを管理しているため、レコード型に比べ、データ更新が大変です。
クエリ備忘録
この章では備忘録も兼ねて、ハンズオン形式で実際にクエリを書き、データを検索したいと思います。ナビゲーションメニューより「BigQuery」を選択してください。BigQueryにはデータを格納しなくても活用できるサンプルが用意されています。今回サンプルを用いていくつか例題を解きたいと思います。
データ構造：bigquery-samples.airline_ontime_data.flights
カラムは以下となります。
{
"date": "2006-08-08",
"airline": "KH",
"airline_code": "19678",
"departure_airport": "HNL",
"departure_state": "HI",
"departure_lat": "21.31",
"departure_lon": "-157.92",
"arrival_airport": "KOA",
"arrival_state": "HI",
"arrival_lat": "19.73",
"arrival_lon": "-156.04",
"departure_schedule": "1725",
"departure_actual": "1716",
"departure_delay": "-9.0",
"arrival_schedule": "1806",
"arrival_actual": "1800",
"arrival_delay": "-6.0"
}
例 出発する空港は「LGA」で出発が遅延している飛行機の「airline,date,departure_delay」を抽出して下さい。
SELECT airline, date, departure_delay FROM `bigquery-samples.airline_ontime_data.flights` WHERE departure_delay > 0 AND departure_airport = 'LGA' LIMIT 100
集計関数
集計関数COUNTを使った例題です。
例 2008-05-13にLGA空港から出発する各航空会社のフライト合計数をフライト昇順で求めよ。
※ デフォルトは ASC （昇順）です
SELECT airline, COUNT(departure_delay) FROM `bigquery-samples.airline_ontime_data.flights` WHERE departure_airport = 'LGA' AND date = '2008-05-13' GROUP BY airlinef ORDER BY airline
例 2008-05-13にLGA空港から出発する各航空会社で主発が遅延したフライトの合計数を求めよ。
SELECT airline, COUNT(departure_delay) FROM `bigquery-samples.airline_ontime_data.flights` WHERE departure_delay > 0 AND departure_airport = 'LGA' AND date = '2008-05-13' GROUP BY airline ORDER BY airline
条件付き関数
IF関数を用いて、条件式を付与します。
例 フライトの合計数と遅延の合計数を取得する。
SELECT f.airline, COUNT(f.departure_delay) AS total_flights, SUM(IF(f.departure_delay > 0, 1, 0)) AS num_delayed FROM `bigquery-samples.airline_ontime_data.flights` AS f WHERE f.departure_airport = 'LGA' AND f.date = '2008-05-13' GROUP BY f.airline
文字列の扱い
クエリ上で文字列の操作を行いたいと思います。
BigQueryで提供されている以下のサンプルデータを活用したいと思います。
{
"station_number": "863700",
"wban_number": "99999",
"year": "1997",
"month": "10",
"day": "24",
"mean_temp": "67.6",
"num_mean_temp_samples": "4",
"mean_dew_point": null,
"num_mean_dew_point_samples": "0",
"mean_sealevel_pressure": null,
"num_mean_sealevel_pressure_samples": "0",
"mean_station_pressure": null,
"num_mean_station_pressure_samples": "0",
"mean_visibility": "6.8",
"num_mean_visibility_samples": "4",
"mean_wind_speed": "0.0",
"num_mean_wind_speed_samples": "4",
"max_sustained_wind_speed": "999.9",
"max_gust_wind_speed": null,
"max_temperature": "75.2",
"max_temperature_explicit": "false",
"min_temperature": "62.6",
"min_temperature_explicit": "false",
"total_precipitation": "0.0",
"snow_depth": null,
"fog": "false",
"rain": "false",
"snow": "false",
"hail": "false",
"thunder": "false",
"tornado": "false",
"country": "UY UY",
"state": " ",
"col": "SUTB ",
"lat": "-31700",
"long": "-55983",
"alt": "1340"
}
例 年月日を"-"で紐づけて抽出して下さい。
SELECT CONCAT(CAST(year AS STRING), '-', LPAD(CAST(month AS STRING),2,'0'), '-', LPAD(CAST(day AS STRING),2,'0')) AS rainyday FROM `bigquery-samples.weather_geo.gsod` WHERE station_number = 725030 AND total_precipitation > 0
日付による結合
結合文を書きたいと思います。BigQueryはテーブルのみならず、データセットが異なるデータに対しても結合させることができます。なおBigQueryはRDBとは異なりJOINは推奨していないようです。1つのテーブルで管理することを推奨しています。
例 異なるデータソース[bigquery-samples.airline_ontime_data.flights・bigquery-samples.weather_geo.gsod]を日付「yyyy-mm-dd」で結合して下さい。
SELECT f.airline, SUM(IF(f.arrival_delay > 0, 1, 0)) AS num_delayed, COUNT(f.arrival_delay) AS total_flights FROM `bigquery-samples.airline_ontime_data.flights` AS f JOIN ( SELECT CONCAT(CAST(year AS STRING), '-', LPAD(CAST(month AS STRING),2,'0'), '-', LPAD(CAST(day AS STRING),2,'0')) AS rainyday FROM `bigquery-samples.weather_geo.gsod` WHERE station_number = 725030 AND total_precipitation > 0) AS w ON w.rainyday = f.date WHERE f.arrival_airport = 'LGA' GROUP BY f.airline
サブクエリ
サブクエリで抽出し、抽出した結果を他のクエリで活用したいと思います。
例 雨の日の飛行機の合計と遅延数と遅延率を抽出して下さい。
SELECT airline, num_delayed, total_flights, num_delayed / total_flights AS frac_delayed FROM ( # 飛行機毎の合計と遅延数を抽出 SELECT f.airline AS airline, SUM(IF(f.arrival_delay > 0, 1, 0)) AS num_delayed, COUNT(f.arrival_delay) AS total_flights FROM `bigquery-samples.airline_ontime_data.flights` AS f JOIN ( # 雨の日付を抽出 SELECT CONCAT(CAST(year AS STRING), '-', LPAD(CAST(month AS STRING),2,'0'), '-', LPAD(CAST(day AS STRING),2,'0')) AS rainyday FROM `bigquery-samples.weather_geo.gsod` WHERE station_number = 725030 AND total_precipitation > 0) AS w ON # 日付で結合する w.rainyday = f.date WHERE f.arrival_airport = 'LGA' GROUP BY f.airline ) ORDER BY frac_delayed ASC
雨の日は全体の40%ほど遅延していることが分かります。
データのインポート
BigQueryにデータを読み込みたいと思います。GUIとCLIでCSVやJSONファイルを読み込みます。
GUI
ますはGUIでデータのインポートを行います。
最初に以下のURLより対象データをDLして下さい。
https://storage.googleapis.com/cloud-training/CPB200/BQ/lab4/airports.csv
「ナビゲーション メニュー」から「BigQuery」を選択し、プロジェクトID右にある「データセット作成」を選択して下さい。
「データセット作成」ダイアログの 「データセット ID」 に「flight_data」と入力し、「データセットの作成」 をクリックします。
作成されたデータセット「flight_data」を選択し、「テーブル作成」をクリックして下さい。
先ほどダウンロードしたCSVファイルからテーブルを作成します。
以下の設定を行い、「テーブル作成」をクリックして下さい。
# ソースデータ： ・ファイルを選択：airports.csv ・ファイル形式: csv # 抽出先テーブル： ・宛先データセット: flight_data # スキーマ[フィールドの追加]： STRING 型： IATA、AIRPORT、CITY、STATE、COUNTRY FLOAT 型：LATITUDE、LONGITUDE 上記全てのモードを[REQUIRED]にします。 # 詳細オプション： スキップするheader行：1 (airports.csv にはヘッダー行が 1 行含まれているため)
CLI
次はCLIでデータセットとテーブルの作成を行います。
まずは、データセットを作成し、テーブルにアップロードするCSVファイルを取得します。
Cloud Shellから以下のCURLコマンドでデータをDLしてください。
# CSBファイルのDL $ curl -O https://storage.googleapis.com/cloud-training/CPB200/BQ/lab4/airports.csv # Out % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 22687 100 22687 0 0 759k 0 --:--:-- --:--:-- --:--:-- 763k
次にデータセットを作成し、DLしたCSVファイルを使い、テーブルを作成します。
# データセットの作成 $ bq mk mydataset # Out Dataset 'buoyant-planet-217108:mydataset' successfully created. # テーブル作成 bq load [--source_format=NEWLINE_DELIMITED_JSON|CSV] destination_table data_source_uri table_schema
・destination_table：
作成するテーブルの完全修飾名。
・--source_format：
BigQuery は、CSV と JSON の 2 種類のファイルを受け付けます。ファイルタイプを明示的に指定しない場合、BigQuery はデフォルトでファイルを CSV として扱います。JSON ファイルをアップロードする場合には、--source_format=NEWLINE_DELIMITED_JSON フラグを指定しなければなりません。ソースファイルとスキーマも、JSON の構造に従っていなければなりません。
・data_source_uri：
テーブルにデータを供給するソース CSV ファイルです。
・table_schema：
テーブルのスキーマです。事前にデータ型など定義してあげる必要があります。
まずはスキーマを定義したファイルを作成します。
# スキーマを定義 $ vi schema.json # 編集 [esc + i] [{"name": "IATA", "type": "string", "mode": "required"}, {"name": "AIRPORT", "type": "string", "mode": "required"}, {"name": "CITY", "type": "string", "mode": "required"}, {"name": "STATE", "type": "string", "mode": "required"}, {"name": "COUNTRY", "type": "string", "mode": "required"}, {"name": "LATITUDE", "type": "float", "mode": "required"} {"name": "LONGITUDE", "type": "float", "mode": "required"}] # 保存&終了 [esc + :wq]
スキーマを定義できたので、BigQueryにアップロードします。header行1行目は飛ばしてください。
bq load --skip_leading_rows=1 --source_format=CSV $DEVSHELL_PROJECT_ID:mydataset.airline ./airports.csv ./s chema.json Upload complete. Waiting on bqjob_r432f08854c34847c_000001660b4237f5_1 ... (0s) Current status: DONE
次にJSONファイルをアップロードしたいと思います。
# データDL & GCS保存 $ curl https://storage.googleapis.com/cloud-training/CPB200/BQ/lab4/schema_flight_performance.json -o schema_ flight_performance.json # Out % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 2360 100 2360 0 0 71344 0 --:--:-- --:--:-- --:--:-- 71515
次にDLしたデータを使いテーブルを作成します。
# テーブル作成 $ bq load --source_format=NEWLINE_DELIMITED_JSON $DEVSHELL_PROJECT_ID:mydataset.flights_2014 gs://cloud-training/CPB200/BQ/lab4/domestic_2014_flights_*.json ./schema_flight_performance.json Waiting on bqjob_r4d3f0f43e56a1d64_000001660b4b4aaa_1 ... (50s) Current status: DONE
無事アップロードできたか データセットの中身を確認してみます。
$ bq ls $DEVSHELL_PROJECT_ID:mydataset # Out tableId Type Labels Time Partitioning -------------- ------- -------- ------------------- airline TABLE flights_2014 TABLE
テーブルのエクスポート
実際の業務としてはテーブルのバックアップや移行作業コピーしたいケースがあるかと思います。
この章ではGUIとCLIでテーブルのエクスポート方法についてお伝えできればと思います。
データセット内のテーブルを選択し「エクスポート」をクリックして下さい。
GCPのバケットパスを入力し、エクスポートします。
gs://
続いてCLIでデータのエクスポートを行います。
$ bq extract mydataset.airline gs://<your-bucket-name>/bq/airports3.csv # Out Waiting on bqjob_rf4557e99c92e0e52d_000001660b549901_1 ... (0s) Current status: DONE # GCS内にファイルがアップロードされたか確認 $ gsutil ls gs://dataproc-c66ce4c8-6a0e-44d9-aa9f-3602459af6f6-us/bq # Out gs://dataproc-c66ce4c8-6a0e-44d9-aa9f-3602459af6f6-us/bq/airports2.csv gs://dataproc-c66ce4c8-6a0e-44d9-aa9f-3602459af6f6-us/bq/airports3.csv
参考：
https://cloud.google.com/bigquery/bq-command-line-tool-quickstart?hl=ja
BigQuery - アナリティクス データ ウェアハウス | BigQuery | Google Cloud
【GCP入門編・第12回】 BigQuery を使って気軽にビッグデータの解析を行ってみよう！ | 株式会社トップゲート
Google BigQueryは「速い・安い・シンプル」の3拍子揃ったビッグデータ処理サービス ～3大クラウドサービス比較～ - GiXo Ltd.
https://cloud.google.com/bigquery/query-reference#orderby
