Mysqlにあるデータの一部を分析のために、BigQueryなどのデータウェアハウスに移す、というのはよくあるケースだと思います。
その際、BigQueryだとテーブルを作る際に下記のようなテーブルのスキーマ定義が必要になります。
[
{
"name": "id",
"type": "integer",
"mode": "required"
},
{
"name": "title",
"type": "string",
"mode": "required"
}
]
スキーマ定義は各カラムの名前(name)、データ型(type)、nullを許可するかなどのモード(mode)の３情報の集合で、MySQLからデータを移す場合は、対応する3つを入れてあげればよさそうです。
今回はRailsアプリケーションの場合簡単なスクリプトを書いて、あるテーブルの全カラムのスキーマを自動生成してみます。
テーブル情報の取り出し方
まず、テーブルのカラムの情報が欲しい場合、下記の１行でまとめて取り出すことができます。
ActiveRecord::Base.connection.columns(:table_name)
このcolumnsで取り出した情報には、テーブルのさまざまな情報が入っているわけですが、今回必要なのはname、type、modeの３情報です。
まずはこれらを簡単に取り出してみましょう。今回は下記のようなmigrateで作成されたbooksというテーブルを対象にしてみます。
create_table "books", id: :integer, charset: "utf8mb4", force: :cascade do |t|
t.integer "author_id", null: false
t.string "title", null: false
t.text "description"
t.datetime "published_at"
end
ActiveRecord::Base.connection.columnsの各カラムには、nameにはカラム名、typeはmigrate時に指定した型（SQL側で定義された情報を使う場合はsql_typeを利用する）、null当然ですがはnullを許可するかの情報が入っています。
なのでまずはこのようなスクリプトを書いて、rails runnerで実行してみます。
schema = ActiveRecord::Base.connection.columns(:books).map do |column|
{
"name": column.name,
"type": column.type,
"mode": column.null
}
end
puts JSON.pretty_generate(schema)
上記のスクリプトを実行するとこのような結果が出力されます。
[
{
"name": "id",
"type": "integer",
"mode": false
},
{
"name": "author_id",
"type": "integer",
"mode": false
},
{
"name": "title",
"type": "string",
"mode": false
},
{
"name": "description",
"type": "text",
"mode": true
},
{
"name": "published_at",
"type": "datetime",
"mode": true
}
]
結構それっぽいものが出力されています。しかし、text型は、BigQueryでは他の文字列と一緒にstringで管理されており、またmodeに関してはnullable、required、repeatedの３値である必要があります。
この点などを踏まえて、スクリプトを改善してみます。
def detect_type(type)
case type
when :datetime
'timestamp'
when :text, :string
'string'
when :float
'float64'
when :binary
'bytes'
when :integer, :boolean, :time, :json, :date
type.to_s
else
raise "Unknown type: #{type}" # 上述以外の型が来たときはエラーで落とす
end
end
schema = ActiveRecord::Base.connection.columns(:books).map do |column|
{
"name": column.name,
"type": detect_type(column.type),
"mode": column.null ? "nullable" : "required"
}
end
puts JSON.pretty_generate(schema)
modeは今回の出力だと、repeatedになることはないので、nullを許可するかどうかでnullableとrequiredを使い分け、typeはRails側がこの型ならこの名前、というcase文を作成しています。
実行すると、下記のようになります。
[
{
"name": "id",
"type": "integer",
"mode": "required"
},
{
"name": "author_id",
"type": "integer",
"mode": "required"
},
{
"name": "title",
"type": "string",
"mode": "required"
},
{
"name": "description",
"type": "string",
"mode": "nullable"
},
{
"name": "published_at",
"type": "timestamp",
"mode": "nullable"
}
]
この出力結果をBigQueryに入れてみたところ、バリデーションを通過し、BigQueryで使えるデータ型をMysqlから作成することができました。(不備があるとエラーメッセージが出る)
まとめ
今回はRailsアプリケーションで使っているMySQLの情報をもとに、BigQueryのスキーマを作成しました。
実際の運用では、作成したスキーマのうちの一部カラムを削除したり、特定のカラムがnullなものは送らない制御をすることにより、生成したスキーマのmodeはnullableだけど、実際はrequiredで良い、というのも考えられます。
しかし、その辺を踏まえても送るデータの条件を踏まえて、一部を削除したり変更したりすればそのまま運用に使えるので、スキーマの作成がだいぶ楽になりそうです。
多分似た取り組みは色々とあると思いますが、Rails（というかActiveRecord）のデータベースのメタデータの取得方法や、BigQueryの型を詳しく調べることができ、勉強になって良かったです。