Buffering tools: warn if running against extremely large files

[onyxfish]

Such as #581.

But what's the limit? 100MB? 500MB? 1GB?

[jpmckinney]

For files larger than 1GB, I don't think we can expect csvkit to ever perform well, so we can at least start there. Some tools will have lower limits. For reference, the buffering tools are listed here.

• csvjoin
• csvjson unless --stream and --no-inference are set but --skip-lines is not
• csvlook
• csvpy
• csvsort
• csvsql
• csvstat
• in2csv unless --format is ndjson and --no-inference is set, or --format is csv and --no-inference is set but --skip-lines is not

Noting that issues had been opened about the performance of csvsort (#157, #338, #457, #626), csvsql (#428, #633), csvstat (#581). #141 discusses general performance.

[jpmckinney]

Fast alternatives:

• csvsort cf664eb
• csvsql csvsql: Document creating schema and COPYing data for large jobs #735

[jpmckinney]

The warning can also mention this from tricks.rst:

If a tool is too slow to be practical for your data try setting the :code:`--snifflimit` option or using the :code:`--no-inference`.

[jpmckinney]

Grepping for if .* in, there are checks for in column_names, in excludes, in patterns which may be faster if they were sets instead of tuples/lists - as long as order/repetition isn't relevant. Update: None of these occur in loops, so using sets would only make a difference for very wide CSVs.

I think if column_id < len(row) in csvcut always passes - need to test on a CSV with a short row. Update: Yes, it's needed; added a test. len(row) doesn't need to be calculated every time, but I expect that wouldn't be much of a speed improvement.

Anyway, for posterity, noting that the streaming tools probably can't get much faster within csvkit.

[fgregg]

Does csvsql always need to buffer?

I see why it would need to buffer in the case that you are both creating the table and filling the table as that requires two passes on the data. But if you are only doing one of those tasks it seems like csvsql should be able to stream?

From csvjson it seems like it's an acceptable design to have arguments change the streaming/buffering nature of the command?

Similarly, many of the statistics calculated by csvstat can be calculated by a streaming algorithm.

[jpmckinney]

csvsql has a faster alternative in #735 which should maybe be pursued.

csvjson does stream if you set --stream and --no-inference but don't set --skip-lines.

csvstat buffers because it uses agate, but we can implement some statistics directly in csvkit to avoid buffering.

[fgregg]

csvsql has a faster alternative in #735 which should maybe be pursued.

I use this technique a lot, but it doesn't help much if the pattern of a columns shift at 6,000,001th row. (I understand that streaming won't really make things faster, but should help with memory).

Would y'all be interested in a csvsql that uses streaming when appropriate.

[jpmckinney]

I would be, yes!

[pixarbuff]

When I run csvstat on a 218MB file it fails silently giving no output. When I run it in verbose mode, it gives a memory error. Would it be possible for it to display this memory error while not in verbose mode?

Traceback (most recent call last):
File "/home/ubuntu/.local/bin/csvstat", line 10, in
sys.exit(launch_new_instance())
File "/home/ubuntu/.local/lib/python3.6/site-packages/csvkit/utilities/csvstat.py", line 341, in launch_new_instance
utility.run()
File "/home/ubuntu/.local/lib/python3.6/site-packages/csvkit/cli.py", line 118, in run
self.main()
File "/home/ubuntu/.local/lib/python3.6/site-packages/csvkit/utilities/csvstat.py", line 140, in main
**self.reader_kwargs
File "/home/ubuntu/.local/lib/python3.6/site-packages/agate/table/from_csv.py", line 65, in from_csv
contents = six.StringIO(f.read())
MemoryError

[jpmckinney]

I've made a commit to do that - thanks!