protoPayload.request.policy.bindings.role="organizations/123456/roles/unco"

protoPayload.methodName : "Setlam"

--操作者

--protoPayload.authenticationInfo.principalEmail="kuso@dayo.com"

変更の詳細は下記当たりに出る（デルタは変量の意味と思われる）
protoPayload.metadata.datasetChange.bidingDeltas
protoPayload.serviceData.policybindingDeltas

/// BQテーブル、ビュー作成
protoPayload.methodName="google.cloud.bigquery.v2.TableServiceInsertTable"

resource.labels.instance_id="12234567898"

severity=WARNING

例えばGKE使用しているアセット情報（GKE cluster）

gcloud asset search-all-resources \

--scope=organizations/組織の数字ID \

--asset-types=container.googleapis.com/Cluster

１）ユーザー管理サービス アカウント例

　service-account-name@project-id.iam.gserviceaccount.com

　プロジェクト名がサブドメインについている

２）デフォルトのサービス アカウント例

　project-number-compute@developer.gserviceaccount.com

３）Google マネージド サービス アカウント

　３－１）サービス固有のサービス エージェント例

　　service-PROJECT_NUMBER@gcp-sa-artifactregistry.iam.gserviceaccount.com

　３－２）Google API サービス エージェント例

　　project-number@cloudservices.gserviceaccount.com 

　３－３）Google 管理のサービス アカウントのロール マネージャー例

　　service-agent-manager@system.gserviceaccount.com
１は所有プロジェクト名で判断できる、それ以外はdeveloperやgcp-やcloudservicesやsystem
３－１、３－２はSAだがサービスエージェントとも言われる

■GCPディスク容量
コンソールからディスクに入り編集で容量追加

　公開イメージのブートは自動変更される

　カスタムイメージや非ブートディスクは手動変更が必要

　手動方法は下記参照

/// BANGBOO BLOG /// - Linux cmd




■IAM>PAM
一時付与の権限申請ができる


■ZOZOの新米Google cloud管理者
新米Google Cloud管理者の奮闘記のその後 〜Organizationの秩序を維持する試み〜 - ZOZO TECH BLOG

■データ
次世代データ基盤：データレイクハウスを Google Cloud で実現する (zenn.dev)

• BigQuery Data Transfer Service：
  Cloud Storage や Amazon S3、Azure Blob Storage など格納されているデータを BigQuery に転送するマネージド サービスです。主に構造化データや半構造化データをバッチ処理にて BigQuery へ直接転送したい場合に使用します。
• Storage Transfer Service：
  Cloud Storage、Amazon S3、Azure Storage、オンプレミス データなどに格納されているオブジェクトやファイルを Cloud Storage へ転送するマネージド サービスです。主にオブジェクトやファイルをバッチ処理にて Cloud Storage へ直接転送したい場合に使用します。
• Datastream：
  Oracle、MySQL、PostgreSQL といったデータベースのから BigQuery に対してシームレスにデータを複製できるサーバーレスな変更データ キャプチャ（CDC）サービスです。データベースの内容をリアルタイムに BigQuery へ反映したい場合に使用します。
• Cloud Data Fusion：
  フルマネージドのデータ パイプライン構築サービスであり、ノーコード・ローコードで ETL を実装できます。データ転送のみの用途でも使用可能であり、特に プラグイン を使用することで、多種多様なデータソースに対応することが可能です。また、Datastream と同様に Oracle、MySQL、PostgreSQL から BigQuery への CDC 機能も提供されています。
• Dataflow：
  Apache Beam のプログラミングモデルを使用して、大規模なデータを処理できるフルマネージドでサーバーレスなデータ処理サービスです。データ転送のみの用途でも使用可能であり、Google 提供テンプレート を使用することで、簡単にデータ転送の仕組みを構築することができます。
• Pub/Sub：
  フルマネージドなメッセージキューイングサービスです。Cloud Storage サブスクリプション を使用すると、Pub/Sub メッセージを Cloud Storage に対して書き込むことが可能です。また BigQuery サブスクリプション を使用すると、Pub/Sub メッセージを BigQuery テーブルに直接書き込むことができ、 さらに BigQuery CDC を使用するとテーブルの行単位で UPSERT（UPDATE, INSERT）、DELETE が可能です。
• Database Migration Service：
  MySQL や PostgreSQL から Cloud SQL や AlloyDB に対して、マネージド サービスへのリフト アンド シフト移行やマルチクラウドの継続的レプリケーションを行います。オンプレミスや他クラウドからのデータベース移行が必要な場合に使用します。
  

■BigQuery CDC(Change data capture)

upsert/delをBQ storage write APIを利用してリアルタイム反映ができる

すでにDatastream for BQの裏で利用されており、PostgreSQL/MySQL/Oracle等データ同期可

GCPを活用するスキルを問われる「Associate Cloud Engineer」

インフラストラクチャの知識を問われる「Professional Cloud Architect」

データと機械学習の知識を問われる「Professional Data Engineer」

　マシーンラーニング：教師ありをベースにパターンを線形として認識し相似のパターンを見つける

　ディープラーニング：人間が把握できる次元のデータ構造ではない多次元でパターンを見つける
　　線形検索みたいなもんか

無料枠

GCE:プリエンプティブル以外の1つの以下のe2-micro VMインスタンスと30GB/月の標準永続ディスク

　オレゴン: us-west1

　アイオワ: us-central1

　サウスカロライナ: us-east1
GCS: 5GB/月のRegional Storage（米国リージョンのみ）

　月額のプロダクトを日割レポートで見ると2月の日割りは高く見え、3月は安く見える

　為替の影響は月初から適応されるので円安だと高いよ

GCP ハンズオンセミナー Google_Cloud_GCPHandson_infra1122.pdf (cloudplatformonline.com)

　GKE, Cloud SQL, Dataflow等

■IAM(Identity and Access Management)　
https://cloud.google.com/iam/docs/overview?hl=ja

https://cloud.google.com/iam?hl=ja
IAMベストプラクティス　https://cloud.google.com/iam/docs/using-iam-securely
操作方法　https://cloud.google.com/iam/docs/how-to?hl=ja
ロール　https://cloud.google.com/iam/docs/understanding-roles?hl=ja

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

https://medium.com/google-cloud-jp/gcp-iam-beginner-b2e1ef7ad9c2

/// BANGBOO BLOG /// - GCP ログ調査 Logging/Bigquery information schema

■GCS
■GCP Cloud asset inventory

公式

https://www.terraform.io/docs/index.html
GCP用リファレンス

https://registry.terraform.io/providers/hashicorp/google/latest/docs

お便強

https://qiita.com/minamijoyo/items/1f57c62bed781ab8f4d7

https://qiita.com/donko_/items/6289bb31fecfce2cda79

https://www.apps-gcp.com/infra-automation-01/

https://colsis.jp/blog/gcpterraform/

moduleは構成に合わないようなリファクタリングが必要になった時にterraform state mv が必要になってとたんにつらい、moduleを細分化しすぎるとvariable と output が大量に必要になって書きづらい、moduleは再利用できる複数のリソースの単位（プログラミング言語でいうところの関数みたいなもの）で作るのがしっくり

環境情報は外部ファイルが基本

prd/stg/dev環境はprd.tfvars, stg.tfvars, dev.tfvarsを用意

.tfvars　各環境の設定

aws_access_key    = "XXXXXXXXXXXXXXXXXXXX"

aws_secret_key    = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"

aws_region        = "eu-west-1"

main.tf

terraform {

  required_version = "= 0.12.26"

}

provider "aws" {

  version    = "2.66.0"

  access_key = var.aws_access_key

  secret_key = var.aws_secret_key

  region     = var.aws_region

}

var.tf　空の受け皿

variable aws_access_key {}

variable aws_secret_key {}

variable aws_region {}

Terraform で変数を使う - Qiita

credentials等の秘匿したい変数を外部のファイルやコマンドライン引数から読み込む

variable "credentials_file" {}　＠var.tf　変数を定義し空にしておく

credentials = file(var.credentials_file)　＠main.tf　ファイルを読むがファイル名は変数

terraform plan -var 'project=' -var 'credentials_file=.json'　＠cmd　プロジェクトとクレデンをコマンド時に指定
他にもvars.tfvars設定ファイル(行頭variableが不要)、TF_VAR_環境変数による指定

Terraformのベストなプラクティスってなんだろうか | フューチャー技術ブログ (future-architect.github.io)
Workspace派、module派、ディレクトリ分離派

HCLの変数は基本2種類のlocalとvariable

variableはグローバル変数、ファイル外やコマンドラインから使える

変数

　variable(input var)はcmd実行時に変数を上書きできるが、localsはできない

　outputはapply時にterminalで値が確認できる、moduleから値を取得する

google_bigquery_connection | Resources | hashicorp/google | Terraform Registry
ドット繋ぎで値を扱える

    for_each = toset(["foo", "bar", "bar"])　でbar, foo

    name = each.key

}

for_each/eachのループ
locals {
    sg = {

        foo = "FOO"
        bar = "BAR"
    }

}
resource "xxx" "aaa" {
    for_each = local.sg
    name = each.key
    description = each.value
}

　　provider "google" {

　　  project = "bangboo-kuso"

　　}

　　terraform {

　　  backend "gcs" {

　　    bucket = "bangboo-kuso-terraform"

　　  }

　　}

　プロジェクトレベルownerのサービスアカウントを持っておく

　セットアップする際にtfsateのbackend保存場所のbucket部分をコメントアウト

　bucketを作るterraformを実施しbucketを作成しつつlocalにtfstateファイルを作成

　再度terraformをするとtfstateファイルがbucketにコピーされる

　bucket部分のコメントアウトを外すと次回tfからはバケットにtfstate更新する
　　このときローカルtfstateの内容をバケットに写すか聞かれるが写す
　　（写さないと差分しかバケットに行かないのでimport等が必要になる）

　リソースタイプや個別パラメータは公式ドキュメントを参照しながら定義

　https://registry.terraform.io/providers/hashicorp/google/latest/docs

リファレンスでoptionalとなっていてもtfファイルでは必要な場合もある

　tfstateファイルからは必要ないとして自動的に削除されるが

スプシをBQでみれるFederatedQueryはテーブルだけ定義しimportしstate show調査

　urlをTFファイルに記載する

シャーディング（日付別）テーブルは定義できないのでは

　生成するクエリの方をTF化したい
Authorized viewはモジュールがあるがconflictがあり全消えする場合がありTF化にまだ向かない

　TFのorg_iamページのArgument referenceのrole項目を見ると

　　Note that custom roles must be of the format organizations/{{org_id}}/roles/{{role_id}}

　TFのorg_iam_custom_roleページのAttributes  referenceのrole項目を見ると

　　id - an identifier for the resource with the format organizations/{{org_id}}/roles/{{role_id}}
　で下記と分かる、使用側と被使用側のTFマニュアルを両方確認する

resource "google_organization_iam_custom_role" "my-custom-role" {

  role_id     = "myCustomRole"

  org_id      = "123456789"

  title       = "My Custom Role"

  description = "A description"

  permissions = ["iam.roles.list", "iam.roles.create", "iam.roles.delete"]

}

resource "google_organization_iam_member" "my-organization" {

  org_id  = "123456789"

  role    = google_organization_iam_custom_role.my-custom-role.id
  #あるいは通常"roles/bigquery.dataEditor"のようにいれるがorganizations/0123456789/roles/chinkoといれる

  member  = "user:jane@example.com"

}
↑
resourceの2番目リソース名を定義しますが任意の名前を指定します
　ここが同じ項目はユニーク制限がない場合は追加としてまとめられます
　通常はユニークにしterraformで管理するリソース名(yourResourceName)を宣言します
　　※1番目のリソースタイプ内でユニークにするのが基本（全体でもユニークの方が分かり易い）

■TFのcount
数を指定してその個数のリソースを作る。なのだが

　enable_lien = true　モジュール側/変数側でこう設定し

　count = var.enable_lien ? 1 : 0　リソース側で3項演算子を使えばIFのように使える
　　※for loopのようなインクリのcount"+="でなく"="の1発実行なので3項演算子でIFになる

gcloud auth login --enable-gdrive-access

gcloud auth application-default login --scopes="https://www.googleapis.com/auth/drive","https://www.googleapis.com/auth/cloud-platform"

排他処理でロックが残る：

他の作業者がいなければ、terraform apply -lock=false で一時的に無視をして続行
エラーIDに対して terraform force-unlock id_num963103164

Terraform と gcloud CLI を使用した完璧な Google Cloud インフラストラクチャの構築 | Google Cloud Blog
gcloud beta resource-config bulk-export --help
gcloud beta resource-config bulk-export --project=kuso12345 --resource-format=terraform --path=/path/to/dir/
　対応を見ると数が少ないgcloud beta resource-config list-resource-types --format=json --project=kuso12345

terraformer import google list --projects=xxxx-123　で対象のリソース確認

terraformer import google --resources=instances,forwardingRules --regions=us-west1 --projects=xxxx-123　とか

https://www.apps-gcp.com/terraformit-gcp/

https://qiita.com/uu4k/items/48d3ecfefe57dba1d7e1

■Terraform applyで意図しない権限削除で障害が発生する
Terraform x GCP で、IAM権限を全削除してしまった - Qiita
google_bigquery_dataset_iam_member：roleとmemberを1対1でresourceを作りまくる、Non-authoritative
　※_iam_policyや_bindingはまとめ易そうだが権限消しそう


Terraform で GCP のサービスアカウントを管理する - Eng (なりたい) (hatenablog.com)
Terraform で GCP IAM 設定どれ使うのがいいのか - pokutuna (scrapbox.io)
google_project_iam | Resources | hashicorp/google | Terraform Registry
Authoritative: TFに明示していないものをApply時に削除しますという意、TFの権威
Non-authoritative: TFは権威を示さず、TFに明示していないものは現状維持ですという意

■勝手に公開
terraform vpc auto_create_subnetworks = falseにせなサブネットを公開して作りよる
Cloud sqlのtfファイルに記述がなくてもtf stateにはPWが出てしまう＞tf化したくない

■Applyの依存関係はdepends_on(プロジェクト作成の前に権限を付与しようとしてエラー等の順序)
[Terraform]Module間の値の受け渡しについて | DevelopersIO (classmethod.jp)
　これより先にあれをTFしてくれという記述

■データセット
google_bigquery_dataset | Resources | hashicorp/google | Terraform Registry
スキーマ取得: bq show --schema --format=prettyjson bangboo-prj:test-dataset.tbl001
ビューはコンソール＞BQ＞該当ビュー＞detailからコピー

■組織ポリシー
google_organization_policy | Resources | hashicorp/google | Terraform Registry
制約について  |  Resource Manager のドキュメント  |  Google Cloud

■parallelismで早くなるかもしれない
あなたのterraform planを手軽に高速化する（かもしれない）魔法の言葉 - Qiita

■テスト
TF Apply後には検証をしっかりしたい、最低Apply後にPlanを再実行したい、テストスクリプト的にチェックしたい
適応されていないことや、TF定義とtfstateと実設定の差分が想定と違うことがある感じがするからだ
moduleを含めて条件的な書き方だと、適応の順序の関係で適応が抜け2回以上TF applyしないといけなかったり

■TFファイルを分割し部分的に別環境へTFファイルを移行したい
tf stateファイルを新環境用にコピー
TFファイルを分割：現行の部分的に削除したTFファイル、と新環境用のTFファイルを準備
tf stateから不要部分を削除
　terrafrom state rm {{asset}}
terrafrom planで差分がないことを確認
新環境用にコピーしたtf stateファイルを編集し準備
　下の terraform state push xxx.tfstate で変更された状態をtf stateファイルに書き戻すことができる？
　このコマンドを実行する前に必要に応じて terraform state pull で最新の状態を取得？

　ダメなら terraform state rm で不要分を削る

新環境用にtfファイルを準備
新環境で下記を実行
　terraform init
　terraform state push xxx.tfstate
　terraform planで差分がないことを確認

■GCP権限（メールの変名時）

　これにクレデンシャルファイルのパスを渡すとサービスアカウントでgcloudコマンド打てるはず

クリップボードをペースト　iで挿入モードに入り Shift+Insert

vi内でコピペ：yコマンドのコピー、pコマンドのペーストなので下記の様にする

　2yw　ならカーソルから2単語コピーされます

　3yl　ならカーソルから3文字コピーされます

　y$　ならカーソルから行末までコピーされます

　p　ならカーソルの後ろにペーストされます

$ ssh-keygen
パスフレーズは空でも設定上は問題ないが塩っ気が足らん気が

秘密鍵（id_rsa）と公開鍵（id_rsa.pub）が生成され、ホームディレクトリに作成される

　/home/yourID/.ssh/id_rsa

　/home/yourID/.ssh/id_rsa.pub.

公開鍵をSSHサーバや外部サービスに登録する等して使う、秘密鍵は明かさないこと

SSHクライアントのproxy越えの設定方法 (mydns.jp)
プロキシbinのインスコ
$ sudo apt-get install connect-proxy
ホームにsshキーができているので

$ vi ~/.ssh/config

Host github.com

HostName ssh.github.com

Port 443
IdentityFile ~/.ssh/id_rsa.pub

ProxyCommand connect-proxy -H proxy.syanai.in:3128 %h %p

User githoge

/etc/resolve.conf
　nameserver 88.88.88.88

~/.profile とか .bashrc とか

  export http_proxy=http://proxy/3128

/etc/apt/apt.conf
ip addr

lsblk

df -Th

du -sk * | sort -nr

#xfsの場合

pvdisplay

pvresize /dev/nvm

vgdisplay

lvdisplay

lvextend -l +100%FREE /dev/vg001/lv001

xfs_growfs /opt

#ext4の場合

apt autoclean　キャッシュあるがインストールされていないdebファイル削除

apt autoremove　必要なくなったパッケージ削除

disk -l /dev/nvm　パーティション情報

growpart /dev/nvm 1　指定パーティションの容量拡張

resize2fs /dev/nvm　ファイルシステム拡張

容量調整
/var/cache　はapt-get clean, yum cleanで消す程度で

sudo apt install mysql-client=6.6.6-0ubuntu2.1~99.99.9

sudo apt install mysql-client-core=6.6.6-0ubuntu2.1~99.99.9
…
dpkg-l | grep mysql

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