BCPコマンドの備忘録となります。今回はSQL Serverのを立てBCPコマンドを使ってテーブル間の情報をコピーできればと思います。
BCPとは
SQL Server のETLツールです。SQL Server間のコピーやクエリを実行してファイルに出力することができます。
Embulkよりも早いという話も聞きますがパフォーマンス面はどこかで計測、比較してみたいと思います。
環境
SQL Serverのを立てて事前にスキーマを定義します。
docker-compose up -d --build
docker-compose.yml
version: '3' services: sql-server: image: mcr.microsoft.com/mssql/server:2017-latest tty: true container_name: sql-server ports: - 1433:1433 environment: - ACCEPT_EULA=Y - SA_PASSWORD='password' volumes: - .://usr/src/app working_dir: /usr/src/app/ sql-server-tool: image: mcr.microsoft.com/mssql-tools tty: true container_name: sqlserver-tool volumes: - .://usr/src/app working_dir: /usr/src/app/
起動したコンテナを確認
docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 4b06dd803bd1 mcr.microsoft.com/mssql-tools "/bin/sh -c /bin/bash" 5 hours ago Up 5 hours sqlserver-tool 401d988a066a mcr.microsoft.com/mssql/server:2017-latest "/opt/mssql/bin/nonr…" 5 hours ago Up 5 hours 0.0.0.0:1433->1433/tcp sql-server
sqlserver-toolコンテナにログイン
docker exec -it sqlserver-tool bash root@4b06dd803bd1:/usr/src/app#
sqlserver-toolコンテナからsql-serverコンテナに接続
sqlcmd -S 'sql-server' -U 'SA' -P 'password'
データベースとテーブルを作ります。
CREATE DATABASE database; GO USE database; GO CREATE TABLE [table]([ID] [int] NOT NULL) GO INSERT INTO table (ID)VALUES (1); GO
これで事前準備は完了です。
BCPプラクティス
今回は別環境のSQL ServerのテーブルデータをBCP を使ってコンテナにコピーしてみます。
変数を定義
IMPORT_TABLE_NAME='' DBBACKBORN_HOST='' DBBACKBORN_PORT='' DBBACKBORN_DATABASE='' DBBACKBORN_USER='' DBBACKBORN_PASSWORD='' DBMARKE_HOST='' DBMARKE_PORT='' DBMARKE_DATABASE='' DBMARKE_USER='SA' DBMARKE_PASSWORD='' batch_size=1000
BCP OUT
クエリを実行してdatファイルに書き込みます。
QUERY='select top 10 * from database.dbo.table' bcp "$QUERY" queryout $IMPORT_TABLE_NAME.dat -N -S $DBBACKBORN_HOST,$DBBACKBORN_PORT -d $DBBACKBORN_DATABASE -U $DBBACKBORN_USER -P $(eval echo $DBBACKBORN_PASSWORD) | grep -vE '^1000 rows .*[1-9]000$' | grep -vE '^1000 rows .*[1-9]0000$' | grep -vE '^1000 rows .*[1-9]00000$' | tee bcp_out.txt
TRUNCATE
BCP INを実行する上でTRUNCATE分でテーブルの中身を削除します。
$sqlcmd -S $DBMARKE_HOST,$DBMARKE_PORT -d $DBMARKE_DATABASE -U $DBMARKE_USER -P $DBMARKE_PASSWORD -Q "TRUNCATE TABLE $IMPORT_TABLE_NAME"
BCP OUT
出力したBCPの情報を別テーブルに書き込みます。
bcp $IMPORT_TABLE_NAME in $IMPORT_TABLE_NAME.dat -E -N -S $DBMARKE_HOST,$DBMARKE_PORT -d $DBMARKE_DATABASE -U $DBMARKE_USER -P $DBMARKE_PASSWORD -b $batch_size -h TABLOCK | grep -vE '^10000 rows .*[1-9]0000$' | grep -vE '^10000 rows .*[1-9]00000$'
試してみる
#!/bin/bash QUERY='select top 10 * from database.dbo.table' IMPORT_TABLE_NAME='table_name' DBBACKBORN_HOST='' DBBACKBORN_PORT='' DBBACKBORN_DATABASE='' DBBACKBORN_USER='' DBBACKBORN_PASSWORD='' DBMARKE_HOST='sql-server' DBMARKE_PORT='1433' DBMARKE_DATABASE='' DBMARKE_USER='SA' DBMARKE_PASSWORD='' batch_size=1000 bcp "$QUERY" queryout $IMPORT_TABLE_NAME.dat -N -S $DBBACKBORN_HOST,$DBBACKBORN_PORT -d $DBBACKBORN_DATABASE -U $DBBACKBORN_USER -P $(eval echo $DBBACKBORN_PASSWORD) | grep -vE '^1000 rows .*[1-9]000$' | grep -vE '^1000 rows .*[1-9]0000$' | grep -vE '^1000 rows .*[1-9]00000$' | tee bcp_out.txt if grep -q 'Error' bcp_out.txt ; then exit 1; fi sqlcmd -S $DBMARKE_HOST,$DBMARKE_PORT -d $DBMARKE_DATABASE -U $DBMARKE_USER -P $DBMARKE_PASSWORD -Q "TRUNCATE TABLE $IMPORT_TABLE_NAME" bcp $IMPORT_TABLE_NAME in $IMPORT_TABLE_NAME.dat -E -N -S $DBMARKE_HOST,$DBMARKE_PORT -d $DBMARKE_DATABASE -U $DBMARKE_USER -P $DBMARKE_PASSWORD -b $batch_size -h TABLOCK | grep -vE '^10000 rows .*[1-9]0000$' | grep -vE '^10000 rows .*[1-9]00000$' | tee bcp_in.txt if grep -q 'Error' bcp_in.txt ; then exit 1; fi cat bcp_in.txt
ファイルを実行して挙動を確認してみます。
bash bcp.sh Starting copy... 10 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 20 Average : (500.0 rows per sec.) bcp.sh: line 22: -S: command not found Starting copy... 10 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 13 Average : (923.1 rows per sec.)
