主キーのないcsvデータを主キーを付けてSQL Serverにインポート #Ubuntu

主キーのないcsvデータはたくさんある。
例えば郵便番号のCSVデータなんてその典型。
郵便番号がunique(一意)だと思っている人も多い。
京都の場合は一つの郵便番号で複数の住所が引っ掛かってしまう。
嘘だと思うんだったら、郵便番号 6040905で郵便番号検索してみるといい。

郵便番号のCSVはBigDataすぎるのでごく小さなデータでやってみようと思う。

下記のようなデータだ。

listを表示

$ cat list.csv
Yusuke
Yusuke
Yusuke

これを下記のように格納しようと思う。

ID	Name
0	Yusuke
1	Yusuke
2	Yusuke

環境

Client

Ubuntu 16.04
Microsoft SQL Server 用 JDBC Driver 6.0
Sqlcmd
Embulk v0.8.18

Server

Windows Server 2012R2
SQL Server 2016 Express

以下の設定は済んでいるものとする

SQL Server JDBCドライバーのダウンロードと展開
sqlcmdのubuntuへのインストール
UbuntuへのJavaのインストール
Embulkのインストール
SQL Serverへの接続設定

方法

ID付きのテーブルを作成する。

ubuntu上からsqlcmdでTable作成

$ sqlcmd -S 192.168.0.3 -U sa -P Password
1> use test1
2> go
データベース コンテキストが 'test1' に変更されました。
1> CREATE TABLE list(
2>         ID INT not null IDENTITY(1,1) PRIMARY KEY
3>         ,Name nvarchar(50)
4> )
5> go
1> select * from list
2> go
ID          Name
----------- --------------------------------------------------

(0 rows affected)
1>

seed.ymlを作成
Embulkではカラム名がない場合は、c0,c1というようなカラム名を勝手につける。
これをTableに存在するカラム名にリネームする。

seed.yml

in:
  type: file
  path_prefix: "/home/ubuntu/list.csv"
  parser:
   type: csv
   charset: utf-8
   newline: LF
   delimiter: ','
   quote: '"'
   escape: '\'
#filterでカラム名を変更する。
filters:
 - type: rename
   columns:
      c0: Name
out:
  type: sqlserver
  #jdbcドライバーの場所
  driver_path: /home/ubuntu/mssql/sqljdbc_6.0/jpn/jre8/sqljdbc42.jar
  host: 192.168.0.3
  user: sa
  password: Password
  database: test1
  table: list
  mode: insert
  #in側のcolumnsでcolumn名がNameになっており、下記のcolumns_optionsは無くても動く
  #column_options:
  #   Name: {value_type: string}

seed.ymlを元にconfig.ymlを生成する。
下記のようにembulkを走らせる。

config.ymlを作成

$ embulk guess seed.yml -o config.yml
2017-04-26 00:51:31.835 +0000: Embulk v0.8.18
2017-04-26 00:51:33.933 +0000 [INFO] (0001:guess): Listing local files at directory '/home/ubuntu' filtering filename by prefix 'list.csv'
2017-04-26 00:51:33.947 +0000 [INFO] (0001:guess): Loading files [/home/ubuntu/list.csv]
...(省略)...

出来上がったconfig.ymlは下記のようになる。

config.yml

in:
  type: file
  path_prefix: /home/ubuntu/list.csv
  parser:
    type: csv
    charset: utf-8
    newline: LF
    delimiter: ','
    quote: '"'
    escape: \
    trim_if_not_quoted: false
    skip_header_lines: 0
    allow_extra_columns: false
    allow_optional_columns: false
    columns:
    - {name: c0, type: string}
filters:
- type: rename
  columns: {c0: Name}
out:
  type: sqlserver
  driver_path: /home/ubuntu/mssql/sqljdbc_6.0/jpn/jre8/sqljdbc42.jar
  host: 192.168.0.3
  user: sa
  password: Password
  database: test1
  table: list
  mode: insert

config.ymlの設定でcsvをインポート

run

~$ embulk run config.yml
2017-04-26 00:53:40.746 +0000: Embulk v0.8.18
2017-04-26 00:53:42.684 +0000 [INFO] (0001:transaction): Loaded plugin embulk-output-sqlserver (0.7.7)
...(省略)... 
2017-04-26 00:53:45.771 +0000 [INFO] (main): Committed.
2017-04-26 00:53:45.777 +0000 [INFO] (main): Next config diff: {"in":{"last_path":"/home/ubuntu/list.csv"},"out":{}}

これで難なくインポート完了。
bcpコマンドより遥かに簡単ですね。

Ubuntuのsqlcmdを使ってSQL Serverのデータを確認してみましょう。

データ確認

$ sqlcmd -S 192.168.0.3 -U sa -P Password
1> use test1
2> go
データベース コンテキストが 'test1' に変更されました。
1> select * from list
2> go
ID          Name
----------- --------------------------------------------------
          1 Yusuke
          2 Yusuke
          3 Yusuke

(3 rows affected)

できているね。

参考

Embulk csv-parser-plugin
Embulk rename-filter-plugin
Embulk-output-sqlserver
Microsoft SQL Server 用 JDBC Driver 6.0