HQL操作语句

Wang·Br

于 2021-09-01 10:22:23 发布

阅读量1.5k

点赞数

分类专栏：笔记 bigdata 文章标签： hive 数据库 sql

本文链接：https://blog.csdn.net/weixin_44847293/article/details/120034023

版权

笔记同时被 2 个专栏收录

41 篇文章 2 订阅

订阅专栏

bigdata

31 篇文章 0 订阅

订阅专栏

返回hive笔记

HQL-DDL命令

参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL

DDL（data definition language）：主要的命令有CREATE、ALTER、DROP等。

DDL主要是用在定义、修改数据库对象的结构或数据类型。

【图】

第 1 节数据库操作

Hive有一个默认的数据库default，在操作HQL时，如果不明确的指定要使用哪个库，则使用默认数据库；

Hive的数据库名、表名均不区分大小写；

名字不能使用数字开头；

不能使用关键字，尽量不使用特殊符号；

创建数据库语法

CREATE [REMOTE] (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [MANAGEDLOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];
  
-- IF NOT EXISTS 如果不存在创建
-- COMMENT 给添加注释
-- LOCATION 指定存储路径
-- MANAGEDLOCATION 管理位置

-- 创建数据库，在HDFS上存储路径为 /user/hive/warehouse/*.db 

hive (default)> create database mydb; 
hive (default)> dfs -ls /user/hive/warehouse; 

-- 避免数据库已经存在时报错，使用 if not exists 进行判断【标准写法】 

hive (default)> create database if not exists mydb; 

-- 创建数据库。添加备注，指定数据库在存放位置 

hive (default)> create database if not exists mydb2 
comment 'this is mydb2' 
location '/user/hive/mydb2.db';

查看数据库

-- 查看所有数据库 
show database; 

-- 查看数据库信息 
desc database mydb2; 
-- 进一步详细信息展示
desc database extended mydb2; 
describe database extended mydb2;

使用数据库

USE database_name;
USE DEFAULT;

-- 查看当前使用的数据库
SELECT current_database();

删除数据库

DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];

-- 删除一个空数据库 
drop database databasename; 

-- 如果数据库不为空，使用 cascade 强制删除 
drop database databasename cascade;

修改数据库

-- 修改数据库参数
ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
-- 
ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
  
ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)
 
ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path; -- (Note: Hive 4.0.0 and later)

第 2 节建表语法

create [external] table [IF NOT EXISTS] table_name 
    [(colName colType [comment 'comment'], ...)] 
    [comment table_comment] 
    [partitioned by (colName colType [comment col_comment], ...)] 
    [clustered BY (colName, colName, ...) 
    [sorted by (col_name [ASC|DESC], ...)] into num_buckets buckets] 
    [row format row_format] 
    [stored as file_format] 
    [LOCATION hdfs_path] 
    [TBLPROPERTIES (property_name=property_value, ...)] 
    [AS select_statement]; 

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] 
    [db_name.]table_name 
    LIKE existing_table_or_view_name 
    [LOCATION hdfs_path];

CREATE TABLE：按给定名称创建表，如果表已经存在则抛出异常。可使用if not exists 规避。
EXTERNAL：有关键字是创建外部表，否则创建的是内部表(管理表)。

删除内部表时，数据和表的定义同时被删除；

删除外部表时，仅仅删除了表的定义，数据保留；

在生产环境中，多使用外部表；
comment：表的注释
partitioned by：对表中数据进行分区，指定表的分区字段
clustered by：创建分桶表，指定分桶字段
sorted by：对桶中的一个或多个列排序，较少使用
存储子句：

ROW FORMAT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] | SERDE serde_name 
[WITH SERDEPROPERTIES (property_name=property_value, 
property_name=property_value, ...)]

建表时可指定 SerDe 。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用默认的 SerDe。建表时还需要为表指定列，在指定列的同时也会指定自定义的 SerDe。Hive通过 SerDe 确定表的具体的列的数据。

SerDe是 Serialize/Deserilize 的简称， hive使用Serde进行行对象的序列与反序列化。

stored as SEQUENCEFILE|TEXTFILE|RCFILE：如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）。
LOCATION：表在HDFS上的存放位置
TBLPROPERTIES：定义表的属性
AS：后面可以接查询语句，表示根据后面的查询结果创建表
LIKE：like 表名，允许用户复制现有的表结构，但是不复制数据

官网文档

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [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]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]
  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]  -- (Note: Available in Hive 0.6.0 and later)
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]   -- (Note: Available in Hive 0.6.0 and later)
  [AS select_statement];   -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)
 
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];
 
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type  -- (Note: Available in Hive 0.7.0 and later)
 
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later)
  | STRING
  | BINARY      -- (Note: Available in Hive 0.8.0 and later)
  | TIMESTAMP   -- (Note: Available in Hive 0.8.0 and later)
  | DECIMAL     -- (Note: Available in Hive 0.11.0 and later)
  | DECIMAL(precision, scale)  -- (Note: Available in Hive 0.13.0 and later)
  | DATE        -- (Note: Available in Hive 0.12.0 and later)
  | VARCHAR     -- (Note: Available in Hive 0.12.0 and later)
  | CHAR        -- (Note: Available in Hive 0.13.0 and later)
 
array_type
  : ARRAY < data_type >
 
map_type
  : MAP < primitive_type, data_type >
 
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
 
union_type
   : UNIONTYPE < data_type, data_type, ... >  -- (Note: Available in Hive 0.7.0 and later)
 
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

第 3 节内部表 & 外部表

在创建表的时候，可指定表的类型。表有两种类型，分别是内部表(管理表)、外部表。

默认情况下，创建内部表。如果要创建外部表，需要使用关键字 external
在删除内部表时，表的定义(元数据) 和数据同时被删除
在删除外部表时，仅删除表的定义，数据被保留
在生产环境中，多使用外部表

内部表

t1.dat文件内容

2;zhangsan;book,TV,code;beijing:chaoyang,shagnhai:pudong
3;lishi;book,code;nanjing:jiangning,taiwan:taibei
4;wangwu;music,book;heilongjiang:haerbin

创建表 SQL

-- 创建内部表 
create table t1(
    id int,
    name string,    
    hobby array<string>,    
    addr map<string, string> 
)
row format delimited 
fields terminated by ";" 
collection items terminated by "," 
map keys terminated by ":"; 

-- 显示表的定义，显示的信息较少 
desc t1; 

-- 显示表的定义，显示的信息多，格式友好 
desc formatted t1; 

-- 加载数据 
load data local inpath '/home/hadoop/data/t1.dat' into table t1; 
-- 查询数据 
select * from t1; 

-- 查询数据文件 
dfs -ls /user/hive/warehouse/mydb.db/t1; 

-- 删除表。表和数据同时被删除 
drop table t1; 
-- 再次查询数据文件，已经被删除

外部表

-- 创建外部表 
create external table t2(    
    id int,    
    name string,    
    hobby array<string>,    
    addr map<string, string> 
)
row format delimited 
fields terminated by ";" 
collection items terminated by "," 
map keys terminated by ":"; 

-- 显示表的定义
desc formatted t2; 
-- 加载数据
load data local inpath '/home/hadoop/data/t1.dat' into table t2;
-- 查询数据
select * from t2;
-- 删除表。表删除了，目录仍然存在
drop table t2; 
-- 再次查询数据文件，仍然存在

内部表与外部表的转换

-- 创建内部表，加载数据，并检查数据文件和表的定义 
create table t1(    
    id int,    
    name string,    
    hobby array<string>,    
    addr map<string, string>
)
row format delimited 
fields terminated by ";" 
collection items terminated by "," 
map keys terminated by ":"; 

load data local inpath '/home/hadoop/data/t1.dat' into table t1; 

dfs -ls /user/hive/warehouse/mydb.db/t1; 

desc formatted t1; 

-- 内部表转外部表 
alter table t1 set tblproperties('EXTERNAL'='TRUE'); 

-- 查询表信息，是否转换成功 
desc formatted t1; 

-- 外部表转内部表。EXTERNAL 大写，false 不区分大小 
alter table t1 set tblproperties('EXTERNAL'='FALSE'); 

-- 查询表信息，是否转换成功 
desc formatted t1;

小结

建表时：
- 如果不指定external关键字，创建的是内部表；
- 指定external关键字，创建的是外部表；
删表时
- 删除外部表时，仅删除表的定义，表的数据不受影响
- 删除内部表时，表的数据和定义同时被删除
外部表的使用场景
- 想保留数据时使用。生产多用外部表

第 4 节分区表

Hive在执行查询时，一般会扫描整个表的数据。由于表的数据量大，全表扫描消耗时间长、效率低。

而有时候，查询只需要扫描表中的一部分数据即可，Hive引入了分区表的概念，将表的数据存储在不同的子目录中，每一个子目录对应一个分区。只查询部分分区数据时，可避免全表扫描，提高查询效率。

在实际中，通常根据时间、地区等信息进行分区。

分区表创建与数据加载

-- 创建表 
create table if not exists t3(    
    id int,    
    name string,    
    hobby array<string>,    
    addr map<String,string> 
)
partitioned by (dt string) 
row format delimited 
fields terminated by ';' 
collection items terminated by ',' 
map keys terminated by ':'; 

-- 加载数据。 
load data local inpath "/home/hadoop/data/t1.dat" into table t3 partition(dt="2020-06-01"); 
load data local inpath "/home/hadoop/data/t1.dat" into table t3 partition(dt="2020-06-02");

备注：分区字段不是表中已经存在的数据，可以将分区字段看成伪列

查看分区

show partitions table_name;

新增分区并设置数据

-- 增加一个分区，不加载数据 
alter table t3 add partition(dt='2020-06-03'); 

-- 增加多个分区，不加载数据 
alter table t3 add partition(dt='2020-06-05') partition(dt='2020-06-06'); 

-- 增加多个分区。准备数据 
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-07
hdfs dfs -cp /user/hive/warehouse/mydb.db/t3/dt=2020-06-01 /user/hive/warehouse/mydb.db/t3/dt=2020-06-08 

-- 增加多个分区。加载数据 
alter table t3 add 
partition(dt='2020-06-07') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-07' 
partition(dt='2020-06-08') location '/user/hive/warehouse/mydb.db/t3/dt=2020-06-08'; 

-- 查询数据 
select * from t3;

修改分区的hdfs路径

alter table t3 partition(dt='2020-06-01') 
set location '/user/hive/warehouse/t3/dt=2020-06-03';

删除分区

-- 可以删除一个或多个分区，用逗号隔开 
alter table t3 drop partition(dt='2020-06-03');

alter table t3 drop partition(dt='2020-06-03'), partition(dt='2020-06-04');

第 5 节分桶表

当单个的分区或者表的数据量过大，分区不能更细粒度的划分数据，就需要使用分桶技术将数据划分成更细的粒度。将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。分桶的原理：

MR中：key.hashCode % reductTask
Hive中：分桶字段.hashCode % 分桶个数

-- 测试数据 
1	java	90
1	c	78
1	python	91
1	hadoop	80
2	java	75
2	c	76
2	python	80
2	hadoop	93
3	java	98
3	c	74
3	python	89
3	hadoop	91
5	java	93
6	c	76
7	python	87
8	hadoop	88

-- 创建分桶表 
create table course( 
    id int, 
    name string, 
    score int 
)
clustered by (id) into 3 buckets 
row format delimited 
fields terminated by "\t";

-- 创建普通表 
create table course_common( 
    id int,
    name string,
    score int
)
row format delimited 
fields terminated by "\t"; 

-- 普通表加载数据 
load data local inpath '/home/hadoop/data/course.dat' into table course_common; 

-- 通过 insert ... select ... 给桶表加载数据 
insert into table course select * from course_common; 

-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区 
desc formatted course;

备注：

分桶规则：分桶字段.hashCode % 分桶数
分桶表加载数据时，使用 insert... select ... 方式进行
网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，那是Hive1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶；

第 6 节修改表 & 删除表

-- 修改表名。
rename alter table course_common 
rename to course_common1; 

-- 修改列名。change column 
alter table course_common1 
change column id cid int; 

-- 修改字段类型。change column
alter table course_common1 
change column cid cid string; 
-- The following columns have types incompatible with the existing columns in their respective positions 
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是 string不能转为int

-- 增加字段。add columns 
alter table course_common1 
add columns (common string); 

-- 删除字段：replace columns 
-- 这里仅仅只是在元数据中删除了字段，并没有改动 hdfs上的数据文件 
alter table course_common1 replace columns(
    id string,
    cname string,
    score int
); 

-- 删除表 
drop table course_common1;

HQL DDL命令小结：

主要对象：数据库、表
表的分类：
- 内部表：删除表时，同时删除元数据和表数据
- 外部表：删除表时，仅删除元数据，保留表中数据；生产环境多使用外部表
- 分区表：按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能
- 分桶表：按照分桶字段，将表中数据分开。分桶字段.hashCode % 分桶数据
主要命令：create、alter 、drop

HQL-数据操作

第 1 节数据导入

装载数据（load）

基本语法：

LOAD DATA [LOCAL] INPATH 'filepath' 
[OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOCAL：
- LOAD DATA LOCAL … 从本地文件系统加载数据到Hive表中。本地文件会拷贝到Hive表指定的位置
- LOAD DATA … 从HDFS加载数据到Hive表中。HDFS文件移动到Hive表指定的位置
INPATH：加载数据的路径
OVERWRITE：覆盖表中已有数据；否则表示追加数据
PARTITION：将数据加载到指定的分区

准备工作：

-- 创建表 
CREATE TABLE tabA (     
    id int,
    name string,
    area string
) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' ; 

-- 数据文件（~/data/sourceA.txt）： 
1,fish1,SZ
2,fish2,SH
3,fish3,HZ
4,fish4,QD
5,fish5,SR 
-- 拷贝文件到 
HDFS hdfs dfs -put sourceA.txt data/

装载数据：

-- 加载本地文件到hive(tabA) 
LOAD DATA LOCAL INPATH '/home/hadoop/data/sourceA.txt' INTO TABLE tabA; 
-- 检查本地文件 还在 

-- 加载hdfs文件到hive(tabA) 
LOAD DATA INPATH 'data/sourceA.txt' INTO TABLE tabA; 
-- 检查HDFS文件，已经被转移 

-- 加载数据覆盖表中已有数据 
LOAD DATA INPATH 'data/sourceA.txt' OVERWRITE INTO TABLE tabA;

-- 创建表时 加载数据 
hdfs dfs -mkdir /user/hive/tabB hdfs dfs -put sourceA.txt /user/hive/tabB 

CREATE TABLE tabB (     
    id INT,    
    name string,    
    area string 
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
Location '/user/hive/tabB';

插入数据（insert）

-- 创建分区表 
CREATE TABLE tabC ( 
    id INT,
    name string,
    area string
)partitioned by (month string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','; 

-- 插入数据 
insert into table tabC 
partition(month='202001') 
values (5, 'wangwu', 'BJ'), (4, 'lishi', 'SH'), (3, 'zhangsan', 'TJ'); 

-- 插入查询的结果数据 
insert into table tabC partition(month='202002') 
select
	id,
	name,
	area 
from tabC where month='202001'; 

-- 多表（多分区）插入模式 
from tabC 
insert overwrite table tabC partition(month='202003') 
select id, name, area where month='202002' 
insert overwrite table tabC partition(month='202004') 
select id, name, area where month='202002';
-- select 不用写from 在sql前已经写了

创建表并插入数据（as select）

-- 根据查询结果创建表 
create table if not exists tabD as 
select * from tabC; 
-- 这个插入 tabD 不是分区表

使用import导入数据

import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

第 2 节数据导入

-- 将查询结果导出到本地
-- 只有 overwrite 没有 into 这里是导出数据
-- local directory 为导入 linux本地文件路径
-- 这个语句没有指定数据分隔格式，导出文件内分隔为hive默认 `^A`。可使用 cat -A 命令查看
insert overwrite local directory '/home/hadoop/data/tabC'
select * from tabC;

-- 将查询结果格式化输出到本地 
insert overwrite local directory '/home/hadoop/data/tabC2' 
row format delimited
fields terminated by ' '
select * from tabC; 

-- 将查询结果导出到HDFS
-- 没有 local 则是hdfs上路径
insert overwrite directory '/user/hadoop/data/tabC3' 
row format delimited
fields terminated by ' '
select * from tabC; 

-- dfs 命令导出数据到本地。本质是执行数据文件的拷贝 
dfs -get /user/hive/warehouse/mydb.db/tabc/month=202001 /home/hadoop/data/tabC4 

-- hive 命令导出数据到本地。执行查询将查询结果重定向到文件
-- 使用 hive -e 命令是hql要加上 库名 ，否则默认查询 default 库
hive -e "select * from mydb.tabC" > a.log 

-- export 导出数据到 HDFS下。使用export导出数据时，不仅有数还有表的元数据信息 `_metadata`文件
export table tabC to '/user/hadoop/data/tabC4'; 

-- export 导出的数据，可以使用 import 命令导入到 Hive 表中
-- 使用 like tname创建的表结构与原表一致。
-- create ... as select ... 结构可能不一致
create table tabE like tabc;
-- 查看表结构
desc tabE;

import table tabE from ''/user/hadoop/data/tabC4';

清除数据

-- 截断表，清空数据。(注意：仅能操作 内部表 ) 
truncate table tabE; 

-- 以下语句报错，外部表 不能执行 truncate 操作 
alter table tabC set tblproperties("EXTERNAL"="TRUE");
truncate table tabC;

小结：

数据导入： load data / insert / create table .... as select ..... / import table

数据导出： insert overwrite ... diretory ... / hdfs dfs -get / hive -e "select ..." >

a.log / export table …

Hive的数据导入与导出还可以使用其他工具：Sqoop(阿帕奇)、DataX（阿里）等；

HQL-DQL命令【重点】

DQL – Data Query Language 数据查询语言

select语法：

SELECT [ALL | DISTINCT] select_expr, select_expr, ... 
    FROM table_reference 
    [WHERE where_condition]
    [GROUP BY col_list]
    [ORDER BY col_list]
    [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list]]
    [LIMIT [offset,] rows]

sql语句书写注意事项：

SQL语句对大小写不敏感
SQL语句可以写一行（简单SQL）也可以写多行(复杂SQL)
关键字不能缩写，也不能分行
各子句一般要分行
使用缩进格式，提高SQL语句的可读性(重要)

创建表，加载数据

-- 测试数据 /home/hadoop/data/emp.dat 
7369,SMITH,CLERK,7902,2010-12-17,800,,20 
7499,ALLEN,SALESMAN,7698,2011-02-20,1600,300,30 
7521,WARD,SALESMAN,7698,2011-02-22,1250,500,30 
7566,JONES,MANAGER,7839,2011-04-02,2975,,20 
7654,MARTIN,SALESMAN,7698,2011-09-28,1250,1400,30 
7698,BLAKE,MANAGER,7839,2011-05-01,2850,,30 
7782,CLARK,MANAGER,7839,2011-06-09,2450,,10 
7788,SCOTT,ANALYST,7566,2017-07-13,3000,,20 
7839,KING,PRESIDENT,,2011-11-07,5000,,10 
7844,TURNER,SALESMAN,7698,2011-09-08,1500,0,30 
7876,ADAMS,CLERK,7788,2017-07-13,1100,,20 
7900,JAMES,CLERK,7698,2011-12-03,950,,30 
7902,FORD,ANALYST,7566,2011-12-03,3000,,20 
7934,MILLER,CLERK,7782,2012-01-23,1300,,10 

-- 建表并加载数据 
CREATE TABLE emp ( 
    empno int, 
    ename string, 
    job string, 
    mgr int, 
    hiredate DATE, 
    sal int, 
    comm int, 
    deptno int 
)row format delimited fields terminated by ","; 

-- 加载数据 
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.dat' 
INTO TABLE emp;

第 1 节基本查询

-- 省略from子句的查询 
select 8*888 ; 
select current_date ; 

-- 使用列别名 as可省略
select 8*888 product; 
select current_date as currdate;

-- 全表查询 
select * from emp; 

-- 选择特定列查询 
select ename, sal, comm from emp; 

-- 使用函数 
select count(*) from emp; 
-- count(colname) 按字段进行count，不统计NULL 
select sum(sal) from emp; 
select max(sal) from emp; 
select min(sal) from emp; 
select avg(sal) from emp; 

-- 使用limit子句限制返回的行数 
select * from emp limit 3;

第 2 节 where 子句

WHERE子句紧随FROM子句，使用WHERE子句，过滤不满足条件的数据；

where 子句中不能使用列的别名；

select * from emp where sal > 2000;

where子句中会涉及到较多的比较运算和逻辑运算；

比较运算符

官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

比较运算符	描述
`=`、`==`、`<=>`	等于
<>、!=	不等于
<、<=、 >、>=	大于等于、小于等于
is [not] null	如果A等于NULL，则返回TRUE，反之返回FALSE。使用NOT关键字结果相反。
in(value1,value2, …)	匹配列表中的值
LIKE	简单正则表达式，也称通配符模式。‘x%’ 表示必须以字母 ‘x’ 开头；’%x’表示必须以字母’x’结尾；’%x%‘表示包含有字母’x’，可以位于字符串任意位置。使用NOT关键字结果相反。% 代表匹配零个或多个字符(任意个字符)；_ 代表匹配一个字符。
[NOT] BETWEEN … AND …	范围的判断，使用NOT关键字结果相反。
RLIKE、REGEXP	基于java的正则表达式，匹配返回TRUE，反之返回FALSE。匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。

备注：

通常情况下NULL参与运算，返回值为NULL；

NULL<=>NULL的结果为true

逻辑运算符

就是我们所熟悉的：and、or、not

-- 比较运算符，null参与运算 
select null=null;
OK
NULL

select null==null;
OK
NULL

select null<=>null;
OK
true
-- <=>null 类似 is null

-- 使用 is null 判空 
select * from emp where comm is null; 

-- 使用 in 
select * from emp where deptno in (20, 30); 

-- 使用 between ... and ... 
select * from emp where sal between 1000 and 2000; 

-- 使用 like 
select ename, sal from emp where ename like '%L%'; 

-- 使用 rlike。正则表达式，名字以A或S开头 
select ename, sal from emp where ename rlike '^(A|S).*';

第 3 节 group by子句

GROUP BY语句通常与聚组函数一起使用，按照一个或多个列对数据进行分组，对每个组进行聚合操作。

-- 计算emp表每个部门的平均工资 
select deptno, avg(sal) 
from emp 
group by deptno; 

-- 计算emp每个部门中每个岗位的最高薪水 
select deptno, job, max(sal) 
from emp 
group by deptno, job;

where子句针对表中的数据发挥作用；having针对查询结果（聚组以后的结果）发挥作用
where子句不能有分组函数；having子句可以有分组函数
having只用于group by分组统计之后

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

第 4 节表链接

Hive支持通常的SQL JOIN语句。默认情况下，仅支持等值连接，不支持非等值连接。

JOIN 语句中经常会使用表的别名。使用别名可以简化SQL语句的编写，使用表名前缀可以提高SQL的解析效率。

连接查询操作分为两大类：内连接和外连接，而外连接可进一步细分为三种类型：

1. 内连接: [inner] join 
2. 外连接 (outer join) 
    - 左外连接。 left [outer] join，左表的数据全部显示 
    - 右外连接。 right [outer] join，右表的数据全部显示 
    - 全外连接。 full [outer] join，两张表的数据都显示

在这里插入图片描述

案例演示：

-- 准备数据 
-- u1.txt数据： 
1,a
2,b
3,c
4,d 
5,e 
6,f 

-- u2.txt数据： 
4,d
5,e
6,f
7,g
8,h
9,i

create table if not exists u1( 
id int, 
name string) 
row format delimited fields terminated by ','; 

create table if not exists u2( 
id int, 
name string) 
row format delimited fields terminated by ','; 
load data local inpath '/home/hadoop/data/u1.txt' into table u1; 
load data local inpath '/home/hadoop/data/u2.txt' into table u2;

-- 内连接 
select * from u1 join u2 on u1.id = u2.id; 

-- 左外连接 
select * from u1 left join u2 on u1.id = u2.id; 

-- 右外连接 
select * from u1 right join u2 on u1.id = u2.id; 

-- 全外连接 
select * from u1 full join u2 on u1.id = u2.id;

多表连接

连接 n张表，至少需要 n-1 个连接条件。例如：连接四张表，至少需要三个连接条件。

多表连接查询，查询老师对应的课程，以及对应的分数，对应的学生：

select * 
from techer t 
left join course c on t.t_id = c.t_id 
left join score s on s.c_id = c.c_id 
left join student stu on s.s_id = stu.s_id;

Hive总是按照从左到右的顺序执行，Hive会对每对 JOIN 连接对象启动一个MapReduce 任务。

上面的例子中会首先启动一个 MapReduce job 对表 t 和表 c 进行连接操作；然后再启动一个 MapReduce job 将第一个 MapReduce job 的输出和表 s 进行连接操作；然后再继续直到全部操作；

笛卡儿积

满足以下条件将会产生笛卡尔集：

没有连接条件
连接条件无效
所有表中的所有行互相连接

如果表A、B分别有M、N条数据，其笛卡尔积的结果将有 M*N 条数据；缺省条件下hive不支持笛卡尔积运算；

hive.strict.checks.cartesian.product=false 参数防止笛卡儿积

set hive.strict.checks.cartesian.product=false; 
select * from u1, u2;

第 5 节排序子句【重点】

全局排序(order by)

order by子句出现在select语句的结尾；

order by子句对最终的结果进行排序；

默认使用升序(ASC)；可以使用DESC，跟在字段名之后表示降序；

ORDER BY执行全局排序，只有一个reduce；

-- 普通排序 
select * from emp order by deptno;

-- 按别名排序 
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno 
from emp 
order by salcomm desc; 

-- 多列排序 
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm, deptno 
from emp 
order by deptno, salcomm desc; 

-- 排序字段要出现在select查询字段中。以下语句无法执行（因为select查询字段中缺少 deptno）： 
select empno, ename, job, mgr, sal + nvl(comm, 0) salcomm 
from emp 
order by deptno, salcomm desc;

每个MR内部排序(sort by)

对于大规模数据而言order by效率低；

在很多业务场景，我们并不需要全局有序的数据，此时可以使用sort by；

sort by为每个reduce产生一个排序文件，在reduce内部进行排序，得到局部有序的结果；

-- 设置reduce个数 
set mapreduce.job.reduces=2; 

-- 按照工资降序查看员工信息 
select * from emp sort by sal desc; 

-- 将查询结果导入到文件中（按照工资降序）。生成两个输出文件，每个文件内部数据按 工资降序排列 
insert overwrite local directory '/home/hadoop/output/sortsal' 
select * from emp sort by sal desc;

分区排序(distribute by)

distribute by 将特定的行发送到特定的reducer中，便于后继的聚合与排序操作；

distribute by 类似于MR中的分区操作，可以结合sort by操作，使分区数据有序；

distribute by 要写在sort by之前；

-- 启动2个reducer task；先按 deptno 分区，在分区内按 sal+comm 排序 
set mapreduce.job.reduces=2; 

-- 将结果输出到文件，观察输出结果 
insert overwrite local directory '/home/hadoop/output/distBy' 
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm 
from emp 
distribute by deptno 
sort by salcomm desc; 

-- 上例中，数据被分到了统一区，看不出分区的结果
-- 因为mr分区是 字段值.hashcode()%分区数 {10%2=0，20%2=0}
-- 将数据分到3个区中，每个分区都有数据 
set mapreduce.job.reduces=3; 
insert overwrite local directory '/home/hadoop/output/distBy1' 
select empno, ename, job, deptno, sal + nvl(comm, 0) salcomm 
from emp 
distribute by deptno 
sort by salcomm desc;

Cluster By

当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法；

cluster by 只能是升序，不能指定排序规则，没有desc，asc；

-- 语法上是等价的 
select * from emp distribute by deptno sort by deptno; 
select * from emp cluster by deptno;

排序小结：

order by: 执行全局排序，效率低。生产环境中慎用
sort by: 使数据局部有序(在reduce内部有序)
distribute by: 按照指定的条件将数据分组，常与sort by 联用，使数据局部有序
cluster by: 当distribute by 与 sort by是同一个字段时，可使用cluster by简化语法

HQL-DML命令

数据操纵语言DML(Data Manipulation Language)，DML主要有三种形式：插入(INSERT)、删除(DELETE)、更新(UPDATE)。

事务(transaction)是一组单元化操作，这些操作要么都执行，要么都不执行，是一个不可分割的工作单元。

事务具有的四个要素：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这四个基本要素通常称为ACID特性。

原子性。一个事务是一个不可再分割的工作单位，事务中的所有操作要么都发生，要么都不发生。
一致性。事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
隔离性。在并发环境中，并发的事务是相互隔离的，一个事务的执行不能被其他事务干扰。即不同的事务并发操纵相同的数据时，每个事务都有各自完整的数据空间，即一个事务内部的操作及使用的数据对其他并发事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性。事务一旦提交，它对数据库中数据的改变就应该是永久性的。

第 1 节 Hive 事务

Hive：SQL=>MR

Hive从0.14版本开始支持事务和 行级更新，但缺省是不支持的，需要一些附加的配置。要想支持行级insert、update、delete，需要配置Hive支持事务。

Hive事务的限制：

Hive提供行级别的ACID语义
BEGIN、COMMIT、ROLLBACK 暂时不支持，所有操作自动提交
目前只支持 ORC 的文件格式
默认事务是关闭的，需要设置开启
要是使用事务特性，表必须是分桶的
只能使用内部表
如果一个表用于ACID写入（INSERT、UPDATE、DELETE），必须在表中设置表属性 : “transactional=true”
必须使用事务管理器 org.apache.hadoop.hive.ql.lockmgr.DbTxnManager
目前支持快照级别的隔离。就是当一次数据查询时，会提供一个数据一致性的快照
LOAD DATA语句目前在事务表中暂时不支持

HDFS是不支持文件的修改；并且当有数据追加到文件，HDFS不对读数据的用户提供一致性的。为了在HDFS上支持数据的更新：

表和分区的数据都被存在基本文件中（base files）
新的记录和更新，删除都存在增量文件中（delta files）
一个事务操作创建一系列的增量文件
在读取的时候，将基础文件和修改，删除合并，最后返回给查询

第 2 节 Hive 事务操作示例

-- 这些参数也可以设置在hive-site.xml中 
SET hive.support.concurrency = true; 

-- Hive 0.x and 1.x only 
SET hive.enforce.bucketing = true; 
SET hive.exec.dynamic.partition.mode = nonstrict; 
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; 

-- 创建表用于更新。满足条件：内部表、ORC格式、分桶、设置表属性 
create table zxz_data( 
    name string, 
    nid int, 
    phone string, 
	ntime date
) 
clustered by(nid) into 5 buckets 
stored as orc
tblproperties('transactional'='true'); 

-- 创建临时表，用于向分桶表插入数据 
create table temp1( 
    name string, 
    nid int, 
    phone string, 
    ntime date
) 
row format delimited 
fields terminated by ","; 

-- 数据 
name1,1,010-83596208,2020-01-01
name2,2,027-63277201,2020-01-02
name3,3,010-83596208,2020-01-03
name4,4,010-83596208,2020-01-04
name5,5,010-83596208,2020-01-05

-- 向临时表加载数据；向事务表中加载数据 
load data local inpath '/home/hadoop/data/zxz_data.txt' overwrite into table temp1; 
insert into table zxz_data select * from temp1; 

-- 检查数据和文件 
select * from zxz_data; 
dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; 

-- DML 操作 
delete from zxz_data where nid = 3; 

dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; 
-- 不支持 
insert into zxz_data values ("name3", 3, "010-83596208",current_date); 
-- 执行
insert into zxz_data values ("name3", 3, "010-83596208","2020-06-01");  
insert into zxz_data select "name3", 3, "010-83596208", current_date; 
dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; 
insert into zxz_data values 
("name6", 6, "010-83596208", "2020-06-02"), 
("name7", 7, "010-83596208", "2020-06-03"), 
("name8", 9, "010-83596208", "2020-06-05"), 
("name9", 8, "010-83596208", "2020-06-06"); 
dfs -ls /user/hive/warehouse/mydb.db/zxz_data ;

update zxz_data set name=concat(name, "00") where nid>3; 
dfs -ls /user/hive/warehouse/mydb.db/zxz_data ; 

-- 分桶字段不能修改，下面的语句不能执行 
-- Updating values of bucketing columns is not supported 
update zxz_data set nid = nid + 1;

Wang·Br

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
HQL操作语句

(返回hive笔记)[https://blog.csdn.net/weixin_44847293/article/details/109897984]HQL-DDL命令参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDLDDL（data definition language）：主要的命令有CREATE、ALTER、DROP等。DDL主要是用在定义、修改数据库对象的结构或数据类型。第 1 节数据库操作
复制链接

扫一扫