この記事は公開されてから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側でエクスポートを行う際に変換を行わせる事で対応が出来そうです。以下に参考リンクを貼っておきましたので是非ご活用頂ければと思います。
- How can I convert a Sql Server 2008 DateTimeOffset to a DateTime - Stack Overflow
- CAST および CONVERT (Transact-SQL)
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で環境を用意してみました。
- RDSのSQL Server 2008で日本語のデータを登録する方法 ｜ Developers.IO
- 付録: SQL Server に関連する一般的な DBA タスク - Amazon Relational Database Service
- CREATE DATABASE (SQL Server Transact-SQL)
- RDS の SQL Server にデータベースを移行する際の覚書 at SE の雑記
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とは文字列データに関する扱いや設定が異なっており、またその設定内容についても多岐に渡っているのでこの辺りは必要に応じてより詳細な調査・対応も行わなければいけないかも知れないですね。こちらからは以上です。
24
