case-kの備忘録

日々の備忘録です。データ分析とか基盤系に興味あります。

SQL Server BCPプラクティス

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 [dbo].[table]([ID] [int] NOT NULL)
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.)