GAミント至上主義

Web Monomaniacal Developer.

BigQueryに入れたアクセスログを調べたい時によく使うスニペットまとめ

よく忘れてDatalabの中を捜索するはめになるので、まとめておく

レガシーSQL

TABLE_DATE_RANGE

昔ながらの日付別テーブルに格納しているとき

FROM TABLE_DATE_RANGE(
  [project_name:dataset_name.table_name_],
  TIMESTAMP("2017-11-27"), 
  TIMESTAMP("2017-11-28")
)

JSON

SELECT
  JSON_EXTRACT(jsn, "$$.text") as string,
  JSON_EXTRACT_SCALAR(jsn, "$$.text") as scalar,

URLからID(数字)を取り出す

SELECT
  INTEGER(REGEXP_EXTRACT(url, r'/doc/detail/([0-9]+)/')) as product_id,

日時の文字列を9時間プラス(日本時間)して、日付だけを返す

dtには「2017-11-26 14:41:27.407015」な感じの日付が文字列で入っている

SELECT
  DATE(DATE_ADD(TIMESTAMP(dt), 9, "HOUR"))