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:

CREATE EXTERNAL TABLE tbl3_part 
(col1 int, col2 string)
PARTITIONED BY (p string, q string)
STORED AS TEXTFILE
LOCATION 'oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/';

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:

$osscmd ls oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3
prefix list is:
object list is:
2018-08-08 14:23:17 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/p=3/q=3/kv1.txt
2018-08-08 18:01:08 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/p=30/q=30/kv1.txt

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.

MSCK REPAIR TABLE tbl3_part;

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

mysql> show partitions tbl3_part;
+-----------+
| Result    |
+-----------+
| p=3/q=3   |
| p=30/q=30 |
+-----------+

Querying a Partition Table

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

mysql> select count(*) from tbl3_part;
+-------+
| _col0 |
+-------+
|  1000 |
+-------+

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.

mysql> select * from tbl3_part limit 3;
+------+---------+------+------+
| foo  | bar     | p    | q    |
+------+---------+------+------+
|  238 | val_238 | 3    | 3    |
|   86 | val_86  | 3    | 3    |
|  311 | val_311 | 3    | 3    |
+------+---------+------+------+

During query, you can filter data by partition column.

mysql> select count(*) from tbl3_part where p='3';
+-------+
| _col0 |
+-------+
|   500 |
+-------+

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. For example, for the directory structure in the preceding example, the following table creation statement is incorrect:

   CREATE EXTERNAL TABLE tbl3_part 
   (col1 int, col2 string)
   PARTITIONED BY (q string, p string)
   STORED AS TEXTFILE
   LOCATION 'oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table3/';

   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.

   $osscmd ls oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/
   prefix list is:
   object list is:
   2018-08-08 14:23:56 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/kv1.txt
   2018-08-08 14:23:48 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/p=4/kv2.txt
   2018-08-08 14:23:40 5.68KB Standard oss://oss-jinluo-openanalytics-test/datasets/test/test_partition/table4/p=4/q=4/kv3.txt

2. If a new partition corresponding to an OSS directory is added, run the MSCK REPAIR TABLE table_name command to make the new partition directory effective for query.

To learn more about Alibaba Cloud Data Lake Analytics (DLA), visit www.alibabacloud.com/products/data-lake-analytics.