Hive causes a lot more pain than it should. My favorite examples are when it tells you 'Table not found'. Usually that means Hive isn't being very smart.
Try to create a table then take 10 rows from it:
CREATE TABLE listed2938.TABLE2 as SELECT * FROM listed2938.TABLE1; SELECT * FROM listed2988.TABLE2 LIMIT 10;
...Table not found 'TABLE2'.
Then you spend forever at the command line trying to figure out why it can't find TABLE2, even though you are sure TABLE2 exists. After 5-10 minutes you realize the error should have been Database not found 'listed2988'. Or Table not found 'listed2988.TABLE2'. If it can't find the database, why is it explaining that it can't find the table?
That one is just stupid. This next one is a bug that really irritates me.
You can't use [IF NOT EXISTS] if you are trying to overwrite a partition without first using the database. That sentence probably doesn't make sense because it seems so ridiculous for that to be that case.
-- THIS WILL NOT WORK INSERT OVERWRITE TABLE listed2938.TABLE1 IF NOT EXISTS PARTITION (date='TODAY') SELECT * FROM listed2938.TABLE2; -- THIS WILL WORK USE listed2938; INSERT OVERWRITE TABLE TABLE1 IF NOT EXISTS PARTITION (date='TODAY') SELECT * FROM listed2938.TABLE2;
The first query gives Table not found 'TABLE1'. I do not even know what the problem its having is. Maybe it's trying to use the default database?
Maybe this will be a part one of one-thousand series on how irritating hive can be. I've got all sorts of problems.