Guide: Using BigQuery with Databox
Access your SQL query results right on your mobile device, big screen or computer
BigQuery is Google's fully managed, petabyte-scale, low-cost enterprise data warehouse for analytics. It’s also serverless. There is no infrastructure to manage so you don't need a database administrator, which means you can focus on analyzing data and finding meaningful insights using familiar SQL. BigQuery is a powerful Big Data analytics platform used by all types of organizations, from startups to Fortune 500 companies.
What will we accomplish in this tutorial?
In this article, we will first configure and run a query on a sample BigQuery database. Next, we’ll configure a JSON credentials file and connect to it remotely. Finally, we will visualize the data using Databox. The end result will be a databoard displaying the data from BigQuery.
So let’s get to it...
1. Prepare the BigQuery database
Visit your GCP / BigQuery console at: https://cloud.google.com/bigquery
To get things started, we’ll be using one of the sample databases that Google provides. The database we chose to run this on is “hacker_news.”
We’ll expand the BigQuery-public-data and choose the Hacker_news database.
Now, we need to run a simple query to check to see if it’s working properly:
SELECT time_ts AS date, score, author FROM [bigquery-public-data:hacker_news.stories] ORDER BY score DESC LIMIT 5;
Press the Run Query button.esults should appear below our input window in a few seconds:
The ultimate goal is to visualize this data in Databox.
As mentioned earlier, Databox will connect to this database remotely. So, we need to setup the proper credentials file in order to connect to BigQuery from Databox.
You will want to create a New Service Account Key and give it the BigQuery User role, so that it can effectively run queries on the project’s database. To do this, visit the API & Services section on Google Console and click “Credentials.”
Click on the Service Account Key and select the option “New service account”:
Enter the appropriate fields and choose an appropriate role for this user BigQuery User allows a user to run queries). Then click “Create.” The credentials file will be downloaded as a JSON file Save this for later. We’ill use it when connecting Databox to BigQuery.
The security and best practices are beyond the scope of this document. To learn more about that, take a look at this documentation:
We have now run a test query and created a credentials file to use inside Databox. This will allow us to connect to BigQuery. In the next step, we’ll be moving over to Databox to establish the connection.
2. Connect the BigQuery data source to Databox
Our database is now ready! The next step is to connect it and test that it’s returning the data we need for our visualizations:
First, log in to the Databox app at https://app.databox.com. Navigate to the ‘Data Manager’ menu item, then select the “+New connection” button.
Filter this next screen by typing “BigQuery” in the Search box on the righthand side. The BigQuery connector will now filter to the top.
Hover over the connector tile and click “Connect.” This will prompt the connect window to appear:
Enter the arbitrary name for this connection and paste the JSON credential file contents. We’ll be using the Legacy SQL syntax, so leave the checkmark selected.
Click “Activate.”If all went well, you should see the message “Successfully connected” after a few seconds... If that doesn’t happen, check the troubleshooting notes at the bottom of this article. If all fails, contact support, so we can debug the issue. Otherwise, we’re ready to test a simple query.
3. Running a custom query
We will write a custom query to test this connection. Databox will run the query and display the data returned in a tabular format. There are a couple of things we need to make sure of:
The query returns a maximum of 1000 rows, LIMIT the query if you’re not sure if you will exceed this number. The query must contain a column named “data” (using AS when SELECT-ing data is perfectly acceptable)
In Databox, navigate to the Data Manager and select the Query Builder submenu item., click the “+New Query” button.
Choose the newly connected BigQuery datasource in the dropdown, and enter the following query:
SELECT date(time_ts) AS date, score, author FROM [bigquery-public-data:hacker_news.stories] ORDER BY score DESC LIMIT 10;
Click “Run query” and the results should appear at the bottom of the screen in the Data Preview section.
Great! You have now validated the connection. Databox has successfully pulled the data from our BigQuery database. Once we save the custom query, data will be fetched regularly (either once an hour or daily depending on your subscription package). In the last step we will visualize the data using the Designer.
3. Visualize Your Data with Databox Designer
Now that the database is connected, we will use the Designer to query, shape and display the data in a format that’s most appropriate and useful for our needs:
Let’s go to the Databoards menu item in the app.
You can either choose an existing databoard or create a new one. On the lefthand side, click on the “Visualization Type” icon. We will display our data in a tabular format. Drag & drop the Table block onto your databoard.
We have selected our BigQuery data source, chosen the Untitled Custom Query metric and added additional time intervals in the Data ranges section of the properties panel. Our table now looks like this:
Well done! Your database is now connected to Databox. Queries can be executed and then displayed on your mobile device, TV or computer.
Go ahead and explore further. Add more custom queries, create new datablocks, play around with different types of visualizations.... Make the perfect databoard that you always needed but didn't know how to create. Now you can!
Ready to try it for yourself? Signup for free today at https://databox.com/signup.
Remember: we’re always glad to help if you run into any obstacles!
- Make sure all data entered is correct and the roles are correctly set.
- One column must contain the Date value. You can cast any column to name “date” using the “AS” SQL syntax
- One column should always contain the numeric value