CODEWITHSUNDEEP
amazon-redshiftamazon-redshift-spectrum
Gabe
Gabe

Reputation: 6065

Union with Redshift native tables and external tables (Spectrum)

If I have a view that contains a union between a native table and external table like so (pseudocode):

create view vwPageViews as 
   select from PageViews
   union all
   select from PageViewsHistory

PageViews has for the last 2 years. External table has for older data than 2 years.

ex.

Select from vwPageViews where MyDate >= '01/01/2021'

What's the best approach for querying both cold and historical data using RS and Spectrum? Thanks!

Upvotes: 0

Views: 823

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11032

How this will happen on Spectrum will depend on whether or not you have provided partitions for the data in S3. Without partitions (and a where clause on the partition) the Spectrum engines in S3 will have to read every file to determine if the needed data is in any of them. The cost of this will depend on the number and size of the files AND what format they are in. (CSV is more expensive than Parquet for example.)

The way around this is to partition the data in S3 and to have a WHERE clause on the partition value. This will exclude files from needing to be read when they don't match on the partition value.

The rub is in providing the WHERE clause for the partition as this will likely be less granular than the date or timestamp you using in your base data. For example if you partition on YearMonth (YYYYMM) and want to have a day level WHERE clause you will need to 2 parts to the WHERE clause - WHERE date_col >= 2015-07-12 AND part_col >= 201507. How to produce both WHERE conditions will depend on your solution around Redshift.

Upvotes: 1

Related Questions