MySQLのCreate Table文をRedshiftのCreate Table文に変換する
こんにちは、Yuraです。 今回もどうぞよろしくお願い申し上げます。
はじめに
MySQLから、Amazon Redshiftへのデータ移行をやってみました。 大抵の場合、テーブルは予め用意しておくと思うので、今回はMySQLのCreate Table文をRedshiftのCreate Table文に変換してみます。
MySQLとRedshiftのデータ型対応について
恥ずかしながら、クラスメソッドで仕事をするここ最近までRedshiftを使ったことがなかったので、 まずはRedShiftにどのようなデータ型があって、MySQLのデータ型とどう紐づければよいのかを調べなければなりませんでした。 こちらの記事 が大変勉強になりました。 調べてみると、AWS Data PipelineにMySQLとRedshiftのデータ型変換スクリプトがあるようなので、 それと同じようにやります。 Amazon RDS から Redshift へのコピー用テンプレート
例えば次のようなMySQL用のCreate Table文があったとして、
CREATE TABLE employees ( id int(11) unsigned NOT NULL, name varchar(255), age tinyint, hire_date date, created_at datetime NOT NULL, updated_at datetime NOT NULL, PRIMARY KEY (id) );
Redshift用に変換すると、こんな感じになります。
CREATE TABLE employees ( id BIGINT NOT NULL, name VARCHAR(1020), age SMALLINT, hire_date DATE, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY (id) );
プログラムで変換
単純にデータ型のマッピングをするだけなのですが、 SQLの数が多いと面倒なので、Javaを使ってできるようにしました。 プログラムで処理するにあたって、mysqldumpにて出力したSQLを使います。 ↓がmysqldumpで吐き出した、employeesテーブルです。
CREATE TABLE `employees` ( `id` int(11) unsigned NOT NULL, `name` varchar(255) DEFAULT NULL, `age` tinyint(4) DEFAULT NULL, `hire_date` date DEFAULT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
プログラムは以下のようになります。 SQLConverterのconvertSQLメソッドに変換したいSQLを渡してあげてください。 ざっくり動きを説明するとSQLを読んで、カラム定義オブジェクトを生成して、そのカラム定義オブジェクトを元に変換後のSQLを出力します。
package jp.classmethod.app;
import java.util.ArrayList;
import java.util.List;
public class SQLConverter {
/** REDSHIFT データ型・SMALLINT */
private static final String REDSHIFT_DATATYPE_SMALLINT = "SMALLINT";
/** REDSHIFT データ型・INTEGER */
private static final String REDSHIFT_DATATYPE_INTEGER = "INTEGER";
/** REDSHIFT データ型・BIGINT */
private static final String REDSHIFT_DATATYPE_BIGINT = "BIGINT";
/** REDSHIFT データ型・DECIMAL */
private static final String REDSHIFT_DATATYPE_DECIMAL = "DECIMAL";
/** REDSHIFT データ型・REAL */
private static final String REDSHIFT_DATATYPE_REAL = "REAL";
/** REDSHIFT データ型・DOUBLE */
private static final String REDSHIFT_DATATYPE_DOUBLE = "DOUBLE PRECISION";
/** REDSHIFT データ型・DATE */
private static final String REDSHIFT_DATATYPE_DATE = "DATE";
/** REDSHIFT データ型・TIMESTAMP */
private static final String REDSHIFT_DATATYPE_TIMESTAMP = "TIMESTAMP";
/** REDSHIFT データ型・VARCHAR */
private static final String REDSHIFT_DATATYPE_VARCHAR = "VARCHAR";
/** REDSHIFT データ型・VARCHAR(MAX) */
private static final String REDSHIFT_DATATYPE_VARCHAR_MAX = "VARCHAR(MAX)";
/**
* SQLの変換を行う
* @param SQL
* @return 変換後SQL
*/
public static String convertSQL(String SQL) {
// 単語ずつに分割
String[] words = SQL.split(" ");
String table = null;
List<ColumnDefinitionObject> columns = new ArrayList<>();
int i = 0;
ColumnDefinitionObject cdo = null;
String prevWord = null;
for (String word : words) {
if (word.startsWith("`") && !"CONSTRAINT".equals(prevWord)
&& !"REFERENCES".equals(prevWord) && !"KEY".equals(prevWord)) {
if (table == null) {
// テーブル名を設定
table = word.replaceAll("`", "\"");
} else {
// カラム名を設定
String columnName = word.replaceAll("`", "\"");
cdo = new ColumnDefinitionObject(columnName);
// データ型を取得
String dataType = words[i + 1].split("\\(")[0];
// データ型にカンマがついている場合があるので外す
dataType = dataType.split(",")[0];
cdo.setDataType(dataType);
// 桁数を取得
if (words[i + 1].split("\\(").length > 1) {
String length = words[i + 1].split("\\(")[1].split("\\)")[0];
cdo.setLength(length);
}
// unsignedかどうかのチェックを行う
if ("unsigned".equals(words[i + 2])) {
cdo.setUnsigned(true);
}
columns.add(cdo);
}
}
prevWord = word;
i++;
}
return createSQL(table, columns);
}
/**
* SQLを出力する
* @return 新たなCREATE TABLE文
*/
private static String createSQL(String table, List<ColumnDefinitionObject> columns) {
StringBuilder sql = new StringBuilder("CREATE TABLE " + table + " (");
int i = 1;
for (ColumnDefinitionObject cdo : columns) {
sql.append(cdo.getColumnName() + " ");
sql.append(dataTypeMapping(cdo));
if (i < columns.size()) {
sql.append(", ");
}
i++;
}
sql.append(");");
return sql.toString();
}
/**
* データ型のマッピングを行う
* @param cdo
* @return マッピング結果
*/
private static String dataTypeMapping(ColumnDefinitionObject cdo) {
String result = "";
switch (cdo.getDataType()) {
case TINYINT :
result = REDSHIFT_DATATYPE_SMALLINT;
break;
case SMALLINT :
result = !cdo.isUnsigned()? REDSHIFT_DATATYPE_SMALLINT : REDSHIFT_DATATYPE_INTEGER;
break;
case MEDIUMINT :
result = REDSHIFT_DATATYPE_INTEGER;
break;
case INT :
result = !cdo.isUnsigned()? REDSHIFT_DATATYPE_INTEGER : REDSHIFT_DATATYPE_BIGINT;
break;
case BIGINT :
result = !cdo.isUnsigned()? REDSHIFT_DATATYPE_INTEGER : REDSHIFT_DATATYPE_VARCHAR;
break;
case DECIMAL :
result = REDSHIFT_DATATYPE_DECIMAL + bracket(cdo.getLength());
break;
case FLOAT :
result = REDSHIFT_DATATYPE_REAL;
break;
case DOUBLE :
result = REDSHIFT_DATATYPE_DOUBLE;
break;
case CHAR :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(Integer.parseInt(cdo.getLength()) * 4);
break;
case VARCHAR :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(Integer.parseInt(cdo.getLength()) * 4);
break;
case TINYTEXT :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(255 * 4);
break;
case TEXT :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case MEDIUMTEXT :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case LONGTEXT :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case BINARY :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(255);
break;
case VARBINARY :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case TINYBLOB :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case BLOB :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case MEDIUMBLOB :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case LONGBLOB :
result = REDSHIFT_DATATYPE_VARCHAR_MAX;
break;
case ENUM :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(255 * 2);
break;
case SET :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(255 * 2);
break;
case DATE :
result = REDSHIFT_DATATYPE_DATE;
break;
case TIME :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(10 * 4);
break;
case DATETIME :
result = REDSHIFT_DATATYPE_TIMESTAMP;
break;
case TIMESTAMP :
result = REDSHIFT_DATATYPE_TIMESTAMP;
break;
case YEAR :
result = REDSHIFT_DATATYPE_VARCHAR + bracket(4 * 4);
break;
}
return result;
}
/**
* 括弧で囲む
* @param val
* @return 括弧で囲んだval
*/
private static String bracket(Object val) {
if (val == null || "".equals(val) || "0".equals(val)) {
return "";
}
return "(" + val +")";
}
}
package jp.classmethod.app;
/**
* カラム定義クラス
*/
public class ColumnDefinitionObject {
/**
* コンストラクタ
* @param columnName
*/
public ColumnDefinitionObject(String columnName) {
this.columnName = columnName;
}
/**
* MySQLのデータ型
*/
public enum MySQLDataType {
TINYINT("TINYINT"),
SMALLINT("SMALLINT"),
MEDIUMINT("MEDIUMINT"),
INT("INT"),
BIGINT("BIGINT"),
DECIMAL("DECIMAL"),
FLOAT("FLOAT"),
DOUBLE("DOUBLE"),
CHAR("CHAR"),
VARCHAR("VARCHAR"),
TINYTEXT("TINYTEXT"),
TEXT("TEXT"),
MEDIUMTEXT("MEDIUMTEXT"),
LONGTEXT("LONGTEXT"),
BINARY("BINARY"),
VARBINARY("VARBINARY"),
TINYBLOB("TINYBLOB"),
BLOB("BLOB"),
MEDIUMBLOB("MEDIUMBLOB"),
LONGBLOB("LONGBLOB"),
ENUM("ENUM"),
SET("SET"),
DATE("DATE"),
TIME("TIME"),
DATETIME("DATETIME"),
TIMESTAMP("TIMESTAMP"),
YEAR("YEAR");
private String dataType;
private MySQLDataType(String dataType) {
this.dataType = dataType;
}
public boolean compareToString(String val) {
return this.dataType.equals(val.toUpperCase().toString());
}
}
/** カラム名 */
private String columnName;
/** データ型 */
private MySQLDataType dataType;
/** 桁数 */
private String length;
/** 符号の有無 */
private boolean unsigend;
/**
* カラム名を取得する
* @return カラム名
*/
public String getColumnName() {
return columnName;
}
/**
* カラム名を取得する
* @return カラム名
*/
public MySQLDataType getDataType() {
return dataType;
}
/**
* 桁数を取得する
* @return カラム名
*/
public String getLength() {
if (length == null || "".equals(length)) {
return "0";
}
return length;
}
/**
* 符号の有無を取得する
* @return カラム名
*/
public boolean isUnsigned() {
return unsigend;
}
/**
* データ型を設定する
* @param dataType
*/
public void setDataType(String dataType) {
for (MySQLDataType mySqlDataType : MySQLDataType.values()) {
if (mySqlDataType.compareToString(dataType)) {
this.dataType = mySqlDataType;
break;
}
}
}
/**
* 符号の有無を設定する
* @param unsigned
*/
public void setUnsigned(boolean unsigned) {
this.unsigend =unsigned;
}
/**
* 桁数を設定する
* @param unsigned
*/
public void setLength(String length) {
this.length = length;
}
}
先ほどのSQLをこのプログラムを使用してRedshift用に変換した結果は以下のようになります。
CREATE TABLE "employees" ("id" BIGINT, "name" VARCHAR(1020), "age" SMALLINT, "hire_date" DATE, "created_at" TIMESTAMP, "updated_at" TIMESTAMP);
見て頂ければわかるようにNOT NULL、主キー、外部キーなどの制約は無視しています。 必要であれば、ソースをちょこっと修正していただければ対応できると思います。
![[レポート] JJUG CCC 2024 Fallに行ってきました](https://devio2024-media.developers.io/image/upload/v1730277254/user-gen-eyecatch/awivhjatkofztrkayhdv.png)
