hive null and empty string--- 系列（四）-- ORCFile

目的：预将hive中 null 与 empty string 统一，便捷后续开发

问题：orc 文件 使用  SET SERDEPROPERTIES('serialization.null.format' = '') 失效

解决方案：探索 null 再各个file_format 的存储方式

ORCFile
（一）原始状态

create table test.orcfile (id int ,name string)   STORED AS orc;


show create table test.orcfile;

CREATE TABLE `test.orcfile`(
  `id` int, 
  `name` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://devCluster/user/hive/warehouse/test.db/orcfile'
TBLPROPERTIES (
  'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 
  'numFiles'='0', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1623402600')

插入数据，查看 null and empty string 存储形式 

insert overwrite table test.orcfile values ('1','ddd');
insert into table test.orcfile values (2,'');
insert into table test.orcfile values (3,null);
insert into table test.orcfile values (4,'\N');
insert into table test.orcfile values (5,'\\N');
insert into table test.orcfile values ('qwe','ppp');
insert into table test.orcfile values ('sdd','ooo');

SELECT
*
from 
test.orcfile
;

 

发现，id 的数据类型是 int ,插入数值的string类型成功，但是插入字符串后其值变成null。但是 插入“\\N” 仍被标记成null了。

（二）使用参数

使用  SET SERDEPROPERTIES('serialization.null.format' = '')参数 

create table test.orcfile_par (id int ,name string)   STORED AS orc;

alter table test.orcfile_par SET SERDEPROPERTIES('serialization.null.format' = '');

show create table test.orcfile_par;

CREATE TABLE `test.orcfile_par`(
  `id` int, 
  `name` string)
ROW FORMAT DELIMITED 
  NULL DEFINED AS '' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://devCluster/user/hive/warehouse/test.db/orcfile_par'
TBLPROPERTIES (
  'last_modified_by'='qll', 
  'last_modified_time'='1623402986', 
  'numFiles'='0', 
  'numRows'='0', 
  'rawDataSize'='0', 
  'totalSize'='0', 
  'transient_lastDdlTime'='1623402986')

插入数据，查看 null and empty string 存储形式

insert overwrite table test.orcfile_par values ('1','ddd');
insert into table test.orcfile_par values (2,'');
insert into table test.orcfile_par values (3,null);
insert into table test.orcfile_par values (4,'\N');
insert into table test.orcfile_par values (5,'\\N');
insert into table test.orcfile_par values ('qwe','ppp');
insert into table test.orcfile_par values ('sdd','ooo');

SELECT
*
from 
test.orcfile_par
;

发现，id 的数据类型是 int ,插入数值的string类型成功，但是插入字符串后其值变成null。空值并未和 empty string 统一，即SET SERDEPROPERTIES('serialization.null.format' = '') 并未起作用。