How to read data from Google BigTables using .NET?

This article is next in series of exploring Google BigQuery and BigTables. In last few posts we learnt what is Google BigQuery and we created some BigTables for demonstration. In this post we will explore how we can read data from Google BigTables using .NET.

Before we jump to .NET, we need to understand some concepts which goes behind the scene towards pulling data from Google BigQuery. We need to know three important details:

- Project ID
- Client Key
- Client Secret

Project ID: When you create your Google account, you get default access to Google API console, a central place to mange access to your Google APIs and billing details. By default, Google creates a project with the name API Project for you. You can create a new project or rename the default project. You need to know Project id, a numeric value of your project or default project( if you are using the default project name). When you create BigTables with Google BigQuery services you create them under this project name. If you do not know the project id of your project, you can visit How to get Project ID of Google Console Projects?

Client Key and Client Secret: When you query data from Google services such as Google Spreadsheet, Google Docs, Picasa etc using your web/ windows application or mobile/ andorid based devices your applications act as client. As a client your identity and authorization need to be known to Google services and, Client key and Client Secret provides this. You need to generate your client key and client secret key using Google API console. This is because Google place limits on API requests and in case you are crossing the free quota limit, Google will charge you as per the uses. You need to visit Google API console to generate your client id and client seceret. When you visit the Google API console for the first time, you see the following screen. You need to click on API Access and go to Create an OAuth 2.0 client ID button to generate your authorization. OAuth is Google Open Authorization. By generation the Google Open Authorization client ID you are allowing user data to be read by clients such as a web page, web service, desktop/mobile applications etc. Next you can click on Generate Key link to generate your client key and client secret.

Once you are set with the three important details, you are all set to start with .NET. You need to download Google Client Library that contains DLLs that you will use in .NET applicaitons. You can download the library from http://code.google.com/p/google-api-dotnet-client/wiki/Downloads#Latest_Stable_Release.

I have following BigTable in Google BigQuery and we will read EmpName from Google BigTable Employee.

Reading data from Google BigTable using .NET is a two step process. In Step 1 we basically sent the user to Google Authorization page. Once the request is authorize, Google geneate an access code which is valid for a limited time. You need to copy this access code to beging with Step 2.

In .NET I have created following interface keeping the two step process in mind.

For Step 1, I have a button Get Authentication. When user click on this button, they are taken to Google Authorization page.

If user click on Allow access, Google generates an access code.

You need to copy this code and paste it to Text box in step 2. When you are done with this, you need to click on button Read Big Table. The code logic written on button 2 will interact with Google BigQuery services and pull data from Google BigTables and data will be shown in Data Grid.

The .NET code logic on this form is following. We need to add references of following Google Data API from the client library that we downloaded before.

using DotNetOpenAuth.OAuth2;
using Google.Apis.Authentication.OAuth2;
using Google.Apis.Authentication.OAuth2.DotNetOpenAuth;

using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;

using Google.Apis.Util;
using System.Diagnostics;

namespace BigQuery
{
    public partial class Form1 : Form
    {
        static string clientId = "<ClientID>.apps.googleusercontent.com";
        static string clientSecret = "<ClientSecret>";
        static string projectId = "<ProjectID>";
        static string query = "SELECT EmpName FROM [BigCompany.Employee];";
        static OAuth2Authenticator<NativeApplicationClient> xx;       
        static IAuthorizationState state;
        static NativeApplicationClient myclient;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            myclient = new NativeApplicationClient(GoogleAuthenticationServer.Description);
            myclient.ClientIdentifier = clientId;
            myclient.ClientSecret = clientSecret;

            state = new AuthorizationState(new[] { BigqueryService.Scopes.Bigquery.GetStringValue() });
            state.Callback = new Uri(NativeApplicationClient.OutOfBandCallbackUrl);
            Uri authUri = myclient.RequestUserAuthorization(state);

            Process.Start(authUri.ToString());          

        }

        private  IAuthorizationState GetAuthorization(NativeApplicationClient arg)
        {            
            return arg.ProcessUserAuthorization(textBox1.Text, state);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            xx = new OAuth2Authenticator<NativeApplicationClient>(myclient, GetAuthorization);
            
            // Create the Google BigQuery service.
            var service = new BigqueryService(xx);
            JobsResource j = service.Jobs;
            QueryRequest qr = new QueryRequest();
            qr.Query = query;

            QueryResponse response = j.Query(qr, projectId).Fetch();

            //Create a DataTable
            DataTable dt = new DataTable("emp");
            dt.Columns.Add("EmpName");
                    
            foreach (TableRow row in response.Rows)
            {
                foreach (TableRow.FData field in row.F)
                {
                    DataRow DR = dt.NewRow();
                    DR["EmpName"] = field.V;
                    dt.Rows.Add(DR);

                }
                
            }
            dataGridView1.DataSource = dt.DefaultView;
        }

      }
}

After clicking on Read BigTable data from Google BigQuery services was pulled and shown in the data grid.

So this is how we can read data from Google BigTables using .NET.

Reference: http://stackoverflow.com/questions/12443878/google-bigquery-with-net-documentation-samples