・08.Google App EngineとBigQuery 導入編(1/3)
・09.Google App EngineとBigQuery 実践編(2/3)
・10.Google App EngineとBigQuery ノウハウ編(3/3)
CA Beat エンジニアリーダーのヤマサキ(@vierjp)です。
前回の続きで「09.Google App EngineとBigQuery 実践編(2/3)」です。
「08.Google App EngineとBigQuery 導入編(1/3)」で
クエリを実行するための環境は整ったので、
今回はRDBとの違いも含めたクエリの構文や関数をご紹介しようと思います。
○クエリの実行方法や関数の紹介
・SELECT
「SELECT * FROM ~」という書き方はできません。個別にカラム名を指定する必要があります。
BigQueryでは条件指定も含め参照するカラム数によって課金額が変わるので、
SELECT句での指定は必要最低限にするのが良いでしょう。
参考:SELECT(Query Reference - Google BigQuery)
・ORDER BY
RDB同様にソート順を指定する。
特に制限なくRDB同様に使えます。
参考:ORDER(Query Reference - Google BigQuery)
・GROUP BY
RDB同様に集計関数を使う際にフィールドに同じ値を持つ行をグループ化する。
特に制限なくRDB同様に使えます。
参考:GROUP BY(Query Reference - Google BigQuery)
・WHERE
抽出条件を指定する。
特に制限なくRDB同様に使えます。
条件指定方法
expr1 = expr2
expr1 != expr2
expr1 > expr2
expr1 < expr2
expr1 >= expr2
expr1 <= expr2
expr IS NULL
expr IN(expr1, expr2, ...)
*IN( SELECT hoge FROM ・・・」のサブクエリも実行可能
よく使うものは一通り使えますし、
これらを「AND」や「OR」で繋いだ複合条件も使えます。
他にも
IFNULL(expr, null_default)
IS_INF(numeric_expr)
IS_NAN(numeric_expr)
IS_EXPLICITLY_DEFINED(expr)
などがあります。
参考:WHERE(Query Reference - Google BigQuery)
・HAVING
GROUP BY句によってグループ化されたデータに対し、検索条件を指定して絞り込みます。
特に制限なくRDB同様に使えます。
参考:HAVING(Query Reference - Google BigQuery)
・LIMIT
指定する件数だけ取得する。
特に制限なくRDB同様に使えます。
参考:LIMIT(Query Reference - Google BigQuery)
・JOIN
SELECT
d.orderId,i.itemName
FROM
[OrderDetail] d
JOIN [Item] i on d.itemId = i.itemId
という感じで構文的にはRDBと同じようにjoinできます。
以下のようにサブクエリも使えます。
SELECT
d.orderId, i.itemName
FROM
[OrderDetail] d
JOIN
(SELECT
item_id, item_name
FROM [Item]
WHERE
delete_flag=false
) i on d.itemId = i.itemId
ドキュメントを見るとLEFT OUTER JOINもできるようです。
私はBigQueryでは今のところ使ったこと無いですが。
ただし、BigQueryのJoinには制限があります。
そして知る限りでこれが一番やっかいなところです。(詳しくは次回)
参考:JOIN(Query Reference - Google BigQuery)
・UNION
これまでも書いてきたとおり、
構文としてはのUNIONはありませんが、
SELECT
hoge
FROM
[dataset.table1],
[dataset.table2],
[dataset.table3]
という記法でUNIONできます。
元データがいくつものファイルに分かれている場合、
「追記アップロード」する代わりに
「別の名前でアップロードして、クエリするときにはUNIONしてまとめて使う」
ということもできます。
BigQueryでは通常のRDBを使う時に比べてUNIONを使う機会は多いと感じます。
・集計関数
AVG(numeric_expr)
COUNT(*)
SUM(field)
MAX(field)
MIN(field)
などを使えます。
これらはRDBと同じ感覚で使えます。
「02.macheでApp EngineのログをBigQueryにコピーして解析」では
以下のようなクエリを紹介しましたが、やはりRDBのクエリと変わらないクエリです。
SELECT path, AVG(cost) as cost
FROM [テーブル名]
GROUP BY path
ORDER BY cost desc
LIIMIT 10;
ただし、「COUNT(DISTINCT, field)」関数には注意してください。
この関数は正確な値を返しません。
ドキュメントには以下のように書いてあります。
Note that the returned value for DISTINCT is a statistical estimate, not necessarily the exact value.
→DISTINCTで返される値は正確な値ではなく統計的な推定値であることに注意してください。
参考:Aggregate Functions(Query Reference - Google BigQuery)
・算術演算と数学関数
SELECT ROUND(1.463),ROUND(1.5)
→1.0,2.0
「最も近い整数に数値を丸める関数」です。
業務的に三角関数とか使う機会が無いのでとりあえずこれだけご紹介。
参考:Arithmetic and Mathematical Functions(Query Reference - Google BigQuery)
・正規表現
・REGEXP_MATCH('str', 'reg_exp')
正規表現で条件を指定して検索する際に使います。
macheで転送したログの検索もこれのおかげで簡単です。
「02.macheでApp EngineのログをBigQueryにコピーして解析」では
以下のようなクエリを紹介しました。
SELECT FORMAT_UTC_USEC(timestamp + 32400000000) as timestamp, httpStatus, path,appLog
FROM [テーブル名]
WHERE REGEXP_MATCH(appLog, '[正規表現や検索したい文字列を記述]')
ORDER BY timestamp desc;
・REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')
クエリした結果の値を正規表現で書き換えたい場合に使います。
REGEXP_REPLACE('[元の文字列]', '[書き換え対象の文字列を示す正規表現]', '[書き換え後の文字列]')
参考:Regular Expression Functions(Query Reference - Google BigQuery)
・文字列関数
・LOWER
LOWER('Test')→「test」
・UPPER
UPPER('Test')→「TEST」
・LEFT
LEFT('2012-11-14 08:00:00',10)→「2012-11-14」
など。
参考:String Functions(Query Reference - Google BigQuery)
・日付・時刻関数
・FORMAT_UTC_USEC(timestamp) as timestamp
時刻を人が見やすい形式に変換する
Unix時間を「YYYY-MM-DD HH:MM:SS.uuuuuu」形式に変換します。
・STRFTIME_UTC_USEC(timestamp_usec, 'date_format_str')
C++のstrftime関数の形式で時刻を指定したフォーマットに変換する。
例えばHTTPヘッダーで使われる「RFC 1123形式」に変換するなら以下のようにします。
STRFTIME_UTC_USEC(PARSE_UTC_USEC('2012-11-21 23:10:20 GMT'), '%a, %d %b %Y %H:%M:%S')
→「Wed, 21 Nov 2012 23:10:20 GMT」
・PARSE_UTC_USEC(readable_timestamp)
時刻の文字列形式からUnix時間に変更する。
文字列形式のフォーマットを指定することはできません。
文字列は「YYYY-MM-DD HH:MM:SS[.uuuuuu]」形式である必要があります。
違う形式の文字列で時刻を保持している場合、正規表現で変換する必要があります。
例えば
'2012/11/14 09:00:00.973'
という形式で保持しているなら
REGEXP_REPLACE(hogeDate ,'/' ,'-')
のようにして変換します。
とはいえ、保持している形式が根本的に違うと苦しいかもしれません。
固定フォーマットなら頑張って指定した文字数ずつ切り取って連結して・・・とできないことはないですが。
また、関数名からもわかるとおり、UTCを基本としていてTimezoneの指定ができません。
集計結果のTimezoneと保持している時刻のTimezoneが違う場合、
(例えばUTCで保持しているけど日本時間の日付毎に集計したいとか)
クエリを考えるとき、結構混乱しそうになります(´・ω・｀)
この辺も詳しくは次回に。
・NOW()
現在時刻取得関数。
UNIX時間の時刻を取得します。
・UTC_USEC_TO_DAY
UTCの時刻からHour以下を切り捨てる関数
他にもUTC_USEC_TO_MONTHとか色々あります。
・加算・減算
timestamp + 32400000000
のように、数値型の時刻(Unix時間)に対して加算・減算できます。
この例では+9時間しています。
これを「FORMAT_UTC_USEC」で"人が見やすい形式"に変換すると日本時間"相当"の時刻の文字列になります。
参考:Timestamp Functions(Query Reference - Google BigQuery)
○実践編まとめ
以上でBigQueryのクエリの構文や関数の紹介でした。
RDBと構文が異なるのはUNIONぐらいで、基本的にはほとんど変わらない事がわかると思います。
関数も良く使われるものは大抵のものはありますし、
大規模データに対して正規表現で検索して高速に結果を取得する事はRDBではできないでしょう。
今回全ての構文や関数について書いたわけではないので、
より詳しく知りたい方は公式のクエリ・リファレンスをご確認ください。
Query Reference - Google BigQuery — Google Developers
ただし、次回に詳しく記述する「JOINの制限」と「日付・時刻周りの関数」は注意が必要です。
次回は
○10.Google App EngineとBigQuery ノウハウ編(3/3)
・BigQueryの制限とその回避策
・日本時間を扱う際のTips
・BigQueryでApp EngineのDatastoreのデータを集計するために考慮しておくと良いこと
です。
3日連続BigQuery特集も次回で最後です。
それではまた明日(｀･ω･´)ﾉｼ
この記事をはてなブックマークに追加
★宣伝★
CA BeatではTwitter、Facebookページの運営も行っております！
ブログの更新情報だけでなく、役立つスマホトピックニュースを
選りすぐって配信しております。
ブログ右サイドバーからぜひフォロー、いいね！してくださいねヽ(´▽`)ﾉ