Hive中常用的交互式命令
hive -help
查看交互式命令hive -e
不进入hive
的交互窗口执行sql
语句;hive -f
执行脚本中的sql
语句,其中使用>
输出到指定目录下的文件当中(定时任务);exit
先隐性提交数据,再退出;quit
不提交数据,退出;
参数配置方式
- 配置文件方式
默认配置文件:hive-default.xml
用户自定义配置文件:hive-site.xml
注意:用户自定义配置会覆盖
默认配置。 - 命令行参数方式
启动Hive
时,可以在命令行添加-hiveconf param=value
来设定参数
注意:仅对本次hive
启动有效。 - 参数声明方式
可以在HQL
中使用SET
关键字设定参数
注意:仅对本次hive
启动有效。
上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。
Hive
数据类型
基本数据类型
Hive数据类型 | Java数据类型 | 长度 | 例子 |
TINYINT | byte | 1byte有符号整数 | 20 |
SMALINT | short | 2byte有符号整数 | 20 |
INT | int | 4byte有符号整数 | 20 |
BIGINT | long | 8byte有符号整数 | 20 |
BOOLEAN | boolean | 布尔类型 | TRUE FALSE |
FLOAT | float | 单精度浮点数 | 3.14159 |
DOUBLE | double | 双精度浮点数 | 3.14159 |
STRING | string | 字符系列 | ‘hello’ OR “world!” |
TIMESTAMP | 时间类型 | ||
BINARY | 字节数组 |
复杂数据类型
数据类型 | 描述 |
STRUCT | 和c语言中的struct类似,都可以通过“点”符号访问元素内容。例如,如果某个列的数据类型是STRUCT{first STRING, last STRING},那么第1个元素可以通过字段.first来引用。 |
MAP | MAP是一组键-值对元组集合,使用数组表示法可以访问数据。例如,如果某个列的数据类型是MAP,其中键->值对是’first’->’John’和’last’->’Doe’,那么可以通过字段名[‘last’]获取最后一个元素 |
ARRAY | 数组是一组具有相同类型和名称的变量的集合。这些变量称为数组的元素,每个数组元素都有一个编号,编号从零开始。例如,数组值为[‘John’, ‘Doe’],那么第2个元素可以通过数组名[1]进行引用。 |
样例:
name string,
friends array<string>,
children map<string, int>,
address struct<street:string, city:string>
)
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP中的key与value的分隔符
lines terminated by '\n'; -- 行分隔符
类型转换
例如:CAST('1' AS INT)
将把字符串’1’ 转换成整数1;如果强制类型转换失败,如执行CAST('X' AS INT)
,表达式返回空值 NULL
。
DDL
数据定义
- 数据库创建
create database if not exists db_hive location '/db_hive2.db';
– 脚本中按照某种规律来建库并放在指定位置,避免db_hive already exists
异常; - 数据库删除
drop database if exists db_hive2;
– 删除空数据库drop database db_hive cascade;
–删除数据库,不管是不是空 - 创建表
[(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 row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
1.CREATE TABLE
创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS
选项来忽略这个异常。
2. EXTERNAL
关键字可以让用户创建一个外部表,在建表的同时指定一个指向实际数据的路径(LOCATION)
,Hive
创建内部表时,会将数据移动到数据仓库指向的路径;若创建外部表,仅记录数据所在的路径,不对数据的位置做任何改变。在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。
3.COMMENT
:为表和列添加注释。
4. PARTITIONED BY
创建分区表
5. CLUSTERED BY
创建分桶表
6. SORTED BY
不常用
7.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
或者使用自带的SerDe
。如果没有指定ROW FORMAT
或者ROW FORMAT DELIMITED
,将会使用自带的SerDe
。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe
,Hive
通过SerDe
确定表的具体的列的数据。SerDe是Serialize/Deserilize
的简称,目的是用于序列化和反序列化。
8. STORED AS
指定存储文件类型
常用的存储文件类型:SEQUENCEFILE
(二进制序列文件)、TEXTFILE
(文本)、RCFILE
(列式存储格式文件)
如果文件数据是纯文本,可以使用STORED AS TEXTFILE
。如果数据需要压缩,使用 STORED AS SEQUENCEFILE
。
9.LOCATION
:指定表在HDFS
上的存储位置。
10.LIKE
允许用户复制现有的表结构,但是不复制数据。
内部表,Hive
会(或多或少地)控制着数据的生命周期。Hive
默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir
(例如,/user/hive/warehouse
)所定义的目录的子目录下。当我们删除一个内部表时,Hive
也会删除这个表中数据。管理表不适合和其他工具共享数据。
- 根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student3 as select id, name from student;
- 根据已经存在的表结构创建表
create table if not exists student4 like student;
外部表(EXTERNAL
声明),Hive
并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。
- 使用场景
每天将收集到的网站日志定期流入HDFS
文本文件。在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表
使用内部表存储,数据通过SELECT+INSERT
进入内部表。
- 外部数据写入
load data local inpath '/opt/module/datas/dept.txt' into table default.dept;
内MANAGED_TABLE
、外EXTERNAL_TABLE
部表转换
- 修改内部表
user_log
为外部表alter table user_log set tblproperties('EXTERNAL'='TRUE');
- 修改外部表
user_log
为内部表alter table user_logset tblproperties('EXTERNAL'='FALSE');
分区表,实际上就是对应一个HDFS
文件系统上的独立的文件夹,该文件夹下是该分区所有的数据文件。Hive
中的分区就是分目录,把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE
子句中的表达式选择查询所需要的指定的分区,这样的查询效率会提高很多。
DML
数据定义
- 数据导入
hive> load data [local] inpath '/opt/module/datas/student.txt' 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:
表示上传到指定分区
(0)创建一张表
试例
- 创建一张表
hive (default)> create table user_info(id string, name string) row format delimited fields terminated by '\t';
(1)加载本地文件到hive
hive (default)> load data local inpath '/opt/module/datas/user_info.txt' into table default.user_info;
(2)加载HDFS
文件到hive
中
上传文件到HDFShive (default)> dfs -put /opt/module/datas/user_info.txt /user/atguigu/hive;
加载HDFS
上数据hive (default)> load data inpath '/user/atguigu/hive/user_info.txt' into table default.user_info;
(3)加载数据覆盖表中已有的数据
上传文件到HDFS
hive (default)> dfs -put /opt/module/datas/user_info.txt /user/atguigu/hive;
加载数据覆盖表中已有的数据hive (default)> load data inpath '/user/atguigu/hive/user_info.txt' overwrite into table default.user_info;
- 使用核心
having与where不同点
(1)where
针对表中的列发挥作用,查询数据;having
针对查询结果中的列发挥作用,筛选数据;
(2)同group by
同时使用时,where
先过滤后聚合,having
先聚合后过滤;
(3)having
只用于group by
分组统计语句。Join
连接
行转列
CONCAT(string A/col, string B/col…):
返回输入字符串连接后的结果,支持任意个输入字符串;CONCAT_WS(separator, str1, str2,...):
它是一个特殊形式的CONCAT()
。第一个参数剩余参数间的分隔符
。分隔符可以是与剩余参数一样的字符串。如果分隔符是NULL
,返回值也将为NULL
。这个函数会跳过分隔符参数后的任何NULL
和空字符串。分隔符将被加到被连接的字符串之间;COLLECT_SET(col)
:函数只接受基本数据类型,它的主要作用是将某字段的值进行去重汇总
,产生array类型字段。
列转行
EXPLODE(col):
将hive
一列中复杂的array
或者map
结构拆分成多行。LATERAL VIEW
用法:LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释:用于和split, explode
等UDTF
一起使用,它能够将一列数据拆成多行数据,在此基础上可以对拆分后的数据进行聚合。
窗口函数
OVER():
指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化CURRENT ROW:
当前行n PRECEDING:
往前n
行数据n FOLLOWING:
往后n
行数据UNBOUNDED:
起点,UNBOUNDED PRECEDING
表示从前面的起点,UNBOUNDED FOLLOWING
表示到后面的终点LAG(col,n):
往前第n
行数据LEAD(col,n):
往后第n
行数据NTILE(n):
把有序分区中的行分发到指定数据的组中,各个组有编号,编号从1
开始,对于每一行,NTILE
返回此行所属的组的编号。注意:n
必须为int
类型。
排序
RANK() :
排序相同时会重复,总数不会变DENSE_RANK():
排序相同时会重复,总数会减少ROW_NUMBER():
会根据顺序计算