LanceDB 中文文档

外观

本页尚未翻译

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

Delete rows ​

Select and delete records, verify the remaining rows, and understand storage retention.

Applies to: Embedded ✅, Enterprise ✅

Deleting rows removes matching records from the current table contents. It keeps the table and schema so you can continue reading and writing. To remove the table itself, see Drop a table.

Inspect the rows you intend to delete ​

Use the same predicate for inspection and deletion. The example below creates Alice, Bob and Charlie, prints Charlie's row, and deletes it by id = 3. Inspection and deletion are separate operations; concurrent writes can change which rows match between them.

Delete matching records ​

Each program creates Alice, Bob and Charlie in a temporary local database, deletes Charlie, and prints the remaining rows: Alice and Bob. The temporary database is removed when the program finishes.

  • Python: install lancedb, save the code as delete_rows.py, and run python delete_rows.py.
  • TypeScript: install @lancedb/lancedb, save the code as delete_rows.mts, and run node delete_rows.mts with Node.js 22.18 or later.
  • Rust: download the Rust examples, extract the project, and run cargo run --locked --bin delete.
python
table = db.create_table(
    "users",
    [
        {"id": 1, "name": "Alice"},
        {"id": 2, "name": "Bob"},
        {"id": 3, "name": "Charlie"},
    ],
)
predicate = "id = 3"
print("Selected:", table.search().where(predicate).to_list())
table.delete(predicate)
rows = sorted(table.search().to_list(), key=lambda row: row["id"])
print("Remaining:", rows)
typescript
const lancedb = await import("@lancedb/lancedb");
const { mkdtemp, rm } = await import("node:fs/promises");
const { tmpdir } = await import("node:os");
const { join } = await import("node:path");
const uri = await mkdtemp(join(tmpdir(), "lancedb-ingestion-"));
try {
  const db = await lancedb.connect(uri);
  const table = await db.createTable("users", [
    { id: 1, name: "Alice" },
    { id: 2, name: "Bob" },
    { id: 3, name: "Charlie" },
  ]);
  const predicate = "id = 3";
  console.log("Selected:", await table.query().where(predicate).toArray());
  await table.delete(predicate);
  const rows = (await table.query().toArray()).sort((a, b) => a.id - b.id);
  console.log("Remaining:", rows);
} finally {
  await rm(uri, { recursive: true, force: true });
}
rust
use std::sync::Arc;

use arrow_array::{Int64Array, RecordBatch, RecordBatchIterator, RecordBatchReader, StringArray};
use arrow_schema::{DataType, Field, Schema};
use futures::TryStreamExt;
use lancedb::query::{ExecutableQuery, QueryBase};

fn make_users_reader(rows: &[(i64, &str)]) -> Box<dyn RecordBatchReader + Send> {
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int64, false),
        Field::new("name", DataType::Utf8, false),
    ]));
    let batch = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(Int64Array::from_iter_values(rows.iter().map(|r| r.0))),
            Arc::new(StringArray::from_iter_values(rows.iter().map(|r| r.1))),
        ],
    )
    .unwrap();
    Box::new(RecordBatchIterator::new(
        vec![Ok(batch)].into_iter(),
        schema,
    ))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let directory = tempfile::tempdir()?;
    let db = lancedb::connect(directory.path().to_str().unwrap())
        .execute()
        .await?;
    let table = db
        .create_table(
            "users",
            make_users_reader(&[(1, "Alice"), (2, "Bob"), (3, "Charlie")]),
        )
        .execute()
        .await?;
    let predicate = "id = 3";
    let selected: Vec<RecordBatch> = table
        .query()
        .only_if(predicate)
        .execute()
        .await?
        .try_collect()
        .await?;
    println!("Selected: {selected:?}");
    table.delete(predicate).await?;
    let rows: Vec<RecordBatch> = table.query().execute().await?.try_collect().await?;
    println!("Remaining: {rows:?}");
    assert_eq!(table.count_rows(None).await?, 2);
    assert_eq!(
        table
            .count_rows(Some("id = 1 AND name = 'Alice'".into()))
            .await?,
        1
    );
    assert_eq!(
        table
            .count_rows(Some("id = 2 AND name = 'Bob'".into()))
            .await?,
        1
    );
    assert_eq!(table.count_rows(Some("id = 3".into())).await?, 0);
    Ok(())
}

Enterprise also supports DELETE.

Verify that the rows are absent ​

Query the same predicate after deletion and check the remaining row count. A reader still using an older snapshot may see the earlier contents; see Consistency.

Deleted rows are excluded from current query results, including indexed searches. Deletion does not imply immediate physical erasure of every stored copy of the data.

Understand storage reclamation ​

Compaction rewrites the live data layout; cleanup removes eligible retained versions and files. Retained history can continue to reference data that no longer appears in the latest table version.

In LanceDB Embedded, optimize() provides maintenance operations. In Enterprise, managed maintenance and retention follow the service configuration. Choose retention according to the history you need, rather than shortening it as part of a routine delete example. See Compaction and Garbage Collection for the separate maintenance workflow.

Complete write workflow ​

Complete examples: create, append, update, upsert and delete

Each program creates a temporary local database, performs the five operations, prints the result, and removes its temporary data when finished.

  • Python: install lancedb, save the code as ingestion.py, and run python ingestion.py.
  • TypeScript: install @lancedb/lancedb, save the code as ingestion.mts, and run node ingestion.mts with Node.js 22.18 or later.
  • Rust: download the complete Rust project, extract it, and run cargo run --locked. It includes src/main.rs, the Cargo manifest and a lockfile for the example's dependencies. The code is shown below.
python
from tempfile import TemporaryDirectory

import lancedb

with TemporaryDirectory(prefix="lancedb-ingestion-") as uri:
    db = lancedb.connect(uri)
    table = db.create_table(
        "users",
        [
            {"id": 1, "name": "Alice", "login_count": 10},
            {"id": 2, "name": "Bob", "login_count": 20},
        ],
    )
    table.add([{"id": 3, "name": "Charlie", "login_count": 5}])
    table.update(where="id = 2", values={"name": "Bobby"})
    table.update(where="id = 2", values_sql={"login_count": "login_count + 1"})
    (
        table.merge_insert("id")
        .when_matched_update_all()
        .when_not_matched_insert_all()
        .execute(
            [
                {"id": 3, "name": "Charlie", "login_count": 6},
                {"id": 4, "name": "Dana", "login_count": 1},
            ]
        )
    )
    print("Rows selected for deletion:", table.search().where("id = 4").to_list())
    table.delete("id = 4")
    rows = sorted(table.search().to_list(), key=lambda row: row["id"])
    print(rows)
typescript
const lancedb = await import("@lancedb/lancedb");
const { mkdtemp, rm } = await import("node:fs/promises");
const { tmpdir } = await import("node:os");
const { join } = await import("node:path");

const uri = await mkdtemp(join(tmpdir(), "lancedb-ingestion-"));
try {
  const db = await lancedb.connect(uri);
  const table = await db.createTable("users", [
    { id: 1, name: "Alice", login_count: 10 },
    { id: 2, name: "Bob", login_count: 20 },
  ]);
  await table.add([{ id: 3, name: "Charlie", login_count: 5 }]);
  await table.update({ where: "id = 2", values: { name: "Bobby" } });
  await table.update({
    where: "id = 2",
    valuesSql: { login_count: "login_count + 1" },
  });
  await table
    .mergeInsert("id")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute([
      { id: 3, name: "Charlie", login_count: 6 },
      { id: 4, name: "Dana", login_count: 1 },
    ]);
  console.log(
    "Rows selected for deletion:",
    await table.query().where("id = 4").toArray(),
  );
  await table.delete("id = 4");
  const rows = (await table.query().toArray()).sort((a, b) => a.id - b.id);
  console.log(rows);
} finally {
  await rm(uri, { recursive: true, force: true });
}
rust
use std::sync::Arc;

use arrow_array::{Int64Array, RecordBatch, RecordBatchIterator, RecordBatchReader, StringArray};
use arrow_schema::{DataType, Field, Schema};
use futures::TryStreamExt;
use lancedb::query::{ExecutableQuery, QueryBase};

fn make_users_reader(rows: &[(i64, &str, i64)]) -> Box<dyn RecordBatchReader + Send> {
    let schema = Arc::new(Schema::new(vec![
        Field::new("id", DataType::Int64, false),
        Field::new("name", DataType::Utf8, false),
        Field::new("login_count", DataType::Int64, false),
    ]));
    let batch = RecordBatch::try_new(
        schema.clone(),
        vec![
            Arc::new(Int64Array::from_iter_values(rows.iter().map(|r| r.0))),
            Arc::new(StringArray::from_iter_values(rows.iter().map(|r| r.1))),
            Arc::new(Int64Array::from_iter_values(rows.iter().map(|r| r.2))),
        ],
    )
    .unwrap();
    Box::new(RecordBatchIterator::new(
        vec![Ok(batch)].into_iter(),
        schema,
    ))
}

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let directory = tempfile::tempdir()?;
    let db = lancedb::connect(directory.path().to_str().unwrap())
        .execute()
        .await?;
    let table = db
        .create_table(
            "users",
            make_users_reader(&[(1, "Alice", 10), (2, "Bob", 20)]),
        )
        .execute()
        .await?;
    table
        .add(make_users_reader(&[(3, "Charlie", 5)]))
        .execute()
        .await?;
    table
        .update()
        .only_if("id = 2")
        .column("name", "'Bobby'")
        .execute()
        .await?;
    table
        .update()
        .only_if("id = 2")
        .column("login_count", "login_count + 1")
        .execute()
        .await?;
    let mut merge = table.merge_insert(&["id"]);
    merge
        .when_matched_update_all(None)
        .when_not_matched_insert_all();
    merge
        .execute(make_users_reader(&[(3, "Charlie", 6), (4, "Dana", 1)]))
        .await?;
    let selected: Vec<RecordBatch> = table
        .query()
        .only_if("id = 4")
        .execute()
        .await?
        .try_collect()
        .await?;
    println!("Rows selected for deletion: {selected:?}");
    table.delete("id = 4").await?;
    let rows: Vec<RecordBatch> = table.query().execute().await?.try_collect().await?;
    println!("Remaining rows: {rows:?}");
    assert_eq!(table.count_rows(None).await?, 3);
    assert_eq!(
        table
            .count_rows(Some(
                "id = 2 AND name = 'Bobby' AND login_count = 21".into()
            ))
            .await?,
        1
    );
    assert_eq!(
        table
            .count_rows(Some("id = 3 AND login_count = 6".into()))
            .await?,
        1
    );
    Ok(())
}

After the operations, Alice has 10 logins, Bobby has 21, and Charlie has 6. Dana is selected for deletion and is absent from the final result.