title: Left join workaround when creating materialized view in Bigquery
body: <p>I have two tables under dataset: <code>my_dataset</code> in BigQuery</p>
  <ol>
  <li>events table</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/oqIQc.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oqIQc.png" alt="enter image description here" /></a></p>
  <ol start="2">
  <li>event_attributes table</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/TL6Ri.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TL6Ri.png" alt="enter image description here" /></a></p>
  <p>Data under these tables is populated via Dataflow job running in GCP.</p>
  <p>In <code>event_attributes</code> table there are 4 distinct values of attribute_number: [1,2,3,4].</p>
  <p>For a given <code>events_fk</code> value, there can maximum 4 rows in <code>event_attributes</code> table i.e. value of all_count in below query can be max 4.</p>
  <pre><code>select events_fk, count(*) as all_count from events_attributes group by events_fk order by all_count limit 1
  </code></pre>
  <p>Now, I want to <strong>create a materialized view, which has</strong> <em>count of all events, count of completed events (from events table) <strong>grouped by</strong> time, app, completed, attribute_value(from event_attributes table)</em>. For this, I'm trying to use below query</p>
  <pre><code>create materialized view my_dataset.test_mv as (
    select 
    extract(DATE FROM timestamp_seconds(epoch_seconds_utc)) as date,
    extract(HOUR FROM timestamp_seconds(epoch_seconds_utc)) as hour,
    app,
    completed,
    attribute_value,
    count(*) as all_count,
    sum(cast(completed as tinyint)) as completed_count
    from `my_dataset.events` c
    left join unnest(
      (
        select array_agg( struct(id, events_fk, attribute_number, attribute_value) ) as eattr
        from `my_dataset.event_attributes`
      )
    ) a
    on c.id = a.events_fk
    group by date, hour, app, completed, attribute_value
  )
  </code></pre>
  <p>Since, left join is not supported in creating materialized view as of now, I was trying to use unnest() and provide an array arguments inside that as a work around.
  But, bigquery complains about this with below error</p>
  <p><a href="https://i.stack.imgur.com/HNp8o.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HNp8o.png" alt="enter image description here" /></a></p>
  <p>I'm guessing it's because of the subquery that I'm using under unnest().</p>
  <p><strong>Reason for trying to create materialized view:</strong> I want to use the created materialized view for constructing different reports.</p>
  <p>My requirements are-</p>
  <ol>
  <li>Have to combine the data in two different table and use resulting table to create materialized view.</li>
  <li>Have all rows of events table considered for the final materialized view calculation(which can't be done if I use inner join).</li>
  </ol>
  <p>I've also thought of having combined data of these two tables in the dataflow stage itself. But that will be a long route as of now. Since, I will have to think about the changes in dataflow job.</p>
  <p><strong>Question:</strong> Need help with the create materialized view query. If query is not possible what should be the best course of action in such situation?
  Any suggestions will be very helpful.</p>
  <p>Thanks in advance!!</p>
  <p><strong>Edit:</strong> Sample data can be constructed via below queries</p>
  <pre><code>create table my_dataset.events (
    id INTEGER,
    epoch_seconds_utc INTEGER,
    app STRING,
    engaged BOOLEAN,
    completed BOOLEAN
  );
  
  create table my_dataset.event_attributes (
    id INTEGER,
    events_fk INTEGER,
    attribute_number STRING,
    attribute_value STRING
  )
  
  
  -- POPULATE EVENTS DATA
  insert into `my_dataset.events` values (435515, 1660807131, 'airtel', FALSE, FALSE);
  insert into `my_dataset.events` values (435517, 1660807514, 'vodafone', FALSE, FALSE);
  insert into `my_dataset.events` values (285111, 1607927586, 'telstra', FALSE, FALSE);
  insert into `my_dataset.events` values (435051, 1649137598, 'healthways', FALSE, FALSE);
  insert into `my_dataset.events` values (435017, 1649068151, 'tangoe', FALSE, FALSE);
  insert into `my_dataset.events` values (435519, 1660821334, 'airtel', FALSE, FALSE);
  insert into `my_dataset.events` values (703, 1589840471,    'vodafone', FALSE, FALSE);
  insert into `my_dataset.events` values (1619, 1591014513, 'telstra', FALSE, FALSE);
  insert into `my_dataset.events` values (783, 1589840537, 'tangoe', FALSE, FALSE);
  insert into `my_dataset.events` values (20245, 1596825490, 'healthways', FALSE, FALSE);
  
  
  -- POPULATE EVENT ATTRIBUTES DATA
  insert into `my_dataset.event_attributes` values (6341, 435017, '1',    'In Progress');
  insert into `my_dataset.event_attributes` values (6381, 435051, '1',    'In Progress');
  insert into `my_dataset.event_attributes` values (5429, 285111, '1',    '9191919191');
  insert into `my_dataset.event_attributes` values (5431, 285111, '2',    'Direct Connection');
  insert into `my_dataset.event_attributes` values (5433, 285111, '3',    'English');
  insert into `my_dataset.event_attributes` values (5435, 285111, '4',    'Covid');
  insert into `my_dataset.event_attributes` values (7161, 435517, '3',    'English');
  insert into `my_dataset.event_attributes` values (7159, 435517, '2',    'Some value');
  insert into `my_dataset.event_attributes` values (7151, 435515, '2', 'Some value');
  insert into `my_dataset.event_attributes` values (7163, 435517, '4',    'Default');
  insert into `my_dataset.event_attributes` values (7157, 435517, '1',    '50000');
  insert into `my_dataset.event_attributes` values (7149, 435515, '1',    '50000');
  insert into `my_dataset.event_attributes` values (7153, 435515, '3',    'English');
  insert into `my_dataset.event_attributes` values (7155, 435515, '4',    'Default');
  </code></pre>
  <p>In this if you perform an inner join using query like</p>
  <pre><code>select e.id, ea.attribute_number, ea.attribute_value 
  </code></pre>
  <p>from my_dataset.events e left join my_dataset.event_attributes ea
  on e.id=ea.events_fk</p>
  <p>I get around 14 rows, and if I use left join in above query, I get around 19 rows because there are records in events table which don't have corresponding entry in event_attributes.</p>
  <p><strong>Desired output:</strong> want to get all rows of events table considered to create materialized view.</p>
  <p>Let me know if any information is needed from my end.</p>