The 'Used datasets' tab displays a list of BigQuery datasets that are used in the Transformation. Here you can also check if the dataset has been granted access to the OWOX BI service account. If access is not granted, the Transformation will not run successfully. In this article, we explain how to configure access to the used datasets and successfully launch your Transformation.
How it works
The list of used datasets is formed based on:
- SQL code of each Operation;
- Destination fields in each Operation.
If you create the Transformation from one of the templates, please follow this instruction to set up Variables and share Used datasets.
If you create the Transformation from scratch, immediately after creating the Transformation, this list of Used datasets is empty. Therefore, you will see a message on the screen, as shown in the screenshot below.
After you write the SQL code, configure the Destination, and click the 'Save transformation' button to save your changes. The list of datasets will be updated after a successful saving of the Transformation. If there is no access to one or more datasets, you will see a warning message "No access to datasets" next to the 'Run' button after saving.
Go to the 'Used datasets' tab to view the list of used datasets that displays:
- Project and dataset IDs;
- Status:
- ‘No access’ means that the OWOX BI service account does not have access to the dataset;
- ‘Granted’ means that access to the dataset is configured, and OWOX BI can read and modify data in that dataset.
By clicking on the Dataset ID, you can expand additional information about it.
For 'No access' status you will see the text message and the 'Share dataset' button:
Note:Every time you save changes in the Transformation, the list of datasets is updated. Be attentive when writing SQL code and avoid typos in project, dataset, and table names.
For 'Granted' status you will see the list of Operations that use this dataset and a link to the Google BigQuery interface:
How to share BigQuery dataset with OWOX BI
If a Transformation uses data from BigQuery datasets that are not accessible to OWOX BI, the Transformation cannot be successfully executed.
To run the Transformation and obtain the execution results for each Operation, you need to grant access to all the used datasets. To do this, click on the 'Share used datasets' button.
Open this instruction and follow steps 4 and 5 to grant access to each dataset. You need to authenticate with a Google account that you use in BigQuery with either the 'BigQuery Admin' or 'BigQuery Data Owner' role.
After successful authentication, please wait while the sharing process will complete.
If everything goes smoothly, the statuses in the dataset list will be updated, and you will see green text indicating 'Granted'. If any of the datasets fail to be shared, you will be presented with an error description along with recommendations for the next steps. Example of the error description:
To view the error log click on the "?" icon. Close this popup, fix errors, and try to share the datasets again by clicking on the 'Share used datasets' button.
When all datasets have the 'Granted' status, the red warning text next to the 'Run' button will disappear.
You are all done.
How to use Google Sheets document as a data source for BigQuery table
If you use a BigQuery table with data from Google Sheet documents then check this article.
0 Comments