CODEWITHSUNDEEP
ruby-on-railsrubygoogle-apigoogle-bigquerygoogle-api-client
Pandurang Waghulde
Pandurang Waghulde

Reputation: 1035

Parsing response from Google big query

i am fetching records from Google big query using gem 'google-api-client', When I fetch records from table 

 client.execute(api_method: @compute_api.tabledata.list,
                                    parameters: {projectId: project,
                                                datasetId: dataset, 
                                                tableId: table,
                                                maxResults: 10}).body

I get response like,

{
"kind": "bigquery#tableDataList",
"etag": "\"iBDiwpngzDA0oFU52344ksWOrjA/-xEFKhLUueR63_XVaLG4z_mJt-8\"",
"totalRows": "2000113",
"pageToken": "BEIYURQ3J4AQAAAS23IIBAEAAUNAICAMCAGCBMFOCU======",
"rows": [
{
"f": [
{
 "v": "11873943041"
},
{
 "v": "[email protected]"
},
{
 "v": "1.430438401E9"
},
{
 "v": "1.430438402E9"
},
{
 "v": "1.430438404E9"
},
{
 "v": "1.430438862E9"
}]}]}

Which does not have column names in it, Does anyone have any idea about how to get columns names along with data?

Currently I need to make another API request to fetch schema and get column names.

Upvotes: 6

Views: 1620

Answers (2)

Jeremy Condit
Jeremy Condit

Reputation: 7046

The API does not provide a way to get the schema and rows for an arbitrary table in a single API call. You need to call tables.get to get the schema, and then tabledata.list to get the rows.

However, if you're running a query, you can get the output schema and output rows in a single API call by using jobs.query or jobs.getQueryResults. You can even call jobs.getQueryResults on an already-completed query job, even if that query job was executed by some other means.

https://cloud.google.com/bigquery/docs/reference/v2/jobs/query https://cloud.google.com/bigquery/docs/reference/v2/jobs/getQueryResults

Upvotes: 1

Pandurang Waghulde
Pandurang Waghulde

Reputation: 1035

I found answer for this myself using bigquery command line tool (bq),

bq --format=json query "select * from calls.details limit 10"

when using bq if we dont provide --quiet option then it returns response with additional text to it(status about big query job), that causes problem in parsing Json as shown below

Waiting on bqjob_r36676afce1bcba8d_0000014f1ba0e36b_1 ... (0s) Current status: DONE   
[{"status":null,"userfield":null,"answer_stamp":"2015-05-01 00:00:04","term_roid":"a"}]

Thats the reason I moved to use google api to fetch data and again that doesn't give you column names along with data. But I found that we can remove that extra text by using --quiet option for bq command like

bq --quiet --format=json query "select * from calls.details limit 10"

Upvotes: 1

Related Questions