SQLServerからのデータ取得してみた | Luigi Advent Calendar 2016 #12

ジョブ管理ツール

#Luigi

#SQL Server

梶原裕

2016.12.12

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

好物はインフラとフロントエンドのかじわらゆたかです。

このエントリは『Luigi Advent Calendar 2016』12日目の内容となります。
今回はLinux対応のあったSQLServerからのデータ習得を行なってみたいと思います。

先日１２日目はMySQLからデータ取得してみたでした。

下準備 sql-cli

SQL Serverに接続するクライアントとしては、SQL Server Management Studioが有名ではありますが、
テストのみをするのであれば、CLIでサクッとやりたいものです。
今回導入するsql-cliはNode.js製のSQL Serverのコマンドラインツールになります。

Node.jsは4.6以上のバージョンで動くとのことですが、ついでなのでnodebrewから V7.2.0を導入して実行してみました。

$ nodebrew install-binary v7.2.0
$ nodebrew user v7.2.0
$ node -v
v7.2.0
$ npm init
$ npm install sql-cli
$ mssql --version
0.4.11

npm で globalインストールせずにパスが通っている件ですが、以下のサイトを参考にnode_module/.bin　をパスに含むようにしています。
このようにすることでglobalの環境を汚さずに実装等をすすめることが可能です。

コマンドパスを自動で通し npm install -g しない - Qiita

下準備 SQL Server Docker image on Linux

Dockerイメージが公開されているのでそれを起動すれば起動はすぐですが、テストデータ用の取り込み等も行いたいので一手間書けるとします。

SQLServerのテストデータはAdventureWorksというのがCodeplexで公開されています。
今回はその中の軽量版を取り込んだDockerImageを作成し、そのテストデータをアタッチすることで環境構築していきます。

下記のサイトより、軽量版のテストデータであるAdventureWorksLT2012_Database.zipをダウンロードし、作業ディレクトリに配置します。 Microsoft SQL Server Product Samples: Database - Download: AdventureWorks Databases – 2012, 2008R2 and 2008

作業ディレクトリに以下のDockerファイルのシェルを配置します。

    FROM microsoft/mssql-server-linux
    MAINTAINER "KAJIWARA Yutaka" <chiba@example.com>

    RUN mkdir -p /var/opt/mssql/data/adv
    COPY ./AdventureWorksLT2012_Database/AdventureWorksLT2012_Data.mdf /var/opt/mssql/data/adv/
    COPY ./AdventureWorksLT2012_Database/AdventureWorksLT2012_log.ldf /var/opt/mssql/data/adv/

    #!/bin/bash
    file=AdventureWorksLT2012_Database.zip
    if [ -e $file ]; then
        echo "$file found."
    else
        echo "$file NOT found."
        exit 1
    fi
    unzip AdventureWorksLT2012_Database.zip -d ./AdventureWorksLT2012_Database
    docker build -t sqlserver_include_sample .
    docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@55w0rd'  -p  1433:1433 -d --name mssql sqlserver_include_sample

配置したシェルを起動します。なお、その際にDockerには4G以上のメモリを割り当てておきます。
これはSQlServer起動時に割り当てが4G以下の場合メモリ不足で起動しないためです。
```
    $ sh ./run.sh
    
```

配置したテスト用のデータをアタッチします。

    $ mssql -s localhost -o 1433 -u sa -p P@55w0rd -q "CREATE DATABASE MyAdventureWorks ON (FILENAME = '/var/opt/mssql/data/adv/AdventureWorksLT2012_Data.mdf'), (FILENAME = '/var/opt/mssql/data/adv/AdventureWorksLT2012_log.ldf') FOR ATTACH;"

アタッチが成功していれば以下のクエリを流すとサンプルデータの値が取得できるはずです。
データベース一覧習得

    $ mssql -s localhost -o 1433 -u sa -p P@55w0rd
    mssql> .databases
    name
    ----------------
    master
    tempdb
    model
    msdb
    MyAdventureWorks

    5 row(s) returned

テーブル一覧習得

    $ mssql -s localhost -o 1433 -u sa -p P@55w0rd
    mssql> use MyAdventureWorks
    OK
    mssql> .tables
    database          schema   name                             type
    ----------------  -------  -------------------------------  ----------
    MyAdventureWorks  SalesLT  Customer                         BASE TABLE
    (省略)

テーブル参照

    $ mssql -s localhost -o 1433 -u sa -p P@55w0rd
    mssql> use MyAdventureWorks
    mssql> select CustomerID,FirstName,MiddleName,LastName from MyAdventureWorks.SalesLT.Customer ORDER BY CustomerID  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
    CustomerID  FirstName  MiddleName  LastName
    ----------  ---------  ----------  ----------
    1           Orlando    N.          Gee
    2           Keith      null        Harris
    3           Donna      F.          Carreras
    4           Janet      M.          Gates
    5           Lucy       null        Harrington
    6           Rosmarie   J.          Carroll
    7           Dominic    P.          Gash
    10          Kathleen   M.          Garza
    11          Katherine  null        Harding
    12          Johnny     A.          Caprio

下準備 pymsql

MySQLのときと同様PythonのSQL Server用モジュールを入れる必要があります。
PythonのSQL Serverの実装はpymssqlというようですが、
これはFreeTDSの上に構築されているとのことです。
Introduction — pymssql 2.2.0.dev documentation そのため導入にはまずFreeTDSを導入し、その後pipコマンドでの導入となります。

$ brew install freetds
$ pip install git+https://github.com/pymssql/pymssql.git
$ pip list
(省略)
pymssql (2.2.0.dev0)
(省略)

SQLServerからのデータ習得

ここまで来てしまえば、あとの実装はMySQLのときとほとんど一緒です。
一部使うメソッド等使うモジュール毎に異なっている点はありますが、
既に実装してあるコードと差分が少ないことがわかります。

# -*- coding: utf-8 -*-
from logging import getLogger, StreamHandler, DEBUG

import luigi
import luigi.contrib.mssqldb
logger = getLogger(__name__)
handler = StreamHandler()
handler.setLevel(DEBUG)
logger.setLevel(DEBUG)
logger.addHandler(handler)


class extractSQLServerTable(luigi.Task):
    def run(self):
        msSqlTarget = luigi.contrib.mssqldb.MSSqlTarget(
            host="localhost:1433",
            database="MyAdventureWorks",
            password="P@55w0rd",
            user="sa",
            table="Customer",
            update_id="sample")
        connection = msSqlTarget.connect()
        connection.execute_scalar(
            "SELECT CustomerID, FirstName, LastName, (CASE WHEN title = 'Mr.' THEN N'男' WHEN title = 'Ms.' THEN N'女' ELSE '-' END) AS 'Gender' FROM SalesLT.customer ORDER BY CustomerID  OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;"
        )
        with self.output().open('w') as out_file:
            for row in connection:
                out_file.write(
                    u"{CustomerID}\t{FirstName}\t{LastName}\t{Gender}\n".
                    format(**row))

    def output(run):
        return luigi.LocalTarget(
            path="employees.tsv", format=luigi.format.UTF8)


if __name__ == '__main__':
    luigi.run()

実装している中でハマった点としては、クエリのパラメータとしてUnicodeの文字列を使う点にハマりました。
今回の作例の場合ですと、titleの値によって男・女と出している点なのですが、
これは文字列の手前にNと付与することで対応しています。

SQL ServerにUnicodeの文字列を格納する方法 - 大人になったら肺呼吸

実行結果

$ python ./useSQLServer.py --local-scheduler extractSQLServerTable 
$ cat ./employees.tsv
2	Keith	Harris	男
3	Donna	Carreras	女
4	Janet	Gates	女
5	Lucy	Harrington	男
6	Rosmarie	Carroll	女
7	Dominic	Gash	男
10	Kathleen	Garza	女
11	Katherine	Harding	女
12	Johnny	Caprio	男