Access OSS Tables through Trino

Prerequisites

• Create a Table Bucket in OSS Tables, and obtain an Alibaba Cloud AccessKey (AccessKey ID and AccessKey Secret).

• Trino 476, together with the lance-trino 0.3.4 plugin (the Trino plugin must match the server version).

• JDK 24 (Trino 476 requires 64-bit Java 24, and no lower than 24.0.1).

• Maven, used to download the Trino CLI, the plugin package, and the connector.

Step 1: Prepare the environment

Download, verify, and extract Trino Server 476. The following uses the Alibaba Cloud Maven mirror as an example; the path remains the same when you switch to another Maven Central mirror:

curl -fLO https://maven.aliyun.com/repository/central/io/trino/trino-server/476/trino-server-476.tar.gz
echo "c9b38be4017ebd2f7095455aaac772fb40247ebe  trino-server-476.tar.gz" | sha1sum -c
tar -xzf trino-server-476.tar.gz

When the verification passes, the output is trino-server-476.tar.gz: OK. The extracted trino-server-476 directory is referred to as <trino> in the following steps.

Use Maven to download the Trino CLI, the lance-trino plugin package, and osstables-lance-connector to the local repository:

mvn dependency:get -Dtransitive=false -Dartifact=io.trino:trino-cli:476:jar:executable
mvn dependency:get -Dtransitive=false -Dartifact=org.lance:lance-trino:0.3.4:zip
mvn dependency:get -Dtransitive=false -Dartifact=com.aliyun.osstables:osstables-lance-connector:1.0.0-rc2

The artifacts are located in ~/.m2/repository. The lance-trino plugin package already bundles the dependencies required by itself and the connector at runtime, and the Trino CLI is an executable jar. Therefore, -Dtransitive=false downloads only these three artifacts, without their transitive dependencies.

The Trino installation package does not include the lance plugin and you must install it manually. Trino does not automatically download plugin dependencies: each plugin is a directory under <trino>/plugin/, and the directory must contain all the jars required by the plugin. Extract the plugin package to the plugin directory, and place the connector jar into it:

mkdir -p <trino>/plugin/lance
unzip -q ~/.m2/repository/org/lance/lance-trino/0.3.4/lance-trino-0.3.4.zip -d /tmp
cp -rf /tmp/lance-trino-0.3.4/. <trino>/plugin/lance/
cp ~/.m2/repository/com/aliyun/osstables/osstables-lance-connector/1.0.0-rc2/osstables-lance-connector-1.0.0-rc2.jar \
  <trino>/plugin/lance/

For cluster deployment, complete this step on the coordinator and all worker nodes, and use the same catalog configuration (see Step 2).

Step 2: Configure the Trino connection

For general Trino deployment (node.properties, config.properties, and so on), see the official Trino deployment documentation. This section describes only the lance-related configuration.

--add-opens=java.base/java.nio=ALL-UNNAMED
--enable-native-access=ALL-UNNAMED
--sun-misc-unsafe-memory-access=allow

connector.name=lance
lance.impl=com.aliyun.osstables.lance.OssTablesNamespace
# Control plane (catalog) configuration
lance.osstables.uri=https://<tablebucket>-<uid>.cn-beijing-internal.oss-tables.aliyuncs.com/lance
lance.osstables.region=cn-beijing
lance.osstables.access_key_id=<Control-plane AccessKey ID>
lance.osstables.secret_access_key=<Control-plane AccessKey Secret>
# Data plane (OSS) configuration. This topic recommends the oss_ prefix
lance.storage.oss_access_key_id=<Data-plane AccessKey ID>
lance.storage.oss_secret_access_key=<Data-plane AccessKey Secret>
lance.storage.endpoint=https://oss-cn-beijing-internal.aliyuncs.com
lance.storage.region=cn-beijing

chmod 600 <trino>/etc/catalog/lance.properties

JAVA_HOME=<jdk24> <trino>/bin/launcher start

java -jar ~/.m2/repository/io/trino/trino-cli/476/trino-cli-476-executable.jar \
  --server http://localhost:8080 --catalog lance

Step 3: Use SQL to manipulate data

In the following examples, the catalog name is lance, and a namespace corresponds to a Trino schema.

SHOW SCHEMAS FROM lance;
CREATE SCHEMA lance.my_db;
SHOW TABLES FROM lance.my_db;

-- Create a table (no USING / WITH clause is required on the Trino side; the table is a Lance table by default)
CREATE TABLE lance.my_db.sales (id BIGINT, region VARCHAR, amount DOUBLE);

INSERT INTO lance.my_db.sales VALUES
  (1, 'cn-north', 100.0), (2, 'cn-south', 200.0),
  (3, 'cn-north', 50.0), (4, 'cn-east', 80.0);

SELECT region, count(*) AS orders, sum(amount) AS gmv
  FROM lance.my_db.sales GROUP BY region ORDER BY region;
SELECT count(*) AS total, sum(amount) AS gmv FROM lance.my_db.sales;

UPDATE lance.my_db.sales SET amount = 999.0 WHERE id = 3;
DELETE FROM lance.my_db.sales WHERE id = 4;

MERGE INTO lance.my_db.sales AS t
USING (VALUES (2, 'cn-south', 250.0), (5, 'cn-west', 10.0)) AS s(id, region, amount)
  ON t.id = s.id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, region, amount) VALUES (s.id, s.region, s.amount);

SELECT id, region, amount FROM lance.my_db.sales ORDER BY id;

-- Read historical data by version number
SELECT count(*) FROM lance.my_db.sales FOR VERSION AS OF 1;

-- Read historical data by timestamp (the timestamp must be earlier than the current time and no earlier than the first version of the table)
SELECT count(*) FROM lance.my_db.sales FOR TIMESTAMP AS OF now() - interval '5' second;

DROP TABLE lance.my_db.sales;
DROP SCHEMA lance.my_db;

Notes

• Do not write long-term AccessKeys into shared configurations. In production, use short-term STS credentials and restrict the permissions of the configuration file.

• Table names and database names must use ASCII characters.