Use the BigQuery Console to explore datasets

Owned by Leopold Strauch
Last updated: Feb 24, 2022 by Siamak Haschemi (Deactivated)

Overview

In this article, we'll explain how you can explore your data using the Google BigQuery console. Therefore we explain step by step how to proceed. Afterwards you can read this article to continue.

Before we get started..

Before we get started, a few conditions must be met to obtain access to Ingenious DWH

Follow the steps listed in https://ingenioustechnologies.atlassian.net/wiki/spaces/KB/pages/2733113347/What+is+required+to+get+access+to+Ingenious+DWH?atlOrigin=eyJpIjoiYTlmZWRkZTlkNTBjNGFmZjhhMjUyZmEwODc3Y2IxYzQiLCJwIjoiYyJ9

Ready? Let's BigQuery…

Step 1

Open the BigQuery Console with a supported web browser.

https://console.cloud.google.com/bigquery

A web interface opens which should look something like this.

 

Step 2

Make sure that the right Google project is selected. This is especially important if you have access to multiple Google projects.

Step 3

If you are already activated by Ingenious Support for the use of BigQuery data and do not see the project "i19s-insights" on the left side below the menu item Resources, click on "ADD DATA". Following Menu appears

Click on Pin a project, then on Enter project name

Enter i19s-insights and then click on Pin.

NOTE: don’t use menu option Search for project. This way you won’t find the i19s-insights project.

 

The project should now be visible below the Resources menu item:

 

Step 4

Below the menu item "i19s-insights" you will find the dataset "insights". Here you can find all tables that are released for you. If you click on a table, you can see the schema and further details of the table on the right side.

 

Step 5

A short query to check the access could look like this. Please note the estimated query size on the right side. This provides a good estimation of the cost of the query.

This message reports the maximal amount of processed data. It is usually less because we use internal BigQuery clustering by the networkId and advertiserId. This is why we added the networkId in the WHERE clause of the query. Contrary to expectations, LIMIT doesn’t reduce the processed data amount.

select * from `i19s-insights.insights.platform_performance` where dateAt = '2021-05-01' and networkId = '<YOUR networkId>' --reduces processed data limit 100; -- doesn't affect amount of processed data

 

If you don’t know your networkId, you can get it fast way with the following query:

select networkId from `i19s-insights.insights.platform_network`;

Next Steps