CDKTF を使った GCP 上の FTP サーバ構築と BigQuery・スプレッドシート連携

はじめに

こんにちは、株式会社TERASSのかんです。
Cloud Development Kit for Terraform (CDKTF) を活用し、Google CloudにFTPサーバーを構築します。さらに、Cloud Storage をマウントし、BigQuery からスプレッドシートまでデータをシームレスに連携できる仕組みを整えます。本記事では、その実装プロセスを詳しく解説していきます。

シーケンス図

想定する運用フロー

本システムでは、FTPを通じてCSVをアップロードし、それをBigQuery・スプレッドシートへ連携することで、データの可視化や分析をスムーズに行えるようにします。

• FTP ユーザー (A)
  • 業務担当者が定期的にCSVをFTPにアップロード
• FTP サーバ (B)（GCP上の VM）
  • CloudStorageをローカルディレクトリとしてマウント (gcsfuse 使用)
  • アップロードされたCSVは即座にCloud Storageへ保存
• Cloud Storage (C)
  • 直接データを保存するストレージ
  • BigQueryからの定期取得に利用
• BigQuery (D)
  • スケジュールクエリでCloudStorageからCSVを取得・INSERT
• Google スプレッドシート (E)
  • 取得データをスプレッドシートに出力し、業務担当者が確認可能に
  • 差分データをエクスポートすることで、不要なデータの蓄積を防ぐ

この構成により、業務担当者は手作業でデータをアップロードするだけで、自動的にBigQuery・スプレッドシートへデータが反映される仕組みを構築できます。
また、全てGCP上で完結するため、外部サービスとの複雑な連携が不要で、メンテナンスや管理がシンプルになるというメリットもあります。

CDKTFによるインフラ構築手順

• GCPでサービスアカウントの鍵(JSON)を作成
  • GCPコンソールでサービスアカウントを作成し、鍵をダウンロード
  • JSONファイルとしてローカルに保存
• ローカル環境にCDKTFをインストール

  pnpm add -g cdktf-cli
  

• プロジェクトのセットアップ

  mkdir cdktf-project && cd cdktf-project
  pnpm init
  pnpm add --save-dev cdktf
  

• ローカルでSSH鍵を作成

  ssh-keygen -t rsa -b 4096 -f ~/.ssh/your-account
  

CDKTFを用いたGCPリソースの作成

以下のTypeScriptコードをmain.tsに記述し、CDKTFを実行してGCPインフラを構築します。

main.ts

import { Construct } from "constructs"
import { App, TerraformStack } from "cdktf"
import { readFileSync } from "fs"
import { GoogleProvider } from "@cdktf/provider-google/lib/provider"
import { ComputeNetwork } from "@cdktf/provider-google/lib/compute-network"
import { ComputeSubnetwork } from "@cdktf/provider-google/lib/compute-subnetwork"
import { ComputeAddress } from "@cdktf/provider-google/lib/compute-address"
import { StorageBucket } from "@cdktf/provider-google/lib/storage-bucket"
import { ComputeInstance } from "@cdktf/provider-google/lib/compute-instance"
import { ComputeFirewall } from "@cdktf/provider-google/lib/compute-firewall"

class MyStack extends TerraformStack {
  constructor(scope: Construct, id: string) {
    super(scope, id)
    
    const projectId = "yourProject"
    const region = "asia-northeast1"
    const credentials = readFileSync("your credentials json file path", "utf-8")
    const zone = "asia-northeast1-a"
    const sshPublicKeyPath = `${process.env.HOME}/.ssh/your-account.pub`
    const sshPublicKey = readFileSync(sshPublicKeyPath, 'utf8').trim()

    new GoogleProvider(this, "Google", {
      project: projectId,
      region: region,
      credentials,
    })

    const network = new ComputeNetwork(this, "teraThreeVPC", {
      name: `${projectId}-vpc`,
      autoCreateSubnetworks: false,
    })

    const subnet = new ComputeSubnetwork(this, "teraThreeSubnet", {
      name: `${projectId}-subnet`,
      region: region,
      network: network.id,
      ipCidrRange: "10.0.0.0/24"
    })

    new ComputeFirewall(this, "allowSsh", {
      name: "allow-ssh",
      network: network.id,
      allow: [{
        protocol: "tcp",
        ports: ["22"],
      }],
      direction: "INGRESS",
      sourceRanges: ["0.0.0.0/0"]
    })

    new StorageBucket(this, 'yourbucket', {
      name: `${projectId}-bucket`,
      location: region
    })

    const staticIp = new ComputeAddress(this, 'teraThreeStaticIp', {
      name: `${projectId}-static-ip`,
      region: region
    })

    new ComputeInstance(this, 'teraThreeVm', {
      name: `${projectId}-vm-instance`,
      machineType: 'e2-micro',
      zone: zone,
      bootDisk: {
        initializeParams: {
          image: 'projects/ubuntu-os-cloud/global/images/family/ubuntu-2204-lts',
        },
      },
      networkInterface: [{
        network: network.name,
        subnetwork: subnet.name,
        accessConfig: [{
          natIp: staticIp.address,
        }],
      }],
      metadata: {
        'ssh-keys': `your account:${sshPublicKey}`,
      }
    })
  }
}

const app = new App()
new MyStack(app, "infrastructure")
app.synth()

CDKTFの実行

cdktf get
cdktf synth
cdktf deploy

サーバー設定

SSH接続

CDKTFで作成したサーバーにSSH接続します。

ssh -i ~/.ssh/your-account your-user@your-instance-ip

gcsfuseのインストール

Cloud StorageをVMにマウントするためにgcsfuseをインストールします。

curl -fsSL https://packages.cloud.google.com/apt/doc/apt-key.gpg | sudo tee /usr/share/keyrings/gcloud-archive-keyring.gpg > /dev/null
echo "deb [trusted=yes] https://packages.cloud.google.com/apt gcsfuse-stretch main" | sudo tee /etc/apt/sources.list.d/gcsfuse.list
sudo apt update
sudo apt install -y gcsfuse

サービスアカウント認証

Cloud StorageへアクセスするためにGCPのサービスアカウントを認証します。

gcloud auth activate-service-account --key-file=your credentials json file path

Cloud Storageのマウント

Cloud StorageをVMにマウントし、FTP経由でアップロードされたCSVをBigQueryで取得できるようにします。

sudo mkdir -p /mnt/your-bucket
sudo /usr/bin/gcsfuse -o allow_other --only-dir=your-folder --uid=0 --gid=$(getent group gcsusers | cut -d: -f3) --file-mode=770 --dir-mode=770 --implicit-dirs your-bucket /mnt/your-bucket

マウントが成功したか確認するために、以下のコマンドでファイル一覧を確認できます。

ls -l /mnt/your-bucket

サーバを再起動するとマウントが解除されるため再起動時、マウントコマンドが実行されるように設定しておく必要があります。crontabなどで設定しておくと楽です。

BigQueryとの連携

CSVをBigQueryにロードするスケジュールクエリの設定

Cloud StorageにアップロードされたCSVをBigQueryに定期的に取り込むスケジュールクエリを作成します。

スケジュールクエリのサンプル

LOAD DATA INTO `your_project.your_dataset.your_table`
FROM FILES (
  format = 'CSV',
  uris = ['gs://your-bucket/your-folder/*.csv']
);

BigQueryのスケジュールクエリを作成し、一定間隔でCSVをロードするように設定します。

差分データをスプレッドシートにエクスポートする

BigQueryで処理したデータをGoogleスプレッドシートにエクスポートするには、スケジュールクエリを作成し、結果をgs://your-bucket/your-spreadsheet.csvにエクスポートします。

スケジュールクエリのサンプル

EXPORT DATA
OPTIONS (
  format = 'CSV',
  uri = 'gs://your-bucket/your-spreadsheet.csv',
  overwrite = true
)
AS
SELECT * FROM `your_project.your_dataset.your_table`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

これにより、最新のデータのみをスプレッドシートで確認できるようになります。

まとめ

この手順を実行することで、GCP上にFTPサーバを構築し、Cloud Storage、BigQuery、Googleスプレッドシートと連携するシステムを構築できます。