Creating a Partition Table with Data Lake Analytics

Alibaba Cloud Data Lake Analytics (DLA) is a serverless big-data query and analysis service, which enables you to directly query and analyze data stored in Object Storage Service (OSS) and Table Store instances by using standard SQL statements.

In a relational database, you can partition a table with a big data volume to improve the query performance. Similarly, you can use partition tables in DLA to break down data and shorten the query response time.

The following takes an OSS data source as an example to explain how to create and use a partition table in DLA.

Creating a Partition Table

To create a partition table in DLA, run the table creation statement with PARTITIONED BY. For example:

Directory Structure of the Partition Table in the OSS Instance

DLA can map directories or files in the OSS instance into a table. The data in the table is the content of the files in the OSS instance.

The partition columns in a partition table are mapped to directories conforming to a special naming rule in the OSS instance.

For the table creation statement in the preceding example, the following directory structure must be available:

  1. A partition column is mapped to a subdirectory under LOCATION of the table. The naming rule of a directory is as follows: Partition column name=Partition value.
  2. If multiple partition columns exist, they must be nested in the specified sequence of partition columns in the table creation statement.

Updating the Partition Information

After successfully creating the table, run the MSCK REPAIR TABLE command to synchronize the partition information to DLA.

After successful execution of the MSCK command, you can run the SHOW PARTITIONS statement to view all the partition information in the table.

Querying a Partition Table

When querying the entire table, you obtain the data in all partitions.

When running the SELECT * statement, you can find that the partition columns are displayed as columns at the back of data columns defined in the table.

During query, you can filter data by partition column.

Additional Notes

  1. The nesting sequence of the directories in the OSS instance must be consistent with that of the corresponding partition columns specified in the partition table.
  2. For example, for the directory structure in the preceding example, the following table creation statement is incorrect:

The partition table scans only the data under the directories mapped to partition columns.

For the following directory structure, if the partition columns specified in the table creation statement are p and q, only kv3.txt is the data file of this table, and kv1.txt and kv2.txt are excluded.


Follow me to keep abreast with the latest technology news, industry insights, and developer trends.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store