sqlxdataform

loop through an array and execute union all query with Dataform


I'm working with Dataform plugged to BigQuery.

I have the following script:

function render_script(table, dimensions, date) {
  return `
      select
      '${dimensions.map(field => `${field} is null`)}' as failing_row_condition,
      *
      from ${table}
      where 
        ${date} >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 day) 
      and ${dimensions.map(field => `${field}`)} IS NULL
    `;
}

module.exports = { render_script };

and the following statement in my sqlx file:

${script_builder.render_script(ref("table"),
                               ["dim1"],
                               "date"
                               )}  

I'm not sure I'm taking the right approach but I'd like to be able to loop through a list of dimensions to execute my render_script and "glue" all this with UNION ALL.

Basically right now I have this:

${script_builder.render_script(ref("table"),
                               ["dim1"],
                               "date"
                               )}

UNION ALL

${script_builder.render_script(ref("table"),
                               ["dim2"],
                               "date"
                               )}

How can I programmatically re-write this? within the sqlx file if possible.


Solution

  • Javascript block can be created within SQLX file.

    This is documented here. Here is an example:

    js {
    const tables = [
    'table1',
    'table2',
    'table3'
    ];
    
    const selectAllFromTables = tables.map(t => {
        return `select * from \`${t}\``;
    }).join(`
    union all
    `);
    }
    
    ${selectAllFromTables}