BigQueryの使い方を調べた時のメモ ex-mixi Advent Calendar 2017/12/08

12月8日になりました、ex-mixi Advent Calendar 2017 - Qiitaの8日目を担当します。
Advent Calendarを担当するのをきっかけにブログを開設しました。まずはブログを書く習慣が出来るまで頑張れればと思います。

自己紹介とか色々書こうかなと思ったのでQiitaではなくこちらで書いていきます。

自己紹介

mixiの在籍期間は2010年から2013年までで、mixiフォトやmixiチェックなどを担当していました。開発拠点が上海にあった時には何回か上海出張をさせていただきました。上海の街並みが忘れられず、またいつの日か旅行で行きたいと思っているのですがいまだ行けずじまいです。

現在はココラブルという会社に勤めています。
会社の主な事業はFacebook/Twitter/Instagram等のSNS広告事業や、越境コマース事業、mamaPRESS等の自社メディアやしろくろちゃんねる、ミニ四リーマン等のメディア事業となります。

入社した当時の人数は20人くらいで、そのうちエンジニアは自分含めて3人でした。今はアルバイトさんやインターン生を含めると60人くらいまで社員数が増えて、エンジニアの人数は11人になりました。

入社してからしばらくはFacebook Marketing APIを利用して広告の入稿ツールや効果測定ツールを作成してました。
Datos（ダトス）というツールを作成していた時に、Facebook様が主催する広告エンジニア向けセミナーでLTしたりという事もありました。

現在は越境コマース事業のエンジニアとしてデータの収集と分析、ECサイトの運用、運用負荷軽減に繋がるツールを作ったりしています。

仕事以外の話では、週1回ジムに行って筋トレしたりとか、柴犬を飼っているので朝と晩に40分ぐらい散歩して健康になるよう努めています。
日々の散歩の様子はinstagramのshibainu_komugiというアカウントで更新しています。今年もフォロワー数1万まで届かなそうですね・・
また、最近初めてTOEIC試験を受けました。545点と思ったよりは点を取れたのですが、700点目指して毎日少しずつ勉強しています。

自己紹介が長くなっているのでそろそろ本題に入ります。

BigQueryについて

昨年くらいからBigQueryを使い始めました。APIを叩くとかスクレイピングして取得したデータをBigQueryに登録したりとか、エンジニア以外の人がまとめていたGoogleスプレッドシートをデータソースとしてBigQueryに取り込んでSQLを書いているのですが、こういう使い方出来るの？みたいなことを調べようとするとなかなか見つからなかったりしていました。
とりあえずあちこちネット検索したりとか、BigQueryの公式ドキュメントを読んで、実際に動かした時に残していたメモをいくつか書いていこうと思います。

レガシーSQLと標準 SQL

BigQueryにはレガシーと標準SQLと言うものがあるようです。公式ドキュメントの標準SQLへの移行に以下のような記載があります。

これまで、BigQuery は BigQuery SQL という非標準 SQL 言語を使用してクエリを実行していました。BigQuery 2.0 から、BigQuery は標準 SQL のサポートをリリースし、BigQuery SQL はレガシー SQL と改名されています。BigQuery に保存されたデータのクエリを実行するには、標準 SQL の使用が推奨されます。

公式ドキュメントで関数を調べると大体レガシーSQLのページに辿り着いて、それをそのまま利用すると動かなかったりするので標準SQLへの移行というページをよく見るようになりました。

JSON

とりあえずデータをJSONで登録することがあると思うのですが、JSONから特定の値を取得する時に試した時の内容です。JSON_EXTRACTとJSON_EXTRACT_SCALARで挙動が変わってきます。

SELECT
  -- ダブルクォートが付く
  JSON_EXTRACT('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello1,
  -- ダブルクォートが無くなる
  JSON_EXTRACT_SCALAR('{"a.b": {"c": "world"}}', "$['a.b'].c") AS hello2;

結果は以下のようになります。

Row	hello1	hello2
1	"world"	world

JSONで登録した値をソート

JSONで取得した情報を基にソートするやり方です。データはDeNAベイスターズのスターティングメンバーを使って、背番号順に並べてみます。

SELECT
  JSON_EXTRACT_SCALAR(dena_baystars, "$[0].name") AS name,
  SAFE_CAST(JSON_EXTRACT_SCALAR(dena_baystars, "$[0].number") AS INT64) AS number
FROM UNNEST([
  '[{"name" : "桑原 将志", "number": "37"}]',
  '[{"name" : "梶谷 隆幸", "number": "3"}]',
  '[{"name" : "J.ロペス", "number": "2"}]',
  '[{"name" : "筒香 嘉智", "number": "25"}]',
  '[{"name" : "宮﨑 敏郎", "number": "51"}]',
  '[{"name" : "戸柱 恭孝", "number": "10"}]',
  '[{"name" : "柴田 竜拓", "number": "31"}]',
  '[{"name" : "今永 昇太   ", "number": "21"}]',
  '[{"name" : "倉本 寿彦", "number": "5"}]'
  ]) AS dena_baystars
ORDER BY number ASC

結果は以下のようになります。SAFE_CAST(x AS INT64)をしないと戸柱 10, ロペス 2, 今永 21といった順番になるので注意が必要です。

Row	name	number
1	J.ロペス	2
2	梶谷 隆幸	3
3	倉本 寿彦	5
4	戸柱 恭孝	10
5	今永 昇太	21
6	筒香 嘉智	25
7	柴田 竜拓	31
8	桑原 将志	37
9	宮﨑 敏郎	51

日付時刻

日付や時刻を取得したい時に以下のようなクエリを使って確認してみました。

SELECT
  CURRENT_DATE() as currentDate,  -- 何も指定がなければUTC
  CURRENT_DATE('Asia/Tokyo') as currentDateTokyo,
  CURRENT_DATETIME() as currentDateTime,
  CURRENT_DATETIME('Asia/Tokyo') as currentDateTimeTokyo,
  CURRENT_TIMESTAMP() as currentTimeStamp,
  FORMAT_TIMESTAMP('%Y年%m月%d日 %H時%M分%S秒', CURRENT_TIMESTAMP()) as formatTimeStamp,
  FORMAT_TIMESTAMP('%Y年%m月%d日 %H時%M分%S秒', CURRENT_TIMESTAMP(), 'Asia/Tokyo') as formatTimeStampTokyo

結果は以下のようになります。

Row	currentDate	currentDateTokyo	currentDateTime	currentDateTimeTokyo	currentTimeStamp	formatTimeStamp	formatTimeStampTokyo
1	2017-12-07	2017-12-07	2017-12-07T07:50:30.336239	2017-12-07T16:50:30.336239	2017-12-07 07:50:30 UTC	2017年12月07日 07時50分30秒	2017年12月07日 16時50分30秒

UDF

取得したデータにドルマークとか円マーク、カンマが入っているものがあるので、UDFを書いたりしていました。公式ドキュメントのユーザー定義の関数を見るとjavascriptで書けるようでした。
下に書いてあるデータ自体にあまり意味は無いですが、javascriptが書けるのは色々助かりそうな気がしました。

CREATE TEMPORARY FUNCTION parse_price(s STRING)
RETURNS STRING
LANGUAGE js AS """
  if (s) {
    s = s.replace(/[$¥,]/g, '');
    return parseFloat(s);
  }
  return 0;
""";

SELECT parse_price(price_list) as price
FROM UNNEST(["$100,000.5", "¥999,999", "1000", ""]) AS price_list;

結果は以下のようになります。

Row	price
1	100000.5
2	999999
3	1000
4	0

文字列の中で特定の単語が何回出てくるか

1つの文中の中で何回出てくるのか興味があったので試してみました。
REGEXP_EXTRACT_ALLでヒットする文字列を配列で取得して、ARRAY_LENGTHで配列の件数を取得するようにしました。

下記の例はギレン総帥の演説でジオンと国民が何回出てくるのかを試した内容です。

-- ギレン総帥の演説テーブルのようなものを作る
-- http://center.ed.kanazawa-u.ac.jp/~taguchi/taguo/ai/giren.htm
WITH  Ghilane AS (
  SELECT  """
我々は一人の英雄を失った。しかし、これは敗北を意味するのか？否！始まりなのだ！ 
地球連邦に比べ、我がジオンの国力は30分の1以下である。 
にもかかわらず今日まで戦い抜いてこられたのは何故か？ 
諸君！我がジオン公国の戦争目的が正義だからだ。これは諸君らが一番知っている。 
我々は地球を追われ、宇宙移民者にさせられた。 
そして、一握りのエリートらが宇宙にまで膨れ上がった地球連邦を支配して50余年、 
宇宙に住む我々が自由を要求して何度踏みにじられたか。 
ジオン公国の掲げる人類一人一人の自由のための戦いを神が見捨てるはずはない。 
私の弟！諸君らが愛してくれたガルマ・ザビは死んだ。 
何故だ！？

「坊やだからさ」 （シャア）

新しい時代の覇権を選ばれた国民が得るは、歴史の必然である。 
ならば、我らは襟を正し、この戦局を打開しなければならぬ。 
我々は過酷な宇宙空間を生活の場としながらも共に苦悩し、錬磨して今日の文化を築き上げてきた。 
かつて、ジオン・ダイクンは人類の革新は宇宙の民たる我々から始まると言った。 
しかしながら地球連邦のモグラ共は、自分たちが人類の支配権を有すると増長し我々に抗戦する。 
諸君の父も、子もその連邦の無思慮な抵抗の前に死んでいったのだ！ 
この悲しみも怒りも忘れてはならない！それを、ガルマは！死をもって我々に示してくれた！ 
我々は今、この怒りを結集し、連邦軍に叩きつけて、初めて真の勝利を得ることができる。 
この勝利こそ、戦死者全てへの最大の慰めとなる。 
国民よ立て！悲しみを怒りに変えて、立てよ！国民よ！ 
我らジオン国国民こそ選ばれた民であることを忘れないでほしいのだ。 
優良種である我らこそ人類を救い得るのである。ジーク・ジオン！
""" AS speech
)

-- 演説中にジオンと国民を何回言っているのか
SELECT
  ARRAY_LENGTH(REGEXP_EXTRACT_ALL(speech, 'ジオン')) as word_1,
  ARRAY_LENGTH(REGEXP_EXTRACT_ALL(speech, '国民')) as word_2
FROM
  Ghilane

結果は以下のようになります。

Row	word_1	word_2
1	6	4

最後に

他にもいくつか書けそうなものもあるのですが、ブログを書く習慣が出来る前にネタが尽きてしまうので今回は終わりにします。小刻みにブログを書いて年末年始を迎えていこうかと思います。最後まで読んで頂きありがとうございました。

明日はQA番長ことkkakizakiさんです。そういえば飲みに行こうと約束してからまだ一度も行ってないですね。

qiita.com