← All databases

OpenSearch into Apache Arrow with ADBC

OpenSearch 3.8 (SQL plugin), read and written through adbcBridge — the ADBC driver that loads the database's ODBC driver — read-only on Linux and Windows x64. Everything below is the compatibility matrix's record for this row.

Status

Driver and connection string

ODBC driver

OpenSearch SQL ODBC (built from source)

Wire

native ODBC

adbcBridge

0.1.1 (release)

The connection string the matrix used, as the connection strings reference records it (Databases with their own native protocol and ODBC driver); {drv} is the driver library or its name from odbcinst.ini, and the host, port and credentials are the test server's.

Driver={drv};host=127.0.0.1;port=19200;auth=NONE;useSSL=0;

Python

import adbcbridge

# {drv}: the ODBC driver library (path), or its name from odbcinst.ini
conn = adbcbridge.connect(uri="Driver={drv};host=127.0.0.1;port=19200;auth=NONE;useSSL=0;")
with conn.cursor() as cur:
    cur.execute("SELECT * FROM my_table")
    table = cur.fetch_arrow_table()      # pyarrow.Table

Polars and pandas

import polars as pl, pandas as pd

df = pl.read_database("SELECT * FROM my_table", connection=conn)   # Polars, Arrow-native
pdf = pd.read_sql("SELECT * FROM my_table", conn)                   # pandas 2.2+, ADBC connection

pip install adbcbridge brings the driver library; the OpenSearch ODBC driver is installed the way its vendor documents, then named in Driver=. Rust, Go, C# and Java use the same connection string through their ADBC driver managers — see the docs.

What the matrix recorded

search engine reached through its bundled SQL plugin (/_plugins/_sql); the project publishes the driver for Windows and macOS only, so it is built for Linux — its never-compiled POSIX branch needed three source fixes: two compile fixes plus a sem_init() given capacity where WIN32/APPLE use initial (the pop semaphore starts full, so pop() corrupts an empty queue and clear() segfaults on close).

What this stack needed

• Driver quirk handled: its ANSI SQLDriverConnect cannot connect at all — CC_connect() asks for the SQL_ASCII client encoding, which it does not support, unless SQLDriverConnectW set the unicode-driver flag first — and returns SQL_ERROR with an empty diagnostic queue, so adbcBridge retries a connect that failed without saying why through SQLDriverConnectW; one that reported a real error, bad credentials say, is left alone — though through unixODBC no other failed connect ever returns: the driver exports SQLErrorW and hands back the same record on every call, and the driver manager, which prefers SQLError for collecting a failed connect's diagnostics, loops on it forever (opensearch-project/sql-odbc#102).

Read next

• Any text read as a timestamp comes back as today's date, under SQL_SUCCESS