Aws-data-wrangler: HIVE_METASTORE_ERROR: com.facebook.presto.spi.PrestoException: Error: type expected at the position 0 of 'integer' but 'integer' is found

[qemtek]

Hi guys, im getting the following error while trying to query using aws.athena.read_sql_query:

Traceback (most recent call last):
File "/Users/chriscollins/Documents/GitHub/FinishTimePredict/venv/lib/python3.7/site-packages/IPython/core/interactiveshell.py", line 3331, in run_code
exec(code_obj, self.user_global_ns, self.user_ns)
File "", line 3, in
database=AWS_DB_NAME).drop_duplicates()
File "/Users/chriscollins/Documents/GitHub/FinishTimePredict/venv/lib/python3.7/site-packages/awswrangler/athena.py", line 515, in read_sql_query
query_response: Dict[str, Any] = wait_query(query_execution_id=query_id, boto3_session=session)
File "/Users/chriscollins/Documents/GitHub/FinishTimePredict/venv/lib/python3.7/site-packages/awswrangler/athena.py", line 228, in wait_query
raise exceptions.QueryFailed(response["QueryExecution"]["Status"].get("StateChangeReason"))
awswrangler.exceptions.QueryFailed: HIVE_METASTORE_ERROR: com.facebook.presto.spi.PrestoException: Error: type expected at the position 0 of 'integer' but 'integer' is found. (Service: null; Status Code: 0; Error Code: null; Request ID: null). You may need to manually clean the data at location 's3://aws-*-results-249959970268-eu-west-1/tables/805b23**e-311b3e1b537e' before retrying. Athena will not delete data in your account.

It doesn't seem to be directly related to awswrangler but I did create the database and tables with awswrangler too. From what I've been able to read online it could be due to some kind of schema mismatch. When I created the table I specified the schema with the following dict:

SCHEMA_COLUMNS = {
     'date': 'timestamp',
     'course': 'string',
     'off': 'string',
     'name': 'string',
     'type': 'string',
     'class': 'string',
     'pattern': 'string',
     'rating_band': 'string',
     'age_band': 'string',
     'sex_rest': 'string',
     'dist': 'string',
     'dist_y': 'integer',
     'dist_m': 'integer',
     'dist_f': 'string',
     'going': 'string',
     'num': 'integer',
     'pos': 'string',
     'ran': 'integer',
     'draw': 'integer',
     'btn': 'double',
     'ovr_btn': 'double',
     'horse': 'string',
     'sp': 'string',
     'dec': 'double',
     'age': 'integer',
     'sex': 'string',
     'wgt': 'string',
     'lbs': 'integer',
     'hg': 'string',
     'time': 'double',
     'jockey': 'string',
     'trainer': 'string',
     'or': 'double',
     'rpr': 'double',
     'ts': 'double',
     'prize': 'string',
     'sire': 'string',
     'dam': 'string',
     'damsire': 'string',
     'owner': 'string',
     'comment': 'string',
     'country': 'string',
     'id': 'integer',
     'horse_cleaned': 'string',
     'jockey_clened': 'string',
     'trainer_cleaned': 'string',
     'year': 'integer'
 }

This solution was working before. The only difference I have made is partitioning by year, where previously there was no partition. I notice in the schema list in AWS Glue, year is now the first column, instead of the last as defined here. Do you think that could be the issue?

[qemtek]

Ah, fixed it! integer is not a valid data type, it has to be int. I will leave this here in case anyone has a similar issue

[igorborgest]

Thanks @qemtek, this thread could help others in the future.

In addition, the data types supported are here in the service documentation.

In this "integer family" the options are:

• TINYINT (8 bits)
• SMALLINT (16 bits)
• INT (32 bits)
• BIGINT (64 bits)