MS SQL Server から Babelfish へデータを移行
〜 Microsoft を使わない T-SQL その (3) 〜
 ⇨ 
2025-12-25 作成 福島
TOP > tips > migrate-ms-babel
[ TIPS | TOYS | OTAKU | LINK | MOVIE | CGI | AvTitle | ConfuTerm | HIST | AnSt | Asob | Shell ]

0. 前置き

データベースにはいくつかの要素があります。
  1. アクセス経路 (ライブラリ, ポート, etc)
  2. アクセス手段 (直接, SQL 言語, etc)
  3. ルール・ひな形 (スキーマ, テーブル, スクリプト, etc)
  4. 格納データ (テキスト, 数値, バイナリ, etc)
本稿は、アクセス経路と手段をそれぞれ TDS, T-SQL とし、データベースを
Microsoft SQL Server → Babelfish (OSS 版)
として移行することを考えます。

Babelfish 5.3.0 は、識別子に漢字を使えません。(データベース名、テーブル名、列名等)
まともな技術者なら (移植性を考慮して) 識別子に多バイト文字は使用しないはずだが、うっかり作成されたらただの地雷。
✓PostgreSQL→ 漢字使える
✓MS SQL Server→ 漢字使える
✗Babelfish→ 漢字使えない
Babelfish で識別子に漢字を使うと、文字化けかエラーのどちらかになる。(内部的には正常に動作しているようだが…)
PostgreSQL と MS SQL Server で識別子に漢字が使用可能だから、Babelfish で不能とは考えないのが普通だろう。
Microsofot は「非推奨」と言ってるものの、実装はされてるし警告も表示しない。

もしも識別子に漢字が使われていたら、以下のどちらかにしなければなりません。
  1. Babelfish への移行を諦める
  2. すべての識別子を ASCII 文字に置換する (クライアントアプリを含む)
移行の手順は以下の 3 段階とします。
1. ツールとサンプルの用意 
2.スキーマ (DDL) の移行既存 DB → 新規 DB
3.データの移行既存 DB → 新規 DB

1. ツールとサンプルの用意

1-1. ツールの用意
1-2. サンプルの用意
これは移行環境のテスト用。(分かってる人はスキップして良い)
MS SQL Server にデータベース「道具」を作成し、これをテスト用データベースとする。

− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -Q "
CREATE DATABASE 道具; -- データベース: 道具
GO
USE 道具;

CREATE TABLE 文房具 ( -- テーブル: 文房具
  ID   INT IDENTITY(1,1) PRIMARY KEY,
  名前 NVARCHAR(20),
  数   INT );

CREATE TABLE 筆箱 ( -- テーブル: 筆箱
  ID       INT IDENTITY(1,1) PRIMARY KEY,
  文房具ID INT NOT NULL );

-- 制約: 筆箱に入るのは文房具のどれか
ALTER TABLE 筆箱
ADD CONSTRAINT FK_筆箱_文房具 FOREIGN KEY (文房具ID) REFERENCES 文房具(ID);
" ⏎



PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -d 道具 -Q "
INSERT INTO 文房具 (名前,数) VALUES ('鉛筆',3);
INSERT INTO 文房具 (名前,数) VALUES ('消しゴム',2);
INSERT INTO 文房具 (名前,数) VALUES ('ノート',5);
INSERT INTO 文房具 (名前,数) VALUES ('定規',1);

INSERT INTO 筆箱 (文房具ID) VALUES (1),(2);
" ⏎



PS C:\> sqlcmd -S localhost,1433 -U sa -P "Password123" -d 道具 -Q "
SET NOCOUNT ON;
SELECT * FROM 文房具;
SELECT * FROM 筆箱;
" ⏎
ID          名前                 数
----------- -------------------- -----------
          1 鉛筆                           3
          2 消しゴム                       2
          3 ノート                         5
          4 定規                           1

ID          文房具ID
----------- -----------
          1           1
          2           2

PS C:\> exit ⏎

3. スキーマの移行

3-1. スキーマの抽出
AWS SCT を起動し、プロジェクトウィザードを選択する。
 File  >  New project wizard Ctrl-W 

 ⇓
Step 1. Choose a source: プロジェクト名と転送元サーバのアクセス方法を指定する。
 下記を記入して ボタンをクリックする。
 • プロジェクト名
 • プロジェクトを保存するディレクトリ
 • データベースの種類 (ETL は複数種の意味)
 • データベースの製品

 ⇓
Step 2. Connect to the source database (1): 転送元サーバへのアクセス方法を指定する。
 下記を記入して ボタンをクリックする。
 接続が成功すると ボタンがアクティブになる。
 • サーバ名 (IP アドレス)
 • 接続ポート (省略すると 1433)
 • 認証方式
 • ユーザ名 (データベースの管理権限があること)
 • パスワード
 • JDBC ファイルのパス

 ⇓
Step 2. Connect to the source database (2): 接続を確認して次へ
 正常に接続出来たら ボタンをクリックする。
 ⇓
SSL を使用しないことによる警告
  ボタンをクリックする。
 SSL の安全な通信を推奨している。
 今回は自ホストなので関係がない。
 ⇓
Step 3. Choose a schema: データベースを選択する。
 データベースを選択して ボタンをクリックする。
 Databases [N] を選択することもできるが、調査時間が非常に長くなる。

 ⇓
 待つ。
 ⇓
Step 4. Run the database migration assesment: 環境調査の表示
 調査結果を確認して ボタンをクリックする。
 今回の目的であるサンプル T-SQL → Babelfish の変換が良好となっている。
 • Storage objects: スキーマやテーブル構造等
 • Code objects: ストアードプロシージャ等
 • Conversion actions: 変換に必要な作業の数

 このドキュメントは CSV や PDF で出力することも可能。

 Babelfish の T-SQL のサポート状況は以下で公表されている。
 • バージョンごとに Babelfish でサポートされている機能
 • Babelfish でサポートされていない機能
 ⇓
Step 5. Choose a target: 変換先の選択。
 変換先データベース製品を選択して ボタンをクリックする。
 今回は Babelfish を選択する。
 ⇓
変換元データベースのスキーマ一覧
 変換元データベースを右クリックして  Save as SQL  を選択する。

 MS SQL Server → Babelfish の場合は T-SQL 同士の SQL となり、
 たとえ変換が必要となっても Babelfish が機能を有しないので Convert が意味をなさないと考えられる。
 その時は、Babelfish のさらなる進化を待つか、プログラムの手直しが必要になるはず。
ここで Convert schema を選択するとエラーになる。
 MS SQL Server → Babelfish の場合は、必ずエラーになる。
 ⇓
DDL ファイルパスの指定。
 保存するファイルのディレクトリとファイル名を指定して ボタンをクリックする。
 今回は  道具_DDL.sql  として保存する。
 ⇓
拡張子 .sql が割り当てられているせいで SSMS で開こうとするが、不要なので拒否する。

 ↓

→

 ⇓
DDL ファイルの存在を確認

 道具_DDL.sql が作成されている。

3-2. DDL の変更

この DDL には漢字が含まれている (そう作ったんだから当たり前) ので、そのままでは使用できない。
識別子に使用されている漢字を ASCII 文字へ変更する必要がある。
(クライアントアプリを変更できない場合は、移行を諦める)

リファクタリングに向けて二つのスクリプトを作成した。
1. remove_comment.py(UTF8, LF): コメント除去用
2.expose_mb.py(UTF8, LF):多バイト文字検出用
これは、多バイト文字が使用されている識別子を検出するためのスクリプト。
自動置換はできたとしても信用できないのが常なので、手動置換をするための補助を目的とする。
(SQL の自動リファクタリングや衝突検出の便利機能は、商用ソフトを利用するしかない)

使用方法は以下。
行番号と識別子を表示するので、これを頼りに手動リファクタリングを実施する。
• Linux の場合

# モジュール sqlparse のインストール
$ python -m pip install --upgrade pip
$ pip install sqlparse


# 実行
$ cat 道具_DDL.sql | ./remove_comment.py | ./expose_mb.py | head
実行結果 (行頭の数字は入力ファイルの行番号)
6: [道具]
9: [筆箱],[FK_筆箱_文房具]
21: [道具]
25: [文房具]
32: [筆箱]
46: [道具]
49: [文房具]
51: [名前]
52: [数]
58: [筆箱]

• PoorShell の場合
 パイプ処理が非常に難解なので WSL を使ったほうが良い。
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # モジュール sqlparse のインストール
PS C:\> python -m pip install --upgrade pip ⏎
PS C:\> pip install sqlparse ⏎



PS C:\> # 実行 (PoorShell は head ができない)
PS C:\> $OutputEncoding = [System.Text.Encoding]::UTF8 ⏎ 
PS C:\> $env:PYTHONIOENCODING = "utf-8" ⏎ 
PS C:\> [System.IO.File]::ReadAllText("道具_DDL.sql",[System.Text.Encoding]::UTF8) |
 python .\remove_comment.py | python .\expose_mb.py ⏎
6: [道具]
9: [筆箱],[FK_筆箱_文房具]
21: [道具]
25: [文房具]
32: [筆箱]
46: [道具]
49: [文房具]
51: [名前]
52: [数]
58: [筆箱]
60: [文房具ID]
74: [道具]
77: [文房具]
82: [筆箱]
95: [道具]
98: [筆箱]
99: [FK_筆箱_文房具],[文房具ID]
100: [文房具]

PS C:\> exit ⏎

手動リファクタリングなので、変更前と変更後の文字列の一覧を作成しておくこと。
今回はこのようにした。
識別子名変換一覧
変更前変更後テーブル名
道具 → dbDougu·
筆箱 → tbFudebako✓
FK_筆箱_文房具 → FK_tbFudebako_tbBunbogu·
文房具 → tbBunbogu✓
名前 → Namae·
数 → Kazu·
文房具ID → BunboguID·
衝突に注意
この例では可読性を高めるために大小文字を使用しているが、
ほとんどの SQL では識別子名の大小文字を区別しない。(Kazu = kAzu)
大小文字を区別するときはダブルクォートで囲むこと。("Kazu" ≠ "kAzu")
3-3. スキーマの適用
3-3-1. データベースの確認と作成
道具_DDL.sql は漢字 → ASCII 変更後のファイル
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # データベース名を確認する
PS C:\> Get-Content -Encoding utf8 .\道具_DDL.sql | Select-String "USE " | Get-Unique ⏎

USE [dbDougu]     ← これ



PS C:\> # データベースを作成する
PS C:\> sqlcmd -S 192.168.11.11,1433 -U babelfish_user -P "12345678" -d master -Q "CREATE DATABASE dbDougu" ⏎ 

PS C:\> exit ⏎
異なる名称のデータベースを作成する場合は、
道具_DDL.sql に記述されている USE [] をすべて変更してから実施する。
3-3-2. DDL の適用
接続するデータベースに注意 (-d dbDougu)
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # スキーマ・テーブルをを作成する
PS C:\> Get-Content -Encoding utf8 .\道具_DDL.sql |
 sqlcmd -S 192.168.11.11,1433 -U babelfish_user -P "12345678" -d dbDougu ⏎ 

PS C:\> exit ⏎

4. データ用 CSV ファイルの作成

bcp コマンドはひとつのテーブルを対象としており、一括で取得・投入することができない。
このため、テーブルをひとつずつ個別に処理する。

4-1. データベースからテーブル名を取得する。
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # テーブル名の一覧を取得する
PS C:\> @"
SET NOCOUNT ON;
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME
"@ | sqlcmd -S localhost,1433 -U sa -P "Password123" -d 道具 -h -1 -W `
| ForEach-Object {$_ -replace '\[|\]', ''} > tables.txt ⏎ 

PS C:\> exit ⏎
4-2. テーブルからデータを取り出す
データを格納するファイル名は「テーブル名.csv」とする。(例: dbo.table1.csv)
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # テーブルのデータを CSV ファイルに取り出す
PS C:\> mkdir -Force .\csv\
PS C:\> type tables.txt | ForEach-Object {
  $table = $_.Trim()
  if ($table -ne "") {
    Write-Output "-- Export $table"
    bcp "道具.$table" out .\csv\$table.csv -S localhost,1433 -U sa -P `"Password123`" -c -t ',' 
  }
} ⏎ 

PS C:\> exit ⏎
4-3. テーブル名変更 (手動リファクタリングの続き)
上記 3-2 でテーブル名に多バイト文字を使用していた場合は、書き留めた識別子名変換一覧に従って、
保存したファイル名 (テーブル名.csv) とファイル名一覧 (tables.txt) の内容を変更する。

変更前変更後
dbo.筆箱.csv → dbo.tbFudebako.csv
dbo.文房具.csv → dbo.tbBunbogu.csv
4-4. データ投入順の入れ替え
制約がある場合は、参照されるテーブルのデータを先に投入するよう、tables.txt の処理順を入れ替える。

制約は以下の SQL で調査できる。
sqlcmd -S localhost,1433 -U sa -P "Password123" -d 道具 -h -1 -W -Q "
  SET NOCOUNT ON;
  SELECT
    fk.name AS ForeignKeyName,
    OBJECT_NAME(fk.parent_object_id) AS TableName,
    OBJECT_NAME(fk.referenced_object_id) AS ReferencedTableName
  FROM
    sys.foreign_keys AS fk;"

FK_筆箱_文房具 筆箱 文房具

本稿のサンプルにおいては「FK_筆箱_文房具」(FK_tbFudebako_tbBunbogu) の定義により、
「文房具」(tbBunbogu) のデータを先に投入する。
tables.txt
dbo.tbBunbogu.csv   ← こっちが先 
dbo.tbFudebako.csv
  …
4-5. CSV ファイルから移行先テーブルへデータをコピーする
− □ × 
 >_ Windows PowerShell ×   + | ∨
 
Windows PowerShell
Copyright (C) Microsoft Corporation. All rights reserved.

新機能と改善のために最新の PowerShell をインストールしてください!https://aka.ms/PSWindows  

PS C:\> # CSV ファイルからテーブルにデータをセットする
PS C:\> type tables.txt | ForEach-Object {
  $dbName = 'dbDougu'
  $table = $_.Trim()
  if ($table -ne "") {
    Write-Output "-- Import $table"
    bcp "$dbName.$table" in .\csv\$table.csv -S 192.168.11.11,1433 -U babelfish_user -P `"12345678`" -c -t ',' 
  }
} ⏎ 

PS C:\> exit ⏎