Hadoop-Hive常用基础HQL语句

一. 数据库

1. 查询数据库列表
show databases ;
2. 使用指定的数据库
use default;
3. 查看数据库的描述信息
desc database extended db_hive_03 ;

二. 表

1. 查询表列表
show tables ;
2. 查询表的描述信息:
desc student ;
desc extended student ;
desc formatted student ;
3. 创建表
create table student(
id int, 
name string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
load data local inpath '/opt/datas/student.txt'into table student ;
4. 创建一张表并复制一个表的结构和数据
create table if not exists default.dept_cats as select * from dept ;
5. 使用另一张表的结构创建一张新表
create table if not exists default.dept_like like default.dept ;
6. 清空表:
truncate table dept_cats ;
7.删除表
drop table if exists dept_like_rename ;
8. 修改表名
alter table dept_like rename to dept_like_rename ;
9.查询表
select * from student ;
select id from student ;

三. 功能函数:

1. 显示功能函数列表
show functions ;
2. 查看功能函数的描述信息
desc function upper ;
3. 查询功能函数的扩展信息
desc function extended upper ;
4. 测试功能函数
select id ,upper(name) uname from db_hive.student ;

四. 进阶:

1. 创建一个外部表,并指定导入文件的位置和字段分割符:
create EXTERNAL table IF NOT EXISTS default.emp_ext2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/hive/warehouse/emp_ext2';
2. 创建分区表:
create EXTERNAL table IF NOT EXISTS default.emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
)
partitioned by (month string,day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' ;
3. 向分区表中导入数据:
load data local inpath '/usr/local/app/hive_test/emp.txt' into table default.emp_partition partition (month='201805',day='31') ;
4. 查看分区表列表:
show partitions emp_partition ;
5. 查询分区表中的数据:
select * from emp_partition where month = '201509' and day = '13' ;
6. 加载数据到hive:
1)加载本地文件到hive表
load data local inpath '/opt/datas/emp.txt' into table default.emp ;

2)加载hdfs文件到hive中
load data inpath '/user/beifeng/hive/datas/emp.txt' overwrite into table default.emp ;

3)加载数据覆盖表中已有的数据
load data inpath '/user/beifeng/hive/datas/emp.txt' into table default.emp ;

4)创建表是通过insert加载
create table default.emp_ci like emp ;
insert into table default.emp_ci select * from default.emp ;

5)创建表的时候通过location指定加载
7. hive到文件:
insert overwrite local directory '/opt/datas/hive_exp_emp'
select * from default.emp ;

insert overwrite local directory '/opt/datas/hive_exp_emp2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY '\n'
select * from default.emp ;

bin/hive -e "select * from default.emp ;" > /opt/datas/exp_res.txt
8. 将查询结果导出到本地文件中:
insert overwrite directory '/hive_test/export_emp.txt' select * from emp;

select * from emp ;
select t.empno, t.ename, t.deptno from emp t ;

五. 进阶查询:

1. = >= <= between and
select * from emp limit 5 ;
select t.empno, t.ename, t.deptno from emp t where  t.sal between 800 and 1500 ;
2. is null / is not null /in /not in
select t.empno, t.ename, t.deptno from emp t where comm is null ;
3. max/min/count/sum/avg
select count(*) cnt from emp ;
select max(sal) max_sal from emp ;
select sum(sal) from emp ;
select avg(sal) from emp ;
4. group by /having 分组
emp表
* 每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno ;

* 每个部门中每个岗位的做高薪水
select t.deptno, t.job, max(t.sal) avg_sal from emp t group by t.deptno, job ;
5. >>>having
* where 是针对单条记录进行筛选
* having 是针对分组结果进行筛选
求每个部门的平均薪水大于2000的部门

select deptno, avg(sal) from emp group by deptno ;
select deptno, avg(sal) avg_sal from emp group by deptno having avg_sal > 2000;
6. join 两个表进行连接
##等值jion  join ... on
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno ;

##左连接  left join
select e.empno, e.ename, d.deptno, d.dname  from emp e left join dept d on e.deptno = d.deptno ;

##右连接  right join
select e.empno, e.ename, e.deptno, d.dname  from emp e right join dept d on e.deptno = d.deptno ;

##全连接  full join
select e.empno, e.ename, e.deptno, d.dname  from emp e full join dept d on e.deptno = d.deptno ;

六. 客户端配置与启停

1. 关闭CLI客户端命令:
exit

#退出hive命令,使用exit,不要直接用ctrl+c,否则进程还在,只是窗口关闭了而已.
2. 在启动hive时设置配置属性信息
$ bin/hive --hiveconf <property=value>
3. 查看当前所有的配置信息
hive > set ;

hive (db_hive)> set system:user.name ;
system:user.name=beifeng
4. 查看帮助
[beifeng@hadoop-senior hive-0.13.1]$ bin/hive -help
5. 执行sql语句
* bin/hive -e <quoted-query-string>
eg:
bin/hive -e "select * from db_hive.student ;"
6. 执行指定的文件
* bin/hive -f <filename>
eg:
$ touch hivef.sql
select * from db_hive.student ;
$ bin/hive -f /opt/datas/hivef.sql 

#将执行结果输入到指定的文件中
$ bin/hive -f /opt/datas/hivef.sql > /opt/datas/hivef-res.txt
7. 在hive cli命令窗口中如何查看hdfs文件系统
hive (default)> dfs -ls / ;  
8. 在hive cli命令窗口中如何查看本地文件系统
hive (default)> !ls /opt/datas ;
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 159,458评论 4 363
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 67,454评论 1 294
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 109,171评论 0 243
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 44,062评论 0 207
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 52,440评论 3 287
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 40,661评论 1 219
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 31,906评论 2 313
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 30,609评论 0 200
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 34,379评论 1 246
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 30,600评论 2 246
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 32,085评论 1 261
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 28,409评论 2 254
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 33,072评论 3 237
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 26,088评论 0 8
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 26,860评论 0 195
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 35,704评论 2 276
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 35,608评论 2 270

推荐阅读更多精彩内容