OSS external tables

Syntax

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <data_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)] 
STORED BY '<StorageHandler>'  
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>';

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name>
(
<col_name> <data_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)]
[row format serde '<serde_class>'
  [WITH serdeproperties (
    ['<property_name>'='<property_value>',...])
  ]
]
STORED AS <file_format> 
location '<oss_location>' 
[USING '<resource_name>']
[tblproperties ('<tbproperty_name>'='<tbproperty_value>',...)];

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> 
(
<col_name> <date_type>,
...
)
[comment <table_comment>]
[partitioned BY (<col_name> <data_type>, ...)] 
STORED BY '<StorageHandler>' 
WITH serdeproperties (
 ['<property_name>'='<property_value>',...]
) 
location '<oss_location>' 
USING '<jar_name>';

Parameters

The following parameters are common across all external table formats. For format-specific parameters, see the documentation for each format.

• Basic syntax parameters

  Parameter	Required	Description
  mc_oss_extable_name	Yes	The name of the OSS external table to create. Table names are case-insensitive and cannot be force-converted to a specific case.
  col_name	Yes	The name of a column in the OSS external table. The schema of the external table must match the schema of the OSS data file. Otherwise, data cannot be read.
  data_type	Yes	The data type of a column in the OSS external table. Each column's data type must match the corresponding column in the OSS data file. Otherwise, data cannot be read.
  table_comment	No	The comment for the table. The comment must be a valid string of no more than 1,024 bytes. Otherwise, an error is reported.
  partitioned by (col_name data_type, ...)	No	If the data files in OSS are stored in a partitioned path, include this parameter to create a partitioned table. col_name: The name of the partition key column. data_type: The data type of the partition key column.
  '<(tb)property_name>'='<(tb)property_value>'	Yes	Extended properties of the external table. See the format-specific documentation for details.
  oss_location	Yes	The OSS path where the data files are located. By default, all data files in this path are read. The format is oss://<oss_endpoint>/<Bucket name>/<OSS directory name>/. oss_endpoint: The OSS domain name. You must use the classic network endpoint provided by OSS, which contains -internal. Example: oss://oss-cn-beijing-internal.aliyuncs.com/xxx. OSS classic network domain names are listed in Regions and endpoints. Keep the OSS region where the data files are stored the same as the region of your MaxCompute project. If they are in different regions, network connectivity issues may occur. If you do not specify an endpoint, the system uses the endpoint of the region where the current project is located. This method is not recommended because cross-region file storage may cause network connectivity issues. Bucket name: The name of the OSS bucket. The bucket name must follow the oss_endpoint. Example: oss://oss-cn-beijing-internal.aliyuncs.com/your_bucket/path/. View bucket names in List buckets. Directory name: The name of the OSS directory. Do not specify a file name after the directory. Example: oss://oss-cn-beijing-internal.aliyuncs.com/oss-mc-test/Demo1/. Incorrect examples: -- HTTP connections are not supported. http://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- HTTPS connections are not supported. https://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/ -- Incorrect connection address. oss://oss-cn-shanghai-internal.aliyuncs.com/Demo1 -- Do not specify a file name. oss://oss-cn-shanghai-internal.aliyuncs.com/oss-mc-test/Demo1/vehicle.csv Permission specification (RamRole): Explicitly specify (Recommended): Create a custom role, attach an access policy, and use its ARN. STS authorization. Use Default (Not recommended): Use the ARN of the aliyunodpsdefaultrole role.

• WITH serdeproperties attributes

  property_name	Scenario	property_value	Default value
  odps.properties.rolearn	Add this property when using STS authorization.	Specify the ARN of the RAM role that has permissions to access OSS. Obtain the ARN from the role details in the RAM console. Example: acs:ram::xxxxxx:role/aliyunodpsdefaultrole.	If the owners of MaxCompute and OSS are the same account: If you do not specify odps.properties.rolearn in the table creation statement, the ARN of the aliyunodpsdefaultrole role is used by default. You must first create the aliyunodpsdefaultrole role using STS authorization. To use a custom role ARN, first create the custom role. STS authorization for OSS (custom authorization). If the owners of MaxCompute and OSS are different accounts, you must specify the ARN of a custom role. For more information, see STS authorization for OSS (custom authorization).

Whitelist and blacklist

MaxCompute OSS external tables support whitelist and blacklist filtering. By setting whitelist and blacklist parameters in tblproperties, you can filter which files to read from a directory.

• tblproperties parameters

  tblproperties parameters details

  property_name	Use case	Note	property_value	Default
  odps.external.data.file.whitelist.regex	Custom whitelist filtering	Regex pattern	Regex pattern for file matching, e.g., '.*\\.json'	None
  odps.external.data.file.blacklist.regex	Custom blacklist filtering	Regex pattern	Regex pattern for file matching, e.g., '.*\\.txt'	None
  odps.external.data.file.recursive.listing.enabled	Control recursive file search		True False	True
  odps.sql.unstructured.file.pattern.black.list（ existing session flag）	Session-level blacklist filtering	Regex pattern	Regex pattern for file matching, e.g., '.*\\.json'	None

• Effective rules

  • Blacklist takes priority over whitelist: if a file matches any blacklist condition, it is excluded.

  • The session flag and tblproperties blacklist parameter have an "OR" relationship. If both are configured, both take effect — a file matching either blacklist condition is excluded. tblProperties configuration is recommended.

    Configuration method	Path type	Parameter
    session flag	Absolute path	odps.sql.unstructured.file.pattern.black.list
    tblproperties	Relative path	odps.external.data.file.blacklist.regex

• Regex notes

  • Line start ^ and line end $ anchors are optional. For example, to match .json files, .*\.json is sufficient.

  • Only one regex pattern is supported. To exclude multiple file types, use |:

    -- Exclude csv, txt, and parquet files
    "odps.external.data.file.blacklist.regex": ".*\\.(txt|csv|parquet)$" 

• Update whitelist and blacklist parameters

  ALTER TABLE {table_name} 
  SET tblproperties('odps.external.data.file.whitelist.regex'='new_regex')
  
  ALTER TABLE {table_name} 
  SET tblproperties('odps.external.data.file.blacklist.regex'='new_regex')

• Examples

  • Sample data

    -- Assume the OSS directory contains the following files:
    
    oss://xxx/bucket/level1/data1.txt
    oss://xxx/bucket/level1/data2.json
    oss://xxx/bucket/level1/data3.csv
    oss://xxx/bucket/level1/level2/data4.txt
    oss://xxx/bucket/level1/level2/data5.json
    oss://xxx/bucket/level1/level2/data6.parquet

  • Whitelist: only read JSON files

    CREATE EXTERNAL TABLE test_whitelist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED AS textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.whitelist.regex'='.*\\.json'); -- Whitelist: JSON files only
    
    -- By running SELECT * FROM test_whitelist_table, all JSON files are selected.
    -- Query returns data from data2.json and level2/data5.json

  • Blacklist: exclude txt, csv, and parquet files

    CREATE EXTERNAL TABLE test_blacklist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.blacklist.regex'='.*\\.(txt|csv|parquet)$'); -- Blacklist: exclude txt, csv, parquet
    
    -- By running SELECT * FROM test_blacklist_table, all TXT, CSV, and Parquet files are filtered out.
    -- Query returns data from data2.json and level2/data5.json

  • Whitelist and blacklist combined: read txt files, exclude level2 directory

    CREATE EXTERNAL TABLE test_whitelist_blacklist_table
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.whitelist.regex'='.*\\.txt', -- Whitelist: txt files only
      'odps.external.data.file.blacklist.regex'='.*level2/.*'); -- Blacklist: exclude level2 directory
    
    -- By running select * from test_whitelist_blacklist_table，
    -- Whitelist filters txt files, then blacklist excludes level2 directory
    -- Query returns data from data1.txt only

  • Recursive listing: control whether to read files in subdirectories

    CREATE EXTERNAL TABLE test_recursive_enabled
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.whitelist.regex'='.*\\.json' -- Whitelist: json files only
      'odps.external.data.file.recursive.listing.enabled'='true'); -- Recursive enabled
    
    
    -- By running SELECT * FROM test_recursive_enabled, all JSON files are selected.
    -- Query returns data from data2.json and level2/data5.json
    
    
    CREATE EXTERNAL TABLE test_recursive_disabled
    (
        id INT,
        name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.whitelist.regex'='.*\\.json' -- Whitelist: json files only
      'odps.external.data.file.recursive.listing.enabled'='false'  -- Recursive disabled
    );
    
    -- By running SELECT * FROM test_recursive_disabled, all JSON files are selected, but recursive reading is disabled.
    -- Query returns data from data2.json only (level2 directory excluded)

  • Update whitelist and blacklist via ALTER TABLE

    CREATE EXTERNAL TABLE test_alter_table_properties
    (
      id INT,
      name STRING 
    )
    row format serde 'org.apache.hive.hcatalog.data.JsonSerDe'
    STORED as textfile
    LOCATION 'oss://xxx/bucket/level1'
    TBLPROPERTIES (
      'odps.external.data.enable.extension'='true', -- Enable file extension support
      'odps.external.data.file.whitelist.regex'='.*\\.json'); -- Whitelist: json files only
    
    -- By running SELECT * FROM test_alter_table_properties, all JSON files are selected
    -- Query returns data from data2.json and level2/data5.json
    
    ALTER TABLE test_alter_table_properties 
    SET tblproperties(
      'odps.external.data.file.whitelist.regex'='.*level2/.*\\.json'
    )
    -- By running select * from test_alter_table_properties
    -- Query now returns data from level2/data5.json only

Notes

• After you create an OSS external table, you can read data from OSS through the external table. For supported data file types and creation syntax, see Syntax.

• If an SQL statement involves complex data types, prepend set odps.sql.type.system.odps2=true; and submit them together. Data type versions.

• For OSS external tables that map to open source data, set set odps.sql.hive.compatible=true; at the session level before reading data from OSS. Otherwise, an error is reported.

• OSS has bandwidth limits. If read/write traffic exceeds the instance bandwidth limit in a short period, external table performance degrades. Limits and performance metrics.

Syntax

<select_statement> FROM <from_statement>;

• select_statement: The SELECT clause, which queries the data to be inserted into the target table from the source table.

• from_statement: The FROM clause, which specifies the data source, such as the name of the external table.

Query optimization

SET odps.meta.exttable.stats.onlinecollect=true;
SELECT * FROM <tablename>;

-- You can use either of the following parameters.

-- Unit: MiB. Default value: 256 MiB. Applies to internal or external tables.
SET odps.stage.mapper.split.size=<value>;
SELECT * FROM <tablename>;

-- Unit: MiB. Default value: 256 MiB. Applies only to external tables.
SET odps.sql.unstructured.data.split.size=<value>;
SELECT * FROM <tablename>;

-- Syntax for the two-tier model: set odps.sql.split.dop={"project.table": xxx};
-- Syntax for the three-tier model: set odps.sql.split.dop={"project.schema.table": xxx};

SET odps.sql.split.dop={
  "project.schema.table1": xxx, 
  "project.schema.table2": yyy
};

SET odps.sql.common.table.planner.ext.hive.bridge=FALSE;

SELECT * FROM <your_table>;

SET odps.sql.split.dop = {"lakehouse47_3.tpch_1t_parquet_snappy.lineitem": 2};
SET odps.input.file.num.limit = 5000;

Syntax

INSERT {INTO|OVERWRITE} TABLE <table_name> PARTITION (<ptcol_name>[, <ptcol_name> ...]) 
<select_statement> FROM <from_statement>;

To insert data into dynamic partitions, see Insert or overwrite data into dynamic partitions (DYNAMIC PARTITION).

Write data to OSS using the OSS multipart upload feature

To write data to OSS in an open source format, create an external table with an open source data parser and enable the OSS multipart upload feature.

To enable the OSS multipart upload feature, set the following:

setproject odps.sql.unstructured.oss.commit.mode =true;

set odps.sql.unstructured.oss.commit.mode =true;

The default value of odps.sql.unstructured.oss.commit.mode is false. The two modes work as follows:

Manage exported files

Write large files using dynamic partitions

-- The service.mode must be turned off.
SET odps.service.mode=off;

-- The dynamic partition capability must be enabled.
SET odps.sql.reshuffle.dynamicpt=true; 	

-- Set the desired data consumption for each reducer. Assume you want each file to be 4 GB.
SET odps.adaptive.shuffle.desired.partition.size=4096;	

• How do I resolve the "Inline data exceeds the maximum allowed size" error when processing OSS data using an external table?

• How do I resolve a memory overflow error that occurs after I upload a UDF to access an OSS external table on MaxCompute, even though the UDF passed local tests?

• How do I merge multiple small files into a single file using an OSS external table?

• How do I resolve the "Couldn't connect to server" error when reading from an OSS external table?

• How do I resolve the "Network is unreachable (connect failed)" error when creating an OSS external table?

• How do I resolve slow SQL job execution on an OSS external table?

• How do I resolve the issue where old data is deleted but new data is not written when using the OSS multipart upload feature?

• Solution for the "table not found" error when accessing an OSS external table from Spark

How do I resolve the "Inline data exceeds the maximum allowed size" error when processing OSS data using an external table?

• Problem

  When processing OSS data, the error Inline data exceeds the maximum allowed size is reported.

• Cause

  OSS Store has a size limit for each small file. An error is reported if a file exceeds 3 GB.

• Solution

  Adjust the following two properties to control the data size each reducer writes to the external table, keeping files within the 3 GB limit.

  set odps.sql.mapper.split.size=256; # Adjusts the size of data read by each mapper, in MB.
  set odps.stage.reducer.num=100; # Adjusts the number of workers in the reduce stage.

How do I resolve a memory overflow error that occurs after I upload a UDF to access an OSS external table on MaxCompute, even though the UDF passed local tests?

• Problem

  When accessing an OSS external table on MaxCompute, a UDF that passed local tests returns the following error after being uploaded.

  FAILED: ODPS-0123131:User defined function exception - Traceback:
  java.lang.OutOfMemoryError: Java heap space        

  After setting the following parameters, the running time increases, but the error persists.

  set odps.stage.mapper.mem = 2048; 
  set odps.stage.mapper.jvm.mem = 4096;       

• Cause

  There are too many object files in the external table, which causes excessive memory usage, and no partitions are set.

• Solution

  • Use a smaller amount of data for the query.

  • Partition the object files to reduce memory usage.

How do I merge multiple small files into a single file using an OSS external table?

Check the Logview log to see if the last stage in the SQL execution plan is a reducer or a joiner.

• If it is a reducer, run the statement set odps.stage.reducer.num=1;

• If it is a joiner, run the statement set odps.stage.joiner.num=1;

How do I resolve the "Couldn't connect to server" error when reading from an OSS external table?

• Problem

  When reading data from an OSS external table, the error ODPS-0123131:User defined function exception - common/io/oss/oss_client.cpp(95): OSSRequestException: req_id: , http status code: -998, error code: HttpIoError, message: Couldn't connect to server is reported.

• Cause

  • Cause 1: When the OSS external table was created, a public endpoint was used for the oss_endpoint in the oss_location address, instead of an internal endpoint.

  • Cause 2: When the OSS external table was created, the endpoint of another region was used for the oss_endpoint in the oss_location address.

• Solution

  • For Cause 1

    Verify that the oss_endpoint in oss_location is an internal endpoint. If it is a public endpoint, change it to an internal endpoint. See Parameters.

    For example, if a user in the Indonesia (Jakarta) region used the address oss://oss-ap-southeast-5.aliyuncs.com/<bucket>/.... to create an external table, it should be changed to the corresponding internal address oss://oss-ap-southeast-5-internal.aliyuncs.com/<bucket>/.....

  • For Cause 2

    Verify that the oss_endpoint in oss_location matches the region you want to access. OSS classic network domain names are listed in Regions and endpoints.

How do I resolve the "Network is unreachable (connect failed)" error when creating an OSS external table?

• Problem

  When creating an OSS external table, the error ODPS-0130071:[1,1] Semantic analysis exception - external table checking failure, error message: Cannot connect to the endpoint 'oss-cn-beijing.aliyuncs.com': Connect to bucket.oss-cn-beijing.aliyuncs.com:80 [bucket.oss-cn-beijing.aliyuncs.com] failed: Network is unreachable (connect failed) is reported.

• Cause

  When the OSS external table was created, a public endpoint was used for the oss_endpoint in the oss_location address, instead of an internal endpoint.

• Solution

  Verify that the oss_endpoint in oss_location is an internal endpoint. If it is a public endpoint, change it to an internal endpoint. See Parameters.

  For example, if a user in the China (Beijing) region used the address oss://oss-cn-beijing.aliyuncs.com/<bucket>/.... to create an external table, it should be changed to the corresponding internal address oss://oss-cn-beijing-internal.aliyuncs.com/<bucket>/.....

How do I resolve slow SQL job execution on an OSS external table?

• Slow reading of GZ compressed files in an OSS external table

  • Symptoms

    A user created an OSS external table with a data source of a 200 GB GZ compressed file in OSS. The data reading process is slow.

  • Cause

    The SQL processing speed is slow because too few mappers are executing the computation in the map stage.

  • Solution

    • For structured data, you can set the following parameter to adjust the amount of data read by a single mapper to speed up SQL execution.

      set odps.sql.mapper.split.size=256; # Adjusts the size of table data read by each mapper, in MB.       

    • For unstructured data, check whether there is only one OSS file in the OSS external table path. If there is only one, only one mapper can be generated because unstructured data in a compressed format cannot be split. This results in slow processing speed. We recommend that you split the large OSS file into smaller files in the corresponding external table path on OSS. This increases the number of mappers that are generated when the external table is read and improves the reading speed.

• Slow search of MaxCompute external table data using an SDK

  • Symptoms

    Searching MaxCompute external table data using an SDK is slow.

  • Solution

    External tables only support full table scans, which is slow. Use a MaxCompute internal table instead.

How do I resolve the issue where old data is deleted but new data is not written when using the OSS multipart upload feature?

• Problem

  In an insert overwrite scenario, if the job fails in extreme cases, the result may be inconsistent with expectations. The old data is deleted, but the new data is not written.

• Cause

  The newly written data fails to be written to the target table due to a very low probability of a hardware failure or metadata update failure. The delete operation in OSS does not support rollback, so the deleted old data cannot be recovered.

• Solution

  • If you are overwriting an OSS external table based on its old data, for example, insert overwrite table T select * from table T;, back up the OSS data in advance. If the job fails, you can then overwrite the OSS external table based on the backed-up old data.

  • If the insert overwrite job can be resubmitted, simply resubmit the job if it fails.

Solution for the "table not found" error when accessing an OSS external table from Spark

• Problem

  When using Spark to access an OSS external table, the task fails with a "table not found" error.

• Solution

  • Add the following parameters:

    • Enable external table configuration:spark.sql.catalog.odps.enableExternalTable=true;

    • Configure the region where OSS is located:spark.hadoop.odps.oss.region.default=cn-<region>

  • If the error persists after you add the above parameters:

    at java.lang.Thread.run(Thread.java:745)
    Caused by: java.lang.Exception: com.aliyun.odps.cupid.CupidException: connectionId and rolearn are mutually exclusive
        at com.aliyun.odps.cupid.table.v1.commontable.impl.reader.CupidCommonTableReadSession.splitTable

    Recreate the OSS external table and then access it again.