SQL ServerからRedshiftへデータ移行する場合の文字列型のサイズについて

kobayashi.m

2020.01.15

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

はじめに

データアナリティクス事業本部のkobayashiです。

前回まででSQL Serverに接続してデータを取得して加工するといった処理がPythonで行えるようになりました。実はここまでの下準備をして最終的に行いたかった事はSQL Serverから取得したデータをRedshiftへ移行することでした。

後はRedshiftでデータベース、スキーマ、テーブルを作成してデータをCOPYコマンドを使ってS3に保存したファイルからデータをロードするという作業を行えば完了なのですが、テーブルを作成してデータをロードする際に全角文字列の扱いに苦戦してしまったのでその内容と解決法をまとめます。

前回までの記事

PythonでODBC接続を行えるpyodbcをインストールする

SQL Serverからデータを取得するためにsqlcmdをインストールする

環境

Redshift
SQL Server 2017

SQL ServerからRedshiftへのデータ移行時の問題

Redshiftへ移行先となるテーブルを作る際に全角文字列を格納したカラムのサイズが問題となりました。全角文字なのでSQL Serverの文字列型のサイズを単純に2倍してvarchar(2n)とすれば問題ないと考えてテーブルを作成し、データを移行したのですが文字列が溢れてしまいCOPYコマンドでエラーが出てしまいました。

SQL Serverで扱える文字列型には「varchar」、「nvarchar」、「char」、「nchar」があります。その中でも全角文字列が扱える型としては「nvarchar」、「nchar」があります。以下Microsoft公式ページより引用です。

nvarchar [ ( n | max ) ] 可変サイズの文字列データです。 n によってバイトペアでの文字列のサイズが定義されます。1 から 4,000 までの値を指定できます。 max は、ストレージの最大サイズが 2^30-1 文字 (2 GB) であることを示します。ストレージのサイズは、n の 2 倍のバイト数 + 2 バイトです。 UCS-2 エンコードの場合、ストレージのサイズは n の 2 倍のバイト数 + 2 バイトとなり、格納できる文字数もまた n となります。 UTF-16 エンコードの場合、ストレージのサイズは引き続き n の 2 倍のバイト数 + 2 バイトですが、補助文字によって 2 つのバイトペア (またはサロゲートペア) が使用されるため、格納できる文字数は n よりも少なくなる場合があります。 ISO シノニム nvarchar は national char のさまざまなと各国語文字がさまざまなです。

nchar [ ( n ) ] 固定サイズの文字列データです。 n によってバイトペアでの文字列のサイズが定義されます。1 から 4,000 までの値にする必要があります。ストレージのサイズは、n の 2 倍のバイト数です。 UCS-2 エンコードの場合、ストレージのサイズは n の 2 倍のバイト数となり、格納できる文字数もまた n となります。 UTF-16 エンコードの場合、ストレージのサイズは引き続き n の 2 倍のバイト数ですが、補助文字によって 2 つのバイトペア (またはサロゲートペア) が使用されるため、格納できる文字数は n よりも少なくなる場合があります。 nchar の ISO シノニムは、national char および national character です。

char および varchar|SQLドキュメント

このドキュメントを読むまではなんとなく

nvarchar(n)→最大文字数がn文字で可変長となる
nchar(n)→文字数がn文字でデータは固定長（足りない場合は半角スペースで埋められる）となる

という浅い認識でしたが、これがRedshiftへデータを移行した際に文字列が溢れる原因となっていました。

SQL Serverの文字列型の正しい認識

「nvarchar( n | max )」、「nchar(n)」いずれとも格納できるサイズは「文字数n」では無く、「nの2倍のバイト数」という点がポイントになります。例えば、n=10の場合は20バイトまで文字列が格納できることになります。通常の全角文字でしたら1文字2バイトで計算すれば10文字格納できる事になりますが、全角文字の中にはサロゲートペア（バイトペア）が使用されるものがあります（ex ?、?、?、?、?など）。この場合は4バイトの容量が必要となります。したがってn=10の場合でも格納できる文字数は10文字に満たない事があります。

サロゲートペア

SQL Serverへデータを投入して確かめる

create table test_schema.test
     (
        nvarchar_10 nvarchar(10),
        nchar_10 nchar(10)
     )

まずは単純な全角文字列を10文字INSERTしてみます。

INSERT INTO [test].[test_schema].[test] ([nvarchar_10], [nchar_10]) VALUES ('あいうえおかきくけこ', 'あいうえおかきくけこ');

SELECT * FROM [test].[test_schema].[test];
|nvarchar_10|nchar_10|
|あいうえおかきくけこ|あいうえおかきくけこ|

問題なく登録されています。

次にサロゲートペアである「?」を10文字INSERTしてみます。

INSERT INTO [test].[test_schema].[test] ([nvarchar_10], [nchar_10]) VALUES ('??????????', '??????????');
[2020-01-14 16:47:55] [22001][8152] String or binary data would be truncated.

予想通りエラーとなります。サロゲートペアは1文字で4バイトとなり5文字までなら格納できるので5文字INSERTしてみます。

INSERT INTO [test].[test_schema].[test] ([nvarchar_10], [nchar_10]) VALUES ('?????', '?????');

SELECT * FROM [test].[test_schema].[test];
|nvarchar_10|nchar_10|
|あいうえおかきくけこ|あいうえおかきくけこ|
|?????|?????|

問題なく登録出来ました。

全角文字列の扱いをRedshiftの設計へ反映させる

ここまででSQL Server上で文字列の扱いは理解出来たのでこれをRedshiftの設計へ反映させます。 Redshiftで文字列型VARCHARは以下のように公式に記載されています。

一定の制限を持つ可変長の文字列を格納するには、VARCHAR 列または CHARACTER VARYING 列を使用します。これらの文字列は空白で埋められないので、VARCHAR(120) 列は、最大で 120 個のシングルバイト文字、60 個の 2 バイト文字、40 個の 3 バイト文字、または 30 個の 4 バイト文字で構成されます。

VARCHAR または CHARACTER VARYING| Amazon Redshift

文字コードとしてUTF-8を使うことを想定すると多くの全角文字列は3バイト、サロゲートペアは4バイトとなります。これを考慮すると