0

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]
  -----------------------------------------------
Eli Reiman
  • 164
  • 2
  • 11

1 Answers1

1

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.

botchniaque
  • 4,698
  • 3
  • 35
  • 63
  • Great observation. Yes, it was created as a HDFS based table. I changed this to be an S3 bucket, which got rid of the error listed above, however I am now getting a timeout, even though this is currently a tiny table -- only 1 row and 1 column. Any ideas why? ``` ----------------------------------------------- error: S3 Query Exception (Fetch), retries exceeded code: 15001 context: S3 request timed out. query: 11397 location: dory_util.cpp:913 process: query0_126_11397 [pid=12636] ----------------------------------------------- ``` – Eli Reiman Jun 14 '19 at 15:07
  • Can you query it with any other sql engine? Hive? Presto? – botchniaque Jun 14 '19 at 17:50
  • Since the data is in s3 you can create an external table over it using glue catalog (`CREATE EXTERNAL SCHEMA my_schema FROM DATA CATALOG ...` and then `CREATE EXTERNAL TABLE my_schema.my_table() ... `) - just to test whether there's a problem with this bucket. – botchniaque Jun 14 '19 at 17:54