Connect DBeaver to BigQuery through ODBC
Contributed by the Google Cloud community. Not official Google documentation.
BigQuery is a managed, serverless data warehouse for storing and querying massive datasets. To access these datasets, you need some kind of tool that understands the BigQuery API, or you can use an ODBC driver that can work with a wide range of ODBC-compliant tools, as a data access layer.
Open Database Connectivity (ODBC) is an API that allows external applications to access data in various database management systems. The ODBC driver acts as an interface between an external database and an ODBC data source.
This tutorial shows you how to use the ODBC Driver for BigQuery to connect and access data in a BigQuery data warehouse using DBeaver Community, a free database administration tool.
- Install the ODBC Driver for BigQuery.
- Configure a DSN for the driver.
- Access BigQuery data from DBeaver Community.
Before you begin
This tutorial assumes that you're using the Microsoft Windows operating system.
- Create an account with the BigQuery free tier. See this video from Google for detailed instructions.
- Create a project in the Cloud Console.
- Install DBeaver Community for Windows.
BigQuery is a billable web service. The first 1 TB of query data processed per month and the first 10 GB of storage per month are free.
See the BigQuery pricing page for details.
Download the driver
- Download the latest version of Devart ODBC Driver for BigQuery for Windows.
Run the ODBC driver installer.
The installation wizard offers to install the 32-bit and 64-bit versions of the driver. Confirm both versions if you are planning to use the driver with older external applications. The driver offers a 30-day trial period, with no credit card required.
Configure a DSN for the driver
- Run the ODBC data source administrator (64-bit) application.
- Select the User DSN or System DSN tab, depending on whether you want to create a DSN for the current user or for all user accounts that exist in your Windows system
- Click Add and select Devart ODBC Driver for Google BigQuery.
- Specify the name for your data source, your Project ID, and Dataset ID.
- Click Request Refresh Token. Follow the prompts to authorize the driver to view and manage your data in BigQuery.
- Check the Save Token box.
- Open the Advanced settings tab and select Ansi strings in String Types.
- Click OK to save the DSN.
Connect and access BigQuery data
After the DSN is configured, run DBeaver Community.
Create a new database connection
- Click New Database Connection.
- Select ODBC in the list of data sources and click Next.
- Specify your DSN in the Database field and click Finish.
You can now expand the created ODBC data source in the Database Navigator pane to see existing objects in your BigQuery project.
Enter a query
- Right-click the data source and select SQL Editor.
- In the SQL Editor that opens in a new tab, enter your SQL statement.
CTRL + Enteron your keyboard to execute the statement.
The following image shows the results of executing a select statement against a sample head_office table that contains employee information:
Similarly, you can run insert, update, or delete statements against your dataset. The choice of data access tools is not limited to ODBC-compliant applications. You can run queries directly from code in programming languages, like Python and PHP.