HBase and Hive with Avro Column size limits

Yesterday, we had the privilege of having to create Hive tables on top of a HBase table with avro columns. Piece of cake, we thought, since you just have to specify the schema location in Hive (since Hive 1.0, it’s actually possible to map avro schema in HBase columns to Hive columns), map it to the column in HBase and voila, it works.

Except that it just doesn’t, at least if you have a large schema definition. In our specific case, we have a XSD Schema at the root – and like all things XML, it’s quite substantial. Converted to something decent like Avro, it is still quite big. That’s when we discovered that Hive has a limit for its Serializer of 4000 chars. That limit makes sense when you consider that Hive stores its metadata in a SQL database. So after googling a bit, we found this post:


which basically pointed to the SERDE_PARAMS table. And unsurprisingly, the limit there is VARCHAR(4000).

Following that advice, we updated the schema of the database using this:


That as such isn’t enough though, because Hive was still consistently failing. Since finding out the second cause was a slightly painful experience involving remote debugging Hive Server (thanks again, Roland), without further ado, here’s the second update command:


That’s also necessary, because after deserializing, Hive stores the extracted column in StorageDefinition (hence the SD in the table name), and that has the same limit.


This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s