Doing complex sql query on Chembl database and it takes 40 minutes on the machine from lab. I was thinking using dask, extract each table and export to parquet, when do the data processing . Is this a good approach or there is a better alternative?
I suspect the issue is you want to parallelise (multi-threading really) the job to reduce run time.
parquet is recommended. It is simply an archive format that allows compression and is often used with a
pandas dataframe. Use
parquet to store data by all means, but thats easy via
pyarrow and the
df.to_parquet('/User/OP/location/file,parquet' ,engine='pyarrow', compression='gzip')
You will need to install
pyarrow via pip or conda, there is a faster version (forget its name, something like
fastparquet or something used by default). Yeah it's a very good archiving format, in fact its industrial standard and I should use it more.
Python has loads of ways to parallelise of which dask is one way. dask is an entire framework. To leverage dask to parallelise postgres I would refer to this post.
I don't use dask, I am aware it is a solution in high performance Python. Personally I would use this approach directly via
from psycopg2 import connect import multiprocessor
... etc ...
I would suggest its horses for courses, I simply know how to multiprocess in Python so I don't bother with dask.
To answer the question in summary
- parquet is the best, if SPEED is not important. If speed is important then
- dask is mainstream within high performance Python and its doable. There's no reason not to
- There are other multithread Postgress approaches and thats what I would do, but if you just getting into parallelisation you may as well use dask. It will take some learning though.
If you're not into parallelisation and you're not writing a data pipeline for publication I would simply consider getting a machine with more RAM. It shouldn't be taking that length of time, parallelisation will solve the run time - no question - but it's the time it will take to get to grips with a parallel solution. What is likely occurring is the job and database size is exceeding the RAM capacity of the machine. At a wild random guess a 16 or 32 Meg RAM chip would collapse the runtime to a few minutes and thats what you are looking for.
From the comments.
parquet is really for data compression and archiving. The issue is I/o is reasonably fast but not lightening fast. It is probably slower than
pickle (it will be slower if you're compressing via gzip as well).
For a 'temporary' save (couple months) and really fast i/o then its
feather. There is no dispute about that.
If you are keeping your data a long time and need to save space its
feather is way faster than
parquet. I've never used
feather BTW I use
To give some idea a 500Meg flat file compressed an archived via
parquet resulting in a 50Meg file, and will take several minutes to write/import. Thats 'cause it's doing loads of stuff. My suspicion is that this too slow for you.