Want to select data on redshift spectrum which was created at Athena?

steps to reconstruct:

1. create redshift cluster
2. login
3. create new external  schema called spectrum and reference to the original schema used in Athena: e.g tpch_data. notice XXXXX should be your account number.
4. query your data , using the new schema name.

create external schema spectrum
from data catalog
database ‘tpch_data’
iam_role ‘arn:aws:iam::XXXXX:role/mySpectrumRole’
create external database if not exists;
select count(*) from tpch_data.lineitem_parquet; — will not work
select count(*) from spectrum.lineitem_parquet; — will work.

——————————————————————————————————————————

I put a lot of thoughts into these blogs, so I could share the information in a clear and useful way. If you have any comments, thoughts, questions, or you need someone to consult with, feel free to contact me:

https://www.linkedin.com/in/omid-vahdaty/