本記事ではSQL入門としてBigQueryのセットアップを行います。SQLとはStructured Query Languageの頭文字で、リレーショナルデータベースを操作するための言語です。その歴史は古く誕生から約50年にもなります。「データベースを操作するための言語」と考えるとよいでしょう。世の中にはいろいろなデータベースシステムが存在します。MySQL, Oracle, PostgreSQL, SQLiteなどが有名です。それぞれ、開発元がことなるため細かい点では文法の差異がありますが、主な使い方は共通しています。一つに習熟すれば、他のシステムで利用することも容易になります。本書ではクラウド上のデータベースであるBigQueryを使用します。BigQueryはGooglerが開発した、膨大なデータを高速に処理できるリレーショナルデータベースです。
ちなみに、リレーショナルとは関係という意味ですが、行と列からなる構造のデータから構成されます。Excelなどの表計算アプリをイメージしていただくとよいでしょう。
テーブルは行と列から構成されます。行のことをレコード、列のことをカラムと呼ぶこともあります。テーブルの各列のデータなどの規定をスキーマと呼びます。このスキーマを参照することで、各列のデータ型や制約（とりうる値）などを確認することができます。
本投稿はオリジナル教材からの引用です。
目次
BigQuery のセットアップ
BigQueryを使用するにはまずデータが必要です。データをセットアップするところからはじめましょう。BigQuery では毎月最初の10GBのストレージと、1TBのクエリ処理は無料になります。今回は以下のようなデータを用意します。
people.csv ランダムに人のデータを生成するAPIです。ここから名前・性別・メール・誕生日などをランダムに生成しました。
squirrel census NYCでのリスの観察結果のデータセットです。以下のURLで無料で提供されています。
https://www.thesquirrelcensus.com/data
BigQueryにおける階層構造
BigQueryでは、プロジェクト / データセット / テーブル といった階層を作ってデータを管理します。
- プロジェクト = GCPのリソースを管理する単位、BigQueryはプロジェクトに含まれるリソースという位置づけ
- データセット = テーブルをまとめて管理するフォルダ
- テーブル = 実際のデータ
プロジェクトの作成
以下のURLからGCPのコンソールにアクセスして、新規プロジェクトを作成します。
https://console.cloud.google.com/
sql-studyという名前のプロジェクトを作成します（名前は任意で構いません）。
画面左上隅にあるプロジェクト選択用のプルダウンメニューでsql-studyが選択されていることを確認し、画面左上のメニューからBigQuery、SQLワークスペースと選択します。
もし「APIを有効化する」という旨のメッセージが表示された場合は、有効化ボタンを押下してください。
データセットの作成
プロジェクト名の横の「アクションを表示」から「データセットを作成」をクリックします。管理しやすい名前をつけて下さい。１つのプロジェクトで複数のデータセットを作成することができます。
以下の画面データセットを作成します。
- データセットID = データセット名を指定します。今回は「sample」としました。
- ロケーションタイプ = リージョンかマルチリージョンを選択します。重要なデータは複数地点で管理するマルチリージョンを選ぶのが良いのですが、今回は学習用なのでリージョンを選択し、「asia-northeast1(東京)」を選びます。
データセットを作成ボタンを押下してデータセットを作成します。作成したデータセットはいつでも削除できます。
テーブルの作成
では、データセットにデータを追加してゆきましょう。この操作を行うことでテーブルが作成されます。エクスプローラの「その他の操作」メニューから、「データを追加」を選択します。
データを追加画面から「ローカルファイル」を選択します。
ファイルを選択します。データセット任意のフォルダを選択します。ここでは先ほど作成した「sample」を選択しました。テーブル名はファイル名と同じにしました。自動検出をチェックして「テーブルを作成」をクリックしてテーブルを作成します。自動検出をオフにした場合、自分で各列のデータ型を明示的に指定することも可能です。
テーブルが作成され、以下のように表示されます。デフォルトで「スキーマ」（＝テーブルの構造：各列の名前とデータ型）が表示されています。内容を確認してください。
同じ手順で以下のファイルのテーブルを作成してください。
- park-data.csv
- squirrel-data.csv
- people.csv
- countries.csv
storiesはCSV形式のまま読み込むとエラーになってしまったので、Pandasを使用して一旦parquet形式に変換してから読み込みました。
import pandas as pd
df = pd.read_csv("stories.csv")
df.to_parquet("stories.parquet")以下のように5つのテーブルがある状態にしてください。
一般公開データの閲覧
BigQueryには一般公開データ(public-data)が用意されています。ここではそれらデータの使い方を簡単に説明します。詳しい手順は以下のURLを参照してください。
https://cloud.google.com/bigquery/docs/quickstarts/query-public-dataset-console?hl=ja
データの読み込み
[検索語句を入力] フィールドに 「bigquery-public-data」 と入力して、データセットを検索します。その中にある usa_names というテーブルを使ってみましょう。1910年から2013年までの米国での名前の情報を管理するテーブルです。
検索してもデータセットが表示されないことがあります。もしデータが見つからない場合は、「すべてのリソースを表示します」や「検索範囲をすべてのプロジェクトに広げます」をクリックしてください。また、キーワードを「public」に変更することも試してください。”bigquery-public-data” でデータセットが表示されないときは、”usa_names” で検索すると表示されるかもしれません。いずれにせよ、以下のように “usa_names/usa_1910_2013” が表示される状態にしてください。
「クエリを新規作成」をクリックして、以下のSQLを入力します。
SELECT
name, gender, SUM(number) AS total
FROM
`bigquery-public-data.usa_names.usa_1910_2013`
GROUP BY
name, gender
ORDER BY
total DESC
LIMIT
10画面右上に「このクエリを実行すると、99.95MBが処理されます」と概要が表示されています。この数値によって課金が変わってくるので注意してください。
実行ボタンを押下するとSQLが実行され、結果が表示されます。男性の名前の多い順からトップ10が表示されます。
James, John, Robert, Micheal, …と続いていることが分かります。
ちなみに、SQLは途中で自由に改行することができます。展開メニューの「クエリを書式設定」を選択すると、SQLを自動でフォーマットし、読みやすい形に整形されます。