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を紐づけして藹??行することになる
Python -> local csv/tsvが基譛?
笳?Python(SA key) -> GCP認証情蝣?(SA Key) + API -> GoogleWorkspace
サービスアカウント縺?GWSにアクセスできないのでダ繝?
信頼しているドメインとのみ藹??部共有を許可する - Google Workspace 管理者 ヘルプ サービ繧? アカウント（ドメイン名の末尾が「gserviceaccount.com」）を信頼しているドメインにすることはできません
OUで許可するとイケるはずだが、、
笳?Python 縺?Google docをイジる
下記縺?URLの内容を検証すればよい
Google 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のシークレットアクセサー権限
(checksumをかけている)
from google.cloud import secretmanager
import google.cloud.logging
import logging
def 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 buffers
APIの鐔??り縺?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_v3
client = 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/124728
pip install googleapis-common-protos でインスコ・??
sudo apt install protobuf-compiler でインス繧?
sudo apt-get install protobuf-compiler でインス繧?
窶?google觸??供のフォルダごと使用しようとして失敗した方觸??
※縺?protoファイルがあるが丸々必要なので臀??記縺?DL
窶?git clone https://github.com/googleapis/googleapis.git
※バスを合繧?せ縺?projects.protoを使うが失敗
※たとえ縺? protoc python_out=. --proto_path=googleapis ./googleapis/google/cloud/resourcemanager/v3/projects.proto
projects.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.proto
projects_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で調整をする
//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しようとした）
テーブル藹??譖?1譌?1500件ま縺?
テーブルメタデータ藹??更縺?10sあたり5回ま縺?
テーブル藹??たりDMLの藹??行待ちキュー縺?20件ま縺?
テーブル藹??たり10sあたり25縺?DMLま縺?
→各insertでスリープを5秒入れた
import time
time.sleep(5)
↓
上限がテーブル単位のためテーブル名を分けると回避できるらしい
■BQ streaming insert->BQ storage read/write APIの臀??限縺?DMLと別で、閾値が大きい
streaming insert -> Bigquery storage write API を使う
■Python/Client libraryの値をBQに入れるにあたり
仕様書で型を調べる。STRUCTやクラスは軆??解いて通常のカラム縺?BQに挿蜈?
timestampやboolやint64はそのままの形縺?BQに挿蜈?
BQ SQL：日臀??は値なしならNULLを入れる、数値やBool値はクォートで囲まない
Python縺?SQLインサート文を作るとき改鐔??コードが含まれるものをセットする縺?Syntax errror:Unclosed string literal
q = 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(
SELECT
ROW_NUMBER() OVER (ORDER BY timestamp) as id,
*
FROM source
WHERE
protopayload_auditlog.methodName = 'SetlamPolicy'
project_authorizationinfo AS(
SELECT
DISTINCT
id,
__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.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 authorizationInfo
project_bindingdeltas AS(
SELECT
id,
--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,
timestamp
FROM 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,
timestamp
FROM project_authorizationinfo
LEFT JOIN project_bindingdeltas ON project_authorizationinfo.id = project_bindingdeltas.id
WHERE 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,
timestamp
FROM source AS __ori
,UNNEST(protopayload_auditlog.authorizationInfo) AS authorizationInfo
WHERE
protopayload_auditlog.methodName = 'google.iam.v1.IAMPolicy.SetlamPolicy'
--AND timestamp= "2024-03-11 04:11:30.885258 UTC"
),
resource_id AS (
SELECT
ROW_NUMBER() OVER (ORDER BY timestamp) as id,
*
FROM resource_source
),
resource_bq_dataset AS (
SELECT
id 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 (
SELECT
id 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,
timestamp
FROM resource_id
LEFT JOIN resource_bq_dataset ON resource_id.id = resource_bq_dataset.id_dataset
LEFT JOIN resource_bq_table ON resource_id.id = resource_bq_table.id_table
)
SELECT * FROM project_setiam
UNION ALL
SELECT * 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.csv
gcloud sql import csv インスタンス名
gs://bucket/tbl.csv --database=データベース名 --table=テーブル名
■ログの重複をな縺?す
import google.cloud.logging
import 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.")
