Skip to content
  • There are no suggestions because the search field is empty.

Guide: Using Google BigQuery with Databox

HOW TO

BigQuery connections are available on the Professional and higher plans. Request a free trial of BigQuery by following these steps.

How to prepare your BigQuery database

1. First, we'll visit our GCP/ BigQuery console here. For our example, we'll use a sample database that Google provides called "hacker_news." To do this, we'll click on BigQuery-public-data and will select the Hacker_news database

 

2. We'll run a query to verify our connection to the database
SELECT time_ts AS date, score, author FROM [bigquery-public-data:hacker_news.stories] ORDER BY score DESC LIMIT 5;
3. Click Run Query. If our connection to the database was successful, we'll see results below our Input window

  4. From the Google Console, navigate to IAM & Admin and select Service Accounts from the left-hand navigation menu.

Click on the + Create Service Account button. 

Pasted image 202405210815075.  Enter the Service account name, then click on Create and Continue and move to the next step.

Pasted image 20240521080853

6. Select an appropriate Role for the user. For most cases the BigQuery Data Viewer role should be sufficient.

Learn more about security and best practices for BigQuery here. 

Pasted image 20240521081000

7. Click Done and select a newly created Service Account from the list.

Pasted image 20240521081133

8. Go to Keys tab and click on Add key, then select Create new key from the drop-down menu.

Pasted image 20240521081218

9. The Create private key window will appear. From here, you will be able to download the credentials file as a JSON file. You can save this in a folder to use later when you connect BigQuery to Databox. 

Pasted image 20240520225455

How to connect BigQuery to Databox

Once our Database is properly prepared, we'll connect it to Databox.

To connect BigQuery to Databox, go to Data Sources > + New connection. Type BigQuery in the search bar and click on the green Connect button. 

This will open the  Activation window, which is where you will be prompted to enter your connection data and authorize the Databox app. Paste the JSON Credentials file contents into the appropriate field. 

We’ll be using the Legacy SQL syntax, so we'll leave the Legacy SQL checkmark selected. 

Pro Tip:   To use Standard SQL you need to unselect the Use Legacy SQL checkbox when connecting the Database.

Click the green Activate button to complete the connection process. If BigQuery was successfully connected, the Activation window will close shortly and you'll see a Connected notice. If you have difficulty connecting, review the following: 

  •  Make sure all data is entered correctly and the appropriate roles are set
  • One column must contain Date values. You can cast any column to name “date” using “AS” SQL syntax
  • One column must contain numeric (Metric) values

How to use the Metric Builder for BigQuery

The Metric Builder for BigQuery allows you to create Custom Metrics using data from your Database to use in Databoards, Alerts, Goals, etc. Learn more about the Metric Builder for BigQuery here. 

Additional Information

  • To use Standard SQL you will need to unselect the "Use Legacy SQL"  when connecting the Database.
  • Partitioned tables are supported. 
  • Query results are limited to 10 MB. If more than 10 MB of data is returned in a response to a query, an error message will be displayed in Databox. Please edit your query to successfully sync the data.
  • The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
  • One column should always contain the Metric Value
  • "Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.