データ移行における『テーブルデータ定義の型変換』のまとめ(MySQL→Amazon Redshift編)
Amazon Redshiftへデータを投入・移行させる際のデータソースについては、様々な種類のデータソースが想定される事と思います。中でも『オンプレ環境のRDBデータ』というケースは結構な割合で想定されるのではないでしょうか。
Amazon Redshiftに対し、異なるデータソースからのデータを投入させる場合、当然の事ながら『データベーステーブルのカラムに於ける型の定義』はデータソース間でも微妙に異なったりします。移行元データソースのテーブル定義をそのままAmazon Redshiftのテーブル定義に当てはめる事が出来れば一番効率が良いのですが、中々そう上手くは行かない部分もあったりします。
そこで当エントリでは、移行元データソースのテーブル定義に対応するAmazon Redshiftのテーブル定義にはどのようなものがあるのか、データ型間の対応可否はどの様になっているのかという観点で情報をまとめてみたいと思います。当エントリでご紹介するのは『MySQL』と『Amazon Redshift』の対応となります。
目次
Amazon Redshiftのデータ型
まずは変換対象となるAmazon Redshiftで用意されているテーブルのデータ型一覧です。以下公式ドキュメントから転記します。
| データ型 | 別名 | 説明 |
|---|---|---|
| SMALLINT | INT2 | 符号付き 2 バイト整数 |
| INTEGER | INT、INT4 | 符号付き 4 バイト整数 |
| BIGNIT | INT8 | 符号付き 8 バイト整数 |
| DECIMAL | NUMERIC | 精度の選択が可能な真数 |
| REAL | FLOAT4 | 単精度浮動小数点数 |
| DOUBLE PRECISION | FLOAT8、FLOAT | 倍精度浮動小数点数 |
| BOOLEAN | BOOL | 論理ブール演算型（true/false） |
| CHAR | CHARACTERNCHAR、BPCHAR | 固定長のキャラクタ文字列 |
| VARCHAR | CHARACTER VARYING、NVARCHAR、TEXT | ユーザーによって定義された制限を持つ可変長キャラクタ文字列 |
| DATE | カレンダー日付（年、月、日） | |
| TIMESTAMP | TIMESTAMP WITHOUT TIME ZONE | 日付と時刻（タイムゾーンなし） |
データ型比較・変換対応表(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のデータ型については以下の通りです。基本的には対応する桁数が双方に存在しているので変換自体は特に手を煩わされる事は無さそうですね。
| MySQL | Amazon Redshift | |||
|---|---|---|---|---|
| データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
| 整数型 | TINYINT | -128から127 (符号無しの場合0から255) | SMALLINT INT2 | -32768～+32767 |
| SMALLINT | -32768から32767 (符号無しの場合0から65535) | SMALLINT INT2 | -32768～+32767 | |
| MEDIUMINT | -8388608から8388607 (符号無しの場合 0から16777215) | INT INTEGER INT4 | -2147483648～+2147483647 | |
| INT INTEGER | -2147483648〜 2147483647 (符号無しの場合 0から4294967295) | INT INTEGER INT4 | -2147483648～+2147483647 | |
| BIGINT | -9223372036854775808 〜 9223372036854775807 (符号無しの場合 0から 18446744073709551615) | BIGINT INT8 | -9223372036854775808 ～ 9223372036854775807 | |
| 固定小数点型 | DECIMAL | 任意の精度及びスケール指定 (整数部小数部併せて65桁まで) | DECIMAL | precision(精度)： デフォルト(指定無し)=18、最大=38 scale(スケール)： デフォルト(指定無し)=0、最大=37 |
| NUMERIC | (同上) | NUMERIC | (同上) | |
| 浮動小数点型 | FLOAT | -3.402823466E+38〜 -1.175494351E-38 0 1.175494351E-38〜3.402823466E+38 | REAL FLOAT4 | 4バイト 有効な精度桁数:6桁 |
| DOUBLE | -1.7976931348623157E+308〜 -2.2250738585072014E-308 0 2.2250738585072014E-308〜 1.7976931348623157E+308 | DOUBLE PRECISION FLOAT8</br/>FLOAT | 8バイト 有効な精度桁数:15桁 | |
真偽(BOOLEAN)型
Amazon Redshiftに於ける真偽(BOOLEAN)型に対応するMySQLのデータ型として、BOOLEAN型の項目は定義は出来るようです。しかし定義後の値としてはTINYINT型の1桁の項目として処理される模様。この事から、BOOLEANで定義されているデータをAmazon Redshiftに移行する場合は(1).そのまま数値型として取り込む (2). 一旦数値型として取り込んだ後、RedshiftのBOOLEAN型に変換を掛けるELT処理を行う (3).BIツール等で用いる場合は、いっその事1/0を何らかの意味ある文字列に置き換えてしまう(『1:はい、0:いいえ』等)等の対応を取る事が選択肢として挙げられるのではないでしょうか。
日付時刻型
日付及び時刻に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。
| MySQL | Amazon Redshift | |||
|---|---|---|---|---|
| データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
| 日付時刻型 | DATE | フォーマット: 'YYYY-MM-DD' 範囲: '1000-01-01'〜 '9999-12-31' | DATE | 4バイト 4713BC～294276AD |
| DATETIME | フォーマット: 'YYYY-MM-DD HH:MM:SS' 範囲: '1000-01-01 00:00:00'〜 '9999-12-31 23:59:59' | TIMESTAMP | 8バイト 4713BC～294276AD | |
| TIMESTAMP | フォーマット: 'YYYY-MM-DD HH:MM:SS' 範囲: '1970-01-01 00:00:01'〜 '2037-12-31 23:59:59' | TIMESTAMP | 8バイト 4713BC～294276AD | |
| TIME | フォーマット: 'HH:MM:SS' 範囲: '-838:59:59'〜 '838:59:59' | ---- | (対応データ型無し) | |
| YEAR[(2|4)] | フォーマット:YYYY 範囲: 1901〜2155 0000(4桁の場合) 範囲: 70〜69 (2桁の場合、 70は1970を表し 69は2069を表す) デフォルトは4桁 | ---- | (対応データ型無し) | |
MySQLに於けるDATE型, DATETIME型, TIMESTAMP型については、そのままAmazon RedshiftのDATE型, TIMESTAMP型に置き換えられるかと思います。
一方、この種類に於けるTIME型, YEAR[(2|4)]型については情報が部分的なものになっており、Tabelau等で扱うには情報として不十分です。Tableauでは日付型やタイムスタンプ型の項目になっていれば自動で型を認識し、ドリルダウン・展開可能な形式に便利な形に扱うように対応させる事が出来ますので、まずは一旦データとして取り込み(このタイミングでは一旦0埋めの文字列として整えておく形が良いでしょうか)、その後に(恐らくはレコードの他の項目として用意されている)日付時刻情報の値と合わせ、DATE型、TIMESTAMP型の値として生成し直す事をお勧めします。
上記同様に、日付項目が年(YYYY)/月(MM)/日(DD)の"文字列型"項目として分割管理されている場合も同様です。一旦情報として取り込んだ後にそれらの文字列を結合し、日付型/タイムスタンプ型項目として生成しなおす事をお勧めします。
文字列型
文字列型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。
| MySQL | Amazon Redshift | |||
|---|---|---|---|---|
| データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
| 文字列型 | CHAR(M) | 固定長文字列 Mは文字数を指定、 0から255文字 | CHAR | 4096バイト |
| VARCHAR(M) | 可変長文字列 Mはバイト数を指定、 0から65535バイト | VARCHAR | 65535バイト (64K-1) | |
| ENUM | MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.7 データ型のストレージ要件 | ---- | (対応データ型無し) | |
| SET | MySQL :: MySQL 5.6 リファレンスマニュアル :: 11.4.5 SET 型 | ---- | (対応データ型無し) | |
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桁までしか入りません。
# 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バイト文字を扱っており、且つ移行するという場合はこの辺り臨機応変に対応する必要がありそうです。
バイナリ・バイト型
バイナリ型・バイト型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。
| MySQL | Amazon Redshift | |||
|---|---|---|---|---|
| データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
| バイナリ・バイト型 | BINARY(M) | 固定長バイナリバイト文字列 Nはバイトを指定、 0から255文字 | ---- | (対応データ型無し) |
| VARBINARY(M) | 可変長バイナリバイト文字列 Mはバイト数を指定、 0から65535バイト | ---- | (対応データ型無し) | |
Amazon Redshiftに於いてはバイナリ・バイト型に相当するデータ型はありません。
BLOB・TEXT型
BLOB・TEXT型に関するMySQLとAmazon Redshiftのデータ型については以下の通りです。
| MySQL | Amazon Redshift | |||
|---|---|---|---|---|
| データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
| BLOB/TEXT型 | TINYBLOB | 最長255(2の8乗-1)バイト | ---- | (対応データ型無し) |
| BLOB(M) | 最長65,535 (2の16乗-1)バイト | ---- | (対応データ型無し) | |
| MEDIUMBLOB | 最長16,777,215 (2の24乗-1)バイト | ---- | (対応データ型無し) | |
| LONGLOB | 最長4,294,967,295、 または 4GB(2の32乗-1)バイト | ---- | (対応データ型無し) | |
| TINYTEXT | 最長255(2の8乗-1)バイト | TEXT | 260バイト TEXT 型および BPCHAR 型/文字型 - Amazon Redshift | |
| TEXT(M) | 最長65,535 (2の16乗-1)バイト | VARCHAR | 65535バイト (64K-1) | |
| MEDIUMTEXT | 最長16,777,215 (2の24乗-1)バイト | ---- | (対応データ型無し) | |
| LONGTEXT | 最長4,294,967,295、 または 4GB(2の32乗-1)バイト | ---- | (対応データ型無し) | |
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についても今後まとめてみたいと思います。こちらからは以上です。
![[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent](https://devio2023-media.developers.io/wp-content/uploads/2022/11/eyecatch_reinvent2022_session-report.png)
