Application Scenarios
Solution |
Application scenarios |
|---|---|
| ODPS Catalog | Quick exploration, no need to create tables, automatic metadata synchronization |
| ODPS external table + Tunnel API | Public Access, Compatibility First |
| ODPS external table + Storage API | High-performance analytics and HTAP mixed workloads within a VPC |
Prerequisites
This tutorial assumes a standard TPC-H lineitem table already exists in MaxCompute (ODPS), with the structure and sample data as follows:
+------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
| l_orderkey | l_partkey | l_suppkey | l_linenumber | l_quantity | l_extendedprice | l_discount | l_tax | l_returnflag | l_linestatus | l_shipdate | l_commitdate | l_receiptdate | l_shipinstruct | l_shipmode | l_comment |
+------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
| 1 | 1551894 | 76910 | 1 | 17.00 | 33078.94 | 0.04 | 0.02 | N | O | 1996-03-13 | 1996-02-12 | 1996-03-22 | DELIVER IN PERSON | TRUCK | egular courts above the |
| 1 | 673091 | 73092 | 2 | 36.00 | 38306.16 | 0.09 | 0.06 | N | O | 1996-04-12 | 1996-02-28 | 1996-04-20 | TAKE BACK RETURN | MAIL | ly final dependencies: slyly bold|
| 1 | 636998 | 36999 | 3 | 8.00 | 15479.68 | 0.10 | 0.02 | N | O | 1996-01-29 | 1996-03-05 | 1996-01-31 | TAKE BACK RETURN | REG AIR | riously. regular, express dep |
| 1 | 21315 | 46316 | 4 | 28.00 | 34616.68 | 0.09 | 0.06 | N | O | 1996-04-21 | 1996-03-30 | 1996-05-16 | NONE | AIR | lites. fluffily even de |
| 1 | 240267 | 15274 | 5 | 24.00 | 28974.00 | 0.10 | 0.04 | N | O | 1996-03-30 | 1996-03-14 | 1996-04-01 | NONE | FOB | pending foxes. slyly re |
+------------+-----------+-----------+--------------+------------+------------------+------------+-------+--------------+--------------+------------+------------+-------------+--------------------+------------+----------------------------------+
Environment Preparation
Before you begin, ensure the following prerequisites are met:
- OceanBase version ≥ V4.3.5 BP2 (supports ODPS Catalog)
- JDK 8 or 11 is deployed with Java support enabled (
ob_enable_java_env = true) - The OceanBase JAVA SDK environment has been deployed. For specific deployment steps, see Deploy the OceanBase Database JAVA SDK environment.
Method 1: Use ODPS Catalog for Quick Data Exploration (Recommended)
OceanBase supports ODPS Catalog starting from V4.3.5 BP2, allowing direct access to MaxCompute tables without manually creating external tables.
Create an ODPS Catalog
CREATE EXTERNAL CATALOG odps_lineitem_test
PROPERTIES (
TYPE = 'ODPS',
ACCESSID = 'XXXXXXXXXXXX',
ACCESSKEY = 'xxxxxxxxxxxxxx',
ENDPOINT = 'https://service.cn-hangzhou.maxcompute.aliyun.com/api',
TUNNEL_ENDPOINT = '',
PROJECT_NAME = 'bigdata_public_dataset'
);
Parameter Description:
- TUNNEL_ENDPOINT can be left empty; the system will automatically derive it.
- This method uses the Tunnel API by default, suitable for public networks or scenarios where OBServer and ODPS are in different VPCs.
bigdata_public_dataset: For details on the dataset address, see Public datasets overview.
Query Data Verification
SET CATALOG odps_lineitem_test;
USE odps_lineitem_test.test_db;
SELECT * FROM lineitem LIMIT 5;
Expected result: Returns 5 rows of data identical to the source table.
Advantages:
- Automatically synchronizes table structure and partition information.
- No need to define column types, avoiding manual mapping errors.
- Suitable for quick data exploration and metadata verification.
Method 2: Create an ODPS External Table (for Fine-grained Control Scenarios)
You can create an ODPS external table when you need to explicitly control column mapping, enable the Storage API, or maintain compatibility with older versions.
Switch back to the OceanBase database
If you have tried Method 1 first, you need to switch back to the OceanBase database now.
USE internal.test;
Create an external table
CREATE EXTERNAL TABLE external_lineitem (
l_orderkey BIGINT,
l_partkey BIGINT,
l_suppkey BIGINT,
l_linenumber BIGINT,
l_quantity DECIMAL(15,2),
l_extendedprice DECIMAL(15,2),
l_discount DECIMAL(15,2),
l_tax DECIMAL(15,2),
l_returnflag CHAR(1),
l_linestatus CHAR(1),
l_shipdate DATE,
l_commitdate DATE,
l_receiptdate DATE,
l_shipinstruct CHAR(25),
l_shipmode CHAR(10),
l_comment VARCHAR(44)
)
PROPERTIES (
TYPE = 'ODPS',
ACCESSID = 'XXXXXXXXXXXX',
ACCESSKEY = 'xxxxxxxxxxxxxx',
ENDPOINT = 'https://service.cn-hangzhou.maxcompute.aliyun.com/api',
TUNNEL_ENDPOINT = '',
PROJECT_NAME = 'bigdata_public_dataset',
SCHEMA_NAME = 'tpch_10g',
TABLE_NAME = 'lineitem',
QUOTA_NAME = '',
COMPRESSION_CODE = 'zstd'
);
Key configuration notes:
COMPRESSION_CODE = 'zstd': Enables compression to improve network transmission efficiency.- The
Tunnel APIis used by default and is suitable for general network environments. - Column definitions must match those of the MaxCompute table.
bigdata_public_dataset: For details about the dataset address, see Public datasets overview.
Verification and advanced operations
Verify external table reading
SELECT * FROM external_lineitem LIMIT 5;
Expected output: Returns 5 rows of data identical to those in the source table.
(Optional) Import internal tables to accelerate analysis
For frequent queries or complex aggregations, you can import data into OceanBase internal tables:
-- Create an internal table
CREATE TABLE ob_lineitem LIKE external_lineitem;
-- Batch Import
INSERT INTO ob_lineitem SELECT * FROM external_lineitem;
-- Verify
SELECT COUNT(*) FROM ob_lineitem;
(Optional) Export data back to MaxCompute
Use the INSERT OVERWRITE statement to write data from OceanBase back to MaxCompute:
-- Assume the target external table lineitem_target has been created.
INSERT OVERWRITE lineitem_target
SELECT * FROM ob_lineitem
WHERE l_shipdate >= '1996-01-01';
Limitations:
- The target table must have the same number of columns, in the same order, and of the same types as the source table.
- Only the INSERT INTO / OVERWRITE ... SELECT syntax is supported.
Additional information: API mode selection guide
OceanBase supports two access modes for MaxCompute. Choose the appropriate one based on your deployment environment:
Scenario 1: High-performance access within a VPC (Recommended)
Application conditions: OceanBase is deployed on Alibaba Cloud ECS and in the same region as MaxCompute in a VPC.
CREATE EXTERNAL TABLE lineitem1 ( /*Same as above.*/ )
PROPERTIES (
TYPE = 'ODPS',
ACCESSID = 'XXXXXXXXXXXX',
ACCESSKEY = 'xxxxxxxxxxxxxx',
ENDPOINT = 'https://service.cn-hangzhou-vpc.maxcompute.aliyun-inc.com/api',
PROJECT_NAME = 'test_obqa',
TABLE_NAME = 'lineitem1',
QUOTA_NAME = 'pay-as-you-go', -- Must be pay-as-you-go.
API_MODE = 'storage_api', -- Enable the storage API.
SPLIT = 'byte', -- Split by byte (recommended when row sizes vary).
COMPRESSION_CODE = 'zstd'
);
Advantages:
- Supports predicate pushdown and column pruning.
- Low latency and high throughput.
- Automatic parallel sharding.
Scenario 2: Public network or general environment (compatibility first)
CREATE EXTERNAL TABLE lineitem ( /*Same as above.*/ )
PROPERTIES (
TYPE = 'ODPS',
ACCESSID = 'XXXXXXXXXXXX',
ACCESSKEY = 'xxxxxxxxxxxxxx',
ENDPOINT = 'https://service.cn-hangzhou.maxcompute.aliyun.com/api',
PROJECT_NAME = 'bigdata_public_dataset',
SCHEMA_NAME = 'tpch_10g',
TABLE_NAME = 'lineitem',
COMPRESSION_CODE = 'zstd'
-- Tunnel API by default
);
Advantages:
- No VPC network configuration required.
- Compatible with all deployment environments.
- Simple configuration and quick start.
For more information, see ODPS external tables.