How to Setup BigQuery ODBC Connection in 3 Easy Steps

Offering an accelerated and unbeatable query performance, Google BigQuery has become a reliable Cloud Data Warehouse & Analytics solution worldwide.

1. With its on-demand scaling, economical pricing, and the ability to efficiently handle fluctuating workloads, BigQuery provides a reliable and secure cloud platform for businesses of all sizes. 
2. Often, you may require to transfer your data from BigQuery to your BI Tools for further analytics & reporting purposes. Magnitude Simba provides an easy-to-setup BigQuery ODBC Connector to seamlessly connect to your BI Tool instantly.
3. Introduced by Microsoft in collaboration with Simba, ODBC(Open Database Connectivity) is a standard API used to access a database.

What is Google BigQuery?

BigQuery is a Cloud-Based Data Warehouse service offered by Google. It is built to handle petabytes of data and can automatically scale as your business flourishes. Some features is as follows

1. User Friendly
2. On-Demand Storage Scaling
3. Real-Time Analytics
4. BigQuery ML
5. Optimization Tools 
6. Secure
7. Google Environment

What is ODBC?

• ODBC(Open Database Connectivity) is an open standard Application Programming Interface (API) for accessing both relational and non-relational databases.
• From a technical point of view, ODBC has based on the Call-Level Interface (CLI) specifications from Open Group and ISO/IEC for database APIs.
• ODBC allows you to easily connect your BI tools to a data source such as a file, a particular database on a DBMS, or even a live data feed to access databases using Structured Query Language (SQL). 

What is Magnitude Simba?

• Magnitude Simba is a complete package of data connectivity solutions that enable efficient and effective data access to applications, data platforms, and databases.
• These solutions include Simba Gateway connectivity as a service, standalone data connector, Simba SDK, as well as managed services with custom-built connectors via rigorous testing and authentication.
• For instance, the Simba BigQuery ODBC connector allows you to get data from BigQuery to your BI Tool.

Configuration of BigQuery ODBC Connector

• Step 1: Install and Download Simba Google BigQuery ODBC Connector
• Step 2: License Allocation
• Step 3: Configuring Simba Google BigQuery ODBC Connector

Step 1: Install and Download Simba Google BigQuery ODBC Connector

• Step 1: Visit the BigQuery ODBC JDBC Drivers page and download the connector for your Operating System. For this article, the windows version is selected. After downloading the MSI File, open it.
• Step 2: The BigQuery ODBC Connector Setup window will pop up on your screen. Click on the Next button. 

• Step 3: For accepting the licensing agreement, click on the Next button.

• Step 4: Provide the location on your system where you want to install the connector and click on the Next button. 

• Step 5: Finally, click on the Install button. After the BigQuery ODBC connector is completely installed, click on the Finish button to close the setup window.   

Step 2: License Allocation

After you have submitted the form, the driver’s license is sent to your email. Ensure that the file is stored in the /lib/ folder underneath where the connector has been installed.

Step 3: Configuring Simba Google BigQuery ODBC Connector

• Step 1: In your Windows system, click on the SEARCH and navigate to ODBC Administrator.  

• Step 2: In the ODBC Administrator window, click on the System DSN tab. Search and click on the Simba GBQ ODBC DSN. Then, click on the CONFIGURE button.

• Step 3: In the DSN setup, configure the settings according to your business needs.

• Step 4: After the configuration is done, click on the TEST button to test the connection.

• Step 5: Click on the OK test window to save your settings.   

Troubleshooting Common Issues

How can I make Queries against BigQuery through an ODBC Connection?

To make queries against BigQuery through an ODBC connection, you can follow these general steps:

• Set up an ODBC Driver: First, you need to install and configure an ODBC driver that supports BigQuery. Google provides an official ODBC driver for BigQuery, which you can download and install on your machine.
• Set Up ODBC Data Source: With the ODBC driver implemented, set the DSN for the connection. The DSN defines the project’s connection parameters used in the statement, such as the BigQuery Project ID, OAuth credentials, and other settings.
• Use ODBC-Compliant Tools: Upon successfully configuring an ODBC driver and data source, you will eventually be able to use any ODBC-compliant tool or application to connect with BigQuery. Some of the most common tools include Microsoft Excel, Tableau, or even programming languages such as Python with libraries like pyodbc.

Unable to Authorise ODBC BigQuery connection

To troubleshoot the issue, perform the following steps:

• Verify service account or user credentials
  • Service Account: If you’re using service account authentication, ensure you have correctly set up and provided the service account credentials (typically JSON format) during the ODBC DSN configuration process.
  • User Authentication: If you’re using user authentication, ensure that the username and password provided in the ODBC configuration are correct and have the required permissions in BigQuery.
• Check ODBC Driver Configuration
  • DSN Configuration: Double-check the configuration of your ODBC Data Source Name (DSN). 
  • Driver Compatibility: Ensure that the Samba ODBC Driver you use is compatible with BigQuery.
• Check Permissions in Google Cloud Console
  • IAM Roles: Ensure that your service account or user account has appropriate IAM roles assigned in the Google Cloud Console.

Conclusion

• In this article, you have learned how to effectively set up the BigQuery ODBC Connection.
• Magnitude Simba is a simple yet powerful solution to seamlessly connect your BigQuery Data Warehouse to your BI Tools.
• Offering more control and granular configuration options, the Simba BigQuery ODBC Connector allows you to connect and maintain your connection using an easy-to-use interface.
• As you collect and manage your data across several applications and databases in your business, it is important to consolidate it for complete performance analysis of your business.
• However, it is a time-consuming and resource-intensive task to continuously monitor the Data Connectors.
• To achieve this efficiently, you need to assign a portion of your engineering bandwidth to Integrate data from all sources, Clean & Transform it, and finally, Load it to a Cloud Data Warehouse like Google BigQuery, BI Tool, or a destination of your choice for further Business Analytics.
• All of these challenges can be comfortably solved by a Cloud-based ETL tool such as Hevo Data.   

FAQs

What are the charges involved when querying BigQuery via the Driver?

The driver can be downloaded for free. Queries run by the driver are charged based on the driver’s configuration:

• By default, the price of the query is applied to all queries from the driver. The query price is the only price that applies provided that the driver is not configured to assist a large result set. 
• For cases when the driver is configured to write a large result set to the target table, the storage charge will be added to the query charge. Data is stored  24 hours a day, and table results are subject to a 24-hour storage fee. 
• Storage API charges apply when the Storage API is called using the driver. These prices apply to the data read from the query results, not the data scanned by the query. Note that the Storage API pricing applies only to large result sets.

Do the BigQuery ODBC Drivers support the SQL Query Prefix?

Unlike the Drivers, BigQuery supports switching between legacy SQL dialects and standard SQL dialects using query prefixes. The driver always maintains a specific status regarding the SQL mode used and automatically sets options when creating the connection. The driver does not support switching SQL dialects using query prefixes because the SQL mode is set when the connection is created.

How do the Drivers deal with BigQuery’s repeated and nested data schemas?

The ODBC data model does not provide a convenient way to represent data. Hence, the nested and iterative data (STRUCTS and ARRAYS in standard SQL) is represented as BigQuery API JSON output. You can execute queries that manipulate these types, but if the query’s output schema is of complex types, the driver encodes and renders them in JSON format.

Can these drivers be used to export or ingest data between BigQuery and your existing environment?

The BigQuery ODBC drivers use BigQuery’s query interface and do not provide the ability to take advantage of BigQuery’s rich ingest and export features. You can use the DML to issue a small set of INSERT requests, but this has its limitations.

Do the Drivers support Parameterized Queries? 

The BigQuery ODBC Drivers provides complete support for Positional Parametrisation. It is to be noted that preparing a query before executing it provides validation information. Also, this doesn’t have any impact on the performance of the executed query.

Tell us about your experience of setting up the Simba BigQuery OBDC Connection! Share your thoughts with us in the comments section below.