仕事メモとか

仕事中に調べた情報とか知ったことをメモしています。
unixコマンド, vim, oracle, putty, postgresql, bash, EXCEL, python, SQL全般 など。
最近は tableau, movabletype とかも触ったりしています。
雑な読書感想とかはこちら

カテゴリ: BigQuery


Resources exceeded during query execution: Table metadata used for the
query is too large が出た

BigQueryを使っていたら、広範囲のデータを扱おうとしたら、こんなエラーが出ました。

Resources exceeded during query execution: Table metadata used for the
query is too large

調べてみると、対象データがデカすぎて諦めたとのこと(意訳)。

どうも集計とかorder byとかすると内部的に容量使いまくって動かないことがあるらしい。

いや、BigなQueryとしてどうなんだい? と思ってSQLを見てみたら、

FROM
(TABLE_DATE_RANGE(eop.pageview,
TIMESTAMP('${FROM_DATE}'),
TIMESTAMP('${TO_DATE}')))

こんな感じになっていました。
あまりBQに詳しくないのですが、調べてみたらTABLE_DATE_RANGEはレガシーSQLだそうで。

スタンダードSQL方式にしてみたら動くか試してみます。

記載例)

#standardSQL
select
*
from
`mydat.data_table_*`
where
_TABLE_SUFFIX between '20180101' and '20181231'


参考:
https://cloud.google.com/bigquery/docs/reference/standard-sql/wildcard-table-reference?hl=ja#migrating_legacy_sql_table_wildcard_functions
https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql?hl=ja
https://note.mu/fjustin/n/n16907ccde019
http://gugurekasu.blogspot.com/2016/03/bigquery.html


BigQueryでスタンダードSQLでの期間指定方法

時代がだんだん進んでいるので、BigQueryでもスタンダードSQLの方がスタンダードになってきました。
古い書き方はレガシー扱いされ、cloudではわざわざチェックしないとレガシーの書き方使えません。

スタンダードとレガシーのSQL実行方法
「オプション」「クエリの設定」「追加の設定」「SQL言語」で標準かレガシーを選択

はい、本題。期間指定方法です。

参考:
https://www.marketechlabo.com/bigquery-legacy-sql/

BigQueryではログみたいなデータを日付でもったりするのですが、
1テーブル1日みたいな切り出しにします。よって複数のテーブルになったりします。

テーブル名の例)
data_20180807


●レガシーの取得方法
select *
from table_date_range([data_], timestamp('2018-08-07'),
timestamp('2018-08-07'))

table_data_rangeで指定します。


●スタンダードの取得方法
select *
from `data_*`
where _table_suffix between '20180807' and '20180807'

テーブル名をバッククォートで囲うのがポイント。でもちょっとキモイ。


BigQueryでネストされたデータを良い感じに展開してくれる unnest関数

ネストしているデータに対し、レガシー、スタンダードの両方でのデータの出し方を纏めてみます。

参考:
https://www.marketechlabo.com/bigquery-legacy-sql/
https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql?hl=JA

まず、ネストのデータ構造について。
この辺はXMLDBみたいな形ですが、

user
data
.data_key
.data_name

みたいに「data」の中に複数の情報が入る形になっています。
select * みたいなもので全展開すると、data_keyが複数入っている場合は出力行も複数になったりします。
(ネストの外側の部分は同値を入れて補完してくれる)

●レガシーの取得方法
select user, data.data_name
from {テーブル}
where data.data_key = 'xxx'

※そのまま展開して使っている


●スタンダードの取得方法
select user, data.data_name
from {テーブル},
unnnest(data) as data
where data.data_key = 'xxx'

※一旦unnestを指定する必要がある


スタンダードSQLを使いたいときの構造指定みたいな感じです。
bigqueryはデータの持ち方が先の時代に進んでるようだ。

↑このページのトップヘ