Unable to access AWS Athena table from AWS Redshift

Question

I am trying to access an existing AWS Athena table fron AWS Redshift.

I tried creating external schema (pointing to AWS Athena DB) in AWS Redshift console. It creates the external schema successfully but it doesn't display tables from Athena DB. Below is the code used.

CREATE EXTERNAL SCHEMA Ext_schema_1
FROM DATA CATALOG
DATABASE 'sample_poc'
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::55276673986:role/sample_Redshift_Role';

Few observations..

1. Even if I specify not existing Athena DB name, it still create external schema in Redshift.
2. My Redshift role has full access to S3 & Athena.

Answer 1

AWS Glue Catalog contains databases, which contain tables. There are no schemas from the perspective of Athena or Glue Catalog.

In Redshift Spectrum, you create an EXTERNAL SCHEMA which is really a placeholder object, a pointer within Redshift to the Glue Catalog.

1. Even if I specify not existing Athena DB name, it still create external schema in Redshift.

The creation of the object is lazy as you have discovered, which is useful if the IAM Role needs adjusting. Note the example in the docs has an additional clause:

create external database if not exists

So your full statement would need to be this if you wanted the database to be created as well.

CREATE EXTERNAL SCHEMA Ext_schema_1 
FROM DATA CATALOG
DATABASE 'sample_poc'
REGION 'us-east-1'
IAM_ROLE 'arn:aws:iam::55276673986:role/sample_Redshift_Role'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

it doesn't display tables from Athena DB

If you are creating an EXTERNAL SCHEMA to a non-existent database then there will be nothing to display. I assume your point 1. is unrelated to the real attempt you made to create the external schema; that you pointed it to an existing schema with tables.

I have found that tables created using Redshift Spectrum DDL are immediately available to Athena via the Glue Catalog. I have also tried specifying tables in Glue Catalog, and alternatively using the Crawler, and in both cases those tables are visible in Redshift.

What tool are you using to attempt to display the tables? Do you mean tables don't list in metadata views or do you mean the contents of tables doesn't display?

Redshift does appear to have some differences in the datatypes that are allowed, and the Hive DDL required in Athena can have some differences to the Redshift Spectrum DDL. Spectrum has some nesting limitations.

2. My Redshift role has full access to S3 & Athena

Assuming you are using Glue Catalog and not the old Athena catalog then your role doesn't need any Athena access.