[大数据] SQL语句整理三--hive

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> SQL语句整理三--hive -> 正文阅读

[大数据]SQL语句整理三--hive

文章目录

创建数据库：

create database jiuyebu;

desc database jiuyebu;

use jiuyebu;

-- casecad 表示有表也删除
drop database yangyang casecad;

创建表：

-- 建表时判断该表是否存在
create table if not exists zb_xsgsbqy_xzq。。。

-- 创建dept测试表
create table emp(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
)
row format delimited fields terminated by '\t';

--  导入测试数据
load data local inpath "/home/hadoop/emp.txt" into table emp;
-- 注释： --local表示本地
		 --overwrite表示覆盖（默认情况使用的是append），一般要加上overwrite 表示覆盖

-- 清空一个表
truncate table emp;

-- 重命名表名
alter table FaRen_JiChuShuJu141 rename to FaRen_JiChuShuJu;

-- 展示表结构
show create table + 表名;

hive 表的类型：

1.内部表：

??hive 的管理表也可以称为内部表：默认表类型。

数据存放的MANAGED_TABLE 内部
默认数据存储在仓库位置目录/user/hive/warehouse/下面，每创建一个库，就是一个目录，建表就会生成文件。
删除表的时候，也会删除HDFS上面的文件。

create table dept1(
    deptno int,
    dname string,
    loc string
)
row format delimited fields terminated by '\t';

-- 导入数据处理：
load data local inpath "/home/hadoop/dept.txt" into table dept1;

2.外部表：

数据存放的MANAGED_TABLE 内部
一般我们会使用location去指定存放到其他位置
删除表的时候，不会去删除HDFS上面的文体,只删除元数据

create table dept2(
    deptno int,
    dname string,
    loc string
)
row format delimited fields terminated by '\t' location '/hive/dept2';

-- 导入数据处理
load data local inpath "/home/hadoop/dept.txt" into table dept2;

3.分区表：

??使用业务场景：

时间增量数据
提高查询速度(核心)
一级分区、二级分区 partitioned by (date string,time string)
创建表时需要给定partitioned 处理一般是指定日期为string 类型。

create table emp3(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
)
partitioned by (data string)
row format delimited fields terminated by '\t';

-- 导入数据的时候要加上partitioned处理。
load data local inpath "/home/hadoop/emp.txt" into table emp3 partition (date='20150515');

-- 查找可以用按 分区去查找
select * from emp3 where date='20150515';

-- 查询某个表的分区信息
show partitions emp3;

注：上传文件到分区表当中没有带分区等相关信息就需要修复分区表，可参考：hive的msck repair命令

msck repair table emp3 ;
-- 或者
alter table emp3 add partition(date='20150515');

4.创建桶表：

create table emp4(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
)
clustered by(empno) into 3 buckets
row format delimited fields terminated by '\t';

-- 默认情况下load 加载时不分桶表：
-- 强制设置分桶：
set hive.enforce.bucketing = true;
-- 可以使用查询其它表加载到桶表，
-- 插入数据： 
insert into emp4 select * from emp4;

-- 显示dfs下文件：路径/库/表/文件
hive> dfs -lsr /

注：hive中的桶表数量如何去设置，评估数据量，保证每个桶的数据量是block的2倍大小

desc命令：

??desc 命令是为了展示hive表格的内在属性。例如列名，data_type，存储位置等信息。这个命令常常用在我们对hive表格观察之时，我们想要知道这个hive各个列名，hive表格的存储位置。

desc table + 表名;

-- 要想获得更加详细的内容，我们可以使用 desc formatted 命令。

-- 想获得表中有分区的情况：
desc formatted revr_bmbs_dmp_offline.rv_dmp_offline_tags_hist partition(dt=20210307);

-- 双分区的情况：
+----------------------+
|         partition      |
+----------------------+
| dt=20210309/hour=6   |
+----------------------+
desc formatted revr_bmbs_dmp_offline.rt_recmd_message partition(dt=20210309,hour=6);

将生成的结果导入到一个文件中：

beeline --outputformat=tsv2 -e "select dmp_id from huiq.heheda where partition_date='20201205'" > 20201205_rd_tmp.csv

注：Beeline和其他工具有一些不同，执行查询都是正常的SQL输入，但是如果是一些管理的命令，比如进行连接，中断，退出，执行Beeline命令需要带上“！”，不需要终止符。常用命令介绍：

1、!connect url –连接不同的Hive2服务器  
2、!exit –退出shell  
3、!help –显示全部命令列表  
4、!verbose –显示查询追加的明细  

The Beeline CLI 支持以下命令行参数:    
Option    
Description    
--autoCommit=[true/false] ---进入一个自动提交模式：beeline --autoCommit=true    
--autosave=[true/false]   ---进入一个自动保存模式：beeline --autosave=true    
--color=[true/false]    ---显示用到的颜色：beeline --color=true    
--delimiterForDSV= DELIMITER ---分隔值输出格式的分隔符。默认是“|”字符。    
--fastConnect=[true/false]  ---在连接时，跳过组建表等对象：beeline --fastConnect=false    
--force=[true/false]    ---是否强制运行脚本：beeline--force=true    
--headerInterval=ROWS   ---输出的表间隔格式，默认是100: beeline --headerInterval=50    
--help ---帮助  beeline --help    
--hiveconf property=value  ---设置属性值，以防被hive.conf.restricted.list重置：beeline --hiveconf prop1=value1     
--hivevar name=value   ---设置变量名：beeline --hivevar var1=value1    
--incremental=[true/false]  ---输出增量  
--isolation=LEVEL  ---设置事务隔离级别：beeline --isolation=TRANSACTION_SERIALIZABLE    
--maxColumnWidth=MAXCOLWIDTH ---设置字符串列的最大宽度：beeline --maxColumnWidth=25    
--maxWidth=MAXWIDTH ---设置截断数据的最大宽度：beeline --maxWidth=150    
--nullemptystring=[true/false]  ---打印空字符串：beeline --nullemptystring=false    
--numberFormat=[pattern]     ---数字使用DecimalFormat：beeline --numberFormat="#,###,##0.00"    
--outputformat=[table/vertical/csv/tsv/dsv/csv2/tsv2] ---输出格式：beeline --outputformat=tsv （默认为talbe）
--showHeader=[true/false]   ---显示查询结果的列名：beeline --showHeader=false    
--showNestedErrs=[true/false] ---显示嵌套错误：beeline --showNestedErrs=true    
--showWarnings=[true/false] ---显示警告：beeline --showWarnings=true    
--silent=[true/false]  ---减少显示的信息量：beeline --silent=true    
--truncateTable=[true/false] ---是否在客户端截断表的列       
--verbose=[true/false]  ---显示详细错误信息和调试信息：beeline --verbose=true    
-d <driver class>  ---使用一个驱动类：beeline -d driver_class    
-e <query>  ---使用一个查询语句：beeline -e "query_string"    
-f <file>  ---加载一个文件：beeline -f filepath  多个文件用-e file1 -e file2  
-n <username>  ---加载一个用户名：beeline -n valid_user    
-p <password>  ---加载一个密码：beeline -p valid_password    
-u <database URL> ---加载一个JDBC连接字符串：beeline -u db_URL

datediff，date_add和date_sub：

1、日期比较函数：datediff语法：datediff(string enddate,string startdate)
返回值：int
说明：返回结束日期减去开始日期的天数。
例如：
hive> select datediff('2019-10-13','2019-10-03');
OK
10

2、日期增加函数：date_add语法：date_add(string startdate, intdays)
返回值：String
说明：返回开始日期startdate增加days天后的日期
例如：
hive> select date_add('2019-10-13',10);
OK
2019-10-23

3、日期减少函数：date_sub语法： date_sub (string startdate,int days)
返回值：String
说明：返回开始日期startdate减少days天后的日期
例如：
hive> select date_sub('2019-10-13',10);
OK
2019-10-03

mysql到hive数据类型转换：

??mysql和hive中的数据类型存在差异，在mysql集成数据到hive中这样的场景下，我们希望在hive中的数据是贴源的，所以在hive中希望创建和mysql结构一致的表。mysql到hive数据类型映射参考如下：

	mysql数据类型	hive数据类型
整型	bigint	BIGINT
整型	int	BIGINT
整型	smalint	BIGINT
整型	tinyint	BIGINT
浮点型	decimal	DECIMAL
浮点型	double	DOUBLE
浮点型	float	DOUBLE
二进制	binary	BINARY
二进制	varbinary	BINARY
字符	char	STRING
字符	varchar	STRING
字符	mediumtext	STRING
字符	text	STRING
时间	datetime	STRING
时间	time	STRING
时间	timestamp	STRING
时间	date	DATE
json	json	MAP<STRING,STRING>

参考：mysql到hive数据类型转换、Hive之数据类型

Hbase表映射Hive表三种方法：

官方文档：https://cwiki.apache.org/confluence/display/Hive/HBaseIntegration

Hbase表	Hive表	映射方法
index1	hbase_table1	列映射（单列）
index1	hbase_table2	列映射（多列）
index1	hbase_table3	列族映射

index1表结构：
在这里插入图片描述
逻辑关系：

1.列映射（单列）：

Hive建表语句：

CREATE EXTERNAL TABLE hbase_table_1(key string, value string) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES("hbase.columns.mapping" = "cf0:indexId") 
TBLPROPERTIES("hbase.table.name" = "index1", "hbase.mapred.output.outputtable" = "index1");

在Hive库中创建一个hbase_table_1表，列名为key、value
映射Hbase库中的index1表的cf0:indexId列
key、value为hive表的列名可修改
string为该字段的字符类型可修改

hive库中的表：

hive> show tables;
OK
hbase_table_1
Time taken: 0.03 seconds, Fetched: 1 row(s)
hive> select * from hbase_table_1;
11	11
13	11
9696	100
990111	11
990_11aa_aza	11
Time taken: 1.266 seconds, Fetched: 5 row(s)

-- 第一列为rowkey的值
-- 第二列为cf0:indexId的值
-- 没有展示cf0:muMac的值

2.列映射（多列）：

Hive建表语句：

CREATE EXTERNAL TABLE hbase_table_2(key int, indexId string, muMac string) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler' 
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf0:indexId,cf0:muMac") 
TBLPROPERTIES("hbase.table.name" = "index1", "hbase.mapred.output.outputtable" = "index1");

在Hive库中创建一个hbase_table_2表，列名为key、indexId、muMac
映射Hbase库中的index1表的cf0:indexId与cf0:muMac列
key、indexId、muMac为hive表的列名可修改
string为该字段的字符类型可修改

hive库中的表：

hive> show tables;
OK
hbase_table_1
hbase_table_2
Time taken: 0.02 seconds, Fetched: 2 row(s)
hive> select * from hbase_table_2;
OK
11	11	NULL
13	11	NULL
9696	100	NULL
9901	NULL	uzzu
990111	11	NULL
NULL	11	NULL
Time taken: 0.144 seconds, Fetched: 6 row(s)

-- 第一列为rowkey的值
-- 第二列为cf0:indexId的值
-- 第三列为cf0:muMac的值

3.列族映射：

Hive建表语句：

CREATE EXTERNAL TABLE hbase_table_3(value map<string,string>,row_key string) 
STORED BY 'org.apache.hadoop.hive.hbase.HBaseStorageHandler'
WITH SERDEPROPERTIES ("hbase.columns.mapping" = "cf0:,:key") 
TBLPROPERTIES("hbase.table.name" = "index1", "hbase.mapred.output.outputtable" = "index1");

在Hive库中创建一个hbase_table_3表，列名为value map、row_key
映射Hbase库中的index1表的cf0:列名
key、indexId、muMac为hive表的列名可修改
string为该字段的字符类型可修改

hive库中的表：

hive> show tables;
OK
hbase_table_1
hbase_table_2
hbase_table_3
Time taken: 0.922 seconds, Fetched: 3 row(s)
hive> select * from hbase_table_3;
{"indexId":"11"}	11
{"indexId":"11"}	13
{"indexId":"100"}	9696
{"muMac":"uzzu"}	9901
{"indexId":"11"}	990111
{"indexId":"11"}	990_11aa_aza
Time taken: 1.469 seconds, Fetched: 6 row(s)

-- 第一列为键值对
-- 第二列为rowkey的值