Accessing BigQuery RECORD - Repeated in Tableau

Question

I have a BigQuery Table with a column of RECORD type & mode REPEATED. I have to query and use this table in Tableau. Using UNNEST or FLATTEN in BigQuery is performing CROSS JOIN of the Table which is impacting performance. Is there any other way to use this table in Tableau without flattening it. Have posted the table schema image link below.

[Schema of Table] https://i.stack.imgur.com/T4jHg.png

What are you trying to do with that column? What queries have you tried so far? — Elliott Brossard
We are trying to join this table with few more tables to supply data to Tableau Report. Performance is hit on doin this. — Safiyur

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2017-01-25T20:05:37

Is there any other way to use ... ?

You should not afraid UNNEST just because it “does” CROSS JOIN
The trick is that even though it is cross join but it is cross join within the row only and global to all rows in table. At the same time, there are always way to do stuff different
So, below example 1 – presents dummy example using UNNEST
And then Example 2 – shows how to do the same without using UNNEST, but rather using SQL UDF
You have not presented specifics about your case, so below is generic enough to show ‘other’ way

With Flattening via UNNEST

#standardSQL
WITH yourTable AS (
  SELECT 1 AS id, ARRAY<STRUCT<details INT64, flag STRING, value STRING, description STRING>>
    [(1,'y','a','xxx'),(2,'n','b','yyy'),(3,'y','c','zzz'),(4,'n','d','vvv')] AS type UNION ALL
  SELECT 2 AS id, ARRAY<STRUCT<details INT64, flag STRING, value STRING, description STRING>>
    [(11,'t','c','xxx'),(21,'n','a','yyy'),(31,'y','c','zzz'),(41,'f','d','vvv')] AS type
)
SELECT id, SUM(t.details) AS details
FROM yourTable, UNNEST(type) AS t
WHERE t.flag = 'y'
GROUP BY id

With SQL UDF

#standardSQL
CREATE TEMP FUNCTION do_something (
  type  ARRAY<STRUCT<details INT64, flag STRING, value STRING, description STRING>>
)
RETURNS INT64 AS ((
  SELECT SUM(t.details) AS details
  FROM UNNEST(type) AS t
  WHERE t.flag = 'y'
));

WITH yourTable AS (
  SELECT 1 AS id, ARRAY<STRUCT<details INT64, flag STRING, value STRING, description STRING>>
    [(1,'y','a','xxx'),(2,'n','b','yyy'),(3,'y','c','zzz'),(4,'n','d','vvv')] AS type UNION ALL
  SELECT 2 AS id, ARRAY<STRUCT<details INT64, flag STRING, value STRING, description STRING>>
    [(11,'t','c','xxx'),(21,'n','a','yyy'),(31,'y','c','zzz'),(41,'f','d','vvv')] AS type
)
SELECT id, do_something(type) AS details
FROM yourTable

Accessing BigQuery RECORD - Repeated in Tableau

1 Answers