本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/index。中文翻译正在分批进行中。

SQL reference

SQL statements, expressions and data types supported by LanceDB Enterprise.

LanceDB Enterprise supports SQL through an Arrow Flight SQL endpoint. This reference covers statement syntax, data types and expressions. To set up a client and run your first query, see SQL.

The dialect

LanceDB uses Apache DataFusion for SQL planning and execution. These pages describe LanceDB's supported statements and extensions. DataFusion's reference is useful for expression syntax, but does not establish that every DataFusion statement or feature is exposed by LanceDB.

Connecting

Use Python's execute_query or a Flight SQL client after configuring the Enterprise connection. TypeScript applications use a Flight SQL client for SQL queries.

reader = db.execute_query("SELECT id, text FROM my_docs LIMIT 10")
for batch in reader:
    print(batch)

SQL results arrive as Arrow record batches. Iterating over the reader avoids collecting the entire result in application memory. See Read and manage results for more.

Usage

The endpoint executes one statement per request and is meant for the queries and DDL/DML pages below. Reach for the SDKs instead for table-management operations that have no SQL form, such as a merge insert. See What SQL cannot do.

Reference

DDL, by object

Database	CREATE, DESCRIBE, SHOW, ATTACH, DROP DATABASE
Namespace	CREATE, ALTER, DESCRIBE, SHOW, DROP SCHEMA/NAMESPACE
Table	CREATE, DESCRIBE, ALTER, CLONE, DROP TABLE
View	CREATE VIEW, DROP VIEW
Materialized view	CREATE MATERIALIZED VIEW, SHOW MATERIALIZED VIEWS
Secret	CREATE, ALTER, DESCRIBE, SHOW, DROP SECRET
Storage Integration	CREATE, DESCRIBE, SHOW, DROP STORAGE INTEGRATION
Index	CREATE INDEX, DROP INDEX
Function	CREATE FUNCTION, SHOW FUNCTIONS, DROP FUNCTION
Job	SHOW JOBS, SHOW JOB EVENTS, DESCRIBE JOB, SHOW ERRORS, PAUSE/RESUME/CANCEL JOB

DML

Insert	INSERT INTO, including INSERT ... SELECT
Update	UPDATE ... SET
Delete	DELETE FROM
Copy	COPY (refused by recent deployment versions)
Refresh column	filling a computed column
Refresh materialized view	bringing a view's stored result up to date

DQL

Data types	how Lance columns appear to SQL, and pseudo-columns
Select	projection, filtering, ordering, set operations, CTEs
Subqueries	scalar, IN, EXISTS, correlated, derived tables
Joins	inner, outer, cross, and namespace-qualified paths
Aggregation	GROUP BY, HAVING, the aggregate set
Window functions	OVER, framing, ranking and offset functions
Sampling	TABLESAMPLE, by rows or by fragments
Changes	CHANGES: rows appended since an earlier version
Full-Text Search	the fts() table function, and its query forms
Operators	comparison, logical, arithmetic, literals, precedence
Functions	where to find each kind, and table functions
Scalar functions	the per-row catalogue, and what Lance accelerates
Explain	EXPLAIN, EXPLAIN ANALYZE, EXPLAIN VERBOSE

SQL limitations and alternatives

Vector search	See SQL vector search for supported query forms and server-version requirements. The SDK query builder also supports vector search.
Merge insert	no SQL form: it needs a source relation and a per-match policy
information_schema	not enabled, so SHOW TABLES and SHOW COLUMNS fail
COPY	refused by recent deployment versions (see Copy)
PREPARE / EXECUTE	preparation is a protocol feature, not a statement
Run on a local database	no engine to plan the statement

See Limits for the bounds these pages enforce.