Cross-engine interoperability

Scenario

• Write with the Python SDK: Create the table doc_interop.embeddings, which contains scalar columns (id / category / score) and a vector column (embedding, fixed_size_list<float>[8]), and write 1,000 rows.

• Read with Spark SQL: Run aggregation, grouping, and detail queries against the same table.

• Read with Trino SQL: Run the same queries against the same table. The results are consistent with those from Spark.

The three engines share the same catalog (osstables.uri points to the same Table Bucket) and therefore see the same data. The same table can also be appended to by Ray and read by LanceDB and the Java SDK. For more information, see the conclusion at the end of this topic.

Step 1: Write with the Python SDK

Use pylance to create the table and write data that contains a vector column:

import lance, lance_namespace
import numpy as np
import osstables_lance_connector  # noqa
import pyarrow as pa
from lance_namespace import CreateNamespaceRequest, DropNamespaceRequest, DropTableRequest

ns = lance_namespace.connect("osstables", {
    "osstables.uri": "https://<tablebucket>-<uid>.cn-beijing-internal.oss-tables.aliyuncs.com/lance",
    "osstables.region": "cn-beijing",
    "osstables.access_key_id": "<Control-plane AccessKey ID>",
    "osstables.secret_access_key": "<Control-plane AccessKey Secret>",
})
STORAGE = {
    "access_key_id": "<Data-plane AccessKey ID>", "access_key_secret": "<Data-plane AccessKey Secret>",
    "endpoint": "https://oss-cn-beijing-internal.aliyuncs.com", "region": "cn-beijing",
}

DB, DIM, N = "doc_interop", 8, 1000
ns.create_namespace(CreateNamespaceRequest(id=[DB]))

rng = np.random.default_rng(42)
cats = ["cn-north", "cn-south", "cn-east", "cn-west"]
emb = rng.random((N, DIM), dtype=np.float32)
data = pa.table({
    "id": pa.array(range(N), pa.int64()),
    "category": pa.array([cats[i % 4] for i in range(N)], pa.string()),
    "score": pa.array(rng.random(N) * 100, pa.float64()),
    "embedding": pa.FixedSizeListArray.from_arrays(pa.array(emb.reshape(-1), pa.float32()), DIM),
})
lance.write_dataset(data, namespace_client=ns, table_id=[DB, "embeddings"],
                    mode="create", storage_options=STORAGE)

After the write, the table schema is:

id: int64
category: string
score: double
embedding: fixed_size_list<item: float>[8]

Step 2: Read with Spark SQL

In Spark, configure a lance catalog that points to the same Table Bucket (for the configuration, see Access OSS Tables through Spark), and then you can query the table:

SELECT count(*) AS cnt, round(avg(score), 3) AS avg_score, min(id) AS min_id, max(id) AS max_id
  FROM lance.doc_interop.embeddings;

SELECT category, count(*) AS c, round(sum(score), 2) AS total
  FROM lance.doc_interop.embeddings GROUP BY category ORDER BY category;

-- In Spark, the vector column is mapped to array<float>. You can use size() to obtain the dimension.
SELECT id, category, round(score, 3) AS score, size(embedding) AS dim
  FROM lance.doc_interop.embeddings WHERE id < 3 ORDER BY id;

Query output:

cnt   avg_score  min_id  max_id
1000  48.594     0       999

category  c    total
cn-east   250  11970.12
cn-north  250  12604.70
cn-south  250  11985.57
cn-west   250  12033.88

id  category  score   dim
0   cn-north  71.610  8
1   cn-south  35.712  8
2   cn-east   85.187  8

Step 3: Read with Trino SQL

In Trino, configure a lance catalog that points to the same Table Bucket (for the configuration, see Access OSS Tables through Trino), and run the same queries:

SELECT count(*) AS cnt, round(avg(score), 3) AS avg_score, min(id) AS min_id, max(id) AS max_id
  FROM lance.doc_interop.embeddings;

SELECT category, count(*) AS c, round(sum(score), 2) AS total
  FROM lance.doc_interop.embeddings GROUP BY category ORDER BY category;

-- In Trino, the vector column is mapped to array(real). You can use cardinality() to obtain the dimension.
SELECT id, category, round(score, 3) AS score, cardinality(embedding) AS dim
  FROM lance.doc_interop.embeddings WHERE id < 3 ORDER BY id;

The query output is exactly the same as the Spark output:

 cnt  | avg_score | min_id | max_id
------+-----------+--------+--------
 1000 |    48.594 |      0 |    999

 category |  c  |  total
----------+-----+----------
 cn-east  | 250 | 11970.12
 cn-north | 250 | 12604.70
 cn-south | 250 | 11985.57
 cn-west  | 250 | 12033.88

 id | category | score  | dim
----+----------+--------+-----
  0 | cn-north | 71.610 |   8
  1 | cn-south | 35.712 |   8
  2 | cn-east  | 85.187 |   8

Step 4: Clean up the example resources

After you confirm that all engines have stopped reading from and writing to the table, you can use the Python SDK to clean up the resources:

ns.drop_table(DropTableRequest(id=[DB, "embeddings"]))
ns.drop_namespace(DropNamespaceRequest(id=[DB]))

Conclusion

• The same Lance table can be read and written by the Python SDK, Ray, LanceDB, the Java SDK, Spark, and Trino. Cross-engine read results are consistent.

• The vector column (fixed_size_list<float>) is mapped to array<float> in Spark (use size()) and to array(real) in Trino (use cardinality()). Both SQL engines can read the column.

• Each engine only needs to point its catalog to the same Table Bucket to share data, without additional export or import operations.

Notes

• Cross-engine reads and writes require the osstables.uri of all engines to point to the same Table Bucket, and the catalog credentials and data plane credentials of each engine must have the required permissions.

• When you append data across engines, use column types that are consistent with those of the table.

• If you use STS temporary credentials, note the validity period and rotation of the credentials during the sessions of each engine.