In order for us to be able to have code control of what goes into BigQuery we introduced sql_views in the repository under bigquery.

Here we have a materialized_view that maps back to one with the same name in Google BigQuery in the cloud.

Steps for creating new data for a dashboard:

Here are the steps we need to follow to get data into a new dashboard or new table in BigQuery:

1. Create a dataset record in opsTracker
2. Create a new materialized_view to host  the data for the dataset record/dataset.
3. Create a new SQL placeholder in Explorer to map to the dataset.
4. Create a new dataset (table) to map to the materialized view.
5. Create a new Python file in the repository with the same name as the materialized view. (See step 2 above)
6. The difference between 'managed' and 'un-managed' BQ tables.
7. Checking the logs will help you see what queries have run and their statuses and help with BQ and dashboard support.
8. Replicating your new dataset in BQ.

1 ) Creating a dashboard dataset record

Here are the steps we need to follow:

1. Create a dataset record in opsTracker
2. Set the process group for the newly created dataset record.
   You will need to check what other datasets are running in that process group as some of the queries take a long time to run and put a lot of load on the server.
3. Update the Engineering Control Doc for dashboards
   This is so we have a record of what datasets are associated with which dashboards.

2 ) Creating a materialized view

Make sure that the materialized view name is exactly the same as the one used/use in the repository (step 5 below).  eg.: bigquery_billingdata

3 ) Create a new SQL placeholder

We do this as not everyone who needs this query has access to our repository (Wayne, Lara). This is a place where they can view the query used in the dashboard you are creating.

We can also run the specific query 'by name', in the terminal, if we need to force that specific query to run.

4) Create a new dataset

1. Create new dataset.
2. Make sure that the dataset name you enter is recognisable. Something like BQ data - Click to call follow-up data. The name is related to the dashboard, dataset and table name of the data we are going to populate it with.
3. Select the query you saved in setp 3 above from the query dropdown.
4. The Bq project name is ALWAYS logimeter-datastudio
5. For Bq dataset name: select the dataset record name, you entered in step 1 above, from the dropdown list.
6. Enter a Bq table name into the Bq table name field - something like tbl_data1 or something more descriptive like `follow_up_data`
7. Make sure to check the disabled checkbox - we don't want the query running until we are ready to use it!
8. Select the materialized view you created in step 2 above from the dropdown list.
9. Save

5 ) Create a new Python file to create the view

1. Make sure that the filename matches the materialized view name you created and selected in step 1
2. Add your SQL to the file
3. Commit and push your code
4. Once it has been reviewed and approved - only then can you create the view on app1 and force the big query to run.

6 ) Managed and Unmanaged datasets

What is a managed dataset?
A managed dataset is a dataset where the fields in the dataset are typed and the number of fields is explicitly set.

Unmanaged datasets  can have fields added, removed, changed, and renamed WITHOUT any consequences to the BQ tables - Managed datasets CANNOT!

Why use a managed dataset?

A managed dataset has strongly typed fields, that way when you upload your data to BQ, it knows what type they are and doesn't try and guess the type. Sometimes BQ gets it wrong and it can cause havoc in the dashboard.

Creating a managed dataset

It might be a good idea to have all the field names ready as well as the types you want them to be before you start adding the fields. That way you can cross them off as you go.

Note: you will need to add all the fields for the relevant table, otherwise the BQ upload WILL FAIL!

1. Open the fields section in opsTracker
2. Click on the 'Add Field button'.
3. Enter the relevant field name from your list into the 'Field name:'
4. Select the field type you want associated with the field name from the dropdown.
5. Select the Dataset you want the field associated with.
6. Save
7. Repeat for all the fields.
8. Remember to add the yes under the managed section in the Engineering sheet!

8 ) Replicating your dataset in BQ

1. You will need to be logged in as logi_data_studio@gmail.com user!
   If you don't have the login, ask myself or Wayne.
2. Create your dataset under logimeter-datastudio. Remember to use the same dataset name you used earlier eg. active_tns, connect_v2
3. Create the table under the dataset name. Remember to use the same table name you used earlier eg. tbl_data1, tbl_data_follow_up
4. For more info on setting up the dataset and tables in BQ and what to look out for, click here

‍