.NET Connection Strings for Google BigQuery

A connection string consists of key-value pairs separated by semicolons. It specifies the Google Cloud project, the authentication settings, and other options that the provider uses to connect. This guide explains how to build connection strings for .NET applications that connect to Google BigQuery with dotConnect for Google BigQuery.

Connection string parameter names are case-insensitive. Most parameters also accept names without spaces, for example, ProjectId for Project Id.

BigQuery connection strings

dotConnect for Google BigQuery connects to BigQuery over HTTPS, so a connection string does not contain a server address or a port. It identifies the Google Cloud project, optionally the default dataset, and the credentials. The following example opens a connection using service account authentication:

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "DataSet Id=your_dataset_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

The required authentication parameters depend on the selected authentication type. The values in the examples are placeholders. In real applications, store the service account key, the client secret, and the refresh token in secure configuration instead of hard-coding them, and do not commit them to source control.

Authentication types

The Authentication Type parameter specifies how the provider authenticates with Google Cloud. dotConnect for Google BigQuery supports three authentication types.

Service account (ServiceAccount)

Create a service account in the Google Cloud console, generate a JSON key for it, and grant the account the roles it needs, for example, BigQuery Data Viewer and BigQuery Job User to read data and run queries. Pass the key in the ServiceAccount Json parameter. The value can be either the content of the key file or the path to that file.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

User account (UserAccount)

Use this authentication type when you have an OAuth 2.0 refresh token obtained through the authorization code flow. Client Id and Client Secret are the OAuth client credentials obtained in the Google Cloud console under APIs & Services > Credentials.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=UserAccount;" +
      "Project Id=your_project_id;" +
      "Client Id=your_client_id;" +
      "Client Secret=your_client_secret;" +
      "Refresh Token=your_refresh_token;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

Interactive sign-in (UserAccountInteractive)

With UserAccountInteractive, the provider opens the default browser on the first connection so that the user can sign in to Google and grant access. The provider obtains the refresh token automatically and keeps it between sessions, so you do not need to specify it. If Client Id and Client Secret are not specified, the provider uses its own OAuth application. Specify your own Client Id and Client Secret to sign in through your OAuth application.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=UserAccountInteractive;" +
      "Project Id=your_project_id;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

In Visual Studio, the Add Connection dialog offers the User Account and Service Account options. Select User Account and click Sign in to Google BigQuery to sign in through the browser.

Main connection properties

The following table lists the main parameters used in Google BigQuery connection strings.

BigQueryConnectionStringBuilder

The BigQueryConnectionStringBuilder class builds a connection string from typed properties and helps prevent errors in parameter names and in values that contain special characters.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    BigQueryConnectionStringBuilder builder = new();
    builder.AuthenticationType = AuthenticationType.ServiceAccount;
    builder.ProjectId = "your_project_id";
    builder.DataSetId = "your_dataset_id";
    builder.ServiceAccountJson = "your_service_account_json";
    builder.LicenseKey = "your_license_key";

    using BigQueryConnection conn = new(builder.ConnectionString);
    conn.Open();
  }
}

BigQueryConnection constructor

You can pass a connection string to the BigQueryConnection constructor or assign it to the ConnectionString property of a connection created with the default constructor.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new();
    conn.ConnectionString =
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "License Key=your_license_key";
    conn.Open();
  }
}

Proxy connection strings

If your network reaches the internet through a proxy server, specify the proxy parameters in the connection string. The provider sends all requests to BigQuery and to Google sign-in through this proxy.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "Proxy Host=your_proxy_host;" +
      "Proxy Port=3128;" +
      "Proxy User=your_proxy_user;" +
      "Proxy Password=your_proxy_password;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

BigQuery connections always use HTTPS. SSL/TLS certificate settings, SSH tunneling, and HTTP tunneling are not available for this provider.

Connection pooling options

Connection pooling is enabled by default. A pool is created for each distinct set of authentication data, and a connection returned to the pool can be reused by the next Open() call with the same connection string.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "Pooling=true;" +
      "Min Pool Size=2;" +
      "Max Pool Size=50;" +
      "Connection Lifetime=300;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

Advanced connection string options

For example, the following connection string sets the legacy SQL dialect, the Cloud Storage bucket for data loading, and the command timeout:

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "DataSet Id=your_dataset_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "Use Legacy SQL=true;" +
      "Default Cloud Storage Bucket=your_bucket_name;" +
      "Default Command Timeout=120;" +
      "License Key=your_license_key");
    conn.Open();
  }
}

Entity Framework Core connection strings

Pass the same connection string to the UseBigQuery method of DbContextOptionsBuilder. The method is provided by the Devart.Data.BigQuery.EFCore package.

using Devart.Data.BigQuery;
using Microsoft.EntityFrameworkCore;

class AppDbContext : DbContext
{
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    optionsBuilder.UseBigQuery(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "DataSet Id=your_dataset_id;" +
      "ServiceAccount Json=your_service_account_json;" +
      "License Key=your_license_key");
  }
}

You can also pass an existing BigQueryConnection object to UseBigQuery instead of a string.

Bulk loading connection strings

BigQueryLoader uploads the rows to a Google Cloud Storage bucket as compressed CSV files and then starts a BigQuery load job. The authenticated account must have permissions to access the bucket, so set Default Cloud Storage Bucket in the connection string or assign the CloudStorageBucketName property of the loader.

using Devart.Data.BigQuery;

class Program
{
  static void Main()
  {
    using BigQueryConnection conn = new(
      "Authentication Type=ServiceAccount;" +
      "Project Id=your_project_id;" +
      "DataSet Id=your_dataset_id;" +
      "Default Cloud Storage Bucket=your_bucket_name;" +
      "ServiceAccount Json=your_service_account_json;" +
      "License Key=your_license_key");
    conn.Open();

    BigQueryLoader loader = new("orders", conn);
    loader.CreateColumns();
    loader.Open();
    loader.SetValue("id", 1L);
    loader.SetValue("name", "Laptop");
    loader.NextRow();
    loader.Close();
  }
}

Conclusion

Choose the authentication type that fits your application: ServiceAccount for server applications and scheduled jobs, UserAccount when the application already has an OAuth 2.0 refresh token, and UserAccountInteractive for desktop tools where users sign in through the browser. Add the default dataset, proxy, pooling, and Cloud Storage bucket parameters only when your scenario requires them.

You can download the fully functional free trial of dotConnect for Google BigQuery and test it with your actual workload.