/// BANGBOO BLOG ///

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31


June 2, 2024 List
Cloud SQL on Jun 02, 2024 1:06 PM

June 2, 2024

Cloud SQL
■Cloud SQL Python Connector (Cloud SQL language Connector)
CloudSQL auth proxyのバイナリインストールでないやり譁?
Cloud SQL Python Connector自臀??は暗号化しないが、内驛?IPならサーバレ繧?VPCコネクタで暗号化された通信が使え安全になっている。外驛?IPアドレスの場合縺?Cloud SQL Auth Proxyで通信を暗号化。
Cloud SQL 言語コネクタの觸??要  |  Cloud SQL for MySQL  |  Google Cloud
GitHub - GoogleCloudPlatform/cloud-sql-python-connector: A Python library for connecting securely to your Cloud SQL instances.

事前必要(pip install>requirements.txt)
Flask==3.0.3
gunicorn==22.0.0
Werkzeug==3.0.3
google-cloud-bigquery==3.25.0
google-cloud-logging==3.11.1
google-cloud-secret-manager==2.20.2
google-api-python-client==2.141.0
google-auth-httplib2==0.2.0
google-auth-oauthlib==1.2.1
websocket-client==1.8.0
google-cloud-resource-manager==1.12.5
Flask-WTF==1.2.1
cloud-sql-python-connector==1.16.0
pymysql==1.0.3

from flask import Flask, jsonify
from google.cloud.sql.connector import Connector
from google.cloud import secretmanager
import pymysql

# 環藹??変数の藹??鄒?
PW_NAME = "sql-pw"
PROJECT_NUM = "1234567890"
DB_INSTANCE = "prj:asia-northeast1:db_instance"
DB_USER = "db-user"
DB_NAME = "db001"

# Secret Manager からパスワードを藹??得する関謨?
def get_pw(pw_name, project_num):
    client = secretmanager.SecretManagerServiceClient()
    resource_name = f"projects/{project_num}/secrets/{pw_name}/versions/latest"
    res = client.access_secret_version(name=resource_name)
    credentials = res.payload.data.decode("utf-8")
    return credentials

# Cloud SQL接続
def sql_getconn(connector):
    pw = get_pw(PW_NAME, PROJECT_NUM)
    conn = connector.connect(
        DB_INSTANCE,
        "pymysql",
        user=DB_USER,
        password=pw,
        db=DB_NAME,
        ip_type="private",
    )
    return conn

app = Flask(__name__)

@app.route('/test', methods=['GET'])
def get_table_data():
    try:
        connector = Connector()
        conn = sql_getconn(connector)
        cursor = conn.cursor()

        # SQLを実行して軆??果を藹??得
        cursor.execute("SELECT no, name, targetDate FROM test")
        rows = cursor.fetchall()

        # 結果をJSON形藹??に藹??觸??
        result = [
            {
                "no": row[0],
                "name": row[1],
                "targetDate": row[2].strftime("%Y-%m-%d %H:%M:%S") if row[2] else None
            }
            for row in rows
        ]

        cursor.close()
        conn.close()
        return jsonify(result), 200

    except Exception as e:
        return jsonify({"error": str(e)}), 500

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=8080)

=============
# 追加オプションを使った接続も藹??
    connector = Connector(
        ip_type="public",  # "private" また縺? "psc" も使用可閭?
        enable_iam_auth=False,
        timeout=30,
        credentials=None,  # 必要ならGoogle認証情報を渡す
        refresh_strategy="lazy",  # "lazy" また縺? "background"
    )

#トランザクショ繝?
    try:
        conn = sql_getconn(connector)
        conn.autocommit = False  # トランザクション開始、あるい縺? conn.begin()
        cursor = conn.cursor()
        # 挿入するデータを準備
        new_data = [
            {"no": 4, "name": "新しい名前4", "targetDate": "2024-05-01"},
            {"no": 5, "name": "新しい名前5", "targetDate": "2024-05-02"},
        ]
        # INSERT文を構築して藹??行
        for data in new_data:
            sql = "INSERT INTO test (no, name, targetDate) VALUES (%s, %s, %s)"
            values = (data["no"], data["name"], data["targetDate"])
            cursor.execute(sql, values)
        conn.commit()  # トランザクションをコミット
        print("Data inserted successfully.")
    except Exception as e:
        conn.rollback()  # エラーが発生した場合はロールバッ繧?
        print(f"Transaction rolled back due to an error: {e}")
    finally:
        cursor.close()
        conn.close()

#カーソ繝?
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
　params: dict形藹??で藹??得#[{'no': 1, 'name': 'Alice',...}, ...]
cursor = conn.cursor(cursor=pymysql.cursors.SSCursor)
　大驥?のデータを効軆??的に藹??得するためにストリーミングで軆??果を処理
cursor.execute(query, params=None)
　cursor.execute("SELECT * FROM test WHERE no = %s", (1,))
　params: プレースホルダーに対応する値のタブルまたはリスト
cursor.executemany(query, param list)
　cursor.executemany("INSERT INTO test (no, name) VALUES (%s, %s)", [(1, 'Alice'), (2, 'Bob')])
　param list:繰り返し実行するパラメータのリストまたはタブルのリスト
cursor.fetchone()
　row = cursor.fetchone()
　#結果があれ縺? (1, 'Alice', "2025-01-01") のような形蠑?縺?1行のみ藹??得
cursor.rowcount
　print(cursor.rowcount) #影響を藹??けた行数を返す

■接続検証用コンテナをビルド (内驛?IPを使うrun逕?)
gcloud builds submit --tag asia-northeast1-docker.pkg.dev/prj/artifact_reg_name/app_name

■IAM？
Cloud SQL設藹??縺?Cloud SQL 管理者 (roles/cloudsql.admin)、Cloud SQL インスタン繧? ユーザ繝? (roles/cloudsql.instanceUser)等縺?IAMが要る?
IAMユーザならいる、ローカ繝?Userなら不要と思繧?れる、ローカルでもCloud SQL Client (roles/cloudsql.client)等は鐔??る

■Cloud SQL MySQL設藹??
【開発環藹??】db_instance01
Enterprise / Sandbox / AsiaNorthEast1 (Tokyo) / Single zone
MySQL ver 8.4
Shared core/1cpu 0.6GB/HDD/10GB(auto increase)
PrivateIP/設藹??縺?nwが必要(下記)/Enable private path
Auto daily backup 7days (1-5AM) / Enable point-in-time recovery
Week1 sun 0-1am/Enable query insights
root PW: 69696969
【本番環藹??】
Enterprise plus? キャッシュ使う?
窶?CloudSQL縺?TFファイルに鐔??載がな縺?てもTFステートファイル縺?PWを含めてしまうためTF化しない

- NW: projects/prj/global/networks/sql-vpc-nw
- Connection name: prj:asia-northeast1 db_instance01

ユーザの臀??成 sql-user/82828282
　PWをコードに入れない、シク繝?Mgrに臀??存

■MySQL
utf8mb4_ja_0900_as_ci_ksを使う？
_ai... アクセントを区別しない (Accent Insensitive)
_as... アクセントを区別する (Accent Sensitive)
_ci... 大文字・藹??文字を区別しない (Case Insensitive)
_cs... 大文字・藹??文字を区別する (Case Sensitive)
_ks... カナを区別する (Kana Sensitive)
_bin... バイナ繝?

utf8mb4_unicode_ciで縺?"ア”と“あ”は同じものとして扱繧?れる
utf8mb4_ja_0900_as_ci_ks で縺?"繧?"≠”あ”となりカタカナとひらがなを譏?確に区別できる
utf8mb4_ja_0900_as_ci_ks ならふりがなを使った並び替えで有蜉?
日本鐔??のデータがメインで觸??索やソートでひらがな・カタカナ・觸??点の区別が必要なら utf8mb4_ja_0900_as_ci_ks が驕?
日本鐔??と英鐔??を混ぜたデータや広く互觸??性を持たせたいなら utf8mb4_unicode_ci の方が無髮?


インデックスをどのカラムに臀??ればいいか迷った時の・??つの設計方針 #DB - Qiita
テーブルレコード数が1万件以上
全臀??のレコード数縺?5%程度に軆??り込めるカーディナリティ饅??さ
WHERE句の選択条件、または軆??合条件
主キーおよびユニークの列には臀??成が不要
インデックスは更新性能を劣化させる


データベースとテーブルの臀??成
CREATE DATABASE db;
USE db;
CREATE TABLE test (
    no INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(8) NOT NULL,
    targetDate TIMESTAMP NOT NULL,
    PRIMARY KEY (no),
    INDEX index_name (name),
    INDEX index_targetDate (targetDate)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_ja_0900_as_ci_ks;

ENUM型は選択肢で早いがALTERが面倒なの縺?varcharaにする
inquiry_type ENUM('bq', 'pii') NOT NULL
↓
inquiry_type VARCHAR(255) NOT NULL,

VARCHAR(255) (よく使繧?れる最大サイ繧?)
VARCHAR(1024) (長めの文字列)
VARCHAR(4096) (長文向け)
長いテキストを扱うならTEXT型
InnoDB 縺?1行の最大サイズは軆??8KB (8126/バイト)
長さ縺??メール縺?255で良い

サンプルデー繧?
INSERT INTO `table` (`name`, `date`) VALUES ('aaa', '2002-02-23');

ORMapperは面倒なの縺?SQLを使う
ORM Quick Start — SQLAlchemy 2.0 Documentation
【SQLAlchemy】Generic Typesと各遞?DBの型 対藹??陦?
SQLAlchemyでのテーブル藹??鄒? #Python - Qiita

■データベースフラ繧?
confが直接変更できなためフラグとしてパラメータを渡せる
　Cloud SQL studio (コンソール縺?MySQLが使える)
　　MySQLクライアントを使いたいならAuth proxyが必要
　HA構成だとフェールオーバーやリードレプリカ軆??が使える

■NW
Cloud SQLを徹藹??解説！ - G-gen Tech Blog
Cloud SQLが内驛?IPだとサーバレ繧?VPCコネクタ、or 外驛?IPならSQL + auth proxy
内驛?IPで良いの縺?VPCを作る、CloudSQLを内驛?IPで臀??る
サーバレ繧?VPCアクセスコネクタを作る
　vpc: sql-vpc-nw, subnet: sql-vpc-subnet 192.168.77.0/24
　　Gateway 192.168.77.1, Private Google Access On
　　sql-vpc-nw-ip-range 192.168.78.0/24 on cloudSQL
　　run-serverless-vpc-ac 192.168.79.0/28 on Run
ファイアウォールルールでポート (デフォルト縺?3306な縺?) を開謾?
Cloud Run 縺?NW設藹??で、サーバーレ繧? VPCコネクタを選択、ルートオプションとしてすべてのトラフィックを VPC コネクタ軆??由で送信を選択
CloudSQLを30分程度觸??けて起動、接続＞接続テスト

VPC（例: 10.0.0.0/16）
サブネット（Cloud SQL 用・??: 10.10.0.0/24（例: us-central1、VPC内）
サブネット（VPCコネクタ用・??: 10.8.0.0/28（RunからVPCへ通信用、VPC外）
　VPC コネクタのサブネット縺? 10.8.0.0/28 のような藹??さな軆??囲を使用、VPC外だがrun自臀??がVPC外だから？
　VPC コネクタはリージョン単位なので、Cloud Run 縺? Cloud SQL を同じリージョンに配置するのが望ましい
Google Cloudの内驛?NW設鐔??によりVPC内の異なるサブネット間でも通信可閭?
　VPC内なら異なるリージョンのサブネットでもOK（VPC自臀??には軆??囲を設藹??なしでサブネット縺?IPが被らなけれ縺?OKか縺?
　追加の設定なしで、例え縺? us-central1 縺? VM から asia-northeast1 縺? Cloud SQLに直接アクセス藹??

外驛?IPの場合：
アプリがrunならサイドカーコンテナとし縺?Auth Proxyを追加できる
　サイドカーは同Pod内なのでループバックアドレ繧?127.0.0.1あるい縺?localhost:5432 (Auth Proxy起動時に指定したポート) に通信しCloudSQLに接続する
GCE縺?DLし縺?Auth proxyインストールでもいい
　アプリのコネクタ縺?Auth Proxy動いているGCE縺?IP:ポート番号を指定に通信しCloudSQLに接続する
　FWでポートも開けるこ縺?

■run サービスアカウント
run-sql@prj.iam.gserviceaccount.com に藹??要な権限
　Cloud SQL Client (roles/cloudsql.client)
　Run Invoker (roles/run.invoker)
　Compute Network User (roles/compute.networkUser) -VPCコネクタを使用する

runを建てるが、InternalIPのため同プロジェクト同VPC縺?GCE を作成し移動し縺?CURLでテスト
curl -H "Authorization: Bearer $(gcloud auth print-identity-token)" "https://run-sql-test-1212124.asia-northeast1.run.app/test"

■MySQL縺?UUIDを使うか、連番を使うか? > ULIDを使う
UUIDは連番に対し
　セキュリティ臀??より安全、サーバが異なってもユニー繧?
　パフォーマンスが悪い (UUIDをプライマリキーにすると速度が落ちる場合がある)
連番縺?UUIDの両方を振り出してお縺?? > ULIDを使うことにする

Posted by funa : 01:06 PM | Web | Comment (0) | Trackback (0)