Google Professional Cloud Database Engineer Question 153
Select 4Google Cloud PlatformYou are managing a PostgreSQL database hosted on Cloud SQL, and you need to migrate the data to a BigQuery dataset for analytics. The migration should be performed efficiently and securely, ensuring that sensitive data is not exposed during the process. Which steps should you take to accomplish this task?
- A
Export the PostgreSQL database to a CSV file using the
pg_dumputility, then upload the CSV file to a Cloud Storage bucket. - B
Use the BigQuery Data Transfer Service to directly transfer data from the PostgreSQL database to BigQuery.
- C
Grant the BigQuery service account read access to the Cloud Storage bucket where the exported data is stored.
- D
Import the data from the Cloud Storage bucket into BigQuery using the BigQuery import functionality.
- E
Enable SSL encryption for the PostgreSQL connection during the data export process.
Show answer and explanation
Correct answers: A, C, D, E
Explanation
To migrate data from a PostgreSQL database on Cloud SQL to BigQuery securely and efficiently, you should export the data to a Cloud Storage bucket (e.g., using pg_dump), grant BigQuery service account access to the bucket, and import the data into BigQuery. Additionally, enabling SSL encryption ensures data security during the export process. The BigQuery Data Transfer Service is not applicable for PostgreSQL databases, so this option is incorrect.
- A. Correct.
Exporting the PostgreSQL database to a CSV file is an efficient way to prepare the data for import into BigQuery. The
pg_dumputility is commonly used for this purpose. - B. Incorrect.
The BigQuery Data Transfer Service does not support direct transfers from PostgreSQL databases. This is not a valid option for this scenario.
- C. Correct.
Granting the BigQuery service account access to the Cloud Storage bucket ensures that BigQuery can securely retrieve the exported data for import.
- D. Correct.
Using the BigQuery import functionality allows you to load the exported data from the Cloud Storage bucket into BigQuery for analytics.
- E. Correct.
Enabling SSL encryption helps secure the data during the export process, ensuring sensitive data is not exposed in transit.