A data warehouse such as Google BigQuery, or Snowflake, is used to consolidate data from multiple sources in a single location. Data can then be analysed using a BI tool such as Tableau. Tableau and many other BI tools discover patterns and help visualize data to make it more understandable and boost the analysis process. These help in gaining a better picture of the metrics, trends, the user needs, performance, and hence play a key role in various data-driven decisions.
This article aims at answering all your queries about connecting Google BigQuery to Tableau. Follow our easy step-by-step guide to master the skill of connecting Google BigQuery to Tableau using various methods.
Table of Contents
Introduction to Google BigQuery
It is Google Cloud Platform’s enterprise data warehouse for analytics. Google BigQuery performs exceptionally even while analyzing huge amounts of data & quickly meets your Big Data processing requirements with offerings such as exabyte-scale storage and petabyte-scale SQL queries. It is a serverless Software as a Service (SaaS) application that supports querying using ANSI SQL & houses machine learning capabilities.
Some key features of Google BigQuery:
- Scalability: Google BigQuery offers true scalability and consistent performance using its massively parallel computing and secure storage engine.
- Data Ingestions Formats: Google BigQuery allows users to load data in various formats such as AVRO, CSV, JSON etc.
- Built-in AI & ML: It supports predictive analysis using its auto ML tables feature, a codeless interface that helps develop models having best in class accuracy. Google BigQuery ML is another feature that supports algorithms such as K means, Logistic Regression etc.
- Parallel Processing: It uses a cloud-based parallel query processing engine that reads data from thousands of disks at the same time.
For further information on Google BigQuery, you can check the official site here.
Introduction to Tableau
Tableau is a powerful business intelligence tool used to turn raw data into an understandable format. It is a tool popularly used to visualize data and can be understood even by a non-technical user.
It creates visualizations with the help of dashboards and worksheets, helping users perform real-time analysis in a very fast and secure manner. It doesn’t require a user to have any programming skill or technical background to operate it.
For further information on Tableau, you can check the official site here.
Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Databases, SaaS applications, Cloud Storage, SDKs, and Streaming Services and simplifies the ETL process. It supports 100+ data sources (including 30+ free data sources) like Asana and is a 3-step process by just selecting the data source, providing valid credentials, and choosing the destination. Hevo not only loads the data onto the desired Data Warehouse/destination but also enriches the data and transforms it into an analysis-ready form without having to write a single line of code.
GET STARTED WITH HEVO FOR FREE[/hevoButton]
Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different BI tools as well.
Check out why Hevo is the Best:
- Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
- Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
- Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
- Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
- Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
- Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
- Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
SIGN UP HERE FOR A 14-DAY FREE TRIAL
Prerequisites
- Working knowledge of Google BigQuery.
- Working knowledge of Tableau.
- Tableau installed at the host workstation.
Methods to Connect BigQuery to Tableau
There are multiple ways in which you can connect Google BigQuery to Tableau:
Method 1: Using Tableau’s Google BigQuery Connector
Tableau’s in-built Google BigQuery connector conveniently establishes a connection with Google BigQuery. You can easily set up a data source and use Tableau to visualize your data to perform a fruitful analysis for your business.
This can be implemented using the following steps:
Step 1: Configuring Tableau’s Google BigQuery Connector
Launch Tableau on your workstation and select Google BigQuery from the connect column on the left. This will open a dialogue box where you need to provide the connection details for Google BigQuery.
To connect with Tableau, you will need to provide information about the Google account that is associated with your data. Provide your credentials and sign in to your Google account.
If you have multiple accounts on Google, you must select and log in to the account that has the access to the Google BigQuery data you want to load in Tableau.
Once you have signed in, you will be asked to provide Tableau, the access to your data. Click on accept to let Tableau easily access and load data stored in your Google BigQuery cluster.
Step 2: Configuring the Data Source
The data source page loads up after configuring the Tableau connector and successfully signing in. This is how the page looks like:
Select the data source name option and give a unique name to the database you are using. It’s considered a good practice to have a unique name as it makes it much easier for users to identify the database from which data is being fetched.
Select a billing project from “the billing project” drop-down list. In case a user doesn’t select a project from the list, an empty project will appear by default in the field. You can also select “publicdata”, from the drop-down list to connect with a sample data set in Google BigQuery.
To select the desired schema, you can use the schema drop-down list from the column on the left. You can also perform a text-based search to find the desired option. Now similarly find and select the desired table and drag it onto the canvas.
This is how you can connect Google BigQuery to Tableau. Now click on the sheets tab to begin the analysis.
Custom SQL features can be used to focus on specific SQL statements, rather than querying the entire database. Click on the Custom SQL option from the panel on the left. A new dialogue box will now open up, where you can provide the query you want to execute.
Google now supports standard SQL, in addition to BigQuery Legacy SQL (BQL) and hence once the data is loaded in Tableau, it automatically switches to standard SQL. When connecting Tableau with Google BigQuery, you must connect in the live mode to avoid any performance-related issues.
How to check if your workbook is using Legacy SQL or Standard SQL?
Google BigQuery APIs were updated by Google in the year 2016 to support Standard SQL in addition to BigQuery Legacy SQL (BQL), similarly from the Tableau v10.1 and above, Tableaus’ Google BigQuery connector also supports Standard SQL. Standard SQL provides a lot of benefits to the users and lets them write detailed queries, validate their metadata quickly and even select a billing project.
When you create a new workbook in Tableau, it supports Standard SQL by default. You can easily switch to Legacy SQL by selecting the “use Legacy SQL” option from the data pane. Legacy SQL option can even be used if you want to upgrade or modify an existing Legacy SQL based workbook to Standard SQL.
Google BigQuery allows creating views in both Standard SQL and Legacy SQL. It is important to select the type of SQL carefully as you can only join the views created with the same variant of SQL. It doesn’t allow combing a SQL view with a view created in Legacy SQL.
For further information about switching from Legacy SQL to Standard SQL, you can check the official Google documentation here.
Limitations of this method:
- Nested data isn’t supported accurately in Tableau desktop when you make use of Legacy SQL or Standard SQL. Various features such as “update now” don’t work in such situations.
- Using multiple Google BigQuery accounts with a single workbook isn’t supported in Tableau. If you try to connect multiple accounts, the data flow fails due to an authentication error.
- Data refreshes take notably longer to occur when working with large data sets. This slows down the data extraction process and hampers the overall performance.
- It is important to keep the connection type as a live connection instead of an extract connection, especially when you are working with large data sets.
Method 2: Using ODBC to connect Google BigQuery to Tableau.
ODBC drivers can also be used to establish a connection between Google BigQuery to Tableau. The ODBC driver can add a data source such as Google BigQuery and then be used to connect the Google BigQuery cluster to Tableau.
This can be implemented using the following steps:
Step 1: Installing Google BigQuery ODBC Driver and DSN Setup
Google BigQuery uses the ODBC driver provided by Magnitude Simba to establish an ODBC connection between Google BigQuery and other data sources. Download the latest version of Simba ODBC driver for Google BigQuery on your system using the following links:
You can also check the official Google Cloud site, if you want to install drivers for Mac, Ubuntu, etc. and for any updates related to the ODBC drivers.
Once the ODBC driver is installed on your system, launch it to start the DSN set up process. Choose the correct 64 or 32-bit version and select the System DSN tab.
Click on the add button and select the Simba ODBC Driver for Google BigQuery.
The Google BigQuery DSN setup box will now appear, where you need to fill in details regarding the connection settings and authentication.
Fill the necessary fields as follows:
- Data Source: Enter the name of your desired data source.
- Description: Enter a small description of the connection.
- Confirmation Code: Enter the code that appears on your screen when you grant access to your Google BigQuery cluster.
- Refresh Token: OAuth mechanism automatically generates a refresh token after you provide the confirmation code.
- Email: Enter the Google email id associated with your Google BigQuery cluster.
- KeyFile Path: Provide the path for the JSON or .p12 file.
- Request Google Drive scope access: This grants Google Drive, access to the driver and allows it to bring in federated data from Google BigQuery and Google Drive in a consolidated manner.
- Use System Trust Score: You don’t have to specify the trusted certificates (.pem file path) if you select this option.
- Project: Enter the name of Google BigQuery billing project associated with your account.
Click on the advanced option to configure advanced properties such as performing a pushdown optimization. To do this, select Standard SQL from the language dialect window.
To test the connection, click on the test button. If the connection is successful, a new dialogue box will open up as follows:
Step 2: Connecting to Tableau
Once you have made the necessary configuration changes, launch Tableau on your system. Now, from the connect columns on the left select the other databases (ODBC) option.
In the ODBC dialogue box, select your DSN from a drop-down list and click on sign in.
To select the desired schema, you can use the schema drop-down list from the column on the left. You can also perform a text-based search to find the desired option. Now similarly find and select the desired table and drag it onto the canvas.
You will now be able to view the data from your desired database. You can now perform an insightful analysis on your DynamoDB data in Tableau.
This is how you can use ODBC to connect Google BigQuery to Tableau.
Limitations of this method
- This method sometimes requires using third-party tools, which essentially is an extra tool that needs to be maintained.
- The Google BigQuery ODBC driver doesn’t support Google BigQuery’s large scale export functionality and is not able to properly leverage the ingestion mechanism.
- Current_time & Current_timestamp function doesn’t support precise time values, irrespective of whether you are using Legacy SQL or Standard SQL.
- While working with REST API, timestamp data type retains the trailing zeroes.
Conclusion
This article introduces you to the various methods that can be used to connect Google BigQuery to Tableau. It also provides in-depth knowledge about the concepts behind every step to help you understand and implement them efficiently. These methods, however, can be challenging especially for a beginner & this is where Hevo saves the day.
visit our website to explore hevo[/hevoButton]
Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully-automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 100+ sources & BI tools, allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.
SIGN UP for a 14-day free trial and see the difference!
Tell us about your preferred way to connect Google BigQuery to Tableau! Share your thoughts with us in the comments section below.