Big Queryの使い方を10分で試してみる

Big QueryにプリセットされているpublicdataのSampleテーブルを使って短い時間でbigqueryを体験してみます。

#### 環境
・CentOS6.5を使用
・インストールはMAC OS X10.9でも確認

#### 事前準備
・事前にGoogle Developers Consoleにログインできるアカウントを作成し、
　プロジェクトも作成しておいてください
・Google Developers ConsoleからBig QueryのAPIをONにしてください
  
`````
注）課金に関する内容は事前にGoogleで確認してください。
`````


CLI環境の準備と設定
------

GUIで頑張り続けるのも大変なので、SDKを入れてコマンドで操作します


#### 1.Google SDKのインストール

`````
# curl https://sdk.cloud.google.com | bash

`````
App Engine toolsに関する選択肢が出ますが、後でインストールできます。
なので、ここでは4のNo App Engineを選択してください。

※インストール後、shellを再起動してください。

#### 2.Googleアカウントにログイン


`````
# gcloud config set account *******@gmail.com

# gcloud auth login
Go to the following link in your browser:
`````

※URLが出るのでブラウザで開いてログイン後に「承認する」を選択してください。
 OSによっては自動でポップアップします。

#### 3.プロジェクトを設定

`````
# gcloud config set project <project>
`````
project ID を使います。プロジェクト作成時に自動で付与されたものです。

#### 4.設定の確認

`````
# gcloud config list
[core]
account = <username>
disable_usage_reporting = False
project = <project>
user_output_enabled = True
`````

#### 5.bqコマンドの確認

ここまでうまく行っていれば下記のような出力になるはずです。

`````
# bq shell
Welcome to BigQuery! (Type help for more information.)
`````

#### 6.確認コマンドを叩いてみる

最後にプロジェクトが正しく設定されているかを確認します。

`````
project>  ls -p
      projectId       friendlyName
 ------------------- --------------
  projectid   project

`````

実際のデータを触ってみる
--------

#### 1.publicdata内のテーブルを確認

```
bq ls publicdata:samples
      tableId       Type
 ----------------- -------
  github_nested     TABLE
  github_timeline   TABLE
  gsod              TABLE
  natality          TABLE
  shakespeare       TABLE
  trigrams          TABLE
  wikipedia         TABLE
```
上記を見ると分かると思いますが、datasetは複数のテーブルを格納する入れ物です。
今回は準備されたものを使用しますが、datasetを作成しテーブルをimportすることも可能です。

#### 2.samples.shakespeareに関する情報を確認

`````
project> show publicdata:samples.shakespeare
Table publicdata:samples.shakespeare

   Last modified                  Schema                 Total Rows   Total Bytes   Expiration
 ----------------- ------------------------------------ ------------ ------------- ------------
  02 May 08:47:25   |- word: string (required)           164656       6432064   
                    |- word_count: integer (required)                           
                    |- corpus: string (required)                                
                    |- corpus_date: integer (required)
`````

#### 3.ブラウザからも確認してみる

https://bigquery.cloud.google.com/project/<project>
もしくはDeveloper Console -> BIG DATA -> Big Query

ログインすると左下にサンプルデータがあるのがわかるかと思います。

今回、「samples.shakespeare」というTebleに対してQueryを試してみます。
右上のNew Queryの欄にSQL文を入力し、「Run Query」ボタンを押します。

![9af6e65d-f982-60f4-bc67-0a46038f1cad.jpeg](https://qiita-image-store.s3.amazonaws.com/0/51152/a607d0d7-44df-6c38-7a74-7a847c73e7a8.jpeg)

このQueryの処理にかかった時間が出てきます。

```
Query complete (1.7s elapsed, 2.35 MB processed)
```

ちなみにTableの情報は以下の通りです。Big Queryからすると小さなデータでしょうか。

```
Table ID	publicdata:samples.shakespeare
Table Size	6.13 MB
Number of Rows	164,656
Creation Time	8:47am, 2 May 2012
Last Modified	8:47am, 2 May 2012
```

samples.gsodを触って過去の天気情報を確認してみる
--------------

#### 1.まずはテーブルの情報を確認してみます。

```
# bq show publicdata:samples.gsod
Table publicdata:samples.gsod


   Last modified                        Schema                       Total Rows   Total Bytes   Expiration
 ----------------- ------------------------------------------------ ------------ ------------- ------------
  02 May 08:47:20   |- station_number: integer (required)            114420316    17290009238
                    |- wban_number: integer                                     
                    |- year: integer (required)                                 
                    |- month: integer (required)                                
                    |- day: integer (required)                                  
                    |- mean_temp: float                                         
                    |- num_mean_temp_samples: integer                           
                    |- mean_dew_point: float                                    
                    |- num_mean_dew_point_samples: integer                      
                    |- mean_sealevel_pressure: float                            
                    |- num_mean_sealevel_pressure_samples: integer              
                    |- mean_station_pressure: float                             
                    |- num_mean_station_pressure_samples: integer               
                    |- mean_visibility: float                                   
                    |- num_mean_visibility_samples: integer                     
                    |- mean_wind_speed: float                                   
                    |- num_mean_wind_speed_samples: integer                     
                    |- max_sustained_wind_speed: float                          
                    |- max_gust_wind_speed: float                               
                    |- max_temperature: float                                   
                    |- max_temperature_explicit: boolean                        
                    |- min_temperature: float                                   
                    |- min_temperature_explicit: boolean                        
                    |- total_precipitation: float                               
                    |- snow_depth: float                                        
                    |- fog: boolean                                             
                    |- rain: boolean                                            
                    |- snow: boolean                                            
                    |- hail: boolean                                            
                    |- thunder: boolean                                         
                    |- tornado: boolean                                         

```

#### 2.サンフランシスコの2010年以降の雨の日数を確認する

`````
SELECT
  count(*)
FROM
  [publicdata:samples.gsod]
WHERE
  station_number = 724940
  AND year >= 2010
  AND rain=TRUE
  
16.1GB 
number of rows 114,420,316

 Query complete (3.1s elapsed, cached) 

`````

#### 3.コマンドラインから別のQueryで試してみます。
`````
#bq query "SELECT year, month, day, thunder, rain, FROM [publicdata:samples.gsod] WHERE thunder=TRUE AND rain=TRUE AND station_number=037720 ORDER BY year DESC, month DESC, day DESC LIMIT 10"

+------+-------+-----+---------+------+
| year | month | day | thunder | rain |
+------+-------+-----+---------+------+
| 2010 |     3 |  23 |    true | true |
| 2010 |     2 |  21 |    true | true |
| 2010 |     2 |  15 |    true | true |
| 2010 |     1 |  23 |    true | true |
| 2010 |     1 |  18 |    true | true |
| 2010 |     1 |  16 |    true | true |
| 2010 |     1 |  15 |    true | true |
| 2009 |    12 |  24 |    true | true |
| 2009 |    12 |  23 |    true | true |
| 2009 |    12 |  22 |    true | true |
+------+-------+-----+---------+------+

`````

新しいdatasetの作成方法
------

・Google Developer Console
　　→DataSetの＋ボタンからNew datasetを選択

・bqコマンド

``````
# bq mk mydataset
``````
　作成後「bq ls」で確認してみてください。

新しいテーブルのimport方法
--------
・Google Developer Console
　　　→datasetの右側の「+」ボタンからWizard形式で追加可能です。
　　　　テーブル名を設定、uploadするファイルの形式とSourceを設定、schemaを指定。
　　　
　![スクリーンショット 2014-08-12 1.24.42.png](https://qiita-image-store.s3.amazonaws.com/0/51152/058c262a-7402-e1b2-0f3a-6674b7729434.png)
  
・bgコマンド
　　　→https://developers.google.com/bigquery/loading-data-into-bigquery?hl=ja#loaddatagcs

> bq load --schema=cloud_storage_usage_schema.json my_dataset.usage_2012_06_18_v0 gs://my_logs/bucket_usage_2012_06_18_14_v0


その他
------

・正規表現を使ってもほぼ遅くならない。並列度がけた違いとのこと。
・パフォーマンスが出ないときは、Query PriorityがInteractiveになっているか確認。  

冒頭にも記載しましたが、課金については事前に十分にご確認の上で実施してください。
この記事によりいかなるトラブルがあった場合でも当方は一切責任を取れません。