CODEWITHSUNDEEP
windows-servicesgoogle-apigoogle-bigquery
Pratik Borkar
Pratik Borkar

Reputation: 475

Authentication Steps to BigQuery via Windows Service

I've created a project in Big Query and within API Access pane, I've created Service Account so that i can make Big Query API accessible through windows application on behalf of user interaction. Since i am new to using and accessing Google API, I want to know the basic steps so that i can access Big Query via Windows Service.

Upvotes: 1

Views: 504

Answers (2)

Pratik Borkar
Pratik Borkar

Reputation: 475

Finally here's the working code for Authentication process to Big Query API and retrieving records from Big Query table:-

I've created a class having a method of return type OAuth2Authenticator<AssertionFlowClient>.

 internal class clsGetOAuth2Authentication
    {
        public OAuth2Authenticator<AssertionFlowClient> objGetOAuth2(string strPrivateFilePath, string strPrivateFilePassword, string strServiceAccEmailId,string strScope)
        {
            AuthorizationServerDescription objAuthServerDesc;
            X509Certificate2 objKey;
            AssertionFlowClient objClient;
            OAuth2Authenticator<AssertionFlowClient> objAuth = null;

            string ScopeUrl = "https://www.googleapis.com/auth/" + strScope;
            string strSrvAccEmailId = strServiceAccEmailId; 
            string strKeyFile = strPrivateFilePath; //KeyFile: This is the physical path to the key file you downloaded when you created your Service Account.
            string strKeyPassword = (strPrivateFilePassword != "") ? strPrivateFilePassword : "notasecret"; //key_pass: This is probably the password for all key files, but if you're given a different one, use that.

            objAuthServerDesc = GoogleAuthenticationServer.Description; //objAuthServerDesc: Description of the server that will grant Authentiation. 
            objKey = new X509Certificate2(strKeyFile, strKeyPassword, X509KeyStorageFlags.Exportable); //objkey: Load up and decrypt the key.
            objClient = new AssertionFlowClient(objAuthServerDesc, objKey) { ServiceAccountId = strSrvAccEmailId, Scope = ScopeUrl }; //objClient: Using the AssertionFlowClient, because we're logging in with our certificate.
            objAuth = new OAuth2Authenticator<AssertionFlowClient>(objClient, AssertionFlowClient.GetState); //objAuth: Requesting Authentication.
            return objAuth;
        }
    }

Now, another class calls the above method:-

clsGetOAuth2Authentication objOAuth2 = new clsGetOAuth2Authentication();
            try
            {
                var objAuth = objOAuth2.objGetOAuth2(strKeyFile, strKeyPassword, strSrvAccEmailId, strScope); //Authentication data returned
                objBQServ = new BigqueryService(objAuth); //Instantiate BigQueryService  with credentials(objAuth) as its parameter

                #region Retrieving Records:-
                JobsResource j = objBQServ.Jobs;
                QueryRequest qr = new QueryRequest();
                qr.Query = strQuery;

                DateTime dtmBegin = DateTime.UtcNow;
                QueryResponse response = j.Query(qr, strProjId).Fetch();
                DateTime dtmEnd = DateTime.UtcNow;
                string strColHead = "";
                foreach (var colHeaders in response.Schema.Fields)
                {
                    strColHead += colHeaders.Name.ToString() + "\t";
                }
                Console.WriteLine(strColHead);
                int intCount = 0;
                foreach (TableRow row in response.Rows)
                {
                    intCount += 1;
                    List<string> list = new List<string>();
                    foreach (var field in row.F)
                    {
                        list.Add(field.V);
                    }
                    Console.WriteLine(String.Join("\t", list));
                }
                TimeSpan tsElapsed = dtmEnd - dtmBegin;
                Console.WriteLine("\n" + "Total no. of records:- " + intCount + ". Time taken:- " + tsElapsed);
                #endregion


            }
            catch (Exception ex)
            {
                Console.WriteLine("Error Occured!" + "\n\n" + "Statement:- " + ex.Message.ToString() + "\n\n" + "Description:- " + ex.ToString() + "\n\n");
                Console.WriteLine("\nPress enter to exit");
                Console.ReadLine();
            }

Upvotes: 0

Ryan Boyd
Ryan Boyd

Reputation: 3018

There are some .NET code samples here using web-based OAuth flows: Google BigQuery with .NET documentation/ samples

For an example from another dev using service accounts in .NET, see this: Google OAuth2 Service Account Access Token Request gives 'Invalid Request' Response

These manually implement service accounts on top of the .NET library. The .NET library has recently added native support for service accounts, though I haven't yet found an official example.

Here's an unofficial sample working with the Analytics API. It should be directly analogous to using service accounts with BigQuery: How do I use a Service Account to Access the Google Analytics API V3 with .NET C#?

Upvotes: 2

Related Questions