case-kの備忘録

日々の備忘録です。最近はGCPやデータ分析系のことを呟きます

【GCP入門】Google BigQuery データセット作成からデータ抽出までハンズオン形式で行います

今回はGCPが提供するGoogle 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

f:id:casekblog:20180926212534p:plain

集計関数

集計関数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

f:id:casekblog:20180926212742p:plain


例 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

f:id:casekblog:20180923122001p:plain

条件付き関数

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

f:id:casekblog:20180923122254p:plain

文字列の扱い

クエリ上で文字列の操作を行いたいと思います。
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

f:id:casekblog:20180923124135p:plain

日付による結合

結合文を書きたいと思います。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

f:id:casekblog:20180923125211p:plain

サブクエリ

サブクエリで抽出し、抽出した結果を他のクエリで活用したいと思います。

例 雨の日の飛行機の合計と遅延数と遅延率を抽出して下さい。

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

f:id:casekblog:20180923125529p:plain:w500

雨の日は全体の40%ほど遅延していることが分かります。

データのインポート

BigQueryにデータを読み込みたいと思います。GUICLICSVJSONファイルを読み込みます。

GUI

ますはGUIでデータのインポートを行います。
最初に以下のURLより対象データをDLして下さい。
https://storage.googleapis.com/cloud-training/CPB200/BQ/lab4/airports.csv
「ナビゲーション メニュー」から「BigQuery」を選択し、プロジェクトID右にある「データセット作成」を選択して下さい。
f:id:casekblog:20180923162055p:plain:w500
「データセット作成」ダイアログの 「データセット ID」 に「flight_data」と入力し、「データセットの作成」 をクリックします。
f:id:casekblog:20180923162524p:plain
作成されたデータセット「flight_data」を選択し、「テーブル作成」をクリックして下さい。
f:id:casekblog:20180923162659p:plain:w500
先ほどダウンロードしたCSVファイルからテーブルを作成します。
以下の設定を行い、「テーブル作成」をクリックして下さい。

# ソースデータ:
・ファイルを選択:airports.csv
・ファイル形式: csv
# 抽出先テーブル:
・宛先データセット: flight_data
# スキーマ[フィールドの追加]:
STRING 型: IATA、AIRPORT、CITY、STATE、COUNTRY
FLOAT 型:LATITUDE、LONGITUDE
上記全てのモードを[REQUIRED]にします。
# 詳細オプション:
スキップするheader行:1
(airports.csv にはヘッダー行が 1 行含まれているため)

f:id:casekblog:20180923173824p:plain:w500
f:id:casekblog:20180923173914p:plain:w500

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 は、CSVJSON の 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

テーブルのエクスポート

実際の業務としてはテーブルのバックアップや移行作業コピーしたいケースがあるかと思います。
この章ではGUICLIでテーブルのエクスポート方法についてお伝えできればと思います。

データセット内のテーブルを選択し「エクスポート」をクリックして下さい。
GCPバケットパスを入力し、エクスポートします。
gs:///bq/airports2.csv
f:id:casekblog:20180926220525p:plain

続いて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