BigQueryよく使うもの

query template

#standardSQL
-- outline
select

meta

table wild card

viewが含まれるとエラーになる

select
  _table_suffix as table_suffix_ymd
  , count(1) as record_count
from dataset.table_*
group by 1

#standardSQL
-- table record counts (日付テーブル用、じゃなくても集計対象にはなる)
select
  _table_suffix as table_suffix
  , regexp_extract(_table_suffix, '(.*)_\\d{8}') as table_name
  , regexp_extract(_table_suffix, '.*_(\\d{8})') as ymd
  , count(1) as record_count
from
  `for_wild_card.*`
group by 1,2,3

timestamp

#standardSQL
select
  -- not date_add
  timestamp_add(timestamp('yyyy-mm-dd'), interval n part)
  , format_timestamp("%F %X", timestamp('yyyy-mm-dd hh:mm:ss'))

array

unnest

#standardSQL
-- five record
with t as (
  -- [1,3,5]
  select 1 as id, generate_array(1, 5, 2) as ary union all
  select 2 as id, [6,7] as ary
)
select
  t.id
  , ary2
from t
cross join unnest(t.ary) as ary2

aggregation

#standardSQL
-- length, records value to array, concat arrays
with t as (
  select 1 as id, 1 as v, [1, 2, 3] as ary union all
  select 2 as id, 2 as v, [1, 2, 3]    as ary union all
  select 2 as id, 2 as v, [1, 2]    as ary union all
  select 2 as id, 3 as v, [4]        as ary
)
select
  id
  , sum(array_length(ary)) as total_length
  , array_agg(v order by v desc) as v_ary
  , array_agg(distinct v order by v) as v_uniq_ary
  , array_concat_agg(ary) as concat_ary
from t
group by id

json(struct, int64, array)

#standardSQL
-- nested struct, int64
create temporary function to_struct(json_str string)
returns struct<
  key int64
  , nested struct<key2 string>
>
language js as """
  return JSON.parse(json_str)
"""
;
with t as (
  -- 2^63 -1
  -- 9223372036854775807
  -- 9223372036854776000 <- JSON.parsed (over 64bit)
  
  -- 2^62-1, js number is double
  -- 4611686018427387903
  -- 4611686018427387904 <- JSON.parsed (not equal)
  select '{"key": 4611686018427387903, "nested": {"key2": "string_value"}}' as json_str
)
select
  json_str
  , cast(json_extract(json_str, '$.key') as int64) as json_value
  , json_extract(json_str, '$.nested.key2') as json_quoted_value
  , json_extract_scalar(json_str, '$.nested.key2') as json_scalar_value
  , to_struct(json_str) as bq_struct
from t

#standardSQL
-- array of struct
create temporary function to_ary(json_str string)
returns array<struct<
  key int64
>>
language js as """
  return JSON.parse(json_str)
"""
;
with t as (
  select '[{"key": 1}, {"key": 2}]' as json_str
)
select
  json_str
  , to_ary(json_str) as bq_ary
from t

create table using bq query

cat .sql | bq -q --apilog=apilog.log query \
  --destination_table=dataset.table \
  --allow_large_results \
  --use_legacy_sql=false \
  --replace=true