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.
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
SELECT * FROM client.tablename
would now look like
SELECT * FROM `pmg-datawarehouse.client.tablename`
Copy/Paste the sql you created above into the
Create the name of your view in the
viewinput 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
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
democlient & the view name I put in my marketplace app was
dod_spend_decrease, then my complete view name would be
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.