/// BANGBOO BLOG ///
■23/6/21 8:00PM
Machine learning(Bigquery ML)
機械学習：　マシーンラーニング、ML。マッシーンがLearnしデータの背景にあるルールやパターンを発見する。モデル：　機械学習における入力データに対して結果(出力)を導き出す仕組み。モデルは入力されたデータを解析し、評価/判定を行った結果を出力として返す。つまり、機械学習は「入カ＞モデル＞出力」から成る。
学習データ　モデルをつくるために学習させるデータ適用データ　モデルに対して予測を適用させるデータ教師あり　学習データに対して正解ラベルを付けて学習する方法　　例)過去にDMを送付した結果(目的変数)を用いて学習させる教師ありは、回帰と分類の2つに分けられます。　回帰(予测)　　連続する数値を予測するもので、売上、重量、温度などを算出する　分類(識別)　　データがどのクラスに属するかを予測するもので、販売商品が売れる見込み/売れない見込みなどに分類　　DMにおいては分類モデル(買う・買わないの識別)を使用
目的変数　結果側の変数　例)ある特定のカテゴリやブランドをその顧客が注文する　　●確率(購入する・しない)　　　ー学習データにターゲットフラグ (購入あり=1、購入無し=0)を立てる　　　ー適用後に出てくる結果は0～1 (0%～100%) の範囲　　●金額説明変数(特微量とも呼ぶ) 　原因側のデータ　目的変数を予測するために使用する顧客の様々な情報、大きく分けて2種類　　●顧客属性　　　ー法人/個人事業主など事業形態、登録住所の郵便番号、業種コードなど　　●購買履歴　　　ー購買 いつ、何を、どのくらい購入したか等の情報　　　ー各商品カテゴリ毎の購入金額 etc.質的変数　数値で推し測ることができ、数字の大小に意味をもつデータ　　(例)売上、購入回数、従業員数、DM送付回数單的変数　単に分類や種類を区別するためだけのデータ　　(例)都道府県、茎種コード、購入有無
統計手法　重回帰分析:　　説明変数xが目的変数yの値を変化させます。そのため、説明変数から、目的変数の値を予測可能です。　ロジスティック回帰　　yが1になる確率を判別します。いくつかの要因(説明変数)から「2値の結果(目的変数)」が起こる確率を説明・予測　　　2値の結果とは合格/不合格、採用/不採用など、答えが2つしかない場合を指し

線形回帰（エクセル）
　slope関数：回帰直線の傾きを取得
　inercept関数：回帰直線の切片を取得
　forecast関数：単回帰分析からの推測値を取得
+++++++++++++

BigQueryを利用して機械学習を簡易に学びたいです。Webアプリのログよりサーバーエラーを予測するようなもので方法を教えてください　> BigQueryを利用して機械学習を学び、Webアプリのログからサーバーエラーを予測する方法について簡単に解説します。以下はその手順の概要です。
●ステップ1: データの準備まず、WebアプリのログデータをBigQueryにインポートします。ログデータには、タイムスタンプ、リクエストの詳細、ステータスコード、エラー メッセージなどの情報が含まれているはずです。
例:ログデータスキーマ server_logtimestamp: TIMESTAMPrequest_id: STRINGstatus_code: INTEGERerror_message: STRINGuser_agent: STRINGresponse_time: FLOAT
●ステップ2:データの前処理エラーログの解析を容易にするために、データをクリーンアップし、特徴量を作成します。
CREATE OR REPLACE TABLE ds.cleaned_log ASSELECTtimestamp,status_code,error_message,user_agent,response_time,IF(status_code >= 500, 1, 0) AS is_errorFROMds.server_logWHEREtimestamp BETWEEN 2023-01-01 AND 2023-12-31;
このクエリでは、ステータスコードが500以上のものをエラーと見なし、is_error列を追加しています。
●ステップ3:特徴量エンジニアリング機械学習モデルに投入するための特徴量を作成します。例えば、以下のような特徴量を考えます。-時間帯 (hour)-ユーザーエージェント (user_agent) -過去のエラーレート(past_error_rate)※past_error_rateを省くとどうなのか?
CREATE OR REPLACE TABLE ds.features ASSELECTEXTRACT(HOUR FROM timestamp) AS hour,user_agent,response_time,is_error,--AVG(is_error) OVER (PARTITION BY user_agent ORDER BY timestamp RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW) AS past_error_rateAVG(is_error) OVER (PARTITION BY user_agent) AS past_error_rateFROMds.cleaned_log;
●ステップ4: 機械学習モデルの作成BigQuery MLを使用してモデルを作成します。ここではロジスティック回帰モデルを使用してエラーの予測を行います。
他には、The CREATE MODEL statement  |  BigQuery  |  Google Cloud
MODEL_TYPE = { 'LINEAR_REG' |'LOGISTIC_REG' |'KMEANS' |'MATRIX_FACTORIZATION' |'PCA' |'AUTOENCODER' |'AUTOML_CLASSIFIER' |'AUTOML_REGRESSOR' |'BOOSTED_TREE_CLASSIFIER' |'BOOSTED_TREE_REGRESSOR' |'RANDOM_FOREST_CLASSIFIER' |'RANDOM_FOREST_REGRESSOR' |'DNN_CLASSIFIER' |'DNN_REGRESSOR' |'DNN_LINEAR_COMBINED_CLASSIFIER' | 'DNN_LINEAR_COMBINED_REGRESSOR' |'ARIMA_PLUS' |'ARIMA_PLUS_XREG' |'TENSORFLOW' |'TENSORFLOW_LITE' |'ONNX' |'XGBOOST'}
CREATE OR REPLACE MODEL ds.error_prediction_modelOPTIONS(model_type='logistic_reg', input_label_cols=['is error'] ASSELECThour, user_agent, response_time, past_error_rate,is_errorFROMds.features;
●ステップ5: モデルの評価モデルを評価し、精度を確認します。
SELECT*FROM ML.EVALUATE(MODEL, `ds.error.prediction_model`,(SELECT hour,user_agent,response_time,past_error_rate,is_errorFROMds.features));
●ステップ6: エラーの予測新しいデータを使ってエラーを予測します。
SELECT*predicted_is_error,predicted_is_error_probsFROMML.PREDICT(MODEL `ds.error_prediction_model`,(SELECTEXTRACT(HOUR FROM timestamp) AS hour,user_agent,response_time,--AVG(CASE WHEN status_code 500 THEN 1 ELSE 0 END) OVER (PARTITION BY user_agent ORDER BY timestamp RANGE BETWEEN INTERVAL 1 DAY PRECEDING AND CURRENT ROW) AS past_error_rateAVG(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) OVER (PARTITION BY user_agent) AS past_error_rateFROMds.new_server_log));

●ダミーデータINSERT INTO ds.server_log (timestamp, request_id, status_code, error_message, user_agent, response_time)VALUES('2024-06-28 18:00:00 UTC', 'req 801, 208, '', 'Mozilla/5.0 (Windows NT 18.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36', 0.123), (2024-06-20 10:01:00 UTC', 'req 002, 588, Internal Server Error', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36', 8.456),(2024-06-28 10:02:00 UTC', 'req 003', 484, 'Not Found', 'Mozilla/5.0 (iPhone; CPU iPhone OS 14,6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.1, 8.234),(2024-06-20 10:03:00 UTC', 'req 004', 200, '', 'Mozilla/5.0 (Windows NT 18.8; Win64; x64; rv:89.0) Gecko/20100181 Firefox/89.8, 0.345),(2024-06-28 10:04:00 UTC, 'req 005', 502, Bad Gateway', 'Mozilla/5.0 (Linux; Android 11; SM-G9918) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Mobile Safari/537.36, 0.678),(2024-86-28 10:05:00 UTC, 'req 006', 503, 'Service Unavailable', 'Mozilla/5.0 (iPad; CPU OS 14.6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/6084.1, 0.789), (2824-86-28 18:06:00 UTC, req 007, 200, Chrome/91.0.4472.124 Safari/537.36, 0.567), Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko)(2024-06-2010:07:00 UTC, 'req 008, 500, Internal Server Error', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Safari/537.361, 0.890),(2024-06-20 18:08:00 UTC, req 009, 404, Not Found', 'Mozilla/5.0 (iPhone; CPU iPhone OS 14 6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.11', 8.345),('2024-06-28 18:09:00 UTC', 'req 010', 200, '', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:89.0 Gecko/20100101 Firefox/89.0', 0.456);

INSERT INTO ds.new_server_log (timestamp, request_id, status_code, error_message, user_agent, response_time)VALUES(2024-06-21 09:00:00 UTC', 'req 101', 200, '', 'Mozilla/5.0 (Windows NT 18.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36', 0.112), (2024-06-21 09:01:08 UTC, req 102', 500, Internal Server Error', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Safari/537.36, 0.478),(2024-06-21 09:02:00 UTC', 'req 183, 484, 'Not Found', 'Mozilla/5.0 (iPhone; CPU iPhone OS 14_6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safar1/684.1, 0.239),(2024-06-21 09:03:00 UTC', 'req 104, 200, Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:89.0) Gecko/20100101 Firefox/89.0, 8.301),
(2024-06-21 09:04:08 UTC, req 185', 502, 'Bad Gateway', 'Mozilla/5.0 (Linux; Android 11; SM-G9918) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Mobile Safari/537.36', 8.683),(2024-06-21 09:05:00 UTC, req 106', 503, Service Unavailable', 'Mozilla/5.0 (iPad; CPU OS 14,6 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.1, 0.756),
(2024-06-21 09:06:00 UTC, req 107, 208, ", Mozilla/5.0 (Windows NT 18.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Safari/537.36, 0.523),
(2024-06-21 09:07:00 UTC, req 188, 500, Internal Server Error, Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.8.4472.124 Safari/537.36, 0.812),('2024-06-21 09:08:08 UTC', 'req 109,, 404, 'Not Found', 'Mozilla/5.0 (iPhone: CPU iPhone OS 14,6 1ike Mac OS X) AppleWebKit/685.1.15 (KHTML, like Gecko) Version/14.1.1 Mobile/15E148 Safari/604.1', 0.267),('2024-06-21 09:09:08 UTC', 'req 110', 200, '', 'Mozilla/5.0 (Windows NT 18.8; Win64: x64; rv:89.0) Gecko/20180101 Firefox/89.8', 8.412);

Comment (0)

■23/6/1 12:00AM
GCP Python Google doc編集
Google Docのコピーや編集https://developers.google.com/docs/api/how-tos/documents?hl=jahttps://rimever.hatenablog.com/entry/2019/10/16/060000クイックスタートhttps://developers.google.com/docs/api/quickstart/python?hl=jaスコープ情報 https://developers.google.com/identity/protocols/oauth2/scopes?hl=ja#docsディスカバリ ドキュメント例えばこれはDriveAPIの分だが、RESTAPIで何ができるか全記載しているっぽいhttps://www.googleapis.com/discovery/v1/apis/drive/v3/restDrive APIhttps://developers.google.com/drive/api/guides/about-sdk?hl=jahttps://developers.google.com/drive/api/reference/rest/v3?hl=jaDocs APIhttps://developers.google.com/docs/api/concepts/document?hl=jahttps://developers.google.com/docs/api/reference/rest?hl=jahttps://googleapis.github.io/google-api-python-client/docs/epy/index.htmlhttps://googleapis.github.io/google-api-python-client/docs/dyn/docs_v1.htmlhttps://developers.google.com/docs/api/reference/rest/v1/documents/get?hl=ja

文字置換 https://developers.google.com/docs/api/how-tos/merge?hl=ja

※DocAPIからdriveld folderidは取得できなさそう、getは使えそう※DriveAPIが使えない?コピーでなくDocAPIでget body からの新規createで行く?共有ドライブ時は、supports All Drives=True が必要だったでOKfile_metadata = service.files().get(fileld=DOCUMENT_ID, fields=id, name, mimeType, driveld', supports AllDrives=True) execute()
サービスアカウントでGWSにアクセスするにはGWS OU設定等が必要な場合がある>Google一般共有Docで検証も可
あるGoogle Docをコピーし、本文を編集した上で　本文の編集は((sample))となっている文字列をAAAに置換する特定のドライブのフォルダに移動
from google.oauth2.service_account import Credentialsfrom googleapiclient.discoveryimport build import re#1. サービスアカウントの認証情報を設定SCOPES = ['https://www.googleapis.com/auth/documents','https://www.googleapis.com/auth/drive']SERVICE_ACCOUNT_FILE = 'path/to/your/service-account-file.json' #サービスアカウント のJSONファイルのパス
creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
#2. Google Docs と Driveのサービスをビルドdocs_service = build('docs', 'v1', credentials=creds)drive_service = build('drive', 'v3', credentials=creds)
#3. コピー元のGoogle DocのIDと、移動先のフォルダIDを設定SOURCE_DOCUMENT_ID = 'source_doc_id' #コピー元のドキュメントIDTARGET_FOLDER_ID = 'target_folder_id' #移動先のフォルダID
#4. Google Docをコピーcopied_doc = drive_service.files().copy(fileld=SOURCE_DOCUMENT_ID, body={"name": "Copied Document"), supportsAllDrives=True).execute()copied_doc_id = copied_doc['id']
#5、本文を取得し、{{sample}} をAAAに置換def replace_text(document_id, old_text, new_text) #ドキュメントの内容を取得 document = docs_service.documents().get(documentid=document_id).execute() content = document.get('body').get('content') #リクエストリスト requests = [] #検索と置換を行う for element in content: if 'paragraph' in element: for paragraph_element in element['paragraph']['elements']: if 'textRun' in paragraph_element: text = paragraph_element['textRun']['content'] if old_text in text: start_index = paragraph_element('startindex'] end_index = paragraph_element['endIndex'] requests append({ 'replaceAllText': { 'containsText': { 'text': re.escape(old_text), #エスケープなしにする必要有 'matchCase': True }, 'replaceText': new_text } }) #置換リクエストを実行 if requests: docs_service.documents().batchUpdate(documentid=document_id, body={'requests':requests}).execute()
#置換処理の実行replace_text(copied_doc_id, '((sample))', 'AAA')
#6、コピーしたドキュメントを指定のフォルダに移動drive_service.files().update(fileld=copied_doc_id, addParents=TARGET_FOLDER_ID, removeParents=copied doc['parents'][0], supportsAllDrives=True).execute() #親が取れないのでフォルダはハードコード
print(f"Document copied, edited, and moved successfully! Document ID: {copied_doc_id)")



Comment (0)

■23/5/29 7:30PM
GCP hands-off 2
■プロジェクト削除時のサービスアカウントプロジェクトは30日保留される。その間サービスアカウント権限は生きており他プロジェクトでは動作する。しかし保留期間はプロジェクトを使用できずサービスアカウントを削除できず、個別に一つ一つ権限をはく奪するしかない。
サービスアカウントはプロジェクト削除前に、必要であれば事前に削除や無効化しておくことも検討する。

■連携
GoogleWorkspace -> GAS -> GCP Oauth + API -> GCP(Bigquery etc.)Python -> Gcloud sdk -> gcloud auth -> GCP(Bigquery etc.) <-> federation query/Connected sheet
Python(Oauth key) -> GCP認証情報(Oauth Key) + API -> GoogleWorkspace
　GCPのクレデンシャルページでOauth2.0 client IDと鍵が発行でき、鍵でイケる
　　Pythonコードで鍵を指定すると実行時にログインを求められ、client IDとユーザIDを紐づけして実行することになる　　Authentication — gspread 5.7.2 documentation
　　Python でシンプルに OAuth 2 する (urllib + oauthlib) - Qiita
　GCPのクレデンシャルページでAPIキーも発行でき、これは可能性はあるPython -> local csv/tsvが基本
●Python(SA key) -> GCP認証情報(SA Key) + API -> GoogleWorkspace
　サービスアカウントでGWSにアクセスできないのでダメ
　　信頼しているドメインとのみ外部共有を許可する - Google Workspace 管理者 ヘルプ
　　サービス アカウント（ドメイン名の末尾が「gserviceaccount.com」）を信頼しているドメインにすることはできません
　　　OUで許可するとイケるはずだが、、
●Python でGoogle docをイジるGoogle Docs APIを使って文章を作成してみる - より良いエンジニアを目指して (hatenablog.com)Google Docs APIを使って、索引を作成する #Python - Qiitaスコープ情報 Google API の OAuth 2.0 スコープ  |  Authorization  |  Google for Developers下記のURLの内容を検証すればよいPython のクイックスタート  |  Google Docs  |  Google for DevelopersGoogle CloudコンソールでOauth同意画面を設定Google Docs APIを有効化OAuth クライアントIDを作成シークレットJson ファイルができるのでDL（リネーム）コードにクレデンシャルJSONファイルとDocのURLに含まれるdocumentIDを記述→Python実行するとDocのデータが取れる（ローカルの場合は楽）

/// runのデプロイ時に設定を入れる方法について１）環境変数を(コンソール/cmd/コンテナymiのどれかで)設定:env=os environ.get("ENV") で使う。ログに出やすく非推奨２）シークレットマネージャ保存分を設定環境変数＋コードでやるのと同じ？３）ボリュームを使う：クレデンを入れ、トークンの一時保存ができる？Cloud RunでSecret Managerを使いたい #Python - Qiita
※サービスアカウントでGWSを扱うにはGWSのOUで受け入れる設定が必要な場合がある

■secret managerに保存してコードで呼び出して使う Secret Managerのシークレットアクセサー権限 シークレット バージョンにアクセス  |  Secret Manager Documentation  |  Google Cloud (checksumをかけている)
from google.cloud import secretmanagerimport google.cloud.loggingimport loggingdef get_url(secret_key, project_num) logging.warning('####### secret_key' + str(secret_key) + '######') client = secretmanager.SecretManagerServiceClient() resource_name = "projects/()/secrets/()/versions/latest.format(project_num, secret_key) res = client.access_secret_version(resource_name) slack_url = res.payload.data.decode("utf-8") return slack_url
■API等でデータを取った時中身が分からない場合pramsが何かわからん時print(params)print(type(params))#<class 'proto.marshal.collections.maps.MapComposite'>#よくわからんクラスでもdirで保持するAttributeが分かるattributes = dir(params)print(attributes)#そこに含まれるメソッドも確認できるのでhelpするhelp(params.get)　#prams.get('query')すると含まれるSQLが分かりこれで進める等

■Protocol buffersAPIの返りはGoogleは自社で開発したProtocol buffersを使っていようだたとえば下記が返るname: "projects/98765"parent: "folders/12345"project_id: "aaaaaa-bbbb-market"state: "ACTIVE"display_name: "aaaaaa-bbbb-market"create_time{ seconds: 1601250933 nanos: 820000000}update_time{ seconds: 1632826231 nanos: 634000000}etag: "W/a06910d9093db111"labels{ key: "budget_group" value: "cccc"}
これはprint (type(response))すると下記であり<class "google.cloud. resourcemanager v3.types.projects.Project"> print (response.project_id) で簡単にデコードし値取得できることが分かる
APIからの値を取るときのコードfrom google.cloud import resourcemanager_v3client = resourcemanager_v3.ProjectsClient()request resourcemanager v3.ListProjectsRequest{ #組織の場合、現状は権限がGOP側で用意がなく無理だった #parent organizations/12345678. parent="folders/1122233344"}page_result = client.list_projects(request=request)for response in page result: print(type(response)) print (response.project_id)
エンコードする場合 https://blog.imind.jp/entry/2019/12/28/124728pip install googleapis-common-protos でインスコ？sudo apt install protobuf-compiler　でインスコ
sudo apt-get install protobuf-compiler　でインスコ　※google提供のフォルダごと使用しようとして失敗した方法 　※googleapis/google/cloud/resourcemanager/v3/projects.proto at master · googleapis/googleapis · GitHub　※にprotoファイルがあるが丸々必要なので下記でDL　※git clone https://github.com/googleapis/googleapis.git　※バスを合わせてprojects.protoを使うが失敗　※たとえば protoc python_out=. --proto_path=googleapis ./googleapis/google/cloud/resourcemanager/v3/projects.protoprojects.proto を下記の内容で一から作成することが必要だったsyntax proto3;
message Resource{ string name = 1; string parent = 2;  string project_id = 3; string state = 4; string display_name = 5;  map<string, string> create_time = 6; map<string, string> update_time = 7; string etag = 8; map<string, string> labels = 9;}そして下記を実行しコンパイルprotoc --python_out=. ./projects.protoprojects_pb2.pyが生成されるため、パッケージとして読みこみprotocol buffersを実行できるようになるimport projects_pb2.py※なおエラーで pip install U protobuf=3.20.0でダウングレードした
注意点としては、pythonとprotocol bufferとBigqueryの型合わせが必要　DateやTimestampはUNIXエポックからの日数や秒数に変換する必要がある　Noneをstr 'None'や、int -1や、bool FalseにPythonで調整をするBigQuery Storage Write API を使用してデータを一括読み込み、ストリーミングする  |  Google Cloud

//UNIXエポックからの日数current_date = datetime.now()epoch = datetime(1970, 1, 1)record_date = (current_date - epoch).days
//UNIXエポックからの秒数data_string = str(date_v)dt_obj = datetime.fromisoformat(date_string.replace("Z","+00:00"))epoch = datetime(1970, 1, 1, tzinfo=timezone.utc)seconds_since_epoch = (dt_obj - epoch).tatal_seconds()microseconds_since_epoch = int(seconds_since_epoch * 1e6)date_v = microseconds_since_epoch
■BQ APIクォータ割り当て超過(1000件insertしようとした）割り当てと上限  |  BigQuery  |  Google Cloudテーブル変更1日1500件までテーブルメタデータ変更は10sあたり5回までテーブル当たりDMLの実行待ちキューは20件までテーブル当たり10sあたり25のDMLまで→各insertでスリープを5秒入れた　import time　time.sleep(5)
↓
上限がテーブル単位のためテーブル名を分けると回避できるらしいGCP BigQuery 応用編 ~制限について~ - 自称フルスタックエンジニアのぶろぐ。 (hatenablog.com)
↓■BQ streaming insert->BQ storage read/write APIの上限はDMLと別で、閾値が大きい
streaming insert -> Bigquery storage write API を使うINFORMATION_SCHEMAを用いたBigQueryのストレージ無駄遣い調査 - ZOZO TECH BLOGBigQuery Storage Write API を使用してデータを一括読み込み、ストリーミングする  |  Google Cloud
Storage Write API を使用したデータ読み込みのバッチ処理  |  BigQuery  |  Google Cloud

CreateWriteStream > AppendRows(ループ) > FinalizeWriteStream > BatchCommitWriteStreams
　をstart/append/send/close(write commit)の関数化し返り値でつなげた形にしたが　sendをした後 proto_rows = types.ProtoRows() を掛け初期化する必要があった（offsetが倍々で増えたから）offsetで送信毎の開始行の設定も必要（一連の処理で件数を記憶しており0固定で処理を書けないようだった）
■Python/Client libraryの値をBQに入れるにあたり仕様書で型を調べる。STRUCTやクラスは紐解いて通常のカラムでBQに挿入timestampやboolやint64はそのままの形でBQに挿入BQ SQL：日付は値なしならNULLを入れる、数値やBool値はクォートで囲まないPythonでSQLインサート文を作るとき改行コードが含まれるものをセットするとSyntax errror:Unclosed string literalq = q.replace('//', '////')　バックスラッシュをエスケープ、あるとイリーガルエスケープシーケンスとなる、raw文字列にしたい？
q = q.replace('/n', '//n')　改行をエスケープq = q.replace("'", "\\'")　SQLが途切れないようシングルクォートをエスケープq = q.replace('/n', '    ')　改行を空白で置き換える
■変更の判断
変更で問題がでないか→PCにマウスと付けて問題が起こらないかという問題と相似、最終的に経験で判断するしか

■監査ログからSetIamのメソッドを取りBQ権限付与を検知するクエリ
WITH source AS(SELECT*FROM `project-logging.organization_audit_log_v2.cloudaudit_googleapis_com_activity_20*`WHERE_TABLE_SUFFIX = format_date('%y%m%d', current_date("Asia/Tokyo"))),project source AS(SELECTROW_NUMBER() OVER (ORDER BY timestamp) as id,*FROM sourceWHEREprotopayload_auditlog.methodName = 'SetlamPolicy'project_authorizationinfo AS(SELECTDISTINCTid,__ori.resource.type as type,__ori.resource.labels.project_id as project_id,__ori.resource.labels.dataset_id as dataset_id,protopayload_auditlog.methodName as method_nameprotopayload_auditlog.resourceName as resource_name,protopayload_auditlog.authenticationInfo.principal Email as email_manipulator,authorizationInfo.resource as request_resource,authorizationInfo.permission as request_permission,authorizationInfo.granted as request_granted,protopayload_auditlog.requestMetadata.callerlp as callerlp,protopayload_auditlog.requestMetadata.callerSuppliedUserAgent as callerSuppliedUserAgent,FROM project_source AS __ori). UNNEST (protopayload_auditlog.authorizationInfo) AS authorizationInfoproject_bindingdeltas AS(SELECTid,--array_binding Deltas_project as binding Deltas_project,array_binding Deltas_project.action as action_project,array_binding Deltas_project.member as member_project,array_binding Deltas_project.role as role_project,timestampFROM project_source AS_ori,UNNEST (protopayload_auditlog.servicedata_v1_iam.policyDelta.bindingDeltas) AS array_binding Deltas_project),project_setiam AS(SELECT--*, except(id)type,project_id,dataset_id,method_name,resource_name,email_manipulator,request_resource,request_permission,request_granted,callerip,callerSuppliedUserAgent.action_project,member_project,role project.CAST(NULL AS STRING) AS metadataJson,CAST(NULL AS STRING) AS bindingDeltas_dataset,CASTINULLAS STRING AS action_dataset,CAST(NULL AS STRING) AS member_dataset,CAST(NULL AS STRING) AS role_dataset,CAST(NULL AS STRING) AS bindingDeltas_table,CAST(NULL AS STRING) AS action_table,CAST(NULL AS STRING) AS member_table,CAST(NULL AS STRING) AS role_table,timestampFROM project_authorizationinfoLEFT JOIN project_bindingdeltas ON project_authorizationinfo.id = project_bindingdeltas.idWHERE role_project LIKE 'roles/bigquery%),resource_source AS (SELECT__ori.resource.type as type,__ori.resource.labels.project_id as project id,__ori.resource.labels.dataset_id as dataset_id,protopayload_auditlog.methodName as method_name,protopayload_auditlog.resourceName as resource_name,protopayload_auditlog.authenticationInfo.principalEmail as email_manipulator,authorizationInfo.resource as request_resource,authorizationInfo.permission as request_permission,authorizationInfo.granted as request_granted,protopayload_auditlog.requestMetadata.callerlp as callerlp,protopayload_auditlog.requestMetadata.callerSuppliedUserAgent as callerSuppliedUserAgent,protopayload_auditiog.metadataJson,timestampFROM source AS __ori,UNNEST(protopayload_auditlog.authorizationInfo) AS authorizationInfo WHEREprotopayload_auditlog.methodName = 'google.iam.v1.IAMPolicy.SetlamPolicy' --AND timestamp= "2024-03-11 04:11:30.885258 UTC"),resource_id AS (SELECTROW_NUMBER() OVER (ORDER BY timestamp) as id,*FROM resource_source),resource_bq_dataset AS (SELECTid as id_dataset,json_extract(metadataJson, '$.datasetChange bindingDeltas') as bindingDeltas_dataset,json_extract(array_bindingDeltas_dataset, '$action') as action_dataset,json_extract(array_bindingDeltas_dataset, $.member') as member_dataset,json_extract(array_bindingDeltas_dataset, '$.role') as role_dataset,FROM resource_id,UNNEST(json query_array(metadataJson, '$.datasetChange.bindingDeltas')) AS array_bindingDeltas_dataset),resource_bq_table AS (SELECTid as id table,json_extract(metadataJson, '$.tableChange.bindingDeltas') as bindingDeltas_table,json extract(array_bindingDeltas_table, '$.action') as action table,json_extract(array_bindingDeltas_table. '$.member') as member table,json_extract(array_bindingDeltas_table, '$.role') as role_table,FROM resource_id,UNNEST(json query_array(metadataJson, '$.tableChange.bindingDeltas')) AS array_bindingDeltas_table),resource_setiam AS ( SELECT--*except(id, id_dataset, id_table)type,project_id,dataset_id,method_name,resource_name,email_manipulator,request_resource,request_permission,request_granted,callerlp,callerSuppliedUserAgent,CAST(NULL AS STRING) AS action_project,CAST(NULL AS STRING) AS member_project,CAST(NULL AS STRING) AS role_project,metadataJson,bindingDeltas_dataset,action_dataset,member_dataset,role_dataset,bindingDeltas_table,action_table,member_table,role_table,timestampFROM resource_idLEFT JOIN resource_bq_dataset ON resource_id.id = resource_bq_dataset.id_datasetLEFT JOIN resource_bq_table ON resource_id.id = resource_bq_table.id_table)SELECT * FROM project_setiamUNION ALLSELECT * FROM resource_setiam

■BQからCloudSQLにデータを入れる (GCSを経由する、コマンドやPythonがあるbq query --use_legacy_sql=false 'CREATE OR REPLACE TABLE `prj.ds._table` AS SELECT FROM `prj.ds.view`';bq extract -destination_format CSV 'prj.ds._table' gs://bucket/tbl.csvgcloud sql import csv インスタンス名 gs://bucket/tbl.csv --database=データベース名 --table=テーブル名

■ログの重複をなくす
import google.cloud.loggingimport logging
# クライアントの作成client = google.cloud.logging.Client()
# Cloud Logging ハンドラを追加client.get_default_handler()client.setup_logging()
# 既存のハンドラをすべて削除for handler in logging.root.handlers[:]:    logging.root.removeHandler(handler)
# 新しいハンドラを追加logging.basicConfig(level=logging.INFO)
# logging.basicConfig(level=logging.DEBUG)  # DEBUG レベルからすべてのレベルを記録
# propagate を無効にして重複を防ぐlogger = logging.getLogger()logger.propagate = False
# 各ログレベルでテストlogging.debug('This is a DEBUG log')logging.info('This is an INFO log')logging.warning('This is a WARNING log')logging.error('This is an ERROR log')logging.critical('This is a CRITICAL log')
■何度かAPIコールを繰り返す
def safe_replace_text(document_id, old_text, new_text, max_attempts=3):    for attempt in range(max_attempts):        try:            replace_text(document_id, old_text, new_text)            break  # 成功した場合はループを抜ける        except Exception as e:            print(f"Attempt {attempt + 1} failed: {e}")            if attempt == max_attempts - 1:                print("Reached maximum attempts.")
■Exponential Backoffで時間を指数級数的にゆらぎながら増やすリトライimport timeimport randomdef exponential_backoff(max_retries=5, base_wait_time=1, max_wait_time=32):    retries = 0    while retries < max_retries:        try:            # APIリクエストの送信            response = send_request()            if response.status_code == 200:                return response  # 成功時に結果を返す        except Exception as e:            wait_time = min(base_wait_time * (2 ** retries), max_wait_time)            wait_time += random.uniform(0, 1)  # ランダムなズレを追加（Jitter）            print(f"Retrying in {wait_time} seconds...")            time.sleep(wait_time)            retries += 1    raise Exception("Max retries reached, request failed")
クォータの増加の依頼もできるが、基本的に下記の上限がある
1. Google Docs API の利用上限

これらの制限を超えると、リクエストが拒否されるか、APIを利用できなくなることがあります。

2. Google Drive API の利用上限
Comment (0)

Navi: <  1 | 2 | 3 | 4  >
-Home
-Column [128]
-Europe [9]
-Gadget [77]
-Web [133]
-Bike [4]
@/// BANGBOO BLOG ///