精华内容
下载资源
问答
  • 温馨提示:如果使用电脑查看图片不清晰,可以使用手机打开文章...使用Hive时大家都会遇到数据类型校验的问题,相比传统关系型数据库会严格要求数据的Schema,数据的列数、每一列的字段类型都有严格的规定,因此数...

    温馨提示:如果使用电脑查看图片不清晰,可以使用手机打开文章单击文中的图片放大查看高清原图。
    Fayson的github:
    https://github.com/fayson/cdhproject
    提示:代码块部分可以左右滑动查看噢

    1

    文章编写目的

    使用Hive时大家都会遇到数据类型校验的问题,相比传统关系型数据库会严格要求数据的Schema,数据的列数、每一列的字段类型都有严格的规定,因此数据的存储必须按照定义的Schema格式来存储。而Hive数据库对数据格式及具体的内容并不关心,只有在数据被读出时才会与定义的Schema进行转换。那这个时候就会出现数据类型转换的问题,本篇文章Fayson主要分析下如何查找表中类型转换错误的数据以及Hive对空值和NULL的处理。

    • 测试环境

    1.RedHat7.2

    2.CM和CDH版本为5.15.0

    2

    测试数据准备

    1. 建表语句如下:
    create table test_null (id int, age string) 
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    STORED AS TEXTFILE;
    

    (可左右滑动)

    1. 测试数据如下:
    [root@cdh2 ~]# vim test1.dat
    1,23
    2,24c
    3,32d
    4,30
    5,NULL
    

    (可左右滑动)

    1. 将测试数据加载到test_cast表中,查看表中的数据

    Fayson在前面定义的表结构为id和age两个字段均为int类型,在Load的示例数据中age列有非数值类型的数据,查看表数据时会看到如上截图类型转换失败显示为NULL。

    3

    查找异常类型数据

    Hive本身没有机制来校验数据的有效性,如果我们想检索出表中类型转换异常的数据,则可以通过nvl和cast两个函数来结合判断数据是否转换失败了。如下为实现方式:

    1. 创建一个测试表及准备测试数据,SQL如下:
    select id,nvl(cast(age as int), "error") age from test_cast;
    

    2.将类型异常的数据插入到新的表中,SQL如下:

    create table  test_exception as
     select * from (select id,nvl(cast(age as int), 'error') age from test_cast) as b where b.age='error';
    

    (可左右滑动)

    同样也可以只是用cast来进行查找,SQL如下:

    create table test_exception as 
    select * from (select id,nvl(cast(age as int), age) age from test_cast) as b where b.age is null;
    

    (可左右滑动)

    查看检索出来类型异常的数据

    3.查看写入到HDFS的类型转换异常的数据

    通过如上方式我们可以检索出test_cast表中age列类型转换异常的数据,通过每条数据的ID查找对应的原始数据找到问题原因。在上述过程中还出现了另一个问题Hive中NULL和空值是如何处理的?如下Fayson再介绍下Hive中对着两个值的处理。

    4

    Hive中NULL和空值处理

    通过上述的处理过程,我们可以看到Hive对于类型转换异常的数据查询出来显示为NULL,但我们将这些数据写入到新的表后数据文件中显示的为\N。那在我们的数据中如果存’NULL’类型的字符串呢?Hive中默认将NULL存为\N,NULL类型的字符串如何检索?

    1.创建一个测试表及准备测试数据,SQL如下:

    create table test_null (id int, age string) 
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    STORED AS TEXTFILE;
    

    测试数据如下:

    [root@cdh2 ~]# vim test1.dat
    1,23
    2,24c
    3,32d
    4,30
    5,NULL
    6,\N
    

    2.将数据Load到test_null表中显示如下:

    如上图所5、6两条数据均显示的为NULL,通过数据我们无法真实的区别那条数据的age真正的为空。

    3.通过指定查询条件可以检索出空值和NULL类型字符串数据

    使用is null可以检索出存储为\N的数据(即id为6的这条数据)

    使用=’NULL’可以检索出为NULL字符串的数据(即id为5的这条数据)

    4.在Hive中是通过serialization.null.format参数来保存和标识NULL,通过将表的该参数修改为NULL表示为空值

    alter table test_null set serdeproperties ('serialization.null.format' = 'NULL');
    

    (可左右滑动)

    向表中插入一条age为NULL的数据

    insert into test_null values(7,NULL);
    

    查看此时表中的数据显示

    查看HDFS插入的数据显示NULL

    5

    总结

    1.Hive在对表进行Put和Load数据操作时,Hive是不支持数据类型的校验,在使用insert into table select…方式向表中插入数据时,对于类型异常的数据会在表中插入一个\N空的值(\N为Hive中默认NULL标识)

    2.可以使用serialization.null.format来指定Hive中保存和标识NULL,可以设置为默认的\N,也可以为NULL或’’

    3.如果表中存在大量的NULL值,则在Hive的数据文件中会产生大量的\N数据,浪费存储空间,那我们可以将serialization.null.format设置为’’

    alter table test_null set serdeproperties('serialization.null.format' = '');
    

    (可左右滑动)

    插入为NULL的数据后,HDFS的数据文件存储如下

    可以通过建表语句中指定Hive保存和标识NULL,也可以通过alter修改已存在的表,建表指定方式如下:

    create table test_null_1 (id int, age string) 
    ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    NULL DEFINED AS''
    STORED AS TEXTFILE;
    

    提示:代码块部分可以左右滑动查看噢
    为天地立心,为生民立命,为往圣继绝学,为万世开太平。
    温馨提示:如果使用电脑查看图片不清晰,可以使用手机打开文章单击文中的图片放大查看高清原图。

    展开全文
  • 此篇文章主要选取关键性指标,数据校验数据源Hive和目标ES内的数据是否一致; 因为你不知道将Hive的数据导入到了ElasticSearch后,数据量是否准确,所以需要钉钉报警校验ElasticSearch和Hive数据仓库内的数据质量,...
  • Hive字段校验测试

    2020-07-13 13:01:02
    2.如果表字段类型和HDFS文件中数据类型不匹配,则直接显示NULL 二、通过HQL插入长度/类型不一致数据场景 DROP TABLE tmp.test0713; CREATE TABLE `tmp.test0713`( `PK_ID` varchar(1) COMMENT '主键' ) ROW FORMAT ...

    一、HDFS文件中字段类型和表字段长度/类型不一致场景

    结论
    1.如果是varchar类型长度短于文件中字段长度,通过HQL查询时,会直接截断超过varchar类型长度的字符显示;
    2.如果表字段类型和HDFS文件中数据类型不匹配,则直接显示NULL

    二、通过HQL插入长度/类型不一致数据场景

    DROP TABLE tmp.test0713;
    CREATE TABLE `tmp.test0713`(
    `PK_ID` varchar(1) COMMENT '主键'
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' STORED AS TEXTFILE;
    insert overwrite table tmp.test0713 select PK_ID from tmp.test0714 limit 100;
    

    如上HQL所示,
    当tmp.test0714的PK_ID 字段比tmp.test0713长时,会直接截断,存入tmp.test0713;
    当tmp.test0714的PK_ID 字段和tmp.test0713的PK_ID 字段类型不一致时,会直接将NULL存入tmp.test0713;

    无论是用sqoop导入数据到HIVE表还是用HSQL insert插入数据,HIVE的字段校验都不会报错,只是做一些简单的截断或者置空处理,有点坑!!!

    补充点:hive的数据类型中string、varchar、char的区别

    varchar是变长,如果达不到设定的长度,按实际长度存储,如果超过了,则截断;
    char是固定长度,如果达不到设定的长度,会补全存储;
    string没有长度限制

    展开全文
  • Hive进行身份证合法性校验

    千次阅读 2016-12-27 10:10:07
    身份证号码格式校验是很多系统在数据集成时的一个常见需求,我们以18位身份证为例,使用一个Hive查询实现身份证号码的合法性验证。该查询结果是所有不合规的身份证号码。按以下身份证号码的定义规则建立查询。 ...
      身份证号码格式校验是很多系统在数据集成时的一个常见需求,我们以18位身份证为例,使用一个Hive查询实现身份证号码的合法性验证。该查询结果是所有不合规的身份证号码。按以下身份证号码的定义规则建立查询。

      身份证18位分别代表的含义,从左到右方分别表示:

    • 1-2 省级行政区代码。
    • 3-4 地级行政区划分代码。
    • 5-6 县区行政区分代码。
    • 7-10 11-12 13-14 出生年、月、日。
    • 15-17 顺序码,同一地区同年、同月、同日出生人的编号,奇数是男性,偶数是女性。
    • 18 校验码,如果是0-9则用0-9表示,如果是10则用X(罗马数字10)表示。

      身份证校验码的计算方法:

    1. 将前面的身份证号码17位数分别乘以不同的系数。从第一位到第十七位的系数分别为:7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2。
    2. 将这17位数字和系数相乘的结果相加。
    3. 用加出来和除以11,看余数是多少。
    4. 余数只可能有0-1-2-3-4-5-6-7-8-9-10这11个数字。其分别对应的最后一位身份证的号码为1-0-X -9-8-7-6-5-4-3-2。
      假设字段t.idcard存储身份证号码,Hive查询语句如下:
    -- Hive 18位身份证号码验证
    select * from
    (select trim(upper(idcard)) idcard from t) t1
     where -- 号码位数不正确
           length(idcard) <> 18 
           -- 省份代码不正确
           or substr(idcard,1,2) not in 
           ('11','12','13','14','15','21','22','23','31',
            '32','33','34','35','36','37','41','42','43',
            '44','45','46','50','51','52','53','54','61',
            '62','63','64','65','71','81','82','91') 
           -- 身份证号码的正则表达式判断
           or (if(pmod(cast(substr(idcard, 7, 4) as int),400) = 0 or
                 (pmod(cast(substr(idcard, 7, 4) as int),100) <> 0 and 
                  pmod(cast(substr(idcard, 7, 4) as int),4) = 0), -- 闰年 
               if(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|[1-2][0-9]))[0-9]{3}[0-9X]$',1,0),
               if(idcard regexp '^[1-9][0-9]{5}19[0-9]{2}((01|03|05|07|08|10|12)(0[1-9]|[1-2][0-9]|3[0-1])|(04|06|09|11)(0[1-9]|[1-2][0-9]|30)|02(0[1-9]|1[0-9]|2[0-8]))[0-9]{3}[0-9X]$',1,0))) = 0
           -- 校验位不正确
           or substr('10X98765432',pmod(
     (cast(substr(idcard,1,1) as int)+cast(substr(idcard,11,1) as int))*7
    +(cast(substr(idcard,2,1) as int)+cast(substr(idcard,12,1) as int))*9
    +(cast(substr(idcard,3,1) as int)+cast(substr(idcard,13,1) as int))*10
    +(cast(substr(idcard,4,1) as int)+cast(substr(idcard,14,1) as int))*5
    +(cast(substr(idcard,5,1) as int)+cast(substr(idcard,15,1) as int))*8
    +(cast(substr(idcard,6,1) as int)+cast(substr(idcard,16,1) as int))*4
    +(cast(substr(idcard,7,1) as int)+cast(substr(idcard,17,1) as int))*2
    +cast(substr(idcard, 8,1) as int)*1
    +cast(substr(idcard, 9,1) as int)*6
    +cast(substr(idcard,10,1) as int)*3,11)+1,1) 
    <> cast(substr(idcard,18,1) as int);
      这条查询语句虽然有些复杂,但条理还是比较清楚的。子查询将字符串转为大写,并去掉左右两边的空格,外层查询的where条件筛选出四种不符合规则的身份证号码。首先判断号码长度和省份代码,然后利用Hive的正则表达式匹配函数对整个号码做逐位判断,最后检查校验位是否正确。各种违规条件之间使用or逻辑运算符,前面的条件一旦满足即可返回数据行,而不会再继续判断后面的条件。
    展开全文
  • 前阵子博主遇到一个需求,因对hadoop集群进行数据迁移,数据迁移完毕后进行两个hive库的数据一致性的比对,不仅对源表数据进行比对,而且要同时使用两个集群加工相同数据,对加工后的数据进行数据一致性比对。...
        前阵子博主遇到一个需求,因对hadoop集群进行数据迁移,数据迁移完毕后进行两个hive库的数据一致性的比对,不仅对源表数据进行比对,而且要同时使用两个集群加工相同数据,对加工后的数据进行数据一致性比对。
    
        博主已知的数据迁移方法有两种,第一种就是hadoop distcp功能来进行集群间数据的复制,那么基本就不用做源表的数据验证了,集群间数据复制失败会报错提示。
        第二种方法就是数据的导入导出了,把原集群数据使用get命令导出至本地,把数据复制至新集群节点下,并把数据load至集群,虽然这种方法过程复杂,效率低下,工作量大,但是在某些情况下(新老集群网络不通或者带宽很低),还只能是唯一的选择。而这种方法在数据传输过程中产生数据丢失的可能性就会大大增加。
        不论使用的是这两种方式的哪一种,为确保数据不产生丢失,都要进行数据一致性的验证。
        大家都知道MD5文件校验工具(不知道的自己百度去),如果使用的是第二种方法进行集群间数据的迁移,那么可以在数据导出完毕后对文件进行一次MD5校验,当数据复制至新集群本地后,在数据入库之前做一次MD5文件校验,对同一文件产生的校验码进行比对,确保数据传输过程中数据不会丢失。那么问题来了,加工后的中间表数据一致性如何比对?而且对于第二种数据迁移方式来说,只能确保数据传输过程中不会产生数据丢失,但是数据导入导出的过程呢?如何确保?

        为此博主想到了以前用过的hive表数据的导出,是不是可以使用对表数据进行MD5校验的方式来实现呢?

         那么说做就做,博主做了一个小实验,对于两个CHD版本不同的集群,把一份相同的数据load至hive表中,以下是使用hive进行数据导出并验证MD5过程:

         测试用表主要字段介绍:

        表名:部门表

        主要字段:月份,部门标识,部门名称,归属地域编码,归属省份编码,部门管理者,起始有效时间,终止有效时间等

        对于两个CHD版本不同的集群,以下简称集群1,集群2,均含有部门表,理论上该部门表数据量及内容是相同的

        hive数据导出,博主是使用hive命令导出的方法,以下是导出sql(集群1):

           hive -e "use source;select * from c01060_depart_201803 where province_code=11 order by depart_id;" >user_base.txt

        根据部门标识进行排序是为了防止数据导出过程中数据所属行发生变化导致MD5数据验证失败(部门标识唯一不重复)

        以下是集群二数据导出以及对文件进行MD5数据验证截图

               

        以下是集群二数据导出以及对文件进行MD5数据验证截图

                

        从截图中可以看出,两个集群中所添加的条件不太相同,这就相当于数据加工的过程,取出的都是3月份表中所属省份标识为11的部门数据,截图可以看出两个集群的导出记录条数相同,导出文件的校验值相同,由此确保两张表中3账期并且省份标识为11的数据是完全一致的。

        了解过MD5原理的同学知道,两个文件中数据哪怕相差一个字符,亦或者行的位置调换,都会导致文件校验值产生变化,所以,这可以说是最严谨的一种验证方式了。但是这种验证方式有它自己的局限性:此种验证方式适合与验证数据量不大的表,对于数据量极大的表,排序速度极慢,导出时还会受制于linux网络以及磁盘IO限制。

        希望本文对您能有所帮助,如果您有更好的验证方式,也请不吝告知,谢谢!




    展开全文
  • Hive 数据质量检测

    2021-02-01 14:13:31
    可以检测Hive的元数据,比如Hive表元数据存在Mysql中,可以在Mysql中查询 mysql> desc TBLS; +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | ...
  • 之前由于工作需要,要对日期格式数据进行校验下面是校验代码 select '20201015' regexp '([\d]{4}(((0[13578]|1[02])((0[1-9])|([12][0-9])|(3[01])))|(((0[469])|11)((0[1-9])|([12][1-9])|30))|(02((0[1-9])|(1...
  • hive数据存储格式

    千次阅读 多人点赞 2019-11-30 16:48:27
    Hive支持的存储数据的格式主要有:TEXTFILE(行式存储) 、SEQUENCEFILE(行式存储)、ORC(列式存储)、PARQUET(列式存储)。 列式存储和行式存储 上图左边为逻辑表,右边第一个为行式存储,第二个为列式存储。 &...
  • 之前一直想做一个Hive一键运行脚本 找了很久才找到这篇博客 首先一定要确认环境变量中添加了hive环境变量 参考 参考 vi /etc/profile 文件内添加: export HIVE_HOME=/usr/local/hive(自己hive的安装...
  • Hive UDF实现身份证强校验

    千次阅读 2019-04-17 19:36:49
    工作中需要对Hive表中的身份证号进行强校验,由于最后一位是校验位,因此,简单的正则无法实现,随用UDF实现相关功能。 我只是实现了功能,没有做深入的优化,欢迎各位留言,指导下如何优化,不胜感激。 源码如下...
  • hive 数据写入

    2021-01-12 23:51:21
    hive表中数据的写入主要有 insert into(overwrite) values 、 insert ... select 、 load 、 create table as select ... dataTable 这几种方式。其中hive从0.14版本开始支持 insert into table values (line data)...
  • 身份证校验码的计算方法 1、将前面的身份证号码17位数分别乘以不同的系数。第i位对应的数为[2^(18-i)]mod11。从第一位到第十七位的系数分别为:7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2 ; 2、将这17位数字和系数相乘...
  • 首先,数据出现质量问题有哪些原因或者情况? 其次,针对这些原因,制定清洗策略。 一般的数据质量出现问题的有:无效,重复,缺失,不一致,错误值,格式出错,业务逻辑规则有问题,抽取数据程序有错等,另外还有...
  • impala查询数据hive的查询数据比对 先在cdh的hue中分别用impala和hive的查询数据对比 将impala的查询语句写入到shell脚本中 a=(`impala-shell -q 'SELECT count(1) FROM educator.t_event WHERE event_name = ...
  • 上图展示了一个Parquet文件的内容,一个文件中可以存储多个行组,文件的首位都是该文件的Magic Code,用于校验它是否是一个Parquet文件,Footer length记录了文件元数据的大小,通过该值和文件长度可以计算出元数据...
  • HIVE数据导入MYSQL实现方式

    千次阅读 2020-05-08 14:42:32
    *以下内容均转自其他CSDN博主的优秀内容 一、PYTHON脚本导数 ...hive_sql = """ beeline -u jdbc:hive2://10.5.145.113:10000 -n 用户名 -p 密码 --showHeader=false --outputformat=csv2 -e " SELECT * from tab...
  • **数据导出将数据写入文件系统**将数据写入一个文件****将数据写入多个文件****从一张表中导出两个文件到不同的路径**、**通过hive -e 命令导出****hive -f sqlfile 大规模SQL** 数据导入 load 加载数据
  • hadoop跨集群之间迁移hive数据

    千次阅读 2018-10-22 09:54:22
    1、hdfs数据迁移过程中,使用hadoop distcp /apps/hive/warehouse/userinfo hdfs://10.11.32.76:8020/apps/hive/warehouse/ 将老库的hive数据复制到新库中,在测试环境报权限错误,需要添加如下配置  &lt;...
  • Hive 数据存储格式

    2019-11-28 20:59:37
    Hive数据存储格式 Hive支持的存储数据的格式主要有:TEXTFILE(行式存储) 、SEQUENCEFILE(行式存储)、ORC(列式存储)、PARQUET(列式存储)。 文章目录Hive数据存储格式列式存储和行式存储TEXTFILE格式ORC格式...
  • hive数据存储格式

    2019-09-08 23:03:38
    Hive支持的存储数的格式主要有:TEXTFILE(行式存储) 、SEQUENCEFILE(行式存储)、ORC(列式存储)、PARQUET(列式存储)。 1 行式存储和列式存储 [外链图片转存失败(img-dOuxrqGC-1567955011190)(assets/...
  • 准备数据、了解数据、将数据导入hive;如何清洗第一行的脏数据?每个用户有多少个订单? (分组);每个用户一个订单平均是多少商品?一个订单有多少个商品? 一个用户有多少商品?进行用户对应的商品数量 sum求和; ...
  • HIVE数据模型

    2019-05-13 21:36:00
    Hive 中所有的数据都存储在 HDFS 中,Hive 中包含以下数据模型: 内部表(Table) 外部表(External Table) 分区(Partition) 桶(Bucket)。 一.内部表 Table 将数据保存到Hive 自己的数据仓库目录中:/usr/hive/...
  • 一、hive是什么 基于hadoop,数据存储在hdfs上...2、Hive是基于Hadoop的一个数据仓库工具 2.1基于hadoop主要是以下两个方面 hive中的表中的数据存储在hdfs上,存储完全依赖于hdfs; hive最终计算使用的默认mapre...
  • Hive通过Antlr语法解析器获取到SQL的抽象语法树(AST)并生成校验过元数据的逻辑执行计划后,在优化阶段会使用Statistics统计的规则(rule),如下图所示: 在AnnotateWithStatistics这个类中,在对执行计划...
  • 数据质量校验简单设计思路

    千次阅读 2020-04-16 15:12:39
    一些数据计算完毕后,我们需要知道计算结果是否符合预期,比如是否有脏数据,是否数据量符合预期。这里就有两个问题,一个是校验什么,另一个是怎么校验校验什么 - 单个字段校验,例如字段的唯一性、非空、数值...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 6,010
精华内容 2,404
关键字:

hive数据校验