Skip to content
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.

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

Is entire file loaded into memory to be queried? would prefer read line by line, not whole file loaded in (10Gb file) #313

Open
therobyouknow opened this issue Apr 11, 2023 · 2 comments

Comments

@therobyouknow
Copy link

I have a 10Gb TSV file that I'd like to read using SQL commands.

As a TSV, tab-separated value file, it is a spreadsheet-like file in that it has headings/columns and rows. It's effectively like a single table database.

Being 10Gb I'd prefer not to bulk complete read in all at once as the whole file into memory, due to time taken to do so and limitation of machines' memory size (though I do have 16Gb, 24Gb and 32Gb machines).

Can you advise if, when running queries on the TSV, it is loaded into memory entirely all at once?

@harelba
Copy link
Owner

harelba commented Apr 12, 2023

Hi, sorry for the late reply.

q does load the data into memory for processing, but it does contain an automatic caching feature which might help for large files.

If you run q with the -C readwrite parameter, then a cache for each file will be generated automatically (if the cache file doesn't already exist). That would cause the first execution to be slow, but all additional executions of q for that file will be extremely fast.

In order to create the cache file, you could run q -t -C readwrite "select count(*) from original-file.tsv. This will create another file called original-file.tsv.qsql.

After this preparation step (which will take a considerable time for a 10GB file), you will be able to do either of the following:

  • Run additional q commands which use original-file.tsv (with either -C readwrite or -C read as additional params). The cache file will be detected automatically and used, speeding up queries considerably.
  • Run additional q command directly against the .qsql file. For example q -t "select ... from original-file.tsv.qsql where ...". These will use the cache file directly and will not even require the original tsv file. Since you'll be using the .qsql file directly, you can copy it to another machine and use it directly there as well, without having to copy the original file.

Hope that will help. I'd appreciate it if you can write down your impressions of the speedup here after testing this.

@therobyouknow
Copy link
Author

thank you @harelba

Hope that will help. I'd appreciate it if you can write down your impressions of the speedup here after testing this.

I will let you know! thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants