PowerShellでのデータ抽出とSQL

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から実行すると、「プロバイダーが登録されていません」というエラーになってしまうため注意が必要です。


コメント