Hive Bad Data - error in athena
I am creating feature groups using sagemaker feature store service. So once a feature group is created, a awsDataCatalog (table) will be automatically created in athena console. While doing this process, I am getting the following error
- Newest
- Most votes
- Most comments
The table created in Athena is configuring field ncustomerid to use bigint which is not effectively convertible to a int based type as bigint. What's the data type of ncustomerid in schema definition of the feature group ? Are you using ncustomerid as record identifier ?
If the customerid is 0 on any of the parquet file processed earlier(using which the table definition is created in glue catalog by crawler) had only '0' for this field on all the rows then it is possible for glue crawler to have assigned 'bigint' for that field in table definition but the later files which has 'double' data will have data type as 'double' on only its partition in the catalog. you can write a python code to iterate on all the files in the bucket/prefix and find out dtype of that column in each file and spit out only the file name(s) that has 'bigint' as dtype. once you the files which has this problem, address the data issue by converting data to double fomat or drop the file . After the fix, drop the Athena table and run crawler again.
Relevant content
- Accepted Answerasked 2 years ago
- asked 2 years ago
- Accepted Answerasked a year ago
- Accepted Answerasked 2 years ago
AWS OFFICIALUpdated a year ago- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 3 years ago
It's a double type. I don't know why athena is automatically taking it as bigint and Its not the record identifier. Its another ID column and this customerID , I am having it for merging purposes.