Flat data of BigQuery table and copy the flatted data to a new BigQuery table

Question

I'm new to BQ. I have a table which some of the columns are record-repeated. I'm trying to flat the table, so it would be kind of relational, and to insert the flatted data into new BigQuery table. Is it possible? How should I do it?

Answer 1

below is for BigQuery Standard SQL

Assume you have simple table like below

Row id  repeated_record  
--- --  ---------------
1   1   google   
        facebook     
        viant    
2   2   dell     
        hp     

You can easily mimic it with below query

#standardSQL
WITH `table-with-repeated-record` AS (
  SELECT 1 AS id, ['google', 'facebook', 'viant'] AS repeated_record UNION ALL
  SELECT 2, ['dell', 'hp']
)
SELECT *
FROM `table-with-repeated-record`  

So now, to get it flatten - use below query

#standardSQL
WITH `table-with-repeated-record` AS (
  SELECT 1 AS id, ['google', 'facebook', 'viant'] AS repeated_record UNION ALL
  SELECT 2, ['dell', 'hp']
)
SELECT id, flatted_data
FROM `table-with-repeated-record`, 
  UNNEST(repeated_record) AS flatted_data   

result will be as below

Row id  flatted_data     
--- --  ------------
1   1   google   
2   1   facebook     
3   1   viant    
4   2   dell     
5   2   hp    

Below is another example

#standardSQL
WITH `table-with-repeated-record` AS (
  SELECT 1 AS id, [STRUCT<line INT64, name STRING>(1, 'google'), (2, 'facebook'), (3, 'viant')] AS repeated_record UNION ALL
  SELECT 2, [STRUCT<line INT64, name STRING>(5, 'dell'), (6, 'hp')]
)
SELECT *
FROM `table-with-repeated-record`  

which mimic below table

Row id  repeated_record.line    repeated_record.name     
--- --  --------------------    --------------------
1   1   1                       google   
        2                       facebook     
        3                       viant    
2   2   5                       dell     
        6                       hp

and way to flatten it is:

#standardSQL
WITH `table-with-repeated-record` AS (
  SELECT 1 AS id, [STRUCT<line INT64, name STRING>(1, 'google'), (2, 'facebook'), (3, 'viant')] AS repeated_record UNION ALL
  SELECT 2, [STRUCT<line INT64, name STRING>(5, 'dell'), (6, 'hp')]
)
SELECT id, flatted_data.line, flatted_data.name
FROM `table-with-repeated-record`, 
  UNNEST(repeated_record) AS flatted_data   

which ends up with

Row id  line    name     
--- --  ----    ----
1   1   1       google   
2   1   2       facebook     
3   1   3       viant    
4   2   5       dell     
5   2   6       hp    

do you have any idea how to do it without specify the data like ['google', 'facebook', 'viant'] etc? the size of the table is not constant, and it is changing from time to time, as well the data the is stored in the table, the only thing I know for sure is the columns

you should just use below (without dummy data that was used as an example and for you to be able to play with query)

#standardSQL
SELECT id, flatted_data.line, flatted_data.name
FROM `yourProject.yourDataset.yourTable`, 
  UNNEST(repeated_record) AS flatted_data