More than 1 year has passed since last update.

Ruby gem mysql2 を使い MySQL の schema 情報から BigQuery で利用する schema 定義を出力する

Last updated at 2023-08-31Posted at 2023-08-31

はじめに

BigQuery の table 定義を作成するために必要な schema 定義 (JSON ファイル) を Gem "mysql2" を利用し作成しました。
ActiveRecord を利用し出力する方法は @WakameSun さんが Qiita に記事を公開しています。

今回は、この記事をベースにし Gem "mysql2" を利用して schema 定義を生成します。

必要な環境

Docker

mysql2 を使い BigQuery で利用する schema 定義を出力する

大まかな流れは以下のとおりです。

bundle init を実行して、Gemfile を作成、必要な Gem を定義
実行用の Dockerfile 等を作成する
MySQL の schema 情報から BigQuery で利用する schema 定義を出力する Ruby script を作成する

`bundle init` を実行して、Gemfileを作成、必要な Gem を定義

適当なディレクトリで bundle init を実行し、Gemfile を作成し、必要な Gem を定義します

bundle init

お好きなエディタで作成した Gemfile を編集します。
Ruby script 内で利用する Gem を 2 つ定義します。

mysql2
json

Gemfile

# frozen_string_literal: true

source 'https://rubygems.org'

gem 'json'
gem 'mysql2'

作成する script で option を受け取りたい場合は別途 optparse 等も定義すると良いでしょう。

実行用の Dockerfile 等を作成する

Dockerfile と docker-compose.yml を作成します。
なお、 docker-compose.yml 内に定義する mysql コンテナにはデータが挿入されているものとします。

MySQL を実行するコンテナに初期データを挿入する場合は、
以下記事を参考にしてください。

Dockerfile

FROM ruby:3.2-bookworm

RUN apt-get update && \
    apt-get install -y --no-install-recommends \
        default-libmysqlclient-dev && \
    rm -rf /var/lib/apt/lists/*

WORKDIR /app

COPY Gemfile.lock \
     Gemfile \
     /app/

RUN gem install bundler --version "$(tail -n1 Gemfile.lock | tr -d ' ')" \
                        --conservative \
                        --no-document

RUN bundle config --global silence_root_warning 1 && \
    bundle install --jobs 4

COPY . /app

ENTRYPOINT ["bundle", "exec"]

docker-compose.yml

version: "3"
services:
  mysql:
    image: mysql:8
    environment:
      MYSQL_ROOT_HOST: "%"
      MYSQL_ROOT_PASSWORD: root_password
      MYSQL_DATABASE: mysql2bq
  mysql2bq:
    build: .
    depends_on:
      - mysql
    environment:
      MYSQL_HOST: mysql
      MYSQL_USER: root
      MYSQL_PASSWORD: root_password
      MYSQL_DATABASE: mysql2bq
    volumes:
      - ./:/app/

ここまで準備が出来たら docker compose build 等実行しましょう。

MySQL の schema 情報から BigQuery で利用する schema 定義を出力する Ruby script を作成する

「はじめに」にも記載しましたが、 ActiveRecord を利用し出力する方法は @WakameSun さんが Qiita に記事を公開しています。

今回は、この記事をベースにし Gem "mysql2" を利用して schema 定義を生成します。
作成した Ruby script は以下のとおりです。

# frozen_string_literal: true

require 'bundler'
Bundler.require

table = ENV.fetch('MYSQL_TABLE', nil)
host = ENV.fetch('MYSQL_HOST', nil)
username = ENV.fetch('MYSQL_USER', nil)
password = ENV.fetch('MYSQL_PASSWORD', nil)
database = ENV.fetch('MYSQL_DATABASE', nil)

# @param [String] type
# @return [String]
def detect_type(type)
  case type
  when 'varchar', 'char', 'tinytext', 'text', 'mediumtext', 'longtext'
    'string'
  when 'bigint', 'int', 'smallint'
    'integer'
  when 'double', 'float', 'decimal'
    'float'
  when 'datetime', 'timestamp'
    'timestamp'
  when 'tinyblob', 'blob', 'mediumblob', 'longblob'
    'bytes'
  when 'boolean', 'tinyint'
    'boolean'
  when 'time', 'json', 'date'
    type
  else
    # enum, set は対応しない
    raise "Unknown type: #{type}"
  end
end

c = Mysql2::Client.new(host:, username:, password:, database:)
sql = <<SQL
  SELECT
    COLUMN_NAME as 'column_name',
    DATA_TYPE   as 'data_type',
    IS_NULLABLE as 'is_nullable'
  FROM
    information_schema.COLUMNS
  WHERE
        TABLE_SCHEMA = '#{database}'
    AND TABLE_NAME = '#{table}'
SQL

result = c.query(sql).map do |row|
  {
    name: row['column_name'],
    type: detect_type(row['data_type']),
    mode: row['is_nullable'] == 'YES' ? 'nullable' : 'required'
  }
end

puts JSON.pretty_generate(result)

ベースにした以下記事と変更している点は、 detect_type の判定条件と MySQL からのデータ取得を行う部分だけです。

detect_type の判定条件については、 MySQL や BigQuery のドキュメントに記載されている型情報を見比べ当てはめていけばOKです。

また、MySQL からのデータ取得についても同様で接続先や実行する SQL を定義する必要があります。
といっても難しいことはなく、上記サンプルコードをもとに書いていただければ OK です。
必要に応じて、 Gem "mysql2" の README も参照してください。

さいごに

BigQuery で利用する schema 定義の作成、実際に1から手で作成すると時間も、工数もかかりました。
ある程度 JSON ファイルを生成した後に必要な箇所のみを修正することで時間も、工数も大幅に削減出来ました。

また、普段覗くことが少い information_schema.COLUMNS について(簡単ではありますが)調べる機会になってとても良かったなと思いました。

References

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up