udfs and unnesting.sql

 DECLARE
  SQLRUN STRING DEFAULT '';
CREATE TEMP FUNCTION
  ga4_firebase( key1 STRING,
    params ARRAY <STRUCT <key STRING,
    value STRUCT <string_value STRING,
    int_value INT64,
    float_value FLOAT64,
    double_value FLOAT64 >>>) AS ( (
    SELECT
      param.value
    FROM
      UNNEST(params) param
    WHERE
      param.key=key1) );
  # *****************************************************************************
  # Set the tables names and define row numbers and update times
  # *****************************************************************************
SET
  SQLRUN = 
  (
  SELECT
    CONCAT('SELECT EVENT_NAME , ', STRING_AGG(CONCAT("ga4_firebase('",key,"',    event_params).", event_parameter_value, " AS ", key, ',' ), '\n'), '  FROM  `project.analytics_yyyymmdd.events_*` WHERE _TABLE_SUFFIX = "YYYYMMDD"  ORDER BY 1' )
  FROM (
    SELECT
      DISTINCT event_name,
      params.key AS key,
      (CASE
          WHEN params.value.string_value IS NOT NULL THEN 'string_value'
          WHEN params.value.int_value IS NOT NULL THEN 'int_value'
          WHEN params.value.double_value IS NOT NULL THEN 'double_value'
          WHEN params.value.float_value IS NOT NULL THEN 'float_value'
      END
        ) AS event_parameter_value,
    FROM
      `project.analytics_yyyymmdd.events_*`,
      UNNEST(event_params) AS params
    WHERE
      _table_suffix BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY))
      AND FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) )
    #WHERE EVENT_NAME = ''
    );
  # *****************************************************************************
  # Execute the querry
  # *****************************************************************************
EXECUTE IMMEDIATE
  (SQLRUN);