Hive相关
# 数据从本地导入
表格信息:
QD01012018070009 张先生 M 山东科技大学 计算机
QD01012017050004 test张飞 M 北京邮电大学 软件工程
QD01012017030001 学员秦小建 M 北京邮电大学 软件工程
QD01012017050003 小四 M 北京邮电大学 软件工程
QD01012017050006 thomas M 哈弗 物理
QD01012017050007 hello kity F 哈弗 物理
QD01012017050002 小星 M 深大 计算机
QD010120180011 12345 M 12345 12345
QD01012017050005 呵呵 F 北京化工大学 信息工程
QD01012018070008 测试学生 M qd rg
QD010120180010 123 F 123 123
创建表格:
create table student_2(code string,name string,gender string,school string,profession string)
comment 'this is a student table'
row format delimited fields terminated by '\t'
stored as textfile;
create table stu_kongge(id int, name string)
row format delimited fields terminated by ' ';
从本地导入数据:
load data local inpath '/soft/module/datas/short-student-utf8.txt' into table student_2;
查看：hadoop fs -cat /user/hive/warehouse/test_db.db/student_2/short-student-utf8.txt
# 数据从hdfs导入
load data inpath '/ahb/datas/short-student-utf8_add.txt' into table student;
# 表格操作
清除表内容: truncate table student;
删除表: drop table student;
重命名: alter table student_2 rename to student;
添加列: alter table student add columns (grade string,class string); --导入数据: 先清空表,再导入整理好的数据
更改列顺序: ALTER TABLE 表名 CHANGE 列名 列名 STRING COMMENT '列注释' AFTER 列名;
删除列: alter table student drop column class string;
alter table student replace columns (code string,name string,gender string,school string,profession string,grade string);
修改列名与类型: alter table student change class banji double;(String转换为Double) --无法double to int转换为String to int或double to int转换为double to int 。
替换列属性:
更新列:
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
增加和替换列:
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
# 数据操作
数据查询 select school,count(*) from student_2 group by school;
数据更新 update student set grade='17' where 1=1; --配置文件和建表时有要求
# hive复合(集合)数据类型
- 建表
create table t5_struct(
id int,
name string,
info struct<city: string ,subway:string > )
row format delimited fields terminated by '\t'
collection items terminated by ',';
数据格式
{
"name": "songsong",
"friends": ["bingbing" , "lili"], //列表 Array
"children": { //键值对Map
"xiao song": 18 , "xiaoxiao song": 19
}
"address":{ //结构体Struct
"street": "hui long guan",
"city": "beijing"
}
}
- 创建表:[ terminate:终止,结束 ] [ street:街道 ]
create table people(
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 keys terminated by ':';
字段解释：
row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':' -- MAP 中的key 与 value 的分隔符
lines terminated by '\n'; -- 行分隔符
数据格式
张伟,黄辉鸿_曾小贤_胡一菲,张小伟:18_张大伟:24,屌丝路_上海
吕小布,陈美嘉_唐悠悠_关谷神奇,小小布:20_吕小小:25,幸福里_上海
# 管理表和外部表
理论
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS 文本文件。在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。
建外部表
create external table if not exists default.emp( empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited fields terminated by '\t';
管理表和外部表相互转换
内部表转为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
外部表转为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
# 分区表(分目录)
建表
create table student2(code string,name string,gender string,school string,profession string,grade string)
partitioned by (class int)
row format delimited fields terminated by '\t'
stored as textfile;
加载数据
load data local inpath "/soft/datas/short-student-utf8_classNO1.txt" into table student2 partition(class=1)
扩展
多分区联合查询
增加分区
创建单个分区
alter table student2 add partition(class=3);
同时创建多个分区
alter table studet2 add partition(class=4) partition(class=5)
删除分区
删除单个分区
alter table student2 drop partition(class=3);
同时删除多个分区
alter table student2 drop partition(class=4),partition(class=5);
查看分区表有多少分区
show partitions student2;
查看分区表结构
desc formatted student2;
批量删除分区
alter table schema.table_name drop partition (ds<'2018-08-01');
批量删除分区脚本
#!/bin/sh
if [ $# == 3 ]; then
begin_date=`date -d "+0 day $2" +%Y-%m-%d`
end_date=`date -d "+0 day $3" +%Y-%m-%d`
date=${end_date}
sql=''
while [[ "${date}" > "${begin_date}" || "${date}" = "${begin_date}" ]]
do
echo $date
sql=${sql}"ALTER TABLE $1 DROP IF EXISTS PARTITION(date = '$date');"
echo ${sql}
date=`date -d "$date -1 days" +"%Y-%m-%d"`
done
echo "hive -e '${sql}' "
hive -e "${sql}"
elif [ $# == 1 ]; then
date=`date -d -1days '+%Y-%m-%d'`
echo "hive -e 'ALTER TABLE $1 DROP IF EXISTS PARTITION(date = '$date');'"
hive -e "ALTER TABLE $1 DROP IF EXISTS PARTITION(date = '$date');"
else
echo 'Parameter error!'
fi
————————————————
原文链接：https://blog.csdn.net/fanlying/article/details/78688003
# 分区表注意事项
1.创建二级分区表
create table student2(code string,name string,gender string,school string,profession string)
partitioned by (grade string,class int)
row format delimited fields terminated by '\t';
2.加载到二级分区表
load data local inpath "" into table student2 partition2 partition(grade="18",class=1);
select * from student2 where grade="" and class=""
动态分区示例：源表字段和输出分区值之间的关系是根据位置而不是根据命名来匹配的，也可以静态分区和动态分区混用
insert overwrite table 目标表 partition(bdp_day, bdp_year)
select
rel.release_session,
rel.release_status,
rel.device_num,
rel.device_type,
rel.sources,
rel.channels,
from_unixtime(rel.ct,'HH'),
from_unixtime(rel.ct,'yyyy-MM-dd HH:mm:ss'),
from_unixtime(rel.ct,'yyyy-MM-dd') as bdp_day -- 分区字段
from_unixtime(rel.ct,'yyyy') as x_year -- 二级分区
from 源表
使用动态分区表必须配置的参数 ：
set hive.exec.dynamic.partition =true --（默认false）,表示开启动态分区功能
set hive.exec.dynamic.partition.mode = nonstrict -- (默认strict),表示允许所有分区都是动态的，否则必须有静态分区字段
动态分区相关的调优参数：
set hive.exec.max.dynamic.partitions.pernode=100 （默认100，一般可以设置大一点，比如1000）
表示每个maper或reducer可以允许创建的最大动态分区个数，默认是100，超出则会报错。
set hive.exec.max.dynamic.partitions =1000(默认值)
表示一个动态分区语句可以创建的最大动态分区个数，超出报错
set hive.exec.max.created.files =10000(默认) 全局可以创建的最大文件个数，超出报错。
# 分桶表
注：分区针对的是数据的存储路径；分桶针对的是数据文件。 分桶后，桶内有序，整体不一定有序。
数据加载通过hdfs dfs -put文件或者通过load data均不好使 原因：数据上传到hdfs上，这个过程怎么识别分桶字段，有怎么取hash值呢（必须经过MapReduce） load是put上去的
缺点: 如果通过数据文件LOAD 到分桶表中，会存在额外的MR负担。 实际生产中分桶策略使用频率较低，更常见的还是使用数据分区。
- 分区针对的是数据的存储路径；分桶针对的是数据文件。
- Hive Load语句不会在加载数据的时候做任何转换工作，而是纯粹的把数据文件复制/移动到Hive表对应的地址。
- 分桶表 加载数据 From Select 是经过MR(映射和归纳)的
1.数据准备
2.创建分桶表
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';
3.查看表结构
desc formatted stu_buck;
4. 导入数据到分桶表
load data local inpath "/soft/datas/student.txt" into table stu_buck;
===================不好使====================
创建分桶表，数据通过子查询方式导入
1.先创建普通表
create table stu(id int, name string)
row format delimited fields terminated by '\t';
2.导入数据到普通表
load data local inpath '/soft/datas/student.txt' into table stu;
3.创建分桶表(清空分桶表)
truncate table stu_buck;
select * from stu_buck;
4.通过子查询的方式导入数据到分桶表
insert into table stu_buck select id, name from stu;
5.发现还是只有一个分桶
6.设置属性
1.set hive.enforce.bucketing=true; #设置让hive强制分桶，自动按照分桶表的bucket 进行分桶 [程序自动分配reduce的数量从而适配相应的bucket;]
2.set mapreduce.job.reduces=-1; #-1是不指定reduce数量
insert into table stu_buck select id, name from stu;
7.查询分桶数据
select * from stu_buck
分桶抽样查询
select * from stu_buck tablesample(bucket 1 out of 4 on id);
注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。
y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。
x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2 个
bucket 的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。注意：x 的值必须小于等于 y 的值，否则
FAILED: SemanticException [Error 10061]:
# 索引(待添加)
# 系统函数
1.查看系统自带函数 show functions;
2.显示自带函数用法 desc function upper;
3.详细显示自带的函数的用法 desc function extended upper
# 数学函数
点击查看
select round(-2.5); #四舍五入
select round(0.131415926,3); #保留n位小数
select bround(2.5); select bround(3.5); #2 4银行家舍入法(四舍六入五取偶)
银行家舍入法:四舍六入五考虑，五后非空就进一，五后为空看奇偶，五前为偶应舍去，五前为奇要进一
select bround(2.55554,3); #2.556 银行家舍入法,保留d位小数
select floor(-2.54); #向下取整(即在数轴上左边的整数)
select ceil(-2.54); #向上取整
select rand(); select rand(2); #返回一个DOUBLE型随机数
select exp(2.5); #e的a幂次方
select ln(2.5); #自然数为底d的对数
select log10(10.0); #以10为底d的对数
select log2(2.0); #以2为底数d的对数
select log(3,9); #select log(DOUBLE base, DOUBLE a)以base为底的对数
select pow(2,10); #a的p次幂
select sqrt(81); #a的平方根
select bin(5); select bin(15); #二进制a的STRING类型 bin(BIGINT a)
select hex(15); #十六进制a的STRING类型 hex的逆方法 unhex(STRING a)
select conv(15,10,2); #把十进制的15转换为二进制
select abs(-3); #绝对值
select pmod(13,4); #取模
select sin(60); #正弦值
select asin(1); #反正弦值 定义域[-1,1]
select e(); # 求e
select pi(); # 求pi
select factorial(5); #阶乘
# 日期函数
点击查看
#获取当前时间戳
select unix_timestamp();
#UNIX时间戳转日期函数
select from_unixtime(1585294471,'yyyy-MM-dd hh:mm:ss');
#日期转UNIX时间戳函数 指定格式日期转UNIX时间戳函数
select unix_timestamp("2020-03-27 03:34:31");
select unix_timestamp("2020-03-27 03:34:31","yyyy-MM-dd hh:mm:ss");
#日期时间转日期
select to_date('2020-03-27 03:34:31');
#日期取年 select year('2020-03-27 03:34:31');
#日期取月 select month('2020-03-27 03:34:31');
#日期取日 select day('2020-03-27 03:34:31');
#日期取时 select hour('2020-03-27 03:34:31');
#日期取分 select minute('2020-03-27 15:34:31');
#日期取秒 select second('2020-03-27 15:34:31');
#日期转周 select weekofyear('2020-03-27 15:34:31');
#日期比较 select datediff("2020-3-27","2020-3-2");
#日期增加 select date_add("2020-3-27",25);
#日期减少 select date_sub("2020-3-27",25);
# 字符串函数
点击查看
--字符串模糊匹配
-- LIKE、RLIKE、REGEXP、regexp_replace、regexp_extract
-- 字符串长度 select length("hello world");
-- 字符串反转 select reverse("洛河飘香茶香飘河洛!");
-- 字符串连接 select concat("Hello","World");
-- 带分隔符字符串连接 select concat_ws("\t","name","age","sex");
-- 字符串截取 substr(str,start),substring(str,start,len)
select substring("洛河飘香茶香飘河洛",4);
select substring("洛河飘香茶香飘河洛",4,3);
-- 字符串转大写函数 upper,ucase
select upper("Hello World");
-- 字符串转小写函数： lower,lcase
select lcase("Hello World");
-- 去空格(两边) select trim(" Hello World ");
-- 左去空格 select ltrim(" Hello World ");
-- 右去空格 select rtrim(" Hello World ");
-- 正则表达式替换 将字符串A中的符合java正则表达式B的部分替换为C regexp_replace(string A, string B, string C)
select regexp_replace('hello world', 'or|ll', ' ');
-- 正则表达式解析 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符 regexp_extract(string subject, string pattern, int index)
-- 注意，在有些情况下要使用转义字符，下面的等号要用双竖线转义，这是java正则表达式的规则
select regexp_extract('foothebar', 'foo(.*?)(bar)', 1);
select regexp_extract('foothebar', 'foo(.*?)(bar)', 2);
-- URL解析函数 parse_url(string urlString, string partToExtract [, string keyToExtract])
-- 返回URL中指定的部分。partToExtract的有效值为：HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO
select parse_url("https://www.bilibili.com/video/BV1yE411v7Pq/?spm_id_from=333.788.videocard.1","HOST");
-- json解析函数 解析json的字符串json_string,返回path指定的内容。如果输入的json字符串无效，那么返回NULL
select get_json_object('{"store":
{"fruit":\[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],
"bicycle":{"price":19.95,"color":"red"}
},
"email":"amy@only_for_json_udf_test.net",
"owner":"amy"
}','$.owner');
-- 空格字符串函数 返回长度为n的字符串 select space(10); select length(space(10));
-- 重复字符串函数 返回重复n次后的str字符串 select repeat('hello',3);
-- 首字符ascii函数 返回字符串str第一个字符的ascii码 select ascii("hello");
-- 左补足函数 将str进行用pad进行左补足到len位 lpad(string str, int len, string pad) select lpad("string",10,"do"); #dodostring
-- 右补足函数 select rpad("string",10,"do");
-- 分割字符串函数 按照pat字符串分割str，会返回分割后的字符串数组 split(string str, string pat)
select split("pneumonoultramicroscopicsilicovolcanoconiosis","n");
结果:["p","eumo","oultramicroscopicsilicovolca","oco","iosis"]
-- 集合查找函数 str在strlist第一次出现的位置，strlist用逗号分割,0未找到 find_in_set(string str, string strList)
select find_in_set('ab','ef,ab,de');
# 聚合函数
点击查看
count(),max(),min(),sum(),avg() 等常用的聚合函数
注意：
聚合操作时要注意 null 值
count(*) 包含 null 值，统计所有行数
count(id) 不包含 null 值
min 求最小值是不包含 null，除非所有值都是 null
avg 求平均值也是不包含 null
-- 非空集合总体变量函数: var_pop
-- 非空集合样本变量函数: var_samp
-- 总体标准偏离函数: stddev_pop
-- 中位数函数: percentile
# 其他（运算符、字符匹配、条件、统计、复杂类型）
点击查看
关系运算 =. <. >. IS NULL. LIKE. PLIKE. REGEXP
数学运算+ - * / % & | ~ ^
逻辑运算AND OR NOT
条件函数
-- if(boolean testCondition, T valueTrue, T valueFalseOrNull)
-- 条件判断函数：case when
-- 非空查找函数:coalesce(T v1, T v2, …)
集合统计函数等等...count 直方图: histogram_numeric 中位数函数: percentile 总体标准偏离函数: stddev_pop
复合类型构建map. struct. array
-- Map类型构建:map (key1, value1, key2, value2, …)
-- Struct 类型构建:struct(val1, val2, val3, …)
-- array 类型构建:array(val1, val2, …)
复杂类型访问操作A[n] M[key] S.x
-- array 类型访问:语法: A[n]
-- map 类型访问:语法: M[key]
-- struct 类型访问:语法: S.x
复杂类型长度统计函数 size(Map<k .V>) size(Array<T>) 类型转换函数
-- Map 类型长度函数:语法: size(Map<k .V>)
-- array 类型长度函数:语法: size(Array<T>)
-- 类型转换函数: cast 语法: cast(expr as <type>)
# lateral view 与 explode 以及 reflect（待完善）
# 窗口函数
点击查看
# SUM、AVG、MIN、MAX
# ROW_NUMBER、RANK、DENSE_RANK、NTILE
-- ROW_NUMBER()： ROW_NUMBER()从 1 开始，按照顺序，生成分组内记录的序列
-- RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位。
-- DENSE_RANK()生成数据项在分组中的排名，排名相等会在名次中不会留下空位。
-- ntile 可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差 1。
# LAG、LEAD、FIRST_VALUE、LAST_VALUE
-- LAG(col,n,DEFAULT) 用于统计窗口内往上第 n 行值。
-- LEAD(col,n,DEFAULT) 用于统计窗口内往下第 n 行值。
-- FIRST_VALUE 取分组内排序后，截止到当前行，第一个值。
-- LAST_VALUE 取分组内排序后，截止到当前行，最后一个值。
# CUME_DIST
此函数的结果和 order by 的排序顺序有关系。CUME_DIST：小于等于当前值的行数/分组内总行数。 order 默认顺序 ：正序
比如，统计小于等于当前薪水的人数，所占总人数的比例。
# GROUPING SETS、GROUPING__ID、CUBE、ROLLUP
-- grouping sets 是一种将多个 group by 逻辑写在一个 sql 语句中的便利写法。等价于将不同维度的 GROUP BY 结果集进行 UNION ALL。
-- CUBE ： 根据 GROUP BY 的维度的所有组合进行聚合。
-- ROLLUP ： 是 CUBE 的子集，以最左侧的维度为主，从该维度进行层级聚合。比如，以 month 维度进行层级聚合：
# 自定义函数(UDF、UDAF、UDTF)
- UDF（User-Defined-Function） 一进一出
- UDAF（User-Defined Aggregation Function） 聚集函数，多进一出 类似于：count/max/min
- UDTF（User-Defined Table-Generating Functions） 一进多出
UDF
https://www.cnblogs.com/swordfall/p/11167486.html
1.继承 org.apache.hadoop.hive.ql.exec.UDF
2.实现 evaluate()函数,此函数支持重载
3.在 hive 命令行创建函数
1.添加jar
add jar /soft/module/datas/GenderUDF.jar
2.创建function
create temporary function [dbname.]gender_udf as "com.hrbu.hive.GenderUDF"
4.在hive命令行删除函数
drop temporary function if exists [dbname.]gender_udf
# Java API代码
package com.hrbu.hive;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
public class TeatApi {
//驱动名称
private static String driverName = "org.apache.hive.jdbc.HiveDriver";
//连接用的url
private static String url = "jdbc:hive2://192.168.1.100:10000/default";
//用户名与密码无需提供
private static String user = "";
private static String password = "";
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet rs = null;
/**
* junit单元测试方法,关键技术是注解
* 1可以随时测试某个方法,不用再写main函数与多余的代码
* 2面向切面的before和after使我们的代码结构更加合理
*/
//加载驱动,创建连接
@Before //表示在任意使用@Test注解标注的public void方法之前执行
public void init() {
try {
Class.forName(driverName);
conn = DriverManager.getConnection(url,user,password);
stmt = conn.createStatement();
} catch (Exception e) {
e.printStackTrace();
}
}
//释放资源
@After //表示在任意使用@Test注解标注的public void方法之后执行
public void destory() throws SQLException {
if(rs != null) {
rs.close();
}
if(stmt != null) {
stmt.close();
}
if(conn != null) {
conn.close();
}
}
//创建数据库
@Test
public void create_DataBase() throws SQLException {
String sql = "create database IF NOT EXISTS hive_jdbc_test"; //create database IF NOT EXISTS hive_jdbc_test
System.out.println("Running " + sql);
stmt.execute(sql);
System.out.println("create database success");
}
//创建表格
@Test
public void create_StudentTable() throws SQLException {
//String sql0 = "use hive_jdbc_test";
String sql = "create table student(code string,name string,gender string,school string,profession string)\r\n" +
" comment 'this is a student table'\r\n" +
" row format delimited fields terminated by '\\t'\r\n" +
" stored as textfile";
System.out.println("Running create table student");
//stmt.execute(sql0);
stmt.execute(sql);
System.out.println("create table student success");
}
// 查询所有数据库
@Test
public void show_DataBases() throws SQLException {
String sql = "show Databases";
System.out.println("Running " + sql);
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getString(1));
}
}
// 查询当前数据库中所有表
@Test
public void show_Tables() throws SQLException {
String sql = "show tables";
System.out.println("Running " + sql);
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getString(1));
}
}
//加载数据
@Test
public void load_Data() throws SQLException {
//linux路径
String filePath = "'/soft/datas/short-student-utf8.txt'";
String sql = "load data local inpath" + filePath + "overwrite into table student";
System.out.println("Running " + sql);
stmt.execute(sql);
System.out.println("load data local success");
}
//查询数据
@Test
public void select_Data() throws SQLException {
String sql = "select * from student";
System.out.println("Running " + sql);
rs = stmt.executeQuery(sql);
System.out.println("学号\t姓名\t性别\t学校\t专业");
while(rs.next()) {
System.out.println(rs.getString("code") + "\t" + rs.getString("name") + "\t" + rs.getString("gender") + "\t" + rs.getString("school") + "\t" + rs.getString("profession"));
}
}
//统计查询(运行mapreduce作业)
@Test
public void count_Data() throws SQLException {
String sql = "select count(*) from student";
System.out.println("Running " + sql);
rs = stmt.executeQuery(sql);
System.out.println("学号\t姓名\t性别\t学校\t专业");
while(rs.next()) {
System.out.println(rs.getInt(1));
}
}
//删除数据库
@Test
public void drop_DataBase() throws SQLException {
//强制删除数据库
//String sql = "DROP DATABASE IF EXISTS hive_jdbc_test CASCADE";
String sql = "DROP DATABASE IF EXISTS hive_jdbc_test";
System.out.println("Running " + sql);
stmt.execute(sql);
System.out.println("DROP DATABASE success");
}
}
编辑 (opens new window)
上次更新: 2022/07/07, 11:20:39