Create BigQuery View

Although everyone has access to create a SQL View in Redshift, not everyone does in BigQuery.

This Alli Marketplace app template was made so you still have the power to publish views to BigQuery without the need for support from a technical team member.

If you have any issues, submit a service desk ticket

Get Started: Create SQL

The App name in Marketplace is called Create BigQuery View.

The first thing you want to do is ensure that the BigQuery SQL that you are writing does execute correctly in the Bigquery UI (adjust the project in the top left accordingly). Once you ensure that works in order to save it as a view, there are view changes you have to make to ensure it saves as a view.

The Google Project has to be included on all your table names, therefore you need to prepend pmg-datawarehouse. to the front of your table name.
After you do that, surround your entire table name in tick marks (` - the same key as the tilda ~ ).
Capitalization matters when pulling from the table name! Make sure your client and table name are exact!

To walk through an example your query that originally looks like the following

CODE

SELECT *
FROM client.tablename

would now look like

CODE

SELECT *
FROM `pmg-datawarehouse.client.tablename`

Marketplace App

Copy/Paste the sql you created above into the sql input box
Create the name of your view in the view input box. View names should generally:
- be all lowercase
- include no spaces (use underscores or dashes instead)
- begin with letters (not numbers)
If you are created a view in the pmg-datawarehouse project, leave projectname blank
- If you are creating in a different project, fill that out
Do not adjust the creds.json file attached, unless necessary (ie creating a view outside of the standard projects)

Do not set a schedule for your app, that is generally unnecessary unless you plan on consistently updating the query.

How to Use Your View

The view you created with be under the client your marketplace app is under. Example:
- If my Marketplace app was created in the demo client & the view name I put in my marketplace app was dod_spend_decrease, then my complete view name would be demo.dod_spend_decrease
You can/should reference your view by it’s complete name (client.view_name) in any future integrations, like a Custom Alli Alert, or otherwise.