bcp ユーティリティを使用した SQL Server データベースの Babelfish for Aurora PostgreSQL 移行

Babelfish for Aurora PostgreSQL には、SQL Server ワイヤプロトコルと、Microsoft SQL Server で使用されるクエリ言語である T-SQL のサポートが含まれています。つまり、開発者は Babelfish を使用して、データベースドライバーを切り替えたり、クエリを完全に書き直したりすることなく、Amazon Aurora PostgreSQL Compatible エディションで既存の SQL Server アプリケーションを実行できます。

SQL Server から Babelfish for Aurora PostgreSQL に移行する場合、次のようなさまざまなデータ移行オプションがあります。

AWS Database Migration Service (AWS DMS) を使用する。詳細については、「AWS Database Migration Service で Babelfish for Aurora PostgreSQL をターゲットとして指定可能に」を参照してください。
SQL Server Integration Services (SSIS) を使用する。詳細については、「SSIS と Babelfish を使用した SQL Server から Aurora PostgreSQL への移行」を参照してください。
T-SQL insert コマンドの生成 (SQL Server Management Studio によるスクリプトの生成)。
一括コピープログラム (bcp) ユーティリティを使用する。

この投稿では、データ移行に bcp を使用する場合の詳細と制限について説明します。

ソリューション概要

SQL Server の開発者や管理者であれば、bcp ユーティリティについて聞いたことがあるでしょう。bcp ユーティリティは、SQL Server データベースとの間で大量のデータをインポートまたはエクスポートできるコマンドラインツールです。

bcp を使用すると、ある SQL Server のテーブルから Babelfish for Aurora PostgreSQL にデータをすばやく転送できます。さらに、Insert メカニズムは行ごとではなく行のバッチを使用して最適化されるため、bcp は効率的です。また、ロギング操作を最小限に抑えることもできます。

Babelfish for Aurora PostgreSQL の詳細については、「Babelfish for Aurora PostgreSQL の使用」を参照してください。

前提条件

以下の前提条件を満たしていることを確認してください。

SQL Server Management Studio (SSMS) または bcp ユーティリティをインストールする必要があります。
SQL Server ネイティブクライアントを端末にインストールする必要があります。

bcp の制約

このデータ移行オプションには、次の制限があることに注意してください。

bcp はテーブルスキーマを移行しません。
bcp は SQL Server データベースと Babelfish を連携しますが、いくつかの制限があります。特定のオプション (-C、-T、-G、-K、-R、-V、-h) はサポートされていません。
Babelfish v2.1 (Aurora PostgreSQL 14.3) 以降でサポートされています。
フィールド / 行ターミネータがデータに含まれる場合、既知の問題があります。デフォルトのフィールドターミネータは \t (タブ文字) で、デフォルトの行ターミネータは \n (改行文字) です。使用しているフィールドターミネータがデータに含まれていて (Major, Maryなど) 、, をフィールドターミネータとして使用している場合、このレコードをインポートする際に問題が発生します (無効な文字値のエラーメッセージ)。

たとえば、次の列があるテーブルを想像してみてください。

ID	Name	Date
1	Major,Mary	1981-01-01
2	John	1980-02-02

bcp をターミネーターフィールド (-t) として使用すると、次の出力が得られます。

1,Major,Mary,1981-01-01
2,John,1980-02-02

最初の行には、データに含まれていた追加項目があることに注意してください。このレコードのデータインポートは失敗する可能性があります。または、データをエクスポートおよびインポートするときに -t | パラメータやその他のカスタムターミネータ文字を使用することもできます。フィールドターミネータ -t | を使用すると、次のような出力が得られるはずです。

1|Major,Mary|1981-01-01 
2|John|1980-02-02

同じことが行ターミネータにも当てはまります。カスタマイズしたフィールド / 行ターミネータの使用方法の詳細については、「フィールドターミネータと行ターミネータの指定」を参照してください。

BCP を使用した SQL Server からのデータエクスポート

次の構文を参考にして、bcp を使用して SQL Server データベースからデータをエクスポートできます。

bcp [schema.table_name] out [output_file] -c -d [database_name] -S [server_name] -U [username] -P [password]

ソーステーブルの名前、出力ファイルの名前、およびデータベースアクセス認証情報を指定する必要があります。bcp は、テーブルの全てのデータを指定されたファイルにエクスポートします。

テーブルが数百ある場合は、次の T-SQL コマンドを使用して、データをエクスポートする bcp コマンドを生成できます。

USE MyDBName
GO
DECLARE @Folder VARCHAR(100)='c:\temp\', @dbName VARCHAR(100) = 'MyDBName', @sqlInstance VARCHAR(50) = 'SQLServer Instance Name', @user VARCHAR(50) = 'SQL Login', @pwd VARCHAR(100) = 'MyPWD#123'

SELECT CONCAT('bcp ',SCHEMA_NAME(schema_id),'.',name,' out "',@Folder,name,'.dat','" -c -d ',@dbName,' -S ',@sqlInstance,' -U ',@user,' -P ', @pwd)
FROM sys.tables
WHERE is_ms_shipped = 0
GO

bcp を使用した Babelfish for Aurora PostgreSQL へのデータインポート

次の構文を参考にして、bcp を使用して Babelfish にデータをインポートできます。

bcp [schema.table_name] in [input_file] -c -S [server_name] -d [database_name] -U [username] -P [password]

保存先テーブルの名前、入力ファイルの名前、およびデータベースアクセス認証情報を指定する必要があります。bcp は、ファイルからすべてのデータを指定されたテーブルにインポートします。

データをインポートする場合、文字データ型 (-c) またはネイティブ形式 (データのエクスポート時に使用したバイナリ (-n) など) を使用してデータをエクスポートしたときと同じパラメータを使用することが非常に重要であることに注意してください。これはプラットフォーム間のデータ移行であるため、-c パラメータを使用する必要があります。

テーブルが数百ある場合は、次の T-SQL コマンドを使用して、データをインポートする bcp コマンドを生成できます。

USE MyDBName
GO
DECLARE @Folder VARCHAR(100)='c:\temp\', @dbName VARCHAR(100) = 'MyDBName', @sqlInstance VARCHAR(100) = 'Mybbf_instance.cluster-cxxxxxxxxxbpc.us-east-1.rds.amazonaws.com', @user VARCHAR(50) = 'postgreslogin', @pwd VARCHAR(100) = 'MyPWD#123'

SELECT CONCAT('bcp ',SCHEMA_NAME(schema_id),'.',name,' in "',@Folder,name,'.dat','" -e "',@Folder,name,'.err','" -c -d ',@dbName,' -S ',@sqlInstance,' -U ',@user,' -P ', @pwd)
FROM sys.tables
WHERE is_ms_shipped = 0

bcp パラメータ

bcp には、データの形式や文字エンコーディングを指定する機能など、多くの便利なパラメータがあります。

次の表は、bcp ユーティリティを使用して Babelfish for Aurora PostgreSQL にデータをインポートするときにサポートされる、一般的に使用されるパラメータをまとめたものです。

Parameter	Description	Default
`-b`	バッチごとの行数を指定します。各バッチは個別のトランザクションとしてインポートされ、記録されます。	All rows
`-c`	すべてのデータをテキストとしてフォーマットする操作を実行します。
`-d`	接続するデータベース名。
`-e`	bcp が転送できなかった行を格納するエラーファイル。このオプションは、インポートできなかった行とそれに対応するエラーメッセージを含むエラーファイルを生成します。
`-m`	bcp が操作をキャンセルするまでに許容される最大エラー数。デフォルトでは、bcp は操作がキャンセルされるまでに最大 10 件のエラーを考慮します。最初のエラーで操作をキャンセルするには、`-m 1` パラメータを使用します。	10
`-P`	Babelfish for Aurora PostgreSQL または SQL Server に接続するためのログインパスワードを指定します。
`-S`	Aurora PostgreSQL エンドポイント用の SQL Server または Babelfish を指定します。
`-t`	フィールドターミネータに通知します。詳細については、「フィールドターミネータと行ターミネータの指定 (SQL Server)」を参照してください。	`\t`
`-U`	Babelfish for Aurora PostgreSQL または SQL Server に接続するためのログインユーザー。

bcp のエラーハンドリング

このセクションでは、bcp を使用してデータをエクスポートおよびインポートするときに直面する可能性のあるいくつかの課題と、その処理方法について説明します。

テキストファイルを作成しているだけなので、通常、bcp エクスポートを実行しても問題はありません。ただし、このデータをインポートする場合、データの品質によっては問題が発生する可能性があります。最も一般的な問題は以下のとおりです。

主キー違反
区切りフィールドの間違い
無効なデータ

bcp はデフォルトで、操作をキャンセルする前にエラーの数が 10 個に制限されていることを覚えておくことが重要です。bcp に最初のエラーで操作をキャンセルさせたい場合は、パラメータ -m 1 を渡すことでこの動作を変更できます。-e パラメータを使用して、詳細なエラーを含むファイルを生成することもできます。

次のスクリーンショットは、データをインポートする際のエラーの例を示しています。

この例は、エラーが発生し、エラーが発生したにもかかわらずインポート処理が続行されたことを示しています。エラーメッセージには無効なデータがあったことが示されていますが、どのレコードがこの問題の原因になっているのか正確にはわかりません。最初のエラーでプロセスを停止させたい場合は、パラメータ m 1 を渡してください。

コマンドを繰り返し、-e パラメータを追加してエラーファイルを生成してみましょう。bcp コマンドの出力は似ていますが、今度はエラーファイル SalesOrderDetail.err があることに注意してください。

エラーファイルを開くと、エラーの詳細と、問題の原因となっているレコードが表示されます。

最初のエラーは、8 行目に無効な文字が含まれていることを示しています。次の行には、bcp がインポートしようとしているレコードも表示されます。3 番目の列が分割されていることがわかります。これはターミネータフィールドにエラーがある可能性があります。bcp で生成されたファイルを開いて 8 行目を調べると、問題のあるレコードと行が表示されます。

エラーファイルの次のエラーは 41,079 行目にあります。エラーメッセージには、無効な日付形式があると表示されます。エラーファイルにはエラーのあるレコードも表示され、日付 213-06-30 のフィールドに入力ミスがあることがわかります。

3 番目のエラーは 88,898 行目にあります。エラーメッセージに Invalid character と表示され、日付フィールドに 2014-0i-29 という無効な文字が表示されます。

これらは、bcp でエラー処理を行う方法の例です。-e パラメータを使用すると、エラーとエラーのあるレコードを検索する時間を大幅に節約できます。

ベストプラクティス

bcp を使用して SQL Server データベースからデータを移行する場合、次のベストプラクティスを考慮する必要があります。

データ移行後に外部キーを作成することを検討してください。これにより、データの整合性が確保されます。外部キーを削除せずにデータをインポートする場合は、テーブルを正しい順序でロードするようにしてください。そうしないと、外部キー違反があることを知らせるエラーメッセージが表示され、操作が失敗します。
大きなテーブルや大量のデータをインポートする場合は、インデックスを削除し、bcp を使用してデータをロードしてから、インデックスを再作成することを検討してください。テーブルを読み込む前にインデックスを削除すると、データロード処理が速くなり、その後、必要に応じてインデックスを再作成します。
SQL Server を Aurora PostgreSQL に移行するので、文字データ型を使用してデータをエクスポートおよびインポートするには -c パラメータが必要です。
フィールドターミネータと行ターミネータがデータに含まれている場合は注意してください。
大量のレコードをインポートする場合、-e パラメータを使用してエラーファイルを生成します。

まとめ

この投稿では、AWS DMS、SSIS、またはカスタマイズされたスクリプトを使用する従来のアプローチを越えて、データ移行の代替オプションを検討しました。その過程で、bcp ユーティリティがサポートするさまざまなパラメータを示しました。これにより、データ移行プロセスを微調整できるようになりました。また、問題のあるデータや誤ったデータを扱う際に発生する可能性のある問題を調査するのに役立つ、エラーを効果的に処理する手法についても検討しました。

学習の締めくくりとして、bcp ユーティリティを使用して Babelfish へのデータ移行をスムーズかつ成功させるためのベストプラクティスを掘り下げました。

この機能と Babelfish for Aurora PostgreSQL の詳細については、「Babelfish for Aurora PostgreSQL の使用」を参照してください。

翻訳はソリューションアーキテクトのYoshinori Sawada が担当しました。原文はこちらです。

著者について

Marcelo Fernandes は、AWS プロフェッショナルサービスチームのシニアデータベースアーキテクトです。データベースソリューションをオンプレミスのデータセンターから AWS に移行してモダナイズする過程で、お客様を支援してきました。

David Carvalho Queiroz は、データベースエンジニアリングマネージャーとして Amazon Games のライブオペレーションデータベースチームを率いています。Amazon Games 内の複数のタイトルのデータベースアーキテクチャの AWS への移行、データパイプライン開発、キャパシティプランニング、最適化/チューニング、自動化戦略、GDPR コンプライアンスを担当しています。

Eduardo Valentim は AWS プロフェッショナルサービスチームのシニアデータベースコンサルタントで、データベース分野で 17 年以上の経験があります。Eduardo はキャリアを通じて、移行、設計、パフォーマンスの最適化など、データベース関連の課題を抱えるお客様の支援をしてきました。Eduardo は、パフォーマンスとセキュリティに関する最新のベストプラクティスを常に把握することで、クライアントの環境が成功に向けて最適化されていることを確認しています。

Amazon Web Services ブログ