6

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": "639592585-0-1809110554@8.19.146.76"
},
{
 "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.

Pandurang Waghulde
  • 995
  • 1
  • 6
  • 19

2 Answers2

1

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"
Pandurang Waghulde
  • 995
  • 1
  • 6
  • 19
1

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

Jeremy Condit
  • 6,766
  • 1
  • 28
  • 30
  • Even after using jobs.getQueryResults, looks like you will need to map those schema labels with actual data. Command line tool bq seems to be better solution for this which gives proper output as I mentioned in above answer. – Pandurang Waghulde Aug 13 '15 at 08:05
  • Yes, you still have to do the mapping, but you only have to make one API call to get all the information. Using bq is fine if it works for you (since it does all this under the hood), but it's also not intended to be an API, so the formatting and output are subject to change. – Jeremy Condit Aug 13 '15 at 16:06