support connector-x read_sql

[tempbottle]

Is your feature request related to a problem? Please describe.

I want to read MV from risingwave and join it with some data from pandas. Code like this:

import connectorx as cx
db1 = "postgresql://root:@127.0.0.1:4566/dev"
sql = """
SELECT * FROM mv_avg_speed;
"""
data = cx.read_sql(db1, sql)
# do data join pandas/polars dataframe which is read from other DBMS

this code got error, on ./risedev playground console, error is

ERROR risingwave_frontend::session: failed to parse sql:
COPY (
SELECT * FROM mv_avg_speed;
) TO STDOUT WITH BINARY:
sql parser error: Expected identifier, found: (

the COPY stmt may not support in risingwave.

Describe the solution you'd like

support COPY stmt and return data as postgresql would be great!

Describe alternatives you've considered

psycopg2 works fine, but I want to use connector-x to make things faster.

Additional context

No response

[xxchan]

I found connector-x support specifying protocols:

• https://github.com/sfu-db/connector-x/blob/main/docs/databases/postgres.md#protocols

cursor: Conventional wire protocol (slowest one), recommend to use only when binary and csv is not supported by the source (e.g. Redshift).

• The API doc: https://github.com/sfu-db/connector-x/blob/b26f3b73714259dc55010f2233e663b64d24f1b1/docs/api.md?plain=1#LL14

Could you try if that works? We may also investigate whether we can support COPY TO later :)

[tempbottle]

this code can not work:

sql = """
SELECT * FROM mv_avg_speed;
"""
db1 = "postgresql://root:@127.0.0.1:4566/dev"
data = cx.read_sql(db1, query=sql, protocol="cursor", return_type="polars")
print(data)

as connectorx emit error:

python3.11/site-packages/connectorx/__init__.py", line 330, in reconstruct_arrow 
   raise ValueError("Empty result")

maybe the response of risingwave miss something here, or connectorx can not parse the response.

[neverchanje]

Hi tempbottle, Thanks for raising this issue. This requirement looks very interesting!

From our end, supporting COPY TO STDOUT is not difficult. What could bring a little complexity is supporting Postgres's COPY protocol: https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-COPY I've created an issue for this task: https://github.com/risingwavelabs/risingwave/issues/7983. We can prioritize development if it really blocks your adoption of RisingWave.

[tempbottle]

That will be great! work with connector-x is rusty and fast.

[jon-chuang]

maybe the response of risingwave miss something here, or connectorx can not parse the respons

I wonder if this is due to some missing features in our binary PSQL response. https://github.com/risingwavelabs/risingwave/issues/8213