CSV export from SQL server DB using Connector X

[vishalkhondre]

Hi Team,

I'm using below code to export data into CSV.
I'm trying export ~4 M records and its taking ~25 to 30 minutes.
Is there a way that I can provide batchsize in below code to speedup the export in CSV?
Also unfortunately there is no any column that I can use for partition. I believe for partition we need integer datatype column.

df = cx.read_sql(url, tabledetails.query)
df.to_csv(output_file, mode=mode, header=header, index=False)

[wangxiaoying]

Hi @vishalkhondre , I think it depends on the bottleneck of the process. (which part is more time costly: read_sql or to_csv)?

If read_sql is the bottleneck, and if there is no numerical datatype column, is it possible for you to manually partitioning the query like in this example:

import connectorx as cx

postgres_url = "postgresql://username:password@server:port/database"
queries = ["SELECT * FROM lineitem WHERE l_orderkey <= 30000000", "SELECT * FROM lineitem WHERE l_orderkey > 30000000"]

cx.read_sql(postgres_url, queries)

In this case the two queries will be run in parallel and speed up the process.

If to_csv is the bottleneck, maybe you can try to use pyarrow to do the work, which should be faster than pandas like mentioned in this article. You can specify return_type=arrow2 in read_sql so connectorx will return you the arrow table.