Railsのスキーマ情報からBigQueryテーブル定義を生成する

WEDの武者（@knu）です。

レシート買取アプリONEでは、膨大な購買データを分析するためBigQueryに情報を集約して大規模活用しています。主要なデータソースであるAlloyDB for PostgreSQLのデータベースからBigQueryにデータを転送するためのETLパイプラインが構築されていますが、最近、コスト削減の文脈からそのフローをリニューアルする機会がありました。

この記事では、RailsでActiveRecordを用いてデータベーステーブル群のスキーマ情報をBigQueryのTerraformリソース定義として出力する方法について解説します。端的に言えば、 db/schema.rb で以下のように表現されるテーブルスキーマから:

  create_table "scheduled_missions", id: :uuid, default: -> { "gen_random_uuid()" }, comment: "missionを特定期間のみ出現させるためのスケジュール情報", force: :cascade do |t|
    t.uuid "mission_id", null: false
    t.date "activated_on", null: false
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false, comment: "#bq:partitioning"
    t.index ["activated_on", "mission_id"], name: "index_scheduled_missions_on_activated_on_and_mission_id"
    t.index ["mission_id"], name: "index_scheduled_missions_on_mission_id"
  end

次のようなリソース定義を生成することを目指します。

locals {
  schema_template_scheduled_missions = <<-EOF
  [
    {
      "name": "id",
      "type": "STRING"
    },
    {
      "name": "mission_id",
      "type": "STRING"
    },
    {
      "name": "activated_on",
      "type": "DATE"
    },
    {
      "name": "created_at",
      "type": "TIMESTAMP"
    },
    {
      "name": "updated_at",
      "type": "TIMESTAMP"
    }
  ]
  EOF
}

resource "google_bigquery_table" "scheduled_missions" {
  project     = var.project_id
  dataset_id  = var.dataset_id
  table_id    = "scheduled_missions"
  description = "missionを特定期間のみ出現させるためのスケジュール情報"

  schema = local.schema_template_scheduled_missions
  time_partitioning {
    type  = "DAY"
    field = "updated_at"
  }
}

そうして生成されたリソース定義をTerraformでapplyすることでBigQueryデータセットにテーブルを作成ないし差分適用し、そこにPostgreSQLのデータを転送するという流れです。

スキーマ情報の取得

ActiveRecordでデータベースのスキーマ情報にアクセスするのはごく簡単です。ActiveRecord::Base.connection の tables メソッドでテーブル名の一覧が、 columns メソッドで指定したテーブルのカラム情報一覧が取得できます。

db/bigquery/ 以下に一テーブルごとに一ファイル ({table_name}.tf)生成するコードは、ざっと以下のような感じになります。

require 'fileutils'
require 'stringio'

class SchemaDumper
  def initialize
    @connection = ActiveRecord::Base.connection
  end

  def tables
    @connection.tables - %w[schema_migrations ar_internal_metadata]
  end

  def dump_all(dir = Rails.root.join('db/bigquery'), dataset_id: ENV.fetch('DATASET') { 'raw' })
    tables.each do |table|
      dump_tf(table, dir, dataset_id:)
    end
  end

  def dump_tf(table, dir, dataset_id:)
    columns = @connection.columns(table)
    tf_output = generate_tf(table, columns, dataset_id:)

    output_path = Pathname(dir).realdirpath.join("#{table}.tf")
    FileUtils.mkdir_p(File.dirname(output_path))
    File.write(output_path, tf_output)

    puts "Created #{output_path.relative_path_from(Dir.pwd)}"
  end

  private

  def generate_tf(table, columns, dataset_id:)
    schema = columns.map { |col|
      description, attrs = parse_comment(col.comment)
      {
        name: col.name,
        **parse_pg_type(col),
        description:,
      }.compact
    }

    # ...Terraformリソース定義の生成処理...
  end

  def parse_pg_type(col)
    # ...PostgreSQLの型情報のパース処理...
  end

  def parse_comment(comment)
    # ...コメントのパース処理...
  end
end

型のマッピング

データ転送によって精度を失ったりクエリ立式の際の利便性を損なうことのないように、PostgreSQLとBigQueryの間でできる限り性質の近いカラム型を対応させる必要があります。それぞれのデータベースエンジンがサポートする型の一覧を見てみましょう。

• PostgreSQLのカラム型
• BigQueryのカラム型

見ての通り、PostgreSQLの方が広範な型をサポートしていますが、BigQueryにもおおむね主要なものは揃っています。特に配列型、JSON型や地理空間型などの多次元量をサポートしているので、入れ物としては不足のない感じです。一方、2025年の執筆時点では

• UUID型はない
• タイムゾーン情報付きの日時型はない
• 地理空間に対応しない平面幾何型(GEOMERTY, POINT等)はサポートしていない

といった制約があり、アプリケーションによっては個別の対応が必要かもしれません。幸い、ONEでは日時型のタイムゾーン情報は用いておらず、空間情報もGEOGRAPHY型のみで事足りるため、UUID型をSTRINGにするだけで済みました。

それでは、 parse_pg_type の実装を見てみましょう。なお、カラム情報の詳細はコネクションアダプタ固有の表現になっているため、MySQLやSQLiteなど別のDBMSではマッピング元の型名も含め、異なる実装になることに注意してください。

• PostgreSQLの日時型には time zone ありなしのバリエーションがありますが、すべてTIMESTAMP型にマッピングしています。
• PostgreSQLの配列(ARRAY)型は、BigQueryでは mode に REPEATED を指定することで表現されます。
• 今回は NOT NULL 情報は含めないことにしました。必要な場合は mode を REQUIRED (デフォルトは NULLABLE) とすることで表現します。

class SchemaDumper
  TYPE_MAPPING = {
    'uuid' => 'STRING',
    'integer' => 'INTEGER',
    'bigint' => 'INTEGER',
    'float' => 'FLOAT',
    'decimal' => 'NUMERIC',
    'string' => 'STRING',
    'character varying' => 'STRING',
    'text' => 'STRING',
    'binary' => 'BYTES',
    'boolean' => 'BOOLEAN',
    'datetime' => 'TIMESTAMP',
    'date' => 'DATE',
    'json' => 'JSON',
    'jsonb' => 'JSON',
    'point' => 'GEOGRAPHY',
    'geography' => 'GEOGRAPHY',
  }.tap { |h|
    h.default_proc = ->(h, k) {
      case k
      when /\Atimestamp\b/
        'TIMESTAMP'
      when /\Atime\b/
        'TIME'
      when /\Anumeric\b/
        'NUMERIC'
      else
        raise "Unknown type: #{k}"
      end
    }
  }

  private

  def parse_pg_type(col)
    pg_type = col.sql_type
    type = TYPE_MAPPING[pg_type]
    info = { type: }

    if type == 'NUMERIC' && /\((?<precision>\d+),(?<scale>\d+)\)/ =~ pg_type
      info.update(precision:, scale:)
    end

    if col.array?
      info.update(mode: 'REPEATED')
    end

    info
  end
end

コメント情報の活用

BigQueryにはテーブルやカラムに説明文(description)を付ける機能があります。また、パーティショニング定義やポリシータグなど、BigQuery側に固有で持たせたい情報もあります。こうした情報をスキーマに含めてPostgreSQL側で定義できると、情報が一元化され、転送処理で一方向に同期することができるようになります。

この目的のため、PostgreSQLのテーブルやカラムのコメントを用いることにしました。単純にコメントをdescriptionにコピーするだけでなく、パーティショニングに使いたいカラムにその旨を記述したりポリシータグを指定するためにコメントを用いるということです。

今回は、以下のような「タグ」を定義して用いる規約にしました。

• テーブルコメントに #bq:skip と付加すると転送対象から外れる
• カラムコメントに #bq:partitioning と付加するとパーティショニング用のカラムになる
• カラムコメントに #bq:policy_tag=xxx と付加するとポリシータグが設定される

こうしたコメントはActiveRecordのmigrationでテーブルやカラムの comment オプションを通じて容易に設定できるので、Railsアプリケーションの開発サイクルに組み込むことができます。

カラムの説明文 #bq:partitioning #bq:policy_tag=xxx のようなコメントから ["カラムの説明文", { "bq:partitioning" => true, "bq:policy_tag" => "xxx" }] のような形式の情報を抽出する parse_comment の実装は以下のようになります。

  def parse_comment(comment)
    attrs = {}

    comment_sans_attrs = comment&.gsub(/(?:\A| )#([\w:]+)(?:=(\S+))?/) {
      attrs[$1] = $2 || true
      ''
    }&.presence

    [comment_sans_attrs, attrs]
  end

そして、テーブルに付されたコメントは ActiveRecord::Base.connection の table_options から、カラムに付されたコメントはカラムオブジェクトの comment メソッドから取得できます。

Terraformリソース定義の出力処理

最後に、スキーマ情報を元にTerraformリソース定義を生成する処理を実装します。生成される定義は、 google_bigquery_table リソースを想定しています。

class SchemaDumper
  module HCLSyntax
    refine String do
      def hcl_string
        inspect # mostly works
      end
    end
  end

  using HCLSyntax

  private

  def generate_tf(table, columns, dataset_id:)
    table_comment, table_attrs = parse_comment(@connection.table_options(table)&.dig(:comment))
    return if table_attrs['bq:skip']

    schema = columns.map { |col|
      description, attrs = parse_comment(col.comment)

      {
        name: col.name,
        **parse_pg_type(col),
        description:,
      }.compact
    }

    tf = StringIO.new

    tf.puts <<~HCL
      locals {
        schema_template_#{table} = <<-EOF
      #{JSON.pretty_generate(schema).indent(2)}
        EOF
      }

      resource "google_bigquery_table" "#{table}" {
        project     = var.project_id
        dataset_id  = #{dataset_id.hcl_string}
        table_id    = #{table.hcl_string}
        description = #{table_comment.hcl_string}

    HCL

    # ...ポリシータグなどはテンプレート機能で埋め込んでいるが今回は省略...
    tf.puts <<~HCL.indent(2)
      schema = local.schema_template_#{table}
    HCL

    partition_column = columns.find { |col|
      _, attrs = parse_comment(col.comment)
      attrs['bq:partitioning']
    }&.name

    if partition_column
      tf.puts <<~HCL.indent(2)
        time_partitioning {
          type  = "DAY"
          field = #{partition_column.hcl_string}
        }
      HCL
    end

    tf.puts <<~HCL
      }
    HCL

    tf.string
  end
end

HCL形式の出力を行う適当なgemが見当たらなかったので、単純に文字列で生成しています。文字列リテラルの生成に String#hcl_string メソッドを局所的に定義していますが、ひとまず String#inspect で事足りるため妥協しました。本来こうした用途には String#dump の方が適当なのですが、これだと日本語などの非ASCII文字はすべてエスケープシーケンスになってしまうので、より高い安全性と引き換えに可読性が犠牲になってしまいます。今回は安全性の懸念は薄いので、インフラエンジニアの管理上の認知負荷を減らすためにも inspect で済ませました。

律儀にインデントしたり桁数を合わせていますが、もちろん terraform fmt に食わせて整形結果を得る方が賢いです。今回は、CIで定義を生成してETLパイプラインのレポジトリに自動でPRを出すフローを組む上で、ツールの依存関係を増やしたくなかったためこうしています。

なお、紙面(?)の都合上、ポリシータグの実装は省略して掲載しています。

おまけ

実際の運用では、転送パイプラインで用いる federated query のSQL文生成なども実装しています。ご紹介できなかった部分からいくつかスキーマ情報の活用ヒントになる部分を抜粋して触れ、おまけとします。

プライマリキー情報の取得

Railsではテーブルのプライマリーキーとして id カラムを持たせることが既定となっていますが、複合プライマリーキーを持ったテーブルを扱わなければならないケースもあります。そのような場合に対応すべく、プライマリーキー情報を取得するためのメソッドは以下のように実装できます。（もちろんこれもPostgreSQL固有の方法です）

  def primary_keys(table)
    Array(@connection.primary_key(table) ||
      @connection.execute(<<~SQL).map { |row| row['attname'] })
        SELECT a.attname
        FROM pg_index i
        JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
        WHERE i.indrelid = '#{table}'::regclass
        AND i.indisprimary;
      SQL
  end

federated query で得られた値の型変換

BigQueryでPostgreSQLのデータソースから federated query でデータを取得した際、PostgreSQLの数値型や文字列型、 datetime/timestamp などの型は自動変換されますが、 uuid, geography, date, time などは変換されず、 INSERT INTO 〜 SELECT などで流し込もうとすると型エラーになります。また、 datetime や timestamp 型についても、タイムゾーンの扱いを誤るとまずいことになります。

そこで、 EXTERNAL_QUERY で通せる型で表現できる中間形式（基本的には文字列表現）を介し、適宜変換を施してやる必要があります。

UUID, GEOGRAPHY, TIMESTAMP 型のデータを受け取る federated query の例はこのようになります。

SELECT
 id,
 ST_GEOGFROMTEXT(coordinates) AS coordinates, -- テキスト表現からGEOGRAPHY型に変換
 mission_id,
 created_at,
 updated_at
FROM
 EXTERNAL_QUERY(
   "sample-project.region.alloydb-bellroy",
   """
   SELECT
     id::text AS id, -- 文字列に直す
     ST_AsText(coordinates) AS coordinates, -- テキスト表現に直す
     mission_id::text AS mission_id, -- 文字列に直す
     created_at AT TIME ZONE 'UTC' AS created_at, -- UTCで取得
     updated_at AT TIME ZONE 'UTC' AS updated_at  -- UTCで取得
   FROM
     mission_target_stores
   WHERE
     updated_at AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' >= '2025-03-01'
   """
 )

予約語の扱いとクオート処理

PostgreSQLやBigQueryには予約語が多く、テーブル名やカラム名に予約語を使うと文法エラーになることがあります。そのため、予約語を含む識別子は適切にクオートしてやる必要がありますが、予約語の一覧はそれぞれ異なり、クオート方法も二重引用符だったりバッククオートだったりします。

以下は、予約語の一覧を定義し、それに基づいてクオート処理を行うメソッドの例です。調べて一覧を特定するのもなかなかの手間だったので、共有のためここに載せておきます。それぞれ、以下を情報源としています。

• PostgreSQL: Appendix C. SQL Key Words
• BigQuery: Lexical structure and syntax - Reserved keywords

今後の更新に備えて、 nokogiri でスクレイピングするワンライナーを添えてありますのでご参考まで。BigQueryでは CURRENT_DATE, CURRENT_DATETIME, CURRENT_TIME, CURRENT_TIMESTAMP は予約語扱いではないのですが、括弧不要で呼び出せる関数なので、予約語として扱わないとカラム名と区別できません。

module SQLSyntax
  # Reserved words in PostgreSQL
  #
  # nokogiri -e 'puts @doc.css(%{table[summary="SQL Key Words"] tbody tr}).filter_map { |tr| tr.at("td:nth(1) code.token").text if tr.at("td:nth(2)").text.match?(/\Areserved/) }.join(" ")' https://www.postgresql.org/docs/current/sql-keywords-appendix.html
  POSTGRESQL_RESERVED_WORDS = %w[
    ALL ANALYSE ANALYZE AND ANY ARRAY AS ASC ASYMMETRIC AUTHORIZATION
    BINARY BOTH CASE CAST CHECK COLLATE COLLATION COLUMN CONCURRENTLY
    CONSTRAINT CREATE CROSS CURRENT_CATALOG CURRENT_DATE CURRENT_ROLE
    CURRENT_SCHEMA CURRENT_TIME CURRENT_TIMESTAMP CURRENT_USER DEFAULT
    DEFERRABLE DESC DISTINCT DO ELSE END EXCEPT FALSE FETCH FOR
    FOREIGN FREEZE FROM FULL GRANT GROUP HAVING ILIKE IN INITIALLY
    INNER INTERSECT INTO IS ISNULL JOIN LATERAL LEADING LEFT LIKE
    LIMIT LOCALTIME LOCALTIMESTAMP NATURAL NOT NOTNULL NULL OFFSET ON
    ONLY OR ORDER OUTER OVERLAPS PLACING PRIMARY REFERENCES RETURNING
    RIGHT SELECT SESSION_USER SIMILAR SOME SYMMETRIC SYSTEM_USER TABLE
    TABLESAMPLE THEN TO TRAILING TRUE UNION UNIQUE USER USING VARIADIC
    VERBOSE WHEN WHERE WINDOW WITH
  ].to_set(&:downcase)

  # Reserved words in BigQuery GoogleSQL
  #
  # nokogiri -e 'puts @doc.at("#reserved_keywords ~ table").text.scan(/[A-Z_]+/).join(" ")' https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical
  BIGQUERY_RESERVED_WORDS = (%w[
    ALL AND ANY ARRAY AS ASC ASSERT_ROWS_MODIFIED AT BETWEEN BY CASE
    CAST COLLATE CONTAINS CREATE CROSS CUBE CURRENT DEFAULT DEFINE
    DESC DISTINCT ELSE END ENUM ESCAPE EXCEPT EXCLUDE EXISTS EXTRACT
    FALSE FETCH FOLLOWING FOR FROM FULL GROUP GROUPING GROUPS HASH
    HAVING IF IGNORE IN INNER INTERSECT INTERVAL INTO IS JOIN LATERAL
    LEFT LIKE LIMIT LOOKUP MERGE NATURAL NEW NO NOT NULL NULLS OF ON
    OR ORDER OUTER OVER PARTITION PRECEDING PROTO QUALIFY RANGE
    RECURSIVE RESPECT RIGHT ROLLUP ROWS SELECT SET SOME STRUCT
    TABLESAMPLE THEN TO TREAT TRUE UNBOUNDED UNION UNNEST USING WHEN
    WHERE WINDOW WITH WITHIN
  ] + %w[
    CURRENT_DATE CURRENT_DATETIME CURRENT_TIME CURRENT_TIMESTAMP
  ]).to_set(&:downcase)

  refine String do
    def pg_quote
      if POSTGRESQL_RESERVED_WORDS.include?(downcase)
        %("#{self}")
      else
        self
      end
    end

    def bq_quote
      if BIGQUERY_RESERVED_WORDS.include?(downcase)
        %(`#{self}`)
      else
        self
      end
    end
  end
end

まとめ

RailsのActiveRecordを使ってPostgreSQLのスキーマ情報を取得し、BigQueryのTerraformリソース定義として出力する方法について解説しました。

DBMS依存はありますが、Railsでデータベースのスキーマ情報を取得して再利用することは難しくありません。また、スキーマ情報にコメントを付け、BigQuery側でも参照できるようにしたり、BigQuery固有の指定を埋め込むことで、広い意味のスキーマ情報をRails側で一元管理できることも示しました。

ぜひデータ分析基盤の構築や運用の効率化に活用してみてください。