はじめての GCP ～ BigQuery にデータをロードしてみた～
こんにちは、みかみです。
弊社業務では、AWS をメインに使用しております。データアナリティクス事業本部に所属しているので、DWH で Redshift は非常によく使っています。
恥ずかしながら、GCP も BigQuery も触ったことがありません。
いまさらながら、BigQuery ってどんな感じ？ ということで、実際にさわってみました。
やりたいこと
BigQuery をさわってみたい！
BigQueryとは？
言わずと知れた、Google Cloud Platform の DWH。
スケーラブルでクエリ実行も超高速！ 大量データのアドホック分析などに非常に役立つようです。
もともと Google さん社内でデータ分析用途に使用していたシステム、Dremel を BigQuery として一般公開したそうで、カラムナストレージとツリーアーキテクチャが高速化実現のカギ。
Dremel Can Scan 35 Billion Rows Without an Index in Tens of Seconds Dremel, the cloud-powered massively parallel query service, shares Google’s infrastructure, so it can parallelize each query and run it on tens of thousands of servers simultaneously. You can see the economies of scale inherent in Dremel.
Index なしで350億行を数十秒でスキャンできるそうな。フルスキャンで速いって、すごいですねーｖ（チューニングはいらないってことかな＠＠？！
BigQueryっていくらかかるの？
個人的には、ひと昔前に話題になった 「BigQuery 破産」の記憶いまだ新しく。。 ちょっと怖いから、ちゃんと調べとこ。。
で、GCP の BigQuery 料金ページ読んでみましたが、何に対して課金されるのか、すごく親切に書かれてました！（ありがたいですｍｍ
※なお、以降の記載は 2019/10 時点のものですので、公式の料金ページをご参照ください。
BigQuery では、主に以下の料金が発生するようです。
- クエリ課金
- ストレージ課金
その他、もしBigQuery Storage API を使用する場合や、 Google Cloud Storage（GCS） にロードデータを置く場合には、別途料金がかかります。
クエリ課金
実行したクエリがスキャンするデータ量に対する課金。
クエリ毎に料金が発生するオンデマンド料金と、1か月 or 1年 毎の定額料金が選択できるとのこと。
オンデマンド料金
オンデマンド料金では、BigQuery は 1 つの指標「処理されたバイト数」（読み取りバイト数とも呼ばれる）に基づいてクエリの料金を課金します。
BigQuery ではカラム型データ構造が使用されています。選択した列で処理されたデータの合計容量に応じて課金されます。列ごとの合計データは、その列のデータ型に基づいて計算されます。データサイズの計算方法の詳細についてはデータサイズの計算をご覧ください。
- 引用：オンデマンド料金
カラムの型定義に従って、バイト単位での課金になるようです（テラバイトレベルの DWH でもバイト単位で課金って、誠実！＠＠ｖ
エラークエリに対しての課金はありませんが、クエリをキャンセルした場合は料金が発生する可能性があるそうです。
定額料金
東京リージョンで、月額 $12,000（500スロット）、年額 $10,200（500スロット） です。
「スロット」ってなんじゃ？（「500スロット」って、充分なの？
BigQuery スロットとは、SQL クエリの実行に必要な演算能力の単位です。BigQuery はクエリのサイズと複雑さに基づいて、クエリごとに必要なスロットの数を自動的に計算します。
プロジェクトごとのスロットの割り当てについては、クエリの割り当てポリシーをご覧ください。
ほとんどのユーザーにとって、デフォルトのスロットの容量は十分です。利用できるスロットの数が増えても、クエリごとのパフォーマンスが高速化するとは限りません。ただし、スロットプールが大きければ、非常に大規模で非常に複雑なクエリのパフォーマンスが向上するとともに、同時実行数の多いワークロードのパフォーマンスも向上します。
・オンデマンド料金のプロジェクトあたり同時実行最大スロット数 - 2,000
オンデマンド クエリに対するデフォルトの個数のスロットは、同じプロジェクトで実行するすべてのクエリで共有されます。一般的に、一度に処理するクエリの容量が 100 GB 未満であれば、2,000 個のスロットを使い切る可能性はほとんどありません。
スロット＝並列実行時のプロセス数的な？（とりま、普通に使う分には大丈夫っぽい？
ストレージ課金
BigQuery に保存しているデータ量に対する課金です。
過去 90 日間の変更有無によって、料金が異なります。
アクティブストレージ
過去 90 日間で変更のあったデータです。 東京リージョンで、1GBあたり$0.023 の料金が発生します。
長期保存
過去 90 日間に変更のなかったデータです。 東京リージョンで、1GBあたり$0.016 と、割安になります（うれしいですねｖ
無料利用枠
ストレージ課金で毎月 10 GB まで、オンデマンドのクエリ課金で毎月 1 TB まで、無料利用枠もあります！
また、BigQuery に限らず、GCP プロダクトを 1年間 $300 までは無料で利用可能なトライアルあり！ GCSにデータ置いて、BigQuery にデータロードしたい場合や、さらにGCE（Google Compute Engine）からスクリプト実行して BigQuery にデータをロードしたい場合など、無料でお試しできて素敵です！ｖ
ローカルPCから BigQuery にデータをロードしてみる
GCPアカウントとプロジェクトは作成済みの前提です。
BigQuery にデータを取り込むには、いろいろな方法があるようです。
まずは手っ取り早く、管理コンソール操作で、ローカルにあるCSVファイルデータを取り込んでみます。
以下のサイトから、都道府県コードのCSVファイルをいただいてきました。
ダウンロードしたのはSJISの方のファイルです。（SJISロードできるかな・・・？
まずはデータセットを作成します。 BigQuery のコンソールからプロジェクトを選択し、データセットの作成ボタンをクリック。
「データセットID」に pref_test と入力して「データのロケーション」プルダウンから「東京（asia-northeast1）」を選択したら「データセットを作成」ボタンをクリック。
続いてテーブルを作成します。
画面左側のナビゲーションパネルで先ほど作成したデータセット pref_test を選択すると表示される、テーブル作成ボタンをクリック。
「ソース」セクション「テーブルの作成元」で「アップロード」を選ぶと、ファイル選択できるようになり、CSVファイルの場合、ファイルを選択すると「ファイル形式」は自動で設定されました。
「送信先」セクション「テーブル名」に pref_code と入力。
「スキーマ」セクションの「自動検出」をチェック。
下にスクロールして「詳細オプション」を開きます。
ダウンロードしたデータにはヘッダがついていたので、「スキップするヘッダー行」に 1 を入力し、「テーブルを作成」ボタンをクリック。
左下の方に「ジョブ実行中」などのポップアップが表示された後、無事テーブルが作成されました。
「行数」も合ってるし、ちゃんとロードできているようです。
が、「プレビュー」タブを見てみると・・・
やはりSJISはサポートされていないようで。。
ドキュメントにもちゃんと書いてありました。（Latin-1 だったら、自動変換してくれるんですねーｖ
文字エンコード デフォルトでは、BigQuery サービスはすべてのソースデータが UTF-8 でエンコードされることを想定しています。CSV ファイルに ISO-8859-1 形式でエンコードされたデータが含まれる場合は、BigQuery がインポート処理中にデータを UTF-8 に正しく変換できるよう、データをインポートするときに明示的にエンコーディングを指定する必要があります。現時点では、ISO-8859-1 または UTF-8 でエンコードされたデータのみをインポートできます。
UTF8のCSVファイルをダウンロードしてきて、先ほどと同じ「テーブルを作成」から、同じテーブル名を入力しダウンロードしたUTF8のCSVファイルを選択。 「詳細オプション」の「書込み設定」で今度は「テーブルを上書きする」を選択してテーブル作成。
今度は漢字もちゃんとロードできました。
が、列名が思ってたのと違う。。（ヘッダ行が日本語だったからかな？
スキーマタブに「スキーマを編集」ボタンがあるので変更しようと思ったら、現在のところ、コンソールから既存の列名の変更はできないそうで。
列の名前の変更 列の名前の変更は、GCP Console、従来の BigQuery ウェブ UI、コマンドライン ツール、API ではサポートされていません。
もう一度、今度はテーブル作成手順でフィールドをちゃんと手動設定
ようやく思った通りにロードできました。
コンソールからクエリを実行してみる
続いて、先ほど作成したテーブルにクエリを投げてみます。
まずは select。
画面上部の「クエリエディタ」に select 文を入力して「実行」ボタンをクリックすると、ちゃんと意図した結果が返ってきました。
続いて insert。
こちらもちゃんと反映されています。
さらに、updateと
deleteも。
普通のSQLでできる操作感、いいですねーｖ
まとめ（所感）
初めての BigQuery でしたが、案ずるより産むがやすし？
さらにバッチ処理などの業務利用を想定した使い方を、引き続き確認していこうと思います。
