By using AWS re:Post, you agree to the Terms of Use

HIVE_METASTORE_ERROR: Error: type expected at the position 0 of 'String' but 'String' is found

0

This query fails

SELECT * FROM information_schema.columns

with

HIVE_METASTORE_ERROR: Error: type expected at the position 0 of 'String' but 'String' is found

How can we identify the table or column with the offending schema declaration so we may correct it (assuming there is some case sensitivity issue at hand)?

asked 15 days ago44 views
1 Answers
0

Hello,

I understand that you are fetching meta store data by running SELECT queries on information_schema database. As you know this database contains many meta-data table like the one "columns" you are querying and it contains data for all of the tables present in different databases present in Glue, In other words when you are querying over the "information_schema.columns" table you are fetching data about columns present in all of the different tables combined from all the databases present in your account for that region.

Coming to your error "HIVE_METASTORE_ERROR". We generally encounter this error when there is a issue with schema for your table. Some "HIVE_METASTORE_ERROR" error are descriptive and can be pin pointed by just looking at the error message. This error "type expected at the position 0 of 'String' but 'String' is found" when :

  1. The datatype(string) for a column is declared in uppercase or in CamelCase but not in lowercase.
  2. Due to improper serde property is used
  3. schema mismatch usually in cases of Parquet file format or a reserved keyword is used to name the columns.

As table information_schema.columns contains data from all of the tables thus by only looking at the error it will be a bit challenging to identify which table's schema is causing the issue.

Next/steps:

To pinpoint the table, first pinpoint the database where this table is present from which the error is coming up. In order to do that filter you SELECT query by where table_schema = <dbname> and perform these select query for all of the databases present and if the same error pops up you now know which database has that table/tables. now you can verify the tables under that database.

SUPPORT ENGINEER
answered 14 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions