Hive基础的一些操作

最新推荐文章于 2024-07-14 10:38:08 发布

kiriko233

最新推荐文章于 2024-07-14 10:38:08 发布

阅读量29

点赞数

文章标签： hive hadoop 数据仓库

本文链接：https://blog.csdn.net/kiriko233/article/details/134276429

版权

Hive基础

数据库

创建数据库

	创建一个数据库，数据库在 HDFS 上默认的存储路径是 /hive/warehouse/*.db 。
		CREATE DATABASE shop;
	避免要创建的数据库已经存在错误，可以使用 IF NOT EXISTS 选项来进行判断。（标准写法）
		CREATE DATABASE IF NOT EXISTS crm;
	指定数据库创建的位置（数据库在 HDFS 上的存储路径）。
		CREATE DATABASE IF NOT EXISTS school location '/hive/school.db';

修改数据库

	修改对一个数据库的属性信息的描述
		ALTER DATABASE school SET DBPROPERTIES('createtime'='20220803');

查看数据库

	显示所有数据库。
		SHOW DATABASES;
	通过 like 进行过滤。
		SHOW DATABASES LIKE 's*';
	查看某个数据库的详情。
		DESC DATABASE school;
		DESCRIBE DATABASE school;
	切换数据库。
		USE school;

删除数据库

	最简写法。
		DROP DATABASE school;
	如果删除的数据库不存在，最好使用 IF EXISTS 判断数据库是否存在。否则会报错
		DROP DATABASE IF EXISTS school;
	如果数据库不为空，使用 CASCADE 命令进行强制删除。否则会报错
		DROP DATABASE IF EXISTS school CASCADE;

数据表

创建表

	特殊情况
		每一行数据的分隔符是多字节分隔符，例如 || 、 -- 等。
		数据的字段中包含了分隔符。
	解决方案
		替换分隔符。使用 MR 程序提前将数据清洗一遍（将多字节分隔符替换为单字节分隔符）
		RegexSerDe。Hive 内置了很多的 SerDe 类，可以使用 RegexSerDe 正则序列化器来处理。推荐使用。

写正则表达式,例如

CREATE TABLE IF NOT EXISTS t_singer (
id string,
name string,
country string,
province string,
gender string,
works string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "(\\d*)\\|\\|([\\u4e00-\\u9fa5\\w\\x20\\-]*)\\|\\|([\\u4e00-
\\u9fa5\\w\\x20\\-]*)\\|\\|([\\u4e00-\\u9fa5\\w\\x20\\-]*)\\|\\|([\\u4e00-\\u9fa5\\w\\x20\\-]*)\\|\\|
([\\u4e00-\\u9fa5\\w\\x20\\-]*)");

		自定义 InputFormat 继承 TextInputFormat，在 RecordReader 中对分割字符进行处理
			将自定义的 InputFormat 类打成 jar 包，例如 MyInputFormat.jar 。
			将 MyInputFormat.jar 放到 hive/lib 目录中或者使用 Hive 的 add jar 命令，然后就可以建表了。由于 Hive 是基于Hadoop 集群运行的，所以 hadoop/lib 目录中也必须放入 MyInputFormat.jar。

CREATE TABLE IF NOT EXISTS t_sleuth_log (
id int, name stirng, ...
)

– 指定分隔符

ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '

– 指定自定义的 InputFormat

STORED AS INPUTFORMAT 'com.yjxxt.hive.input'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

查看创建表时的语句

 SHOW CREATE TABLE t_person;

查询表

显示所有数据表。

SHOW TABLES;

可以通过 like 进行过滤

SHOW TABLES LIKE 't*';

查看某个数据表的详情。

DESC t_person;
DESC FORMATTED t_person;
DESCRIBE FORMATTED t_person;

重命名

ALTER TABLE old_table_name RENAME TO new_table_name;

内部表会同时修改文件目录，外部表因为目录是共享的，所以不会修改目录名称。

修改列

添加列
一次增加一个列(默认添加为最后一列)
ALTER TABLE table_name ADD COLUMNS (new_col INT);
可以一次增加多个列
ALTER TABLE table_name ADD COLUMNS (c1 INT, c2 STRING);
添加一列并增加列字段注释
ALTER TABLE table_name ADD COLUMNS (new_col INT COMMENT 'a comment');
更新列
ALTER TABLE table_name CHANGE old_col new_col STRING;
将列 a 的名称更改为 a1
ALTER TABLE table_name CHANGE a a1 INT;
将列 a1 的名称更改为 a2，将其数据类型更改为字符串，并将其放在列 b 之后
ALTER TABLE table_name CHANGE a1 a2 STRING AFTER b;
将 c 列的名称改为 c1，并将其作为第一列
ALTER TABLE table_name CHANGE c c1 INT FIRST;
清空表
TRUNCATE TABLE table_name;
清空表只能删除内部表的数据（HDFS 文件），不能删除外部表中的数据。

删除表

DROP TABLE table_name;

内外部表

内部表（managed table）
	创建 Hive 内部表时，数据将真实存在于表所在的目录内，删除内部表时，物理数据和文件也一并删除。
外部表（external table）
	管理仅仅只是在逻辑和语法意义上的，即新建表仅仅是指向一个外部目录而已。
		同样，删除时也并不物理删除外部目录，而仅仅是将引用和定义删除。

创建方案一

		创建时指定数据位置*

CREATE EXTERNAL TABLE IF NOT EXISTS test.t_user (
id int,
username string,
password string,
gender string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'

– 可以指定到某个目录（该目录下的文件都会被扫描到），也可以指定到某个具体的文件
LOCATION '/yjx/user';
如果 /yjx/user 目录不存在 Hive 会帮我们自动创建，我们只需要将 t_user 表所需的数据上传
至 /yjx/user 目录即可。或者数据已经存在于 HDFS 某个目录，Hive 创建外部表时直接指定数据位置即可。

创建方案二

		先建表再导入

CREATE EXTERNAL TABLE IF NOT EXISTS test.t_user2 (
id int,
username string,
password string,
gender string,
age int
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

LOAD DATA INPATH '/yjx/user/user.txt' INTO TABLE test.t_user2;
在这种情况下，执行 LOAD DATA 语句后 user.txt 文件将会被移动到 t_user2 表所在路径下
（ /hive/warehouse/test.db/t_user2/user.txt ）。唯一的区别是，外部表此时如果使用 DROP 语句删除
t_user2 后，只会删除元数据，也就是说 t_user2 目录和 user.txt 数据并不会被删除。

载入数据

基本语法：

LOAD DATA [LOCAL] INPATH 'datapath' [OVERWRITE] INTO TABLE student [PARTITION (partcol1=val1,…)];

关键词
LOAD DATA：加载数据
[LOCAL]：本地，不加 LOCAL 就是从 HDFS 获取
INPATH：数据的路径
'datapath'：具体的路径，要参考本地还是 HDFS
[OVERWRITE]：覆盖，不加 OVERWRITE 则追加数据
INTO TABLE：加入到表
student：表名
[PARTITION (partcol1=val1,…)]：分区
加载本地数据:
LOAD DATA LOCAL INPATH '/root/user.txt' INTO TABLE t_user;
要加载的文件必须和 HiveServer2 在同一个节点，否则会报错：
SemanticException Line 1:23 Invalid path''/root/test.txt'': No files matching path file
加载 HDFS 数据：
LOAD DATA INPATH '/yjx/user.txt' INTO TABLE t_user;
加载并覆盖已有数据：
LOAD DATA INPATH '/yjx/user.txt' OVERWRITE INTO TABLE t_user;

通过查询插入数据：
追加
INSERT INTO t_user1 SELECT id, username FROM t_user;
覆盖
INSERT OVERWRITE TABLE t_user1 SELECT id, username FROM t_user;
INSERT OVERWRITE TABLE t_user2 SELECT id, password FROM t_user;

将查询结果一次性存放到多张表（多重模式）
追加

FROM t_user
INSERT INTO t_user1 SELECT id, username
INSERT INTO t_user2 SELECT id, password;

覆盖

FROM t_user
INSERT OVERWRITE TABLE t_user1 SELECT id, username
INSERT OVERWRITE TABLE t_user2 SELECT id, password;

导出数据

通过 SQL 操作

将查询结果导出到本地

INSERT OVERWRITE LOCAL DIRECTORY '/root/user' SELECT * FROM t_user;

按指定的格式将数据导出到本地

INSERT OVERWRITE LOCAL DIRECTORY '/root/person'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '-'
MAP KEYS TERMINATED BY ':'
LINES TERMINATED BY '\n'
SELECT * FROM t_person;

将查询结果输出到 HDFS

INSERT OVERWRITE DIRECTORY '/export/user'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM t_user;

通过 HDFS 操作
使用 HDFS 命令拷贝文件到其他目录。

hdfs dfs -cp /hive/warehouse/t_person/* /yjx/export/person

将元数据和数据同时导出
将表结构和数据同时导出

EXPORT TABLE t_person TO '/yjx/export/person';

基本查询

内置运算符与函数

相关帮助命令：
SHOW FUNCTIONS;
– 查看系统自带函数
DESC FUNCTION UPPER;
– 显示某个函数的用法
DESC FUNCTION EXTENDED UPPER;
– 详细显示某个函数的用法
常用函数-数学函数
SELECT ABS(-1);
– 求绝对值
SELECT MOD(10, 3);
– 取余
SELECT FLOOR(3.666);
– 向下取整
SELECT CEILING(3.666);
– 向上取整
SELECT ROUND(3.666);
– 四舍五入
SELECT ROUND(3.666, 2);
– 四舍五入，保留两位小数
SELECT POW(3, 2);
– x 的 y 次方
SELECT PI();
– 圆周率
SELECT RAND();
– 生成 0~1 的随机小数
常用函数-字符串函数
SELECT ASCII('A');
– 字符转 ASCII 码
SELECT CHR(97);
– ASCII 码转字符
SELECT BASE64(ENCODE('abcde', 'UTF8'));
– 将二进制转换为 base64 编码
SELECT UNBASE64('YWJjZGU=');
– 将 base64 编码转换为二进制
SELECT length('123456'), char_length('123456'), character_length('123456');
– 字符串长度
SELECT LTRIM(' ABCDE '), RTRIM(' ABCDE '), TRIM(' ABCDE ');
– 去除指定位置的空格
SELECT SPACE(5);
– 产生 n 个空格的字符
SELECT SUBSTR('ABCDEFG', 2, 3);
– 从哪开始截取几个
SELECT REPLACE('ABCDE', 'AB', '12');
– 替换字符串
SELECT UPPER('abcde'), LOWER('ABCDE');
– 大小写转换
SELECT ename, LOWER(ename) FROM emp WHERE 1=1
– 将所有员工的姓名转化成小写
SELECT CONCAT_WS(',', 'A', 'B', 'C');
先转成字符串然后拼接字符串,会把第一个字符作为后面所有字符之间的连接
CONCAT_WS 不可以直接拼接 INT 类型，CONCAT_WS 只支持 STRING 或者 ARRAY
CONCAT_WS 拼接时，如果参数中有 NULL 会忽略 NULL 值
SELECT CONCAT('A', ',', 'B', ',', 'C');
CONCAT 可以直接将 INT 类型拼接成 STRING
CONCAT 拼接时，只要参数中有 NULL，不管有多少不为空的参数，结果都为 NULL
SELECT SPLIT('这个杀手不太冷,剧情-动作-犯罪', '-');
使用指定字符拆分字符串
常用函数-时间日期函数
SELECT CURRENT_DATE();
– 获取当前日期
SELECT CURRENT_TIMESTAMP();
– 获取当前日期时间
格式化日期时间，日期字符串必须满足 yyyy-MM-dd HH:mm:ss 格式
SELECT DATE_FORMAT('2020-06-01 14:52:21.123', 'yyyy-MM-dd HH:mm:ss.SSS');
SELECT DATE_FORMAT('2020-06-01 14:52:21', 'yyyy-MM-dd');
获取指定时间的 UNIX 时间戳
SELECT UNIX_TIMESTAMP('1/6/2020 14:52:21', 'd/M/yyyy HH:mm:ss');
SELECT UNIX_TIMESTAMP('01/06/2020 14:52:21', 'dd/MM/yyyy HH:mm:ss');
转化 UNIX 时间戳到当前时区的时间格式
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), 'yyyy-MM-dd HH:mm:ss.SSS');
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('1/6/2020 14:52:21', 'd/M/yyyy HH:mm:ss'), 'yyyyMMdd');
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('01/06/2020 14:52:21', 'dd/MM/yyyy HH:mm:ss'), 'yyyyMMdd');

获取特定的值 YEAR()/MONTH()/DAY()/HOUR()/MINUTE()/SECOND()

	SELECT YEAR(CURRENT_TIMESTAMP()),
	MONTH(CURRENT_TIMESTAMP()),
	DAY(CURRENT_TIMESTAMP()),
	HOUR(CURRENT_TIMESTAMP()),
	MINUTE(CURRENT_TIMESTAMP()),
	SECOND(CURRENT_TIMESTAMP());

日期字符串必须满足 yyyy-MM-dd HH:mm:ss 格式
SELECT YEAR('2022-08-05');
SELECT YEAR('2022-08-05 12:12:12.0');
SELECT DATE_SUB('2022-08-05 12:12:12.0', 1);
– 返回日期前 n 天的日期
SELECT DATE_ADD('2022-08-05 12:12:12.0', 1);
– 返回日期后 n 天的日期
SELECT DATEDIFF('2022-08-05 12:12:12.0', '2022-07-05 12:12:12.0');
– 返回开始日期减去结束日期的天数
SELECT NEXT_DAY('2022-08-05 12:12:12.0', 'SUNDAY');
– 得到一个字符串日期的下周几的具体日期
SELECT LAST_DAY('2022-08-05');
– 当月的最后一天

常用函数-URL解析
解析 URL，返回 PROTOCOL 请求协议
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'PROTOCOL');
解析 URL，返回 HOST 主机
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'HOST');
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'PATH');
解析 URL，返回 PATH 请求地址
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'QUERY')
解析 URL，返回 QUERY 请求参数
SELECT PARSE_URL('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为', 'QUERY', 'wq');
解析 URL，返回 QUERY 指定参数的值
SELECT PARSE_URL_TUPLE('https://search.jd.com/Search?keyword=华为&enc=utf-8&wq=华为','HOST', 'PROTOCOL', 'PATH', 'QUERY', 'QUERY:wq');
常用函数-正则
REGEXP_EXTRACT(str, regexp[, idx])
将字符串 str 按照 regexp 正则表达式的规则拆分，返回 idx 指定的字符。
将字符串按照正则表达式的规则拆分，返回 index 指定的字符，1 表示返回正则表达式中第一个()对应的结果，以此类推

SELECT REGEXP_EXTRACT('2022-08-11 14:15:25.326 [gateway-server,,] [reactor-http-nio-2] DEBUG',
'([\\d\\x20-:\\.]*) (.*) (.*) ([a-zA-Z]*)', 1);
REGEXP_REPLACE(str, regexp, rep)

将字符串 str 中的符合 regexp 正则表达式的部分替换为 rep。

查询语法

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

查询语句执行顺序

		(7)    SELECT
		(8)    DISTINCT <select_list>
		(1)    FROM <left_table>
		(3)    <join_type> JOIN <right_table>
		(2)    ON <join_condition>
		(4)    WHERE <where_condition>
		(5)    GROUP BY <group_by_list>
		(6)    HAVING <having_condition>
		(9)    ORDER BY <order_by_condition>
		(10)   LIMIT <limit_number>

排序

全局排序：ORDER BY
全局排序是在一个 Reduce 中进行排序的。

内部排序：SORT BY
只在每个 Reducer 内部进行排序
SET mapred.reduce.tasks;
查看 Reduce 的个数
SET mapred.reduce.tasks=3;
设置 Reduce 的个数
SELECT * FROM emp SORT BY sal DESC;
局部排序

分区排序：DISTRIBUTE BY
控制某个特定行应该到哪个 Reducer，通常是为了进行后续的聚集操作。
DISTRIBUTE BY 类似 MR 中的 PARTITION（自定义分区），进行分区，一般结合 SORT BY 使用（注意：DISTRIBUTE BY
要在 SORT BY 之前）。

组合排序：CLUSTER BY
除了具有 DISTRIBUTE BY 的功能外还兼具 SORTS BY 的功能
所以当 DISTRIBUTE BY 和 SORT BY 字段相同时，可以使用 CLUSTER BY 方式。
但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC

kiriko233

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Hive基础的一些操作

唯一的区别是，外部表此时如果使用 DROP 语句删除。将字符串按照正则表达式的规则拆分，返回 index 指定的字符，1 表示返回正则表达式中第一个()对应的结果，以此类推。如果 /yjx/user 目录不存在 Hive 会帮我们自动创建，我们只需要将 t_user 表所需的数据上传。t_user2 后，只会删除元数据，也就是说 t_user2 目录和 user.txt 数据并不会被删除。获取特定的值 YEAR()/MONTH()/DAY()/HOUR()/MINUTE()/SECOND()
复制链接

扫一扫