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ボックスは忘れて何度か買ったが小さい
バターよりマーガリンの方が健康的になったらしい
　小岩井ヘルシー仕立てか、明治コーンソフトは近くで買えるが


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

キリンGreen's free　旨い、ノンアルならビールが良さそうで、ノンアルビールならこれでは？
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)　
/// BANGBOO BLOG /// - GCP

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

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)

■フレームワーク
3C(Customer/Competitor/Company:自社)
SWOT(Strength-Weakness x Opportunity-Threat)
PEST分析(Politics/Economy/Society/Technology)
3M(人物金、Men/Material/Money)

3M(ムリ:負荷が人に掛かる/ムダ:資源が余り浪費しコスト高/ムラ:負荷と浪費が交互)
7S(<3sはハード>Structure/Strategy/System、<4sはソフト>Shared value:価値観/Skill/Staff/Style:文化)
VRIO(Value/Rarity/Inimitability:模範困難性/Organization)
MVV(Mission:使命why/Vision:将来像what/Value:価値観how)
マーケ4P4C(4p:product/price/place/promotion, 4c:customer cost/customer cost/convenience/communication)
QCD(Quarity品質/Costコスト/Delivery納期)

ポーターの5forces(Competitive Rivalry:競争企業間の敵対関係/Supplier Power:売り手の交渉力/Buyer Power:買い手=顧客の交渉力/Threat of Substitution:代替品の脅威/Threat of New Entry:新規参入業者の脅威)

コトラーのSTP(segmentation:顧客やニーズや特性等で細分化/targeting:セグメ選択/positioning:勝てる位置取り)
競争戦略ポジショニング (コストや差別化や集中戦略等で業界ポジを練る)
PPM:product portfolio mgt(市場成長率xシェア->問題児:花形に育てる/花形:継続投資/金のなる木:投資無し/負け犬:撤退検討)
製品ライフサイクル:PLC(導入期:認知度と需要up/成長期:製品多角化販路拡大/成熟期:差別化とコストダウン/衰退期:選択と集中効率化)
イノベータ理論(Innovators/Early Adopters/Early Majority/Late Majority/Laggards:16%キャズム:段階で戦略を変えていく)
RFM(recency/frequency/monetary->優良顧客を見つけよりマーケ)
ポーターのバリューチェーン(企画/開発/購買/製造/物流/販売/サービス/人事の各々での価値->VRIOで分析)
アンゾフの成長マトリクス(既存市場浸透:既存市場x既存商品/新市場開拓:新規市場x既存商品/新商品開発:既存市場x新規商品/多角化:新規市場x新規商品)
TOWS(クロスSWOT分析:SWOTをSWOTする->SWOTで現状分析、さらにSWOTで戦略化)
SCAMPER(アイデアの展開->substitute代用/combine結合/adapt応用/modify修正-magnify拡大/put to other uses転用/elininate削除-minify削減/reverse逆転-rearrange再編集)
ECRS:イクルス(改善策->排除/統合/交換/簡素)

ギャップ分析(As Is/To Be<-間にaction)

特性要因図:フィッシュボーンチャート(要因を網羅的に調べる、他にも親和図法やマインドマップ)
意思決定マトリックス(縦軸に候補、評価基準を横軸、評価基準に重みをつけて点を出し、合計の総合点で判断)
CS/CE分析(Customer satisfaction/Customer expectation->各機能のcsをceに合わせる、ce高cs低の改善)
リスク評価マップ(インパクトx不確実性、インパクト大に対処し小は静観、不確実性が高いと注意が必要<-pest/5f)

AIDMA...etc...

ユダヤの商法：78対22の法則で上手くいく、契約主義で約束を守る

ケイパビリティ戦略：ビジネスプロセスを能力化、スピード/整合性/明瞭性/俊敏性/革新性が上がる、CEOがツールや組織を検討

ポジショニング戦略：競合との位置付け、STP分析をしどうポジションを取り差別化するか

タイムベース競争戦略：時間こそが希少資源、スピードが一番顧客満足が高い、数稼ぐ

アダプティブ型戦略：多くの新しいアプローチを試し最も有望なものを拡大展開、PDCA、やらな分からん時代

JCOM回線不安定：

インターネット回線の信号レベルはタブレットで通信でモデムに入って測れる

宅外でモデムを繋いででも宅外で調査しろ

同時接続数等々でスロットリングしているだろうが