Plan for supporting data sources

[wangxiaoying]

We plan to add more RDBMS to connector-x as data sources. Here is a list that we are planning to support next:

Stage 1 (leading open source sources)

• MariaDB (MySQL-derivative)
• MySQL

Stage 2 (popular commercial sources)

• Oracle
• Microsoft SQL Server
• Microsoft Azure SQL Database

Stage 3 (popular cloud-based PaaS that can leverage completed work-to-date)

• Amazon Redshift (Postgres-derivative)
• Amazon Aurora (MySQL-derivative)
• Google BigQuery

Stage 4 (popular open source sources)

• Hive
• Impala
• Spark SQL
• Presto / Trino

Backlog:

• SQLite
• DB2
• Snowflake

Feel free to reply here if you think we should add any other RDBMS / data sources (e.g. S3), or share your opinion about the priority of implementation!

[pablojim]

Clickhouse support would be great!

[pcoccoli]

I agree on Clickhouse!

[overcoil]

The good news is Clickhouse supports the MySQL wire protocol and development for MySQL is under way already.

[dovahcrow]

Hi @pcoccoli, since Clickhouse uses the MySQL protocol, you can use the MySQL support in our 0.2.0a2 release to download data from Clickhouse.

[dovahcrow]

We currently support these data types for MySQL: Int, BigInt, Double, Datetime, Date, Time, Decimal, Char, VarChar.

[wolfgangcolsman]

Trino support would be great as well!

[andreapiso]

Trino itself only sends you data through REST API so probably not much advantage in using connectorx? Since the bottleneck is o their side.

The trino jdbc and odbc call the REST API and convert from json...

[pcoccoli]

I think SQLite should be prioritized higher since it's so common and easy to use.

[dovahcrow]

Hi @pcoccoli we are pleased to inform you that we just released the connectorx 0.2.0a2 version, which includes MySQL and SQLite support. You can try it using pip install -U connectorx==0.2.0a2.

[pd2871]

@dovahcrow can you write the code sample to connect the sqlite3? I have a local .db file and I want to connect it with connectorx.

[wangxiaoying]

Please check out our doc here.

[mrtztg]

Great. I'm waiting for MySQL support.

[dovahcrow]

Hi @mrtztg we are pleased to inform you that we just released the connectorx 0.2.0a2 version, which includes MySQL and SQLite support. You can try it using pip install -U connectorx==0.2.0a2.

A simple benchmark on the TPC-H lineitem table (8G) shows we are 36x faster than pandas + PyMySql (shown in the gif), 15x faster than pandas+mysqlconnector (the MySQL connector provided by MySQL) and 12x faster than pandas + mysqlclient (the fastest python MySQL connector).

[mrtztg]

Thanks @dovahcrow ❤️❤️❤️. I'm going to try it.

[mrtztg]

It runs ~40% faster without using partitioning.
This is good. My query is complex. I will try to use partitioning and give the result here.

[jokyv]

I tried with connectorx==0.2.0a2 but I got "PanicException: not implemented: MYSQL_TYPE_TIMESTAMP"

[dovahcrow]

@jokyv Yep we haven't added the timestamp type to the code. Will do that in the next release.

[ericjohn]

KDB+/Q support would be nice to have!

[JazzFan]

I would like to ask for BigQuery to have a higher priority

[cawoodjm]

When do you think Oracle and Microsoft SQL Server will be ready for testing or generally available?

[wangxiaoying]

Hi @cawoodjm, we just released the connectorx-0.2.1a1 which includes both Oracle and Microsoft SQL Server. You can try it using pip install -U connectorx==0.2.1a1. The benchmark result for Oracle and Microsoft SQL Server are also uploaded respectively.

If all tests go well, we plan to release the 0.2.1 version in early October. Please feel free to have a try and let us know if you found any issue.

[abel-diaz]

I am unable to connect to Oracle. I am using Connector X v. 0.2.1a1
I have the following creds
host
port
servicename
username
password

According to the connectorx instructions i need to have
(postgres|postgressql|mysql|mssql|sqlite)://username:password@addr:port/dbname

Using my above creds, I have
cstr = 'oracle://username:password@host:port/servicename'
query ' 'select * from table'

When I run
df = cx.read_sql(cstr,query)

I receive RuntimeError: timed out waiting for connection: OCI Error: ORA-01017: invalid username/password; logon denied
I am able to connect with sqlalchemy using my creds

How do I connect conncetorx with Oracle?
Thank you,
Abel

[cawoodjm]

I am experiencing similar issues, I am unable connect to an oracle environment no matter how I configure the connection string.

[wangxiaoying]

Hi @cawoodjm , this issue has been solved here: #130. Not sure whether our newest alpha version (now is 0.2.2-alpha.1) can help.

[farhat-nawaz]

Is daterange type for postgres not supported yet? I'm getting a PanicException: not implemented: daterange error.

[wangxiaoying]

Hi @farhat-nawaz , we do not support daterange type in postgres right now since we didn't find the parsing of this type implemented in the underlying driver. All the types we support are listed here.

[matthieugross-bib]

Hi, I'm trying to connect to a Microsoft Azure SQL Database. It works fine using pymssql:

conn = pymssql.connect(user='a',
                                        password='b',
                                        database='c',
                                        host='d.database.windows.net',
                                        port=e)
query = "SELECT * FROM INFORMATION_SCHEMA.TABLES order by TABLE_SCHEMA,TABLE_NAME"
schema = pd.read_sql(query,conn)
schema.head()

But I get a "Timed out in bb8" runtime error using the following:

import connectorx as cx
conn_url = "mssql://a:b@d.database.windows.net:e/c"
query = "SELECT * FROM INFORMATION_SCHEMA.TABLES order by TABLE_SCHEMA,TABLE_NAME"
df = cx.read_sql(conn_url, query)

See:

.../site-packages/connectorx/__init__.py in read_sql(conn, query, return_type, protocol, partition_on, partition_range, partition_num, index_col)
    121             queries=queries,
    122             protocol=protocol,
--> 123             partition_query=partition_query,
    124         )
    125         df = reconstruct_pandas(result)

RuntimeError: Timed out in bb8

Currently using version '0.2.3'.

Any idea?
Thanks
Matt

[wangxiaoying]

Hi, it seems like we currently have some issue with Azure database. I will redirect it to this discussion: #181 (reply in thread).

[anybodysguest]

I am actually getting the same error. I am trying to connection to a MS SQL Server in AWS EC2. I used the example shown here:

There is a same trusted connection that is shown that looks like this: mssql://host:port/db?encrypt=true&trusted_connection=true

So, in my case I replace the values in the same above with my own. For example:
Server/Host = MyMsSQLServer
Port=1433
Database = MyDatabase

Now the connection string looks like this:
mssql://MyMsSQLServer:1433/MyDatabase?encrypt=true&trusted_connection=true

When I attempt to connect I am getting the "Timed out in bb8" that @wangxiaoying pointed out.

I'd like to use ConnectionX because I am trying our Polars. Polars, apparently using ConnectionX under the covers.

Any ideas?

[wangxiaoying]

Hi @anybodysguest , does this comment (of enabling TCP/IP) help? #181 (reply in thread)

[anybodysguest]

I'm pretty sure all of our MS SQL Servers have TCP/IP enabled.

[wKollendorf]

Hi,

are there any plans for integrating impala?

Thanks

[wangxiaoying]

Hi @wKollendorf , we are currently working on supporting ODBC #215 , which could be used to access impala when it's ready.

[grantmwilliams]

I think integrating with S3 or Athena would be pretty useful

[overcoil]

Are Trino (formerly PrestoSQL) or PrestoDB of use/interest? They are upstream of Athena but different.

[alec-heif]

trino would be great!

[ameetnsharma]

Hi. I'm getting this error in AWS:

pyo3_runtime.PanicException: called Result::unwrap() on an Err value: Os { code: 11, kind: WouldBlock, message: "Resource temporarily unavailable" }

So I'm in a loop that's calling

df = cx.read_sql(f"mysql://{db_username}:{db_password}@{db_host}:3306/{db_name}", query)

where query is changing to cover different date ranges.

The loop works for 336 times then the exception happens. On the 337th time it gives this exception. So it seems to be unrelated to the particular date range I'm pulling from... if I start the loop at a different starting date... it still runs exactly 336 times then crashes on the 337th time.

Appreciate any help. Thanks.

EDIT: Very similar issue on another resource in case it's of any help:

huggingface/tokenizers#444

I was able to fix the issue on my end by pulling more data per loop and reducing the number of loops. Still, might be something to look at.

[wangxiaoying]

Hi @ameetnsharma,

I tried to run a query in a loop 400 times on an AWS instance to fetch data from mysql, but it works fine.

I think this error is more similar to this issue: openethereum/parity-ethereum#10009 where might be caused by not enough resource (RAM, reaching to thread limit, etc) when tokio spawn new thread. Can you check whether your instance's status when you see the error?

[ameetnsharma]

Hi Mr. Wang. I was able to fix the problem. It was likely something wrong with my instance. But I'll keep a lookout if it happens again. Thanks. Excellent tool! Best way to read from sql into pandas.

…

-Ameet

________________________________ From: Xiaoying Wang ***@***.***> Sent: Saturday, May 7, 2022 11:47 PM To: sfu-db/connector-x ***@***.***> Cc: ameetnsharma ***@***.***>; Mention ***@***.***> Subject: Re: [sfu-db/connector-x] Plan for supporting data sources (Discussion #61) Hi @ameetnsharma<https://github.com/ameetnsharma>, I tried to run a query in a loop 400 times on an AWS instance to fetch data from mysql, but it works fine. I think this error is more similar to this issue: openethereum/parity-ethereum#10009<openethereum/parity-ethereum#10009> where might be caused by not enough resource (RAM, reaching to thread limit, etc) when tokio spawn new thread. Can you check whether your instance's status when you see the error? — Reply to this email directly, view it on GitHub<#61 (reply in thread)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AKFU3CUG73NHNFVOP32K2O3VI5BOXANCNFSM422E6IZQ>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[sfc-gh-paubrey]

Snowflake please!

[verramilli]

hi @wangxiaoying : Any update on snowflake? ConnectorX works very well for mysql but would like to use it for Snowflake as well. Thanks.

[zykezero]

amazing news

[GeraudK]

@wangxiaoying would a valid option to have a fallback when the client driver isn't supported by ConnectorX to use a SQLAlchemy connectable like pandas.read_sql does?

[MarvaZy]

Do you have an estimation when the support for snowflake will be available though? I see the branch you mentioned in the comments is not done yet

[colelandolt]

Curious as to whether support for snowflake is still in the works and if so, is there is a timeline? I see that the branch mentioned above hasn't been contributed to in almost one year. Thanks for all of your work on this thus far!

[RxL-Mudit-Singh]

Reading data from Oracle tables and storing it in a spark data frame will be of great help

[satyakommula96]

Apache Calcite Avatica support would be great!

[daincrawford]

Seems like you could make a lot of people happy if they had ODBC read-only support for existing viable DSN's. And then work on possible to_sql later.
Keep up the great work. Thank you @wangxiaoying.

[romanzdk]

Hello, any update on Trino connector?

[agalvanz]

Is it possible to add Vertica support?

[SantiScandorla]

I would like to connect SQL Server Management Studio with Python using polars and connectorx function:

I wrote this chunk of code

import connectorx as cx 
import urllib.parse

conn = 'mssql://sa:{}@{}:5432/Planning_insight?encrypt=true&trusted_connection=true'.format(
    urllib.parse.quote_plus('MYPASSWORD'),
    urllib.parse.quote_plus('MYSERVER\SQLEXPRESS')
    )                                                                        
str_query = f"SELECT * FROM raw.calendar"

cx.read_sql(conn,str_query)

but after 30 second, it returned this error

RuntimeError Traceback (most recent call last) Cell In[1], line 10 4 conn = 'mssql://sa:{}@{}:5432/Planning_insight?encrypt=true&trusted_connection=true'.format( 5 urllib.parse.quote_plus('MYPASSWORD'), 6 urllib.parse.quote_plus('MYSERVER\SQLEXPRESS') 7 )
8 str_query = f"SELECT * FROM raw.calendar" ---> 10 cx.read_sql(conn,str_query)

File c:\Users\lorenzsc\AppData\Local\miniconda3\envs\padp2\lib\site-packages\connectorx_init_.py:224, in read_sql(conn, query, return_type, protocol, partition_on, partition_range, partition_num, index_col) 221 except ModuleNotFoundError: 222 raise ValueError("You need to install pandas first") --> 224 result = _read_sql( 225 conn, 226 "pandas", 227 queries=queries, 228 protocol=protocol, 229 partition_query=partition_query, 230 ) 231 df = reconstruct_pandas(result) 233 if index_col is not None:

RuntimeError: Timed out in bb8

Can anyone help me?

[pangjunrong]

Hey @SantiScandorla, SSL was previously unsupported for MSSQL which resulted in a generic RuntimeError when encrypt=true&trusted_connection=true was specified. As of 0.4.0, you should be able to connect with no issues now by adding trust_server_certificate=true. together with the other parameters.

[mvpalheta]

I suggest support for Netezza

[trichie]

Is there any chance for DB2 support to be added in the near future?

[nicolaslattuada]

DuckDb and azure data explorer would be nice too !

[j0bekt01]

Are there any plans to also support writing to DBs?

[hanibounoua]

adding teradata to supported dbms would be great !!!

[vebalaji]

Does this currently support connecting to and retrieving data from Amazon Aurora PostgreSQL tables?

[TsinlingLi]

Can the native protocol of clickhouse be supported? It is not very practical to obtain data from clickhouse using the MySQL protocol because many places control port numbers, and in most cases, only the native port numbers are opened to the external network

[mhvsa]

Hi!

I appreciate your work, fantastic job.

Any plans supporting oracle wallet as auth in oracle connection? If it’s supported, then some notion in the documentation would be helpful.

Thanks!

[pangjunrong]

Hey @mhvsa, assuming you have your tnsnames.ora setup and ready to go with your wallet, you can use the alias connection method. This is found in the documentation under Oracle TNS Names (Alias) Connection.

You can find more information about configuring your wallet with TNS here.

[Feromond]

Definitely am interested to see if there could be some support added for databricks sql, or if there is a similar protocol that exists already that could work for connecting and making queries to data bricks.