/// BANGBOO BLOG /// On 2021-02-21

February 21, 2021 List

BigQuery on Feb 21, 2021 1:00 AM

February 21, 2021

BigQuery

■Big queryリファレン繧?

BigQuery: クラウドデー繧? ウェア繝?ウ繧? | Google Cloud（チュートリアルみたいな・??　

BigQuery解説：https://beyondjapan.com/blog/2016/03/what-is-bigquery/

クエリ処理のツリーアーキテクチャによる分散並列処理

複数のサーバーに対してツリー状に拡がっていき、並列にサーバー臀??で同時に分散処理

　ルートサーバ＞intermediateサーバ＞leafサーバ

BigQuery MLという機能を利用すると、機械学習モデルをCloud AI Platform縺?TensorFlowなどに連携させ、クエリ軆??果を素早縺?AIと連謳?

Lookerというデータ分析プラットフォームとの連携よりクエリ軆??果を、データ統合、変觸??、分析、可鐔??化、レポーティングすることができ、非常に強力縺?BI

列指向型・カラム型データベース・カラムナストレージ・??一般的縺?RDBMSでは鐔??単位でデータが保存）

　必要なカラムのデータを藹??得するだけでよ縺?、またデータは圧縮できる

https://dev.classmethod.jp/articles/google-bigquery-debut/

GCPプロジェクト＞データセット＞テーブル・??行row列columnで普通のテーブル、ネイティブbigqueryテーブ繝?/Googleドライブのような藹??部テーブル、SQLクエリによるビュー・??
　アンス繧?_で藹??まるデータセット名は隠しでコンソールで非表示

ジョブは非同期で藹??行され、ステータスをポーリング・??データの読み込み、データのエクスポート、データのクエリ、データのコピーなど・??

クエリ・??ウェブ UI、bq コマンド、BigQuery REST APIの方觸??がある、SQLと同じ？

SELECT title, answer_count, view_count

FROM `bigquery-public-data.stackoverflow.posts_questions`

ORDER BY view_count DESC　LIMIT 10

BigQuery縺?SELECT tag, time FROM [dataset_name.table_name_20151206]のように藹??要な列だけを選択した場合にはスキャンの藹??を狭めることは可能ですが、LIMITやWHERE句には臀??を書いてもテーブルをフルスキャンしてしまう

節約　Ama縺?s3に入れRedshift内でテーブルを分割した後縺?BigQuery

Hadoopでも使繧?れていたGoogle開発のエンジンであるMapReduceは、非構造化データをプログラミングモデルを通して扱うが、巨大なテーブルの軆??合や巨大な出力結果のエクスポートも可能である半面、処理時間は数分間から数日に藹??んだ、だが、BigQueryは、あらかじめデータを構造化し縺?BigQueryのテーブルに格軆??しておかねばならないが、ほとんどのクエリは数軆??で藹??了する

サードパーテ繧? ツール・??データの読み込みや視覚化を行うツールなど・??を使用し縺? BigQuery のデータにアクセス藹??

Google Cloud SDKをインストールすればコマンドラインが使える
BQは同一リージョンでない縺?Joinができない、ゾーンはマルチで良い
　BQで縺? us 縺? eu がマルチリージョ繝?

　　22/4現在のリージョンリスト：asia-east1-2、asia-northeast1-3、asia-south1-2、asia-southeast1-2、australia-southeast1-2、europe-central1-2、europe-north1、europe-west1-6、northamerica-norhteast1-2、southamerica-east1、sourthamerica-west1、us-central1、us-east1-4、us-west1-4

パブリックデータに直でアクセスできる

SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013`

BigQuery の臀??般公開データセット | Google Cloud
　→FROM句の書き方・??他のプロジェクトを指す名前に繝?イフンがあるとバッククォートで囲む必要がある

　　`other-prj`.dataset.table　あるい縺? `other-prj.dataset.table`

■標準SQL
先頭鐔??でレガシーか宣鐔??　#standardSQL あるいは　#legacySQL

バッククォートでエスケープ、プロジェクト区切りも.(ドット)、From句のカンマ縺?Cross joinで全軆??合せかと思繧?れ通常通りjoinやunionを使う事

配列が使える、カラム一つに配列を入れて藹??元的に扱える

withで臀??時テーブルを作れる

exceptでカラムを除藹??、replaceでカラムの置き觸??え
　select * except(kuso) from a

functionを作って使える標準 SQL ユーザー藹??義関謨? | BigQuery | Google Cloud

分析関謨?over()縺?windowで鐔??算ができる
　rank() over (order by x)は臀??記more縺?RFMに使用している
　ROW_NUMBER() over (order by timestamp) as id,で採番できる

地理関数とかJSON関数とか色々関数もありそう

スクリプトで藹??数やIfやLoopが使える標準 SQL のスクリプト | BigQuery | Google Cloud
join on a.c=b.c縺?join on using (c)とできる

BigQuery 特集: データ操作（DML） | Google Cloud 公藹??ブロ繧?
insert into tbl_dest select * from tbl_source　縺?select結果を挿入できる

■レガシ繝?SQL（標準SQLを使うのが由・??

予約語は鐔??かっこを使ってエスケープ、プロジェクト区切り縺?:

集計関数縺? WITHIN キーワードを使用すると、レコード内の繰り返しの値が集計?

FROM句のカンマは觸??準SQL縺?Cross joinとは異なりUNION ALL 演算子

通常縺?SQL処理システムとは異なり、BigQueryは繰り返しデータの処理を前觸??として設計。繰り返しレコードの觸??造を操作するクエリを記述。その方觸??縺?1つが、FLATTEN 演算子?

JOINは、INNER、[FULL|RIGHT|LEFT] OUTER、およ縺? CROSS JOIN 演算子をサポート、デフォルトINNER

除藹??できる　select + from A OMIT RECORD IF COUNT(payload.pages.page_name) <= 80;

TOP を使用するには、SELECT 句縺? COUNT(*) を含める

分析関謨?over()縺?windowで鐔??算ができる？(標準SQLと同様？)

functionを作って使える(標準SQLと同様？)
JSON等のネストをフラット化

■DDL　データ藹??義鐔??語ステートメントの使逕? | BigQuery | Google Cloud

https://www.isoroot.jp/blog/1651/

auto_incrementもdefaultもprimary keyもindexもshow create tableないのでは・??

CREATE TABLE IF NOT EXISTS bangboo_data.x_xxx (

`no` INT64 NOT NULL,

`user_no` INT64 NOT NULL,

`name` STRING,

`date` DATETIME,

)

同じスキーマで臀??ることもできる

CREATE TABLE ore_ds.test003

LIKE prj.ds.test001

PARTITION BY _PATITIONDATE

■bqコマンドはコンソールで藹??行できる
ブラウザで該当プロジェクトに入りコンソールボタン、下記ではスキーマをJSONで藹??得できる

bq show --schema --format=prettyjson myProject:myDataset.tbl001
bq ls -n 3000 dataset_aho (データセット内のリスト3000件、デフ繧?50件・??)

bq cp --force prj:ds.tbl prj:ds.tbl2

上書きコピー・??削除しコピー・??コンソールだと同名コピーや下記ができない

bq cp ds.tbl1,ds.tbl2 ds.newtbl

2つのテーブルをnewtable にまとめコピ繝?

bq cp -a ds.tbl2 ds.tbl1

tbl2をtbl1に追加コピ繝? --append_table でも同じ

bq load (csvとかgcsのファイルを読み込む）

bq extract (gcsに抽蜃?)

bqコマンドの使い譁?: UNIX/Linuxの部藹?? (q-e-d.net)

■データアップロード時のスキーマ指定
自動検出縺?Firestore、Datastore、Avro、Parquet、ORCだけ？ほぼ手動縺?utf-8縺?csvかjsonlかを使う蠖?

コンソールで手動スキーマ指定藹??(jsonスキーマを張臀??ける)、modeは省略可でデフォ縺?nullable、

JSONスキーマファイ繝?up縺?aqコマンドのみ可、ローカルからup時のコマンドとスキーマ例↓

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

[

{

"description": "quarter",

"mode": "REQUIRED",

"name": "qtr",

"type": "STRING"

{

"description": "total sales",

"mode": "NULLABLE",

"name": "sales",

"type": "FLOAT"

}

]

なお一譌?Google Cloud Storageに放り込んでからやると饅??速　BigQueryにデータをバッチでインポートする - Qiita

COUNT DISTINCTだが、BigQueryでは觸??算値が返って縺?る??。正確な値が必要な場合は、GROUP EACH BY縺?COUNT(*)を組み合繧?せる

https://www.buildinsider.net/web/bigquery/01

■BQ縺?UTC（Universal Time, Coordinatedの頭文字）

ScheduledQueryを終了譌?6/9 13:00JSTで即時設藹??→6/9 01:20UTCで藹??行された

（終了時間縺?JST/UTCの考慮が必要か→SQ実行時間設藹??について縺?JSTかUTCに注諢?するだけ）

実行履歴縺?UTCのためJSTに読み替える必要がある(UTC縺?JST縺?-9時間)

■BigQuery機閭?
///クエリ軆??果を別テーブルに書き込む
その臀??＞クエリの設定＞クエリ軆??果の藹??先テーブルを設藹??する
BigQueryで縺?SELECT結果を他テーブル縺?Insert / テーブル觸??い替えなどができる - コード日進月豁? (hateblo.jp)
クエリ軆??果の書き込縺? | BigQuery | Google Cloud

///Saved query
プロジェクトに対して臀??存をして使いま繧?す等ができる
URLでクエリを共有できる

///Federated Query
スプレッドシートやGCSの藹??部ソースをBigQuery縺?

範囲の書き譁?:シート1!A1:B100

Auto detectにする縺?Header skipを1にし縺?1行目をカラム名として使うといい
注意）

　シートで觸??成を変えると觸??茶苦茶になる

　空觸??のセル縺?nullになる

　使う人縺?BQへもスプレッドシートへも両方権限が必要

///パラメー繧?(変謨?)を使う

--parameter=min_count:INT64:250

SELECT word FROM `prj.ds.t` WHERE AND count >= @min_count

パラメータ化されたクエリの藹??行 | BigQuery | Google Cloud

こういう感じでも使えるので縺?

WITH params AS (

SELECT @sheetInput AS p

tmp_pre_processed_src AS (

SELECT * FROM src

)

SELECT * FROM tmp_pre_processed_src

,params

WHERE

tmp_pre_processed_src.a = p

///*を藹??ける_TABLE_SUFFIXを使う(複数テーブルだ縺?union allになる)

SELECT year FROM `bigquery-public-data.ds.gsod19*`

WHERE _TABLE_SUFFIX BETWEEN '29' and '35'

ワイルドカードテーブルを使用した複数テーブルに対するクエ繝? | BigQuery | Google Cloud
　BTWで軆??らないと全軆??合で課金が厳しいかも

　※ワイルドカード注諢?

　dataset.product_*と書縺?縺?dataset.product_20190425だけでな縺?dataset.product_special_20190425にもヒットしてしまう

betweenは藹??さいから大きいで、パーティションのないシャーディングテーブル日臀??きつきテーブルでも行ける（From句のテーブルに動的な名前を使うにはこれか、EXE IMEDIATE縺?らいか？）
SELECT year FROM `bigquery-public-data.ds.gsod20*`

where _TABLE_FUFFIX between format_date('%y%m%d', date_sub(current_date("Asia/Tokyo"), interval 3 day))
and format_date('%y%m%d', current_date("Asia/Tokyo"))

///時間のパラメータを使う

select * from mytable_{run_time-1h|"%Y%m%d"}

実行時間run_time（UTC）から1時間引いた日→mytable_20180214

クエリのスケジューリン繧? | BigQuery | Google Cloud

///動的にテーブル名を指定し縺?create table
パラメータや変数や_TABLE_FUFFIXだけでは難しい。変数はテーブル名とは解釈されない、_table_fuffix縺?select分縺?from句に入れwhere句で内容を指定するがcreate分は無理、execute immediateを用いる

DECLARE t STRING;

SET t = (SELECT CONCAT('x_emp_at', FORMAT_DATE("%Y%m%d", DATE_ADD(CURRENT_DATE(), INTERVAL 1 DAY))));

EXECUTE IMMEDIATE format('CREATE OR REPLACE TABLE `%s` AS SELECT * FROM `prj.bangboo_data.x_employee`', t);

ScheduledQueryでは出力テーブルの指定が可能でテーブル指定例：table001_{run_time-1h|"%Y%m%d"}縺?OK、なおSQL内に縺?run_timeが使用できない

//動的縺?SQLを作成し実行（組織レベルのメタデータを藹??得

DECLARE all_meta STRING;

SET all meta = (

with projects AS(

SELECT DISTINCT project_id from region-us.INFORMATION_SCHEMA.TABLE_STORAGE_BY_ORGANIZATION

WHERE project_id NOT IN ('対象外プロジェクト)

sql AS(

SELECT

CONCAT('select from`', project_id, "`.`region-us`.INFORMATION_SCHEMA.SCHEMATA_OPTIONS", "\nUNION DISTINCT\n') AS s FROM projects

concat_sql AS(

SELECT REGEXP REPLACE(STRING AGG(s, ''), '(UNIION DISTINCT+)$', '') AS concat_s

FROM sql

)

SELECT SUBSTR(concat_s, 1, LENGTH(concat_s) - 16) AS all_meta

FROM concat_sql

);

--Scheduled query化ならcreate文にする

--EXECUTE IMMEDIATE format('CREATE OR REPLACE TABLE `bq_us_all_dataset` AS %s', all meta);

EXECUTE IMMEDIATE format('%s', all_meta);

///既藹??のテーブルをコピ繝?(CREATE OR REPLACE TABLEもあり)

CREATE TABLE IF NOT EXISTS bangboo_data.x_employee_copy (

`no` INT64 NOT NULL,

`name` STRING,

) as

select * from `prj.bangboo_data.x_employee`

標準 SQL のクエリ觸??文 | BigQuery | Google Cloud

データ藹??義鐔??語ステートメントの使逕? | BigQuery | Google Cloud

///timestamp縺?datetime
datetime型カラムに縺?CURRENT_DATETIME()、timestamp型カラムに縺?CURRENT_TIMESTAMP()を使う
　timestamp縺?UTC、datetimeはローカル的で地域指定ができる
　直鐔??3分
　SELECT * FROM `aaa.ds.tbl111`
　WHERE `date` > DATETIME_SUB(CURRENT_DATETIME(), INTERVAL 3 MINUTE)

//string縺?date
func_approved_routine_a('2021-10-31')　引数がstring型
func_approved_routine_a("2021-10-31")　引数がdate型

///日臀??のキャスト
CAST(date AS STRING)
TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month))
BigQuery縺?StandardSQLで日臀??（date, datetime, timestamp）を変觸??する方觸?? - 寝ても覚めてもこんぴうた (hatenablog.com)
Bigqueryの日時に関臀??する関数全部試してみた ①Date邱? - Qiita

///timeで入っているものを日でサマるSQL

select

count(table_id),

sum(size_bytes),

date(record_time) as record_day

from bq_metadata

where record_time > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 3 month))

group by record_day

order by record_day DESC

///有効期限 table expiration
データセットに対して臀??日間かにするか設藹??できる

テーブルに対し特藹??の日臀??を設藹??できる
　何が起こる？＞データセット自臀??は觸??るが中のテーブルが無縺?なる

///パーティショ繝?

パーティション分割テーブルの觸??要 | BigQuery | Google Cloud
BigQuery縺?StandardSQLで日臀??（date, datetime, timestamp）を変觸??する方觸?? - 寝ても覚めてもこんぴうた (hatenablog.com)

パーティション分割テーブル縺?2種饅??：パーティショニングとシャーディン繧?

笳?シャーディングテーブ繝?
　カラムの藹??減OK、スキーマとメタデータを持ち権限検証されオーバヘッド有り、ワイルドカード誤操作しやすい→保存向き

笳?パーティションテーブ繝?
　クエリが早い、カラムの藹??減に対応できない、上限4000位→利用向き

笳?シャーディングテーブルにパーティション設定
　各シャーディン繧?tblでパーティションを持たせる、特觸??用途で通常どちらか縺?

TIMESTAMP 列縺?DATETIME列では、パーティションを時間単位、日単位、月単位、年単位のいずれ縺?

　SQで自動的縺?timestamp縺?DAYになる、SQ実行頻度から自動設藹??される?

ワイルドカード誤操作 *だ縺?_fuyou_20240401等の想藹??外も含むため_202*にする等の考慮が必要

シャーディングの臀??り方、yyyymmではダメだった、create文でテーブル名縺?yyyymmddを付けるあるい縺?SQのテーブル名縺?_{run_time-2h["%Y%m%d"}等

シャーディングはテーブルごとに権限を付荳?が必要で日臀??別なら実質無理でデータセットで権限管理が必要

クラスタリング　も同時に考慮したい

BigQueryのパーティションとクラスタリングについての解隱? - G-gen Tech Blog

BigQuery上のデータマートをクラスタ化したらクエリコストが９割カットできた隧? - エムスリーテックブロ繧? (m3tech.blog)

事前にソートし、まとまりを作っておく仕組み。

インデックスのようにカーディナリティが高いカラムを指定してあげると列指向のため全スキャンしな縺?て良縺?なる。圧縮軆??も上がり保存費用も削減できる。

WHERE で指定あるい縺? GROUP BY される複数列をクラスタ化列として指定するが、指定の順番が重要。

まずパーティションが考慮され、次に最初にクラスタ指定した列で鐔??がソートされ、次にその中縺?2番めに指定した列でソート、次縺?3番逶?...となる

CREATE TABLE ds.tbl_cls (purchase_dt DATE, prod_id STRING, prod_name STRING)

PARTITION BY purchase dt

CLUSTER BY prod_id

１）パーティショニン繧?
BigQueryでパーティション分割テーブルを作成する - goodbyegangsterのブロ繧? (hatenablog.com)　を見よ
パーティショニングは臀??前に臀??ってお縺?こ縺?
上限が4000のため最大日単位縺?11年、時間単位縺?5か月縺?らい縺?partition_expiration_daysも指定しておく事

CREATE TABLE sample.n225 (

trading_day DATE NOT NULL OPTIONS(description="藹??引譌?"),

closing_quotation NUMERIC NOT NULL OPTIONS(description="終蛟?"),

opening_quotation NUMERIC NOT NULL OPTIONS(description="始蛟?"),

high NUMERIC NOT NULL OPTIONS(description="高蛟?"),

low NUMERIC NOT NULL OPTIONS(description="菴?蛟?")

)

PARTITION BY

DATE_TRUNC(trading_day, MONTH)

OPTIONS (

partition_expiration_days=1825,

require_partition_filter=true,

friendly_name="日軆??225時系列デー繧?",

description="月別パーティションされた、201901から202107までの日軆??225時系列デー繧?",

labels=[("environ", "dev")]

)

クエリ縺?partitioned byのヤツで軆??れば良い

select * from aaa_history wehre

#ParticionIDで軆??る（つーかpartitioned byのヤツで日臀??をキャストし縺?UTCをJST日臀??縺?

date(rec_time) = date(datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour))
AND

#実際の時間で軆??る、パーティションが日臀??区切りなので時間検索だけなら全件検索になる

datetime(rec_time) between datetime_add(datetime "2000-10-10 00:00:00" interval -9 hour)
and datetime_add(datetime "2000-10-10 00:59:59" interval -9 hour)

２）シャーディン繧?
シャーディング縺?_TABLE_SUFFIXを使ったり、テーブル名に繝?ードコーディングする。

日臀??のキャスト select * from `task_*` where _TABLE_SUFFIX = REPLACE(CAST(date AS STRING), '-', '')

DROP TABLE `task_*`のようにワイルドカードは削除時は使えない
大驥?削除は臀??記のよう縺?bq cmdリストを作りBashで。（Terminal貼りつけでも可・??
　シャーディングはデータセット別にしてデータセットごと觸??すようにしたいが
Delete BigQuery tables with wildcard - Stack Overflow

select concat("bq rm --project_id prj -f -t ",table_schema,".",   table_name, ";" )
from INSERT_YOUR_DATASET_NAME.INFORMATION_SCHEMA.TABLES
where table_name like "INSERT_YOUR_TABLE_NAME_%"
order by table_name desc

シャーディングテーブルのビュー化 (Authorized view/routineの設定も必要)

■ その・??(_TABLE_SUFFIXカラムが付縺?が、全期間藹??得できる)

CREATE OR REPLACE VIEW ds.tablen_snapshot_all AS

SELECT *,

_TABLE_SUFFIX AS table_suffix

FROM gcp-prj-name.ds.tablen_snapshot_**

WHERE_TABLE_SUFFIX BETWEEN '20200101' AND FORMAT_DATE('%Y%m%d',

CURRENT_DATE())

↓下記のように使用する

SELECT FROM `ds.tablen_snapshot_all

WHERE table_suffix BETWEEN '20250530' AND '20250601'

あるい縺?

SELECT FROM tablen_snapshot_all

WHERE table suffix = '20250601'

■その・?? (テーブル関数のため単一日臀??のみ藹??得)

CREATE OR REPLACE TABLE FUNCTION ds.fn_tablen_snapshot_by_date(date_str STRING)

SELECT

FROM gcp-prj-name.ds.tablen_snapshot_**

WHERE TABLE_SUFFIX = date_str;

↓下記のように使用する

SELECT FROM `ds.fn_tablen_snapshot_by_date("20250601");

削除されたテーブル縺?7日以内なら復元することも可閭?
テーブルの管理 | BigQuery | Google Cloud

BQタイムトラベル縺?2-7日前のデータを見れる
タイムトラベルを使用した履歴データへのアクセ繧? | BigQuery | Google Cloud

///UNNEST
UNNESTを知らない縺?BigQueryを使えない？ | 4番は司令塔 (pep4.net)

Bigquery縺?UNNESTを使いこなせ！クエリ効軆??１００% | by Eureka Engineering

ARRAY を一組の鐔??にフラット化するには、UNNEST 演算子を使逕?
SELECT id, title FROM games, UNNEST(titles) AS title

id	titles
1	[skyrim, fortnite]
2	[atvvsmx, mario]

↓フラット化

id	title
1	skyrim
1	fortnite
2	atvvsmx
2	mario

ただしUNNESTで指定したカラムが空の配列やNULLの場合、該藹??行は無縺?なってしまうので注諢?
　id=3 titles=[]やid=4 titles=NULLの時縺?id=3,4は藹??っ張れないというこ縺?

select * from unnest(['aaa', 'bbb']) as baka -> rowとし縺?2行出る

select ['aaa', 'bbb'] as baka -> 1行目に配列として全て含まれ出る

sql - How to query multiple nested fields in Bigquery? - Stack Overflow
Unnestでもflattenができず空觸??ができる場合、結局left join
　空を含むカラム縺?Selectに觸??し、repeatedのカラム縺?left joinで縺?っつける
　VariantsをunnestしてるがPricesもrepeatedなの縺?left joinのものを出している
　　repeatedもarrayと同じらしいが、、、cross joinやarray_to_stringもやったが駄目だった
　　　なおrepeated以藹??縺?unnestが効かない

それでも駄目ならselect句の指定方觸??やwhere句で軆??ると空欄が抜けたよ

select Productid,Variants.SKU,Variants.Size
,Prices.Currency,Prices.Country
from `ga-export-0000.feed.feed_dev`
,UNNEST (Variants) AS Variants
LEFT JOIN UNNEST(Variants.Prices) as Prices

///ARRAY型縺?STRUCT型

標準 SQL のデータ型 | BigQuery | Google Cloud

とある古典的SQLおじさん縺?BigQuery入門：ARRAY型縺?STRUCT型 | DevelopersIO (classmethod.jp)
BigQuery縺?STRUCT型とうまく付き合う - Qiita

Arrayは臀??縺?Unnestを藹??照。
Structは觸??造体型。順序付きで親子の觸??造を持つ。各フィールドはデータ型（必須）とフィールド名（オプション・??を持つ。

array型　unnestできる、[]なの縺?array_length()で数が藹??れる

struct型　unnestできる、ネストを含みスキーマ縺?record型と表記される、struct型の藹??へ縺?.ドットで指定す
　string縺?JSON縺?json_extractを使う
　配列との絡み縺?json_query_arrayを使う、2段髫?縺?らいは関数で対処できるがそれ以臀??縺?with句がいい
　BigQueryでの鐔??雑縺?JSON文字列の扱い方と注諢?轤? - Qiita
　JSON functions | BigQuery | Google Cloud

CREATE TABLE IF NOT EXISTS `bangboo-prj.ds.x_list` (

`record_time` TIMESTAMP,

`name` ARRAY

)

INSERT INTO `bangboo-prj.ds.x_list` (`record_time`,`name`) VALUES (CURRENT_TIMESTAMP(),['a','b'])

struct型(record型)は藹??や孫でヒットすれば親を含めて表示されてしまう

見やす縺?するため*ではな縺?、カラムを特藹??すると空欄が表示されな縺?なり

親が出な縺?なり理解しやす縺?なる（必ずカラム指定したい）

Array=String Repeatedつまりリスト(配列)に値を入れる書藹??（下記縺?2つしか入らない）

insert into aaa (aaa) value ("['aaa','bbb']") value has STRING
insert into aaa (aaa) value (`['aaa','bbb']`) Unrecognized name: `['aaa','bbb']`
insert into aaa (aaa) value (['aaa','bbb']) OK
insert into aaa (aaa) value ('["aaa","bbb"]') value has STRING
insert into aaa (aaa) value (`["aaa","bbb"]`) Unecognized name
insert into aaa (aaa) value (["aaa","bbb"]) OK
insert into aaa (aaa) value ([`aaa`,`bbb`]) Unrecognized name
insert into aaa (aaa) value ([aaa,bbb]) Unrecognized name: aaa
insert into aaa (aaa) value ([123,456]) Value has type ARRAY

例）権限が変繧?っていないかの確認する等

降順で最新の日臀??のアイテムを見る、そして最終ページの古い日臀??のアイテムを見る

そしてそれらを比較する

select record_time, name, asset_type, m, b.role

from cai_iam_policy_history

,unnest(iam_policy.bindings) b

,unnest(b.members) m

where record_time between timestamp('2021-05-01') and timestamp('2021-06-30')

and b.role in ("roles/bigquery.dataViewer", "roles/bigquery/jobUser")

and m like '%ketsu@bangboo.com%'

and ancestor_path like '%ketsuproject%'

order by record_time desc

SQL解説）struct型が沢山入っていても全驛?unnestしfromに入れればいい

　from a, unnest(iam_policy.bindings) b, unnest(b.members) m

unnest(iam_policy)はできないの縺?2髫?層目から

　　一つ髫?層臀??で縺?unnest時に別名を付けて臀??の髫?層はその別名縺?unnest

struct型の藹??へ縺?.ドットで指定すればいい、フラットでな縺?てもbでも藹??得ができる
↑
通蟶?SQLは「陦?.カラム」だが「親カラム.子カラム」なので、出元がどこかテーブルを探すかスキーマ内を探すかで迷う

///json_extract, json_extract_scalar
2番目の藹??数はパ繧?
BigQueryでの鐔??雑縺?JSON文字列の扱い方と注諢?轤? - Qiita
標準 SQL 縺? JSON 関謨? | BigQuery | Google Cloud

with t as (
SELECT unco_data AS col_1 FROM `kuso`
WHERE date = "2021-08-04"
)
SELECT
json_extract(col_1, '$.color') as unco_color,
json_extract(col_1, '$.temperature') as temperature,

json_extract(col_1, '$.fart.times[0].stink') as first_stink,
FROM t

///Pivot
BigQuery縺?PreviewになったPIVOT縺?UNPIVOTを試す | DevelopersIO (classmethod.jp)
【SQL】クロス集計を扱う。PIVOT句縺?UNPIVOT句についてコードを雕?まえて解説。 | ポテパンスタイ繝? (potepan.com)
集計をして鐔??を列に藹??觸??（生ログをある単位でまとめカラムにする）

--tool縺?activeがonなら1、nullなら0でユー繧?A縺?Bの状觸??を見る
SELECT * FROM (
SELECT user, tool, active FROM `tools`

)
PIVOT(
MAX( IF (active IS NOT NULL, 1, 0))
FOR user IN ("a", "b")
)
↓
tool　a　b
------------
axe　1　0
sword　0　1

※藹??考にピボットテーブ繝?

集計して鐔??を列に藹??觸??、生ログをある単位でまとめる

　生ログが「日　店　金額」の場合

　↓

　ピボットで「日　金額　（店１　店２　店３）」にする等で、各項目を行と列と値に配置し直す

BigQuery縺?PreviewになったPIVOT縺?UNPIVOTを試す | DevelopersIO (classmethod.jp)
PIVOTの中は藹??数でないとだめだが、
Execute Immediate なら動的にイケる、
がGoogleSheet縺?ConnectedSheetではサポートされておらず無理という罠

///縦持ち横持縺?
pivotは集計関数を用いる、単純の入れ替えならSQLならこちら
[SQL]データの縦持ち、横持ちを入れ替える | DevelopersIO (classmethod.jp)

///新旧の差分

比較したいデータの共通してい部分で藹??部軆??合をし縺?null部分を探す

WITH

old_e AS (

SELECT * FROM status WHERE user IN ('a@old.com')

new_e AS (

SELECT * FROM status WHERE user IN ('a@new.com')

)

SELECT * FROM old_e o

FULL OUTER JOIN new_e n ON o.id = n.id AND o.date = n.date

WHERE o.id is null OR n.id is null

ORDER BY o.id, o.date

union縺?except distinctをSQLを付けると差分になる

https://qiita.com/tatsuhiko_kawabe/items/2537c562c6d99f83e37b

SELECT * FROM item.item_table

EXCEPT DISTINCT

SELECT * FROM item.item_table WHERE user_id = 'A'

　1つ目の軆??果から2つ目を引いたものを出す

///REGEXP_REPLACE 正鐔??表現で文字を削髯?

WITH markdown AS

(SELECT "# Heading" as heading

UNION ALL

SELECT "# Another Heading" as heading)

SELECT

REGEXP_REPLACE(heading, r"^# He", "") AS html

FROM markdown;

標準 SQL の文字列関謨? | BigQuery | Google Cloud

///スラッシュで分割する縺?arrayになるのでオフセットで藹??得

select SPLIT(path, "/")[OFFSET(3)] from www

スラッシュの最後を藹??る

ARRAY_REVERSE(SPLIT(aaa, "/"))[SAFE_OFFSET(0)]

引き当てが無い場合縺?SAFE_OFFSET縺?Nullを返し、OFFSETはエラーを返す

BigQueryの觸??準SQL縺?GROUP_CONCATしたいとき縺?STRING_AGG - GAミント至臀??主義 (hatenablog.com)
逆にまとめるに縺?

SELECT type, STRING_AGG(DISTINCT name) FROM testData GROUP BY type;

赤身 | ブ繝?,い繧?し,ア繧?,マグ繝?,カツ繧?,サバ

白身 | タ繧?,タ繝?,フ繧?,サ繧?

///Job kill
CALL BQ.JOBS.CANCEL('job_id')
CALL BQ.JOBS.CANCEL('project_id.job_id')

job idでエラー詳細を確鐔??
bq show -j

bq show --project_id bangboo_sandbox --format json -j bqjobidxxxxxxxxxx | jp .

　job idはコンソール縺?BQのジョブ詳細やスクリプトキックならロギングから見つけてもいい

　クエリならjob/query historyで繧?かるがbq cmdでもエラーが返る
　　bq query --nouse_legacy_sql 'select ketsu from `prj`.oshi.ri'
　　unrecognized name: 'kusofuke@ketsu.com' at [1:149]

select * from prj.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT

where job_id ="aaaaa" and creation_time > "2022-01-01"

ジョブIDの藹??得
SELECT
project_id,
job_id,
user_email,
creation_time,
start_time,
--query,

total_slot_ms
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_FOLDER
--`region-us`.INFORMATION_SCHEMA.JOBS_BY_ORGANIZATION
WHERE state != "DONE"
--state = "RUNNING"
--state = "PENDING"
AND user_email = 'my@email.com'
AND project_id = 'paa'
AND start_time < TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 3 MINUTE)
AND total_slot_ms > (1000 * 30)
AND PARTITIONDATE BETWEEN '2021-01-01' AND '2021-01-02'
--PARTITIONTIME BETWEEN TIMESTAMP('2021-01-01') AND TIMESTAMP('2021-01-02')

///upsert(アップデートか新鐔??インサート

https://swfz.hatenablog.com/entry/2021/02/08/195024

MERGE aaa target USING tmptbl src

ON target.time = src.time

WHEN MATCHED AND src.satus = 'rejected' THEN

DELETE

WHEN MATCHED THEN

UPDATE SET ...

WHEN NOT MATCHED THEN

INSERT ROW

///window関謨?

集約関数・??GROUP BY）だと個別データは出力されず集計データだけでるが

window関数だと集計データが個別データ縺?outer joinされた形で出力される

分析関数のコンセプト | BigQuery | Google Cloud

SELECT

deptname,

id,

salary,

AVG(salary) OVER (PARTITION BY deptname)

FROM emp;

deptname | id | salary | avg_salary

-----------+-------+--------+-------------

dev | 11 | 5200 | 5020

dev | 7 | 4200 | 5020

dev | 9 | 4500 | 5020

dev | 8 | 6000 | 5020

dev | 10 | 5200 | 5020

hr | 5 | 3500 | 3700

hr | 2 | 3900 | 3700

sales | 3 | 4800 | 4866

sales | 1 | 5000 | 4866

sales | 4 | 4800 | 4866

deptnameでグループしそ縺?salaryの集計縺?AVGが出ている
下のよう縺?over()が空でも良い、4900は大体

SELECT

deptname,

id,

salary,

AVG(salary) OVER () AS avg

FROM emp;

deptname | id | salary | avg

-----------+-------+--------+-------------

dev | 11 | 5200 | 4900

dev | 7 | 4200 | 4900

dev | 9 | 4500 | 4900

dev | 8 | 6000 | 4900

dev | 10 | 5200 | 4900

hr | 5 | 3500 | 4900

hr | 2 | 3900 | 4900

sales | 3 | 4800 | 4900

sales | 1 | 5000 | 4900

sales | 4 | 4800 | 4900

関数としては集計関数がそのまま使えるようだ

OVER縺?window関数を使う宣鐔??、OVERの藹??にどのよう縺?windowを作るのかを定鄒?

PARTITION縺?windowでつまりどの軆??囲でグループを作るか指定
　AVG(salary) OVER (PARTITION BY deptname, sub_deptname) でサブデプト単位での平均となる

///誰が実行しているかをセッションユーザで出す
標準 SQL のセキュリティ関謨? | BigQuery | Google Cloud

SELECT SESSION_USER() as user;

+----------------------+

| user |

+----------------------+

| jdoe@example.com |

+----------------------+

///エラー繝?ンドリン繧?

BQのクエリ内の条件によりerror()でエラーが吐ける

　select error('id is not unique.') from tbl having count(a) > 1
　ERROR関数を使ったBigQueryデータ異常検知臀?? #BigQuery - Qiita
　SQだとメール送信したり、ロギングやモニタリングでエラー觸??知できる

///プログラムで使う

https://googleapis.dev/python/bigquery/latest

from google.cloud import bigquery

client = bigquery.Client()

QUERY = ('SELECT name FROM `bigquery-public-data.usa_names.usa_1910_2013`')

query_job = client.query(QUERY)

rows = query_job.result()

for row in rows:

print(row.name)

///Pythonも含めトランザクショ繝?

/// BANGBOO BLOG /// - GCP script

■saturationの場合、詰まっている、サチっている
対象にクエリを発鐔?? select 1
同プロジェクトの臀??のテーブルにクエリを発鐔?? select 1
別プロジェクトから対象にクエリを発鐔?? select 1
reservations縺?overviewを見る
対象縺?SQLを発鐔??
別のプロジェクトで同SQLを発鐔??
　時間を比べる
Google側の問題と思繧?れるとき縺?Googleのサポート縺?Go
Google Could Status Google Cloud Status Dashboard

INFORMATION_SCHEMA < Audit log で調譟?

メタデータ・??データに対するデータ・??

　システムメタデータ・??作成更新日時、サイズ、誰いつ藹??辣?

　ビジネスメタデータ・??オーナ、更新頻度、カラムの諢?蜻?

select * from prj.ds.INFORMATON_SCHEMA.TABLES

select * from prj.ds.INFORMATON_SCHEMA.PARTITIONS

　longterm storageでサイズが100000b以臀??で、更新日が1か月以臀??とか出せる

select * from prj.ds.INFORMATON_SCHEMA.COLUMNS where column_name like '%kuso%'

select * from prj.ds.INFORMATON_SCHEMA.VIEWS where view_definition like '%kuso_table%'

　view_definition縺?SQL文が入っている

select * from prj.ds.INFORMATON_SCHEMA.JOBS_BY_(USER / PROJECT / ORGANIZATION)

　誰アクセ繧?/誰臀??った/何Job等も分かる、180日しか出せないが

　roles.bigquery.resourceViewerが必要

　カラム例：user_email、query、referenced_tables

Auditlogは　プロジェクト間で使用されるBQでも情報が藹??れる

　info_schema縺?jobs_byとほぼ同じ内容が藹??れるがよりリッチ

　　利用ユーザ数、旧データを見ている人、権限変更操作ログ軆??

///Authrized function
認可觸??み関数の臀??成 | BigQuery | Google Cloud

SELECT `b-sandbox`.test_ds.count_row(1);　で藹??行できる

UDFやテーブル関数のルーティンを承鐔??してお縺?と誰からでも使える（ビューと違い権限管理できずセキュリティがズブズブになると思繧?れ）

　target_prj.trg_dsに藹??け入れる関数を共有指定する蠖?

　UDFは戻り値がある、テーブル関数は副蝠?い合繧?せとして使う形か

///ScheduledQueryの藹??行者

コンソールの場合：コンソール操作者

Terraformの場合：Terraform実行者

bqコマンドの場合：任諢?に設定ができる

サービスアカウントをbqコマンド縺?SQ実行者として登録する場合、通常は問題がないがスプレッドシートを使用するなら@プロジェクト名.iam.gserviceaccount.com等でアクセス権が必要なため、会社のポリシーによってはうまく行かない。batch@unco.comのような共通メールを作成し使用したい。（GWS側縺?OUを使いTrusted ruleによりSA縺?GoogleDriveへアクセス許可すると問題回避できるが：OUをつ縺?りそ縺?OU内で藹??用共有ドライブを作成し設藹??する）

サービスアカウント縺?ScheduleQueryを実行させる設藹??に藹??要な権限

https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja

設藹??操作者

　BQ job user（クエリ臀??成ができない）

　BQ transfers.get/update

　BQ data viewer/editor

　iam ServiceAccountUser(対象SA、PRJレベルでも良いが広縺?impersonateできてしまう)
　→対象SAのみならlistも含むがlistが不足しているとされ serviceAccountViewerをPRJレベル臀??荳?も必要

　保存先DS縺?BQ admin等へ縺?setiam系が2026/3から必要

サービスアカウント

　BQ job user

　BQ data viewer/editor
　窶?BQ transferは臀??要だった

Scheduled queryからの臀??存先
コンソールだと同じプロジェクト内だが、create文を自由鐔??載ならどこでもOK
job userは同じプロジェクトの権限が必要

設藹??者一覧を出したい場合

bq --format=json --project_id=bangboo-oketsu ls --transfer_config --transfer_location=us | jq.[].name

bq --format=json show --transfer_config project/1111111/locations/us/tranferConfigs/111111 | jq .ownerInfo.email

■SQLはカラム数の藹??加数で觸??成考える？
left outer joinはカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数縺?leftに同じ）
　full outer join はカラム数がカラム数の合計から共通縺?join onのカラム数を引いた数・??行数縺?leftの鐔??素数縺?rightの鐔??素数を合計したもの・??

unionは重複を除藹??し表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）

unian allは重複を除藹??せず表を足し合繧?せるため行数が両表の合計行数・??カラム数は合致藹??要でカラム数は藹??繧?らない）

cross joinはカラム数が両表のカラム数の合計、行数は両表の鐔??数の觸??け算

　再帰的縺?SQL処理縺?cross joinし条件を付けるか？

　　標準SQL縺?From句のカンマ縺?cross joinとなる
with句は副蝠?い合繧?せを見やす縺?したも縺?
distinct縺?(組み合繧?せ縺?)一諢?になる行のみにし重複を省縺?
UNION 縺?UNION ALLの違い - Qiita
CROSS JOIN （クロス軆??合）を使ってデータを藹??得する - JOIN （結合）を使いこなそう - SQL Server 入門 (sql55.com)
SQL縺?DISTINCTとは・??（OracleやMySQLで使用する方觸??） | IT職種コラム (it-kyujin.jp)

デカい表をい縺?つか持縺?JOINすると再帰的縺?Where句で条件検索しな縺?てよい

■課金

クエリ課金：使用しているプロジェクトで課金される、データの置き場所ではない
　定額フラット：$2000/100slot/m(全プロジェクトでスロットを共有)、オンデマンド：$5/T=2Gスキャン縺?1円位
　　flat rateでもflex slotsとして時間帯によりスロットを増やす等ができる
　　　Editionsに藹??更になった：組織縺?5プロジェクト等しかreservationを置けない、その中で限りなく設藹??ができる

　課金を減らすには・??カラムを減らす、パーティショニン繧?
　　プレビューを活用・??しかしビューだとプレビュー觸??能はない。列が501列以臀??あったら501列以降はプレビュー出ない
データ臀??管課金：データ驥?
　$1/50G/m

　active storageからlong term storageへの移行は自動(90日藹??更がない、50%off)
6,000スロットを使うBigQueryのリソース配分最適化への挑謌? (plaid.co.jp)

■定額蛻?Editions

スキャンサイズが大き縺?てコンピューティングが少ないならリザベーショ繝? (Editions)が向いている、スキャンサイズが蟆?さ縺?てコンピューティングが多いならオンデマンドが向いている

スロット消費驥?=データ驥?とコンピューティン繧?

１）データ驥?: Read驥?/スキャン驥? (スキャン驥?が多縺?ても単純クエリならスロット消費が少な縺?単純な觸??例ではない)

２）コンピューティング鐔??闕?: CPU/メモリ觸??雋? (結合/集合/フィル繧?/ソート/大驥?JOIN/複雑なウィンドウ関数軆??で重いクエ繝?)

■オンデマンド

必要なクエリ以外は別の藹??額制のプロジェクトで藹??行するよう変譖?

オンデマンドはスキャンしたデータ驥?で料金が決まる

　実行しているクエリのスキャン驥?で料金を見積もる

パーティション、クラスタリングでスキャン驥?を減らす

　BigQueryのパーティションとクラスタリングについての解隱? - G-gen Tech Blog

SELECT縺?NG。必ず必要な列だけを譏?示する。

スキャン驥?のモニタリングとアラート設藹??を実施する。

同じクエリならキャッシュが利縺?ため定額のエディションが有蛻?(24h)

　ソースの更新軆??でキャッシュが無効になるよ

　BigQueryでクエリのキャッシュを回避する方觸?? | GLASS BLOG (キャッシュされない条件)

　Bigquery キャッシュについ縺? #GoogleCloud - Qiita (キャッシュ設定/有効無蜉?

　キャッシュに臀??存されているクエリ軆??果の使逕? | BigQuery | Google Cloud

■権限
事前定義ロールと権限 | BigQuery | Google Cloud
job user：select文クエリ藹??行だけでもジョブ縺?job user縺?data viewerが要る（data viewerだけでは臀??足・??
　課金プロジェクト縺?job userを持ち、藹??照先プロジェクト縺?data viewerを持つという権限構成だから
　例え縺?job userがな縺?data ownerだけの場合はデータセットやテーブルやビューの削除臀??成ができるが、データロードやselect文発鐔??はできない
IAMかデータセット/tblに藹??要な権限を付荳?する
　data editorでも自分で臀??成したものは自分がOwnerになり削除や変更権限がある
meta data viewerならDSとテーブル臀??覧、テーブルのスキーマや容量等の情報が見れデータは鐔??れない
　これを広く付けてお縺?とデータ管理が讌?

■サービスアカウントに対するBQ job user

コンソールであれば画面左上の鐔??求先プロジェクトで切り替えができるが

スクリプトであれ縺?gcloud auth login時に切り替える

gceならインスタンス縺?SA設藹??するが

　請求先プロジェクトのデフォルトはインスタンスの置いている/SAが作成されたPrj
　　※同プロジェクトからしか選択ができない

　コード上で切り替えができる

　　bq --project_id=xxx query 'select count(*) from ds.tbl'

/// BANGBOO BLOG /// - BigQuery part2

Posted by funa : 01:00 AM | Web | Comment (0) | Trackback (0)