Querying External data sources in BigQuery
Also read BigQuery roles and BigQuery File Formats for loading data
Querying external datasets is fairly straightforward in Google BigQuery
- Create a table definition file for the file in the Google Cloud Storage bucket
- Link the data source as a table in the BigQuery dataset
- Query the table in the BigQuery dataset
Step 1 - Create a Table Definition
bq mkdef --autodetect --source_format=CSV "gs://mygcpproject/accountdetails.csv" > tabledefinition.txt
Step 2 - Use the table definition to query bq query --external_table_definition=employees::tabledefinition.txt 'SELECT account_id FROM accounts;'
To create a permanent linked table, use this command
bq mk --external_table_definition=tabledefinition.txt AccountDetails.Accounts_Linked
Summary
Querying external tables is not too different from querying BigQuery tables. One can do this with a temporary link or a permanent table link.
Leave a Reply