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


## はじめに

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

https://qiita.com/WakameSun/items/08aa736889f916d6831d

今回は、この記事をベースにし 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 を定義します

```sh
bundle init
```

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

- mysql2
- json

```ruby: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 を実行する コンテナに初期データを挿入する場合は、
以下記事を参考にしてください。

https://qiita.com/Cyber_Diver/items/6c95ee174aa0e1d9c6d7

```dockerfile: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"]
```

```yml: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 に記事を公開しています。

https://qiita.com/WakameSun/items/08aa736889f916d6831d

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

```ruby
# 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 からのデータ取得を行う部分だけです。

https://qiita.com/WakameSun/items/08aa736889f916d6831d

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

https://dev.mysql.com/doc/refman/8.0/en/data-types.html

https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

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

https://github.com/brianmario/mysql2

## さいごに

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

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

## References

- [Railsアプリケーションで利用しているMySQLのテーブル情報から、BigQueryのためのスキーマを作ってみる - Qiita](https://qiita.com/WakameSun/items/08aa736889f916d6831d)
- [Data types  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types)
- [MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types](https://dev.mysql.com/doc/refman/8.0/en/data-types.html)
- [MySQL :: MySQL 8.0 リファレンスマニュアル :: 26.8 INFORMATION_SCHEMA COLUMNS テーブル](https://dev.mysql.com/doc/refman/8.0/ja/information-schema-columns-table.html)
- [brianmario/mysql2: A modern, simple and very fast Mysql library for Ruby - binding to libmysql](https://github.com/brianmario/mysql2)
- [flori/json: JSON implementation for Ruby](https://github.com/flori/json)