Ubuntu と SQL Server の連携

Microsoft SQL Server を利用している場合、 Web サーバも Microsoft の Windows Server を利用しているプロジェクトが多いでしょう。
しかし弊社では他社から引き継いだシステムの都合上、 Web サーバが Ubuntu で DB サーバが SQL Server のシステムがあります。そして今回業務の都合上、定期的に本番用 DB サーバからデータを取得し、データの加工を行った後に開発用 DB サーバを更新する処理が必要になりました。
当初はデータ量がそこまで多くなかったため手作業で行っていたのですが、本番 DB サーバのデータを取得したとき 2GB を超えるようになり、一括で開発用 DB サーバを更新できなくなってしまいました。（sqlcmd を利用したファイルによるデータ更新は、 2GB 以下でなければ読み込むことができません。）そのため、本番用 DB サーバからの取得データは、テーブル毎に分割し 2GB 以下に抑える必要があります。しかしテーブル数が多く手作業が困難になったため、シェルによる更新を行うことにしました。
今回は Ubuntu から SQL Server にあるデータの取得および更新方法について、 Bash からの利用方法を紹介したいと思います。

開発環境

• AWS EC2 Ubuntu 18.04
• Python 3.8
• AWS RDS SQL Server 2017
• (既に sqlcmd が利用できる状態であることを想定)

SQL Server からのデータ取得

早速ですが、データ取得方法についてみてみましょう。
データ取得には mssql-scripter を利用します。
https://pypi.org/project/mssql-scripter/
次のコマンドでインストールしましょう。

pip install mssql-scripter

実行は次のようになります。

python3 -m mssqlscripter -S $SERVER -U $USER -P $PASSWORD -d $DATABASE -f $WORK_DIR --file-per-object --data-only --include-objects $OBJECTS_STR

mssqlscripter には多くのオプションが用意されていますが、今回利用したのは
　file-per-object … テーブル毎にファイルを分ける
　data-only … データのみ取得

SQL Server のデータ更新

次にデータの更新方法を確認します。
データ更新には sqlcmd を利用します。通常であればシステム構築時に sqlcmd がインストールされていると思います。
1ファイルずつ実行するには次のようなコマンドになります。今回の場合テーブル分のファイルがあるため、ループの中で実行しています。

sqlcmd -S $SERVER -U $USER -P $PASSWORD -i $FILE

実際のスクリプト

上記を利用して実行しているスクリプトは次のようになっています。

#!/bin/bash

WORK_DIR=/home/ubuntu/xxxxx

#取得元(本番サーバ)
FROM_SERVER=xxxxx.zzzzz.ap-northeast-1.rds.amazonaws.com
FROM_USER=user_name
FROM_PASSWORD=password
FROM_DATABASE=database_name
OBJECTS=(table_name_1 table_name_2 table_name_3)

#更新先(開発サーバ)
TO_SERVER=yyyyy.zzzzz.ap-northeast-1.rds.amazonaws.com
TO_USER=user_name
TO_PASSWORD=password
TO_DATABASE=database_name

#データ取得対象文字列生成
OBJECTS_STR=""
for OBJECT in "${OBJECTS[@]}"; do
  OBJECTS_STR+=" ${OBJECT}"
done

#データ取得
echo `date +%Y-%m-%d_%H:%M`" データ取得中"
python3 -m mssqlscripter -S $FROM_SERVER -U $FROM_USER -P $FROM_PASSWORD -d $FROM_DATABASE -f $WORK_DIR --file-per-object --data-only --include-objects $OBJECTS_STR

#テーブル分ループ
for OBJECT in "${OBJECTS[@]}"; do
  echo `date +%Y-%m-%d_%H:%M`" ${OBJECT} データ更新中"

  FILE="$WORK_DIR/dbo.${OBJECT}.Table.sql"

  if [[ "$OBJECT" == "table_name_x" ]]; then
    # テーブル毎の処理
  fi

  #データ更新
  sqlcmd -S $TO_SERVER -U $TO_USER -P $TO_PASSWORD -i $FILE | sed -e "s/(1 rows affected)//g" | sed -e "/^$/d"
done

#開発用にデータ更新
sqlcmd -S $TO_SERVER -U $TO_USER -P $TO_PASSWORD -i change_db.sql | sed -e "s/(1 rows affected)//g" | sed -e "/^$/d"

echo `date +%Y-%m-%d_%H:%M`" データ更新完了"

26行目で mssqlscripter を利用したデータ取得、39行目で sqlcmd を利用したデータ更新を行っています。テーブル毎に異なった処理を行う場合は、35行目に記述しておけばいいでしょう。また全体的に実行したいクエリ分は 43行目で実行するようにしています。
もちろん運用しているスクリプトでは、テーブル毎の処理のほか共通で実行する処理などありますのでもっと複雑になっています。

まとめ

Ubuntu から SQL Server にあるデータの取得および更新方法について、 Bash からの利用方法について紹介しました。
今回データが増えたことにより自動化を行いましたが、こんなに楽になれならもっと早くにしておくべきだったと思いました。ぜひ活用してみてください。