DuckDB Update & Blog reading #6:非標準のCSV読み込み
まえがき
DuckDBの公式ブログの更新が来ました。🙌
今回は非標準のCSVの読み込みについてでした。
詳しい記事は↓
概要
Pollock Robustness Benchmark(ポロックロバストネスベンチマーク)というものがあるらしい。
CSVは一応標準規格はあれどみんな思い思いの状態で使ってることが本当に多い。(自分もですが)
DuckDBはそういったCSVでも読めるが
それが本当に正しいか確かめるために、非標準ファイルに対するCSVリーダーの性能を測定する
「Pollockベンチマーク」を使用した...
ところDuckDBは第1位にランクされた!そうです。なので今回は色々な手法でCSVを読むという内容です!
(Blogの画像はポロックだからドロッピングなのか...)
非標準のCSVとは...
例えば、
名前,趣味
田中,サッカー
山田,"読書 "ゲーム" "
一見別問題ないように見えるが、""で囲まれている値の中に「"」が含まれている。rfc4180というCSVの標準規格では
名前,趣味
田中,サッカー
山田,"読書 ""ゲーム"" "
というような感じで、""の中で"ゲーム"というようなものを書く場合には""ゲーム""と書かないといけないらしいです。
こういった非標準のCSVが生まれるのは万国共通なのかもしれないですね。
何十年も古いシステムから出力されるCSV、手入力のCSV、スプレッドシート(Excel)から出力されたCSV...🫠
こういったエラーが表形式のデータ、データフレーム、スペシャルな(?)CSVツールでは蔓延り、サルベージしてテーブル形式に何とかして収めてるのが現状🔨
こんな時DuckDBならどうしてくれるかというのが今回の内容でした。
CSVの読み方色々
とりあえず普通に読んでみる
今回の例で使用されているcsvは以下のようなものです。
ZIP,Name,Country
1014,"Cafe ""Gedoogt""",Netherlands
1015,"Cafe "De Tuin" Amsterdam",Netherlands
1095,Joost
1872,Cafe Gezellig,Netherlands,5
これだけのデータには以下のような問題があります。
| Line | Row | Comment |
|------|------------------------------------|-------------------------------------------------------------------------------------|
| 1 | ZIP,Name,Country | ヘッダー. |
| 2 | 1014,"Cafe ""Gedoogt""",Netherlands | 引用符のなかに引用符("""")で囲まれた値がある正しい例 |
| 3 | 1015,"Cafe "De Tuin" Amsterdam",Netherlands | 引用符のなかに引用符("")で囲まれた値がある非標準の例 |
| 4 | 1095,Joost | Country列に何も入っていない |
| 5 | 1872,Cafe Gezellig,Netherlands,5 | 新しく5がヘッダーで示されてる列外にある |
なにもオプションをつけずにDuckDBで読んでみます。
import duckdb
con = duckdb.connect(database=":memory:")
print(con.sql("FROM read_csv('cafes.csv');"))
┌─────────┬───────────────┬─────────────┬─────────┐
│ column0 │ column1 │ column2 │ column3 │
│ int64 │ varchar │ varchar │ int64 │
├─────────┼───────────────┼─────────────┼─────────┤
│ 1872 │ Cafe Gezellig │ Netherlands │ 5 │
└─────────┴───────────────┴─────────────┴─────────┘
とりあえず一行だけ読めました。
2列目も標準に適してるはずなんですが読めないんですね...
CSV Sniffer
sniff_csvという読み方でそのCSVの「方言」を推定する...とのこと
「方言」とは区切り文字に何が使われているか...などです。
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
SELECT Delimiter, Quote, Escape, SkipRows
FROM sniff_csv('cafes.csv')
"""))
結果は...?
┌───────────┬─────────┬─────────┬──────────┐
│ Delimiter │ Quote │ Escape │ SkipRows │
│ varchar │ varchar │ varchar │ uint32 │
├───────────┼─────────┼─────────┼──────────┤
│ , │ \0 │ \0 │ 4 │
└───────────┴─────────┴─────────┴──────────┘
Delimiterは区切り文字で「,」が使われてるよという情報
Quoteは「"」が何個使われているかという情報
Escapeは「\」が何個使われているか?という情報(かな)
SkipRowsは何行を読み飛ばしたかということ。(今回は4行)
sniff_csvでCSVデータを読むことでこう言った情報が読めるみたいですね👀
自動検出を切って手動で読み込みの設定をする
ここで一旦自動検出を切ってから読み込みをしています。
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
FROM read_csv('cafes.csv',
auto_detect = false,
header = true,
delim = ',',
quote = '"',
escape = '"',
columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'}
);
"""))
auto_detect = false で自動検出を切っています。
あとは先ほどsniff_csvで読み取った内容に基づいて,
delim(区切り文字)、quote(クオート)、escape(")で各列の型も指定しています。
ただこれで実行しても以下のようなエラーになってしまい
ます。
Traceback (most recent call last):
File "/Users/......readcsv.py", line 3, in <module>
print(
duckdb.duckdb.InvalidInputException: Invalid Input Error: CSV Error on Line: 3
Original Line: 1015,"Cafe "De Tuin" Amsterdam",Netherlands
Value with unterminated quote found.
Possible fixes:
* Disable the parser's strict mode (strict_mode=false) to allow reading rows that do not comply with the CSV standard.
* Enable ignore errors (ignore_errors=true) to skip this row
* Set quote to empty or to a different value (e.g., quote='')
file = cafes.csv
delimiter = , (Set By User)
quote = " (Set By User)
escape = " (Set By User)
new_line = \n (Auto-Detected)
header = true (Set By User)
skip_rows = 0 (Auto-Detected)
comment = \0 (Auto-Detected)
strict_mode = true (Auto-Detected)
date_format = (Auto-Detected)
timestamp_format = (Auto-Detected)
null_padding = 0
sample_size = 20480
ignore_errors = false
all_varchar = 0
ここでは
print(
duckdb.duckdb.InvalidInputException: Invalid Input Error: CSV Error on Line: 3
Original Line: 1015,"Cafe "De Tuin" Amsterdam",Netherlands
Value with unterminated quote found.
となってますね👀
先ほどの""の中に""がある場合ちゃんとエラーとして出てきてます。
解決策としてstrict_mode=falseを設定してとエラーメッセージ内に記載があります。
Strict_modeを切る。
Strict_mode = falseにすることで標準規格に沿っていないCSVも読めるようになったはずです。
というわけでStrict_modeを切って再チャレンジ
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
FROM read_csv('cafes.csv',
auto_detect = false,
header = true,
strict_mode = false,
delim = ',',
quote = '"',
escape = '"',
columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'}
);
"""))
Traceback (most recent call last):
File "/Users/nk/Desktop/renshu/pythonbox/test/readcsv.py", line 3, in <module>
print(
duckdb.duckdb.InvalidInputException: Invalid Input Error: CSV Error on Line: 4
Original Line: 1095,Joost
Expected Number of Columns: 3 Found: 2
Possible fixes:
* Enable null padding (null_padding=true) to replace missing values with NULL
* Enable ignore errors (ignore_errors=true) to skip this row
file = cafes.csv
delimiter = , (Set By User)
quote = " (Set By User)
escape = " (Set By User)
new_line = \n (Auto-Detected)
header = true (Set By User)
skip_rows = 0 (Auto-Detected)
comment = \0 (Auto-Detected)
strict_mode = false (Set By User)
date_format = (Auto-Detected)
timestamp_format = (Auto-Detected)
null_padding = 0
sample_size = 20480
ignore_errors = false
all_varchar = 0
まだエラーが出てしまいますね。
CSV Error on Line: 4
Original Line: 1095,Joost
Expected Number of Columns: 3 Found: 2
これはCountry列に何も入っていなかったので3列あるはずなのに値は2つしか見つかっていないというエラーが出ています。
ignore_errors = true
じゃあもうエラーなんか無視してとりあえず動かしたい! ということで今度はerrorを無視する設定をします。
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
FROM read_csv('cafes.csv',
auto_detect = false,
header = true,
strict_mode = false,
delim = ',',
quote = '"',
escape = '"',
columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'},
ignore_errors = true
);
"""))
結果は...?
┌───────┬────────────────────────┬─────────────┐
│ ZIP │ Name │ Country │
│ int16 │ varchar │ varchar │
├───────┼────────────────────────┼─────────────┤
│ 1014 │ Cafe "Gedoogt" │ Netherlands │
│ 1015 │ Cafe De Tuin Amsterdam │ Netherlands │
│ 1872 │ Cafe Gezellig │ Netherlands │
└───────┴────────────────────────┴─────────────┘
何とか読めました。
ただ先ほどのCountry値に何も入っていない行は読めませんでした。
Null Padding
null paddingについては以前自分が書いた記事でも使用したんですが列がヘッダーより増えてしまった場合でもnullで埋めて読み込めるようにするオプションです。
今回のデータで適用すると
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
FROM read_csv('cafes.csv',
auto_detect = false,
header = true,
strict_mode = false,
delim = ',',
quote = '"',
escape = '"',
columns = {'ZIP': 'INT16', 'Name': 'VARCHAR', 'Country': 'VARCHAR'},
null_padding = true
);
"""))
┌───────┬────────────────────────┬─────────────┐
│ ZIP │ Name │ Country │
│ int16 │ varchar │ varchar │
├───────┼────────────────────────┼─────────────┤
│ 1014 │ Cafe "Gedoogt" │ Netherlands │
│ 1015 │ Cafe De Tuin Amsterdam │ Netherlands │
│ 1095 │ Joost │ NULL │
│ 1872 │ Cafe Gezellig │ Netherlands │
└───────┴────────────────────────┴─────────────┘
という感じで四行全てよむことができました!
...が何か足りませんね。
1872のデータは本来以下のような感じだったのですがそれが失われています。
1872,Cafe Gezellig,Netherlands,5
読みたいものを読むために必要なコード
どうもauto_detectをfalseにしている設定とheaderの設定を消して以下のようなコードをすると期待のcsvが読めるようです。
import duckdb
con = duckdb.connect(database=":memory:")
print(
con.sql("""
FROM read_csv('cafes.csv',
strict_mode = false,
delim = ',',
quote = '"',
escape = '"',
null_padding = true
);
"""))
公式のブログではdelim設定は無いです、無くても読めます。
┌───────┬────────────────────────┬─────────────┬─────────┐
│ ZIP │ Name │ Country │ column3 │
│ int64 │ varchar │ varchar │ int64 │
├───────┼────────────────────────┼─────────────┼─────────┤
│ 1014 │ Cafe "Gedoogt" │ Netherlands │ NULL │
│ 1015 │ Cafe De Tuin Amsterdam │ Netherlands │ NULL │
│ 1095 │ Joost │ NULL │ NULL │
│ 1872 │ Cafe Gezellig │ Netherlands │ 5 │
└───────┴────────────────────────┴─────────────┴─────────┘
これでなんとか増設分の5が読めてヘッダーも追加でつけてくれました🙌
ポロックベンチマーク
非標準的なCSVファイルを読み込む際のCSVシステムの堅牢性を評価するために設計された
CSVデータ読み込みベンチマーク...ということらしいです。
2023年のVLDBで発表され、完全にオープンソースで公開されてるみたいですね。
最近、DuckDBにそのリポジトリに追加されたらしいです。
具体的な評価方法は公式サイトに詳細が書いていますが、
- 約245,000の公開CSVデータセットを分析
- RFC-4180標準に対してどういう間違ったCSVが世の中にあるか調査
- エラーパターンを一般化して、非標準のCSVが生成されるジェネレーターを作成した。
- 2,200以上の汚染されたファイルを生成(polluted...汚染という表現がされてました。🪣)
こういった非標準なCSVを生成して、読み込んでクリーンなバージョンと比較してからどれだけ正確に読み取ったかという評価方法を行なっているようです。
DuckDBは、これで99.61%のデータを正しく読み取ったとのことです!自動検出モードでは、約90.75%のデータを正しく読めたということでした🙌
まとめ
DuckDBでCSVを読み込む際のオプションの設定についていろいろ学ぶことができました!
CSVのデータの処理は本当に大変で...こう言った機能を無料で使えるので重宝しています。🙏
英語勉強用
Pollock Robustness Benchmark:Pollockは画家のPollockと同じ綴り...関連性は要検証
CSV files found in the wild:野生のCSV...
Into the CSV Abyss:CSVの深淵...行きたくない
prevalent:蔓延る。跳梁跋扈
dialect configuration:方言設定。csvの方言とかでも使用
Methodology:方法論
Discussion