0

I am using the Redshift Cluster in us-west-1 (NCAL)

s3 file location is in us-west-1 (NCAL) Glue data catalog is in us-west-2 (Oregon)

When I try to query the table

select count(*) from spectrum_schema.table_name;

I get the error below.

[Code: 500310, SQL State: XX000]  [Amazon](500310) Invalid operation: The 
specified region 'us-west-1' is invalid.
Details: 
-----------------------------------------------
  error:  The specified region 'us-west-1' is invalid.
  code:      8001
  context:   The valid regions are us-east-1,us-east-2,us-west-2.
  query:     169676
  location:  region_endpoints.cpp:54
  process:   padbmaster [pid=4487]
-----------------------------------------------

The table definition did get created in the Glue catalog and I can also see it being defined in svv_external_tables in Redshift. Just that I cannot query it.

How can this be fixed? Can anyone help?

Thanks and regards, Kunal Ghosh

DJo
  • 2,133
  • 4
  • 30
  • 46
Kunal
  • 294
  • 4
  • 15

2 Answers2

0

This might be because the Redshift Spectrum service is not available in the us-west-1 region. As of November 2017 the only US regions it was available in were:

  • us-east-1 (N. Virginia)
  • us-east-2 (Ohio)
  • us-west-2 (Oregon)

You can try moving your S3 files to one of the above regions.

Nathan Griffiths
  • 12,277
  • 2
  • 34
  • 51
  • It is available as of December https://aws.amazon.com/about-aws/whats-new/2017/12/amazon-redshift-spectrum-launches-in-three-additional-aws-regions/ – Kunal Jan 12 '18 at 19:24
  • It is available as of December https://aws.amazon.com/about-aws/whats-new/2017/12/amazon-redshift-spectrum-launches-in-three-additional-aws-regions/ My experiment was in January, 2018. Strangely I can create object definitions in Glue using the Spectrum feature as mentioned above. Looks more of a bug to me. – Kunal Jan 12 '18 at 20:06
  • AWS Support acknowledged that they are facing some issues with spectrum for "us-west-1" region. They're going to fix this ASAP. – Kunal Jan 18 '18 at 00:05
0

AWS Support patched a bug for Redshift Spectrum in us-west-1 to resolve this finally. I had to make some deliberate DB parameter changes in the Cluster and re-boot the existing cluster to implement the changes. Redshift and Glue Catalog in different region works fine provided that the Redshift Cluster and the S3 bucket are in the same region.

You can follow the documentation here https://docs.aws.amazon.com/redshift/latest/dg/c-using-spectrum.html

Kunal
  • 294
  • 4
  • 15