Why dask's read_sql_table requires a index_col parameter?

user_13899801 0
Viewed 1675

I'm trying to use the read_sql_table from dask but I'm facing some issues related to the index_col parameter. My sql table doesn't have any numeric value and I don't know what to give to the index_col parameter.

I read at the documentation that if the index_col is of type "object" I have to provide the "divisions" parameter, but I don't know what are the values in my index_col before reading the table.

I'm really confused. Don't know why I have to give an index_col when using read_sql_table but don't have to when using read_csv.

dask-dataframe dask
2 Answers

I've found in certain situations it's easiest to handle this by scattering DataFrame objects out to the cluster by way of pd.read_sql and its chunksize argument:

from dask import bag as db

sql_text = "SELECT ..."
sql_meta = {"column0": "object", "column1": "uint8"}
sql_conn = connect(...)
dfs_futs = map(client.scatter,               # Scatter each object to the cluster
               pd.read_sql(sql_text, 
                           sql_conn, 
                           chunksize=10_000, # Iterate in chunks of 10,000
                           columns=list(sql_meta.keys())))

# Now join our chunks (remotely) into a single frame.
df = db.from_sequence(list(dfs_futs)).to_dataframe(meta=sql_meta)

This is nice since you don't need to handle any potential drivers/packages that would be cumbersome to manage on distributed nodes and/or situations where it's difficult to easily partition your data.

Just a note on performance, for my use case we leverage our database's external table operations to spool data out to a CSV and then read that with pd.read_csv (it's pretty much the same deal as above) while a SELECT ... FROM ... WHERE compared to the way Dask parallelizes and chunks up queries, can be acceptable performance-wise since there is a cost to performing the chunking inside the database.

user_1746520

Dask needs a way to be able to read the partitions of your data independently of one-another. This means being able to phrase the queries of each part with a clause like "WHERE index_col >= val0 AND index_col < val1". If you have nothing numerical, dask cab't guess reasonable values for you, you can still do this if you can determine a way to provide reasonable delimiters, like list(string.ascii_letters). You can also provide your own complete WHERE clauses if you must.

Note that OFFSET/LIMIT does not work for this task, because

  • the result is not in general guaranteed for any given inputs (this behaviour is database implementation specific)
  • getting to some particular offset is done by paging through the results of a while query, so the server has to do many time the amount of work necessary
user_38211540
Related