Answer-first summary for fast verification
Answer: Construct a query to return every row of the table CLICK_STREAM, while using the built-in function to cast strings from the column DT into TIMESTAMP values. Run the query into a destination table NEW_CLICK_STREAM, in which the column TS is the TIMESTAMP type. Reference the table NEW_CLICK_STREAM instead of the table CLICK_STREAM from now on. In the future, new data is loaded into the table NEW_CLICK_STREAM.
Option E is the best choice because it modifies the schema with minimal data movement. This option avoids the need to delete and recreate the entire CLICK_STREAM table, which is time-consuming and requires reloading all data again. By constructing a query that casts the existing DT column as TIMESTAMP and runs it into a new destination table, NEW_CLICK_STREAM, you can gradually migrate to the new data format. Future queries referencing the new table will benefit from the optimized data type for timestamp operations, making them computationally efficient.
Author: LeetQuiz Editorial Team
Ultimate access to all questions.
No comments yet.
You have spent a few days loading data from comma-separated values (CSV) files into a Google BigQuery table named CLICK_STREAM. This table includes a column called DT that stores the epoch time of click events. For simplicity and convenience, you initially defined every field in the table with a STRING data type. Now, your goal is to compute the web session durations for users who visit your site. To achieve accurate computation, you need to change the data type of the DT column from STRING to TIMESTAMP. You also want to minimize the migration effort while ensuring that future queries do not become computationally expensive. What should you do?
A
Delete the table CLICK_STREAM, and then re-create it such that the column DT is of the TIMESTAMP type. Reload the data.
B
Add a column TS of the TIMESTAMP type to the table CLICK_STREAM, and populate the numeric values from the column TS for each row. Reference the column TS instead of the column DT from now on.
C
Create a view CLICK_STREAM_V, where strings from the column DT are cast into TIMESTAMP values. Reference the view CLICK_STREAM_V instead of the table CLICK_STREAM from now on.
D
Add two columns to the table CLICK_STREAM: TS of the TIMESTAMP type and IS_NEW of the BOOLEAN type. Reload all data in append mode. For each appended row, set the value of IS_NEW to true. For future queries, reference the column TS instead of the column DT, with the WHERE clause ensuring that the value of IS_NEW must be true.
E
Construct a query to return every row of the table CLICK_STREAM, while using the built-in function to cast strings from the column DT into TIMESTAMP values. Run the query into a destination table NEW_CLICK_STREAM, in which the column TS is the TIMESTAMP type. Reference the table NEW_CLICK_STREAM instead of the table CLICK_STREAM from now on. In the future, new data is loaded into the table NEW_CLICK_STREAM.