How to create a bigquery table and import from cloud storage using the ruby api

Question

Im trying to create a table on BigQuery - I have a single dataset and need to use the api to add a table and import data (json.tar.gz) from cloud storage. I need to be able to use the ruby client to automate the whole process. I have two questions:

I have read the docs and tried to get it to upload (code below) and have not been successful and have absolutely no idea what Im doing wrong. Could somebody please enlighten me or point me in the right direction?
Once I make the request, how do I know when the job has actually finished? From the API, I presume Im meant to use a jobs.get request? Having not completed the first part I have been unable to get to look at this aspect.

This is my code below.

config= {
  'configuration'=> {
      'load'=> {
        'sourceUris'=> ["gs://person-bucket/person_json.tar.gz"],
        'schema'=> {
          'fields'=> [
            { 'name'=>'person_id', 'type'=>'integer' },
            { 'name'=> 'person_name', 'type'=>'string' },
            { 'name'=> 'logged_in_at', 'type'=>'timestamp' },
          ]
        },
        'destinationTable'=> {
          'projectId'=> "XXXXXXXXX",
          'datasetId'=> "personDataset",
          'tableId'=> "person"
        },
        'createDisposition' => 'CREATE_IF_NEEDED',
        'maxBadRecords'=> 10,
      }
    },
    'jobReference'=>{'projectId'=>XXXXXXXXX}
  }

multipart_boundary="xxx"
body = "--#{multipart_boundary}\n"
body += "Content-Type: application/json; charset=UTF-8\n\n"
body += "#{config.to_json}\n"
body += "--#{multipart_boundary}\n"
body +="Content-Type: application/octet-stream\n\n"
body += "--#{multipart_boundary}--\n"

param_hash = {:api_method=> bigquery.jobs.insert }
param_hash[:parameters] = {'projectId' => 'XXXXXXXX'}
param_hash[:body] = body
param_hash[:headers] = {'Content-Type' => "multipart/related; boundary=#{multipart_boundary}"}

result = @client.execute(param_hash)
puts JSON.parse(result.response.header)

I get the following error: {"error"=>{"errors"=>[{"domain"=>"global", "reason"=>"wrongUrlForUpload", "message"=>"Uploads must be sent to the upload URL. Re-send this request to https://www.googleapis.com/upload/bigquery/v2/projects/XXXXXXXX/jobs"}], "code"=>400, "message"=>"Uploads must be sent to the upload URL. Re-send this request to https://www.googleapis.com/upload/bigquery/v2/projects/XXXXXXXX/jobs"}}

From the request header, it appears to be going to the same URI the error says it should go to, and I am quite at a loss for how to proceed. Any help would be much appreciated.

Thank you and have a great day!

Jordan Tigani Jordan Tigani · Accepted Answer · 2013-11-14T15:52:47

Since this is a "media upload" request, there is a slightly different protocol for making the request. The ruby doc here http://rubydoc.info/github/google/google-api-ruby-client/file/README.md#Media_Upload describes it in more detail. I'd use resumable upload rather than multipart because it is simpler.
Yes, as you suspected, the way to know when it is done is to do a jobs.get() to look up the status of the running job. The job id will be returned in the response from jobs.insert(). If you want more control, you can pass your own job id, so that in the event that the jobs.insert() call returns an error you can find out whether the job actually started.

How to create a bigquery table and import from cloud storage using the ruby api

2 Answers