因为你有空字符串所以当我们使用合并或 nvl仅当我们有空值在数据中。这些功能不适用于空字符串。
对于空字符串:
hive> select coalesce(string(""),"1");
+------+--+
| _c0 |
+------+--+
| |
+------+--+
hive> select nvl(string(""),"1");
+------+--+
| _c0 |
+------+--+
| |
+------+--+
对于空值:
hive> select coalesce(string(null),"1");
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
hive> select nvl(string(null),"1");
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
Try to 改变表格 https://stackoverflow.com/questions/43263038/empty-string-is-not-treated-as-null-in-hive并添加这个属性
TBLPROPERTIES('serialization.null.format'='')
如果此属性不将空字符串显示为 null,那么我们需要使用case/if
语句来替换空字符串。
您可以使用if statement https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-ConditionalFunctions
if(布尔测试条件, T 值True, T 值FalseOrNull)
hive> select if(length(trim(<col_name>))=0,'<replacement_val>',<col_name>) from <db>.<tb>;
Example:
hive> select if(length(trim(string("")))=0,'1',string("col_name"));
+------+--+
| _c0 |
+------+--+
| 1 |
+------+--+
hive> select if(length(trim(string("1")))=0,'1',string("col_name"));
+-----------+--+
| _c0 |
+-----------+--+
| col_name |
+-----------+--+