■BQ同時実行数

オンデマンドでは使用可能なスロット数に基づき自動的に同時実行数が決定され超えるとスロットに 空きがでるまでキューに保管される

プロジェクトごとにクエリの最大同時実行数は動的に決まる

同時実行数の最大値は指定できるが、大きくしても実行数が増えることはなく、あくまで自動決定 内の方が優先される

Editionsが割り当てられているプロジェクトでは最大同時実行数を明示的に設定できる

他に、インタラクティブクエリ、バッチクエリ、クエリ順序や上限など

■BQクリーンルーム

データ準備側でパブリックし、使う側でサブスクする (BQ exploreでペインでAddする)

スプシ保存できない、開覧数のレポートが見れる(使用者名は見えない) 実はパブ側でサブスクし公開すれば、閲覧とJobUserだけで使用できるようになる

GAでなく、またオンデマンドしか無理、コピペやデータコネクタは可能で残念

■ロール割り当て者の出力
カスタムロールのProject_Admin、Project_Managerが誰に割り当てられているか

■IAM(Identity and Access Management)

　　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`

バッククォートでエスケープ、プロジェクト区切りも.(ドット)、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結果を挿入できる

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

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

通常の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と同様？)

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,

)

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

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

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

■データアップロード時のスキーマ指定
自動検出は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

ScheduledQueryを終了日6/9 13:00JSTで即時設定→6/9 01:20UTCで実行された

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

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

範囲の書き方:シート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

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'

　※ワイルドカード注意

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

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

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

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

///既存のテーブルをコピー(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`

///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

パーティション分割テーブルは2種類：パーティショニングとシャーディング

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

日付のキャスト 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

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


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

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は「表.カラム」だが「親カラム.子カラム」なので、出元がどこかテーブルを探すかスキーマ内を探すかで迷う

※参考にピボットテーブル

集計して行を列に変換、生ログをある単位でまとめる

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

　↓

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

///新旧の差分

比較したいデータの共通してい部分で外部結合をして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つ目を引いたものを出す

WITH markdown AS

  (SELECT "# Heading" as heading

  UNION ALL

  SELECT "# Another Heading" as heading)

SELECT

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

FROM markdown;

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

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

///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

集約関数（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は大体

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

OVERはwindow関数を使う宣言、OVERの後にどのようにwindowを作るのかを定義

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

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

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
■saturationの場合、詰まっている、サチっている
対象にクエリを発行 select 1
同プロジェクトの他のテーブルにクエリを発行 select 1
別プロジェクトから対象にクエリを発行 select 1
reservationsのoverviewを見る
対象のSQLを発行
別のプロジェクトで同SQLを発行
　時間を比べる
Google側の問題と思われるときはGoogleのサポートへGo
Google Could Status Google Cloud Status Dashboard

パッケージとバージョンを指定してアップデート

pip install -U google-cloud-bigquery==3.4.0

下記にもろもろ
pipでパッケージをupdate｜pip自体のアップデートについても解説 (itc.tokyo)

pyenv　パイソンバージョン管理としてインスコ
ubuntu 20.04 / 18.04 に pyenv をインストールする話 #Python - Qiita
pyenvのインストール、使い方、pythonのバージョン切り替えできない時の対処法 #Python - Qiita

　pyenv install --list　インストールできるもの

　pyenv install 3.8.8　指定verをインスコ

　pyenv global 3.8.8　デフォルトに指定

　.python-versionファイルをGITに載せ管理したい？

pipenv　PIP管理としてインスコ
Pipenvでの基本的な環境構築 - Pirika Developers Blog

　pipenvはPipfileとPipfile.lockを利用しpipでrequrements.txtを用いるよりも強力

　　PipfileとPipfile.lockとrequirementsをGITに載せ管理したい？

　pipenv --python 3.8.8　など最初にpyバージョンをpipfileに記載

pipenvのバージョンが古いと依存関係、Ver整合性で問題が起きやすい

　pipenv --version

　pip install pipenv

　pipenv update

　pipenv upgrade <パケ>でやり直す

assert文は組み込み定数__debug__がTrueの時のみ実行されます

実行コマンドにオプションに-Oをつけると__debug__がFalseになりassert文が無効に

def func_so(a, b):

def test():

if __name__ == "__main__":

import dataclasses

[詳解] Pythonのdataclasses (zenn.dev)
　データ格納するオブジェクトを作れば使い回しが楽

import datetime

pip install pyyaml > import yaml

pip install requests > import requests

　SlackのwebhookにPostするとか
Python 3.12で増えた並列処理と、これまでの並列処理の挙動を比べてみる #Python - Qiita

文字列[開始:終了]→→ str = "Flower" print(str[1:4]) → low

文字列 % (値1, 値2, ...)→→ num= "10進数では %d 、16進数では %x " % (num, num)
"xxxx{index:書式指定子}xxxx".format(値)→→ "名は{:<8s}で年は{:>3d}で".format(name, age)

f"xxxx{値:書式指定子}xxxx"→→ f"名は{name:<8s}で年は{age:>3d}で" 

0／空の文字列''／値なしはfalse、Noneは？ x = None x is None→→true？

//→除算切り捨てし整数、**→べき乗
関数宣言はdef kansu(): で中で宣言する変数はローカル変数
　関数外で宣言された変数はグローバル変数でどの関数の中でも扱えるようになる
　なお関数内でもglobal henでグローバル変数を宣言できる　Pythonでのグローバル（global）変数の宣言方法 | UX MILK
返り値複数はcsvでタプルになる、リストが楽か？ return a,b → (a, b)　あるいは return [a, b] → [a, b]
try／exceptを関数内で設定することも、逆に関数呼び出し時にも使用ができる、else, finally, raiseも使う、エラーが出ても止めたくない場合は try-except Exceptions as e、逆にexceptを入れなければ止まるので安全置換は左辺が要る？要る a = a.replace('x','')
とほほのPython入門 - リスト・タプル・辞書 - とほほのWWW入門 (tohoho-web.com)
Pythonの辞書とリストとクラス　複数情報の受け渡し｜みはみ｜note
リストa=[1,2,3]はmap(), filter(), reduce()等が使える
　a=a.append()とかa=a.extend()は値がないんで駄目、単純にappend(b)やextend(b)で左辺不要
　取得：a[0]、for v in a:
　リストの合体：list_a += list_b
セット型set={1,2,3}は重複や順序や添字の無いリスト、set(list)でキャストし重複を無くせる、ミュータブルは格納できない
　取得 for v in a:
tuple→タプルは定数リスト、更新無しならリストより速い　a = 1,2,3　a = (1, 2, 3)
　取得：a[0]、for num in a:

dict→辞書は連想配列みたいな{a:1,b:2}はitems(), keys(), valus(), iteritems(), get()を使える
　Python | 辞書に含まれるすべてのキーと値を取得する (javadrive.jp)
　取得：dict_a['key1']、for k in dict_a.keys(): for v in dict_a.values(): for k, v in dict_a.items():
　dictの合体：dict_a.update(dict_b)
クラス→例えば●●クラスを宣言しsampleインスタンスを生成し、getter/setterで変数に入れて置く
　取得：sample.key
BigQuery→別名を付ければ名前で取得できるが、インデックスでも取得できる（これ何？）
　取得：for row in query_job:　→row[0], row["t"]

withで終了処理を指定できる、ファイル読込とその後の処理とか
assertや__debug__はテストで機体通りかを確認する？
passは中身の無い関数やクラスを作成しkara.p=1で粋なり属性追加等ができる
execは引数の文字列をPythonとして実行　exec "print 'Hello'"
delはオブジェクトを削除　del x, y, z
【基礎一覧】Pythonの基本文法を全て解説してみた！【初心者】 (suwaru.tokyo)
Python基本文法まとめ - Qiita
とほほのPython入門 - とほほのWWW入門 (tohoho-web.com)

半角スペース２個で改行

#の数で見出し

*で箇条書き

数字と.で番号を振る、- でリスト

* or - or _ を３つ以上で水平線

[ ]でチェックボックス、[x]でチェック

| td | td | td |でテーブル

**aaa**で太字、*aaa*で斜体

~~aaa~~で打消し線

[タイトル](URL)でリンク

```でコードの挿入、`でインライン挿入

> or >> で引用
 [^1]で注釈

\バックスラッシュでマークダウンのエスケープ

マジックナンバーを使わない(数字の方が曖昧性が無い場合も)

STATUS_ERROR = -1

STATUS_SUCCESS = 0

self.status_error = STATUS_SUCCESS

初期値をエラー値にし、業務判定エラーでステータスを設定したらreturnで抜ける

def exFunction(self):

self.status_error = STATUS_ERROR

try:

if XX = AAA:

self.status_error = STATUS_XX_ERROR

retrun

self.status_error = STATUS_SUCCESS

retrun

except:

～エラー処理、ステータスは変更しない

エラーメッセのハードコーディングを避ける方法(ハードが場所と内容が分かり易いかも)

MSG_ERROR_OLD_EMAIL = "Error: 旧メール%sです\n"

e_message_list.append(MSG_ERROR_OLD_EMAIL % (old_email))

self.error_message = '\n'.join(e_message_list)