3

I'm getting an HIVE_PARTITION_SCHEMA_MISMATCH error that I'm not quite sure what to do about. When I look at the 2 different schemas, the only thing that's different is the order of the keys in one of my structs (created by a glue crawler). I really don't care about the order of the data, and I'm receiving the data as a JSON blob, so I cannot guarantee the order of the keys.

struct<device_id:string,user_id:string,payload:array<struct<channel:string,sensor_id:string,type:string,unit:string,value:double,name:string>>,topic:string,channel:string,client_id:string,hardware_id:string,timestamp:bigint,application_id:string>
struct<device_id:string,user_id:string,payload:array<struct<channel:string,name:string,sensor_id:string,type:string,unit:string,value:double>>,topic:string,channel:string,client_id:string,hardware_id:string,timestamp:bigint,application_id:string>
Matt Westlake
  • 3,499
  • 7
  • 39
  • 80
  • Possible duplicate of [How to create AWS Glue table where partitions have different columns? ('HIVE\_PARTITION\_SCHEMA\_MISMATCH')](https://stackoverflow.com/questions/46241088/how-to-create-aws-glue-table-where-partitions-have-different-columns-hive-par) – Theo Jun 01 '19 at 15:31
  • I think this is answered by https://stackoverflow.com/questions/46241088/how-to-create-aws-glue-table-where-partitions-have-different-columns-hive-par – Theo Jun 01 '19 at 15:31
  • @Theo no that doesn't solve the issue. The issue is that the struct is expected to be in the same order every time (even though all the keys are the same), and they're not. – Matt Westlake Jun 03 '19 at 18:03
  • I'm sure you did, but just to make sure: did you try checking the "Update all new and existing…" checkbox? I realise it's not the same question, but the solution sounds like it would work for your case too. The schemas that the crawler discovers are different, so that checkbox should make sure that old schemas are updated. – Theo Jun 04 '19 at 07:26

1 Answers1

8

I suggest you stop using Glue crawlers. It's probably not the response you had hoped for, but crawlers are really bad at their job. They can be useful sometimes as a way to get a schema from a random heap of data that someone else produced and that you don't want to spend time looking at to figure out what its schema is – but once you have a schema, and you know that new data will follow that schema, Glue crawlers are just in the way, and produce unnecessary problems like the one you have encountered.

What to do instead depends on how new data is added to S3.

If you are in control of the code that produces the data, you can add code that adds partitions after the data has been uploaded. The benefit of this solution is that partitions are added immediately after new data has been produced so tables are always up to date. However, it might tightly couple the data producing code with Glue (or Athena if you prefer to add partitions through SQL) in a way that is not desirable.

If it doesn't make sense to add the partitions from the code that produces the data, you can create a Lambda function that does it. You can either set it to run at a fixed time every day (if you know the location of the new data you don't have to wait until it exists, partitions can point to empty locations), or you can trigger it by S3 notifications (if there are multiple files you can either figure out a way to debounce the notifications through SQS or just create the partition over and over again, just swallow the error if the partition already exists).

You may also have heard of MSCK REPAIR TABLE …. It's better than Glue crawlers in some ways, but just as bad in other ways. It will only add new partitions, never change the schema, which is usually what you want, but it's extremely inefficient, and runs slower and slower the more files there are. Kind of like Glue crawlers.

Theo
  • 131,503
  • 21
  • 160
  • 205