Guide: Using Google BigQuery with Databox

HOW TO

    BigQuery connections are available in Plus and Business Accounts. Agency Basic Accounts have the ability to use BigQuery in the Agency Account only.

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 

add_user_popup@2x

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

add_user_popup@2x

4
  Next, we must create a credentials file so that our database is ready to connect to Databox. From Google Console, we'll navigate to API & Services and will select Credentials

add_user_popup@2x

5
 Click on  Service account key and select  New service account

add_user_popup@2x

6

 Enter the Service account name and choose an appropriate Role for the user. All BigQuery Users can run queries. 

Learn more about security and best practices for BigQuery here

7

 Click Create to create the new service account key. From here, we'll be able to download the credentials file as a JSON file. We'll save this in a folder to use later when we connect BigQuery to Databox. 

How to connect BigQuery to Databox

How to connect BigQuery to Databox

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

add_user_popup@2x

To connect BigQuery to Databox, go to Data Manager > + 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. 

add_user_popup@2x

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

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 Query Builder for BigQuery

The Query 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 Query Builder for BigQuery here

Additional Information

  • Query results are limited to 1000 rows. If you expect your query to return over 1000 rows or if you're not sure how many rows will be returned, be sure to LIMIT the query
  • The query must contain a column named "Date." If you need to, use AS in your SELECT statement to satisfy this requirement
  • "Null" values will not be recognized. Use a COALESCE function when needed. Learn more here.
Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.

Still need help? Contact Us Contact Us