This tutorial describes how to connect to Google BigQuery.

Requirements

To connect to Google BigQuery, you need to have the corresponding account, dotConnect for Google BigQuery installed, and the IDE running. You also have to know the required connection parameters described below.

Note that you must activate the product on the computer where your application runs; otherwise, provide the activation key at run time with the License Key connection string parameter or the BigQueryConnection.LoadLicenseKey method. For more information, see Activation.

Required Connection Parameters

To establish a connection to the server, you have to provide the required connection parameters to dotConnect for Google BigQuery. This information is used by BigQueryConnection component to connect to Google BigQuery. The parameters are represented as a connection string. You can compose the connection string manually or have dotConnect for Google BigQuery construct it for you.

The following connection string parameters are required:

• Authentication Type – Determines the authentication method used: UserAccount, UserAccountInteractive, or ServiceAccount. UserAccount uses a refresh token to authorize access to Google BigQuery. UserAccountInteractive opens a browser window to sign in to Google when the connection is opened without a refresh token. ServiceAccount uses a service account key (JSON) to authorize access to Google BigQuery.
• Project Id – The unique identifier of the Google BigQuery project.
• Refresh Token – The OAuth 2.0 refresh token that authorizes access to your Google BigQuery data. Required for UserAccount authentication.
• Client Id and Client Secret – The OAuth 2.0 client of your own Google Cloud application. If you omit both, the built-in dotConnect for Google BigQuery client is used. A refresh token works only with the client it was issued for.
• ServiceAccount Json – The service account key: either the content of the JSON key file or the path to this file. Required for ServiceAccount authentication.

You can specify DataSet Id, the default dataset of the connection. If it is not set, objects of all datasets of the project are available.

For UserAccount authentication, you can get the refresh token by creating a connection at design-time or in the Server Explorer following the procedure described below.

For ServiceAccount authentication, you need a JSON key of a service account. For more information on how to get it, see Obtaining the JSON Service Account Key.

Creating a Connection to Google BigQuery

Design time creation

The following assumes that you have the IDE running, and you are currently focused on a form designer.

1. Open Toolbox and find BigQueryConnection component in the Google BigQuery Data category.
2. Double-click the component. Notice that a new object appears on the designer underneath the form. If this is the first time you create the BigQueryConnection in this application it is named bigQueryConnection1.
3. Click the bigQueryConnection1 object and press F4 to focus on the object's properties.
4. Enter the required connection string parameters, described above, to the corresponding boxes.
5. Notice that as you assign values to these properties the ConnectionString property is automatically updated to reflect your settings. Now it contains something like "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********".
6. If necessary, click the Advanced button and configure other connection string parameters. You can find the description of these connection string parameters in the BigQueryConnection.ConnectionString topic.

Run time creation

You can also configure a connection at run-time by setting its ConnectionString property (note that you have to add references to Devart.Data.BigQuery.dll, Devart.Data.SqlShim.dll, and Devart.Data.dll assemblies):

bigQueryConnection1.ConnectionString = @"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********";

bigQueryConnection1.ConnectionString = "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********"

Using connection string builder

If you decide to set up the connection by assigning values to several properties, consider using BigQueryConnectionStringBuilder class. It has all of the possible connection settings exposed as properties, thus allowing you to customize the connection to the full extent.

BigQueryConnectionStringBuilder connectionStringBuilder = new BigQueryConnectionStringBuilder();

connectionStringBuilder.AuthenticationType = AuthenticationType.UserAccount;
connectionStringBuilder.ProjectId = "your_project";
connectionStringBuilder.DataSetId = "your_dataset";
connectionStringBuilder.RefreshToken = "your_refresh_token";
// For ServiceAccount authentication, use the following instead:
// connectionStringBuilder.AuthenticationType = AuthenticationType.ServiceAccount;
// connectionStringBuilder.ServiceAccountJson = "your_json_key";

BigQueryConnection myConnection = new BigQueryConnection(connectionStringBuilder.ConnectionString);

Dim connectionStringBuilder As BigQueryConnectionStringBuilder = New BigQueryConnectionStringBuilder

connectionStringBuilder.AuthenticationType = AuthenticationType.UserAccount
connectionStringBuilder.ProjectId = "your_project"
connectionStringBuilder.DataSetId = "your_dataset"
connectionStringBuilder.RefreshToken = "your_refresh_token"
' For ServiceAccount authentication, use the following instead:
' connectionStringBuilder.AuthenticationType = AuthenticationType.ServiceAccount
' connectionStringBuilder.ServiceAccountJson = "your_json_key"

Dim myConnection As BigQueryConnection = New BigQueryConnection(connectionStringBuilder.ConnectionString)

Notice that in this example we used the BigQueryConnection constructor that accepts a connection string as an argument.

For information on the arguments allowed in the connection string, refer to the description of the BigQueryConnection.ConnectionString property.

Creating Connection in Server Explorer

To create a Server Explorer connection:

1. On the Server Explorer toolbar, click Connect to Database.
2. Under Data source, select Google BigQuery Data Source.
3. Under Data provider, select dotConnect for Google BigQuery, then click Continue.
4. In Authentication, select the authentication type: UserAccount or ServiceAccount.
5. Specify the Project Id and DataSet Id.
6. Specify the connection parameters:
   • For UserAccount authentication: Click Sign In, authenticate your Google BigQuery account in the browser, and the Refresh Token will be assigned automatically. Alternatively, enter the Refresh Token manually.
   • For ServiceAccount authentication: In Private Key JSON, click the ellipsis (...), then select the JSON key file.
7. Click OK.

After this, you can browse Google BigQuery objects in Server Explorer.

Opening a Connection

To open a connection, call the Open method.

myConnection1.Open();

myConnection1.Open()

The myConnection1 object must have a valid connection string assigned earlier. When you call Open, dotConnect for Google BigQuery tries to find the host and connect to Google BigQuery. If any problem occurs, it raises an exception with a brief explanation of what is wrong. If no problem is encountered, dotConnect for Google BigQuery tries to establish the connection during the ConnectionTimeout interval. Finally, when the connection is established, the Open method returns and the State property is changed to Open.

To connect to the server at design time:

1. Right-click the connection object in designer and select Connect.
2. In the dialog window, enter the required connection parameters.
3. Click the connection object and press F4 to focus on object's properties.
4. If necessary, click Advanced and configure other connection string parameters. You can find the description of these connection string parameters in the BigQueryConnection.ConnectionString topic.
5. Click Connect.

Alternatively, change the State property to Open in the Properties window to establish a connection using the current connection string.

Closing a Connection

To close a connection, call its Close method, or set its State property to Closed.

The following example summarizes the aforementioned information and shows how to create, set up, open, use, and then close the connection.

BigQueryConnection myConn = new BigQueryConnection();
myConn.ConnectionString = @"Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********";
myConn.Open();
MessageBox.Show(myConn.State.ToString());
myConn.Close();

Dim myConn As BigQueryConnection = New BigQueryConnection()
myConn.ConnectionString = "Authentication Type=ServiceAccount;ServiceAccount Json=C:\keys\service-account.json;Project Id=your_project;DataSet Id=your_dataset;License Key=**********"
myConn.Open()
MessageBox.Show(myConn.State.ToString())
myConn.Close()

The sample code connects to Google BigQuery, shows the connection state, and then closes the connection. This is actually a rare usage, because in real applications connections are used by other objects like BigQueryCommand, BigQueryDataReader and others. For more information on this, see the corresponding tutorials or reference information.

Modifying a Connection

You can modify a connection by changing properties of the BigQueryConnection object. Keep in mind that while some of the properties can be altered freely, most of them close the connection when a new value is assigned.