DOCSEARCHbyAlgolia
Submit Docs

ConnectorX Documentation

repository·main·Indexed 25 days ago

https://github.com/sfu-db/connector-x

A high-performance data connector written in Rust designed to load data from various databases into Python dataframes (Pandas, Polars, etc.) and Arrow formats. It supports parallel data downloading via partitioning, federated queries across multiple databases, and streaming via Arrow RecordBatches. Supported backends include PostgreSQL, MySQL, SQLite, MSSQL, Oracle, BigQuery, DuckDB, and Redshift.

Tokens
17.4K
Snippets
39
Records
104
Agent score
80%

What's inside ConnectorX

  2. Supported databases in ConnectorX

    main

    ConnectorX supports high-performance data retrieval from the following databases:

    • BigQuery
    • MsSQL
    • MySQL
    • Oracle
    • Postgres
    • SQLite
    • Trino
    • ClickHouse

    Each database has specific configuration requirements and type conversion behaviors between database types and Pandas types.

  3. Enable Rust logging in Python

    main

    To view detailed Rust logs while using ConnectorX in Python, set the RUST_LOG environment variable before importing connectorx. You can specify log levels for connectorx and connectorx_python.

    import os
os.environ["RUST_LOG"]="connectorx=debug,connectorx_python=debug"
import connectorx as cx

df = cx.read_sql(conn, query)

  4. Build ConnectorX from source code

    main

    To build ConnectorX from source, follow these steps:

    1. Install tools:
      • Install Rust: curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh
      • Install just: cargo install just
      • Install Poetry: pip3 install poetry
    2. Clone the repository: git clone https://github.com/sfu-db/connector-x.git
    3. Set Rust version: Install and switch to the specific Rust version required by the project (check .github/workflows/release.yml for the latest version).
      rustup install {version}
rustup override set {version}
    4. Install system dependencies: Refer to the .github/workflows/release.yml file for the specific dependencies required for your operating system.
    5. Install Python dependencies: just bootstrap-python
    6. Build the wheel: just build-python-wheel

    Important Notes for Building

    • Windows users: You might need to set OPENSSL_NO_VENDOR=1 during compilation.
    • Python Shared Libraries: A dynamic library is required for the Python installation. If using pyenv, you must install Python with shared libraries enabled using: PYTHON_CONFIGURE_OPTS="--enable-shared" pyenv install {version}
    git clone https://github.com/sfu-db/connector-x.git
rustup install {version}
rustup override set {version}
just bootstrap-python
just build-python-wheel

  6. Generate TPC-H data

    main

    To generate TPC-H benchmark data, compile the tpch-kit and use dbgen. You can generate all tables or target specific tables like LINEITEM using the -T option.

    # 1. Download and compile TPC-H toolkit
git clone https://github.com/gregrahn/tpch-kit.git
cd tpch-kit/dbgen && make MACHINE=LINUX DATABASE=POSTGRESQL

# 2. Generate data (Scale factor 10)

# Generate all tables
./dbgen -s 10

# Alternatively, only generate LINEITEM table
./dbgen -s 10 -T L

  7. Connect to MsSQL using ConnectorX

    main

    To read data from MsSQL, use the cx.read_sql function with a connection URI. Note that for MsSQL, you do not need to specify a protocol in the URI.

    Important: If your password contains special characters, you must sanitize it using urllib.parse.quote_plus to ensure the connection string is valid.

  9. Connect to BigQuery using connectorx

    main

    To connect to BigQuery, you must provide a path to a Google Cloud Platform authentication JSON file within the connection string. The connection string format is bigquery://<path_to_auth_json>.

    Note: BigQuery does not require a protocol specification in the connection string beyond the bigquery:// prefix.

    import connectorx as cx
authentication_file_path = '/home/user/path/auth.json'      # path to your authentication json file
conn = 'bigquery://' + authentication_file_path             # connection token
query = 'SELECT * FROM `database.dataset.table`'            # query string
cx.read_sql(conn, query)                                    # read data from BigQuery

  11. Connect to Trino using ConnectorX

    main

    To read data from Trino, use the trino+https:// or trino+http:// protocol in your connection string.

    Security Notes:

    • Using trino+http disables TLS for the connection.
    • Trino requires TLS for basic authentication credentials.
    • If you are using self-signed certificates, append ?verify=false to your connection string.
    import connectorx as cx
conn = 'trino+https://username:password@server:port/catalog'     # connection token
query = "SELECT * FROM table"                                    # query string
cx.read_sql(conn, query)                                         # read data from Trino

  12. Choose a partition column for optimal performance

    main

    The partition_on parameter specifies the column used to partition the query. To achieve maximum performance:

    1. Even Distribution: Choose a numerical column that is evenly distributed so that each partitioned query returns a similar number of rows.
    2. Indexing: Consider whether the column is indexed in your source database, as this affects performance.
    3. Manual Partitioning: If the automatic partitioning method does not meet your needs, you can manually partition the query. ConnectorX will return a single combined dataframe containing all results from the list of queries provided.