I don't think there is any such configuration. You can use CASE statement to achieve this e.g. SELECT CASE WHEN col is NULL THEN '' ELSE col END from table;
... View more
You can use something like following to find the maximum number of characters being used by the column. This will give you the maximum length (number of UTF-8 characters) for the column. select char_length(column_in_question) as len from tvar order by len desc limit 1;
... View more
This is a bug (confirmed that this exist on upstream/apache master as well). Please go ahead and open a bug on apache hive (Or let me know and I can do that)
... View more
Try set hive.compute.query.using.stats=false and see if you get correct count using select count(*). If this works that means you have stale statistics and you need to re-run analyze table on all partitions.
... View more
This looks like BUG (regression). I am able to observe/reproduce the same behavior on latest hive master. Though I haven't confirmed that it was working in previous version. Feel free to open a JIRA to report this. EDIT: Digged more into the code and found a workaround for this. Use set hive.mv.files.thread=0. This will disable parallel load of directories and LOAD should be able to load all directories by renaming them. This is definitely a bug which needs to be fixed. Please go ahead with the JIRA report if you can. Otherwise let me know and I'll file one.
... View more
I don't think there is a way to do it at once for all partitions, best you could is to specify multiple partitions like ALTER TABLE tableName PARTITION(dt=20180109, dt=20180110..) CONCATENATE. Please note that there are known issues with ALTER TABLE CONCATENATE in versions earlier than HDP 2.6 and it is not recommended to run CONCATENATE.
... View more
These numbers (Num rows, Data size) are estimated by Hive (optimizer) and do not represent actual numbers. You can run EXPLAIN + ANALYZE to see both Estimated and Actual numbers.
... View more
You can also achieve this by using following query: SELECT date_column,
count(*)-count(c1) as null_c1,
count(*)-count(c2) as null_c2,
count(*)-count(c3) as null_c3,
count(*)-count(c4) as null_c4
FROM t1
GROUP BY date_column;
... View more
(NULL=NULL) is neither true nor false it will evaluate to NULL/UNKNOWN. (reference https://www.simple-talk.com/sql/learn-sql-server/sql-and-the-snare-of-three-valued-logic/) As pointed by @Murali Ramasami above you should use <=> operator if you would like null=null evaluate to true.
... View more
