DEA-C01 Exam - Question 81

Question

A data engineer creates an AWS Glue Data Catalog table by using an AWS Glue crawler that is named Orders. The data engineer wants to add the following new partitions:

s3://transactions/orders/order_date=2023-01-01

s3://transactions/orders/order_date=2023-01-02

The data engineer must edit the metadata to include the new partitions in the table without scanning all the folders and files in the location of the table.

Which data definition language (DDL) statement should the data engineer use in Amazon Athena?

Examice · Accepted Answer

To add new partitions to an existing table in Amazon Athena without scanning all folders and files, the appropriate DDL statement is ALTER TABLE ... ADD PARTITION. Using this command, the data engineer can specify and add the new partitions directly. The following statements accomplish this task: ALTER TABLE Orders ADD PARTITION(order_date='2023-01-01') LOCATION 's3://transactions/orders/order_date=2023-01-01'; ALTER TABLE Orders ADD PARTITION(order_date='2023-01-02') LOCATION 's3://transactions/orders/order_date=2023-01-02'. This approach meets the requirement of adding the new partitions without a full scan.

tgv · Answer

A is correct because it uses the appropriate DDL statements to add the new partitions directly without scanning all folders and files, meeting the requirements stated in the question.
B is incorrect because while it would update the partitions, it would involve scanning all files and folders.
C is incorrect because REPAIR TABLE is not a valid command.
D is incorrect because it modifies partitions instead of adding new ones.

artworkad · Answer

A is correct as per https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html

artworkad · Answer

A is correct as per https://docs.aws.amazon.com/athena/latest/ug/alter-table-add-partition.html

Ja13 · Answer

Why the Other Options Are Incorrect:
Option B: MSCK REPAIR TABLE Orders: This command is used to repair the partitions of a table by scanning all the files in the specified location. This is not efficient if you know the specific partitions you want to add, as it will scan the entire table location.
Option C: REPAIR TABLE Orders: This is not a valid Athena DDL command.
Option D: ALTER TABLE Orders MODIFY PARTITION: This command is used to modify the location of existing partitions, not to add new partitions. It would not work for adding new partitions.

DEA-C01 Exam - Question 81

Discussion