BigQuery is Google's fully managed, petabyte scale, low cost analytics data warehouse. BigQuery is NoOps—there is no infrastructure to manage and you don't need a database administrator—so you can focus on analyzing data to find meaningful insights, use familiar SQL, and take advantage of our pay-as-you-go model.

In this codelab, you will use Google Cloud Client Libraries for .NET to query BigQuery public datasets with C#.

What you'll learn

What you'll need

Survey

How will you use this tutorial?

Read it through only Read it and complete the exercises

How would you rate your experience with C#?

Novice Intermediate Proficient

How would you rate your experience with using Google Cloud Platform services?

Novice Intermediate Proficient

Self-paced environment setup

If you don't already have a Google Account (Gmail or Google Apps), you must create one. Sign-in to Google Cloud Platform console (console.cloud.google.com) and create a new project:

Remember the project ID, a unique name across all Google Cloud projects (the name above has already been taken and will not work for you, sorry!). It will be referred to later in this codelab as PROJECT_ID.

Next, you'll need to enable billing in the Cloud Console in order to use Google Cloud resources.

Running through this codelab shouldn't cost you more than a few dollars, but it could be more if you decide to use more resources or if you leave them running (see "cleanup" section at the end of this document).

New users of Google Cloud Platform are eligible for a $300 free trial.

Start Cloud Shell

While Google Cloud can be operated remotely from your laptop, in this codelab you will be using Google Cloud Shell, a command line environment running in the Cloud.

Activate Google Cloud Shell

From the GCP Console click the Cloud Shell icon on the top right toolbar:

Then click "Start Cloud Shell":

It should only take a few moments to provision and connect to the environment:

This virtual machine is loaded with all the development tools you'll need. It offers a persistent 5GB home directory, and runs on the Google Cloud, greatly enhancing network performance and authentication. Much, if not all, of your work in this lab can be done with simply a browser or your Google Chromebook.

Once connected to the cloud shell, you should see that you are already authenticated and that the project is already set to your PROJECT_ID.

Run the following command in the cloud shell to confirm that you are authenticated:

gcloud auth list

Command output

Credentialed accounts:
 - <myaccount>@<mydomain>.com (active)
gcloud config list project

Command output

[core]
project = <PROJECT_ID>

If it is not, you can set it with this command:

gcloud config set project <PROJECT_ID>

Command output

Updated property [core/project].

BigQuery API should be enabled by default in all Google Cloud projects. You can check whether this is true with the following command in the Cloud Shell: You should be BigQuery listed:

gcloud services list

You should see BigQuery listed:

NAME                              TITLE
bigquery-json.googleapis.com      BigQuery API
...

In case the BigQuery API is not enabled, you can use the following command in the Cloud Shell to enable it:

gcloud services enable bigquery-json.googleapis.com

In order to make requests to the BigQuery API, you need to use a Service Account. A Service Account belongs to your project and it is used by the Google Client C# library to make BigQuery API requests. Like any other user account, a service account is represented by an email address. In this section, you will use the Cloud SDK to create a service account and then create credentials you will need to authenticate as the service account.

First, set an environment variable with your PROJECT_ID which you will use throughout this codelab:

export GOOGLE_CLOUD_PROJECT=$(gcloud config get-value core/project)

Next, create a new service account to access the BigQuery API by using:

gcloud iam service-accounts create my-bigquery-sa --display-name "my bigquery codelab service account"

Next, create credentials that your C# code will use to login as your new service account. Create these credentials and save it as a JSON file "~/key.json" by using the following command:

gcloud iam service-accounts keys create ~/key.json --iam-account  my-bigquery-sa@${GOOGLE_CLOUD_PROJECT}.iam.gserviceaccount.com

Finally, set the GOOGLE_APPLICATION_CREDENTIALS environment variable, which is used by the BigQuery API C# library, covered in the next step, to find your credentials. The environment variable should be set to the full path of the credentials JSON file you created, by using:

export GOOGLE_APPLICATION_CREDENTIALS="/home/${USER}/key.json"

You can read more about authenticating the BigQuery API.

BigQuery uses Identity and Access Management (IAM) to manage access to resources. BigQuery has a number of predefined roles (user, dataOwner, dataViewer etc.) that you can assign to your service account you created in the previous setep. You can read more about Access Control in BigQuery docs.

Before you can query the public datasets, you need to make sure the service account has at least the user role. In Cloud Shell, run the following command to assign the user role to the service account:

gcloud projects add-iam-policy-binding ${GOOGLE_CLOUD_PROJECT} --member "serviceAccount:my-bigquery-sa@${GOOGLE_CLOUD_PROJECT}.iam.gserviceaccount.com" --role "roles/bigquery.user"

You can run the following command to verify that the service account has the user role:

gcloud projects get-iam-policy $GOOGLE_CLOUD_PROJECT

First, create a simple C# console application that you will use to run BigQuery API samples.

dotnet new console -n BigQueryDemo

You should see the application created and dependencies resolved:

The template "Console Application" was created successfully.
Processing post-creation actions...
...
Restore succeeded.

Next, navigate to BigQueryDemo folder:

cd BigQueryDemo

And add Google.Cloud.BigQuery.V2 NuGet package to the project:

dotnet add package Google.Cloud.BigQuery.V2
info : Adding PackageReference for package 'Google.Cloud.BigQuery.V2' into project '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.
log  : Restoring packages for /home/atameldev/BigQueryDemo/BigQueryDemo.csproj...
...
info : PackageReference for package 'Google.Cloud.BigQuery.V2' version '1.2.0' added to file '/home/atameldev/BigQueryDemo/BigQueryDemo.csproj'.

Now, you're ready to use BigQuery API!

A public dataset is any dataset that is stored in BigQuery and made available to the general public. There are many other public datasets available for you to query, some of which are also hosted by Google, but many more that are hosted by third parties. You can read more on the Public Datasets page.

In addition to the public datasets, BigQuery provides a limited number of sample tables that you can query. These tables are contained in the bigquery-public-data:samples dataset. One of those tables is called shakespeare. It contains a word index of the works of Shakespeare, giving the number of times each word appears in each corpus.

In this step, you will query the shakespeare table.

First, open the code editor from the top right side of the Cloud Shell:

Navigate to the Program.cs file inside the BigQueryDemo folder and replace the code with the following. Make sure you replace projectId with your actual project id:

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "samples", "shakespeare");
            var sql = $"SELECT corpus AS title, COUNT(word) AS unique_words FROM {table} GROUP BY title ORDER BY unique_words DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
            }
        }
    }
}

Take a minute or two to study the code and see how the table is being queried.

Back in Cloud Shell, run the app:

dotnet run

You should see a list of words and their occurrences:

hamlet: 5318
kinghenryv: 5104
cymbeline: 4875
troilusandcressida: 4795
kinglear: 4784
kingrichardiii: 4713
2kinghenryvi: 4683
...

To get more familiar with BigQuery, you'll now issue a query against GitHub public dataset. You will find the most common commit messages on GitHub. You'll also use BigQuery 's Web console to preview and run ad-hoc queries.

To see how the data looks like, open the GitHub dataset in the BigQuery web UI:

https://console.cloud.google.com/bigquery?p=bigquery-public-data&d=github_repos&t=commits&page=table

Get a quick preview of how the data looks, use the Preview button:

Navigate to the Program.cs file inside the BigQueryDemo folder and replace the code with the following. Make sure you replace projectId with your actual project id:

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";

            var results = client.ExecuteQuery(sql, parameters: null);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }
        }
    }
}

Take a minute or two to study the code and see how the table is being queried for the most common commit messages.

Back in Cloud Shell, run the app:

dotnet run

You should see a list of commit messages and their occurrences:

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863

After the initial query, BigQuery caches the results. As a result, the subsequent queries take much less time. It is possible to disable caching with query options. BigQuery also keeps track of some stats about the queries such as creation time, end time, total bytes processed.

In this step, you will disable caching and also display some stats about the queries.

Navigate to the Program.cs file inside the BigQueryDemo folder and replace the code with the following. Make sure you replace projectId with your actual project id:

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var client = BigQueryClient.Create("projectId");
            var table = client.GetTable("bigquery-public-data", "github_repos", "commits");
            
            var sql = $"SELECT subject AS subject, COUNT(*) AS num_duplicates FROM {table} GROUP BY subject ORDER BY num_duplicates DESC LIMIT 10";
            var queryOptions = new QueryOptions {
                UseQueryCache = false
            };

            var results = client.ExecuteQuery(sql, parameters: null, queryOptions: queryOptions);

            foreach (var row in results)
            {
                Console.WriteLine($"{row["subject"]}: {row["num_duplicates"]}");
            }

            var job = client.GetJob(results.JobReference);
            var stats = job.Statistics;
            Console.WriteLine("----------");
            Console.WriteLine($"Creation time: {stats.CreationTime}");
            Console.WriteLine($"End time: {stats.EndTime}");
            Console.WriteLine($"Total bytes processed: {stats.TotalBytesProcessed}");
        }
    }
}

A couple of things to note about the code. First, caching is disabled by introducing query options and setting UseQueryCache to false. Second, you accessed the statistics about the query from the job object.

Back in Cloud Shell, run the app:

dotnet run

Like before, you should see a list of commit messages and their occurrences. In addition, you should also see some stats about the query in the end

Update README.md: 2509242
: 1971725
Initial commit: 1942149
Mirroring from Micro.blog.: 838586
update: 575188
Update data.json: 548651
Update data.js: 548339
Add files via upload: 379941
*** empty log message ***: 358528
Can't you see I'm updating the time?: 286863
----------
Creation time: 1533052057398
End time: 1533052066961
Total bytes processed: 9944197093

If you want to query your own data, you need first load your data into BigQuery. BigQuery supports loading data from many sources such as Google Cloud Storage, other Google services, a readable source. You can even stream your data using the streaming inserts. You can read more on Loading Data into BigQuery page.

In this step, you will load a JSON file stored on Google Cloud Storage into a BigQuery table. The JSON file is located at gs://cloud-samples-data/bigquery/us-states/us-states.json

If you're curious about the contents of the JSON file, you can use gsutil command line tool to download it in the Cloud Shell:

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .
Copying gs://cloud-samples-data/bigquery/us-states/us-states.json...
/ [1 files][  2.0 KiB/  2.0 KiB]                                                
Operation completed over 1 objects/2.0 KiB.

You can see that it contains the list of US states and each state is a JSON document on a separate line:

less us-states.json
{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

To load this JSON file into BigQuery, navigate to the Program.cs file inside the BigQueryDemo folder and replace the code with the following. Make sure you replace projectId with your actual project id:

using System;
using Google.Cloud.BigQuery.V2;

namespace BigQueryDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            var gcsUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
            var client = BigQueryClient.Create("projectId");
            var dataset = client.GetOrCreateDataset("us_states_dataset");

            var schema = new TableSchemaBuilder 
            {
                { "name", BigQueryDbType.String },
                { "post_abbr", BigQueryDbType.String }
            }.Build();

            var jobOptions = new CreateLoadJobOptions
            {
                SourceFormat = FileFormat.NewlineDelimitedJson
            };

            var table = dataset.GetTableReference("us_states_table");
            var loadJob = client.CreateLoadJob(gcsUri, table, schema, jobOptions);

            loadJob.PollUntilCompleted();
            loadJob.ThrowOnAnyError();
            Console.WriteLine("Json file loaded to BigQuery");
        }
    }
}

Take a minute of two to study how the code loads the JSON file and creates a table with a schema under a dataset.

Back in Cloud Shell, run the app:

dotnet run

A dataset and a table are created in BigQuery

Json file loaded to BigQuery

To verify that the dataset is actually created, you can go to the BigQuery console. You should see a new dataset and a table created. If you switch to the preview tab of the table, you can see the actual data:

You learned how to use BigQuery using C#!

Clean up

To avoid incurring charges to your Google Cloud Platform account for the resources used in this quickstart:

Learn More

License

This work is licensed under a Creative Commons Attribution 2.0 Generic License.