Querying Redshift external table: "Could not find parent table for alias"

Question

I am trying to select from an external table in Redshift and get this error.

QUERY:

SELECT * FROM "awsdatacatalog"."redshift-spectrum"."testaccess_txt";

ERROR:

Could not find parent table for alias "awsdatacatalog.redshift-spectrum.testaccess_txt". [ErrorId: 1-65dfbb91-28cfed766dc69ef05da3ebc8]

I have looked over permissions front to back and googled the error with no luck. I am new to Redshift and struggling with something I thought was easy.

Answer 1

awsdatacatalog points to glue data catalog (which I actually didn’t know till just now, I thought u always had to create an external schema first to reach glue from redshift). You created a database in glue called “redshift-spectrum” and a table in that database named “testaccess_txt”? I’m guessing this error means, “hey I can’t find the glue data catalog db that contains this table”. See this link for more info, I think you need to do the steps in it first, and even then you still need the glue table to exist. Usually with spectrum that’s done by creating a table in an external schema you’ve added with “create database if not exists”, which creates a glue db for you.

https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-glue.html

Even with all this you still need to make sure to attach a iam role to your warehouse that gives it correct spectrum access.