Pinot DB-API and SQLAlchemy Dialect

Warnings

• breaking Python 3.9 support was dropped in `pinotdb` version 7.0.0.
  Fix: Upgrade your Python environment to 3.10 or newer (current requirement: >=3.10, <4).
• breaking Python 3.8 support was dropped in `pinotdb` version 6.0.0.
  Fix: Upgrade your Python environment to 3.9 or newer before upgrading to `pinotdb` 6.0.0, or 3.10+ for later versions.
• breaking `pinotdb` version 8.0.0 introduced compatibility with SQLAlchemy 2.0. If you are using SQLAlchemy 1.x, you may need to adjust your code or pin to an older `pinotdb` version.
  Fix: Review SQLAlchemy 2.0 migration guides and update your SQLAlchemy-dependent code. Alternatively, pin `pinotdb` to a version prior to 8.0.0 if you must remain on SQLAlchemy 1.x.
• gotcha Versions prior to 9.1.1 had a potential issue with clear-text logging of sensitive information.
  Fix: Upgrade to `pinotdb` 9.1.1 or newer to benefit from the fix addressing clear-text logging of sensitive data.
• gotcha `pinotdb` versions >= 0.3.2 use the Pinot SQL API and require Apache Pinot server version >= 0.3.0. Older `pinotdb` versions (0.2.x) use the deprecated PQL API.
  Fix: Ensure your Apache Pinot cluster is running a version that supports the SQL API (0.3.0 or later) when using recent `pinotdb` clients. For older Pinot clusters, consider using `pinotdb` 0.2.x if PQL is necessary, but be aware of missing newer features.

Install

• pip install pinotdb==9.1.1 Install latest stable version

Imports

• connect

  from pinotdb import connect

  For direct DB-API 2.0 connections to Pinot.
• create_engine

  from sqlalchemy.engine import create_engine

  For using the SQLAlchemy dialect to connect to Pinot.

Quickstart

This example demonstrates how to connect to an Apache Pinot broker using the DB-API 2.0 interface and execute a simple SQL query. It fetches the count of records from a hypothetical 'baseballStats' table and prints the results and query statistics. Authentication details are optional and can be provided via environment variables.

import os
from pinotdb import connect

# Configure connection details (replace with your Pinot broker host and port)
PINOT_HOST = os.environ.get('PINOT_BROKER_HOST', 'localhost')
PINOT_PORT = int(os.environ.get('PINOT_BROKER_PORT', '8000')) # Default Pinot QuickStart broker port
PINOT_PATH = os.environ.get('PINOT_QUERY_PATH', '/query/sql')
PINOT_SCHEME = os.environ.get('PINOT_SCHEME', 'http')
PINOT_USERNAME = os.environ.get('PINOT_USERNAME', '')
PINOT_PASSWORD = os.environ.get('PINOT_PASSWORD', '')

try:
    if PINOT_USERNAME and PINOT_PASSWORD:
        conn = connect(
            host=PINOT_HOST, 
            port=PINOT_PORT, 
            path=PINOT_PATH, 
            scheme=PINOT_SCHEME,
            username=PINOT_USERNAME,
            password=PINOT_PASSWORD
        )
    else:
        conn = connect(host=PINOT_HOST, port=PINOT_PORT, path=PINOT_PATH, scheme=PINOT_SCHEME)

    curs = conn.cursor()
    curs.execute("""
        SELECT count(*) FROM baseballStats LIMIT 10
    """)

    print("Query Results:")
    for row in curs:
        print(row)

    print(f"Time taken: {curs.timeUsedMs} ms")
    print(f"Query Stats: {curs.query_stats}")

except Exception as e:
    print(f"An error occurred: {e}")
    print("Please ensure a Pinot broker is running and accessible at {PINOT_SCHEME}://{PINOT_HOST}:{PINOT_PORT}")