MaxCompute:Use the UNLOAD command

Feature description

MaxCompute allows you to execute UNLOAD statements to export data from MaxCompute to the external data store OSS or Hologres. OSS can store data in the CSV format or another open source format. Take note of the following points:

• MaxCompute data must be exported to OSS before the data can be exported to other compute engines, such as Amazon Redshift and BigQuery.

• If you repeatedly export data, the previously exported file is not overwritten. Instead, a new file is generated.

Usage notes

• The dual-signature authorization mode is not supported when you export MaxCompute data to Hologres.

• Exporting MaxCompute data to Hologres partitioned tables is not supported.

• You cannot add file extensions to exported files in open-source formats.

Authorization

1. The operating account must have the required MaxCompute permissions.

   Before you run the UNLOAD command, the operating account must have at least the read (Select) permission on the table data that you want to export from the MaxCompute project. For more information about how to grant permissions, see MaxCompute permissions.

2. The operating account must have the required permissions on the data source for the external storage.

   Before you export data from MaxCompute, you must grant MaxCompute access to the external storage, such as OSS or Hologres. The authorization mode for the UNLOAD command is the same as that for MaxCompute foreign tables. The authorization methods for OSS and Hologres are as follows:

   • OSS: OSS uses a more secure one-click authorization method. The role name is defined as AliyunODPSDefaultRole. For more information, see STS authorization.

   • Hologres: To complete the authorization, create a RAM role, grant it permissions to allow MaxCompute access, and then add the role to the Hologres instance. For more information, see Hologres external tables.

Export data

UNLOAD FROM {<select_statement>|<table_name> [PARTITION (<pt_spec>)]} 
INTO 
LOCATION <external_location>
STORED BY <StorageHandler>
[WITH SERDEPROPERTIES ('<property_name>'='<property_value>',...)];

UNLOAD FROM {<select_statement>|<table_name> [PARTITION (<pt_spec>)]}
INTO 
LOCATION <external_location>
[ROW FORMAT SERDE '<serde_class>' 
  [WITH SERDEPROPERTIES ('<property_name>'='<property_value>',...)]
]
STORED AS <file_format>
[PROPERTIES ('<tbproperty_name>'='<tbproperty_value>')];

Set file prefixes, suffixes, and extensions

When you use the UNLOAD command to export a MaxCompute table to a file, you can customize the file's prefix and suffix, and generate the extension that corresponds to the file format by default.

Export data to downstream systems through OSS

MaxCompute can use OSS as intermediate storage to export data to various databases that are not directly supported, such as the Time Series Database (TSDB) InfluxDB. The steps are as follows:

1. Use the UNLOAD command to export data to OSS.

2. Download the OSS data to your local environment.

3. Use the import tool or command of the destination database to load the data.

Billing

• Compute costs: The UNLOAD command itself is not billed. However, the query clause within the UNLOAD command scans data and consumes compute resources to calculate results. Therefore, the query clause is billed as a regular SQL job.

• Storage costs: Storing structured data in OSS can save storage costs in some scenarios, but you should estimate the costs in advance.

  • The storage cost for MaxCompute is 0.018 USD/GB/month. For more information about storage billing, see Storage fees (pay-as-you-go). The data compression ratio for data that is imported into MaxCompute is about 5:1. You are charged based on the size of the data after compression.

  • OSS Standard storage costs USD 0.018/GB/month. Other storage classes, such as Infrequent Access, Archive, and Cold Archive, are also available. For more information, see Storage fees.

    If you export data only to save on storage costs, estimate the compression ratio based on the features of your data. You should also estimate the UNLOAD costs based on the query statement used for the export and assess how you will access the exported data to avoid extra costs from unnecessary data migration.

References

• To import data in CSV or other open-source formats from external storage to MaxCompute, see LOAD.

• For information about the syntax and parameters for creating, reading from, and writing to OSS foreign tables, see OSS foreign tables.

• For more information about Hologres foreign tables, see Hologres foreign tables.

FAQ

Q: When I export data from a MaxCompute table to external storage, such as OSS or Hologres, can I enclose each field in double quotation marks? For example: "Cloud-native Big Data Computing Service", "MaxCompute", "Hello".

A: Yes, you can. This can be achieved by modifying the field data or by changing the table format.

• Modify the field data: You can manually concatenate strings using the CONCAT function.

• Adjust the table format: When you use the open-source data Resolver org.apache.hadoop.hive.serde2.OpenCSVSerde to create a foreign table, double quotation marks are added to each field of the exported data by default.

  If you use the built-in text data resolver com.aliyun.odps.CsvStorageHandler/TsvStorageHandler to create an OSS foreign table, this output format is not supported, and no parameters are available to enable this feature.

For more information, see the descriptions of data resolvers and parameters in OSS foreign tables.