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).

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 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. 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. 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, as shown below:

    

    Recreate the OSS external table and then access it again.