この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
はじめに
好物はインフラとフロントエンドのかじわらゆたかです。
このエントリは『Luigi Advent Calendar 2016』12日目の内容となります。
今回はLinux対応のあったSQLServerからのデータ習得を行なってみたいと思います。
先日12日目は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ファイルのシェルを配置します。
Dockerfile
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のときとほとんど一緒です。
一部使うメソッド等使うモジュール毎に異なっている点はありますが、
既に実装してあるコードと差分が少ないことがわかります。
useSQLServer.py
# -*- 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 男
まとめ
今回はSQL Serverに対してデータ習得してみました。
次回からはRedshiftへのアクセス等を調べていきたいと思います。