社内システムをGCPへ移行②

GMO NIKKOのS.Tです。

社内システムをクラウド化(移行中)する機会があったので共有します。

今回はその第二回目として下記を説明します。

・Cloud Functions

・Cloud SQL

・App Engine

全体図

現在オンプレで稼働中のシステムからなるべく変更点を少なくして工数を抑えつつ費用もあまりかからない方法になっていると思います。

Cloud Functions

比較的最近PHPも使えるようになっていたので個人的に使いやすいPHPで実装してみます。

Cloud Storageの指定バケットにファイルがアップロードされるとトリガーとしてCloud Functionsが実行されるように設定します。

アップされたファイル名やそのバケット名等の情報が$cloudevent->getData()からを取得できるのでそれを使用してCloud SQL(PostgreSQL)へ登録します。

バケット名からテーブル名を取得してPostgreSQLのテーブル情報からプライマリキーを取得します。

CSVのヘッダー名とテーブルのカラム名は同じにしているのでそのヘッダー名とプライマリキーを使用して

insert into on conflict do updateのSQLを動的に作成して登録しています。

今後項目が追加になってもこのプログラムの修正は必要なくなりますね。

<?php

use Google\CloudFunctions\CloudEvent;
use Google\Cloud\Storage\StorageClient;

$log = fopen(getenv('LOGGER_OUTPUT') ?: 'php://stderr', 'wb');

function main(CloudEvent $cloudevent)
{
    global $log;

    try {
        $data = $cloudevent->getData();
        if (substr($data['name'], -3) !== '.gz') {
            return;
        }

        $storage = new StorageClient();
        $storage->registerStreamWrapper();
        $bucket = $storage->bucket($data['bucket']);
        $object = $bucket->object($data['name']);
        if ($object->exists()) {
            $conn = new PDO('pgsql:dbname=***;host=/cloudsql/***:asia-northeast1:***', '***', '***', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
            $path = 'gs://' . $data['bucket'] . '/' . $data['name'];
            $content = gzdecode($object->downloadAsString());

            // バケットファイル名からテーブル名を取得
            preg_match('/(\d{6})\d{2}\-.+\-(.+?)\./', $data['name'], $match);
            $tableName = strtolower($match[2]) . '_reports_' . $match[1];

            // BOMを削除
            if (substr($content, 0, 3) === pack('C*', 0xEF, 0xBB, 0xBF)) {
                $content = substr($content, 3);
            }

            // テーブルのプライマリキーを取得
            $sql = <<<EOS
                select ccu.column_name 
                from information_schema.table_constraints tc
                left join information_schema.constraint_column_usage ccu
                on 
                    (tc.table_catalog=ccu.table_catalog) and 
                    (tc.table_schema=ccu.table_schema) and 
                    (tc.table_name=ccu.table_name) and 
                    (tc.constraint_name=ccu.constraint_name)
                where tc.table_name = '{$tableName}'
                and tc.constraint_type = 'PRIMARY KEY'
EOS;
            $primaryKey = '';
            foreach ($conn->query($sql) as $value) {
                $primaryKey .= ($primaryKey==='' ? '' : ', ') . $value['column_name'];
            }

            $rows = preg_split('/\r\n/', $content);
            $csvColumnArray = array();
            foreach ($rows as $row) {
                if ($row === '') {
                    continue;
                }
                if ($row === reset($rows)) {
                    // CSVのカラム名からSQLを作成
                    $row = rtrim($row, '"');
                    $row = ltrim($row, '"');
                    $csvColumnArray = preg_split('/","/', $row);
                    $columns = $csvColumnArray;
                    $columns[0] = ltrim($columns[0], '"');
                    $columns[count($columns)-1] = rtrim($columns[count($columns)-1], '"');
                    $sql = 'insert into reports.' . $tableName . ' (';
                    $sql .= implode(', ', $columns);
                    $sql .= ') values (';
                    $csvColumn = ':' . implode(', :', $columns);
                    $sql .= $csvColumn;
                    $sql .= ') ';
                    $sql .= 'on conflict (' . $primaryKey . ') ';
                    $sql .= 'do update set ';
                    $updateSql = '';
                    foreach ($columns as $value) {
                        if ($value === 'syscreated') continue;

                        $updateSql .= ($updateSql==='' ? '' : ', ') . $value . ' = :' . $value;
                    }
                    $sql .= $updateSql;
                    $stmtTarget = $conn->prepare($sql);
                    continue;
                }

                $row = rtrim($row, '"');
                $row = ltrim($row, '"');
                $columns = preg_split('/","/', $row);
                $param = array_combine($csvColumnArray, $columns);
                // ''があると失敗するので置換
                foreach ($param as &$value) {
                    if ($value === '') {
                        $value = null;
                    }
                }
                $stmtTarget->execute($param);
                if ($stmtTarget->errorCode() !== '00000') {
                    $info = $stmtTarget->errorInfo();
                    fwrite($log, $stmtTarget->errorCode() . ':' .  print_r($info, true));
                }
            }
        }
    }
    catch (Exception $e) {
        fwrite($log, '【Error】:' . $e->getMessage());
    }
    finally {
    }
}

100

101

102

103

104

105

106

107

108

109

110

<?php

use Google\CloudFunctions\CloudEvent;

use Google\Cloud\Storage\StorageClient;

$log = fopen(getenv('LOGGER_OUTPUT') ?: 'php://stderr', 'wb');

function main(CloudEvent $cloudevent)

{

global $log;

try {

$data = $cloudevent->getData();

if (substr($data['name'], -3) !== '.gz') {

return;

}

$storage = new StorageClient();

$storage->registerStreamWrapper();

$bucket = $storage->bucket($data['bucket']);

$object = $bucket->object($data['name']);

if ($object->exists()) {

$conn = new PDO('pgsql:dbname=***;host=/cloudsql/***:asia-northeast1:***', '***', '***', array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

$path = 'gs://' . $data['bucket'] . '/' . $data['name'];

$content = gzdecode($object->downloadAsString());

// バケットファイル名からテーブル名を取得

preg_match('/(\d{6})\d{2}\-.+\-(.+?)\./', $data['name'], $match);

$tableName = strtolower($match[2]) . '_reports_' . $match[1];

// BOMを削除

if (substr($content, 0, 3) === pack('C*', 0xEF, 0xBB, 0xBF)) {

$content = substr($content, 3);

}

// テーブルのプライマリキーを取得

$sql = <<<EOS

select ccu.column_name

from information_schema.table_constraints tc

left join information_schema.constraint_column_usage ccu

(tc.table_catalog=ccu.table_catalog) and

(tc.table_schema=ccu.table_schema) and

(tc.table_name=ccu.table_name) and

(tc.constraint_name=ccu.constraint_name)

where tc.table_name = '{$tableName}'

and tc.constraint_type = 'PRIMARY KEY'

EOS;

$primaryKey = '';

foreach ($conn->query($sql) as $value) {

$primaryKey .= ($primaryKey==='' ? '' : ', ') . $value['column_name'];

}

$rows = preg_split('/\r\n/', $content);

$csvColumnArray = array();

foreach ($rows as $row) {

if ($row === '') {

continue;

}

if ($row === reset($rows)) {

// CSVのカラム名からSQLを作成

$row = rtrim($row, '"');

$row = ltrim($row, '"');

$csvColumnArray = preg_split('/","/', $row);

$columns = $csvColumnArray;

$columns[0] = ltrim($columns[0], '"');

$columns[count($columns)-1] = rtrim($columns[count($columns)-1], '"');

$sql = 'insert into reports.' . $tableName . ' (';

$sql .= implode(', ', $columns);

$sql .= ') values (';

$csvColumn = ':' . implode(', :', $columns);

$sql .= $csvColumn;

$sql .= ') ';

$sql .= 'on conflict (' . $primaryKey . ') ';

$sql .= 'do update set ';

$updateSql = '';

foreach ($columns as $value) {

if ($value === 'syscreated') continue;

$updateSql .= ($updateSql==='' ? '' : ', ') . $value . ' = :' . $value;

}

$sql .= $updateSql;

$stmtTarget = $conn->prepare($sql);

continue;

}

$row = rtrim($row, '"');

$row = ltrim($row, '"');

$columns = preg_split('/","/', $row);

$param = array_combine($csvColumnArray, $columns);

// ''があると失敗するので置換

foreach ($param as &$value) {

if ($value === '') {

$value = null;

}

$stmtTarget->execute($param);

if ($stmtTarget->errorCode() !== '00000') {

$info = $stmtTarget->errorInfo();

fwrite($log, $stmtTarget->errorCode() . ':' . print_r($info, true));

}

catch (Exception $e) {

fwrite($log, '【Error】:' . $e->getMessage());

}

finally {

}

Cloud SQL

Cloud SQL(PostgreSQL)はWindowsで使い慣れたA5:SQLを使用して接続しています。

■下準備

1.Google Cloud SDKをインストール
https://cloud.google.com/sdk/#nix

2.Windows版プロキシクライアント(cloud_sql_proxy_x64.exe)をダウンロード
https://cloud.google.com/sql/docs/mysql/connect-external-app?hl=ja

3.サービスアカウントから秘密鍵のJSONファイルをダウンロード
https://console.cloud.google.com/iam-admin/serviceaccounts

4.プロキシクライアントを起動
コマンドプロンプトで下記を実行

<ダウンロードしたプロキシクライアントのファイル名> -instances=<インスタンス接続名>=tcp:5432 -credential_file=<ダウンロードしたJSONファイルのパス>

1	<ダウンロードしたプロキシクライアントのファイル名> -instances=<インスタンス接続名>=tcp:5432 -credential_file=<ダウンロードしたJSONファイルのパス>

■A5:SQLの設定

1.ここからA5:SQL Mk-2をダウンロード

A5_SQL Mk-2 – フリーのSQLクライアント_ER図作成ソフト

https://a5m2.mmatsubara.com/

2.データベースの追加と削除を選択して追加ボタンをクリックすると

下記のウィンドウが表示されるのでPostgreSQL (直接接続)をクリック

3.ホスト名をlocalhostに設定してインスタンス接続パスワードを入力

ダウンロードしたプロキシクライアントを起動していれば接続できるはず

App Engine

■application\config\database.phpを修正

CodeIgniterを使用しているので下記のように修正します。

$db['default']['dsn']      = '';
$db['default']['hostname'] = '/cloudsql/<インスタンス接続名>';
$db['default']['dbdriver'] = 'postgre';

$db['default']['dsn'] = '';

$db['default']['hostname'] = '/cloudsql/<インスタンス接続名>';

$db['default']['dbdriver'] = 'postgre';

■app.yamlを追加

主に使用言語とアップロードするファイルの拡張子を設定しています。

runtime: php72

handlers:
- url: /(.+\.(html|css|js|gif|png|jpg|txt|svg|ico|pdf))$
  static_files: \1
  upload: .+\.(html|css|js|gif|png|jpg|txt|svg|ico|pdf)$
  secure: always

- url: .*
  script: auto
  secure: always

runtime: php72

handlers:

- url: /(.+\.(html|css|js|gif|png|jpg|txt|svg|ico|pdf))$

static_files: \1

upload: .+\.(html|css|js|gif|png|jpg|txt|svg|ico|pdf)$

secure: always

- url: .*

script: auto

secure: always

■.gcloudignoreを追加

最近は対応しているようですが、アップロードするファイル名に日本語が含まれているとデプロイできなかったので

無視するファイルをここで設定しています。

盲点だったのがデフォルトだとログファイルまでアップロードしてしまい容量オーバーでデプロイできないのでそれも無視します。

# This file specifies files that are *not* uploaded to Google Cloud Platform
# using gcloud. It follows the same syntax as .gitignore, with the addition of
# "#!include" directives (which insert the entries of the given .gitignore-style
# file at that point).
#
# For more information, run:
#   $ gcloud topic gcloudignore
#
.gcloudignore
# If you would like to upload your .git directory, .gitignore file or files
# from your .gitignore file, remove the corresponding line
# below:
.git
.gitignore

# PHP Composer dependencies:
/vendor/

# ignore 2byte character files
/xxx/xxxxx/
︙

# ignore log files
/tmp/
/application/logs/

# This file specifies files that are *not* uploaded to Google Cloud Platform

# using gcloud. It follows the same syntax as .gitignore, with the addition of

# "#!include" directives (which insert the entries of the given .gitignore-style

# file at that point).

# For more information, run:

# $ gcloud topic gcloudignore

.gcloudignore

# If you would like to upload your .git directory, .gitignore file or files

# from your .gitignore file, remove the corresponding line

# below:

.git

.gitignore

# PHP Composer dependencies:

/vendor/

# ignore 2byte character files

/xxx/xxxxx/

︙

# ignore log files

/tmp/

/application/logs/

あとはapp.yamlを追加したディレクトリでgcloud app deployすればカレントプロジェクトにデプロイされるので、

gcloud app browseを実行すればデプロイされたWebアプリケーションが表示されると思います。

あと当然ですが、使用データベースを変更している場合はSQLも修正する必要があります。

S.T

全体図

Cloud Functions

Cloud SQL

App Engine

おすすめ

GoogleのVertex AI PaLM Embedding APIを使って類似記事タイトルをレコメンドする

Duet AIのBigQueryサポート機能を試す

プレビュー機能: BigQuery のパーティショニングとクラスタリングの Recommender を覗いてみる

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル