SQL Queries in LanceDB Enterprise
Note
This is a preview feature that is only available in LanceDB Enterprise.
Our solution includes 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 a 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.
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.
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"},
)
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()
print(run_query("SELECT * FROM flights WHERE origin = 'SFO'"))
```typescript import { Client } from "@lancedb/flightsql-client";
const client = await Client.connect({ host: "your-enterprise-endpoint:10025", username: "lancedb", password: "password", });
const result = await client.query("SELECT * FROM flights WHERE origin = 'SFO'");
// Results are returned as plain JS/TS objects and we create an interface // here for our expected structure so we can have strong typing. This is // optional but recommended. interface FlightRecord { origin: string; destination: string; }
const flights = (await result.collectToObjects()) as FlightRecord[]; console.log(flights);