■21/2/21 1:00AM
BigQuery
■Big queryリファレンス標準SQLとレガシーSQLがある、違いは？
標準 SQL のクエリ構文 | BigQuery | Google Cloud
標準 SQL への移行 | BigQuery | Google Cloud
標準 SQL のデータ型 | BigQuery | Google Cloud
レガシー SQL 関数と演算子 | BigQuery | Google Cloud
レガシー SQL のデータ型 | BigQuery | Google CloudBigQuery: クラウド データ ウェアハウス | 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 10BigQueryは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 afunctionを作って使える 標準 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結果を挿入できる
■レガシー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 Cloudhttps://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.test001PARTITION 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.newtbl2つのテーブルをnewtable にまとめコピーbq cp -a ds.tbl2 ds.tbl1tbl2をtbl1に追加コピー --append_table でも同じ
bq load (csvとかgcsのファイルを読み込む）bq extract (gcsに抽出)bqコマンドの使い方: UNIX/Linuxの部屋 (q-e-d.net)
■データアップロード時のスキーマ指定
自動検出は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:B100Auto detectにするとHeader skipを1にして1行目をカラム名として使うといい
注意） シートで構成を変えると滅茶苦茶になる 空欄のセルはnullになる 使う人はBQへもスプレッドシートへも両方権限が必要
///パラメータ(変数)を使う--parameter=min_count:INT64:250SELECT 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,paramsWHERE 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,) asselect * from `prj.bangboo_data.x_employee`標準 SQL のクエリ構文 | BigQuery | Google Cloudデータ定義言語ステートメントの使用 | 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で入っているものを日でサマるSQLselectcount(table_id),sum(size_bytes),date(record_time) as record_dayfrom bq_metadatawhere record_time > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 3 month))group by record_dayorder 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"}等シャーディングはテーブルごとに権限を付与が必要で日付別なら実質無理でデータセットで権限管理が必要
クラスタリング も同時に考慮したいBigQueryのパーティションとクラスタリングについての解説 - G-gen Tech BlogBigQuery上のデータマートをクラスタ化したらクエリコストが９割カットできた話 - エムスリーテックブログ (m3tech.blog)事前にソートし、まとまりを作っておく仕組み。インデックスのようにカーディナリティが高いカラムを指定してあげると列指向のため全スキャンしなくて良くなる。圧縮率も上がり 保存費用も削減できる。WHERE で指定あるいは GROUP BY される複数列をクラスタ化列として指定するが、指定の順番が重要。まずパーティションが考慮され、次に最初にクラスタ指定した列で行がソートされ、次にその中で2番めに指定した列でソート、次に3番目...となるCREATE TABLE ds.tbl_cls (purchase_dt DATE, prod_id STRING, prod_name STRING)PARTITION BY purchase dtCLUSTER 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 Overflowselect 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 CloudBQタイムトラベルで2-7日前のデータを見れる
タイムトラベルを使用した履歴データへのアクセス | BigQuery | Google Cloud
///UNNEST
UNNESTを知らないとBigQueryを使えない？ | 4番は司令塔 (pep4.net)
BigqueryでUNNESTを使いこなせ！クエリ効率１００% | by Eureka EngineeringARRAY を一組の行にフラット化するには、UNNEST 演算子を使用
SELECT id, title FROM games, UNNEST(titles) AS title
idtitles1[skyrim, fortnite]2[atvvsmx, mario]↓フラット化
idtitle1skyrim1fortnite2atvvsmx2mario
ただし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型
標準 SQL のデータ型 | BigQuery | Google Cloudとある古典的SQLおじさんのBigQuery入門：ARRAY型とSTRUCT型 | DevelopersIO (classmethod.jp)
BigQueryのSTRUCT型とうまく付き合う - QiitaArrayは上の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.rolefrom cai_iam_policy_history,unnest(iam_policy.bindings) b,unnest(b.members) mwhere 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 descSQL解説）struct型が沢山入っていても全部unnestしfromに入れればいい from a, unnest(iam_policy.bindings) b, unnest(b.members) m unnest(iam_policy)はできないので2階層目から 一つ階層上ではunnest時に別名を付けて下の階層はその別名でunneststruct型の子へは.ドットで指定すればいい、フラットでなくても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/2537c562c6d99f83e37bSELECT * 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 htmlFROM 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_PROJECTwhere 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/195024MERGE aaa target USING tmptbl src ON target.time = src.timeWHEN MATCHED AND src.satus = 'rejected' THEN DELETEWHEN MATCHED THEN UPDATE SET ...WHEN NOT MATCHED THEN INSERT ROW
///window関数
集約関数（GROUP BY）だと個別データは出力されず集計データだけでるがwindow関数だと集計データが個別データにouter joinされた形で出力される分析関数のコンセプト | BigQuery | Google Cloud
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 avgFROM 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だとメール送信したり、ロギングやモニタリングでエラー検知できる
///プログラムで使う
https://googleapis.dev/python/bigquery/latestfrom google.cloud import bigqueryclient = 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は参照元の権限も必要） この権限移譲は閲覧権限のみで編集権限等は含まないBigQueryの承認済みビュー設定方法 - Qiita被参照の元テーブル側に許可する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.TABLESselect * 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_tablesAuditlogは プロジェクト間で使用されるBQでも情報が取れる info_schemaのjobs_byとほぼ同じ内容が取れるがよりリッチ 利用ユーザ数、旧データを見ている人、権限変更操作ログ等
///Authrized function
認可済み関数の作成 | BigQuery | Google CloudSELECT `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.[].namebq --format=json show --transfer_config project/1111111/locations/us/tranferConfigs/111111 | jq .ownerInfo.email
■BQ transfer(クエリが不要なBQ連携、3rd partyもあり)
サードパーティ転送を使用する | BigQuery | Google CloudCloud Storage の転送の概要 | BigQuery | Google CloudAmazon S3 転送の概要 | BigQuery | Google Cloudデータセットコピー、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だとすぐに壁にあたる
上限がテーブル単位のためテーブル名を分けると回避できるらしいGCP BigQuery 応用編 ~制限について~ - 自称フルスタックエンジニアのぶろぐ。 (hatenablog.com)BQ streaming insert->BQ storage read/write APIの上限はDMLと別で、閾値が大きいINFORMATION_SCHEMAを用いたBigQueryのストレージ無駄遣い調査 - ZOZO TECH BLOGBigQuery Storage Write API を使用してデータを一括読み込み、ストリーミングする | Google Cloud
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の列レベル・行レベルのセキュリティを解説 - G-gen Tech Blog個人情報や機微情報を隠す
BigQuery の行レベルのセキュリティの概要 | Google Cloud
行レベルなら同じテーブルを使うので同じダッシュボード/Appが使える（AuthorizedViewの方が柔軟だが）
データ マスキングの概要 | BigQuery | Google Cloud
列レベルアクセス権以外にもマスクの種類があり、ハッシュだったり先頭4文字や末尾４文字等で共通文字化としてマスク化できる列レベルのアクセス制御の概要 | BigQuery | Google Cloud列レベルのアクセス制御によるアクセス制限 | BigQuery | Google Cloud
BQ画面＞左ナビのポリシータグ ポリシータグを作成（組織単位で一括一覧表示） タグは階層化できるので、全ユーザタグ＞管理者タグ＞社長タグ スキーマ＞Addポリシータグ タグが付いていればプレビューで見れない select * except(tag_column)にする必要がある メタデータは見れる（カラム名、型 ポリシータグ画面＞対象ポリシー選択＞情報パネルで権限者一覧 fine-grained readerを付与するとselect *ができるようになる 社長タグに社長だけ権限付ける等※APIを有効にし、ポリシーを有効にする必要がある
/// BANGBOO BLOG /// - BigQuery part2
(More)
Comment (0)
■21/2/11 12:00AM
Python Python
■importとfrom
Pythonのモジュールとimportとfrom入門 - QiitaPython, importの使い方（from, as, PEP8の推奨スタイル, 注意点など） | note.nkmk.me
Pythonインポート周り徹底理解への道 - Qiita
Pythonの相対インポートで上位ディレクトリ・サブディレクトリを指定 | note.nkmk.me
標準ライブラリならimport文を本体に書いていれば良い
標準ライブラリ以外はPyPI（Python Package Index）と呼ばれる3rdパーティライブラリから
pip（The Python Package Installer）インスコ
import文を使って下記の3つなどをインポートし使う
標準ライブラリ
pipでインスコしたパッケージ
自作のパッケージ
自作は大体わかるがそれ以外はどこにあるのか？ $ python >>> import sys
>>> sys.path
でパス一覧が出るので探すと分かる >>> exit()でpythonコマンド終了
例えば Cloud functionsなら requrements.txtに google-api-python-client==3.3.2と記載し
PyPI · The Python Package Index でバージョンを探す
コードに from google.cloud import bigqueryと宣言する
requirementがpipインスコ
import フォルダ.ファイル名from フォルダ.ファイル名 import * 上下同じだが、fromは一部を指定し直接使うという意、*は非推奨 つまりimport hello なら下記とする必要があるが print(hello.hello)from hello import hello なら省略ができ下記で良い print(hello)from フォルダ名 の場合 そのフォルダ名の中に __init__.pyがあれば其れfrom .xxx import aaa の.の意味は？
mainに対するモジュールから見て相対で隣
モジュール検索パスを出すfrom pprint import pprintimport syspprint(sys.path)
■pipインスコpipの使い方 (2014/1バージョン) — そこはかとなく書くよん。 ドキュメント (tdoc.info)Python：pip における管理者権限と user install - pyてよn日記 (hatenablog.com)Python でパッケージを開発して配布する標準的な方法 - Qiitapythonのsetup.pyについてまとめる - QiitaPyPIでサードパーティライブラリを管理していてインスコ可setup.pyが含まれたローカルディレクトリも指定しインスコ可eオプションで編集可能な状態でインスコ--userで~/.local下の管理権限不要なユーザディレクトリ以下でシステムが汚れない--userなしで/usr下にインスコpip install --user -e unkopip3 install pipenvpip list インスコ済みのものを確認
pip install -r requirements.txt reqirements.txtで一括インスコ
pip freeze > r.txt pip listをファイルに書き出す
pip uninstall -y -r r.txt -yで確認なしで一括アンインストール
Python, pipでrequirements.txtを使ってパッケージ一括インストール | note.nkmk.me
パッケージとバージョンを指定してアップデートpip install -U google-cloud-bigquery==3.4.0
下記にもろもろ
pipでパッケージをupdate｜pip自体のアップデートについても解説 (itc.tokyo)
■envツール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 install "google-cloud-tasks==1.5.0" バージョン無しでも有りでも入れられる
Pipfileを書き換える方法 [packages] google-cloud-tasks = "==1.5.0" protobuf = "*" そして下記cmdでインスコ pipenv install PipefileからインストールしPipefile.lockを更新
pipenv sync Pipfile.lockの最新を取得し環境更新(Pipefileは使わない） pipenv shell 仮想環境を起動
pipenv run python main.py 他に
pipenv uninstall google-cloud-tasks アンインスコ Pipfile, Pipfile.lockがあれば pip syncでOKだがrequirements.txtも使える pipenv lock -r > requirements.txt 生成 pipenv install -r requirements.txtpipenvのバージョンが古いと依存関係、Ver整合性で問題が起きやすい pipenv --version pip install pipenv pipenv update pipenv upgrade <パケ>でやり直す
■assertでテスト
[Python] assert文で超お手軽テスト #Python - Qiitaassert文は組み込み定数__debug__がTrueの時のみ実行されます実行コマンドにオプションに-Oをつけると__debug__がFalseになりassert文が無効にdef func_so(a, b): c = a * b return def test(): assert(func_so(1,2) == 2)if __name__ == "__main__": test() main()
■個別
import dataclasses[詳解] Pythonのdataclasses (zenn.dev)
データ格納するオブジェクトを作れば使い回しが楽import datetimePythonのdatetimeで日付や時間と文字列を変換（strftime, strptime） | note.nkmk.mepip install pyyaml > import yamlPython で YAML ファイルを扱う - まくまくPythonノート (maku77.github.io)pip install requests > import requestsRequests の使い方 (Python Library) - Qiita SlackのwebhookにPostするとか
tenacityでリトライ処理
[Python] tenacity ライブラリを利用してリトライ処理を簡単に実装する (zenn.dev)boto3で並列処理Python 3.12で増えた並列処理と、これまでの並列処理の挙動を比べてみる #Python - Qiita
Python + VSCode の環境構築 20240604 (zenn.dev)
↓本家
/// BANGBOO BLOG /// - Python
Comment (0)
■21/2/10 7:30PM
Python
おッPythonやるのか？
ファイル拡張子oppython.py デフォUTF-8、全部オブジェクト(list,dict,set等のミュータブルなら参照になる点に注意、必要ならcopy())#コメント、ドキュメントストリング(三連引用符)："""そのまま表示""" print mymod.__doc__で見れる変数型不要：p = 500 * num、でもキャストは必要、定数はない文字繰り返し、キャスト："文字列" * 4 + str(p) + "Hi\nお元気ですか？\nSee u"raw文字列でescしない：print(r"インストール先は c:\\code\python\bin です")
正規表現のrも同意 re_result = re.match('hel', r'hellow python, 123, end.' )
if re_result: #None以外という意味で、Noneはいわゆるnull、Pythonにnullはない文字数：len("東京都")→3
文字列[開始:終了]→→ 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]
def func(a, b): return a, bresult = func() result[0]がa、result[1]がbtry／exceptを関数内で設定することも、逆に関数呼び出し時にも使用ができる、else, finally, raiseも使う、エラーが出ても止めたくない場合は try-except Exceptions as e、逆にexceptを入れなければ止まるので安全try: get_all_transfer(project_id)excerpt Exception as e: print(e)置換は左辺が要る？要る 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"]
lambdaは無名関数？
str_w = input('何か入力してください-->') #入力させた値を取れるが数字もstr__iter__()はnext()を持つオブジェクトを返し、next()は次の要素を返し、最後に達するとStopIteration例外を返す？yield はイテレータを返すジェネレータを定義？@デコレータは関数を実行する前後に特殊な処理を実行したい場合？withで終了処理を指定できる、ファイル読込とその後の処理とか
assertや__debug__はテストで機体通りかを確認する？
passは中身の無い関数やクラスを作成しkara.p=1で粋なり属性追加等ができる
execは引数の文字列をPythonとして実行 exec "print 'Hello'"
delはオブジェクトを削除 del x, y, z
継承やオーバーライド class MyClass2(MyClass):
多重継承class MyClassC(MyClassA, MyClassB): で纏めて使えるようになる
class MyClass: """A simple example class""" # 三重クォートによるコメント def __init__(self): # コンストラクタ self.name = "" def __del__(self): #インスタンスが消滅する際に呼出でコンストラクタ print "DEL!" def __str__(self): #文字列化 return "My name is " + self.name def getName(self): # getName()メソッド return self.name def setName(self, name): # setName()メソッド self.name = name
class MyClass2(MyClass): def world(self): print "World"class MyClass3(MyClass): def hello(self): # 親クラスのhello()メソッドをオーバーライド print "HELLO"a = MyClass() # クラスのインスタンスを生成a.setName("Tanaka") # setName()メソッドをコールprint a.getName() # getName()メソッドをコールprint a #=> My name is Tanaka 文字列化b = MyClass2() #継承b.hello() #=> Hellob.world() #=> Worldc = MyClass3() #オーバーライドc.hello() #=> HELLO
super()を使ってオーバーライドする super()は基底クラスのメソッドを継承した上で処理を拡張 super().__init__(x、y)が使えるif __name__ == "__main__": モジュール時の勝手実行を抑える import helloの時hello.py 内部での __name__ は "hello" python hello.pyのような実行時hello.py の内部の __name__ は "__main__"
from math import pi, radians→mathモジュールから特定のオブジェクト(関数/変数/クラス)をimpo(math.piみたいに書かず省略できる)import urllib.error→urllibパッケージからerrorモジュールをimpo、パッケージはフォルダimport numpy as np→別名でしか使えなくなるnp.array()とかで モジュール=ファイル名.pyでファイルをimpoしている
from {another_file} import {ClassName} another_file.pyがファイル名 class ClassNameがクラス名
from {パッケージ：ディレクトリ} import {モジュール：ファイル}
ちゅー書き方もできるらしいが、どっち？impo順：標準ライブラリ＞サードパーティライブラリ＞ローカルライブラリ(自作のライブラリ)
関数や変数：小文字スネークケース（sample_func）クラス名、例外、型変数：キャピタルパスカルケース（SampleClass）定数名：大文字アンダースコア区切り（SAMPLE_CONST）モジュール名：小文字（samplemodule, sample_module）パッケージ（フォルダ）名：小文字。アンダースコア非推奨（samplepackage）
インデントは半角スペース4つ1行半角で79文字以内トップレベルの関数やクラスは2行開けるクラス内部では１行ずつ開けてメソッド定義
ドックストリングでクラスや関数についてコメントする(慣習的にダブルクォート)
コード中は処理についてのコメントをなくし関数化とdocstringで参照するように
「コメント(#)とdocstring(""")の違いは？」コメントとdocstringについて
[Python]可読性を上げるための、docstringの書き方を学ぶ（NumPyスタイル） - Qiita
デバッグの方法案
print(type(v)) でどんなメソッドを持っているか等を探る
print(v) をコマンド前後や流れで沢山仕込みでどこでエラーが出ているか探す
print("creds:")print(creds)print("type(creds:")print(type(creds))print("vars(creds:")print(vars(creds))print("creds.keys():")print(creds.keys())print("dir(creds):")print(dir(creds))print("creds._dict_:")print(creds.__dict__)
is not subscriptableのエラー 添字不可エラーでリストでないのにリストとして入れようとしている
※参照になりコピーされない、必要ならコピー(値を入れた時点で参照が外れるので実際問題少ない？)a = []b = ab.append(1)print(a) #[1]https://qiita.com/ponnhide/items/cda0f3f7ac88262eb31ehttps://nishiohirokazu.hatenadiary.org/entry/20120125/1327461670
環境変数を扱う
import os print(os.environ["HOME"]) ホームディレクトリ、LANGでja_JP.UTF-8とか os.environ["PHASE"] = "staging" 環境変数に代入できるのは文字列だけ del os.environ["PHASE"] 削除コマンドラインの引数を扱う python3 sys_arg_test.py a 100
dst_prj = sys.argv[1] （aが入っている） sys.argv （['sys_arg_test.py','a','100']
Pythonのリストと文字列を相互に変換する方法まとめ | HEADBOOST
→リストをStrに変換してSQLにする場合For文が良い（Pythonの書式とSQL書式のコンビなので丁寧に対処するため）i = 0
v = "["
for s in list_v: i += 1 if i > 1: v += ","
v += "'" + s + "'"
v += "]"SQL = "insert into aaa (aaa) value ({v})"
※テキスト選択 Shift↑or↓ で行全体 home(+fn)で行頭、end(+fn)で行末移動
【基礎一覧】Pythonの基本文法を全て解説してみた！【初心者】 (suwaru.tokyo)
Python基本文法まとめ - Qiita
とほほのPython入門 - とほほのWWW入門 (tohoho-web.com)Python入門 ～Pythonのインストール方法やPythonを使ったプログラミングの方法について解説します～ | Let'sプログラミング (javadrive.jp)
Welcome to Python.org
HTMLの中に少し埋め込めず、基本的にプログラムの中にHTMLを埋め込む：CGI(Perl然)
さくらインターネットでPython CGI (mwsoft.jp)
WSGI Python で WSGI (Web Server Gateway Interface) に従ったシンプルな Web サーバで Hello World - Qiita
Python用Webサイト用途フレームワーク：Flask(軽量)、Django
WSGI について — Webアプリケーションフレームワークの作り方 in Python (c-bata.link)
GCPでどう使うかは不明だがホスティングは↓
ウェブ ホスティング | Google Cloud 静的ウェブサイトのホスティング | Cloud Storage | Google Cloud
str.split() 区切り文字で分割しリスト等に入れる Pythonで文字列を分割（区切り文字、改行、正規表現、文字数） | note.nkmk.me
print('Sam' in 'I am Sam') # True 任意の文字列を含むか判定: in演算子 Pythonで文字列を検索（〜を含むか判定、位置取得、カウント） | note.nkmk.me
＝＝＝＝＝＝＝＝＝＝＝＝＝＝
ここで動かせるgoogle colaboratory→ Colaboratory へようこそ - Colaboratory (google.com)
コラボラトリはマークダウン Qiita マークダウン記法 一覧表・チートシート - Qiita
半角スペース２個で改行#の数で見出し*で箇条書き数字と.で番号を振る、- でリスト* or - or _ を３つ以上で水平線[ ]でチェックボックス、[x]でチェック| td | td | td |でテーブル**aaa**で太字、*aaa*で斜体~~aaa~~で打消し線[タイトル](URL)でリンク```でコードの挿入、`でインライン挿入> or >> で引用
[^1]で注釈\バックスラッシュでマークダウンのエスケープ
＝＝＝＝＝＝＝＝＝＝＝＝＝＝
宗教論争（事実は同じでも他人の認知は違うので意味なし
if self.flag_ok == 1 and self.mode == '1'
↓一見で分からんなら変数名を工夫してこうやんな
if self.file_verify_completed and self.mode == GRANT_PERMISSION:
マジックナンバーを使わない(数字の方が曖昧性が無い場合も)STATUS_ERROR = -1STATUS_SUCCESS = 0self.status_error = STATUS_SUCCESS
with構文で処理の前後でコンテキストマネジャ__enter__、__exit__が使われる __enter__メソッドで事前処理 __exit__メソッドで事後処理with ファイル操作や通信などの開始時の前処理と終了時の後処理など必須となる処理を自動で実行try/finallyみたいなもの、最初と最後に何かしてくれるclass a(object): def_enter_(self): print 'sss' return 'sss111' def_exit__(self, type, value, traceback): print 'ok' return Falsewith a() as s: print sssssss111okPythonのwith文の正体 (zenn.dev)
初期値をエラー値にし、業務判定エラーでステータスを設定したら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)
ケチって分厚い本1冊にしたが全然進まぬ、薄い奴星e、？チッPython、誰がJSONじゃ～い、チェーンソー魔わすっぞ
続編、、モジュールとかmportとか、
/// BANGBOO BLOG /// - Python Python
(More)
Comment (0)
Navi: < 8 | 9 | 10 | 11 >
-Home
-Column [128]
-Europe [9]
-Gadget [77]
-Web [133]
-Bike [4]
@/// BANGBOO BLOG ///
