How to query bigquery through ODBC connection?

It would be nice to be able to use bigquery through ODBC I have used MySQL through ODBC, which allows me to switch to bigquery as a substitute for MySQL for my big data table

Solution

Simba is an ODBC expert (they can have ODBC drivers for almost every data source you think of). It builds an ODBC connector for bigquery You can download it for free from the bigquery third-party tools page here (for Windows) (scroll down to the bottom of the page) If you prefer Linux, you can download ODBC drivers directly from here and Simba Please note that Simba will charge if you download from their site Their drivers may be updated and will include support

One function to be noted is that Simba driver can convert standard SQL-92 compatible SQL into bigquery SQL dialect This can be useful if you want to use it as a replacement for relational databases However, if you want to use the full function of bigquery (nested data, random play join / group by), you should turn off this switch to send the query directly

Once the bigquery ODBC driver is installed and the DSN is created (see the Simba ODBC documentation for a step-by-step guide to DSN creation), you will need to start ODBC queries This is a code example of c# performing this operation If you compile and run it, it will connect to bigquery, run a simple query, and print the results

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BigQueryE2E
{
  /** 
   * Helper class to build an ODBC Connection to connect to a Simba 
   * BigQuery ODBC Driver.
   */
  class ConnectionBuilder {
    private String Dsn;
    private String Catalog;
    private String ExecCatalog;
    private bool UseNativeQuery;

    public ConnectionBuilder SetDsn(String dsn) { 
      Dsn = dsn; 
      return this; 
    }
    public ConnectionBuilder SetCatalog(String catalog) {
      Catalog = catalog; 
      return this; 
    }
    public ConnectionBuilder SetBillingCatalog(String catalog) {
      ExecCatalog = catalog;
      return this;
    }
    public ConnectionBuilder SetUseNativeQuery(bool nativeQuery) {
      UseNativeQuery = nativeQuery;
      return this;
    }

    public OdbcConnection Build() {
      if (Catalog == null || Dsn == null) {
        throw new ArgumentException("Missing required Connection setting");
      }

      StringBuilder connectionString = new StringBuilder();

      connectionString.AppendFormat("DSN={0}; Catalog={1};",Dsn,Catalog);
      if (ExecCatalog != null) {
        connectionString.AppendFormat("ExecCatalog={0};",ExecCatalog);
      }
      if (UseNativeQuery) {
        connectionString.Append("UseNativeQuery=1");
      }

      OdbcConnection conn = new OdbcConnection();
      conn.ConnectionString = connectionString.ToString();
      return conn;
    }
  }

  class Program {
    private static String Query = 
        "SELECT corpus,SUM(word_count) " + 
        "FROM samples.shakespeare " +
        "GROUP BY corpus";

    private static void PrintResults(OdbcDataReader reader) {
      for (int ii = 0; ii < reader.FieldCount; ii += 1) {
        System.Console.Write("{0}{1}",reader.GetName(ii),ii + 1 < reader.FieldCount ? "\t" : "\n");
      }
      while (reader.Read()) {
        for (int ii = 0; ii < reader.FieldCount; ii += 1) {
          System.Console.Write("{0}{1}",reader.GetValue(ii),ii + 1 < reader.FieldCount ? "\t" : "\n");
        }
      }
    }
    static void Main(string[] args) {
      OdbcConnection connection = new ConnectionBuilder()
          .SetDsn("bigquery1")
          .SetCatalog("publicdata")
          .SetBillingCatalog("bigquery-e2e")
          .Build();
      try {
        connection.open();
        using (OdbcCommand command =  connection.CreateCommand()) {
          command.CommandText = Query;
          using (OdbcDataReader reader = command.ExecuteReader()) {
            PrintResults(reader);
          }
        }
      } catch (Exception ex) {
        System.Console.WriteLine("Error {0}: {1}",connection.State != ConnectionState.Open 
              ? "opening connection" : "executing query",ex);
      } finally {
        connection.Close();
      }
      System.Console.ReadKey();
    }
  }
}