2019-08-27

Cloud StorageからBigQueryに定期的にデータをロードする

使用するマネージドサービス

Cloud Composerのようなワークフローを使用せずにCloud Storageにあるデータを毎日BigQueryにロードしたい時に Google BigQuery Data Transfer Service | Google BigQuery Data Transfer Service | Google Cloud を使用すると簡単にデータロードを行うことが出来ます。

データ準備

アイリスデータをダウンロード

アイリスデータを使用してBigQueryに取り込んで行きたいと思います。 GithubからCSV形式データをローカルPCにダウンロードしておきます。

$ wget https://raw.githubusercontent.com/pandas-dev/pandas/master/pandas/tests/data/iris.csv

データには1行目にカラムのヘッダーがあり、5カラム分のデータは保存されています。

SpealLength : がく片の長さ
SepalWidth : がく片の幅
PetalLength : 花びらの長さ
PetalWidth : 花びらの幅
Name : あやめの種類

Cloud Storageにアイリスデータにアップロード

バケット名やオブジェクトのパスは適宜設定してください
- 今回はiris_csv ディレクトリ配下にYYYYMMDDの日付形式のフォルダを作成して、その中にCSVファイルをアップロードしています。

$ gsutil cp iris.csv gs://$BUCKET/iris_csv/YYYYMMDD/

BigQuery

テーブル作成

コマンドラインとWeb UIでテーブル作成を用意しておきましたので、どちらか一方の手段でテーブル作成してください。

コマンドライン

gistsに使用するBigQueryのスキーマファイルを保存してあるのでローカルPCにダウンロードしておきます。

$ wget https://gist.githubusercontent.com/hirokiokumura/80059ca2cbfade4a2dd1ff3f1aadac49/raw/3d52234da05a50bd609b45a9b4bdf5afc32bd26a/iris_fields.json

bqコマンドを使用しテーブルを作成します
- $DATASETは適宜データセット名を環境変数に設定してください

$ bq mk --time_partitioning_type=DAY $DATASET.iris ./iris_fields.json

Web UI

f:id:aidazuki:20190813131244p:plain

BigQuery Data Transfer Service for Cloud Storage

画面遷移は[BigQuery] > [転送]から行います
Destination table
${run_time|"%Y%m%d"}はData Transfer Serviceが実行した際に評価されてYYYYMMDD形式に変換されます

iris${run_time|"%Y%m%d"}

Cloud Storage URI
- $BUCKETは適宜変更してください

gs://$BUCKET/iris_csv/{run_time|"%Y%m%d"}/iris.csv

f:id:aidazuki:20190813135329p:plain

参考サイト

2017-02-27

CloudSQL Proxyコンテナ経由でCloud SQLマスター・リードレプリカに接続する

CloudSQL Kubernetes

GCP Cloud SQL

マスター(dev-test)とリードレプリカ(dev-test-replica)のインスタンスを用意しておきます。

インスタンス名

マスターとリードレプリカのインスタンス接続名になります。
- マスター
- リードレプリカ

Mysqlユーザ

Cloud SQLのdevというデータベースにcloudsqlproxy経由で接続するマスター用とリードレプリカ用のユーザを作成します。

マスター用ユーザ

ユーザ名:cloudmaster, パスワード:secret

CREATE USER 'cloudmaster'@'cloudsqlproxy~%' IDENTIFIED BY 'secret';
GRANT SELECT,INSERT,UPDATE,DELETE ON dev.* TO 'cloudmaster'@'cloudsqlproxy~%';
FLUSH PRIVILEGES;

リードレプリカ用ユーザ

ユーザ名:cloudread, パスワード:secret

CREATE USER 'cloudread'@'cloudsqlproxy~%' IDENTIFIED BY 'secret';
GRANT SELECT ON dev.* TO 'cloudread'@'cloudsqlproxy~%';
FLUSH PRIVILEGES;

コンテナ

認証情報

cloudsql-proxyコンテナがCloudSQLに接続出来るようにするためにサービスアカウントで作成した際にダウンロードしたキーを指定してます。
- この設定はKubernetesのDevelopmentファイルに渡されます。

$kubectl create secret generic cloudsql-oauth-credentials --from-file=credentials.json=[PATH_TO_CREDENTIAL_FILE]

go言語で開発環境や本番環境ごとにMysqlのユーザ名、パスワードを環境変数で変更出来るようになっている場合は同様に設定しておきます。--from-literal=usernameと--from-literal=passwordは作成したMysqlユーザとパスワードになります。
- マスター用

$kubectl create secret generic cloudsql-master-user --from-literal=username=cloudmaster
$kubectl create secret generic cloudsql-master-password --from-literal=password=secret

リードレプリカ用

$kubectl create secret generic cloudsql-read-user --from-literal=username=cloudread
$kubectl create secret generic cloudsql-read-password --from-literal=password=secret

Kubernetes

CloudSQL Proxyコンテナ

CloudSQL ProxyコンテナからCloud SQLのマスター・リードレプリカに接続する設定になります。
- ポート番号: マスター(3306), リードレプリカ(3307)
- -instancesのproject_idはGCPのプロジェクトIDを適宜変更して下さい。
- -instancesにマスターのインスタンス接続名を設定した後にカンマ区切りにした後にリードレプリカのインスタンス接続名を設定します。

- image: b.gcr.io/cloudsql-docker/gce-proxy:1.05
        name: cloudsql-proxy
        command: ["/cloud_sql_proxy", "--dir=/cloudsql",
                  "-instances=project_id:asia-east1:dev-test=tcp:3306,project_id:asia-east1:dev-test-replica=tcp:3307",
                  "-credential_file=/secrets/cloudsql/credentials.json"]
        volumeMounts:
          - name: cloudsql-oauth-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
      volumes:
        - name: cloudsql-oauth-credentials
          secret:
            secretName: cloudsql-oauth-credentials

Developmentファイル

アプリケーション(go言語)のgo-appコンテナとCloudSQL Proxyコンテナを一つのpodに含めた場合のDevelopmentファイルになります。
- CloudSQLインスタンスに接続するイメージ
  - go-appコンテナからlocalhost接続でcloudsql-proxyを経由してCloudSQLインスタンスに接続されるようになります。

apiVersion: extensions/v1beta1
kind: Deployment
metadata:
  name: app
spec:
  replicas: 1
  template:
    metadata:
      labels:
        app: deployment-example
    spec:
      containers:
      - name: go-app
        image: asia.gcr.io/project-id/go-app:latest
        imagePullPolicy: Always
        env:
          - name: MASTER_USER
            valueFrom:
              secretKeyRef:
                name: cloudsql-master-user
                key: username
          - name: MASTER_PASSWORD
            valueFrom:
              secretKeyRef:
                name: cloudsql-master-password
                key: password
          - name: READ_USER
            valueFrom:
              secretKeyRef:
                name: cloudsql-read-user
                key: username
          - name: READ_PASSWORD
            valueFrom:
              secretKeyRef:
                name: cloudsql-read-password
                key: password
        ports:
        - containerPort: 8080
          protocol: TCP
        resources:
            requests:
                cpu: 20m
                memory: 20Mi
     - image: b.gcr.io/cloudsql-docker/gce-proxy:1.05
        name: cloudsql-proxy
        command: ["/cloud_sql_proxy", "--dir=/cloudsql",
                  "-instances=project_id:asia-east1:dev-test=tcp:3306,project_id:asia-east1:dev-test-replica=tcp:3307",
                  "-credential_file=/secrets/cloudsql/credentials.json"]
        volumeMounts:
          - name: cloudsql-oauth-credentials
            mountPath: /secrets/cloudsql
            readOnly: true
      volumes:
        - name: cloudsql-oauth-credentials
          secret:
            secretName: cloudsql-oauth-credentials
        - name: ssl-certs
          hostPath:
            path: /etc/ssl/certs

aidazuki blog