"_col0" is an internal/generated column. If you run a "select count(*) from foo" hive does not have to read any columns from the table, it just needs to count the records. What the explain plan says is that the table scan and select operator first generate empty records from the table. Then the group by counts those and stores that in a generated column "_col0". (There is a second group by because Hive has to aggregate all the results from the different "mappers".) If you just count all rows it's odd to run out of memory. What's the actual query and failure you're seeing? ... View more

Check the explain plan of both. I believe the distinct is re-written to a group-by by the planner. ... View more

This: insert into <tname> partition pcol = "01/02/.." select <cols> from <tname> where pcol = "01/01/2017" You could also copy the partition folder and use "alter table add partition" the data files don't contain the partition column. ... View more

I see. This is going to be inefficient, because Hive can't shuffle the data based on keys. You can do it like this probably: select ... from table_1, table_2 where fuzzy_match(column_x, column_y) (inner join branch) union all select ... from table_1 where not exists (select 1 from table_2 where fuzzy_match(column_x, column_y)) s1; (left outer branch) With this: https://issues.apache.org/jira/browse/HIVE-14731 you will at least not have single node cross products, but it will still be an expensive operation. ... View more

typical pattern is: select * from table_1 left join table_2 on (lcase(column_x) = lcase(column_y)) can you produce a canonical format on both sides (like lcase above) and join on that? ... View more