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、主キー、外部キーなどの制約は無視しています。
必要であれば、ソースをちょこっと修正していただければ対応できると思います。