hive学习之hql教程

最新推荐文章于 2024-03-06 15:11:42 发布
最新推荐文章于 2024-03-06 15:11:42 发布
阅读量951 收藏 5
点赞数 1
文章标签： hive big data hql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44178366/article/details/120581403
版权

Hsql语法

DDL数据定义

数据库的ddl

创库语法

create  database [if not exists] database_name
[comment database_comment]
[location hdfs_path] -- 先不指定location
[with dbproperties (property_name=property_value,...)];

# 创建库mydb
create database if not exists mydb
comment "my first db"
with dbproperties("createtime"="2021-10-01");

# mydb1
create database if not exists mydb1
location "/mydb1.db"

查看数据库

show databases;
desc database mydb;
desc database extended mydb;

在这里插入图片描述

删除库

# 空表才能删除，危险操作。
drop database mydb1;

表的ddl

创建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name  -- external:外部表，默认内部表
[(col_name data_type [COMMENT col_comment], ...)] -- 指定列名，列类型[列描述信息...]
[COMMENT table_comment] -- 表描述信息
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]  -- 创建分区表，指定分区字段
[CLUSTERED BY (col_name, col_name, ...) -- 创建分桶表，指定分桶字段
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] -- 指定分桶数
[ROW FORMAT delimited fields terminated by ...]  -- 指定一条数据字段与字段的分隔符
[collection items terminated by ...] -- 指定集合元素与元素的分隔符
[map keys terminated by ...] -- 指定map的K,V分隔符
[STORED AS file_format]  -- 指定文件存储格式，默认为textfile
[LOCATION hdfs_path] -- 指定表在hdfs中对应的路径
[TBLPROPERTIES (property_name=property_value, ...)] -- 指定表的属性
[AS select_statement] -- 基于某个查询建表

# 建表：use mydb;没有指定location的话，默认就在mydb库对应的目录的下面
# 1001,zhangssan
# 1002,lisi
# 1003,wangwu


create table if not exists test1(
  id int comment "this is id",
  name string comment "this is name"
)
comment "this is a table"
row format delimited fields terminated by ','
stored as textfile
TBLPROPERTIES("createtime"="2021-10-01");

# 创建表2,这个location很灵活的，无论是先有数据还是先有表，只有数据在对应的目录下，那么就能分析数据
create table if not exists test2(
  id int ,
  name string
)
row format delimited fields terminated by ','
location "/test2.table";

查看表

show tables;
desc tablename;
desc formattd tablename; # 可以看到表的存放位置MYSQL: select * from SDS;

内外部表转换

# 修改内部表student2为外部表(严格大写)
alter table student2 set tblproperties('EXTERNAL'='TRUE');

# 修改外部表student2为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');

MANAGED_TABLE和EXTERNAL_TABLE有什么区别呢

当两者在删除的时候，内部表删除的时候会在在hdfs中对应的目录也给删除掉
而外部表在删除的时候会保留在hdfs中的目录

修改表

可以对表的名字，列等进行修改

# 创建数据文件 emptest.txt
1001	zhangsan	10000.1
1002	lisi	10000.2
1003	wangwu	10000.3

# 创建表
create table emp(
 id int,
 name string,
 salary double
)
row format delimited fields terminated by '\t';

# 加载数据
load data local inpath '/opt/module/hive-3.1.2/datas/emptest.txt' into table emp;

# 查询数据
0: jdbc:hive2://hadoop102:10000> select * from emp;

+---------+-----------+-------------+
| emp.id  | emp.name  | emp.salary  |
+---------+-----------+-------------+
| 1001    | zhangsan  | 10000.1     |
| 1002    | lisi      | 10000.2     |
| 1003    | wangwu    | 10000.3     |
+---------+-----------+-------------+
3 rows selected (7.477 seconds)
0: jdbc:hive2://hadoop102:10000> 

# 修改表
alter table rmp rename to emptest;
# 更新列(要注意列的类型，大于等于原来的类型)
alter table emptest change column salary sal double;
# 增加列
alter table emptest add columns (addr string,deptno int);
注意：假设数据一开始就是有3列，但是呢，我们创建表的时候只写了三列，这样也是可以的，因为是从左往右匹配的；

# 替换列(也是可以的，按顺序匹配从左往右)
alter table emptest replace columns (empid int,empname string);

删除表

drop table emptest;

DML数据操作

数据操作语言，在hive中主要包括数据导入与导出

数据导入

向表中装载数据(load)

语法

hive> load data [local] inpath '数据的path' [overwrite] into table student [partition (partcol1=val1,…)];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

重复装载（没有overwrite）会追加，overwrite是全覆盖

# 从本地装载数据到hive表中
load data local inpath '/opt/module/hive-3.1.2/datas/student.txt' into  table student;
# 从hdfs中装载数据到hive表中（hdfs中数据会删除，相当于剪切到了hive表中）
load data inpath '/hivedatas/student.txt' into  table student;
通过查询语句向表中插入数据(insert)

insert into select …from table,这种方式会用的比较多些

在这里插入图片描述

查询语句中创建表并加载数据(as select)
基于查询建表，根据查询结果创建表（查询的结果会添加到新创建的表中）
create table if not exists emptest1
as select empid, empname from emptest;
创建表时通过Location指定加载数据路径

这种方式用的最多

1）上传数据到hdfs上
hive (default)> dfs -mkdir /student;
hive (default)> dfs -put /opt/module/datas/student.txt /student;
2）创建表，并指定在hdfs上的位置
hive (default)> create external table if not exists student5(
              id int, name string
              )
              row format delimited fields terminated by '\t'
              location '/student;
3）查询数据
hive (default)> select * from student5;
import数据到指定Hive表中

注意：先用export导出后，再将数据导入

import table emptest2 from '/emptest';

export和import主要用于两个Hadoop平台集群之间Hive表迁移。

数据导出

Insert 导出

注意一个现象，如果是null的数据，导出来就是 \N

# 导出到本地，没有格式
insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result' select empid,empname from emptest;

# 导出到本地，指定格式
insert overwrite local directory '/opt/module/hive-3.1.2/datas/insert-result' 
row format delimited fields terminated by '\t' select empid,empname from emptest;

# 将查询结果导出到HDFS(没有Local)
insert overwrite directory '/insert-result'
row format delimited fields terminated by '\t' select empid,empname from emptest;
Hadoop命令导出到本地
hive (default)> dfs -get /user/hive/warehouse/student/student.txt
/opt/module/datas/export/student3.txt;
Hive Shell命令导出

基本语法：（hive -f/-e 执行语句或者脚本 > file）

[atguigu@hadoop102 insert-result]$ hive -e "select * from mydb.emptest" > /opt/module/hive-3.1.2/datas/hive-result/0.txt
Export导出到HDFS上

不仅会导出数据，还会把元数据信息也导出,注意：只能导出到HDFS,不能导出到本地

export和import主要用于两个Hadoop平台集群之间Hive表迁移。

export table emptest to '/emptest';
Sqoop导出

后面专门会讲

清除表中数据（Truncate）

注意：Truncate只能删除管理表（内部表），不能删除外部表中数据

truncate table emptest2;

DQL数据查询

基本语法

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [HAVING having_condition]
  [ORDER BY col_list]  -- 全局排序
  [CLUSTER BY col_list    -- 分区排序（对应MR中的分区排序）
    | 
   [DISTRIBUTE BY col_list] -- 分区
   [SORT BY col_list] -- 区内排序
  ]
 [LIMIT number]

基本查询

数据准备

dept.txt:
10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700
emp.txt：
7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20

（1）创建部门表
create table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

（2）创建员工表
create table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

（3）导入数据
load data local inpath '/opt/module/hive-3.1.2/datas/dept.txt' into table dept;
load data local inpath '/opt/module/hive-3.1.2/datas/emp.txt' into table emp;
全表和特定列查询
select * from emp;
select empno, ename from emp;

注意：

（1）SQL 语言大小写不敏感。

（2）SQL 可以写在一行或者多行

（3）关键字不能被缩写也不能分行

（4）各子句一般要分行写。

（5）使用缩进提高语句的可读性。

列别名
select ename AS name, deptno dn from emp;
select sal+1 as salary from emp;
算术运算符
运算符描述
A+BA和B 相加
A-BA减去B
A*BA和B 相乘
A/BA除以B
A%BA对B取余
A&BA和B按位取与
A|BA和B按位取或
A^BA和B按位取异或
~AA按位取反
常用函数

count,max,min,sum,avg

1）求总行数（count）
hive (default)> select count(*) cnt from emp;
2）求工资的最大值（max）
hive (default)> select max(sal) max_sal from emp;
3）求工资的最小值（min）
hive (default)> select min(sal) min_sal from emp;
4）求工资的总和（sum）
hive (default)> select sum(sal) sum_sal from emp; 
5）求工资的平均值（avg）
hive (default)> select avg(sal) avg_sal from emp;
limit语句
hive (default)> select * from emp limit 5;
hive (default)> select * from emp limit 2,3;
where语句
（1）查询出薪水大于1000的所有员工
hive (default)> select * from emp where sal >1000;
（2）查询出薪水等于5000的所有员工
hive (default)> select * from emp where sal =5000;
（3）查询工资在500到1000的员工信息
hive (default)> select * from emp where sal between 500 and 1000;
（4）查询comm为空的所有员工信息
hive (default)> select * from emp where comm is null;
（5）查询工资是1500或5000的员工信息
hive (default)> select * from emp where sal IN (1500, 5000);
Like和RLike
-- 查找名字中带有A的员工信息
hive (default)> select * from emp where ename  RLIKE '[A]';
逻辑运算符

AND ,OR ,NOT

分组

Group By语句

GROUP BY语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作。在集群上每次运行的时候都很慢，可以开启本地模式 set hive.exec.mode.local.auto=true;发现启动报错，原因是内存溢出，需要修改conf下面的hive-env.sh.template文件： export HADOOP_HEAPSIZE=1024

（1）计算emp表每个部门的平均工资
	select t.deptno, avg(t.sal) avg_sal from emp t group by t.deptno;
（2）计算emp每个部门中每个岗位的最高薪水
    select t.deptno, t.job, max(t.sal) max_sal from emp t group by
    t.deptno, t.job;

Having语句

求每个部门的平均薪水大于2000的部门
hive (default)> select deptno, avg(sal) avg_sal from emp group by deptno having
 avg_sal > 2000;

Join语句

其实就两种：内连接，内连接的结果取交集

外连接：主表（驱动表）和从表（匹配表），外连接的结果集为主表的所有数据+从表中与主表匹配的数据

与mysql一样。

等值Join
根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称；
select e.empno, e.ename, d.deptno, d.dname from emp e join dept d on e.deptno = d.deptno;

left outer join

right outer join

等等

排序

全局排序（Order By）

order by:全局排序，只有一个Reducer

使用 ORDER BY 子句排序
ASC（ascend）: 升序（默认）
DESC（descend）: 降序

（1）查询员工信息按工资升序排列
	select * from emp order by sal;
（2）查询员工信息按工资降序排列
	select * from emp order by sal desc;
（3） 按照别名排序,按照员工薪水的2倍排序
	select ename, sal*2 twosal from emp order by twosal;
（4） 多个列排序,按照部门和工资升序排序
	select ename, deptno, sal from emp order by deptno, sal ;
每个Reducer内部排序（Sort By）

Sort By：对于大规模的数据集order by的效率非常低。在很多情况下，并不需要全局排序，此时可以使用sort by。

Sort by为每个reducer产生一个排序文件。每个Reducer内部进行排序，对全局结果集来说不是排序。

注意：Sort By最好配置Distribute By使用，一定是先分区后排序。

在这里插入图片描述

分区（Distribute By）

Distribute By： 在有些情况下，我们需要控制某个特定行应该到哪个reducer，通常是为了进行后续的聚集操作。distribute by 子句可以做这件事。distribute by类似MR中partition（自定义分区），进行分区，结合sort by使用。

对于distribute by进行测试，一定要分配多reduce进行处理，否则无法看到distribute by的效果。

案例实操

（1）先按照部门编号分区，再按照员工编号降序排序。
hive (default)> set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory '/opt/module/hive/datas/distribute-result' select * from emp distribute by deptno sort by empno desc;

注意：

  • distribute by的分区规则是根据分区字段的hash码与reduce的个数进行模除后，余数相同的分到一个区
  • Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
Cluster By

当distribute by和sort by字段相同时，可以使用cluster by方式。

cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

以下两种写法等价

# 注意：按照部门编号分区，不一定就是固定死的数值，可以是20号和30号部门分到一个分区里面去。
select * from emp cluster by deptno;
select * from emp distribute by deptno sort by deptno;
倔强的耗子
关注
  • 1
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
HQL基本教程
爱乂乂的博客
08-23 1896
SQL统计表中总记录行数 下面是选自 “access_log” 表的数据： +-----+---------+-------+------------+ | aid | site_id | count | date | +-----+---------+-------+------------+ | 1 | 1 | 45 | 2016-05-10 | | 2 ...
HQL基础语法
weixin_45012397的博客
05-17 1176
ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。Hive中的语句叫做HQL语句,是一种类似SQL的语句,基本上和SQL相同但是某些地方也是有很大的区别.
Hive中的HQL是什么？请解释其语法和常用操作。
jakelihua
09-11 416
它是基于Hive的数据模型和查询引擎构建的，允许用户使用类似于SQL的语法来查询和处理数据。可以指定要聚合的列，并可以通过GROUP BY子句对数据进行分组。通过使用HQL，用户可以方便地查询和操作Hive中的数据，类似于使用SQL查询关系型数据库。可以指定要更新的列和对应的新值，并可以通过WHERE子句指定更新的条件。可以指定要排序的列，并可以指定升序（ASC）或降序（DESC）。可以指定要查询的列，并可以通过WHERE子句指定查询的条件。可以通过WHERE子句指定要删除的数据的条件。
HQL语法
qq_61162288的博客
05-17 1583
ARRAY和MAP与Java中的Array和Map类似，而STRUCT与C语言中的Struct类似，它封装了一个命名字段集合，复杂数据类型允许任意层次的嵌套。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。对于Hive的String类型相当于数据库的varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。原生类型从窄类型到宽类型的转换称为隐式转换，反之，则不允许。- LIKE 允许用户复制现有的表结构，但是不复制数据。
什么是VHDL？一文带你了解VHDL语言
热门推荐
coachip的博客
09-08 1万+
基于FPGA的SOC在嵌入式系统应用越来越广了，比较流行的硬件描述语言有两种Verilog HDL/VHDL，均为IEEE标准。VHDL如果有C语言基础的话就会比较容易上手。而VHDL语言则需要Ada编程基础。另外VHDL语言具有大量成熟的模块，从某种角度说VHDL更具生命力。
大数据技术之hive学习文档
06-29
【大数据技术之 Hive 学习文档】 Hive 是一个基于 Hadoop 的数据仓库工具，它允许用户使用类似于 SQL 的查询语言（Hive Query Language，HQL）对存储在 HDFS 上的大量结构化数据进行分析。Facebook 开源了 Hive，以...
hive学习笔记精华版
10-08
【Hive学习笔记精华版】 Hive是大数据处理领域中的一款重要工具，它基于Hadoop构建，主要用于数据仓库和数据分析。作为一个离线处理系统，Hive可以将结构化的数据文件映射成数据库表，允许用户使用类似SQL的查询...
hadoop Hive学习配套数据
04-01
Hadoop Hive学习配套数据是大数据领域中非常关键的一部分，它主要服务于那些想要深入理解Hive在Hadoop生态系统中如何操作和分析大规模数据的学者和从业者。Hive是Apache软件基金会开发的一个数据仓库工具，它允许...
《Hive数据仓库案例教程》教学大纲.pdf
04-04
学习者需要掌握不同数据类型的操作，理解Hive的数据存储机制，如TextFile、SequenceFile、Orc和Parquet等。 4. **Hive数据定义**：讲解如何创建、查询、修改和删除数据库及表，包括分区表和桶表的创建。学生将学会...
hive学习记录
05-16
【Hive学习记录】 Hive 是一种基于 Hadoop 的数据仓库工具，用于处理和管理大数据。它提供了 SQL 类似的查询语言——HQL (Hive Query Language)，使得非程序员也能轻松进行大数据分析。以下是对Hive的一些关键知识...
掌握HQL--HQL语法全面指南
jixiangendless的博客
01-12 2958
HQL语法全面指南
HQL使用技巧
weixin_45065245的博客
09-19 219
善用union all，多表union all会优化成一个MR job，但在使用union all聚合时，由于聚合函数、分组会使MR job数增加，消除子查询内的group by、巧用中间表（临时表、单独处理子查询内的Join等能起到的效果。此时，由于使用了distinct，因此在Map阶段不能用combine对输出结果驱虫，必须将author_id作为Key输出，在Reduce阶段再对来自于不同Map Task、相同Key的结果去重。桶表抽样是一种桶表进行优化的抽样。
关于一些简单HQL语句的总结
weixin_41928342的博客
06-13 901
//创建一个普通表 , 并且用默认的分割字段 /001 在shell中ctrl+v然后ctrl+a create table tableName(id int,name string,age int);//建表语法create [external] table [if not exists] table_name(filed_name field_type,filed_name field_ty...
Hadoop Hive sql语法详解
weixin_30355437的博客
02-14 6927
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构 化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需 要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户...
hive--HQL基础语法（附带案例）
m0_47605847的博客
12-28 2011
写在前面 HQL与SQL语法都差不多，所以对于sql稍有了解的新手学习起来还是很容易的，不过不了解也没有关系，不会将sql与hql某些句法搞混hhh 在前一篇文章，hive创建元数据已经对hive的基础类型做了说明，总而言之， 基本类型：tinyint, smalint, int, bigint, boolean, float, double,string, timestamp, binary ； 集合类型： struct，array，map。 学习某一个数据结构最基础的就是要熟练运用他的“增删改查”
HQL的使用
琦彦
09-22 2343
导读 HQL(Hibernate Query Language) 是面向对象的查询语言, 它和 SQL 查询语言有些相似. 在 Hibernate 提供的各种检索方式中, HQL 是使用最广的一种检索方式. 它有如下功能: 在查询语句中设定各种查询条件；支持投影查询, 即仅检索出对象的部分属性；支持分页查询；支持连接查询；支持分组查询, 允许使用 HAVING 和 GROUP BY
【SQL入门】最全SQL基础知识总结（理论+举例+建议）
最新发布
qq_39172059的博客
03-06 6768
数字型（int，tinyint，decimal，float等）、日期和时间类型（Date，TIME等）、字符串类型（char，varchar等）。：于1992年发布，这个版本通过引入新的特性进一步扩展了SQL语言的能力，包括多表连接、子查询、视图和存储过程等，是目前获得最广泛支持的版本。：为表设置适当的表别名，不仅可以减少SQL语句的复杂度，也能尽量避免出错，让SQL语句更加的直观、易理解。：在1989年发布，这个版本引入了更多的特性，包括排序、分组和连接操作的支持。
HQL - Hibernate查询语言 - 示例教程
YunWisdom
03-22 249
HQL - Hibernate查询语言 - 示例教程 HQL或Hibernate查询语言是Hibernate Framework的面向对象查询语言。HQL与SQL非常相似，只是我们使用Objects而不是表名，这使得它更接近面向对象的编程。 目录[隐藏] 1Hibernate查询语言 - HQL 1.1HQL示例数据库设置 1.2Hibernate Maven依...
【无标题】
qq_41682017的博客
04-26 845
MySql 高级函数
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值