PowerShellでSQL serverに接続して、csvファイルへ保存する

PowerShellからMicrosoft SQL serverへの接続する

PowerShellでETLができそうだとわかると当然データベースに直接アクセスしてそのデータを抽出してETLを行いたいと思うようになると思います。

そこで Microsoft SQL Server というデータベースへアクセスして直接データを取得することがありましたので備忘録を記しておきます。

PoweShellでは、SqlServer というモジュールが供されており、これを利用することでMicrosoft SQL Server へ接続できます。

SqlServer module をインストールする

まず、SqlServer module がインストールされていないと使えないのでまずはインストールをします

Install-Module SqlServer -scope CurrentUser -verbose

# -scope で CurrentUser を指定して現在のユーザに対してインストールを行います

# -verbose は 特に記述の必要はありませんが、インストールの進行を確認できるのでよく使います

この操作は、システム上1回行えば、よいのでpwsh でPowershellのコマンドライン上で実行すればよいかと思います。

PowerSehllスクリプトファイル上にコメントとしてこのモジュールをインストールすることを記述しておくと親切です

SqlServer module をPowerShell上で使用できるようにする

PowerShell 3.0 以上を使用している場合は、特に何も記述しなくても最初のコマンドの時に自動で読み込まれるとのこと。

記述しておいても問題はありません。記述する場合以下の通り

Import-Module SqlServer

SQL server に接続し、SQL言語にて データベースからデータを抽出する

※Microsoft SQL Server 側での設定で、「SQL認証」で「サーバ証明書を信頼する」での接続方法しか試してませんので各自必要な認証方法はテストしてください。

基本的には、このInvoke-Sqlcmdコマンドレットを使用して接続からデータの取得まで行います。

$query 内に取得する抽出データのSQL文を記述してデータを取ってくる

取得したデータは、カスタムオブジェクトとして$sql_dataに保存される ※そのため大量のデータはメモリーを圧迫するので気をつけましょう

もしかするとそのままパイプ処理でファイル保存したほうが良いのかも知れません

$servername = 'SQLサーバIP/SQLサーバ名'

$databasename = 'データベース名'

$username = 'ユーザ名'

$passwodfile = 'パスワードの入ったファイル' #こちらを参照して作っておく👉Poweshellで暗号化文字列を扱ってみる 

$query =@"

SELECT *
FROM [databasename].[table]
WHERE .... 

"@

$SecurePassword = Get-Content -Path $passwordfile | ConvertTo-SecureString

$credential = New-Object System.Management.Automation.PSCredential($username, $securepassword)

$sql_data = Invoke-Sqlcmd  `
-ServerInstance $servername `

-Database $databaseName `
-Credential $credential 'true'`
-TrustServerCertificate ` #SqlSever module ver22より
-Query $query 

取得したデータを加工する

取得したデータは、カスタムオブジェクトになっているのでCSVをETLする方法で加工できると思いますので

こちらを参考に👉PowershellでcsvをETLする 

CSVファイルとして保存する

$csvfilepath = '保存するcsvファイルのパス名'

$sql_data | Export-Csv -Path $csvflepath -NoTypeInformation -Encoding oem