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

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.