Google BigQuery returns only partial table data with C# application using .net Client Library

Question

I am trying to execute the query (Basic select statement with 10 fields). My table contains more than 500k rows. C# application returns the response with only 4260 rows. However Web UI returns all the records.

Why my code returns only partial data, What is the best way to select all the records and load into C# Data Table? If there is any code snippet it would be more helpful to me.

using Google.Apis.Auth.OAuth2;
using System.IO;
using System.Threading;
using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;
using System.Data;
using Google.Apis.Services;
using System;
using System.Security.Cryptography.X509Certificates;

namespace GoogleBigQuery
{
    public class Class1
    {
        private static void Main()
        {
            try
            {
                Console.WriteLine("Start Time: {0}", DateTime.Now.ToString());
                String serviceAccountEmail = "SERVICE ACCOUNT EMAIL";

                var certificate = new X509Certificate2(@"KeyFile.p12", "notasecret", X509KeyStorageFlags.Exportable);

                ServiceAccountCredential credential = new ServiceAccountCredential(
                   new ServiceAccountCredential.Initializer(serviceAccountEmail)
                   {
                       Scopes = new[] { BigqueryService.Scope.Bigquery, BigqueryService.Scope.BigqueryInsertdata, BigqueryService.Scope.CloudPlatform, BigqueryService.Scope.DevstorageFullControl }
                   }.FromCertificate(certificate));

                BigqueryService Service = new BigqueryService(new BaseClientService.Initializer()
                {
                    HttpClientInitializer = credential,
                    ApplicationName = "PROJECT NAME"
                });

                string query = "SELECT * FROM [publicdata:samples.shakespeare]";

                JobsResource j = Service.Jobs;

                QueryRequest qr = new QueryRequest();

                string ProjectID = "PROJECT ID";

                qr.Query = query;
                qr.MaxResults = Int32.MaxValue;
                qr.TimeoutMs = Int32.MaxValue;

                DataTable DT = new DataTable();
                int i = 0;

                QueryResponse response = j.Query(qr, ProjectID).Execute();

                string pageToken = null;

                if (response.JobComplete == true)
                {
                    if (response != null)
                    {
                        int colCount = response.Schema.Fields.Count;

                        if (DT == null)
                            DT = new DataTable();

                        if (DT.Columns.Count == 0)
                        {
                            foreach (var Column in response.Schema.Fields)
                            {
                                DT.Columns.Add(Column.Name);
                            }
                        }

                        pageToken = response.PageToken;

                        if (response.Rows != null)
                        {
                            foreach (TableRow row in response.Rows)
                            {
                                DataRow dr = DT.NewRow();

                                for (i = 0; i < colCount; i++)
                                {
                                    dr[i] = row.F[i].V;
                                }

                                DT.Rows.Add(dr);
                            }
                        }
                        Console.WriteLine("No of Records are Readed: {0} @ {1}", DT.Rows.Count.ToString(), DateTime.Now.ToString());

                        while (true)
                        {
                            int StartIndexForQuery = DT.Rows.Count;
                            Google.Apis.Bigquery.v2.JobsResource.GetQueryResultsRequest SubQR = Service.Jobs.GetQueryResults(response.JobReference.ProjectId, response.JobReference.JobId);
                            SubQR.StartIndex = (ulong)StartIndexForQuery;
                            //SubQR.MaxResults = Int32.MaxValue;
                            GetQueryResultsResponse QueryResultResponse = SubQR.Execute();

                            if (QueryResultResponse != null)
                            {
                                if (QueryResultResponse.Rows != null)
                                {
                                    foreach (TableRow row in QueryResultResponse.Rows)
                                    {
                                        DataRow dr = DT.NewRow();

                                        for (i = 0; i < colCount; i++)
                                        {
                                            dr[i] = row.F[i].V;
                                        }

                                        DT.Rows.Add(dr);
                                    }
                                }

                                Console.WriteLine("No of Records are Readed: {0} @ {1}", DT.Rows.Count.ToString(), DateTime.Now.ToString());

                                if (null == QueryResultResponse.PageToken)
                                {
                                    break;
                                }
                            }
                            else
                            {
                                break;
                            }
                        }
                    }
                    else
                    {
                        Console.WriteLine("Response is null");
                    }
                }


                int TotalCount = 0;

                if (DT != null && DT.Rows.Count > 0)
                {
                    TotalCount = DT.Rows.Count;
                }
                else
                {
                    TotalCount = 0;
                }

                Console.WriteLine("End Time: {0}", DateTime.Now.ToString());
                Console.WriteLine("No. of records readed from google bigquery service: " + TotalCount.ToString());
            }
            catch (Exception e)
            {
                Console.WriteLine("Error Occurred: " + e.Message);
            }

            Console.ReadLine();
        }
    }
}

In this Sample Query get the results from public data set, In table contains 164656 rows but response returns 85000 rows only for the first time, then query again to get the second set of results. (But not known this is the only solution to get all the results).

In this sample contains only 4 fields, even-though it does not return all rows, in my case table contains more than 15 fields, I get response of ~4000 rows out of ~10k rows, I need to query again and again to get the remaining results for selecting 1000 rows takes time up to 2 minutes in my methodology so I am expecting best way to select all the records within single response.

Answer 1

Answer from User #:Pentium10

There is no way to run a query and select a large response in a single shot. You can either paginate the results, or if you can create a job to export to files, then use the files generated in your app. Exporting is free.

Step to run a large query and export results to files stored on GCS:

1) Set allowLargeResults to true in your job configuration. You must also specify a destination table with the allowLargeResults flag.

Example:

"configuration": 
  {
    "query": 
    {
      "allowLargeResults": true,
      "query": "select uid from [project:dataset.table]"
      "destinationTable": [project:dataset.table]

    }
  }

2) Now your data is in a destination table you set. You need to create a new job, and set the export property to be able to export the table to file(s). Exporting is free, but you need to have Google Cloud Storage activated to put the resulting files there.

3) In the end you download your large files from GCS.

It my turn to design the solution for better results.

Answer 2

The Web UI automatically flattens the data. This means that you see multiple rows for each nested field.

When you run the same query via the API, it won't be flattened, and you get fewer rows, as the nested fields are returned as objects. You should check if this is the case at you.

The other is that indeed you need to paginate through the results. Paging through list results has this explained.

If you want to do only one job, than you should write your query ouput to a table, than export the table as JSON, and download the export from GCS.

Answer 3

Hoping this might help someone. One could retrieve next set of paginated result using PageToken. Here is the sample code for how to use PageToken. Although, I liked the idea of exporting for free. Here, I write rows to flat file but you could add them to your DataTable. Obviously, it is a bad idea to keep large DataTable in memory though.

    public void ExecuteSQL(BigqueryService bqservice, String ProjectID)
    {
        string sSql = "SELECT r.Dealname, r.poolnumber, r.loanid FROM [MBS_Dataset.tblRemitData] R left join each [MBS_Dataset.tblOrigData] o on R.Dealname = o.Dealname and R.Poolnumber = o.Poolnumber and R.LoanID = o.LoanID Order by o.Dealname, o.poolnumber, o.loanid limit 100000";

        QueryRequest _r = new QueryRequest();
        _r.Query = sSql;
        QueryResponse _qr = bqservice.Jobs.Query(_r, ProjectID).Execute();

        string pageToken = null;
        if (_qr.JobComplete != true)
        {
            //job not finished yet! expecting more data
            while (true)
            {
                var resultReq = bqservice.Jobs.GetQueryResults(_qr.JobReference.ProjectId, _qr.JobReference.JobId);
                resultReq.PageToken = pageToken;
                var result = resultReq.Execute();

                if (result.JobComplete == true)
                {
                    WriteRows(result.Rows, result.Schema.Fields);
                    pageToken = result.PageToken;
                    if (pageToken == null)
                        break;
                }
            }
        }
        else
        {
            List<string> _fieldNames = _qr.Schema.Fields.ToList().Select(x => x.Name).ToList();
            WriteRows(_qr.Rows, _qr.Schema.Fields);
        }
    }