Document the fast SQL method of creating all UDFs required by BigQuery Migration Service for SQL Translation

[danieldeleo]

-- REPLACE VARIABLE BELOW WITH YOUR OWN PROJECT 
DECLARE YOUR_PROJECT_ID STRING DEFAULT("YOUR_OWN_PROJECT_ID_HERE");
-- REPLACE VARIABLE BELOW WITH YOUR BQ REGION 
DECLARE YOUR_REGION STRING DEFAULT("us-east4");
/**********************************
 * DO NOT EDIT SQL BELOW THIS LINE
 **********************************/
DECLARE region_suffix STRING DEFAULT(
  IF(YOUR_REGION="us", "", "_" || REPLACE(YOUR_REGION, "-", "_"))
);
-- Get regional UDFs
DECLARE cw_udf_ddls ARRAY<STRING>;
EXECUTE IMMEDIATE 
FORMAT("""
  SELECT ARRAY_AGG(ddl ORDER BY created) AS cw_udf_ddls
  FROM bqutil.fn%s.INFORMATION_SCHEMA.ROUTINES
  WHERE specific_name LIKE "cw_%%"
  """,
  region_suffix
)
INTO cw_udf_ddls;
-- Create the fn dataset with your project
EXECUTE IMMEDIATE "CREATE SCHEMA IF NOT EXISTS " || YOUR_PROJECT_ID || ".fn" || region_suffix;
-- Create all cw_* UDFs within your new fn dataset
FOR cw_udf_ddl IN (SELECT * FROM UNNEST(cw_udf_ddls) ddl)
DO EXECUTE IMMEDIATE REPLACE(cw_udf_ddl.ddl, "bqutil", YOUR_PROJECT_ID);
END FOR;