Configurable Query

[kepplertreet]

Hi Guys.

• Wanted to know if the query that connector X generates for requesting data from the database server is configurable. In my case connector X generated a query that didn't seem the best way to do it to me.

• Query : SELECT * FROM (SELECT * FROM <table_name>) AS CXTMPTAB_PART WHERE 1128737626 <= CXTMPTAB_PART.id AND CXTMPTAB_PART.id < 1159318468

• The problem that I have is the nested query is a full table read, which causes a lot of load on the compute resources of the server and can be easily avoided by just removing the nested query.

USE-CASE: Profiling to use as a replacement for SQOOP to avoid interactions with HDFS

[wangxiaoying]

Hi @kepplertreet , currently this is the only way for us to automatically do the query partitioning. But you can manually specified the partitioned query by giving a list of sql queries, like this example:

import connectorx as cx

postgres_url = "postgresql://username:password@server:port/database"

# a list of queries that result in the same schema, connectorx will issue them in parallel and concat their result
queries = ["SELECT * FROM lineitem WHERE PARTITION_FILTER_0",
...,
"SELECT * FROM lineitem WHERE PARTITION_FILTER_N"]

df = cx.read_sql(postgres_url, queries)