sqlgoogle-bigquerygeolocationmaterialized-views

BigQuery Materialized view Left Join


I know that currently materialized views don't support LEFT JOINS, so my question is if there is a way to get the same result. I have a table with gps coordinates (mytable1) and I want to match with certain areas (polygon) created in table mytable3. mytable2 is only to add personalized information and not important for the question itself. My code above ... changing from LEFT to INNER works in Materialized view, but I loose the points outside the areas in mytable3. Any idea?

CREATE MATERIALIZED VIEW MY_TABLE
  PARTITION BY TIMESTAMP_TRUNC(date, DAY)
  CLUSTER BY node_address
  OPTIONS (enable_refresh = true)
AS (
  SELECT
    date,
    personName,
    personId,
    area,
  FROM
    MyTable1 AS Mytable1
  INNER JOIN
    MyTable2 AS Mytable2
  ON
    Mytable1.uuid = Mytable2.uuid
  LEFT JOIN
    Mytable2 AS Polygons
  ON
    ST_CONTAINS(Polygons.geoJSON, Mytable1.gps_coordinates)
  WHERE
    type = "person"
)


Solution

  • There are 2 ways that come to my mind in this question. When you have window functions or joins like you said in a MV, and you absolutely have to use a MV and cant just use a normal view, the only way is to change the MV to a non-incremental one, and set proper max-staleness etc.

    https://cloud.google.com/bigquery/docs/materialized-views-create#non-incremental

    1. Another way is to somehow use inner join without succumbing to a left join. You can try this:
    CREATE MATERIALIZED VIEW MY_TABLE
      PARTITION BY TIMESTAMP_TRUNC(date, DAY)
      CLUSTER BY node_address
      OPTIONS (enable_refresh = true)
    AS (
      SELECT
        date,
        personName,
        personId,
        area
      FROM
        MyTable1 AS Mytable1
      INNER JOIN
        MyTable2 AS Mytable2
      ON
        Mytable1.uuid = Mytable2.uuid
      INNER JOIN
        Mytable3 AS Polygons
      ON
        ST_CONTAINS(Polygons.geoJSON, Mytable1.gps_coordinates)
      WHERE
        type = "person"
      
      UNION
      
      SELECT
        date,
        personName,
        personId,
        area
      FROM
        MyTable1 AS Mytable1
      INNER JOIN
        MyTable2 AS Mytable2
      ON
        Mytable1.uuid = Mytable2.uuid
      WHERE
        type = "person"
        AND NOT EXISTS (
          SELECT 1
          FROM Mytable3 AS Polygons
          WHERE ST_CONTAINS(Polygons.geoJSON, Mytable1.gps_coordinates)
        )
    );
    

    the first part performs the INNER JOIN with the polygons and includes only the points that are within the defined areas. The second part retrieves the points that are not within any area by using the NOT EXISTS clause.