Upload CSV to BigQuery in C#

Question

Basically what I want to do is to submit a job to BigQuery (Asynchronously), check job status after and print out corresponding status info or error info. I created a frame as below. But I need help on:

1. GoogleApiException: Not Found Job exception when "BigQueryService.Jobs.Get(jobReference.ProjectId, jobReference.JobId).Execute()" was called. My gut is that the job wasn't submit correctly, but I don't know how to do it correctly.

2. How should I handle GoogleApiExceptions?

Firt step: create a Job (uploading CSV file into BigQuery), return the JobReference

        TableReference DestTable = new TableReference();
        DestTable.ProjectId = project;
        DestTable.DatasetId = dataset;
        DestTable.TableId = tableId;

        Job Job = new Job();
        JobConfiguration Config = new JobConfiguration();
        JobConfigurationLoad ConfigLoad = new JobConfigurationLoad();


        ConfigLoad.Schema = schema;
        ConfigLoad.DestinationTable = DestTable;
        ConfigLoad.Encoding = "ISO-8859-1";
        ConfigLoad.CreateDisposition = "CREATE_IF_NEEDED";
        ConfigLoad.WriteDisposition = createDisposition;
        ConfigLoad.FieldDelimiter = delimiter.ToString();
        ConfigLoad.AllowJaggedRows = true;
        Config.Load = ConfigLoad;
        Job.Configuration = Config;

        //set job reference (mainly job id)
        JobReference JobRef = new JobReference();
        JobRef.JobId = GenerateJobID("Upload");
        JobRef.ProjectId = project;
        Job.JobReference = JobRef;

        using(FileStream fileStream = new FileStream(filePath,FileMode.Open)){
            var JobInfo = BigQueryService.Jobs.Insert(Job,project,fileStream,"text/csv");//application/octet-stream
            JobInfo.UploadAsync();
            Console.WriteLine(JobInfo.GetProgress().Status.ToString());
        }
        return JobRef;

Then, Pull Job status using projectId and jobId in the returned JobReference from the first step:

     while (true)
        {
              pollJob = BigQueryService.Jobs.Get(jobReference.ProjectId, jobReference.JobId).Execute();
                i = 0;
                Console.WriteLine("Job status" + jobReference.JobId + ": " + pollJob.Status.State);
                if (pollJob.Status.State.Equals("DONE"))
                {
                    return pollJob;
                }
                // Pause execution for pauseSeconds before polling job status again,
                // to reduce unnecessary calls to the BigQuery API and lower overall
                // application bandwidth.
                Thread.Sleep(pauseSeconds * 1000);

        }

Answer 1

There's hardly any useful sample code out there showing how to upload a local CSV file to Bigquery table. I eventually get something to work. It might not be the best solution, but it at least works. It's open to any improvement.

private JobReference JobUpload(string project, string dataset, string tableId, string filePath, TableSchema schema, string createDisposition, char delimiter)
    {

        TableReference DestTable = new TableReference();
        DestTable.ProjectId = project;
        DestTable.DatasetId = dataset;
        DestTable.TableId = tableId;

        Job Job = new Job();
        JobConfiguration Config = new JobConfiguration();
        JobConfigurationLoad ConfigLoad = new JobConfigurationLoad();


        ConfigLoad.Schema = schema;
        ConfigLoad.DestinationTable = DestTable;
        ConfigLoad.Encoding = "ISO-8859-1";
        ConfigLoad.CreateDisposition = "CREATE_IF_NEEDED";
        ConfigLoad.WriteDisposition = createDisposition;
        ConfigLoad.FieldDelimiter = delimiter.ToString();
        ConfigLoad.AllowJaggedRows = true;
        ConfigLoad.SourceFormat = "CSV";
        Config.Load = ConfigLoad;
        Job.Configuration = Config;

        //set job reference (mainly job id)
        JobReference JobRef = new JobReference();
        JobRef.JobId = GenerateJobID("Upload");
        JobRef.ProjectId = project;
        Job.JobReference = JobRef;

        using(FileStream fileStream = new FileStream(filePath,FileMode.Open)){
            JobsResource.InsertMediaUpload InsertMediaUpload = new  JobsResource.InsertMediaUpload(BigQueryService,Job,Job.JobReference.ProjectId,fileStream,"application/octet-stream");
            var JobInfo = InsertMediaUpload.UploadAsync();
            Console.WriteLine(JobInfo.Status);
            while (!JobInfo.IsCompleted)
            {
               //wait for the job to be activated and run 
                Console.WriteLine(JobInfo.Status);
            }
        }
        return JobRef;
    }

After this, you can actually use the returned JobRef to pull job status, almost the same as we do with Java API:

while(true)
{
     PollJob = BigQueryService.Jobs.Get(jobReference.ProjectId, jobReference.JobId).Execute();

     Console.WriteLine("Job status" + jobReference.JobId + ": " + PollJob.Status.State);
     if (PollJob.Status.State.Equals("DONE"))
     {
       return PollJob;
     }
}