How to Select large dataset and loading datatable from bigquery using c# application

Question

I have created C#.NET application (using Service Authentication).

I am trying to run select statement (Public Sample Table From Google BigQuery) and loading results into datatable, but unable to achieve it, it throws error as

Query Causing Error is: "SELECT * FROM [publicdata:samples.github_timeline]"

Google.Apis.Requests.RequestError

Response too large to return. Consider setting allowLargeResults to true in your job configuration. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors [403]

Errors [

Message[Response too large to return. Consider setting allowLargeResults to true in your job configuration. For more information, see https://cloud.google.com/bigquery/troubleshooting-errors] Location[ - ] Reason[responseTooLarge] Domain[global]

].

Here is the C# Code.

        String serviceAccountEmail = "SERVICE ACCOUNT EMAIL ADDRESS";

            var certificate = new X509Certificate2(@"KEY FILE NAME", "KEY SECRET", 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.github_timeline]";

            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();

How do we select large dataset and loading the results into Datatable in Best way? Getting worried with BigQuery throws these types of errors, then how we will trust our program works 100%.

Answer 1

Returning large query results

Normally, queries have a maximum response size. If you plan to run a query that might return larger results, you can set allowLargeResults to true in your job configuration.

Queries that return large results will take longer to execute, even if the result set is small, and are subject to additional limitations:

You must specify a destination table. You can't specify a top-level ORDER BY, TOP or LIMIT clause. Doing so negates the benefit of using allowLargeResults, because the query output can no longer be computed in parallel. Window functions can return large query results only if used in conjunction with a PARTITION BY clause.

The question will be if the Google .net client library supports adding allowLargeResults to the request. check for option values.

if your lucky something like :

qr.allowLargeResults = true