Redshift serverless: error while trying to create an external table

Question

The error:

```
> SQL Error [500310] [XX000]: [Amazon](500310) Invalid operation: Unknown std exception when calling external catalog API: 
  -----------------------------------------------
  error:  The requested role arn:aws:iam::*******:role/service-role/AWSGlueServiceRole-analytics is not associated to cluster
  code:      30000
  context:   
  query:     0
  location:  xen_aws_credentials_mgr.cpp:403
  process:   padbmaster [pid=16004]
  -----------------------------------------------
;
```

I was able to create a schema prior to creating external table, with no errors:

```
create external schema rsqa
from data catalog
database 'actions'
region 'us-west-2' 
iam_role 'arn:aws:iam::**********:role/service-role/AWSGlueServiceRole-analytics';
```

But after that, this is the sql that produces the error above:

```
create external table dev.rsqa.user_actions(
  event_id varchar(256),
  api_key varchar(32),
  event_type varchar(32),
  action_id INT8,
  action_name varchar(128),
  action_data_id INT8,
  action_time INT4,
  create_time INT4,
  metadata varchar(256),
  user_id INT8,
  username varchar(256),
  user_first_name varchar(128),
  user_last_name varchar(128),
  value FLOAT4,
  passive BOOL
)
PARTITIONED BY (
	ics varchar(32),
	site_id varchar(32),
	year varchar(32),
	month varchar(32),
	week varchar(32),
	dt varchar(32)
)
stored as parquet 
location 's3://my-datalake-qa/actions/user_actions/';
```

does redshift serverless support external tables?

or could this be due to another setting I am missing?

Answer

Update:

I tried this external table setup in a regular redshift cluster (not a serverless one), and it works, after I do associate the role used to the cluster.
For redshift serverless though, I do not see any clusters to attach the role to.

So even if the error says: "The requested role ... is not associated to cluster", there is no "cluster" to associate the role to, when running a serverless instance.

To corroborate that, I run:

aws redshift describe-clusters

And I got an empty response:

{
    "Clusters": []
}

So I guess the questions is: is it possible (given the error), to associate a role to a clusterless redshift, as in when it runs serverless?

or:

Has anybody been able to create an external table in a redshift serverless instance? is that not supported?

Answer

Hi Ramiro,

Have you already added the IAM role to the Redshift Serverless namespace? If not, add it and then try to re-execute your command.

Go to the Redshift Serverless console => Namespace configuration => Click on the appropriate namespace => Security and Encryption tab => Manage IAM Roles => Associate the IAM Role => Save changes.

Thanks.

Redshift serverless: error while trying to create an external table

Relevanter Inhalt