- (Exam Topic 1)
Your company is using WHILECARD tables to query data across multiple tables with similar names. The SQL statement is currently failing with the following error:
# Syntax error : Expected end of statement but got “-“ at [4:11] SELECT age
FROM
bigquery-public-data.noaa_gsod.gsod WHERE
age != 99
AND_TABLE_SUFFIX = ‘1929’ ORDER BY
age DESC
Which table name will make the SQL statement work correctly?

1. A. ‘bigquery-public-data.noaa_gsod.gsod‘
2. B. bigquery-public-data.noaa_gsod.gsod*
3. C. ‘bigquery-public-data.noaa_gsod.gsod’*
4. D. ‘bigquery-public-data.noaa_gsod.gsod*`

Correct Answer: D

- (Exam Topic 6)
You want to migrate an on-premises Hadoop system to Cloud Dataproc. Hive is the primary tool in use, and the data format is Optimized Row Columnar (ORC). All ORC files have been successfully copied to a Cloud Storage bucket. You need to replicate some data to the cluster’s local Hadoop Distributed File System (HDFS) to maximize performance. What are two ways to start using Hive in Cloud Dataproc? (Choose two.)

1. A. Run the gsutil utility to transfer all ORC files from the Cloud Storage bucket to HDF
2. B. Mount the Hive tables locally.
3. C. Run the gsutil utility to transfer all ORC files from the Cloud Storage bucket to any node of the Dataproc cluste
4. D. Mount the Hive tables locally.
5. E. Run the gsutil utility to transfer all ORC files from the Cloud Storage bucket to the master node of the Dataproc cluste
6. F. Then run the Hadoop utility to copy them do HDF
7. G. Mount the Hive tables from HDFS.
8. H. Leverage Cloud Storage connector for Hadoop to mount the ORC files as external Hive table
9. I. Replicate external Hive tables to the native ones.
10. J. Load the ORC files into BigQuer
11. K. Leverage BigQuery connector for Hadoop to mount the BigQuery tables as external Hive table
12. L. Replicate external Hive tables to the native ones.

Correct Answer: BC

- (Exam Topic 6)
You have a query that filters a BigQuery table using a WHERE clause on timestamp and ID columns. By using bq query – -dry_run you learn that the query triggers a full scan of the table, even though the filter on timestamp and ID select a tiny fraction of the overall data. You want to reduce the amount of data scanned by BigQuery with minimal changes to existing SQL queries. What should you do?

1. A. Create a separate table for each ID.
2. B. Use the LIMIT keyword to reduce the number of rows returned.
3. C. Recreate the table with a partitioning column and clustering column.
4. D. Use the bq query - -maximum_bytes_billed flag to restrict the number of bytes billed.

Correct Answer: B

- (Exam Topic 6)
Your organization has been collecting and analyzing data in Google BigQuery for 6 months. The majority of the data analyzed is placed in a time-partitioned table named events_partitioned. To reduce the cost of queries, your organization created a view called events, which queries only the last 14 days of data. The view is described in legacy SQL. Next month, existing applications will be connecting to BigQuery to read the events data via an ODBC connection. You need to ensure the applications can connect. Which two actions should you take? (Choose two.)

1. A. Create a new view over events using standard SQL
2. B. Create a new partitioned table using a standard SQL query
3. C. Create a new view over events_partitioned using standard SQL
4. D. Create a service account for the ODBC connection to use for authentication
5. E. Create a Google Cloud Identity and Access Management (Cloud IAM) role for the ODBC connectionand shared “events”

Correct Answer: AE

- (Exam Topic 6)
You have historical data covering the last three years in BigQuery and a data pipeline that delivers new data to BigQuery daily. You have noticed that when the Data Science team runs a query filtered on a date column and limited to 30–90 days of data, the query scans the entire table. You also noticed that your bill is increasing more quickly than you expected. You want to resolve the issue as cost-effectively as possible while maintaining the ability to conduct SQL queries. What should you do?

1. A. Re-create the tables using DD
2. B. Partition the tables by a column containing a TIMESTAMP or DATE Type.
3. C. Recommend that the Data Science team export the table to a CSV file on Cloud Storage and use Cloud Datalab to explore the data by reading the files directly.
4. D. Modify your pipeline to maintain the last 30–90 days of data in one table and the longer history in a different table to minimize full table scans over the entire history.
5. E. Write an Apache Beam pipeline that creates a BigQuery table per da
6. F. Recommend that the Data Science team use wildcards on the table name suffixes to select the data they need.

Correct Answer: C