[大数据]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数据类型转换、Hive之数据类型

Hbase表映射Hive表三种方法：

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

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的值

参考：Hbase表映射Hive表三种方法