OSS-HDFS external table

Usage notes

• You have created an OSS bucket and enabled the OSS-HDFS service for it.

• (Optional) To allow a RAM user to create an OSS-HDFS external table, you must grant the RAM user permissions to access the OSS-HDFS service.

• The MAX_PT function is not supported.

• Writing data to a dynamic partition is not supported.

• When you read data from OSS-HDFS, if a file has fewer columns than the number of columns defined in the DDL statement for the external table, the missing columns are populated with NULL. If a file has more columns, the extra columns are ignored.

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[PARTITIONED BY (<col_name> <data_type>, ...)]
[ROW FORMAT SERDE 'serde_class'
  [WITH SERDEPROPERTIES (
    ['<property_name>'='<property_value>',...])
  ]
]
STORED AS file_format 
LOCATION 'oss://<oss_hdfs_endpoint>/<bucket>/<path_data>' 
[TBLPROPERTIES (
  'odps.oss-hdfs.state'='on',
  '<tbproperty_name>'='<tbproperty_value>',...)
];

LOCATION is set to the directory path of OSS-HDFS. You can obtain the oss_hdfs_endpoint as follows:

1. Log on to the Object Storage Service (OSS) console.

2. On the Buckets page, click Create Bucket.

3. On the Buckets page, click the target Bucket Name to open the Objects page.

4. On the Objects page, click Overview in the navigation pane on the left to open the Overview page.

5. In the Port section of the Overview page, obtain the Endpoint for the OSS-HDFS.

Parameters

• For common parameters, see Basic syntax parameters.

• The following table describes the parameters for OSS-HDFS external tables.

  Parameter	Required	Description
  odps.oss-hdfs.state	Yes	The value is fixed to on. When the endpoint of an external table is of the OSS-HDFS type (such as cn-beijing.oss-dls.aliyuncs.com in the China (Beijing) region), you need to add the tblproperty parameter for the table to properly read data from and write data to OSS-HDFS. Important If you do not add tblproperties('odps.oss-hdfs.state'='on') when you create a table, the created external table will be an OSS external table instead of an OSS-HDFS external table, and all written data will be stored in OSS.

• For format-specific parameters, see the parameter sections for each format under OSS external table.

Example

This example demonstrates how to create a Parquet-formatted OSS-HDFS external table, write data to it, and read the data. For information about other formats, see OSS external table.

1. Log on to the OSS console, create an OSS bucket, and enable the OSS-HDFS service for the bucket.

   Assume that the OSS Bucket created in the China (Hangzhou) region is named oss-mc-test, and the new directory is named oss_hdfs_tbl_parquet_pt.

2. Create an OSS-HDFS external table in MaxCompute.

   Note

   When you run the following code, you need to replace <uid> with the actual Alibaba Cloud account ID or the UID of a RAM user.

   CREATE EXTERNAL TABLE oss_hdfs_tbl_parquet_pt ( 
     id INT, dt STRING
   )
   PARTITIONED BY (ds STRING)
   ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
   WITH SERDEPROPERTIES(
     'odps.properties.rolearn' = 'acs:ram::<uid>:role/aliyunodpsdefaultrole'
   )
   STORED AS PARQUET
   LOCATION 'oss://cn-hangzhou.oss-dls.aliyuncs.com/oss-mc-test/oss_hdfs_tbl_parquet_pt/'
   TBLPROPERTIES('odps.oss-hdfs.state'='on');

3. Write data to the OSS-HDFS external table and then read the data.

   INSERT OVERWRITE oss_hdfs_tbl_parquet_pt PARTITION (ds='2025-0609') VALUES(1, '0106'), (2, '0107');
   
   SELECT * FROM oss_hdfs_tbl_parquet_pt WHERE ds = '2025-0609';

   The following result is returned:

   +------------+------------+------------+
   | id         | dt         | ds         |
   +------------+------------+------------+
   | 1          | 0106       | 2025-0609  |
   | 2          | 0107       | 2025-0609  |
   +------------+------------+------------+

4. Go to the oss_hdfs_tbl_parquet_pt/ds=2025-0609/ directory in the target Bucket and click the HDFS tab to view the data files generated in the OSS-HDFS directory.

FAQ