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?
1
-
1$\begingroup$ Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. $\endgroup$– Community BotCommented Jul 15, 2023 at 15:25
Add a comment
|
1 Answer
$\begingroup$
$\endgroup$
2
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 pandas command
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 multiprocessor
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
feather - 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 parquet. Performance-wise feather is way faster than parquet. I've never used feather BTW I use pickle or parquet
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.
-
1$\begingroup$ Thank you, the idea is using dask distributed with multiple machines, then save parquet. But parquet files for a fastapi application is it good idea? $\endgroup$– Jack2501Commented Jul 15, 2023 at 16:01
-
1$\begingroup$ Please note, "accepts" (green tick-button) are much more welcome than thanks. Answered above $\endgroup$– M__ ♦Commented Jul 15, 2023 at 16:06