View with ARRAY_AGG + OffSet not using the benefits of date partition on BQ table

Harikrishnan Balachandran 0
Viewed 40

I have a date(createDt) partitioned BQ table that is being loaded by kafka stream data. I want to select only the latest record based on updatedTime grouped by ID column. I am able to achieve it through the 2nd top solution referred here. I want to create it as a view and did it by the following code

create or replace view myView as 
SELECT row.* FROM (
  SELECT ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)] AS row
  FROM `yourTable` AS t
  GROUP BY ID
)

I noticed that if I query myView using the partitioned date column createDtin where condition, the entire base table is being scanned which I don't want to happen.

However if I a create a normal view like

  create or replace view myNormalView as 
    SELECT * FROM  `yourTable`

and then query the using createDt, only the required partition is being scanned and very less bytes are read/billed.

My question - How do I make partition work on top of ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)].

I am still a beginner in BigQuery. Any suggestion/solution is highly appreciated

google-cloud-platform google-bigquery sql
1 Answers

Turns out that if you apply ARRAY_AGG(t ORDER BY updatedTime DESC LIMIT 1)[OFFSET(0)] to all the columns individually and select them to define the view, the partitioned column works! Also I am lucky since the partitioned column createDt wouldn't change once the ID is created. So I included it in the group by. Below is the solution,

Create view myView
SELECT ID,createDT, 
  ARRAY_AGG(col1 ORDER BY updatedTime DESC)[OFFSET(0)] AS col1,
  ARRAY_AGG(col2 ORDER BY updatedTime DESC)[OFFSET(0)] AS col2
FROM `yourTable`
GROUP BY ID,createDT

Now if I query my view with createDT as filter, only the intended partition is being read. I have tested it with large data and it works

NOTE: I got curious if adding the partitioned column createDT in group by alone would solve the problem and tried the same in .* query posted in query. But it still doesn't work. I guess it essential to apply ARRAY_AGG to each column. Sounds silly but that's how it is :)

Harikrishnan Balachandran0
Related