search

llooker / google_ga360

Looker Blocks for Google Analytics 360
MIT License
39 stars 51 forks source link

Improve BQ performance by changing 'LEFT JOIN' by ', UNNEST()' #24

Open kokevidaurre opened 5 years ago

kokevidaurre commented 5 years ago

Hi, As suggest, you can get better performance on large dataset using UNNEST instead LEFT JOIN when joining subrecords of the BQ schema.

Also is necessary to add hit id to all that subrecords tables to keep consistency on counts.

ernestoongaro commented 4 years ago

Hi @kokevidaurre - do you have any examples/documentation of where UNNEST is faster than LEFT JOIN UNNEST?

kokevidaurre commented 4 years ago

Hi @ernestoongaro. No, just the official BigQuery documentation, but also experience within big datasets. If you see the BigQuery Export Schema, you may find that is just a single table and there's no need to do a LEFT JOIN with it self.