The BigQuery public datasets provides a helpful repository of open access datasets to connect to if you don’t have your own data available to connect yet.
In Omni we can create connections to and query these datasets if you follow the steps below (Omni’s documentation for connecting to BigQuery can be found here:
- Create a BigQuery service account according to these steps, note you must have Google Cloud admin permissions to create a service account.
- In your Omni instance, to add a BigQuery connection:
(i) Navigate to the Admin tab.
(ii) SelectConnectionsmenu option on the left hand panel
(iii) Select theAdd Connectionbutton in the top right
(iV). Select theBigQuerybutton
- Fill out the connection details for your BigQuery connection (Further Omni Documentation Available for setting up the BigQuery Connection). In this example, connecting to a public dataset you can either set the Default Dataset or Include Other Projects to
bigquery-public-data. If you want to main connection to be to the public dataset use the former and if you plan on using this connection for your own database then it’s best to include thebigquery-public-datain Include Other Projects.
Once you’ve filled in all the details, you can update and test the connection and wait for it to complete successfully.
- We’ll use the US Bureau census data. The
data-catalog.schema.tableis highlighted in the screenshot below.
Navigate back to the workbook environment. You can query this table in Omni by writing the SQL query directly and running it. Clicking on the SQL button will open the SQL editor which you can edit in. Pressing the play button (shortcut CMD+Enter) will run your query.
The SQL query running here is:
SELECT * FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2000`
Note do not apply any LIMIT in the query.
- Save this query as a view and name it.
- It’s now available for us as a table in the field picker.
