Query with SQL

SQL query capabilities in LanceDB Enterprise for analytical queries and data exploration.

Enterprise-only

LanceDB Enterprise comes with an SQL endpoint that can be used for analytical queries and data exploration. The SQL endpoint is designed to be compatible with the Arrow FlightSQL protocol, which allows you to use any Arrow FlightSQL-compatible client to query your data.

Installing the client

There are Flight SQL clients available for most languages and tools. If you find that your preferred language or tool is not listed here, please reach out to us and we can help you find a solution. The following examples demonstrate how to install the Python and TypeScript clients.

Python

# The `flightsql-dbapi` package provides a Python DB API 2 interface to the
# LanceDB SQL endpoint. You can use it to connect to the SQL endpoint and
# execute queries directly and get back results in pyarrow format.

pip install flightsql-dbapi

Usage

LanceDB uses the powerful DataFusion query engine to execute SQL queries. This means that you can use a wide variety of SQL syntax and functions to query your data. For more detailed information on the SQL syntax and functions supported by DataFusion, please refer to the DataFusion documentation.

Setting Up the Client

Establish a connection to your LanceDB Enterprise SQL endpoint using your preferred FlightSQL client:

Python

from flightsql import FlightSQLClient

client = FlightSQLClient(
    host="your-enterprise-endpoint",
    port=10025,
    insecure=True,
    token="DATABASE_TOKEN",
    metadata={"database": "your-project-slug"},
    features={"metadata-reflection": "true"},
)

Executing a Query

Run SQL queries against your LanceDB tables. Different clients may handle the FlightSQL protocol differently:

Python

def run_query(query: str):
    """Simple method to fully materialize query results"""
    info = client.execute(query)
    if len(info.endpoints) != 1:
        raise Error("Expected exactly one endpoint")
    ticket = info.endpoints[0].ticket
    reader = client.do_get(ticket)
    return reader.read_all()

result = run_query("SELECT * FROM flights WHERE origin = 'SFO'")

Processing Results

Handle the query results returned by your FlightSQL client:

Python

print(result)

Inspecting query plans

The SQL endpoint runs queries through DataFusion, which means DataFusion’s EXPLAIN family of statements is available unchanged. They’re the SQL counterpart of the Python/TypeScript explain_plan and analyze_plan methods and are useful for the same things: confirming index usage, checking filter pushdown, and finding the slow operator in a query that’s underperforming.

Statement	What it returns
EXPLAIN <query>	Logical and physical plan, without executing the query.
EXPLAIN ANALYZE <query>	Executes the query and annotates each operator with runtime metrics (rows, timing).
EXPLAIN VERBOSE <query>	Adds intermediate optimizer plans on top of EXPLAIN.

Run them through the same client you use for regular queries — the result is a small Arrow table with plan_type and plan columns:

Python

plan = run_query(
    "EXPLAIN ANALYZE SELECT origin, destination "
    "FROM flights WHERE origin = 'SFO' LIMIT 100"
)
for row in plan.to_pylist():
    print(row["plan_type"])
    print(row["plan"])
    print()

The operators that show up in the SQL plan are the same ones documented on the Optimize Query Performance page (LanceScan, ScalarIndexQuery, KNNVectorDistance, ANNIvfPartition, and so on), so the same reasoning about index coverage and filter pushdown applies — just read the plan from a SQL client instead of a query builder.