Answer-first summary for fast verification
Answer: The new nested Field is added to the target schema, and dynamically read as NULL for existing unmatched records.
The MERGE operation with schema evolution enabled allows for the addition of new nested fields without requiring changes to existing data files. The new 'coupon' field in the 'items' array is added to the target schema, and existing records will dynamically return NULL for this new field when queried, as their underlying data files are not rewritten to include the new field. This behavior is correctly described in option B. Option D is incorrect because it suggests that existing files are updated to include NULL values for the new field, which does not happen with schema evolution. The missing 'email' column in the source does not affect the handling of the new nested field, as schema evolution focuses on adding new fields rather than enforcing the presence of existing ones.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
Given a table Carts with schema (id LONG, items ARRAY<STRUCT<id: LONG, count: INT>>, email STRING) containing the following data:
1001 | [{"id": "DESK65", "count": 1}] | "u1@domain.com"
1002 | [{"id": "KYBD45", "count": 1}, {"id": "M27", "count": 2}] | "u2@domain.com"
1003 | [{"id": "M27", "count": 1}] | "u3@domain.com"
1001 | [{"id": "DESK65", "count": 1}] | "u1@domain.com"
1002 | [{"id": "KYBD45", "count": 1}, {"id": "M27", "count": 2}] | "u2@domain.com"
1003 | [{"id": "M27", "count": 1}] | "u3@domain.com"
The following MERGE statement with schema evolution enabled is executed:
MERGE INTO carts c
USING updates u
ON c.id = u.id
WHEN MATCHED
THEN UPDATE SET *
MERGE INTO carts c
USING updates u
ON c.id = u.id
WHEN MATCHED
THEN UPDATE SET *
How would this update be processed when applying the following record from the updates view that contains:
coupon) in the items arrayemail)id: 1001
items: [{"id": "DESK65", "count": 2, "coupon": "BOG050"}]
id: 1001
items: [{"id": "DESK65", "count": 2, "coupon": "BOG050"}]
A
The update throws an error because changes to existing columns in the target schema are not supported.
B
The new nested Field is added to the target schema, and dynamically read as NULL for existing unmatched records.
C
The update is moved to a separate "rescued" column because it is missing a column expected in the target schema.
D
The new nested field is added to the target schema, and files underlying existing records are updated to include NULL values for the new field.
No comments yet.