BigQuery: The results have much more columns that I have selected after using the UNNEST function & ways to clean the results data

Question

I used the following code to query Google Analytics data from BigQuery. I only selected a few metrics but seeing from the results, it gives me everything starts with "product".

#standardSQL
SELECT
  date, prod.productRevenue,
  hits.transaction.transactionId, 
  prod.productQuantity, hits.product, prod.productSKU
FROM `XXX.ga_sessions_*`,
UNNEST(hits) AS hits,
UNNEST(hits.product) AS prod   
WHERE _TABLE_SUFFIX BETWEEN 'XXX' AND 'XXX' AND hits.transaction.transactionId IS NOT NULL

Is this because I used UNNEST? If so what should I do to only query the columns I have selected?

Also, is there a good way to clean the results data? The results data look like this:

v2ProductName:"AAA" productQuantity:"1" transactionId:"123"
v2ProductName:"BBB" productQuantity:"1" transactionId:"456"

But what I want is:

+---------------+-----------------+---------------+
| v2ProductName | productQuantity | transactionId |
+---------------+-----------------+---------------+
| AAA           | 1               | 123           |
| BBB           | 1               | 456           |
+---------------+-----------------+---------------+

Is there a way to format the results like this before exporting it from BigQuery? If not, is there a good way to do that?

"The results data look like this" - where do they look like this? Representation is context dependant — Felipe Hoffa, May 22 '19 at 04:54
Export the results as a json file, the json file looks like that — xxyb, May 22 '19 at 16:15
Ok, so you want a JSON file to look like a CSV file? Have you tried exporting it as CSV? — Felipe Hoffa, May 22 '19 at 20:05
The file is too big to be exported as CSV. The only option BigQuery gave me was JSON. — xxyb, May 22 '19 at 22:07
AFAIK - there's no size limitation to the size of CSV exports — Felipe Hoffa, May 22 '19 at 22:20
No they do have limitations in export: https://cloud.google.com/bigquery/docs/exporting-data — xxyb, May 22 '19 at 23:46
"You cannot export nested and repeated data in CSV format." I think this is why I am not able to export it as a csv — xxyb, May 23 '19 at 23:17
The query you pasted doesn't seem to have any nested data. The JSON results you pasted don't match with the query either. Make sure your results don't contain nested/repeated data, and you'll be able to export CSVs. — Felipe Hoffa, May 23 '19 at 23:19

score 0 · Answer 1 · answered May 23 '19 at 23:20

0

The query you pasted doesn't seem to have any nested data.

The JSON results you pasted don't match with the query either.

Make sure your results don't contain nested/repeated data, and you'll be able to export CSVs.

answered May 23 '19 at 23:20

Felipe Hoffa

54,922
16
151
325

BigQuery: The results have much more columns that I have selected after using the UNNEST function & ways to clean the results data

1 Answers1