hive简单操作总结和实例(一)_MySQL

bitsCN.com

1.准备环境

@1.启动hadoop集群环境

@2.启动mysql数据库服务

@3.启动hive

2.简单操作总结

@1.常用关系运算符

    1> =,,,>=,is null,is not null,like rlike,regexp

    2>例子

        假设已经建好一张表user,该表有三个字段 id int,name string,age int,三条数据 [(1,jl,20),(2,jx,24),(3,zw,28)]

        HQL:select * from user where id=1;

        结果:1,jl,20

        HQL:select * from user where id>1;

        结果:2,jx,24   3,zw,28

        HQL:select * from user where id>=2;

        结果:2,jx,24   3,zw,28

        HQL:select * from user where id

        结果:1,jl,20   2,jx,24

        HQL:select * from user where id

        结果:1,jl,20   2,jx,24

        HQL:select * from user where name is not null;

        结果:1,jl,20   2,jx,24   3,zw,28

        HQL:select * from user where name is null;

        结果:

        HQL:select * from user where name like '%j%';

        结果:1,jl,20   2,jx,24

        HQL:select * from user where id rlike '^[0-9]$';

        结果:1,jl,20   2,jx,24   3,zw,28

HQL:select * from user where name regexp '^//w+$';

        结果:1,jl,20   2,jx,24   3,zw,28

       小结:hql语句很想数据库中的sql,懂sql的话这些都不难理解和掌握

 @2.运算符

       1>+，-，*，/，%，&，|，^，~，

       2>例子

           HQL:select id+name from user;

           结果:三行三列的NULL...可见+在hive中不能将整形和字符串进行类似的操作

           HQL:select name+name from user;

           结果:散列NULL...可见+也不能讲字符串进行类似操作

           HQL:select id+age from user where id=1;

           结果:29

           HQL:select age/id from user;

           结果:28.0、20.0、26.666666...

           HQL:select age%id from user;

           结果:0、0、2

          HQL:select id from user where id=1 and id=3;

          结果:无结果 当然and也可以换成or 这和关系型数据库里边是一样的

          HQL:select id from user where not id=1;

          结果:2、3 等同于select id from user where id1;

@3.字符串

        HQL:select length(name) from user where id=1;

        结果:9

        HQL:select reverse(name) from user where id=1;

        结果:gnolgnaij

        HQL:select concat(id,name) from user where id=1;

        结果:1jianglong

        HQL:select concat_ws('-',id,name) from user where id=1;

        结果:无结果，会报错,意思貌似是不能将int类型的值和string类型的值进行字符串拼接操作。。。

        HQL:select concat_ws('-',id,age) from user where id=1;

        结果:无结果,会报错,意思貌似是不能将int类型的值进行字符串拼接操作....

       HQL:select concat_ws('-',name,name) from user where id=1;

       结果:jianglong-jianglong

       HQL:select substr(name,1),substring(name,1) from user where id=1;

       结果:jianglong jianglong

       HQL:select substr(name,1,3),substring(name,1,3) from user where id=1;

       结果:jia jia

       HQL:select upper(name),ucase(name) from user where id=1;

       结果:JIANGLONG JIANGLONG

       HQL:select lower(upper(name)),lcase(ucase(name)) from user where id=1;

       结果:jianglong jianglong

       HQL:select trim(' 123 ') from user;

       结果:123 123 123 rtrim和ltrim用法同上

       HQL:select id,name,regexp_replace(name,'j','') from user where id=1;

       结果:1 jianglong ianglong

现就到这儿吧,有时间再仔细研究研究,总体上手也比较简单,深奥的东东还需要琢磨

bitsCN.com