Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 622
Pandurang Waghulde Avatar asked Aug 11 '15 06:08

Pandurang Waghulde


People also ask

How do I Unnest JSON?

There are three parts to this: flatten the original JSON array and select the values you want from it. create new JSON objects based on the resulting row values. combine the JSON objects into a single object.

Is Google BigQuery same as SQL?

BigQuery supports the Google Standard SQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use Google Standard SQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using Google Standard SQL.

Is BigQuery better than SQL?

Google BigQuery is a cloud-based Architecture and provides exceptional performance as it can auto-scale up and down based on the data load and performs data analysis efficiently. On the other hand, SQL Server is based on client-server architecture and has fixed performance throughout unless the user scales it manually.

Is BigQuery good for transactional data?

BigQuery stores data using a columnar storage format that is optimized for analytical queries. BigQuery presents data in tables, rows, and columns and provides full support for database transaction semantics (ACID).


2 Answers

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"
like image 101
Pandurang Waghulde Avatar answered Oct 20 '22 18:10

Pandurang Waghulde


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

like image 1
Jeremy Condit Avatar answered Oct 20 '22 16:10

Jeremy Condit