Using .js files to generate queries from a template

[brightbytes-dude]

I couldn't Google an obvious answer to this, but there probably is one. Apologies if so.

I want to generate a large number of bulk-load BQ Federation queries to run in parallel in a .js file.

I pull the CloudSQL info schema into a BQ dataform table like so:

ods_info_schema.sqlx

config {
  type: "table"  
}

SELECT * FROM EXTERNAL_QUERY(
  "<CloudSQL Connection here>", 
  """
   SELECT table_schema, 
          table_name, 
          ARRAY_TO_STRING(
            ARRAY_AGG(
              CASE 
              WHEN UPPER(data_type) IN ('MONEY', 'UUID', 'CITEXT', 'USER-DEFINED') 
              THEN 'CAST(' || column_name || ' AS VARCHAR) AS ' || column_name
              ELSE column_name
              END
            ),
            ', '
          ) AS columns
     FROM (SELECT table_schema, table_name, ordinal_position, column_name, data_type
             FROM information_schema.columns 
            WHERE table_schema IN (<the schemata whose contents I want to federate>)
              AND table_name NOT IN (<tables I don't want to federate>)
            ORDER BY 1, 2, 3) AS ods_info_schema
    GROUP BY 1, 2
  """
)

Now, I want to use the entire contents of that table as the array for a forEach in the following file:

federation.js

// How to get this from the DB rather than hardcoding?
const ods_info_schema_rows = [
    // This is one of the many, many rows that would come in, and verifies that this approach will work.
    ['amt_mappings', 'attendance_presence_mapped_values', 'id, CAST(value AS VARCHAR) AS value, core, created_at, updated_at']    
]

ods_info_schema_rows.forEach(ods_info_schema_row => {
    publish(ods_info_schema_row[1]).
        type("table").
        dependencies(["ods_info_schema"]).
        query(ctx => `
                 SELECT *
                   FROM EXTERNAL_QUERY(
                            "<CloudSQL Connection here>",
                            "SELECT ${ods_info_schema_row[2]} FROM ${ods_info_schema_row[0]}.${ods_info_schema_row[1]}"
                        )
        `);
});

My guess is that I probably need to use a JS library to pull in the table contents as an array, but I was hoping for a native way to do it in Dataform.

Thanks for any help you can provide!

[BenBirt]

Ah, so I think you are trying to do something at compile-time which you can only actually do at run-time.

Dataform code is "compiled" (really: "executed") to produce static SQL, i.e. all JavaScript is gone. Compilation is (by design) hermetic, i.e. your JavaScript cannot communicate with the outside world - including BigQuery. Later, the compiled SQL is then executed against BigQuery.

I think you want to use BigQuery scripting (https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language) for this. You'd have (probably) an operations SQLX file which used BigQuery scripting to generate the SQL you want to run, based on the contents of ods_info_schema.

Unfortunately I cannot see a way of making those run in parallel.

[brightbytes-dude]

Thanks - I'll look into BQ scripting. Though, hmm, it doesn't look like I get parallelism from that approach.

One thought: If I integrate with an external git repo, I could write a script that pulls the schema info from CloudSQL and then generates a bunch of .sqlx files, each with one of the federation queries. (Or, better, that calls an includes function with the varying params.) The source schema changes slowly, so this could be viable. Were I to do that, could I check in the .sqlx files to the external repo, and then see them in Dataform?

If not, I guess I could just write a script to generate the needed const in the above, and copy/paste into place.

In any case, thanks for your reply!!

[BenBirt]

If I understand you correctly - absolutely that would work! Dataform has full support for Git repos hosted on e.g. GitHub.

[brightbytes-dude]

Excellent: good enough to be getting on with. I can simply regenerate the .sqlx files any time the source schema changes.

(The actual question was really just: "If I make a change in an external git repo outside of Dataform, will that change become visible in Dataform?")

Thanks again!!