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.
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/';
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
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 | +-----------+
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 | +-------+
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
To learn more about Alibaba Cloud Data Lake Analytics (DLA), visit www.alibabacloud.com/products/data-lake-analytics
Alibaba EMR - May 20, 2022
Alibaba Clouder - August 8, 2018
Alibaba Clouder - July 5, 2019
Alibaba Cloud Data Lake Analytics - February 26, 2019
Alibaba Clouder - November 15, 2018
Apache Flink Community China - June 8, 2021
Alibaba Cloud provides big data consulting services to help enterprises leverage advanced data technology.Learn More
Alibaba Cloud experts provide retailers with a lightweight and customized big data consulting service to help you assess your big data maturity and plan your big data journey.Learn More
Deploy custom Alibaba Cloud solutions for business-critical scenarios with Quick Start templates.Learn More
Build a Data Lake with Alibaba Cloud Object Storage Service (OSS) with 99.9999999999% (12 9s) availability, 99.995% SLA, and high scalabilityLearn More
More Posts by Alibaba Clouder