データの種類でアーキテクチャを決める？

　コンテナはオーバヘッドが少なくVM/GCEに比べ軽量高速、スケールアップ/ダウンに優れている

GCS
　IAPを使うと画像アクセスにも認証が使えそう＞LBのバックエンドではIAPが使えない
　　GCEにGCSマウントするとGCEのIAPとしてイケる、gcsfuseのOSSでLinux上のドライブになる
　　　CensOS6.8にgcsfuseをインストールしてGCSをマウント - Qiita
　　　GCS Fuseを使ってみる - Qiita
　リテンションで保護期間を設定したり、ライフサイクルで削除時期を設定したり
　世代管理ができたり

■制約が重要そうでまとめて記載したい

default VPCネットワークを削除（セキュリティが緩いため）

vpcネットワーク作成 サブネットIPレンジ 10.27.0.0/16-192.168.27.0/24 等で

　private google accessはオンでいいのか?on

　FWはひとまずなしで、だが大体ポートは22,80,443,8080?

別途firewallで下記を設定（ちなみにリクエスト側を許可すればよく、レスポンス側は自動）

bangboo-fw-allow-iap-ssh IAPからのSSHを許可 35.235.240.0/20 tcp:22

　レンジはマニュアル https://cloud.google.com/iap/docs/using-tcp-forwarding#create-firewall-rule

bangboo-fw-allow-lb GFE(Google Front End)からのHTTPを許可 35.191.0.0/16,130.211.0.0/22 tcp:qll

　レンジはマニュアル https://cloud.google.com/load-balancing/docs/health-checks#fw-rule

Cloud NAT(Router)を設定

https://cloud-ace.jp/column/detail260/

https://www.topgate.co.jp/google-cloud-network-security

　インスタンステンプレートのメタデータ

　　起動スクリプトをファイル化できる

　　　キーstartup-script or shutdown-script、値にパス

　　　キーstartup-script-url or shutdown-script-url、値にGCSのURL
　　OSLoginをIAPにしてVM上の独自ID管理PW管理不要に
　　　便利機能「OS Login」を使ってIAMでインスタンスへのSSH接続制限をする | apps-gcp.com
　　　キーenable-oslogin、値にTRUE、IAMで権限（compute OSLogin/IAP tunnel/gce系）、IAP API有効
　　　　権限もIAMで管理されるのでLINUX上の755等も不要なようだ（computeinstanceAdmin.v1を付けとく）

　　MIGとLBと同じヘルスチェックを使うことは非推奨

　　　LBはより短い低い閾値にせよ

　IAPを見るとLBを設定するとFWはLBに対するものだけになるので不要との指示がある

　　fw-bangboo-http-ingress-allow　0.0.0.0/0（削除）

　　下記はインスタンス作成時の許可設定分で不要

　　　default-allow-internal　69.11.0.0/9（削除）　default-allow-http　0.0.0.0/0（削除）
　　　これも不要？default-allow-http 0.0.0.0/0 tcp:443（削除）default-allow-icmp 0.0.0.0/0（削除）
　　　default-allow-rdp 0.0.0.0/0 tcp:3389（削除）default-allow-ssh 0.0.0.0/0 tcp:22（削除）
　IAP(ssh/tcp)を見るとFWで開けてくれの指定があるが開けるとhttpsに穴ができると出るし止め
　　fw-bangboo-lb-iap-tcp-allow  Source IP range:072.72.2.0/20 tcp:all（sshターミナルを使う時だけFW開ける、通常priority9000）

CloudSQL

　APIライブラリからCloud SQL API、Cloud SQL Admin APIを有効に

　平文通信→暗号化CloudSQL proxyバイナリをVMインスコ、ディレクトリ切る

　　proxyとアプリ設定を合わせる、proxyサービス起動

　　↑Readyになってからコマンドが返るのに何分か掛かる
　　もう一つSSHを開くと下記コマンドが通る
　　mysql -u root -p -S /cloudsqlproxy/bangboo-sql:asia-northeast1:mysql-01
FW/ArmorでIngress全止め or VMインスタンス停止、LB停止

■GCE MIGローリング更新
■インスタンススケジュール

sute16　asia-northeast1-b　2021/7/24(91日で33000yen-10/20位まで)
sute3　asia-northeast1-b　2022/2/20(91日で34500yen-5/20位まで)

Instance groupはどう止める？＞LB削除＞LBのバックエンド削除＞Instance group削除
　LBはinstance groupがいる、IAPはGCEの場合はLBにSSL証明書要る（ドメインもGlobalIPも要る）
　　DNSのAレコードを登録しLB設定すれば証明書入る
　　　毎回検証終了時につぶして、立てるのが面倒そうだな→FWでdeny allしとく
　【初心者】GCP Identity-Aware Proxy (IAP), Access Context Managerを使ってみる (WEBサーバへのアクセス制限) - Qiita
　Oauth同意画面のサポートメールはログインしているユーザのものか、そいつがオーナになっているGoogle workspaceのメールグループを設定することができる（gcpのロールとしてwnerかoauthconfig.editorも要る）

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

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

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

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

　手動方法は下記参照

/// BANGBOO BLOG /// - Linux cmd


■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にせなサブネットを公開して作りよる

■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

極ZERO: 138/0/0/5 ちょい高、不味い、5%で軽いが酔えなくはない、太る感じ×

Asahi Off: 118/0/0/3-4 酔えない、ノンアルの亜種かフルーティで旨い〇

のどごしZERO: 118/0/0/4 フルーティで旨い、軽いが酔えなくはない〇

贅沢ZERO: 118/0/x/6 プリン有 6%で嬉しいが雑味がすごくある気が▲

バーリアル糖質50%off緑: 85/5-10/?/4 かるくない、そもそもZEROでないが旨い◯+

氷結ZEROレモン: 108/0/0/5  レモンが強く旨い、飲みやすい◎

鏡月焼酎ハイ: 98/0/0/7 スッキリ高アルコール度で満足感、ドライが好きかも◯+

いいちんこ下町のハイボール: 168/0/0/7 高ぇ、重い感じがする▲
パーフェクトサントリービール: 168/0/x/5.5 高ぇ、旨い〇

　→氷結Zeroレモン＞鏡月ドライのコンボが強い

イオン麻婆豆腐辛口(肉付)vs丸美屋辛口(花椒なし)：丸は味穏やか、Aは塩と肉多い

　→Aeonのとろみ入れるの減らす、甘口は邪魔甘さで、中辛が一番いい

イオン四川式(肉無)vs丸美屋大辛(花椒付)：Aは高いし辛い、お口は甘め好きda
　冷食が新しくなった、Palmボックスは忘れて何度か買ったが小さい
バターよりマーガリンの方が健康的になったらしい
　小岩井ヘルシー仕立てか、明治コーンソフトは近くで買えるが

ノンアルはどうなのか？飲み比べる、ジンジャエールよりいいか

Suntoryのんある酒場レモンサワー　酸っぱい、旨い（メシが進む感じはしない
Takara辛口ゼロボール　スッキリしているが何か不味いので逆に酒感あり旨い
AsahiStyleBalanceハイボール　まじウィスキー、旨い（メシが進む感じはしない
トップバリューホワイトサワー　カルピスソーダでノンアルの意味なし
トップバリューBeerTaste　スッキリ旨いが軽くハレ感が少ない

Suntoryノンアル気分カシスオレンジ　旨い、大人のジュース感
他に、檸檬堂とジントニックとワインのノンアルを試したい

「カルト」はすぐ隣に: オウムに引き寄せられた若者たち (岩波ジュニア新書) | 紹子, 江川 |本 | 通販 | Amazon
霊的体験は飲み物にLSDを入れていたとなっている、まぁ殆どコレで教祖になれる

　　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

■IAM(Identity and Access Management)　
https://www.bangboo.com/cms/blog/page_347.html

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

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　デフォルトに指定

pipenv　PIP管理としてインスコ

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

　pipenv --python 3.8.8　など環境をpipfileに記載

　pipenv sync　Pipfileの最新を取得し環境更新

　pipenv shell　仮想環境を起動

import dataclasses

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

import datetime

pip install pyyaml > import yaml

pip install requests > import requests

　SlackのwebhookにPostするとか