BigQuery用SQLスニペット

#はじめに

BigQueryでアレをする時どんなクエリを書けばいいんだっけ？ということが良くあるので、残しておきます。
StandardSQLです。
都度追記します。

公式ドキュメント

INFORMATION_SCHEMA
PARSE_DATE
DATE でサポートされる形式設定要素

スニペット

履歴 関連

クエリで特定のテーブルやカラムが使われているか調べる

SELECT
  query
FROM
  region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT -- リージョンが異なる場合は適宜変更
WHERE
  query LIKE '%XXXXX%'

テーブル定義 関連

スキーマ定義をText形式で抽出する

SELECT
  ARRAY_TO_STRING(columns, ',')
FROM (
  SELECT
    ARRAY(
    SELECT
      CONCAT(column_name, ":",
        CASE data_type
          WHEN 'INT64' THEN 'INTEGER'
          WHEN 'FLOAT64' THEN 'FLOAT'
          ELSE data_type END) AS type
    FROM
      `<dataset>.INFORMATION_SCHEMA.COLUMNS`
    WHERE
      table_name = 'all'
    ORDER BY
      ordinal_position) AS columns)

descriptionを抽出する

SELECT
  table_name,
  option_value
FROM
  `<dataset>.INFORMATION_SCHEMA.TABLE_OPTIONS`
WHERE
  option_name = 'description'

Date 関連

2015/2/6をDate型にする

WITH
  data AS (
  SELECT
    '2015/2/6' AS date1,
    '2015/02/16' AS date2,
    '15/02/16' AS date3 )
SELECT
  PARSE_DATE('%Y/%m/%d',
    date1),
  PARSE_DATE('%Y/%m/%d',
    date2),
  PARSE_DATE('%y/%m/%d',
    date3) --yは小文字
FROM
  data

Nov 26, 2018 をDate型にする

WITH
  data AS (
  SELECT
    'Nov 26, 2018' AS date1 )
SELECT
  PARSE_DATE('%b %e, %Y',
    date1)
FROM
  data

日付別テーブル 関連

最新の日付別テーブルのみ参照する

WITH
  latest AS (
    SELECT
      MAX(_TABLE_SUFFIX) AS table_suffix
    FROM
      `sandbox-suzutatsu-bq.test.sample1_*`
)
SELECT
  *
FROM
  `sandbox-suzutatsu-bq.test.sample1_*`,
  latest
WHERE
  _TABLE_SUFFIX = latest.table_suffix

日付別テーブルの日付をカラムとして持つ

SELECT
  PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) as date
FROM
  `sandbox-suzutatsu-bq.test.sample1_*`

便利クエリ 関連

クエリ履歴をクエリ費用が高い順に表示

WITH
  x AS (
  SELECT
    user_email,
    project_id,
    job_id,
    start_time,
    ROUND(total_bytes_billed / POW(1024, 3), 1) AS total_giga_bytes_billed,
    query
  FROM
    region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE
    total_bytes_billed IS NOT NULL
    -- AND 金額に関する条件
    -- AND 期間に関する条件
    )
SELECT
  user_email,
  project_id,
  job_id,
  DATETIME(start_time, 'Asia/Tokyo') AS datetime,
  total_giga_bytes_billed,
  query
FROM
  x
ORDER BY
  total_giga_bytes_billed DESC

ある日付の期間の分だけ行を返す

WITH base AS (
SELECT
  GENERATE_DATE_ARRAY(
    DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 2 MONTH),
    CURRENT_DATE('Asia/Tokyo'),
    INTERVAL 1 DAY) AS date_array
)
SELECT
  date_row
FROM
  base, UNNEST(date_array) AS date_row

UDF（ファンクション）関連

マスタとして用意したテーブルを使ってAをBに変換する

CREATE OR REPLACE FUNCTION func.yyyymmdd_to_yearmonth(yyyymmdd STRING)
OPTIONS (description="YYYYMMDDからマスタテーブルで変換後のXX年月を取得する")
 AS ((
    SELECT
      MAX(year_month)
    FROM
      master.xxxxx_master
    WHERE
      start_date <= PARSE_DATE('%Y%m%d', yyyymmdd)))