Using Query Builder with Large CSV filesI was talking to a customer today about query builder. The customer has large (3GB+) CSV files. They've tried to load them into JMP directly, but they're seeing a significant performance hit. The reason they were wanting to import the data was to subset out a more reasonably sized portion of it for analysis. They wanted to know if there was a was for Query Builder to work with CSV files directly.
I proposed that it would be really easy to use DB Browser for SQLite to turn the CSV into an SQLite database that could be queried by Query Builder. I didn't think it would be possible to access directly. I wanted to see if anybody had any strategies on or thoughts on this.
Thoughts?
M
Re: Using Query Builder with Large CSV filesUpdate to this - ActualTech found the bug and is updating the driver as well as some Mojave support related issues. They haven't given me an eta for when the updates will be coming through.
MRe: Using Query Builder with Large CSV filesWelp - It turns out there IS a way to go after CSV's from within Query Builder directly. Microsoft (Windows) and ActualTech (MacOS) both make drivers that will let you treat CSV, TSV, and XSLX files as data sources... Which means they can be queried by ODBC from Query Builder. The options this opens up are really exciting...
For Windows, you need to install the access driver set. I found some that work here (https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=13255). Then it's just a matter of setting up the new data source. I verified that this works as expected with CSV's using my Windows 7 vm.
(Edit - the more current driver set is here: https://www.microsoft.com/en-us/download/details.aspx?id=54920)
For Mac, you use the Access Driver from ActualTech. This also sets up easily, but there are some issues with how the query behaves. I'm pretty sure it's a driver issue, so I've got a question into ActualTech about it. I'll update this after I hear back from them. The connection and query works, but it seems to repeat the results over and over without stopping. (i.e. for a 300 row csv the result can be as much as 1M rows before I terminate the query with results being the 300 rows repeated in sequence over and over).
So - Windows, you're probably good to show this to a customer. Mac - let's hang tight for a sec.
M
Re: Using Query Builder with Large CSV filesProbably not - I completely forgot about that bit too... It's a good point, though I think they really want to be able to use QB directly on the CSV file. Maybe that coupled with the data table QB would be a good solution.
MRe: Using Query Builder with Large CSV filesI'm referring to the direct text import, BTW, not Query Builder.Re: Using Query Builder with Large CSV filesThey did set the preference to only scan column types for 5 seconds, rather than scan whole file, right?
Brian
Re: Using Query Builder with Large CSV filesIt will eventually read it in, but the problem is that JMP will try and read the entire file before it decides what to do with it. So, it's not a hardware problem, but an issue with how JMP treats flat files.
MRe: Using Query Builder with Large CSV filesDo you know if this is a PC only thing? I can't find any info for mac.Re: Using Query Builder with Large CSV filesThis was my first thought - but the "data with preview" option requires JMP to parse the whole file before you get the preview... which is the slow step. It won't hang, it just takes forever to do the initial read of the file before you get any options.
MRe: Using Query Builder with Large CSV filesMike,
Do you know how much RAM is installed?
What other applications do they have open?
Generally, Minimum RAM = 2Xs the file size, or in this case the minium RAM to open a single 3GB file they will need a minimum of 6GB RAM.
And they attempting to open >1 of the 3GB files?
What is the time to open each of the CSV files directly with Excel? I would expect the time to open the CSV file in JMP to be comparable.
Stan
Re: Using Query Builder with Large CSV filesThere is an ODBC driver for Text that Microsoft provides if you have Office installed. However, I'm not convinced it will help with the performance issues. It would allow them to do simple Where clauses through the database Query Builder without having the text files completely resident within JMP.
Brian
Re: Using Query Builder with Large CSV filesYou can create a subset of a CSV when you open a file under "Data with Preview". Are they able to make it that far or does their machine hang during this operation?
