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

Amazon Redshift

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

以前投稿した以下エントリでは、MySQLのデータをAmazon Redshiftに移行する際の『テーブルデータ型』に関する設定の対応について情報をまとめて紹介しました。当エントリではその続編として『SQL Server』に関する情報のまとめを行いたいと思います。

目次

 

Amazon Redshiftのデータ型

変換対象のAmazon Redshiftのデータ型についてはMySQL編記載の以下の箇所をご参照ください。

 

SQLServerにおける『テーブル定義情報』の取得方法について

SQL Serverでは、データベースのオブジェクト情報を格納している『オブジェクトカタログ』に必要となるであろう定義情報が含まれています。Amazon Redshiftに対応する様なテーブル定義を作成する際は、まずここから移行元となるSQL Serverのテーブル定義情報を取得し、内容に応じてテーブル定義の生成内容を分岐させる...という方法が取れそうです。

 

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

次いで、データ型種別毎の対応表です。見出しの区切りは下記のMicrosoft社の関連ページに基づいた内容としています。

 

数値(真数)型

数値(真数)型に関するSQL ServerとAmazon Redshiftのデータ型については以下の通りです。bit型については直接の対応するデータ型はBOOLEAN型となりますが、この部分の情報をBIツール等で利用するのであればSQL Serverからのエクスポートの時点/RedshiftにBOOLEAN型で取り込んだ後に文字列型で置き換えてしまうのも手かも知れません。

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
tinyint 0 ~ 255 SMALLINT
INT2
-32768~+32767
smallint -2の15乗(-32,768)

2の15乗-1(32,767)
SMALLINT
INT2
-32768~+32767
int -2の31乗(-2,147,483,648)

2の31乗-1(2,147,483,647)
INT
INTEGER
INT4
-2147483648~
+2147483647
bigint -2の63乗(-9,223,372,036,854,775,808)

2の63乗-1(9,223,372,036,854,775,807)
BIGINT
INT8
-9223372036854775808

9223372036854775807
bit (1、0、またはNULLの値をとる整数型) BOOLEAN 参考:ブール型 - Amazon Redshift
numeric -10の38乗+1

10の38乗-1
有効値(最大有効桁数を使用した場合)
DECIMAL precision(精度):
デフォルト(指定無し)=18、最大=38
scale(スケール):
デフォルト(指定無し)=0、最大=37
decimal -10の38乗+1

10の38乗-1
有効値(最大有効桁数を使用した場合)
NUMERIC precision(精度):
デフォルト(指定無し)=18、最大=38
scale(スケール):
デフォルト(指定無し)=0、最大=37
smallmoney - 214,748.3648

214,748.3647
INT
INTEGER
INT4
-2147483648~
+2147483647
money -922,337,203,685,477.5808

922,337,203,685,477.5807
BIGINT
INT8
-9223372036854775808

9223372036854775807

 

数値(概数)型

数値(概数)型に関するSQL ServerとAmazon Redshiftのデータ型については以下の通りです。

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
float -1.79E+308~-2.23E-308、
0、
および2.23E-308~1.79E+308
DOUBLE PRECISION
FLOAT8
FLOAT
8バイト
有効な精度桁数:15桁
real -3.40E+38~-1.18E-38、
0、
および
1.18E-38~3.40E+38
DOUBLE PRECISION
FLOAT8
FLOAT
8バイト
有効な精度桁数:15桁

 

日付時刻型

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

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
Date 0001-01-01 ~ 9999-12-31
西暦1年1月1日 ~西暦9999年12月31日
DATE 4バイト
4713BC~294276AD
datetime 日付範囲:
 1753年1月1日~9999年12月31日
時刻範囲:
 00:00:00~23:59:59.997
タイムゾーンオフセット範囲:
 無し
TIMESTAMP 8バイト
4713BC~294276AD
datetime2 日付範囲:
 0001-01-01 ~ 9999-12-31
 西暦1年1月1日~西暦9999年12月31日
時刻範囲:
 00:00:00~23:59:59.9999999
タイムゾーンオフセット範囲:
 無し
TIMESTAMP 8バイト
4713BC~294276AD
datetimeoffset 日付範囲:
 0001-01-01~9999-12-31
 西暦1年1月1日~西暦9999年12月31日
時刻範囲:
 00:00:00~23:59:59.9999999
タイムゾーンオフセット範囲:
 -14:00~+14:00
---- (タイムゾーン部分は
Redshift対応無し)
smalldatetime 日付範囲:
 1900-01-01~2079-06-06
 1900年1月1日~2079年6月6日
時刻範囲:
 00:00:00 ~ 23:59:59
 2007-05-09 23:59:59は
次のように丸められます。
 2007-05-10 00:00:00
TIMESTAMP 8バイト
4713BC~294276AD
Time 00:00:00.0000000

23:59:59.9999999
---- (対応データ型無し)

SQL Serverの日付/日付時刻型については、型毎に特色が異なります。以下はその特色一覧です。

データ型 説明
Date 日付を定義
datetime 24 時間形式の時刻(1秒未満の秒を含む)と
組み合わせた日付を定義
datetime2 24時間形式の時刻と組み合わせた日付を定義
datetime2は既存のdatetime型を拡張して、
日付範囲と既定の有効桁数を増やし、
ユーザーが必要に応じて有効桁数を指定できるように
したものと考えることが出来る
datetimeoffset タイムゾーンを認識する
24時間形式の時刻と 組み合わせた日付を定義
smalldatetime 日付を時刻と組み合わせて定義
時刻は24時間制
秒数は常にゼロ(:00)で、1秒未満の秒を持たない
Time 1日の時刻を定義
時刻は24時間形式で
タイムゾーンは認識されない

6つあるデータ型のうち、4つはDATE型/TIMESTAMP型に収まるのですが、datetimeoffset型とTime型については対応するデータ型はAmazon Redshiftには存在しません。

datetimeoffset型については、SQL Server側で対応しているタイムゾーン部分の設定情報がAmazon Redshiftでは対応していない形となるため、正しく移行する事が出来ません。Amazon Redshiftで対応しているタイムゾーンはUTC一択のみであり、現状このタイムゾーンを変更する事も出来ません。

この部分の問題に対応させるためには、SQL Server側でエクスポートを行う際に変換を行わせる事で対応が出来そうです。以下に参考リンクを貼っておきましたので是非ご活用頂ければと思います。

Time型ついては、その名の如く"時間"しか情報が存在しないため、TIMESTAMP(日付時刻)型に置き換えるにしても情報が足りません。MySQL→Redshift編の日付時刻型で言及したのと同じ様に、日付型の情報が別項目で存在しているのであれば処理の過程で連結させて日付時刻型の項目(datetime型、datetime2型)に置き換えておく必要があるかと思います。

 

文字列型

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

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
char(n) Unicodeではない固定長の文字列データ
nで文字列の長さを定義、
指定できる値の範囲は1~8,000
CHAR 4096バイト
(※4096バイト以上の場合は対応出来ず)

varchar(n)
varchar(max)
Unicodeではない可変長の文字列データ
nで文字列の長さを定義し、
指定できる値の範囲は1~8,000
maxの場合は最大格納サイズが2の31乗-1 バイト(2GB)
格納サイズは、入力したデータの実際の長さ + 2バイト
VARCHAR 65535バイト
(64K-1)
(※65535バイトを超過する場合は対応出来ず)
text サーバーコードページ内の可変長の非Unicodeデータを指定
文字列の最大長:2の31乗-1 (2,147,483,647)
サーバーコードページが2バイト文字を使用する場合
格納サイズはそのまま 2,147,483,647バイト
文字列によっては、格納サイズが
2,147,483,647 バイトより少なくなることもある
VARCHAR 65535バイト
(64K-1)
(※65535バイトを超過する場合は対応出来ず)

検証用に環境を整えてみます。まずはデータベースの作成。日本語環境のデータベースが作成されているという事を想定し、Shift_JISで環境を用意してみました。

sql> CREATE DATABASE cmdb COLLATE Japanese_XJIS_100_BIN
completed in 1s 108ms

sql> USE cmdb
Changed database context to 'cmdb'.
completed in 306ms

ちなみに、日本語環境(Japanese)に関する照合順序の一覧も併せて出してみました。SQL Serverで環境を構築する際にはこれらの設定値が使えるようです。

SELECT
  name,
  description
FROM
  fn_helpcollations()
WHERE
  name like'%Japanese%';

# | name | description
---------------------------------------------------
1 | Japanese_BIN | "Japanese, binary sort"
2 | Japanese_BIN2 | "Japanese, binary code point comparison sort"
3 | Japanese_CI_AI | "Japanese, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
4 | Japanese_CI_AI_WS | "Japanese, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
5 | Japanese_CI_AI_KS | "Japanese, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
6 | Japanese_CI_AI_KS_WS | "Japanese, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
7 | Japanese_CI_AS | "Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
8 | Japanese_CI_AS_WS | "Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
9 | Japanese_CI_AS_KS | "Japanese, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
10 | Japanese_CI_AS_KS_WS | "Japanese, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
11 | Japanese_CS_AI | "Japanese, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
12 | Japanese_CS_AI_WS | "Japanese, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
13 | Japanese_CS_AI_KS | "Japanese, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
14 | Japanese_CS_AI_KS_WS | "Japanese, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
15 | Japanese_CS_AS | "Japanese, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
16 | Japanese_CS_AS_WS | "Japanese, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
17 | Japanese_CS_AS_KS | "Japanese, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
18 | Japanese_CS_AS_KS_WS | "Japanese, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
19 | Japanese_90_BIN | "Japanese-90, binary sort"
20 | Japanese_90_BIN2 | "Japanese-90, binary code point comparison sort"
21 | Japanese_90_CI_AI | "Japanese-90, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
22 | Japanese_90_CI_AI_WS | "Japanese-90, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
23 | Japanese_90_CI_AI_KS | "Japanese-90, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
24 | Japanese_90_CI_AI_KS_WS | "Japanese-90, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
25 | Japanese_90_CI_AS | "Japanese-90, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
26 | Japanese_90_CI_AS_WS | "Japanese-90, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
27 | Japanese_90_CI_AS_KS | "Japanese-90, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
28 | Japanese_90_CI_AS_KS_WS | "Japanese-90, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
29 | Japanese_90_CS_AI | "Japanese-90, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
30 | Japanese_90_CS_AI_WS | "Japanese-90, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
31 | Japanese_90_CS_AI_KS | "Japanese-90, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
32 | Japanese_90_CS_AI_KS_WS | "Japanese-90, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
33 | Japanese_90_CS_AS | "Japanese-90, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
34 | Japanese_90_CS_AS_WS | "Japanese-90, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
35 | Japanese_90_CS_AS_KS | "Japanese-90, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
36 | Japanese_90_CS_AS_KS_WS | "Japanese-90, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
37 | Japanese_90_CI_AI_SC | "Japanese-90, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
38 | Japanese_90_CI_AI_WS_SC | "Japanese-90, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
39 | Japanese_90_CI_AI_KS_SC | "Japanese-90, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
40 | Japanese_90_CI_AI_KS_WS_SC | "Japanese-90, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
41 | Japanese_90_CI_AS_SC | "Japanese-90, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
42 | Japanese_90_CI_AS_WS_SC | "Japanese-90, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
43 | Japanese_90_CI_AS_KS_SC | "Japanese-90, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
44 | Japanese_90_CI_AS_KS_WS_SC | "Japanese-90, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"
45 | Japanese_90_CS_AI_SC | "Japanese-90, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
46 | Japanese_90_CS_AI_WS_SC | "Japanese-90, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
47 | Japanese_90_CS_AI_KS_SC | "Japanese-90, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
48 | Japanese_90_CS_AI_KS_WS_SC | "Japanese-90, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
49 | Japanese_90_CS_AS_SC | "Japanese-90, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
50 | Japanese_90_CS_AS_WS_SC | "Japanese-90, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
51 | Japanese_90_CS_AS_KS_SC | "Japanese-90, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
52 | Japanese_90_CS_AS_KS_WS_SC | "Japanese-90, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"
53 | Japanese_Bushu_Kakusu_100_BIN | "Japanese-Bushu-Kakusu-100, binary sort"
54 | Japanese_Bushu_Kakusu_100_BIN2 | "Japanese-Bushu-Kakusu-100, binary code point comparison sort"
55 | Japanese_Bushu_Kakusu_100_CI_AI | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
56 | Japanese_Bushu_Kakusu_100_CI_AI_WS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
57 | Japanese_Bushu_Kakusu_100_CI_AI_KS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
58 | Japanese_Bushu_Kakusu_100_CI_AI_KS_WS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
59 | Japanese_Bushu_Kakusu_100_CI_AS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
60 | Japanese_Bushu_Kakusu_100_CI_AS_WS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
61 | Japanese_Bushu_Kakusu_100_CI_AS_KS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
62 | Japanese_Bushu_Kakusu_100_CI_AS_KS_WS | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
63 | Japanese_Bushu_Kakusu_100_CS_AI | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
64 | Japanese_Bushu_Kakusu_100_CS_AI_WS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
65 | Japanese_Bushu_Kakusu_100_CS_AI_KS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
66 | Japanese_Bushu_Kakusu_100_CS_AI_KS_WS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
67 | Japanese_Bushu_Kakusu_100_CS_AS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
68 | Japanese_Bushu_Kakusu_100_CS_AS_WS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
69 | Japanese_Bushu_Kakusu_100_CS_AS_KS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
70 | Japanese_Bushu_Kakusu_100_CS_AS_KS_WS | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
71 | Japanese_Bushu_Kakusu_100_CI_AI_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
72 | Japanese_Bushu_Kakusu_100_CI_AI_WS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
73 | Japanese_Bushu_Kakusu_100_CI_AI_KS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
74 | Japanese_Bushu_Kakusu_100_CI_AI_KS_WS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
75 | Japanese_Bushu_Kakusu_100_CI_AS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
76 | Japanese_Bushu_Kakusu_100_CI_AS_WS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
77 | Japanese_Bushu_Kakusu_100_CI_AS_KS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
78 | Japanese_Bushu_Kakusu_100_CI_AS_KS_WS_SC | "Japanese-Bushu-Kakusu-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"
79 | Japanese_Bushu_Kakusu_100_CS_AI_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
80 | Japanese_Bushu_Kakusu_100_CS_AI_WS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
81 | Japanese_Bushu_Kakusu_100_CS_AI_KS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
82 | Japanese_Bushu_Kakusu_100_CS_AI_KS_WS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
83 | Japanese_Bushu_Kakusu_100_CS_AS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
84 | Japanese_Bushu_Kakusu_100_CS_AS_WS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
85 | Japanese_Bushu_Kakusu_100_CS_AS_KS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
86 | Japanese_Bushu_Kakusu_100_CS_AS_KS_WS_SC | "Japanese-Bushu-Kakusu-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"
87 | Japanese_Unicode_BIN | "Japanese-Unicode, binary sort"
88 | Japanese_Unicode_BIN2 | "Japanese-Unicode, binary code point comparison sort"
89 | Japanese_Unicode_CI_AI | "Japanese-Unicode, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
90 | Japanese_Unicode_CI_AI_WS | "Japanese-Unicode, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
91 | Japanese_Unicode_CI_AI_KS | "Japanese-Unicode, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
92 | Japanese_Unicode_CI_AI_KS_WS | "Japanese-Unicode, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
93 | Japanese_Unicode_CI_AS | "Japanese-Unicode, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
94 | Japanese_Unicode_CI_AS_WS | "Japanese-Unicode, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
95 | Japanese_Unicode_CI_AS_KS | "Japanese-Unicode, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
96 | Japanese_Unicode_CI_AS_KS_WS | "Japanese-Unicode, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
97 | Japanese_Unicode_CS_AI | "Japanese-Unicode, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
98 | Japanese_Unicode_CS_AI_WS | "Japanese-Unicode, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
99 | Japanese_Unicode_CS_AI_KS | "Japanese-Unicode, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
100 | Japanese_Unicode_CS_AI_KS_WS | "Japanese-Unicode, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
101 | Japanese_Unicode_CS_AS | "Japanese-Unicode, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
102 | Japanese_Unicode_CS_AS_WS | "Japanese-Unicode, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
103 | Japanese_Unicode_CS_AS_KS | "Japanese-Unicode, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
104 | Japanese_Unicode_CS_AS_KS_WS | "Japanese-Unicode, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
105 | Japanese_XJIS_100_BIN | "Japanese-XJIS-100, binary sort"
106 | Japanese_XJIS_100_BIN2 | "Japanese-XJIS-100, binary code point comparison sort"
107 | Japanese_XJIS_100_CI_AI | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
108 | Japanese_XJIS_100_CI_AI_WS | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
109 | Japanese_XJIS_100_CI_AI_KS | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
110 | Japanese_XJIS_100_CI_AI_KS_WS | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
111 | Japanese_XJIS_100_CI_AS | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
112 | Japanese_XJIS_100_CI_AS_WS | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
113 | Japanese_XJIS_100_CI_AS_KS | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
114 | Japanese_XJIS_100_CI_AS_KS_WS | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
115 | Japanese_XJIS_100_CS_AI | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive"
116 | Japanese_XJIS_100_CS_AI_WS | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive"
117 | Japanese_XJIS_100_CS_AI_KS | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive"
118 | Japanese_XJIS_100_CS_AI_KS_WS | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive"
119 | Japanese_XJIS_100_CS_AS | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive"
120 | Japanese_XJIS_100_CS_AS_WS | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive"
121 | Japanese_XJIS_100_CS_AS_KS | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive"
122 | Japanese_XJIS_100_CS_AS_KS_WS | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive"
123 | Japanese_XJIS_100_CI_AI_SC | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
124 | Japanese_XJIS_100_CI_AI_WS_SC | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
125 | Japanese_XJIS_100_CI_AI_KS_SC | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
126 | Japanese_XJIS_100_CI_AI_KS_WS_SC | "Japanese-XJIS-100, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
127 | Japanese_XJIS_100_CI_AS_SC | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
128 | Japanese_XJIS_100_CI_AS_WS_SC | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
129 | Japanese_XJIS_100_CI_AS_KS_SC | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
130 | Japanese_XJIS_100_CI_AS_KS_WS_SC | "Japanese-XJIS-100, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"
131 | Japanese_XJIS_100_CS_AI_SC | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive, supplementary characters"
132 | Japanese_XJIS_100_CS_AI_WS_SC | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive, supplementary characters"
133 | Japanese_XJIS_100_CS_AI_KS_SC | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive, supplementary characters"
134 | Japanese_XJIS_100_CS_AI_KS_WS_SC | "Japanese-XJIS-100, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive, supplementary characters"
135 | Japanese_XJIS_100_CS_AS_SC | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive, supplementary characters"
136 | Japanese_XJIS_100_CS_AS_WS_SC | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive, supplementary characters"
137 | Japanese_XJIS_100_CS_AS_KS_SC | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive, supplementary characters"
138 | Japanese_XJIS_100_CS_AS_KS_WS_SC | "Japanese-XJIS-100, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive, supplementary characters"

文字列型のデータ型を定義したテーブルを作成。

sql> CREATE TABLE strings_test (
  id INT NOT NULL,
  item_char CHAR(10) NOT NULL,
  item_varchar VARCHAR(10) NOT NULL,
  item_text TEXT
)
completed in 512ms

データを投入してみます。VARCHAR型の項目については10桁で登録し、全角文字5文字までは登録出来、6文字以上を登録しようとすると文字数超過のエラー(String or binary data would be truncated)で弾かれました。この事からも分かるように、SQL Serverでは全角文字は2バイトで扱われるようです。

sql> INSERT INTO strings_test VALUES(1,'ABCDEFGHIJ','あいうえお','text123')
1 row(s) affected in 238ms

sql> SELECT * FROM strings_test
1 row(s) retrieved starting from 1 in 463ms (469ms total)

sql> INSERT INTO strings_test VALUES(2,'ABCDEFGHIJ','あいうえおか','text123456')
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

sql> INSERT INTO strings_test VALUES(3,'ABCDEFGHIJ','1234567890','text789');
1 row(s) affected in 220ms

sql> INSERT INTO strings_test VALUES(4,'ABCDEFGHIJ','12345678901','text789');
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

UTF8でデータベースを作ってみて挙動を確かめてみます。Shift_JISで作った時と同じ挙動を見せましたのでこの辺りは同じ基準で考えて良さそうです。

sql> CREATE DATABASE cmdb_utf8 COLLATE Japanese_Unicode_BIN
completed in 1s 135ms

sql> USE cmdb_utf8
Changed database context to 'cmdb_utf8'.
completed in 296ms

sql> CREATE TABLE strings_test_utf8 (
  id INT NOT NULL,
  item_char CHAR(10) NOT NULL,
  item_varchar VARCHAR(10) NOT NULL,
  item_text TEXT
)
completed in 252ms

sql> INSERT INTO strings_test_utf8 VALUES(1,'ABCDEFGHIJ','あいうえお','text123')
1 row(s) affected in 229ms

sql> INSERT INTO strings_test_utf8 VALUES(2,'ABCDEFGHIJ','あいうえおか','text123456')
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

sql> INSERT INTO strings_test_utf8 VALUES(3,'ABCDEFGHIJ','1234567890','text789');
1 row(s) affected in 233ms

sql> INSERT INTO strings_test_utf8 VALUES(4,'ABCDEFGHIJ','12345678901','text789');
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

表にも記載したように、SQL Serverで用意されている文字列型の最大桁数の方が、Amazon Redshiftで用意されている文字列型の最大桁数を軒並み超過してしまっています。桁数が(Redshift側の設定値に)収まっていれば問題無いですが、超過するようであれば何らか対策を講じる必要がありそうです。また、SQL Serverの場合、全角文字は2バイト:Amazon Redshiftの場合全角文字は3バイトとなる事から、カラム内の文字が全て全角文字だった場合の事を考えて、SQL Serverでの桁数 x 1.5 = Amazon Redshiftでの桁数とする調整は必要になりそうです。

 

Unicode文字列型

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

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
nchar(n) 固定長のUnicode文字列データ
nで文字列の長さを定義
指定できる値の範囲は1~4,000
ストレージのサイズはnの2倍のバイト数
照合順序のコードページで2バイト文字が
使用されている場合、記憶領域のサイズはnバイトのまま
文字列によっては、nバイトの記憶領域のサイズが
nに指定された値よりも小さくなる可能性がある
CHAR 4096バイト
(※4096バイト以上の場合は対応出来ず)

nvarchar(n)
nvarchar(max)
可変長のUnicode文字列データ
nで文字列の長さを定義
指定できる値の範囲は1~4,000
maxは最大格納サイズが2の31乗-1バイト(2GB)
記憶領域のサイズ(バイト単位)は、入力したデータの
実際の長さの 2倍のバイト数に2バイトを足した数
VARCHAR 65535バイト(64K-1)
(※65535バイトを超過する場合は対応出来ず)
ntext 文字列の最大長が2の30乗-1(1,073,741,823)の
可変長のUnicodeデータ
格納サイズは、入力した文字列の長さの2倍のバイト数
VARCHAR 65535バイト(64K-1)
(※65535バイトを超過する場合は対応出来ず)

それぞれUnicode文字列のデータ型を備えた項目でテーブルを定義し、データを投入してみます。文字列型とは異なり、NVARCHAR(10)で定義した型の項目には全角文字が10文字入りました。この事から、nvarchar型を指定した場合、許容出来る文字数=設定桁数となるようです。詳細は以下エントリなどをご参照ください。

sql> CREATE TABLE unicode_strings_test (
  id INT NOT NULL,
  item_nchar nchar(10) NOT NULL,
  item_nvarchar nvarchar(10) NOT NULL,
  item_ntext text NOT NULL
)
completed in 194ms

sql> INSERT INTO unicode_strings_test VALUES(1,'ABCDEFGHIJ','あいうえおかきくけこ','text123')
1 row(s) affected in 201ms

sql> INSERT INTO unicode_strings_test VALUES(2,'ABCDEFGHIJ','あいうえおかきくけこさ','text123')
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

sql> INSERT INTO unicode_strings_test VALUES(3,'ABCDEFGHIJK','あいうえおかきくけこ','text123')
The statement has been terminated.
[22001][8152] String or binary data would be truncated.

Amazon Redshiftへの桁数変換ですが、Amazon Redshiftでの定義を超過する桁数データをSQL Server側のデータが有していた場合の状況については文字列型と変わらず。文字列数に収まる場合、SQL Serverの場合、全角文字は1バイト:Amazon Redshiftの場合全角文字は3バイトとなる事から、カラム内の文字が全て全角文字だった場合の事を考えて、SQL Serverでの桁数 x 3 = Amazon Redshiftでの桁数とする調整が必要になりそうです。

 

バイナリ文字列型

バイナリ文字列型に関するSQL ServerとAmazon Redshiftのデータ型については以下の通りです。

SQL Server Amazon Redshift
データ型 データ範囲 データ型 データ範囲
binary 長さnバイトの固定長のバイナリデータ
nは1~8,000
記憶領域のサイズはnバイト
---- (対応データ型無し)
vbinary 可変長binaryデータ
nに指定できる値の範囲は1~8,000
maxは最大格納サイズが2の31乗-1
格納サイズは、入力したデータの実際の長さ+2バイト
入力するデータの長さは0バイトでもかOK
---- (対応データ型無し)
image 0~2の31乗-1(2,147,483,647)バイトの
可変長のバイナリデータ
---- (対応データ型無し)

バイナリデータ型に対応するAmazon Redshiftのデータ型はありません。

 

その他のデータ型

その他上記以外のデータ型に関するSQL ServerとAmazon Redshiftのデータ型については以下の通りです。これらのデータ型に対応するAmazon Redshiftのデータ型はありません。

SQL Server Amazon Redshift
データ型 説明 データ型 データ範囲
cursor カーソルへの参照を格納している変数やストアドプロシージャのOUTPUTパラメーターを表すデータ型 ---- (対応データ型無し)
hierarchyid 可変長のシステムデータ型
階層内の位置を表すために使用
---- (対応データ型無し)
sql_variant SQL Serverでサポートしている各種データ型の値を格納 ---- (対応データ型無し)
table 後で処理できるように結果セットを
格納するために使用できる特別なデータ型
---- (対応データ型無し)
rowversion データベース内で自動的に生成され、
一意の2進数を公開するデータ型
---- (対応データ型無し)
uniqueidentifier 16 バイトの GUID ---- (対応データ型無し)
xml XMLデータを格納するデータ型 ---- (対応データ型無し)
空間型 geography,geometryのデータを扱う ---- (対応データ型無し)

 

まとめ

以上、SQL ServerとAmazon Redshiftに於けるテーブルデータ型の変換に関する情報まとめでした。以前書いたMySQLとは文字列データに関する扱いや設定が異なっており、またその設定内容についても多岐に渡っているのでこの辺りは必要に応じてより詳細な調査・対応も行わなければいけないかも知れないですね。こちらからは以上です。

その他参考情報:

AWS Cloud Roadshow 2017 福岡