Create Lance external tables to map Lance table directories in OSS and access AI-native multimodal data through MaxCompute.
Lance is an AI-native multimodal data lake format. MaxCompute supports creating Lance external tables that map to Lance table directories in OSS for data access.
Limits
The size of a single file cannot exceed 2 GB. You must split files that are larger than 2 GB.
MaxCompute and OSS must be in the same region.
Lance external tables do not support partitioning.
Lance external tables do not automatically reflect Lance file schema changes.
Lance external tables do not support cluster attributes or primary keys.
Lance external tables do not support UPDATE or DELETE operations.
Lance external tables do not currently support querying historical data versions.
Create an external table
Syntax
CREATE EXTERNAL TABLE [if NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[COMMENT <table_comment>]
com.aliyun.odps.common.table.lance.LanceStorageHandler
[WITH serdeproperties (
['<property_name>'='<property_value>',...]
)]
LOCATION '<oss_location>'
;Parameters
You must specify the Lance data schema (column names and data types) for a standard external table. This schema does not update automatically with Lance data schema evolution.
A standard external table can point to an empty directory. The Lance data schema is created on the first write based on the specified schema.
Common parameters: Basic syntax parameters.
Write data
For MaxCompute write syntax, see OSS external tables.
Before writing to a Lance external table, run
SET odps.service.mode=off;to disable online mode. Online mode does not support writes.
SET odps.service.mode=off;
-- Append data
INSERT INTO <table_name> VALUES (<value_1>, <value_2>, ...);
-- Overwrite data
INSERT OVERWRITE TABLE <table_name> VALUES (<value_1>, <value_2>, ...);Query data
For SELECT syntax, see OSS external tables.
For query plan optimization, see OSS external tables.
Other DDL operations
View the CREATE TABLE statement
SHOW CREATE TABLE <table-name>;View table details
DESC [EXTENDED] <table-name>;
Examples
The following example creates a Lance external table, inserts a row, and queries the data.
Prepare a dedicated directory in an OSS bucket in the same region as your MaxCompute project for the Lance table, for example: oss://<endpoint>/lancetable-demo/lance_table/image.lance/.
Create the external table
CREATE EXTERNAL TABLE IF NOT EXISTS mc_oss_lance_external
(
id BIGINT COMMENT 'Row ID',
name STRING COMMENT 'Image file name',
image BINARY COMMENT 'Image content or description'
)
COMMENT 'Lance external table example'
STORED BY 'com.aliyun.odps.common.table.lance.LanceStorageHandler'WITH SERDEPROPERTIES (
'odps.properties.rolearn'='acs:ram::<uid>:role/aliyunodpsdefaultrole'
)
LOCATION 'oss://oss-cn-<region>-internal.aliyuncs.com/lancetable-demo/lance_table/image.lance/';Insert data
SET odps.service.mode=off;
INSERT INTO mc_oss_lance_external VALUES (
1,
'test1.jpg',
CAST('The weather is nice today, and I am in a good mood! The sun is shining, perfect for a walk.' AS BINARY)
);Query data
SELECT * FROM mc_oss_lance_external;
-- results:
+------------+------------+------------+
| image_id | image_name | image |
+------------+------------+------------+
| 1 | test1.jpg | =20=E4=BB**|
+------------+------------+------------+
-- View data by using the CAST function
SELECT CAST(image AS STRING) FROM mc_oss_lance_external;
-- results:
+------------+------------+------------+
| image_id | image_name | image |
+------------+------------+------------+
| 1 | test1.jpg | The weather is nice today, and I am in a good mood! The sun is shining, perfect for a walk. |
+------------+------------+------------+Supported data types
For MaxCompute data types, see MaxCompute V1.0 data type edition and MaxCompute V2.0 data type edition.
Lance (Arrow) data type | MaxCompute data type | Read supported | Write supported |
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
|
| ||
Note For standard Lance external tables, writing a MaxCompute TIMESTAMP column to Lance maps to the |
| ||
| Not supported | ||
|
| ||
|
| ||
|
| ||
|
|