Redshift Spectrum and Hive Metastore - Ambiguous Error

Question

From Redshift, I created an external schema using the Hive Metastore. I can see the Redshift metadata about the tables (such as using: select * from SVV_EXTERNAL_TABLES), however when querying one of these tables, I get an ambiguous error "error: Assert"

I tried creating the external schema and querying the tables. I can query the metadata about the tables, but cannot actually query the tables themselves.

I created the external schema as follows:

create external schema hive_schema
from hive metastore
database 'my_database_name'
uri 'my_ip_address' port 9083
iam_role 'arn:aws:iam::123456789:role/my_role_name';

Here is the error message when running "select * from hive_schema.my_table_name;"

  -----------------------------------------------
  error:  Assert
  code:      1000
  context:   loc->length() > 5 && loc->substr(0, 5) == "s3://" -
  query:     1764
  location:  scan_range_manager.cpp:221
  process:   padbmaster [pid=26902]
  -----------------------------------------------

i guess your hive meta is incorrect? does it work from elsewhere (hive/presto?) how did you create it? — Jon Scott
@JonScott - yes, I can query the tables successfully from hive. — Eli Reiman

botchniaque botchniaque · Accepted Answer · 2019-06-14T05:05:10

What is the LOCATION of your Hive table? Seems like Redshift is asserting the location to start with s3://.

You should see LOCATIONs of your tables by running that query:

select location from SVV_EXTERNAL_TABLES

Where are your Hive tables stored? Is it maybe HDFS? I doubt whether Redshift supports any other locations than S3 - in the section Considerations When Using AWS Glue Data Catalog of this AWS guide they describe how to setup your Hive Metastore to store data in S3.

Redshift Spectrum and Hive Metastore - Ambiguous Error

1 Answers