Generating a View From Dynamic Query Bigquery

We are generating a dynamic query using the script below :

execute immediate (select
  ''' select event_id, value, ''' || string_agg('''
    (select value from b.key_value where key = "''' || key_name || '''") as ''' || key_name , ''', ''')
  || '''
  from (
    select event_id, value,
      array(
        select as struct 
          json_extract_scalar(kv, '$.key') key, 
          json_extract_scalar(kv, '$.value') value
        from a.kvs kv
      ) key_value
    from `project.dataset.table`,
    unnest([struct(json_extract_array(dimensions, '$.key_value') as kvs)]) a
  ) b
  '''
  from (
    select distinct json_extract_scalar(kv, '$.key') key_name
    from `project.dataset.table`,
    unnest(json_extract_array(dimensions, '$.key_value')) as kv
  )
)  

We want to create a view out of this is there any way in BQ by which we can create a view from this, so as user we can directly query our view.



Read more here: https://stackoverflow.com/questions/67006642/generating-a-view-from-dynamic-query-bigquery

Content Attribution

This content was originally published by Bad Coder at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: