PowerShell でデータベースからCSVファイルを出力したい（ODBC接続） #PowerShell

目的

SQLを渡せばデータベースから取得する機能がほしい。
簡単にCSVファイルで出力したい

要件

Windows上で実行したい
件数が多くてもメモリ不足にならないようにしたい
ODBC接続を使いたい
- Oracle Postgres SQLServer SQLite3 どれでも動くようにしたい
Byte[] （画像のバイナリとか）は base64 の文字列で出力したい

制限

出力時の項目名で同じものが複数あるとか普通はないと思うが禁止。内部でハッシュを使っているので。

解決

PowerShellでかいた。
Github https://github.com/toronya/Invoke-OdbcSelectSql

Invoke-OdbcSelectSql.ps1


function Invoke-OdbcSelectSql
{
  param (
    [string]$conString,
    [ValidateScript({$_ -match '\bselect\b'})] [string]$sqlString,
    [string]$sqlFilePath,
    $con,
    [string]$DateFormatString = "yyyy/MM/dd",
    [string]$DateTimeFormatString = "yyyy/MM/dd HH:mm:ss",
    $timeout = 36000
  )
  begin{
    if ($sqlString -eq ""){
        if ($sqlFilePath -ne "" -and (Test-Path -Path $sqlFilePath) -eq $true ) {
          [string]$sqlString = (Get-Content $sqlFilePath)
        }else{
          Write-Error ("Required : -sqlString or -sqlFilePath")
          exit
        }
      }

    if($null -eq $con){
        $createCon = $true
        $con = New-Object System.Data.Odbc.OdbcConnection($conString)
        $con.Open()
    }else{
        $createCon = $false
    }
  }
  
#---------------------------------------------------

    process{
        $cmd = New-Object System.Data.Odbc.OdbcCommand
        $cmd.CommandTimeout = $timeout
        $cmd.Connection = $con
        $cmd.CommandText = $sqlString

        $rdr = $cmd.ExecuteReader()

        # HEADER
        $columnNames = @($rdr.GetSchemaTable() | Select-Object -ExpandProperty ColumnName)
          
        # BODY
        while ($rdr.Read()) {
            $result = [ordered]@{}
            for ($i=0; $i -lt $rdr.FieldCount; $i++) {
              $clm = $rdr[$i]
              if ($null -eq $clm){
                $clm = ""
              }elseif($clm -is [byte[]]){
                $clm = [System.Convert]::ToBase64String($clm)
              }elseif($clm -is [DateTime]){
                if ($clm.Hour -eq 0 -and $clm.Minute -eq 0 -and $clm.Second -eq 0 ){
                  $clm = ([DateTime]$clm).toString($DateFormatString)
                }else{
                  $clm = ([DateTime]$clm).toString($DateTimeFormatString)
                }
              }
              $result.Add($columnNames[$i], $clm)
            }
            # Hash to PSCustomObject
            [PSCustomObject]$result
        }
    }
    end{
        if($createCon -eq $true){
            $con.close()
            $con.dispose()
        }
    } 
}

使い方

事前にODBC接続設定しておく。
ODBC設定が用意できるドライバが32BITならpowershellのプロセスも32BITで実行する必要あり。

Import-Module .\Invoke-OdbcSelectSql.ps1
Invoke-OdbcSelectSql -conString "dsn=postgres_x64" -sqlString "select * from work.test" |
ConvertTo-Csv -NoTypeInformation |
ForEach-Object {$_ -replace '"',''} |
Out-File -FilePath ".\test.csv" -Encoding Default

完成までのあれこれ。

PowerShellにした理由。
- 最近のWindowsなら最初からインストールされているので、使いまわしがきくため。
大きいCSVでもメモリ不足にならないようにした。
- 世間によくあるサンプルだと OdbcDataAdapter で Fill しているが件数が多いとメモリ不足でエラーになる。
  対応として OdbcDataReader で１行ずつ読みこんでは Hash（項目名：値のペア）にする。
最初はCSVファイルを直接出力していたが、パイプラインの後続処理でファイル出力できるようにしたくなった。
- Hash を PSCustomObjectに変換してReturnすれば次のパイプへ流せると知ったので対応。
日付しかないカラムなのに時刻(00:00:00)がついてくるですけど・・・。
- データベースあるあるみたいだがすっきりする解決方法がみつからず。
- カラムのタイプがDateTime かつ、0時0分0秒だったら yyyy/MM/dd にフォーマットすることにした。
- ※正確にはデータから時刻が消えてしまう可能性がある。更新日時が入るカラムの場合に、0時0分0秒ちょうどに更新した場合。
でも自分の環境ではそんな処理はしていないので割り切った。(-_-)
※日付しかないカラムなのに時刻(00:00:00)がついてくるのはODBCドライバ側の仕様のようです。
画像データとかバイナリに対応し忘れていた。バイナリ、byte[] の場合はbase64の文字列としてCSVで出力し取り出し後にバイナリに戻すと良いです。

ライセンス

MITライセンスにしておきます