top of page

PowerShellでのデータ抽出とSQL

5月27日
読了時間: 3分

Python環境がないときにデータ抽出したい場合、PSできるけど、実際にCSVをパイプで抽出するのとSQLを使う方法、どんな違いがあるのだろう?と思って実際にやってみた。

※実行タイムは出さないです。10万件とかのテストデータ持って来るの面倒だから。











パイプで。年齢が30歳以上

PS C:\Users\T> import-csv "C:\tmp\test.csv" | Where-Object{$_.age -gt 30}


実行結果

Name Age Gender

---- --- ------

Taka 47 M

Uji 35 M

 

SQL 


PS C:\Users\T> $path = "C:\tmp\test.xlsx"


$conn = New-Object System.Data.OleDb.OleDbConnection


# Extended Propertiesの値を 'Excel 12.0;HDR=YES' に修正

$conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$path;Extended Properties='Excel 12.0;HDR=YES';"


$conn.Open()

$query = "SELECT * FROM [Sheet1$] WHERE Age > 30"


$cmd = $conn.CreateCommand()

$cmd.CommandText = $query


$adapter = New-Object System.Data.OleDb.OleDbDataAdapter $cmd

$table = New-Object System.Data.DataTable


$adapter.Fill($table) | Out-Null


$conn.Close()


$table


実行結果

Name Age Gender

---- --- ------

Taka 47 M

Uji 35 M

 

 

Out-Null

  • 「戻り値いらないから捨てる」


実際使ってみるとなんて面倒なんだって思った。

しかしながらパイプでいくか、SQLでいくかはデータ量にもよるのかな。

数千行であればパイプ、数万行以上であればSQLそれぞれすみ分けってあるし。

ざっと考えると頭から準読みしてふるいにかけるのと、インデックスを使ってまとめて持ってくる違いかな。


以下AIの受け売り

2つの手法の決定的な違い

1. メモリの消費方法(オンメモリ vs プロセスへの委任)

  • パイプ(Import-Csv):

    指定したCSVの「全行」を一度にメモリ上に読み込み、オブジェクトの配列に変換してから1行ずつフィルターにかけます。データ量が数十万行になると、PowerShellプロセスだけでギガバイト単位のメモリを消費し、最悪の場合はメモリ不足(OOM)で停止します。

  • SQL(OLEDB):

    データの絞り込み(WHERE句の処理)をPowerShell側ではなく、データベースエンジン(ACE OLEDB)側で行わせます。 PowerShell側には、条件に合致した結果(数件〜数百件など)だけが戻ってくるため、PowerShell自体のメモリ消費を劇的に抑えられます。

2. インデックスとフルスキャン(全件走査)

  • パイプ(Import-Csv):

    常に「フルスキャン(全件走査)」です。10万件あれば必ず10万回条件判定を行います。

  • SQL(OLEDB):

    Excelファイルに対するOLEDB接続の場合、通常のRDB(SQL Server等)のように明示的なインデックス(索引)が効くわけではありません。しかし、エンジン内部で必要な列データのみを最適化して走査したり、メモリ転送を最適化したりするため、純粋なテキスト解析を行う Import-Csv よりも大容量データに対して高速に動作します。

実際の住み分けの目安

Taka様が想定された通り、データ量(行数)によって明確な住み分けが存在します。

手法

適したデータ量の目安

メリット

デメリット

パイプ(CSV)

数千行 〜 1万行程度

* 1行で書けて直感的


* 外部ドライバや環境(32bit/64bit)の制約がない

* 大容量データでメモリを大量消費する


* 速度が低下する

SQL(OLEDB/Excel)

数万行 〜 10万行超

* メモリ効率が良い


* 条件絞り込み済みのデータだけをスマートに取得できる

* コードが冗長で面倒


* Officeのビット数(x86/x64)に依存する


補足:うちはExcel 2007という古い環境を使っているため上記コネクション設定(Excel 12.0)にしていますが、Office 365などの新しい環境で .xlsx を扱う場合は、以下のように Excel 12.0 Xml を指定します(※各プロパティの前に半角スペースが必要です)。

PowerShell

$conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=$path; Extended Properties='Excel 12.0 Xml;HDR=YES';"

※環境によっては、プロバイダ名が Microsoft.ACE.OLEDB.16.0 になっている場合もあります。


また、実行するPCのExcelが32ビット版の場合、PowerShellも32ビット版(Windows PowerShell (x86) や 32ビット版のPowerShell ISE)で実行する必要があります。64ビット版のPowerShellから実行すると、「プロバイダーが登録されていません」というエラーになってしまうため注意が必要です。

コメント


bottom of page