Google BigQuery - Execute dynamically generated queries from a select statement

Question

Have a huge table in Google BigQuery with following structure (> 100 million rows):

name | departments
abc  | 1,2,5,6
xyz  | 4,5
pqr  | 3,4,6

Want to convert the data into following format:

name | 1 | 2 | 3 | 4 | 5 | 6
abc  | 1 | 1 |   |   | 1 | 1
xyz  |   |   |   | 1 | 1 |
pqr  |   |   | 1 | 1 |   | 1

As of now, able to generate the queries required to prepare the dataset in this format by using CONCAT and REGEX_REPLACE functions:

    SELECT ' insert into dataset.output ( name, ' + 
  CONCAT(
      '_' , replace(departments,',',',_')  ) 
      
   + ' ) values(  \'' + name +'\','+ REGEXP_REPLACE(departments, "([^,\n]+)", "1") +')'
FROM (
select name, departments from dataset.input )

This generates the output with the 100 M insert queries which can be used to create the data in the required structure.

However, now below are my questions:

Can we execute the output of this query (100 M insert queries) directly by using Big Query SQL or we would need to fire each insert one by one?
I believe there is no way to pivoting or transposing the data in a column with multiple comma separated values. Is that right?
Is there a more optimal way of achieving this using BigQuery SQL and not writing custom Java code?

Thanks.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-07-17T23:46:31

Below example for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'abc' name, '1,2,5,6' departments UNION ALL
  SELECT 'xyz', '4,5' UNION ALL
  SELECT 'pqr', '3,4,6' 
)
SELECT 
  name,
  IF(departments LIKE '%1%', 1, 0) AS d1,
  IF(departments LIKE '%2%', 1, 0) AS d2,
  IF(departments LIKE '%3%', 1, 0) AS d3,
  IF(departments LIKE '%4%', 1, 0) AS d4,
  IF(departments LIKE '%5%', 1, 0) AS d5,
  IF(departments LIKE '%6%', 1, 0) AS d6
FROM `project.dataset.table`

with result as

Row name    d1  d2  d3  d4  d5  d6   
1   abc     1   1   0   0   1   1    
2   xyz     0   0   0   1   1   0    
3   pqr     0   0   1   1   0   1

So you need to run above with destination to whatever new table you prepared

Note, above assumes you have just 6 departments and most important there is no ambiguity in numbers like 1 does not conflict with 10 for example
If you do have such case - you need transform below lines

  IF(departments LIKE '%2%', 1, 0) AS d2,

into

  IF(CONCAT(',', departments, ',') LIKE '%,2,%', 1, 0) AS d2 ...

And of course, you can use just one simple INSERT statement

INSERT `project.dataset.new_table` (name, d1, d2, d3, d4, d5, d6)    
SELECT 
  name,
  IF(departments LIKE '%1%', 1, 0) AS d1,
  IF(departments LIKE '%2%', 1, 0) AS d2,
  IF(departments LIKE '%3%', 1, 0) AS d3,
  IF(departments LIKE '%4%', 1, 0) AS d4,
  IF(departments LIKE '%5%', 1, 0) AS d5,
  IF(departments LIKE '%6%', 1, 0) AS d6
FROM `project.dataset.table`

So, the final point of all this is: instead of generating INSERT STATEMENT for each and every row in original table - you should generate simple SELECT statement that does "pivoting"

Update for "extreme" minimizing generated code

See an example:

#standardSQL
CREATE TEMP FUNCTION c(departments STRING, department INT64) AS (
  IF(departments LIKE CONCAT('%',CAST(department AS STRING),'%'), 1, 0)
);
WITH `project.dataset.table` AS (
  SELECT 'abc' name, '1,2,5,6' departments UNION ALL
  SELECT 'xyz', '4,5' UNION ALL
  SELECT 'pqr', '3,4,6' 
), temp AS (
  SELECT name, departments AS d
  FROM `project.dataset.table`
)
SELECT 
name,
c(d,1)d1,
c(d,2)d2,
c(d,3)d3,
c(d,4)d4,
c(d,5)d5,
c(d,6)d6
FROM temp

as you can see - now each of your 10000 lines will be like c(d,N)dN, with max in length as c(d,10000)d10000, so you have chance to fit into query size limit

Google BigQuery - Execute dynamically generated queries from a select statement

1 Answers