Skip to content

Parallel CSV Reader #5194

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
merged 41 commits into from
Nov 10, 2022
Merged

Parallel CSV Reader #5194

merged 41 commits into from
Nov 10, 2022

Conversation

pdet
Copy link
Contributor

@pdet pdet commented Nov 3, 2022

Experimental implementation of the Parallel CSV Reader.

We can currently parallelize cases where the quotation, escape, and delimiters are limited to one character.
The parallel CSV reader doesn't also doesn't support new lines inside strings nor the CSV Sniffer (A schema must be provided first).

Since the implementation is not yet integrated with the CSV Sniffer is currently hidden by the

SET experimental_parallel_csv=true;

The CSV reader works by default by throwing as many threads as possible to work on 32 Mb Chunks. The chunk size can be adjusted via the CSV Read function.
e.g.,

SELECT sum(a) FROM read_csv('test/sql/copy/csv/data/test/multi_column_integer.csv',  COLUMNS=STRUCT_PACK(a := 'INTEGER', b := 'INTEGER', c := 'INTEGER'), auto_detect='false', delim = '|', buffer_size=5)

For TPC-H SF1:

Latest DuckDB duckdb-0.5.1
SF:1 Threads: 1 Time:2.9291566249448806

This PR
SF:1 Threads: 1 Time:2.953550708014518
SF:1 Threads: 4 Time:0.8697507920442149

~3.6x faster with 4 threads.

pdet and others added 30 commits October 18, 2022 19:34
@pdet
Basis for CSV parallel read
f71c246
@pdet
Simple csv running in parallel
3621f70
@pdet
moving next out of the readcsv
0c1e806
@pdet
Separating file handle from buffered reader
60c9b8c
@pdet
Ok parallel csv reader mostly parallel
0f94abc
@pdet
Changing battle plan to multiple threads in one buffer
dcd5cd6
@pdet
[WIP] Continuing to change the csv reader to sahare buffers on multip…
6144e22 
…le threads
@pdet
Alright, ST working
21e40e9
@pdet
ST working
b9b9e1d
@pdet
Alright this works with a buffer of size 1
603183a
@pdet
Dealing with EOF
7bf3eeb
@pdet
Fixing some errors + adding Batch Index
5833fd7
@Mytherin
Correctly deal with the scenario where a thread is initialized after …
25e0754 
…the file is already fully processed
@Mytherin
Merge branch 'master' into parallel_csv
02851f8
@Mytherin
Refactor 0.1
0e232c7
@Mytherin
Re-use initial reader options
0cea405
@Mytherin
Fixes for headers/skip_rows, linenr in error message, and auto-detect…
9d8c994 
…ed types
@Mytherin
Add single-threaded CSV reader back in
2528538
@Mytherin
Single-threaded CSV reader
7c98632
@Mytherin
experimental_parallel_csv setting
738f0f3
@Mytherin
1111
635ffb6
@pdet
Fixing bug the refactoring introduced plus adding tpch tests
fd14f3b
@pdet
Fixing issues and adding TPCH TPCDS and Clickbench as tests for the p…
e6c2cda 
…arallel csv reader
@pdet
small cleanup test fix
def0e04
@pdet
Small fix and cleanup
254451e
@pdet
small fix
edbdd71
@pdet
Fixing some bugs
97336d2
@Mytherin
Merge branch 'master' into parallel_csv
64dc49c
@pdet
Detecting quoted new lines from within the same buffer
24961a5
@pdet
Merge remote-tracking branch 'origin/master' into parallel_csv
2ec5518
@Mytherin Mytherin merged commit 3261e00 into duckdb:master Nov 10, 2022
@lnkuiper
Copy link
Contributor

Awesome work @pdet !! This release is going to be awesome :)

@pdet
Copy link
Contributor Author

pdet commented Nov 10, 2022

Thanks @lnkuiper, yeah this release will be lit 🔥

We did some benchmarking yday and now we can load the clickbench csv file in ~400s in the same machine as their official benchmark here

@arjenpdevries
Copy link
Contributor

So that's more than an order of improvement, impressive!

@Mytherin
Copy link
Collaborator

The single-threaded results are actually around 1200 seconds, the results on that page are from the previous DuckDB version that would load all data ingested in a single transaction uncompressed in memory prior to writing it out to disk, causing very poor performance when loading CSV files that were larger than memory as is the case in the benchmark (see #4996 for the PR that fixed that).

@arjenpdevries
Copy link
Contributor

Still a great improvement!

@hatvik
Copy link
Contributor

hatvik commented Nov 17, 2022

@pdet So this works with COPY from csv too?

@pdet
Copy link
Contributor Author

pdet commented Nov 17, 2022

HI @hatvik
Yes, it should work, you do need to set SET experimental_parallel_csv=true; first.

Please let me know if you encounter any issues :-)

@jaens
Copy link

jaens commented Nov 24, 2022

Is it possible to load in parallel from multiple CSV files at once?

I am currently using datasets that are partitioned into Zstd-compressed fixed-row-count CSV chunks (since that makes it easy to process in parallel with arbitrary tools eg. just using GNU parallel).

@Alex-Monahan
Copy link
Contributor

Hello! Using either the glob syntax or passing in a list of files should be run in parallel!

@bucweat bucweat mentioned this pull request Feb 2, 2023
Closed
2 tasks
@alamb alamb mentioned this pull request Feb 7, 2023
Closed
@alamb alamb mentioned this pull request May 10, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Reviewers
No reviews
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

Successfully merging this pull request may close these issues.
7 participants
@pdet @lnkuiper @arjenpdevries @Mytherin @hatvik @jaens @Alex-Monahan