データ移行における『テーブルデータ定義の型変換』のまとめ(MySQL→Amazon Redshift編)

Amazon Redshiftへデータを投入・移行させる際のデータソースについては、様々な種類のデータソースが想定される事と思います。中でも『オンプレ環境のRDBデータ』というケースは結構な割合で想定されるのではないでしょうか。

Amazon Redshiftに対し、異なるデータソースからのデータを投入させる場合、当然の事ながら『データベーステーブルのカラムに於ける型の定義』はデータソース間でも微妙に異なったりします。移行元データソースのテーブル定義をそのままAmazon Redshiftのテーブル定義に当てはめる事が出来れば一番効率が良いのですが、中々そう上手くは行かない部分もあったりします。

そこで当エントリでは、移行元データソースのテーブル定義に対応するAmazon Redshiftのテーブル定義にはどのようなものがあるのか、データ型間の対応可否はどの様になっているのかという観点で情報をまとめてみたいと思います。当エントリでご紹介するのは『MySQL』と『Amazon Redshift』の対応となります。

目次

• Amazon Redshiftのデータ型
• データ型比較・変換対応表(MySQL → Amazon Redshift)
  • 数値型
  • 真偽(BOOLEAN)型
  • 日付時刻型
  • 文字列型
  • バイナリ・バイト型
  • BLOB・TEXT型
• まとめ

Amazon Redshiftのデータ型

まずは変換対象となるAmazon Redshiftで用意されているテーブルのデータ型一覧です。以下公式ドキュメントから転記します。

• データ型 - Amazon Redshift

データ型比較・変換対応表(MySQL → Amazon Redshift)

次いで、MySQLとAmazon Redshiftの各種データ型をそれぞれ見て行きたいと思います。主に以下の情報を参考にしました。

• MySQL :: MySQL 5.6 Reference Manual :: 11 Data Types
• MySQL :: MySQL 5.6 リファレンスマニュアル :: 11 データ型
• 数値型 - Amazon Redshift
• 日付型と時刻型 - MySQLのデータ型 - MySQLの使い方
• 型の互換性と変換 - Amazon Redshift

数値型

数値型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。基本的には対応する桁数が双方に存在しているので変換自体は特に手を煩わされる事は無さそうですね。

真偽(BOOLEAN)型

Amazon Redshiftに於ける真偽(BOOLEAN)型に対応するMySQLのデータ型として、BOOLEAN型の項目は定義は出来るようです。しかし定義後の値としてはTINYINT型の1桁の項目として処理される模様。この事から、BOOLEANで定義されているデータをAmazon Redshiftに移行する場合は(1).そのまま数値型として取り込む (2). 一旦数値型として取り込んだ後、RedshiftのBOOLEAN型に変換を掛けるELT処理を行う (3).BIツール等で用いる場合は、いっその事1/0を何らかの意味ある文字列に置き換えてしまう(『1:はい、0:いいえ』等)等の対応を取る事が選択肢として挙げられるのではないでしょうか。

• MySQLにおけるBOOLEAN型の扱いについて調べてみた - amari3のはてなダイアリー
• MySQLに真偽値を格納する場合はbit(1)型のフィールドが最適 - Qiita

日付時刻型

日付及び時刻に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

MySQLに於けるDATE型, DATETIME型, TIMESTAMP型については、そのままAmazon RedshiftのDATE型, TIMESTAMP型に置き換えられるかと思います。

一方、この種類に於けるTIME型, YEAR[(2|4)]型については情報が部分的なものになっており、Tabelau等で扱うには情報として不十分です。Tableauでは日付型やタイムスタンプ型の項目になっていれば自動で型を認識し、ドリルダウン・展開可能な形式に便利な形に扱うように対応させる事が出来ますので、まずは一旦データとして取り込み(このタイミングでは一旦0埋めの文字列として整えておく形が良いでしょうか)、その後に(恐らくはレコードの他の項目として用意されている)日付時刻情報の値と合わせ、DATE型、TIMESTAMP型の値として生成し直す事をお勧めします。

上記同様に、日付項目が年(YYYY)/月(MM)/日(DD)の"文字列型"項目として分割管理されている場合も同様です。一旦情報として取り込んだ後にそれらの文字列を結合し、日付型/タイムスタンプ型項目として生成しなおす事をお勧めします。

文字列型

文字列型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

ENUM型, SET型については対応するデータ型はAmazon Redshiftには存在しません。エクスポートもしくはエクスポート後のタイミングで、Amazon Redshiftで扱える形にバラし、Redshiftに投入せざるを得ないのでは無いかと思います。

また、VARCHAR型の移行についてはMySQLとAmazon Redshiftでは考え方が異なるので注意してください。MySQLでのVARCHAR型は1文字に1桁を要し、Amazon Redshiftに於けるVARCHARは1文字格納するためには、文字に対応したバイトを数必要とします。

実際に試してみましょう。MySQLに於けるVARCHAR型項目を定義してみます。以下例では10桁のVARCHAR型項目に対し、全角文字(ひらがな)を10文字まで格納出来ています。11文字以上を投入しようとするとWARNINGとなり、桁数以上の部分は切り捨てられてしまいました。

• MySQLでテーブル定義を取得する方法 - Narusaseの日記 -ハニポってどうよ？(仮)-
• MySQL5.0.45で、varchar(100)で定義したカラムに200バイト(全角1… - 人力検索はてな
• MySQLのvarchar(XX)は、文字数(MySQL4.1.20) - 肉とビールとパンケーキ by @sotarok
• データベースを作成する（文字コード指定） - MySQL 逆引きリファレンス

mysql> CREATE DATABASE stringtestdb CHARACTER SET sjis;
Query OK, 1 row affected (0.00 sec)

mysql> USE stringtestdb;
Database changed
mysql> 
mysql> CREATE TABLE strings_test_mysql (
    ->      id INT NOT NULL,
    ->      name VARCHAR(10) NOT NULL
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> USE stringtestdb;
Database changed
mysql> INSERT INTO strings_test_mysql VALUES(1, 'あいうえおかきくけこ');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO strings_test_mysql VALUES(2, 'あいうえおかきくけこさ');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM strings_test_mysql;
+----+--------------------------------+
| id | name                           |
+----+--------------------------------+
|  1 | あいうえおかきくけこ           |
|  2 | あいうえおかきくけこ           |
+----+--------------------------------+
2 rows in set (0.00 sec)

mysql>

ちなみに環境構築の手順については以下の通りです。EC2環境を1つ用意し、その中にMySQLをインストールした形を想定しました。

$ sudo yum -y update
$ sudo yum install -y mysql mysql-server
$ sudo chkconfig mysqld on
$ sudo service mysqld start
Starting mysqld:                                           [  OK  ]

$ mysql -u root
mysql> update mysql.user set password=password('adminadmin') where user = 'root';
mysql> flush privileges;
mysql> exit
$
$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.42 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

次いで、Amazon Redshiftで同じような環境を再現してみます。同じ桁数でテーブルを作成し、データを投入してみていますが、MySQLの時とは異なり全角文字(ひらがな)は3文字まで、半角文字は10桁までしか入りません。

• VARCHAR または CHARACTER VARYING/文字型 - Amazon Redshift
• マルチバイト文字のロードエラー - Amazon Redshift

# CREATE TABLE strings_test_redshift (
     id INT NOT NULL,
     name VARCHAR(10) NOT NULL
 );
CREATE TABLE
# INSERT INTO strings_test_redshift VALUES(1,'あいうえおかきくけこ');
ERROR:  value too long for type character varying(10)
# INSERT INTO strings_test_redshift VALUES(1,'あいうえ');
ERROR:  value too long for type character varying(10)
# INSERT INTO strings_test_redshift VALUES(1,'あいう');
INSERT 0 1

# INSERT INTO strings_test_redshift VALUES(2,'ABCDEFGHIJ');
INSERT 0 1
# INSERT INTO strings_test_redshift VALUES(2,'ABCDEFGHIJK');
ERROR:  value too long for type character varying(10)

# SELECT * FROM strings_test_redshift;
 id |    name    
----+------------
  1 | あいう
  2 | ABCDEFGHIJ
(2 rows)

#

この事から、MySQLからAmazon Redshiftにデータを移行する際、VARCHAR型の桁数については[MySQLに於ける桁数]x3をAmazon Redshiftに於ける桁数とする等の対応が必要になるかと思われます。

ちなみにAmazon RedshiftのVARCHAR型では最大4バイトまで対応している模様。MySQLで4バイト文字を扱っており、且つ移行するという場合はこの辺り臨機応変に対応する必要がありそうです。

• マルチバイト文字/データ型 - Amazon Redshift

バイナリ・バイト型

バイナリ型・バイト型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

Amazon Redshiftに於いてはバイナリ・バイト型に相当するデータ型はありません。

BLOB・TEXT型

BLOB・TEXT型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。

Amazon Redshiftに於いてはBLOB型に相当するデータ型はありません。

TINYTEXT/TEXT型については、対応する型に移行する事は出来そうですが、MEDIUMTEXT/LONGTEXTについては最大値に於いてAmazon Redshiftの対応型が存在していません。この辺りは何らか考慮しておく必要があるかと思われます。

まとめ

以上、MySQLとAmazon Redshiftに於けるデータ移行の際のデータ型変換のまとめでした。MySQLのデータをAmazon Redshiftで扱うためには変換などが必要なケース、また投入自体を(そのままのデータ型では)諦めざるを得ないケースが出て来そうです。MySQLからエクスポートした後にETL処理で、またエクスポートしたデータを一旦そのままAmazon Redshiftにインポートした後にELT処理で変換を掛け、Redshiftで扱えるような形のデータを構成する検討は必要となるでしょう。

当エントリではMySQL→Amazon Redshiftについてまとめてみましたが、その他の主要なRDBMSについても今後まとめてみたいと思います。こちらからは以上です。