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

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<3;

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

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

结果: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 id<>1;

@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

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