PowerShell で操作する SQL Server クックブック（bash から PowerShell へ）

Article
11/14/2016

Microsoft Japan Data Platform Tech Sales Team

西村哲徳

こんにちわ。今回は Linux ユーザ向けに PowerShell を使って SQL Server にバッチ的に SQL 文や管理コマンドを実行する方法を紹介します。今までデータベースを Linux 上で使ってきたエンジニアにとって SQL Server を使う上で壁となるのは互換性の高い SQL よりも慣れ親しんだ bash が使えないということでしょう。 Cygwin や Bash on Ubuntu on Windows（現時点ではWindows 10のみでプレビューという扱い）を使うことはできますが、PowerShell は思ったより bash ユーザにもなじみやすい構文なのでこの機会にぜひ覚えてみてください。もちろん PowerShell ならではの高度なことは可能ですが今回は bash を対象にしたシンプルな構文のみにできるだけフォーカスします。

文法の相違を細かくあげてもキリがないのと面白くないのでよく使われそうなケースを想定して bash から PowerShell へ移行してみたいと思います。例えば製品の性能評価などで何本かSQLを作りまとめて実行したい場合を想定して下記のようなスクリプトを作ってみたいと思います。

「ディレクトリ内の拡張子 sql のファイルを3回づつ実行し別々のログファイルに結果と実行時間を出力」

ログを出力するディレクトリの存在チェックをしなければ作成
接続サーバ名、ユーザ、パスワード、DB 名は引数として指定（エラーチェックは省略）
ログファイル名は SQL ファイル名_回数_YYYYMMDDHH24MISS

作業にあたって、本記事では sqlcmd を使用するのでまだ触ったことがない人は「SQLCMD の使い方」を参考にしてください。また、エディタは何でもいいですが、特にこだわりがなければ標準でインストール済みの補完やデバッグなどが便利な「Windows PowerShell ISE」をご利用ください。

bash

 
#!/bin/bash 
# 実行方法: sqltest.sh <servername> <username> <password> <dbname>

#引数の取得と変数の定義
SERVER=${1} 
USER=${2} 
PASS=${3} 
DBNAME=${4} 
COUNT=3 
EXT=sql 
DATETAG=`date '+%Y%m%d%H%M%S'` 
LOGDIR="./log" 

#ログディレクトリの存在を確認してなければ作成する 
if [ ! -e ${LOGDIR} ]; then 
  echo "${LOGDIR} does not exists, so make it!" 
  mkdir -p ${LOGDIR} 
fi 

# 拡張子がsqlのファイルをlsで取得し、そのファイル数分ループを実行 
for FILENAME in `ls -1 *.${EXT}` 
  do 
# ファイル名から拡張子を除去 
  PREFIX=`basename ${FILENAME} .${EXT}` 
  # 3回同じsqlファイルを実行する 
  for i in `seq 1 ${COUNT}` 
    do 
    LOGFILE=${LOGDIR}/${PREFIX}_${i}_${DATETAG}.log 
    #ヒアドキュメントでsqlcmdでsqlファイルを実行 
    sqlcmd -S ${SERVER} -U ${USER} -P ${PASS} -d ${DBNAME} -o ${LOGFILE} <<EOF
    set statistics time on 
    :r ${FILENAME} 
    go 
EOF 
  done 
done

bash に関しては既に慣れ親しんでると思いますので特に説明はしません。続いて PowerShell に移行したスクリプトを見てみます。説明の都合上分けて書いてますがまとめて１つのスクリプトにして実行できます。また、PowerShell ならではの機能を使えばより効率的にかけますが今回は Linux ユーザが簡単になじめることを目的としていますのでできるだけ bash に近い構文にしています。

PowreShell

 
#実行方法：sqltest.ps1 <servername> <username> <password> <dbname> 
#引数の取得と変数の定義 

#パラメータとなる変数の定義
param($S,$U,$P,$d)  
${SERVER}=$S 
${USER}=$U 
${PASS}=$P 
${DBNAME}=$d 
${COUNT}=3 
${EXT}="sql" 
${DATETAG}=date -format "yyyyMMddHHmmss" 
${LOGDIR}=".\log"

PowerShell の変数は代入、参照に関わらず $ をつけますが Linux でよく使われる perl や php と同じなので違和感は特にないとおもいます。 date コマンドもオプション指定方法は違いますが PowerShell でも使えます。
引数の値が入る変数は PowerShell では明示的に PARAM([変数名][,変数名]*)で定義する必要があります。上記の方法で実行する場合、変数名に意味はなく順序通りに値が入ります。ちなみに、sqltest.ps1 -U <username> -S <servername> -P <password> -d <dbname>とハイフンに変数名を指定して実行すると変数名に指定した値が入ります。この例では $U に<username> $S に<servername> $P に<password> $d に<dbname>が入ります。

続いてログディレクトリの作成です。

 
#ログディレクトリの存在を確認してなければ作成する
if ( -not (Test-Path -Path ${LOGDIR}) ) {
  echo "${LOGDIR} does not exists, so make it!"
  mkdir -p ${LOGDIR}
}

ディレクトリの存在チェックは"-e"はないので、独自のコマンド（コマンドレット）Test-Path を使用します。また否定演算も"!"ではなく –not になります。これらは覚えるしかないですがわかりやすいので使っているうちに慣れると思います。if を使った制御分は bash とは違いますが、php や perl とほぼ同じなので一度は使ったことのある書き方ではないでしょうか。mkdir は linux のコマンドがそのまま使えます。

参考までに if 文の書き方を紹介しておきます。

 
if 文の書式
if (条件式1) 
  { 処理1 } 
elseif(条件式2)
  { 処理2 }
else 
  { 処理3}

続いて、本題のディレクトリ内の sql ファイルを実行する部分です。

 
# 拡張子が sql のファイルを ls で取得し、そのファイル数分ループを実行
foreach (${FILE} in  (ls *.${EXT}) ) 
{
  ${PREFIX}= ${FILE}.basename
  for (${i}=0; ${i} -lt ${COUNT}; ${i}++) {
    ${LOGFILE}="${LOGDIR}\${PREFIX}_${i}_${DATETAG}.log"
    ${SQL}= @" 
    set statistics time on
    :r ${FILE}
    go
 "@ 
  echo ${SQL} | sqlcmd -S ${SERVER} -U ${USER} -P ${PASS} -d ${DBNAME} -o ${LOGFILE}
  }
}

PowerShell では for ではなく php や perl と同じ foreach ですが、ls コマンドで拡張子 sql のファイルを bash と同じように取得できます。スクリプト内でコマンドの実行結果を変数に格納する場合、bash は``で囲みますが PowerShell では()でコマンドを囲みます。

非互換部分ですが PowerShell で便利なのが basename を含むファイルの情報を取得するのに、コマンドではなく属性にアクセスして取得できることです。これは PowerShell がオブジェクト指向で、ls で戻ってきた値を fileinfo オブジェクトとして扱いその属性として basename をもつからです。ここではあまり詳細に触れず深入りせずにすすみます。

最後に bash でよく使うヒアドキュメントを見てみましょう。実は PowerShell でも@を使いヒアドキュメントを使えます。直接、sqlcmd に続けて書くことはできませんが、文字列にして sqlcmd にパイプで渡して実行できます。注意点としては終了識別子の”@にはインデントをつけられないので左端に書く必要があります。

こうしてみると Linux で慣れ親しんだ bash 等と PowerShell が非常に似ていて簡単に実装できることがわかっていただけたと思います。まずはなるべくシンプルに移行して文法に慣れ親しんだ後、PowerShell 独自のより便利な機能に足を踏み入れてはいかがでしょうか。

ここまでできると次は、バックグランド処理による SQL の同時実行や実行時にあわせてパフォーマンス情報の取得がしたくなってきますね。次回、PowerShell を題材にしたブログを書く時はそれらを取り上げたいと思います。

PowerShell で操作する SQL Server クックブック （bash から PowerShell へ）

Additional resources

PowerShell で操作する SQL Server クックブック（bash から PowerShell へ）