mysql插入数据hql语句_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

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

eg:

bin/hive -e "select * from db_hive.student ;"

6. 执行指定的文件

* bin/hive -f

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 ；