BigQueryのStandard SQLの日付・時刻、JSON、正規表現のTips

はじめに

BigQueryを使っていて、しばしば書き方を忘れたりするものをメモしておきます。

内容

日付・時間関連

unixtimestamp と TIMESTAMP型 の変換

TIMESTAMP型は「時刻」的なデータなので unixtimestamp と普通に対応します。

• unixtime -> TIMESTAMP: TIMESTAMP_SECONDS(unixtime)
• TIMESTAMP -> unixtime: UNIX_SECONDS(TIMESTAMP "2016-11-18 00:00:00+09")

文字列からTIMESTAMP型には普通にCASTできる。TimeZoneは省略すると+00になります。

SELECT
  TIMESTAMP "2016-11-18 00:00:00" as utc,
  TIMESTAMP "2016-11-18 00:00:00+09" as jst

utc	jst
2016-11-18 00:00:00 UTC	2016-11-17 15:00:00 UTC

TIMESTAMP型 と DATETIME型 の変換(DATE型もほぼ同じ)

DATETIME と TIMESTAMP は普通にCASTできます。

• TIMESTAMP -> DATETIME: DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo')
• DATETIME -> TIMESTAMP: TIMESTAMP(DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'))

DATETIME型の場合「暦」的な型なので、あるTimeZoneに変換したあとは、TimeZone情報自体は失われます（たぶん）。
なので、 TIMESTAMP->DATETIME->TIMESTAMP にCASTすると、時刻が変わってしまっている場合があるので注意が必要です(３つ目のケース)。

SELECT
  TIMESTAMP "2016-11-18 00:00:00+09",
  DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'),
  TIMESTAMP(DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'))

f0_	f1_	f2_
2016-11-17 15:00:00 UTC	2016-11-18T00:00:00	2016-11-18 00:00:00 UTC

DATETIME型は「日」「月」などに丸めることができる(DATE型もほぼ同じ)

DATETIME型は「暦」的なので、DATETIME_TRUNC()関数を使うと「日」や「月」という単位にTruncateできます。
Timezoneを考慮した日単位や月単位にグルーピングするときに便利です。

SELECT
  DATETIME_TRUNC(DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'), DAY) as day,
  DATETIME_TRUNC(DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'), MONTH) as month,
  DATETIME_TRUNC(DATETIME(TIMESTAMP "2016-11-18 00:00:00+09", 'Asia/Tokyo'), YEAR) as year

day	month	year
2016-11-18T00:00:00	2016-11-01T00:00:00	2016-01-01T00:00:00

DATETIME型は「１ヶ月後」などの計算ができる(DATE型もほぼ同じ)

SELECT
  DATETIME_ADD(DATETIME '2015-12-01', INTERVAL 30 DAY) as day30,
  DATETIME_ADD(DATETIME '2015-12-01', INTERVAL 1 MONTH) as month

day30	month
2015-12-31T00:00:00	2016-01-01T00:00:00

JSON文字列のParse

※ 以下の方法は既に不要で、最近なら JSON_EXTRACT または JSON_EXTRACT_SCALAR
などが便利です。

JSON文字列から取り出す名前や型を明示する必要はありますが、以下のようにすれば可能です。

CREATE TEMPORARY FUNCTION parse_json(json_str STRING) 
RETURNS STRUCT<name STRING, birthday STRING, mail STRING> 
LANGUAGE js AS """
  return JSON.parse(json_str);
""";

SELECT parse_json('{"name": "hogehoge", "birthday": "2000-01-01", "pref": 31}') as user

Row	user.name	user.birthday	user.mail
1	hogehoge	2000-01-01	null

<> で指定しなかった列 pref は出力されませんし、キーが無かったmailはnullになります。

正規表現

正規表現を使って文字列から、マッチした部分を抽出したり、マッチするかを判定することができます。

SELECT 
  REGEXP_EXTRACT("[e-mail]", r'^([^@]+)@'),
  REGEXP_CONTAINS("[e-mail]", r'@gmail\.com$')

f0_	f1_
hogehoge	true

さいごに

これで毎回過去の履歴を捜索しなくて済むかな。。。