DEA-C01 Practice Question 56: Glue Partition Index Spectrum
AWS Certified Data Engineer – Associate (DEA-C01) · Test 6 · question 6 of 10
A Redshift Spectrum external table maps to a Data Catalog table partitioned by country, category, and event_date, with about 900,000 partitions. Spectrum queries that filter on country and event_date spend most of their elapsed time before any S3 data is scanned. The same table is also read by an Amazon EMR Spark job. The partition layout and the queries cannot change.
Which action will reduce the partition-retrieval time for these queries?
Show answer and explanation
Answer:
The pre-scan time is the GetPartitions call. As the documentation puts it: "If no partition indexes are present on the table, AWS Glue loads all the partitions of the table, and then filters the loaded partitions using the query expression provided by the user in the GetPartitions request. The query takes more time to run as the number of partitions increase on a table with no indexes. With an index, the GetPartitions query will try to fetch a subset of the partitions instead of loading all the partitions in the table." The leading key matters: "The first key of the index should be passed in the expression for the indexes to be used in filtering", which is why the index must start with country. Both engines here benefit without any change to the queries: "Redshift Spectrum, Amazon EMR and AWS Glue ETL Spark DataFrames are able to utilize indexes for fetching partitions after indexes are in an ACTIVE state in AWS Glue."
Option A is the right idea for the wrong engine. Partition projection is an Athena feature and the Athena documentation states that it "is usable only when the table is queried through Athena. If the same table is read through another service such as Amazon Redshift Spectrum, Athena for Spark, or Amazon EMR, the standard partition metadata is used." Neither consumer here is Athena.
Option B addresses registration, not retrieval. MSCK REPAIR TABLE adds missing partitions to the catalog; running it more often does not make the GetPartitions call that already loads 900,000 partitions any faster; if anything it adds more of them.
Option C would help, but it is explicitly out of bounds: the stem says the partition layout cannot change. It also destroys the country and category pruning that the existing queries rely on.
Reference: Creating partition indexes