SELECT¶

Query data from Lance tables using SQL or DataFrames.

Basic Queries¶

SELECT * FROM users;

# Load table as DataFrame
users_df = spark.table("users")

# Use DataFrame operations
filtered_users = users_df.filter("age > 25").select("name", "email")
filtered_users.show()

// Load table as DataFrame
val usersDF = spark.table("users")

// Use DataFrame operations
val filteredUsers = usersDF.filter("age > 25").select("name", "email")
filteredUsers.show()

// Load table as DataFrame
Dataset<Row> usersDF = spark.table("users");

// Use DataFrame operations
Dataset<Row> filteredUsers = usersDF.filter("age > 25").select("name", "email");
filteredUsers.show();

Select Specific Columns¶

SELECT id, name, email FROM users;

Query with WHERE Clause¶

SELECT * FROM users WHERE age > 25;

Aggregate Queries¶

SELECT department, COUNT(*) as employee_count
FROM users
GROUP BY department;

Join Queries¶

SELECT u.name, p.title
FROM users u
JOIN projects p ON u.id = p.user_id;

Querying Blob Columns¶

When querying tables with blob columns, the blob data itself is not materialized by default. Instead, you can access blob metadata through virtual columns. For each blob column, Lance provides two virtual columns:

• <column_name>__blob_pos - The byte position of the blob in the blob file
• <column_name>__blob_size - The size of the blob in bytes

These virtual columns can be used for:

• Monitoring blob storage statistics
• Filtering rows by blob size
• Implementing custom blob retrieval logic
• Verifying successful blob writes

SELECT id, title, content__blob_pos, content__blob_size
FROM documents
WHERE id = 1;

# Read table with blob column
documents_df = spark.table("documents")

# Access blob metadata using virtual columns
blob_metadata = documents_df.select(
    "id",
    "title",
    "content__blob_pos",
    "content__blob_size"
)
blob_metadata.show()

# Filter by blob size
large_blobs = documents_df.filter("content__blob_size > 1000000")
large_blobs.select("id", "title", "content__blob_size").show()

// Read table with blob column
val documentsDF = spark.table("documents")

// Access blob metadata using virtual columns
val blobMetadata = documentsDF.select(
  "id",
  "title",
  "content__blob_pos",
  "content__blob_size"
)
blobMetadata.show()

// Filter by blob size
val largeBlobs = documentsDF.filter("content__blob_size > 1000000")
largeBlobs.select("id", "title", "content__blob_size").show()

// Read table with blob column
Dataset<Row> documentsDF = spark.table("documents");

// Access blob metadata using virtual columns
Dataset<Row> blobMetadata = documentsDF.select(
    "id",
    "title",
    "content__blob_pos",
    "content__blob_size"
);
blobMetadata.show();

// Filter by blob size
Dataset<Row> largeBlobs = documentsDF.filter("content__blob_size > 1000000");
largeBlobs.select("id", "title", "content__blob_size").show();

Time Travel¶

Lance supports time travel queries, allowing you to query historical versions of your data using either a specific version number or a timestamp.

Query by Version¶

Use VERSION AS OF to query a specific version of the table:

-- Query version 5 of the table
SELECT * FROM users VERSION AS OF 5;

-- Query specific columns from a version
SELECT id, name FROM users VERSION AS OF 3;

# Query a specific version using DataFrame API
df = spark.read \
    .format("lance") \
    .option("version", "5") \
    .load("/path/to/dataset.lance")
df.show()

// Query a specific version using DataFrame API
val df = spark.read
    .format("lance")
    .option("version", "5")
    .load("/path/to/dataset.lance")
df.show()

// Query a specific version using DataFrame API
Dataset<Row> df = spark.read()
    .format("lance")
    .option("version", "5")
    .load("/path/to/dataset.lance");
df.show();

Query by Tag¶

Use VERSION AS OF with a quoted tag name to query the snapshot referenced by a tag:

-- Query the snapshot referenced by the release_candidate tag
SELECT * FROM users VERSION AS OF 'release_candidate';

-- Query specific columns from a tagged snapshot
SELECT id, name FROM users VERSION AS OF 'v1.0';

# Query a tag using SQL
spark.sql("SELECT * FROM users VERSION AS OF 'release_candidate'").show()

// Query a tag using SQL
spark.sql("SELECT * FROM users VERSION AS OF 'release_candidate'").show()

// Query a tag using SQL
spark.sql("SELECT * FROM users VERSION AS OF 'release_candidate'").show();

Tag queries are read-only. UPDATE, DELETE, INSERT, MERGE INTO, ADD COLUMNS, and UPDATE COLUMNS operations cannot target a tagged snapshot.

Query by Branch¶

Read the current head of a named branch. Do not set branch and version on the same read.

SELECT * FROM catalog.db.users.branch_audit;

audit = spark.read.option("branch", "audit").table("catalog.db.users")

audit_path = spark.read \
    .format("lance") \
    .option("branch", "audit") \
    .load("/path/to/dataset.lance")

val audit = spark.read.option("branch", "audit").table("catalog.db.users")

val auditPath = spark.read
    .format("lance")
    .option("branch", "audit")
    .load("/path/to/dataset.lance")

Dataset<Row> audit = spark.read()
    .option("branch", "audit")
    .table("catalog.db.users");

Dataset<Row> auditPath = spark.read()
    .format("lance")
    .option("branch", "audit")
    .load("/path/to/dataset.lance");

If a table named users.branch_audit already exists, Spark reads that table. Otherwise the last segment is a branch on the parent table. Do not add VERSION AS OF or TIMESTAMP AS OF. Branch identifiers are read-only; mutating commands are rejected.

.option("branch").table(...) applies the branch at scan time. Spark still analyzes with the table schema. Use table.branch_name when the branch schema differs from the table.

Query by Timestamp¶

Use TIMESTAMP AS OF to query the table as it existed at a specific point in time:

-- Query the table as it was at a specific timestamp
SELECT * FROM users TIMESTAMP AS OF '2024-01-15 10:30:00';

-- Query with timestamp in epoch microseconds
SELECT * FROM users TIMESTAMP AS OF 1705314600000000;

# Query by timestamp using SQL
spark.sql("SELECT * FROM users TIMESTAMP AS OF '2024-01-15 10:30:00'").show()

// Query by timestamp using SQL
spark.sql("SELECT * FROM users TIMESTAMP AS OF '2024-01-15 10:30:00'").show()

// Query by timestamp using SQL
spark.sql("SELECT * FROM users TIMESTAMP AS OF '2024-01-15 10:30:00'").show();

Read Options¶

These options control how data is read from Lance datasets. They can be set using the .option() method when reading data.

User-Defined Types¶

Spark UDT columns (e.g. MLlib's VectorUDT) round-trip transparently. On write the column is unwrapped to the UDT's sqlType (a struct) for storage, and the UDT's fully qualified class name is stamped onto the Arrow field's user metadata under the __udt key. On read, that marker is consulted and the schema is rewrapped to the original UDT automatically. No configuration needed; the user-facing class (e.g. Vector for VectorUDT) is materialized in rows as usual.

Example: Reading with Options¶

-- Read a specific version using table options
SELECT * FROM lance.`/path/to/dataset.lance` VERSION AS OF 10;

# Reading with options
df = spark.read \
    .format("lance") \
    .option("batch_size", "1024") \
    .option("version", "5") \
    .load("/path/to/dataset.lance")

// Reading with options
val df = spark.read
    .format("lance")
    .option("batch_size", "1024")
    .option("version", "5")
    .load("/path/to/dataset.lance")

// Reading with options
Dataset<Row> df = spark.read()
    .format("lance")
    .option("batch_size", "1024")
    .option("version", "5")
    .load("/path/to/dataset.lance");

Example: Tuning Batch Size for Performance¶

# Larger batch size for better throughput on large scans
df = spark.read \
    .format("lance") \
    .option("batch_size", "4096") \
    .load("/path/to/dataset.lance")

// Larger batch size for better throughput on large scans
val df = spark.read
    .format("lance")
    .option("batch_size", "4096")
    .load("/path/to/dataset.lance")