/// BANGBOO BLOG ///

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31


February 21, 2021 List
BigQuery on Feb 21, 2021 1:00 AM

February 21, 2021

BigQuery
■Big queryリファレンス
標準SQLとレガシーSQLがある、違いは？
標準 SQL のクエリ構文  |  BigQuery  |  Google Cloud
標準 SQL への移行  |  BigQuery  |  Google Cloud
標準 SQL のデータ型  |  BigQuery  |  Google Cloud
レガシー SQL 関数と演算子  |  BigQuery  |  Google Cloud
レガシー SQL のデータ型  |  BigQuery  |  Google Cloud
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
分析関数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に抽出)

■データアップロード時のスキーマ指定
自動検出は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`
データ定義言語ステートメントの使用  |  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"}等
シャーディングはテーブルごとに権限を付与が必要で日付別なら実質無理でデータセットで権限管理が必要

クラスタリング　も同時に考慮したい
事前にソートし、まとまりを作っておく仕組み。
インデックスのようにカーディナリティが高いカラムを指定してあげると列指向のため全スキャンしなくて良くなる。圧縮率も上がり 保存費用も削減できる。
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 -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

削除されたテーブルは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
idtitles
1[skyrim, fortnite]
2[atvvsmx, mario]
↓フラット化
idtitle
1skyrim
1fortnite
2atvvsmx
2mario

ただし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型
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された形で出力される

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だとメール送信したり、ロギングやモニタリングでエラー検知できる

///プログラムで使う
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

///承認済みビュー authorized view
authorized viewを設定するとそのviewを対象とする権限だけ必要で権限をさかのぼり付与しなくていい（通常のviewは参照元の権限も必要）
　この権限移譲は閲覧権限のみで編集権限等は含まない
被参照の元テーブル側に許可するview名を設定する
　参照権限は緩くなるが、編集権限は厳しくなる（設定するビューは変更しない前提で承認する形）
authorized viewを付与すると玄関となったビューはdataEditorではビュー更新ができなくなる
　玄関ビューにも、ソーステーブルにもEditor権限が必要
　基本の安全策はauthorized view設定を外す＞ビュー変更＞AV再設定がいい
　　対象のauthorized viewは管理者を立て一元管理するのが良さそう
　　（テーブルやビューを作って権限付与してバッチだとdata ownerが必要なのは注意）

■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とほぼ同じ内容が取れるがよりリッチ
　　利用ユーザ数、旧データを見ている人、権限変更操作ログ等

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レベルでも良いが)
サービスアカウント
　BQ job user
　BQ transfer
　BQ data viewer/editor

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

■BQ transfer(クエリが不要なBQ連携、3rd partyもあり)
データセットコピー、GCSファイル
Ama S3, Azure storage, Oracle, Salesforce, Ads系等々

■Cloud SQLにBQからクエリ
SELECT * FROM EXTERNAL_QUERY("connection_name", "SELECT * FROM db.tbl")
https://zenn.dev/ykdev/articles/4a4d2fbc6b4fe1

■BQ DMLクォータ超過
割とSQLだとすぐに壁にあたる
上限がテーブル単位のためテーブル名を分けると回避できるらしい
BQ streaming insert->BQ storage read/write APIの上限はDMLと別で、閾値が大きい

APIだとProtocol buffersが必要で、Date/Timestampが対応しておらず
Unixエポックからの日数/秒数への変換が必要、、、

■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)

■権限
事前定義ロールと権限  |  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'

■BigQueryの列レベル・行レベルのセキュリティ
個人情報や機微情報を隠す
BigQuery の行レベルのセキュリティの概要  |  Google Cloud
行レベルなら同じテーブルを使うので同じダッシュボード/Appが使える（AuthorizedViewの方が柔軟だが）
データ マスキングの概要  |  BigQuery  |  Google Cloud
列レベルアクセス権以外にもマスクの種類があり、ハッシュだったり先頭4文字や末尾４文字等で共通文字化としてマスク化できる
BQ画面＞左ナビのポリシータグ
　ポリシータグを作成（組織単位で一括一覧表示）
　　タグは階層化できるので、全ユーザタグ＞管理者タグ＞社長タグ
　スキーマ＞Addポリシータグ
　　タグが付いていればプレビューで見れない
　　select * except(tag_column)にする必要がある
　　メタデータは見れる（カラム名、型
　ポリシータグ画面＞対象ポリシー選択＞情報パネルで権限者一覧
　　fine-grained readerを付与するとselect *ができるようになる
　　　社長タグに社長だけ権限付ける等
※APIを有効にし、ポリシーを有効にする必要がある


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


PhotoGallery


TWITTER
Search

Mobile
QR for cellphone  QR for smart phone
For mobile click here
For smart phone click here
Popular Page
#1Web
#2Hiace 200
#3Gadget
#4The beginning of CSSレイアウト
#5Column
#6Web font test
#7Ora Ora Ora Ora Ora
#8Wifi cam
#9みたらし団子
#10Arcade Controller
#11G Suite
#12PC SPEC 2012.8
#13Javascript
#14REMIX DTM DAW - Acid
#15RSS Radio
#16Optimost
#17通話SIM
#18Attachment
#19Summer time blues
#20Enigma
#21Git
#22Warning!! Page Expired.
#23Speaker
#24Darwinian Theory Of Evolution
#25AV首相
#26htaccess mod_rewite
#27/// BANGBOO BLOG /// From 2016-01-01 To 2016-01-31
#28竹書房
#29F☆ck CSS
#30Automobile Inspection
#31No ID
#32Win7 / Win10 Insco
#33Speaker
#34Arcade Controller
#35Agile
#36G Suite
#37Personal Information Privacy Act
#38Europe
#39Warning!! Page Expired.
#40GoogleMap Moblile
#41CSS Selectors
#42MySQL　DB　Database
#43Ant
#44☆od damnit
#45Teeth Teeth
#46Itinerary with a eurail pass
#47PHP Developer
#48Affiliate
#49/// BANGBOO BLOG /// From 2019-01-01 To 2019-01-31
#50/// BANGBOO BLOG /// From 2019-09-01 To 2019-09-30
#51/// BANGBOO BLOG /// On 2020-03-01
#52/// BANGBOO BLOG /// On 2020-04-01
#53Windows env tips
#54恐慌からの脱出方法
#55MARUTAI
#56A Rainbow Between Clouds‏
#57ER
#58PDF in cellphone with microSD
#59DJ
#60ICOCA
#61Departures
#62Update your home page
#63CSS Grid
#64恐慌からの脱出方法
#65ハチロクカフェ
#66/// BANGBOO BLOG /// On 2016-03-31
#67/// BANGBOO BLOG /// From 2017-02-01 To 2017-02-28
#68/// BANGBOO BLOG /// From 2019-07-01 To 2019-07-31
#69/// BANGBOO BLOG /// From 2019-10-01 To 2019-10-31
#70/// BANGBOO BLOG /// On 2020-01-21
#71Bike
#72Where Hiphop lives!!
#73The team that always wins
#74Tora Tora Tora
#75Blog Ping
#76無料ストレージ
#77jQuery - write less, do more.
#78Adobe Premire6.0 (Guru R.I.P.)
#79PC SPEC 2007.7
#80Google Sitemap
#81Information privacy & antispam law
#82Wifi security camera with solar panel & small battery
#83Hope get back to normal
#84Vice versa
#85ハイエースのメンテ
#86Camoufla
#87α7Ⅱ
#88Jack up Hiace
#89Fucking tire
#90Big D
#914 Pole Plug
#925-year-old shit
#93Emancipation Proclamation
#94Windows env tips
#95Meritocracy
#96Focus zone
#97Raspberry Pi
#98Mind Control
#99Interview
#100Branding Excellent
Category
Recent Entry
Trackback
Comment
Archive
<     February 2021     >
Sun Mon Tue Wed Thi Fri Sat
1 2 3 4 5 6
7 8 9 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28
Link