はじめに
他のデータベース(以下、DB)からVerticaへの移行概要については、「Verticaへの移行について」でご紹介しました。今回は、MySQLを例にあげてテーブル、SQL文をVerticaで利用するまでの具体的な手順を解説します。
MySQLからの移行ステップ
Verticaに移行するまでのステップと各ステップにおける作業対象のDBは、以下のとおりです。
ステップ1. Verticaインストール、DB作成
まず、はじめにVerticaのインストールを行い、DBを作成します。Verticaのインストールは、Vertica Community Edition(無償)をダウンロードし、rpmを展開するだけなので簡単です。
ステップ2. テーブルのDDL出力
MySQLのテーブルをVertica用のテーブルに置換えるために「show create table」コマンドや「mysqldump」コマンドでテーブルのDDLを出力します。「MySQL Workbench」などのツールを使うことで、簡単にDDLを出力することもできます。
構文1
mysql> show create table <テーブル名>;
実行例1
mysql> show create table test1;
+-------+----------------------------------------------
| Table | Create Table
+-------+----------------------------------------------
| test1 | CREATE TABLE `test1` (
`col1` tinyint(4) DEFAULT NULL,
`col2` varchar(30) DEFAULT NULL,
`col3` datetime DEFAULT NULL,
KEY `col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis |
+-------+----------------------------------------------
1 row in set (0.00 sec)構文2
# mysqldump <データベース名> <テーブル名> -u <ユーザ名> -p <パスワード> --no-data
実行例2
# mysqldump testdb test1 -u root -p --no-data
Enter password: *****
-- MySQL dump 10.13 Distrib 5.7.12, for Linux (x86_64)
--
-- Host: localhost Database: testdb
-- ------------------------------------------------------
-- Server version 5.7.12-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `test1`
--
DROP TABLE IF EXISTS `test1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `test1` (
`col1` tinyint(4) DEFAULT NULL,
`col2` varchar(30) DEFAULT NULL,
`col3` datetime DEFAULT NULL,
KEY `col1` (`col1`)
) ENGINE=InnoDB DEFAULT CHARSET=sjis;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on YYYY-MM-DD hh:mm:ssステップ3. テーブルのDDL変換
MySQLとVerticaで利用可能なデータ型を比較して、Verticaが対応しているデータ型に置換えます。以下は、MySQLとVerticaのデータ型のマッピング表です。
数値型
MySQL | Vertica | |||
|---|---|---|---|---|
データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
整数型 | TINYINT | -128～127 | TINYINT | -9,223,372,036,854,775,807～ |
SMALLINT | -32,768～32,767 | SMALLINT | -9,223,372,036,854,775,807～ | |
MEDIUMINT | -8,388,608～8,388,607 | ---- | (対応データ型無し) | |
INT | -2,147,483,648～2,147,483,647 | INT | -9,223,372,036,854,775,807～ | |
BIGINT | -9,223,372,036,854,775,808～ | BIGINT | -9,223,372,036,854,775,807～ | |
固定小数点型 | DECIMAL | 任意の精度及びスケール指定 | DECIMAL | 任意の精度及びスケール指定 |
NUMERIC | 任意の精度及びスケール指定 | NUMERIC | 任意の精度及びスケール指定 | |
浮動小数点型 | FLOAT | -3.402823466E+38～ | FLOAT | 64ビットのIEEE-754浮動小数点形式で格納 |
DOUBLE | -1.7976931348623157E+308～ | DOUBLE | 64ビットのIEEE-754浮動小数点形式で格納 | |
文字列型
MySQL | Vertica | |||
|---|---|---|---|---|
データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
文字列型 | CHAR(M) | 固定長文字列 | CHAR(M) | 固定長文字列 |
VARCHAR(M) | 可変長文字列 | VARCHAR(M) | 可変長文字列 | |
TINYTEXT | 最長255(2の8乗-1)バイト | ---- | (対応データ型無し) | |
TEXT | 最長65,535 | VARCHAR(M) | 可変長文字列 | |
MEDIUMTEXT | 最長16,777,215 | LONG VARCHAR(M) | 可変長文字列 | |
LONGTEXT | 最長4,294,967,295 | |||
日付/時刻型
MySQL | Vertica | |||
|---|---|---|---|---|
データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
日付/時刻型 | DATE | フォーマット: | DATE | フォーマット: |
DATETIME | フォーマット: | DATETIME | フォーマット: | |
TIMESTAMP | フォーマット: | TIMESTAMP | フォーマット: | |
TIME | フォーマット: | TIME | フォーマット: | |
YEAR[(2|4)] | フォーマット: | ---- | (対応データ型無し) | |
バイナリ/BLOB型
MySQL | Vertica | |||
|---|---|---|---|---|
データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
バイナリ型 | BINARY(M) | 固定長バイナリバイト文字列 | BINARY(M) | 固定長バイナリバイト文字列 |
VARBINARY(M) | 可変長バイナリバイト文字列 | VARBINARY(M) | 可変長バイナリバイト文字列 | |
BLOB型 | TINYBLOB | 最長255(2の8乗-1)バイト | ---- | (対応データ型無し) |
BLOB | 最長65,535 | BINARY(M) | BINARY(M) | |
MEDIUMBLOB | 最長16,777,215 | LONG VARBINARY(M) | 可変長rawバイトのデータを保持 | |
LONGLOB | 最長4,294,967,295 | |||
ENUM/SET型
MySQL | Vertica | |||
|---|---|---|---|---|
データ型分類 | データ型 | データ範囲 | データ型 | データ範囲 |
ENUM/SET型 | ENUM | 列挙値の数 | ---- | (対応データ型無し) |
SET | セットメンバーの数 | ---- | (対応データ型無し) | |
ステップ4. テーブル作成
置換えたテーブル定義をもとに、Verticaでテーブルを作成します。一例ですが、MySQLからVerticaに置換えて、テーブルを作成した結果は以下のとおりです。
【MySQL】
mysql> CREATE TABLE `test1` (
-> `col1` tinyint(4) DEFAULT NULL,
-> `col2` varchar(30) DEFAULT NULL,
-> `col3` datetime DEFAULT NULL,
-> INDEX `col1` (`col1`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=sjis
-> ;
Query OK, 0 rows affected (0.33 sec)
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1 | tinyint(4) | YES | MUL | NULL | |
| col2 | varchar(30) | YES | | NULL | |
| col3 | datetime | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)【Vertica】
dbadmin=> CREATE TABLE test1 (
dbadmin(> col1 TINYINT DEFAULT NULL,
dbadmin(> col2 VARCHAR(90) DEFAULT NULL,
dbadmin(> col3 DATETIME DEFAULT NULL
dbadmin(> );
CREATE TABLE
dbadmin=> \d test1;
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+-----------------+----------+-------------+-------------
public | test1 | col1 | int | 8 | NULL::int | f | f |
public | test1 | col2 | varchar(90) | 90 | NULL | f | f |
public | test1 | col3 | timestamp | 8 | NULL::timestamp | f | f |
(3 rows)MySQLからVerticaに置換えるまでのポイントを簡単に纏めました。
・Verticaは列指向DBのため、インデックスは存在しません。作成は不要です。
・MySQLで指定していた「ENGINE」や「CHARSET」の指定はできません。DBキャラクタセットは「UTF8」のみに対応しています。
・Verticaではオブジェクト名を「`」(バッククォーテーション)で囲むことはできません。
・「MySQL：varchar(30)」から「Vertica：varchar(90)」に変更しています。MySQLのカラムサイズの指定は「文字数」ですが、Verticaは「バイト数」の指定のためです。 VerticaのDBキャラクタセットはUTF8なので、マルチバイト1文字は「3バイト」で扱われます。そのため、VerticaのカラムサイズをMySQLの3倍に指定しています。
・Verticaの「tinyint型」は「int型」のシノニムなので、テーブル定義を確認すると「int型」になります。
・Verticaの「datetime型」は「timestamp型」のシノニムなので、テーブル定義を確認すると「timestamp型」になります。
ステップ5. データのCSV出力
Verticaのテーブルにデータを取り込むため、MySQLからデータを「SELECT .. INTO OUTFILE」コマンドでCSVに出力します。MySQLに対応しているETLツールの場合、ETLツールからCSVの出力をすることもできます。
構文
mysql> SELECT * FROM <テーブル名> INTO
OUTFILE ‘<csv>’ FIELDS TERMINATED BY ‘<区切り文字>‘;</csv>
実行例
mysql> SELECT * FROM test1 INTO OUTFILE '/tmp/test1.csv' FIELDS TERMINATED BY ',';
Query OK, 10 rows affected (0.00 sec)ステップ6. CSVの文字コード変換
VerticaがサポートしているDBキャラクタセットはUTF8のみです。そのため、対象CSVファイルの文字コードがUTF-8形式ではない場合には、以下のコマンドを利用し、エンコーディングする必要があります。文字コードの変換に対応しているETLツールもあるので、ETL側で変換することもできます。MySQLのDBキャラクタセットが、SJISの場合は出力されたCSVファイルもSJISで出力されるので、注意してください。
構文
# nkf -wx <変換元ファイル名> > <変換先ファイル名>
または
# iconv -f <変換元コード名> -t UTF8 <変換元ファイル名> > <変換先ファイル名>
エンコードを確認する場合には、以下のコマンドを実行します。
# nkf -g <変換先ファイル名>
※「iconv」コマンドには文字コードを判定する機能がありません。
※「nkf」コマンドがOS標準パッケージに含まれていない場合には、ソースなどからインストールしてください。
実行例
# nkf -g /tmp/test1.csv
Shift_JIS (LF)
# nkf -wx /tmp/test1.csv > /tmp/test1_utf8.csv
# nkf -g /tmp/test1_utf8.csv
UTF-8 (LF)
# cat test1_utf8.csv
1,北海道,2016-01-01 01:10:35
2,東京,2016-02-24 05:01:30
3,大阪,2016-03-30 12:00:00
4,福岡,2016-04-05 10:10:24
5,神奈川,2016-05-10 08:30:01
6,京都,2016-06-19 03:35:10
7,埼玉,2016-07-01 09:28:15
8,千葉,2016-08-31 21:49:54
9,広島,2016-09-30 15:45:08
10,沖縄,2016-10-01 19:46:15ステップ7. CSVのデータロード
UTF8に変換したCSVファイルをVerticaの各テーブルに「COPY」コマンドで、ロードします。
構文
dbadmin=> COPY test1 FROM ‘<csv>’ DELIMITER ‘<区切り文字>‘ <オプション>;</csv>
実行例
dbadmin=> COPY test1 FROM '/tmptest1_utf8.csv' DELIMITER ',' DIRECT;
Rows Loaded
-------------
10
(1 row)
dbadmin=> select * from test1;
col1 | col2 | col3
------+--------+---------------------
1 | 北海道 | 2016-01-01 01:10:35
2 | 東京 | 2016-02-24 05:01:30
3 | 大阪 | 2016-03-30 12:00:00
4 | 福岡 | 2016-04-05 10:10:24
5 | 神奈川 | 2016-05-10 08:30:01
6 | 京都 | 2016-06-19 03:35:10
7 | 埼玉 | 2016-07-01 09:28:15
8 | 千葉 | 2016-08-31 21:49:54
9 | 広島 | 2016-09-30 15:45:08
10 | 沖縄 | 2016-10-01 19:46:15
(10 rows)ステップ8. プロジェクションの最適化
各テーブルへのロード完了後、「admintools」もしくは「Management Console」を利用し、プロジェクションの最適化を実行します。Vertica独自の作業であり、他のDBと大きく違うポイントです。プロジェクションについては、「プロジェクションの概要」で詳しく紹介しています。
ステップ9. アプリの実行SQL洗出し
Verticaで実行できるSQL文に置換えるために、MySQLに対して実行しているSQL文の洗出しをおこないます。アプリーケションで実行しているSQL文のつくりは、恐らく類似しているものが多いと思います。Vertica用にSQL文を置換える場合、以下のように進めると効果的かもしれません。
・幾つかパターンの違うSQL文をピックアップする。そのSQL文に対して、修正ポイントを整理する。
・整理したポイントをもとに、全てのSQL文に対して横展開を実施する。
ステップ10. SQL変換
MySQLに対して実行していたSQL文をVerticaで実行できるようにSQL文を置換えます。Verticaは「ANSI SQL99」に準拠しているため、基本的には他のDBで実行しているSQL文をそのまま利用することができます。ただし、一例となりますが、以下の構文はMySQLから、Verticaに移行する場合は置換えが必要になります。
MySQL | Vertica | 説明 |
|---|---|---|
SELECT col1, col2, CONCAT(col1,' ',col2) as col3 FROM test1; | SELECT col1, col2, col1 || ' '|| col2 as col3 FROM test1; | CONCAT関数 → 「 || 」に置換する必要があります。 |
SELECT col1, if(col1='あ','○','×') as col2 FROM test1; | SELECT col1, CASE WHEN col1='あ' THEN '○' ELSE '×' END as col2 FROM test1; | IF文 → CASE文 に置換する必要があります。 |
SELECT cast(col1 as SIGNED) as cast_value FROM test1; | SELECT cast(col1 as int) as cast_value FROM test1; | CAST関数で、数値型を指定する方法が違います。 |
SELECT (CASE WHEN SUBSTRING( ' ',2 ,1 ) = 1 THEN 1 ELSE 0 END) as test1 FROM dual; | SELECT (CASE WHEN SUBSTRING( ' ',2 ,1 ) = '1' THEN 1 ELSE 0 END) as test1 FROM dual; | CASE文とSUBSTRING関数を利用し、文字データを比較する場合、指定方法が違います。 |
SELECT IFNULL(SUBSTRING('1234',2,1),0) + 1 FROM dual; | SELECT IFNULL(SUBSTRING('1234',2,1),'0')::int + 1 FROM dual; | IFNULL文とSUBSTRING関数を利用し、取得結果を加算する場合、データ型の指定する方法が違います。 |
SELECT DATE_FORMAT(SYSDATE(),'%Y%m') FROM dual; | SELECT TO_CHAR(SYSDATE(),'yyyymmdd') FROM dual; | DATE_FORMAT関数 → TO_CHAR関数 に置換する必要があります。 |
SELECT DATE_ADD(SYSDATE(),INTERVAL -1 MONTH) FROM dual; | SELECT ADD_MONTHS(SYSDATE(),-1) || ' ' || TO_CHAR(SYSDATE(),'HH24:MI:SS') FROM dual; | (月の計算) DATE_ADD関数 → ADD_MONTHS関数 に置換する必要があります。 |
SELECT DATE_ADD(SYSDATE(),INTERVAL -1 DAY) FROM dual; | SELECT SYSDATE() -1 FROM dual; | (日の計算) DATE_ADD関数 → SYSDATE関数 に置換する必要があります。 |
SELECT col1, CASE WHEN col1="あ" THEN "○" ELSE "×" END as col2 FROM test1; | SELECT col1, CASE WHEN col1='あ' THEN '○' ELSE '×' END as col2 FROM test1; | 文字データを指定する場合、ダブルコーテェーションは使用できません。 |
SELECT * FROM test1 where col1 in (1,2); | SELECT * FROM test1 where col1 in ('1','2'); | IN句で条件を指定する場合、文字データの指定方法が違います。 |
SELECT test1.col1 FROM test1 LEFT JOIN test2 on test1.col1 = test2.col1 GROUP BY col1; | SELECT test1.col1 FROM test1 LEFT JOIN test2 on test1.col1 = test2.col1 GROUP BY test1.col1; | 複数テーブルで同一列名が存在する場合、曖昧指定はできません。 |
SELECT col1, col2 FROM test1 GROUP BY col1; | SELECT col1, col2 FROM test1 GROUP BY col1,col2; | GROUP BY句の曖昧指定はできません。 |
ステップ11. SQL変換後の動作確認
Vertica用に置換えたSQL文の動作確認をおこないます。トライ＆エラーの繰り返しの作業です。SQL文が実行できない場合のエラーですが、Verticaは詳しいエラー内容を教えてくれます。そのため、デバッグ作業ですが、比較的容易にできると思います。
実行例
dbadmin=> SELECT col1, col2, CONCAT(col1,' ',col2) as col3 FROM test1;
ERROR 3457: Function CONCAT(int, unknown, varchar) does not exist, or permission is denied for CONCAT(int, unknown, varchar)
HINT: No function matches the given name and argument types. You may need to add explicit type casts
dbadmin=> SELECT col1, col2 FROM test1 GROUP BY col1;
ERROR 2640: Column "test1.col2" must appear in the GROUP BY clause or be used in an aggregate function検証バージョンについて
この記事の内容はMySQL5.7(InnoDB)、Vertica 7.2で確認しています。
※OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。
文中の社名、商品名等は各社の商標または登録商標である場合があります。