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"
)
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
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.