Hive-结构化数据存储
通过SQL来操作数据，而不是MR，SQL YYDS，这是一项基本功。建议熟练掌握，以及调优等。
hive是基于Hadoop的一个数据仓库工具 ，可以将结构化的 数据文件映射为一张数据库表。
并提供简单的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。
其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
Hive 简介及核心概念 简介 Hive 是一个构建在 Hadoop 之上的数据仓库 ，它可以将结构化的数据文件映射成表，并提供类似 SQL 的查询功能(HQL)，HQL会被转化为 MapReduce 作业，然后提交到 Hadoop 上运行。
Hive由Facebook实现并开源。
为什么用hive?
使用Hadoop MapReduce直接处理数据所面临的问题：人员学习成本太高 需要掌握java语言，MapReduce实现复杂查询逻辑开发难度太大
使用Hive处理数据的好处
操作接口采用类SQL语法 ，提供快速开发的能力（简单、容易上手 ）
避免直接写MapReduce，减少开发人员的学习成本
支持自定义函数，功能扩展很方便
背靠Hadoop，擅长存储分析海量数据集
特点 ：
简单、容易上手 (提供了类似 sql 的查询语言 hql)，使得精通 sql 但是不了解 Java 编程的人也能很好地进行大数据分析；
数据是存储在HDFS上的，Hive本身并不提供数据的存储功能，它可以使已经存储的数据结构化。
Hive是将数据映射成数据库和一张张的表，库和表的元数据信息一般存在关系型数据库上（比如MySQL）。
灵活性高，可以自定义用户函数 (UDF) 和存储格式；
数据存储方面：为超大的数据集设计的计算和存储能力，集群扩展容易，可以直接访问存储在Apache HDFS或其他数据存储系统（如Apache HBase）中的文件。;
统一的元数据管理，可与 presto／impala／sparksql 等共享数据；
数据处理方面：因为Hive语句最终会生成MapReduce任务去计算。执行延迟高，不适合做数据的实时处理，但适合做海量数据的离线处理。
Hive除了支持MapReduce计算引擎，还支持Spark和Tez这两种分布式计算引擎；
数据的存储格式有多种，比如数据源是二进制格式，普通文本格式等等
Hive和Hadoop关系
从功能来说，数据仓库软件，至少需要具备下述两种能力：存储数据的能力、分析数据的能力
Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力。只不过Hive并不是自己实现了上述两种能力，而是借助Hadoop。
Hive利用HDFS存储数据，利用MapReduce查询分析数据。
这样突然发现Hive没啥用，不过是套壳Hadoop罢了。
其实不然，Hive的最大的魅力在于让用户专注于编写HQL,Hive帮您转换成为MapReduce程序完成对数据的分析。
理解 Hive能将数据文件映射成为一张表，这个映射是指什么？ 文件和表之间的对应关系
Hive软件本身到底承担了什么功能职责？ SQL语法解析编译成为MapReduce
出去做大数据离线分析，我们使用hive干活，而hive底层使用MR干活。
Hive的体系架构 版本1
版本2（发展）
客户端 command-line shell & thrift/jdbc & WebGUI
command-line shell ：通过 hive 命令行的的方式来操作数据；thrift／jdbc/odbc ：通过 thrift 协议按照标准的 JDBC 的方式操作数据。WebGUI是通过浏览器访问Hive
在 Hive 中，表名、表结构、字段名、字段类型、表的分隔符等统一被称为元数据 。所有的元数据默认存储在 Hive 内置的 derby 数据库中，但由于 derby 只能有一个实例，也就是说不能有多个命令行客户端同时访问，所以在实际生产环境中，通常使用 MySQL 代替 derby 。
Metastore服务的作用是管理metadata元数据，对外暴露服务地址，让各种客户端通过连接metastore服务，由metastore再去连接MySQL数据库来存取元数据。客户端不需要知道MySQL密码，而是链接metastore，一定程度上保证了hive元数据的安全。
Hive 进行的是统一的元数据管理，就是说你在 Hive 上创建了一张表，然后在 presto／impala／sparksql 中都是可以直接使用的，它们会从 Metastore 中获取统一的元数据信息，同样的你在 presto／impala／sparksql 中创建一张表，在 Hive 中也可以直接使用。
驱动器:Driver (1)解析器(SQL Parser):将SQL字符转换成抽象语法树AST,这一步一般使用都是第三方工具库完成,比如antlr,对AST进行语法分析,比如表是否存在,字段是否存在,SQL语句是否有误
(2)编译器(Physical Plan):将AST编译生成逻辑执行计划
(3)优化器(Query Optimizer):对逻辑执行计划进行优化
(4)执行器(Execution):把逻辑执行计划转换成可以运行的物理计划,对于Hive来说,就是MR/Spark
存储 Hive本身并不存储数据文件，而是使用HDFS进行存储。
执行引擎 Hive本身并不直接处理数据文件。而是通过执行引擎处理。当下Hive支持MapReduce（慢，默认）、Tez、Spark3种执行引擎。
HQL的执行流程 Hive 在执行一条 HQL 的时候，会经过以下步骤：
语法解析：Antlr 定义 SQL 的语法规则，完成 SQL 词法，语法解析，将 SQL 转化为抽象 语法树 AST Tree；
语义解析：遍历 AST Tree，抽象出查询的基本组成单元 QueryBlock；
生成逻辑执行计划：遍历 QueryBlock，翻译为执行操作树 OperatorTree；
优化逻辑执行计划：逻辑层优化器进行 OperatorTree 变换，合并不必要的 ReduceSinkOperator，减少 shuffle 数据量；
生成物理执行计划：遍历 OperatorTree，翻译为 MapReduce 任务；
优化物理执行计划：物理层优化器进行 MapReduce 任务的变换，生成最终的执行计划。
关于 Hive SQL 的详细执行流程可以参考美团技术团队的文章：Hive SQL 的编译过程
Hive与传统数据库对比
hive具有sql数据库的外表，但应用场景完全不同，hive只适合用来做批量数据统计分析
数据类型 整体分为两类：原生/基本数据类型（primitive data type）和复杂数据类型（complex data type）
工作当中最常用的数据类型是字符串String和数字类型Int
常用类型总结：
整数：int
小数：double/decimal(10,2)
字符串: string
日期: 注意：虽然支持日期，但是我们一般用用string,避免一些不必要的兼容性问题
年月日：date yyyy-MM-dd
年月日时分秒: TIMESTAMP yyyy-MM-dd HH:mm:ss[.fffffffff] 会添加ffff不好用
日期直接用string就行了
字典值用TINYINT
ID主键可以用INT/STRING，其他表的外键可以用STRING
价格可以用DECIMAL(11,2)
创建时间修改时间用string
基本数据类型 Hive 表中的列支持以下基本数据类型：
大类
类型
Integers（整型） TINYINT—1 字节的有符号整数
Boolean（布尔型） BOOLEAN—TRUE/FALSE
Floating point numbers（浮点型） FLOAT— 单精度浮点型
Fixed point numbers（定点数） DECIMAL—用户自定义精度定点数，比如 DECIMAL(7,2)
String types（字符串） STRING—指定字符集的字符序列
Date and time types（日期时间类型） TIMESTAMP — 时间戳
Binary types（二进制类型） BINARY—字节序列
TIMESTAMP 和 TIMESTAMP WITH LOCAL TIME ZONE 的区别如下：
TIMESTAMP WITH LOCAL TIME ZONE ：用户提交时间给数据库时，会被转换成数据库所在的时区来保存。查询时则按照查询客户端的不同，转换为查询客户端所在时区的时间。TIMESTAMP ：提交什么时间就保存什么时间，查询时也不做任何转换。
1 2 3 4 5 6 7 8 9 10 11 12 整形： int 浮点型： float / double / decimal(10,2) 字符串： string （特别！！） 日期类型： 年月日：date 时分秒：time 年月日-时分秒：date_time 注意：如果是日期或者时间，则使用字符串可以避免一些不必要的兼容问题 复杂类型： array：数组，集合 map ：键值对集合 struct： 类
double，如果小数位为0，会去掉0保留，比如100.00，会保存为100，而100.12，则会保存为100.12。
隐式转换 Hive 中基本数据类型遵循以下的层次结构，按照这个层次结构，子类型到祖先类型允许隐式转换。例如 INT 类型的数据允许隐式转换为 BIGINT 类型。额外注意的是：按照类型层次结构允许将 STRING 类型隐式转换为 DOUBLE 类型。
复杂类型
类型
描述
示例
STRUCT 类似于对象，是字段的集合，字段的类型可以不同，可以使用 名称.字段名 方式进行访问
STRUCT (‘xiaoming’, 12 , ‘2018-12-12’)
MAP 键值对的集合，可以使用 名称[key] 的方式访问对应的值
map(‘a’, 1, ‘b’, 2)
ARRAY 数组是一组具有相同类型和名称的变量的集合，可以使用 名称[index] 访问对应的值
ARRAY(‘a’, ‘b’, ‘c’, ‘d’)
复杂类型操作
用复杂类型就是为了可以使用hive中一些好用的函数去操作数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # 源数据：name与locations之间制表符分隔，locations中元素之间逗号分隔 zhangsan beijing,shanghai,tianjin,hangzhou wangwu changchun,chengdu,wuhan,beijing # 建表 create external table hive_array(name string, work_locations array < string> )row format delimited fields terminated by '\t' collection items terminated by ',' ; # 导入数据 从本地导入 load data local inpath '/export/data/hivedatas/work_locations.txt' overwrite into table hive_array; # 常用查询： select * from hive_array;select name, work_locations[0 ] location from hive_array;select name, size(work_locations) location_size from hive_array;select * from hive_array where array_contains(work_locations,'tianjin' );
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 # 源数据：字段与字段分隔符: “,”；需要map字段之间的分隔符："#"；map内部k- v分隔符：":" 1 ,zhangsan,father:xiaoming#mother:xiaohuang#brother:xiaoxu,28 2 ,lisi,father:mayun#mother:huangyi#brother:guanyu,22 3 ,wangwu,father:wangjianlin#mother:ruhua#sister:jingtian,29 4 ,mayun,father:mayongzhen#mother:angelababy,26 # 建表语句 create table hive_map(id int , name string, members map< string,string> , age int ) row format delimitedfields terminated by ',' collection items terminated by '#' map keys terminated by ':' ; # 导入数据 load data local inpath '/export/data/hivedatas/hive_map.txt' overwrite into table hive_map; # 常用查询 select * from hive_map;#根据键找对应的值 select id, name, members['father' ] father, members['mother' ] mother, age from hive_map;#获取所有的键 select id, name, map_keys(members) as relation from hive_map;#获取所有的值 select id, name, map_values(members) as relation from hive_map;#获取键值对个数 select id,name,size(members) num from hive_map;#获取有指定key的数据 select * from hive_map where array_contains(map_keys(members), 'brother' );#查找包含brother这个键的数据，并获取brother键对应的值 select id,name, members['brother' ] brother from hive_map where array_contains(map_keys(members), 'brother' );
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # 源数据： 字段之间#分割，第二个字段之间冒号分割 192.168 .1 .1 #zhangsan:40 192.168 .1 .2 #lisi:50 192.168 .1 .3 #wangwu:60 192.168 .1 .4 #zhaoliu:70 # 建表语句 create table hive_struct(ip string, info struct< name:string, age:int > ) row format delimitedfields terminated by '#' collection items terminated by ':' ; # 导入数据 load data local inpath '/export/data/hivedatas/hive_struct.txt' into table hive_struct; # 常用查询 select * from hive_struct;#根据struct来获取指定的成员的值 select ip, info.name from hive_struct;
示例 如下给出一个基本数据类型和复杂数据类型的使用示例：
1 2 3 4 5 6 7 8 CREATE TABLE students( name STRING, age INT , subject ARRAY < STRING> , score MAP< STRING,FLOAT > , address STRUCT< houseNumber:int , street:STRING, city:STRING, province：STRING> ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
内容格式
分隔符，因为hive是数据文件到表的映射，所以需要将文件安装一定的规律分割成字段。只有分隔符正确，解析数据成功，才能在表里看到数据。建表的时候用ROW FORMAT DELIMITED 指定
当数据存储在文本文件中，必须按照一定格式区别行和列，如使用逗号作为分隔符的 CSV 文件 (Comma-Separated Values) 或者使用制表符作为分隔值的 TSV 文件 (Tab-Separated Values)。但此时也存在一个缺点，就是正常的文件内容中也可能出现逗号或者制表符。
所以 Hive 默认使用了几个平时很少出现的字符，这些字符一般不会作为内容出现在文件中。Hive 默认的行和列分隔符如下表所示。
分隔符
描述
\n 对于文本文件来说，每行是一条记录，所以可以使用换行符来分割记录
^A (Ctrl+A) 分割字段 (列)，在 CREATE TABLE 语句中也可以使用八进制编码 \001 来表示
^B 用于分割 ARRAY 或者 STRUCT 中的元素，或者用于 MAP 中键值对之间的分割，\002 表示
^C 用于 MAP 中键和值之间的分割，在 CREATE TABLE 语句中也可以使用八进制编码 \003 表示
默认分隔符是 \001,特殊字符，使用的是ASCII编码的值，在vim中连续按下ctrl+v/ctrl+a即可输入\001,显示为^A或者在文本编辑器(nodepad++)中显示为SOH
使用示例如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 # LazySimpleSerDe是Hive默认的，包含4 种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。在建表的时候可以根据数据的特点灵活搭配使用 ROW FORMAT DELIMITED [FIELDS TERMINATED BY char ] - > 字段之间分隔符 [COLLECTION ITEMS TERMINATED BY char ] - > 集合元素之间分隔符 [MAP KEYS TERMINATED BY char ] - > Map映射kv之间分隔符 [LINES TERMINATED BY char ] - > 行数据之间分隔符 CREATE TABLE page_view(viewTime INT , userid BIGINT )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS SEQUENCEFILE;
字段以\001分隔建表时很方便，那么采集、清洗数据时对数据格式追求有什么启发 => 优先考虑\001分隔符
数据压缩 在实际工作当中，hive当中处理的数据，一般都需要经过压缩，可以使用压缩来节省我们底层的MR处理的网络带宽
MR支持的压缩编码
为了支持多种压缩/解压缩算法，Hadoop引入了编码/解码器，如下表所示
压缩性能的比较
http://google.github.io/snappy/
On a single core of a Core i7 processor in 64-bit mode, Snappy compresses at about 250 MB/sec or more and decompresses at about 500 MB/sec or more.
压缩配置参数 要在Hadoop中启用压缩，可以配置如下参数（mapred-site.xml文件中）：
参数
默认值
阶段
建议
io.compression.codecs （在core-site.xml中配置）
org.apache.hadoop.io.compress.DefaultCodec, org.apache.hadoop.io.compress.GzipCodec, org.apache.hadoop.io.compress.BZip2Codec,
输入压缩
Hadoop使用文件扩展名判断是否支持某种编解码器
mapreduce.map.output.compress
false
mapper输出
这个参数设为true启用压缩
mapreduce.map.output.compress.codec
org.apache.hadoop.io.compress.DefaultCodec
mapper输出
使用LZO、LZ4或snappy编解码器在此阶段压缩数据
mapreduce.output.fileoutputformat.compress
false
reducer输出
这个参数设为true启用压缩
mapreduce.output.fileoutputformat.compress.codec
org.apache.hadoop.io.compress.DefaultCodec
reducer输出
使用标准工具或者编解码器，如gzip和bzip2
mapreduce.output.fileoutputformat.compress.type
RECORD
reducer输出
SequenceFile输出使用的压缩类型：NONE和BLOCK
map阶段压缩 开启map输出阶段压缩可以减少job中map和Reduce task间数据传输量。具体配置如下：
1 2 3 4 5 6 7 8 9 10 11 12 案例实操： 1 ）开启hive中间传输数据压缩功能hive(default )> set hive.exec.compress.intermediate= true ; 2 ）开启mapreduce中map输出压缩功能hive (default )> set mapreduce.map.output.compress= true ; 3 ）设置mapreduce中map输出数据的压缩方式hive (default )> set mapreduce.map.output.compress.codec= org.apache.hadoop.io.compress.SnappyCodec; 4 ）执行查询语句select count (1 ) from score;
map端生效位置图：
reduce阶段压缩 当Hive将输出写入到表中时，输出内容同样可以进行压缩。属性hive.exec.compress.output控制着这个功能。用户可能需要保持默认设置文件中的默认值false，这样默认的输出就是非压缩的纯文本文件了。用户可以通过在查询语句或执行脚本中设置这个值为true，来开启输出结果压缩功能。
1 2 3 4 5 6 7 8 9 10 11 案例实操： set hive.exec.compress.output= true ;set mapreduce.output.fileoutputformat.compress= true ;set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;set mapreduce.output.fileoutputformat.compress.type= BLOCK;insert overwrite local directory '/export/data/compress' select * from score distribute by sid sort by sscore desc ;
可以去全局配置文件设置，但是一般不推荐，都是在脚本里临时设置，全局设置没法通用
存储格式 支持的存储格式 Hive 会在 HDFS 为每个数据库上创建一个目录，数据库中的表是该目录的子目录，表中的数据会以文件的形式存储在对应的表目录下。Hive 支持以下几种文件存储格式：
格式
说明
TextFile 存储为纯文本文件。 这是 Hive 默认的文件存储格式。这种存储方式数据不做压缩，磁盘开销大，数据解析开销大。
SequenceFile SequenceFile 是 Hadoop API 提供的一种二进制文件，它将数据以<key,value>的形式序列化到文件中。这种二进制文件内部使用 Hadoop 的标准的 Writable 接口实现序列化和反序列化。它与 Hadoop API 中的 MapFile 是互相兼容的。Hive 中的 SequenceFile 继承自 Hadoop API 的 SequenceFile，不过它的 key 为空，使用 value 存放实际的值，这样是为了避免 MR 在运行 map 阶段进行额外的排序操作。
RCFile RCFile 文件格式是 FaceBook 开源的一种 Hive 的文件存储格式，首先将表分为几个行组，对每个行组内的数据按列存储，每一列的数据都是分开存储。
ORC Files ORC 是在一定程度上扩展了 RCFile，是对 RCFile 的优化。
Avro Files Avro 是一个数据序列化系统，设计用于支持大批量数据交换的应用。它的主要特点有：支持二进制序列化方式，可以便捷，快速地处理大量数据；动态语言友好，Avro 提供的机制使动态语言可以方便地处理 Avro 数据。
Parquet Parquet 是基于 Dremel 的数据模型和算法实现的，面向分析型业务的列式存储格式。它通过按列进行高效压缩和特殊的编码技术，从而在降低存储空间的同时提高了 IO 效率。
以上压缩格式中 ORC 和 Parquet 的综合性能突出，使用较为广泛，推荐使用这两种格式
行式存储和列式存储
行存储的特点: 查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快 。
列存储的特点：因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法 。
相比于行式存储，列式存储在分析场景下有着许多优良的特性：
1）分析场景中往往需要读大量行但是少数几个列。在行存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出，读取操作被严重放大。而列存模式下，只需要读取参与计算的列即可，极大的减低了IO开销，加速了查询。
行存储优点
使用行查询效率高（select * from t_covid）
使用列查询效率低（select dt_value,state from t_covid）
列存储优点
使用列查询效率高（select dt_value,state from t_covid）
使用行查询效率低（select * from t_covid）
由于在生产环境一般都是列查询(几百个字段,不大可能都查出来,而且一般不运行用select *),所以一般用列式存储，查询效率高。推荐用ORC。
2）同一列中的数据属于同一类型，压缩效果显著。列存储往往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。
3）更高的压缩比意味着更小的数据空间，从磁盘中读取相应数据耗时更短。
4）自由的压缩算法选择。不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同。可以针对不同列类型，选择最合适的压缩算法。
TEXTFILE和SEQUENCEFILE的存储格式都是基于行存储的
ORC和PARQUET是基于列式存储的
ORC格式的表不能用laod 加载文本数据，要用insert into ... select from textfile表数据方法加载。而这一步一般第三方框架会封装文本数据加载到临时表的普通textfile表，然后插入到ORC表的操作。
指定存储格式 通常在创建表的时候使用 STORED AS 参数指定：
1 2 3 4 5 6 CREATE TABLE page_view(viewTime INT , userid BIGINT )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS SEQUENCEFILE;
各个存储文件类型指定方式如下：
STORED AS TEXTFILE
STORED AS SEQUENCEFILE
STORED AS ORC
STORED AS PARQUET
STORED AS AVRO
STORED AS RCFILE
压缩比： ORC > Parquet > textFile
查询速度：ORC > textFile > Parquet
存储和压缩结合 ORC存储方式的压缩：
Key
Default
Notes
orc.compress
ZLIB
high level compression (one of NONE, ZLIB, SNAPPY)
orc.compress.size
262,144
number of bytes in each compression chunk
orc.stripe.size
67,108,864
number of bytes in each stripe
orc.row.index.stride
10,000
number of rows between index entries (must be >= 1000)
orc.create.index
true
whether to create row indexes
orc.bloom.filter.columns
“”
comma separated list of column names for which bloom filter should be created
orc.bloom.filter.fpp
0.05
false positive probability for bloom filter (must >0.0 and <1.0)
建表时指定非压缩的ORC存储方式
1 STORED AS orc tblproperties ("orc.compress"= "NONE");
建表时指定SNAPPY压缩的ORC存储方式
1 STORED AS orc tblproperties ("orc.compress"= "SNAPPY");
orc存储文件默认采用ZLIB压缩。比snappy压缩的小。
存储方式和压缩总结： (orc + snappy)
在实际的项目开发当中，hive表的数据存储格式一般选择：orc或parquet。压缩方式一般选择snappy。
内部表和外部表 内部表又叫做管理表 (Managed/Internal Table)，创建表时不做任何指定，默认创建的就是内部表。想要创建外部表 (External Table)，则需要使用 External 进行修饰。 内部表和外部表主要区别如下：
内部表
外部表
数据存储位置
内部表数据存储的位置由 hive.metastore.warehouse.dir 参数指定，默认情况下表的数据存储在 HDFS 的 /user/hive/warehouse/数据库名.db/表名/ 目录下
外部表数据的存储位置创建表时由 Location 参数指定；
导入数据
在导入数据到内部表，内部表将数据移动到自己的数据仓库目录下，数据的生命周期由 Hive 来进行管理
外部表不会将数据移动到自己的数据仓库目录下，只是在元数据中存储了数据的位置
删除表
删除元数据（metadata）和文件
只删除元数据（metadata）
Hive 环境安装
见Linux-大数据篇
metastore服务配置有3种模式：内嵌模式、本地模式、远程模式 。
区分3种配置方式的关键是弄清楚两个问题：
Metastore服务是否需要单独配置、单独启动？
Metadata是存储在内置的derby中，还是第三方RDBMS,比如MySQL。
企业推荐模式–远程模式部署 。
在生产环境中，建议用远程模式来配置Hive Metastore。在这种情况下，其他依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性。
Beeline CLI访问HiveServer2，然后再访问MetaStore
安装前准备 由于Apache Hive是一款基于Hadoop的数据仓库软件，通常部署运行在Linux系统之上。因此不管使用何种方式配置Hive Metastore，必须要先保证服务器的基础环境正常，Hadoop集群健康可用 。
集群时间同步、防火墙关闭、主机Host映射、免密登录、JDK安装
启动Hive之前必须先启动Hadoop集群。特别要注意，需等待HDFS安全模式关闭(大概30s)之后再启动运行Hive。
Hive不是分布式安装运行的软件，其分布式的特性主要借由Hadoop完成。包括分布式存储、分布式计算。
Hadoop和hive整合
因为Hive需要把数据存储在HDFS上，并且通过MapReduce作为执行引擎处理数据；
因此需要在Hadoop中添加相关配置属性，以满足Hive在Hadoop上运行。
修改Hadoop中core-site.xml，并且Hadoop集群同步配置文件，重启生效。
1 2 3 4 5 6 7 8 9 <property > <name > hadoop.proxyuser.root.hosts</name > <value > *</value > </property > <property > <name > hadoop.proxyuser.root.groups</name > <value > *</value > </property >
MySQL安装
注意MySQL只需要在一台机器安装并且需要授权远程访问。
1 2 3 4 5 6 [root@node3 ~]# rpm -qa|grep mariadb mariadb-libs-5.5.64-1.el7.x86_64 [root@node3 ~]# rpm -e mariadb-libs-5.5.64-1.el7.x86_64 --nodeps [root@node3 ~]# rpm -qa|grep mariadb [root@node3 ~]#
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mkdir /export/software/mysql # 上传mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar 到上述文件夹下 解压 tar xvf mysql-5.7.29-1.el7.x86_64.rpm-bundle.tar # 执行安装 yum -y install libaio [root@node3 mysql]# rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm mysql-community-libs-5.7.29-1.el7.x86_64.rpm mysql-community-client-5.7.29-1.el7.x86_64.rpm mysql-community-server-5.7.29-1.el7.x86_64.rpm warning: mysql-community-common-5.7.29-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-5.7.29-1.e################################# [ 25%] 2:mysql-community-libs-5.7.29-1.el7################################# [ 50%] 3:mysql-community-client-5.7.29-1.e################################# [ 75%] 4:mysql-community-server-5.7.29-1.e################ ( 49%)
1 2 3 4 5 6 7 8 9 10 11 12 13 # 初始化 mysqld --initialize # 更改所属组 chown mysql:mysql /var/lib/mysql -R # 启动mysql systemctl start mysqld.service # 查看生成的临时root密码 cat /var/log/mysqld.log [Note] A temporary password is generated for root@localhost: o+TU+KDOm004
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 [root@node2 ~]# mysql -u root -p Enter password: #这里输入在日志中生成的临时密码 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.7.29 Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> # 更新root密码 设置为hadoop mysql> alter user user() identified by "hadoop" ; Query OK, 0 rows affected (0.00 sec) # 授权 mysql> use mysql; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root' @'%' IDENTIFIED BY 'hadoop' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; # mysql的启动和关闭 状态查看 （这几个命令必须记住） systemctl stop mysqld systemctl status mysqld systemctl start mysqld # 建议设置为开机自启动服务 [root@node2 ~]# systemctl enable mysqld Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service. # 查看是否已经设置自启动成功 [root@node2 ~]# systemctl list-unit-files | grep mysqld mysqld.service enabled
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 # 关闭mysql服务 systemctl stop mysqld.service # 查找安装mysql的rpm包 [root@node3 ~]# rpm -qa | grep -i mysql mysql-community-libs-5.7.29-1.el7.x86_64 mysql-community-common-5.7.29-1.el7.x86_64 mysql-community-client-5.7.29-1.el7.x86_64 mysql-community-server-5.7.29-1.el7.x86_64 # 卸载 [root@node3 ~]# yum remove mysql-community-libs-5.7.29-1.el7.x86_64 mysql-community-common-5.7.29-1.el7.x86_64 mysql-community-client-5.7.29-1.el7.x86_64 mysql-community-server-5.7.29-1.el7.x86_64 # 查看是否卸载干净 rpm -qa | grep -i mysql # 查找mysql相关目录 删除 [root@node1 ~]# find / -name mysql /var/lib/mysql /var/lib/mysql/mysql /usr/share/mysql [root@node1 ~]# rm -rf /var/lib/mysql [root@node1 ~]# rm -rf /var/lib/mysql/mysql [root@node1 ~]# rm -rf /usr/share/mysql # 删除默认配置 日志 rm -rf /etc/my.cnf rm -rf /var/log/mysqld.log
Hive3的安装 上传安装包 解压 官网：http://archive.apache.org/dist/hive/hive-3.1.2
1 2 3 4 tar zxvf apache-hive-3.1.2-bin.tar.gz -C /usr/local/apps # 重命名 # mv apache-hive-3.1.2-bin hive-3.1.2
1 2 3 cd /usr/local/apps/apache-hive-3.1.2-bin/ rm -rf lib/guava-19.0.jar cp /usr/local/apps/hadoop-3.3.0/share/hadoop/common/lib/guava-27.0-jre.jar ./lib/
修改配置文件 1 2 3 4 5 6 7 cd /usr/local/apps/apache-hive-3.1.2-bin/conf mv hive-env.sh.template hive-env.sh vim hive-env.sh export HADOOP_HOME=/usr/local/apps/hadoop-3.3.0 export HIVE_CONF_DIR=/usr/local/apps/apache-hive-3.1.2-bin/conf export HIVE_AUX_JARS_PATH=/usr/local/apps/apache-hive-3.1.2-bin/lib
vim hive-site.xml 新增文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 <configuration > <property > <name > javax.jdo.option.ConnectionURL</name > <value > jdbc:mysql://node1:3306/hive3?createDatabaseIfNotExist=true& useSSL=false& useUnicode=true& characterEncoding=UTF-8</value > </property > <property > <name > javax.jdo.option.ConnectionDriverName</name > <value > com.mysql.jdbc.Driver</value > </property > <property > <name > javax.jdo.option.ConnectionUserName</name > <value > root</value > </property > <property > <name > javax.jdo.option.ConnectionPassword</name > <value > root</value > </property > <property > <name > hive.server2.thrift.bind.host</name > <value > node1</value > </property > <property > <name > hive.metastore.uris</name > <value > thrift://node1:9083</value > </property > <property > <name > hive.metastore.event.db.notification.api.auth</name > <value > false</value > </property > <property > <name > hive.metastore.schema.verification</name > <value > false</value > </property > </configuration >
1 2 3 4 5 6 7 8 9 # 添加环境变量 vim /etc/profile export HIVE_HOME=/usr/local/apps/hive-3.1.2 export PATH=:$HIVE_HOME/bin:$PATH # 让环境变量生效 source /etc/profile
上传mysql jdbc驱动到hive安装包lib下
1 2 # 上传到/usr/local/apps/apache-hive-3.1.2-bin/lib下 mysql-connector-java-5.1.32.jar
1 2 3 4 cd /usr/local/apps/apache-hive-3.1.2-bin/ bin/schematool -initSchema -dbType mysql -verbos # 初始化成功会在mysql中创建74张表
在hdfs创建hive存储目录（如存在则不用操作）
1 2 3 4 hadoop fs -mkdir /tmp hadoop fs -mkdir -p /user/hive/warehouse hadoop fs -chmod g+w /tmp hadoop fs -chmod g+w /user/hive/warehouse
启动hive 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 # 前台启动 关闭ctrl+c /usr/local/apps/apache-hive-3.1.2-bin/bin/hive --service metastore # 前台启动开启debug日志 /usr/local/apps/apache-hive-3.1.2-bin/bin/hive --service metastore --hiveconf hive.root.logger=DEBUG,console # 后台启动 进程挂起 关闭使用jps+ kill -9 nohup /usr/local/apps/apache-hive-3.1.2-bin/bin/hive --service metastore & # nohup 命令，在默认情况下（非重定向时），会输出一个名叫 nohup.out 的文件到当前目录下# 验证是否安装成功! jps -m 可以看进程信息 在Linux中输入hive命令，直接回车,出现一个终端，在该终端中可以输入sql命令: show databases; # 如果hive没有安装成功，则需要做以下操作: 1、 检查参数是否有问题 2、 杀死两个junjar进程 3、 进入mysql删除hive数据库：drop database hive 4、 重新对hive初始化：schematool -initSchema -dbType mysql -verbos
前台启动，进程会一直占据终端，ctrl + c结束进程，服务关闭。可以根据需求添加参数开启debug日志，获取详细日志信息，便于排错。
1 2 3 nohup /usr/local/apps/apache-hive-3.1.2-bin/bin/hive --service hiveserver2 & # 注意 启动hiveserver2需要一定的时间 不要启动之后立即beeline连接 可能连接不上
HIVE有2代客户端
一代$HIVE_HOME/bin/hive(不推荐使用)，是一个shellUtil,主要用于交互/批处理模式运行hive查询；以及hive相关服务启动，比如metastore服务。
二代$HIVE_HOME/bin/beeline，是个JDBC客户端,比一代性能更好，安全性更高。
远程模式下beeline通过 Thrift 连接到单独的HiveServer2 服务上，这也是官方推荐在生产环境中使用的模式。
HiveServer2支持多客户端的并发和身份认证，旨在为开放API客户端如JDBC、ODBC提供更好的支持。
bin/beeline客户端使用 ： 首先启动metastore服务，然后启动hiveserver2服务。
拷贝node1安装包到beeline客户端机器上（node3）
1 scp -r /usr/local/apps/apache-hive-3.1.2-bin/ node3:/usr/local/apps/
1 Error: Could not open client transport with JDBC Uri: jdbc:hive2://node1:10000: Failed to open new session: java.lang.RuntimeException: org.apache.hadoop.ipc.RemoteException(org.apache.hadoop.security.authorize.AuthorizationException): User: root is not allowed to impersonate root (state=08S01,code=0)
1 2 3 4 5 6 7 8 9 在hadoop的配置文件core-site.xml中添加如下属性： <property > <name > hadoop.proxyuser.root.hosts</name > <value > *</value > </property > <property > <name > hadoop.proxyuser.root.groups</name > <value > *</value > </property >
1 2 3 4 5 6 7 /usr/local/apps/apache-hive-3.1.2-bin/bin/beeline # 连接节点node1 beeline> ! connect jdbc:hive2://node1:10000 # 输入用户名和密码 beeline> root beeline> 直接回车
错误解决：Hive3执行insert插入操作 statstask异常
现象: 在执行insert + values操作的时候 虽然最终执行成功，结果正确。但是在执行日志中会出现如下的错误信息。
1 2 3 2020-11-09 00:37:48,963 WARN [5ce14c58-6b36-476a-bab8-89cba7dd1706 main] metastore.RetryingMetaStoreClient: MetaStoreClient lost connection. Attempting to reconnect (1 of 1) after 1s. setPartitionColumnStatistics ERROR [5ce14c58-6b36-476a-bab8-89cba7dd1706 main] exec.StatsTask: Failed to run stats task
分析原因和解决
statstask是一个hive中用于统计插入等操作的状态任务 其返回结果如下
在mysql metastore中删除 PART_COL_STATS这张表即可 。
hive将数据保存到mysql中，默认不支持中文编码，所以comment注释都是乱码的。
修改hive元数据信息
1 2 3 4 5 6 7 8 9 10 11 12 13 14 use hive3; show tables;alter table hive3.COLUMNS_V2 modify column COMMENT varchar (256 ) character set utf8;alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar (4000 ) character set utf8;alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar (4000 ) character set utf8 ;alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar (4000 ) character set utf8;alter table hive3.INDEX_PARAMS modify column PARAM_VALUE varchar (4000 ) character set utf8;alter table hive3.DBS modify column `DESC ` varchar (4000 ) character set utf8 ;
重新创建表即可
Hive CLI和Beeline命令行的基本使用 Hive CLI Help 使用 hive -H 或者 hive --help 命令可以查看所有命令的帮助，显示如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 usage: hive -d,--define <key=value> Variable subsitution to apply to hive commands. e.g. -d A=B or --define A=B --定义用户自定义变量 --database <databasename> Specify the database to use -- 指定使用的数据库 -e <quoted-query-string> SQL from command line -- 执行指定的 SQL -f <filename> SQL from files --执行 SQL 脚本 -H,--help Print help information -- 打印帮助信息 --hiveconf <property=value> Use value for given property --自定义配置 --hivevar <key=value> Variable subsitution to apply to hive --自定义变量 commands. e.g. --hivevar A=B -i <filename> Initialization SQL file --在进入交互模式之前运行初始化脚本 -S,--silent Silent mode in interactive shell --静默模式 -v,--verbose Verbose mode (echo executed SQL to the console) --详细模式
交互式命令行 直接使用 Hive 命令，不加任何参数，即可进入交互式命令行。
执行SQL命令 在不进入交互式命令行的情况下，可以使用 hive -e 执行 SQL 命令。
1 hive - e 'select * from emp' ;
执行SQL脚本 用于执行的 sql 脚本可以在本地文件系统，也可以在 HDFS 上。
1 2 3 4 5 # 本地文件系统 hive -f /usr/file/simple.sql; # 生产上比较常用 # HDFS文件系统 hive -f hdfs://hadoop001:8020/tmp/simple.sql;
其中 simple.sql 内容如下：
配置Hive变量 可以使用 --hiveconf 设置 Hive 运行时的变量。
1 2 3 hive - e 'select * from emp' \
hive.exec.scratchdir：指定 HDFS 上目录位置，用于存储不同 map/reduce 阶段的执行计划和这些阶段的中间输出结果。
配置文件启动 使用 -i 可以在进入交互模式之前运行初始化脚本，相当于指定配置文件启动。
1 hive -i /usr/file/hive-init.conf;
其中 hive-init.conf 的内容如下：
1 set hive.exec.mode.local.auto = true ;
hive.exec.mode.local.auto 默认值为 false，这里设置为 true ，代表开启本地模式。
用户自定义变量 --define <key=value> 和 --hivevar <key=value> 在功能上是等价的，都是用来实现自定义变量，这里给出一个示例:
定义变量：
在查询中引用自定义变量：
1 2 3 4 5 6 7 # 以下两条语句等价 hive > select ${n} from emp; hive > select ${hivevar:n} from emp; # 以下两条语句等价 hive > select ${j} from emp; hive > select ${hivevar:j} from emp;
结果如下：
Beeline HiveServer2 Hive 内置了 HiveServer 和 HiveServer2 服务，两者都允许客户端使用多种编程语言进行连接，但是 HiveServer 不能处理多个客户端的并发请求，所以产生了 HiveServer2。
HiveServer2（HS2）允许远程客户端可以使用各种编程语言向 Hive 提交请求并检索结果，支持多客户端并发访问和身份验证。HS2 是由多个服务组成的单个进程，其包括基于 Thrift 的 Hive 服务（TCP 或 HTTP）和用于 Web UI 的 Jetty Web 服务器。
HiveServer2 拥有自己的 CLI(Beeline)，Beeline 是一个基于 SQLLine 的 JDBC 客户端。由于 HiveServer2 是 Hive 开发维护的重点 (Hive0.15 后就不再支持 hiveserver)，所以 Hive CLI 已经不推荐使用了，官方更加推荐使用 Beeline。
Beeline Beeline 拥有更多可使用参数，可以使用 beeline --help 查看，完整参数如下：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Usage : java org.apache.hive.cli.beeline.BeeLine -u <database url> the JDBC URL to connect to -r reconnect to last saved connect url (in conjunction with !save) -n <username> the username to connect as -p <password> the password to connect as -d <driver class> the driver class to use -i <init file> script file for initialization -e <query> query that should be executed -f <exec file> script file that should be executed -w (or) --password-file <password file> the password file to read password from --hiveconf property=value Use value for given property --hivevar name=value hive variable name and value This is Hive specific settings in which variables can be set at session level and referenced in Hive commands or queries. --property-file =<property-file> the file to read connection properties (url, driver, user, password) from --color =[true/false] control whether color is used for display --showHeader =[true/false] show column names in query results --headerInterval =ROWS; the interval between which heades are displayed --fastConnect =[true/false] skip building table/column list for tab-completion --autoCommit =[true/false] enable/disable automatic transaction commit --verbose =[true/false] show verbose error messages and debug info --showWarnings =[true/false] display connection warnings --showNestedErrs =[true/false] display nested errors --numberFormat =[pattern] format numbers using DecimalFormat pattern --force =[true/false] continue running script even after errors --maxWidth =MAXWIDTH the maximum width of the terminal --maxColumnWidth =MAXCOLWIDTH the maximum width to use when displaying columns --silent =[true/false] be more silent --autosave =[true/false] automatically save preferences --outputformat =[table/vertical/csv2/tsv2/dsv/csv/tsv] format mode for result display --incrementalBufferRows =NUMROWS the number of rows to buffer when printing rows on stdout, defaults to 1000; only applicable if --incremental=true and --outputformat=table --truncateTable =[true/false] truncate table column when it exceeds length --delimiterForDSV =DELIMITER specify the delimiter for delimiter-separated values output format (default: |) --isolation =LEVEL set the transaction isolation level --nullemptystring =[true/false] set to true to get historic behavior of printing null as empty string --maxHistoryRows =MAXHISTORYROWS The maximum number of rows to store beeline history. --convertBinaryArrayToString =[true/false] display binary column data as string or as byte array --help display this message
常用参数 在 Hive CLI 中支持的参数，Beeline 都支持，常用的参数如下。更多参数说明可以参见官方文档 Beeline Command Options
参数
说明
-u <database URL> 数据库地址
-n <username> 用户名
-p <password> 密码
-d <driver class> 驱动 (可选)
-e <query> 执行 SQL 命令
-f <file> 执行 SQL 脚本
-i (or)–init <file or files> 在进入交互模式之前运行初始化脚本
–property-file <file> 指定配置文件
–hiveconf property *=*value 指定配置属性
–hivevar name *=*value 用户自定义属性，在会话级别有效
示例： 使用用户名和密码连接 Hive
1 $ beeline -u jdbc:hive2://localhost:10000 -n username -p password
一键启动脚本 这里，我们写一个expect脚本，可以一键启动beenline，并登录到hive。expect 是建立在tcl基础上的一个自动化交
互套件, 在一些需要交互输入指令的场景下, 可通过脚本 设置自动进行交互通信。
1、安装expect
2、 创建脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 cd /export/server/hive-3.1.2/bin vim beenline.exp # !/bin/expect spawn beeline set timeout 5 expect "beeline>" send "!connect jdbc:hive2://node1:10000\r" expect "Enter username for jdbc:hive2://node1:10000:" send "root\r" expect "Enter password for jdbc:hive2://node1:10000:" send "root\r" interact
3、修改脚本权限
4、启动脚本
5、退出beeline
1 0: jdbc:hive2://node3:10000> !quit
6、创建shell脚本
1 2 3 4 5 6 7 vim /export/server/hive-3.1.2/bin/beeline2 # !/bin/bash expect /export/server/hive-3.1.2/bin/beeline.exp chmod 777 /export/server/hive-3.1.2/bin/beeline2
7、最终调用
Hive配置 可以通过三种方式对 Hive 的相关属性进行配置，分别介绍如下：
配置文件 方式一为使用配置文件，使用配置文件指定的配置是永久有效的。Hive 有以下三个可选的配置文件：
hive-site.xml ：Hive 的主要配置文件；
hivemetastore-site.xml： 关于元数据的配置；
hiveserver2-site.xml：关于 HiveServer2 的配置。
示例如下,在 hive-site.xml 配置 hive.exec.scratchdir：
1 2 3 4 5 <property > <name > hive.exec.scratchdir</name > <value > /tmp/mydir</value > <description > Scratch space for Hive jobs</description > </property >
hiveconf 方式二为在启动命令行 (Hive CLI / Beeline) 的时候使用 --hiveconf 指定配置，这种方式指定的配置作用于整个 Session。
1 hive --hiveconf hive.exec.scratchdir=/tmp/mydir
set 方式三为在交互式环境下 (Hive CLI / Beeline)，使用 set 命令指定。这种设置的作用范围也是 Session 级别的，配置对于执行该命令后的所有命令生效。set 兼具设置参数和查看参数的功能。如下：
1 2 3 4 5 6 7 8 0: jdbc:hive2://hadoop001:10000> set hive.exec.scratchdir=/tmp/mydir; No rows affected (0.025 seconds) 0: jdbc:hive2://hadoop001:10000> set hive.exec.scratchdir; +----------------------------------+--+ | set | +----------------------------------+--+ | hive.exec.scratchdir=/tmp/mydir | +----------------------------------+--+
配置优先级 配置的优先顺序如下 (由低到高)：hive-site.xml - >hivemetastore-site.xml- > hiveserver2-site.xml - > -- hiveconf- > set
配置参数 Hive 可选的配置参数非常多，在用到时查阅官方文档即可AdminManual Configuration
Hive 可视化工具 DataGrip 、Dbeaver、SQuirrel SQL Client等
可以在Windows、MAC平台中通过JDBC连接HiveServer2的图形界面工具 ；
这类工具往往专门针对SQL类软件进行开发优化、页面美观大方，操作简洁，更重要的是SQL编辑环境优雅；
SQL语法智能提示补全、关键字高亮、查询结果智能显示、按钮操作大于命令操作；
DataGrip DataGrip是由JetBrains公司推出的数据库管理软件，DataGrip支持几乎所有主流的关系数据库产品，如DB2、Derby、MySQL、Oracle、SQL Server等，也支持几乎所有主流的大数据生态圈SQL软件，并且提供了简单易用的界面，开发者上手几乎不会遇到任何困难。
1.windows创建工程文件夹
2.DataGrip中创建新Project
3.关联本地工程文件夹
4.DataGrip连接Hive
5.配置Hive JDBC连接驱动
6.配置Hive JDBC连接驱动
7.返回，配置Hiveserver2服务连接信息
8.返回，配置Hiveserver2服务连接信息
HUE HUE相关的使用
HUE: hadoop 用户体验
出现目的: 提升使用hadoop生态圈中相关软件便利性 （不止能操作HIVE）
核心: 是将各类hadoop生态圈的软件的操作界面集成在一个软件中 (大集成者)
如何HUE界面呢?
用户密码：hue/hue
Hive 常用 DDL 操作
和mysql DDL语法类似
Database 在Hive中，默认的数据库叫做default，存储数据位置位于HDFS 的/user/hive/warehouse下。/user/hive/warehouse/database_namedb下
在hive中databases和schemas是一样的概念，都表示数据库。
查看数据列表
使用数据库 新建数据库 语法：
1 2 3 4 CREATE (DATABASE| SCHEMA) [IF NOT EXISTS ] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name= property_value, ...)];
示例：
1 2 3 CREATE DATABASE IF NOT EXISTS hive_test COMMENT 'hive database for test' WITH DBPROPERTIES ('create' = 'zuoer' );
查看数据库信息 语法：
1 DESC DATABASE [EXTENDED] db_name;
示例：
1 DESC DATABASE EXTENDED hive_test;
删除数据库 语法：
1 DROP (DATABASE| SCHEMA) [IF EXISTS ] database_name [RESTRICT| CASCADE];
默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。
示例：
1 DROP DATABASE IF EXISTS hive_test CASCADE;
创建表 建表语法 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 CREATE [TEMPORARY] [EXTERNAL ] TABLE [IF NOT EXISTS ] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ... [constraint_specification])] [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 ] [SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] ] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name= property_value, ...)] [AS select_statement];
1.建表语句注释不要带分号
内部表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 CREATE TABLE emp( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"; 1 、内部表认为该表独占表数据文件，该文件不能共享2 、内部表对表文件有绝对的控制权3 、当删除内部表时，表文件会跟着一起删除（同时删除元数据和表数据）4 、所有的非共享表都可以设置为内部表
外部表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE EXTERNAL TABLE emp_external( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_external' ; 1 、外部表认为该表不能独占表数据文件，文件可以共享2 、外部表对表文件没有绝对的控制权3 、当删除外部表时，表文件不会跟着一起删除（只会删除mysql中的hive元数据（映射信息），不会删除HDFS中的表数据）4 、所有的非共享表都可以设置为内部表5 、如果表数据需要被共享，则可以设置为外部表
使用 desc format emp_external 命令可以查看表的详细信息如下：
分区表
比如你做数据分析时，本来100G的全量文件，可能分区后只需要查询1G的文件进行分析。查询速度会快很多。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 CREATE EXTERNAL TABLE emp_partition( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ) ) PARTITIONED BY (deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_partition' ; #分区就是分文件夹 1 、分区表实际是就是对要进行分析的文件进行分类管理2 、本质是将相同特征的文件存放在同一个文件夹下，通过文件夹对数据进行分类3 、分区之后在查询时，可以通过添加条件，避免进行全表扫描，提高查询效率4 、分区表又分为静态分区和动态分区5 、分区表是一种优化手段，是锦上添花的东西，一张表可以没有分区，但是查询效率可能会比较低
分桶表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 CREATE EXTERNAL TABLE emp_bucket( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) CLUSTERED BY (empno) SORTED BY (empno ASC ) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_bucket' ; 1 、分桶表和分区表没什么关系2 、分桶表是将表数据分到多个文件，分区表是将数据分到多个文件夹3 、分桶表底层就是MapReduce中分区4 、分桶和分区的区别 1 )MapReduce的分区就是Hive的分桶 2 )Hive的分桶就是MapReduce的分区 3 )Hive的分区和MapReduce分区没什么关系 5 、结论：分桶就是分文件6 、分桶的本质就是将大表进行拆分变成小表，小表好join
倾斜表 通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。
1 2 3 4 5 6 7 8 9 10 11 12 CREATE EXTERNAL TABLE emp_skewed( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ) ) SKEWED BY (empno) ON (66 ,88 ,100 ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_skewed' ;
临时表 临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：
1 2 3 4 5 6 7 8 9 10 CREATE TEMPORARY TABLE emp_temp( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
CTAS创建表 支持从查询语句的结果创建表：
1 CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno= '20' ;
复制表结构 语法：
1 2 3 CREATE [TEMPORARY] [EXTERNAL ] TABLE [IF NOT EXISTS ] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path];
示例：
1 CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp
加载数据到表 加载数据到表中属于 DML 操作，这里为了方便大家测试，先简单介绍一下加载本地数据到表中：
1 2 3 4 5 6 7 8 9 10 11 load data local inpath "/usr/file/emp.txt" into table emp; #2 、给内部表加载数据- 从Linux本地- 复制 #将Linux本地的文件复制到表 目录：/ user / hive/ warehouse/ myhive.db/ t_covid load data local inpath '/root/test/covid19.dat' into table t_covid; #2 、给内部表加载数据- 从HDFS加载- 剪切 #将HDFS文件剪切到表目录：/ user / hive/ warehouse/ myhive.db/ t_covid load data inpath '/input/covid19.dat' into table t_covid;
其中 emp.txt 的内容如下，你可以直接复制使用，也可以到本仓库的resources 目录下载：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10
加载后可查询表中数据：
加载数据到表几种方式区分
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 drop table if exists myhive.score2;create table if not exists myhive.score2( sid string, cid string, sscore int ) row format delimited fields terminated by '\t' ;insert into score2 values ('08' ,'02' ,80 ),('09' ,'02' ,80 ),('10' ,'02' ,80 );hadoop fs - put score.txt / user / hive/ warehouse/ myhive.db/ score select * from score2;load data local inpath '/root/hive_data/test/score.txt' overwrite into table myhive.score2; insert into score2select * from score where sscore > 80 ;create table score3 as select * from score;sqoop框架： MySQL/ Oracle = = = = = = = = = = = > Hive表 Kettle框架: MySQL/ Oracle = = = = = = = = = = = > Hive表 create external table t_covid2( dt string comment '日期' , country string comment '县' , state string comment '州' , country_code string comment '县编码' , cases int comment '确诊人数' , deaths int comment '死亡任务' )comment '美国新冠数据' row format delimited fields terminated by ',' location '/input/data' ;
查询的数据如何导出 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 insert overwrite local directory '/root/hive_data/export_hive' select * from score where sscore > 85 ;insert overwrite local directory '/root/hive_data/export_hive' row format delimited fields terminated by ',' select * from score where sscore > 85 ;insert overwrite directory '/output/hive_data' row format delimited fields terminated by ',' select * from score where sscore > 85 ;insert overwrite into table 目标表select 字段 from 原表 where 条件;sqoop Kettle Datax Presto
修改表 重命名表 语法：
1 ALTER TABLE table_name RENAME TO new_table_name;
示例：
1 ALTER TABLE emp_temp RENAME TO new_emp;
修改列 语法：
1 2 ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN ] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST | AFTER column_name] [CASCADE| RESTRICT];
示例：
1 2 3 4 5 6 7 8 ALTER TABLE emp_temp CHANGE empno empno_new INT ; ALTER TABLE emp_temp CHANGE sal sal_new decimal (7 ,2 ) AFTER ename;ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr' ;
新增列 示例：
1 ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address' );
清空表/删除表 清空表 语法：
1 2 TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 Cannot truncate non-managed table XXXX。
示例：
1 TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno= 20 );
删除表 语法：
1 DROP TABLE [IF EXISTS ] table_name [PURGE];
内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）。
其他命令 Describe 查看数据库：
1 DESCRIBE | Desc DATABASE [EXTENDED] db_name;
查看表：
1 2 3 4 5 6 7 8 9 10 11 12 DESCRIBE | Desc [EXTENDED| FORMATTED] table_name desc t_team_ace_player; desc formatted t_team_ace_player; #如何判断一张表是内部表还是外部表，通过元数据查看 desc formatted t_covid;Table Type: ,EXTERNAL_TABLE #外部表Table Type: ,MANAGED_TABLE #内部表# Partition Infomation = > 显示的是分区字段信息
Show 1. 查看数据库列表
1 2 3 4 5 SHOW (DATABASES| SCHEMAS) [LIKE 'identifier_with_wildcards' ];SHOW DATABASES like 'hive*' ;
LIKE 子句允许使用正则表达式进行过滤，但是 SHOW 语句当中的 LIKE 子句只支持 *（通配符）和 |（条件或）两个符号。例如 employees，emp *，emp * | * ees，所有这些都将匹配名为 employees 的数据库。
2. 查看表的列表
1 2 3 4 5 SHOW TABLES [IN database_name] ['identifier_with_wildcards' ];SHOW TABLES IN default ;
3. 查看视图列表
1 SHOW VIEWS [IN / FROM database_name] [LIKE 'pattern_with_wildcards' ];
4. 查看表的分区列表
1 SHOW PARTITIONS table_name;
5. 查看表/视图的创建语句
1 SHOW CREATE TABLE ([db_name.]table_name| view_name);
Hive 分区表和分桶表
MR的分区 就是hive的分桶
hive的分桶就是MR的分区
hive的分区和MR分区没有关系
hive中分桶就是分文件，分区就是分目录
分区表 概念 Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。
分区为 HDFS 上表目录的子目录 ，数据按照分区存储在子目录中。如果查询的 where 字句的中包含分区条件，则直接从该分区去查找，而不是扫描整个表目录，合理的分区设计可以极大提高查询速度和性能。
这里说明一下分区表并非 Hive 独有的概念，实际上这个概念非常常见。比如在我们常用的 Oracle 数据库中，当表中的数据量不断增大，查询数据的速度就会下降，这时也可以对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据存放到多个表空间（物理文件上），这样查询数据时，就不必要每次都扫描整张表，从而提升查询性能。
使用场景 通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。
创建分区表 在 Hive 中可以使用 PARTITIONED BY 子句创建分区表。表可以包含一个或多个分区列，程序会为分区列中的每个不同值组合创建单独的数据目录。下面的我们创建一张雇员表作为测试：
1 2 3 4 5 6 7 8 9 10 11 12 CREATE EXTERNAL TABLE emp_partition( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2) ) PARTITIONED BY (deptno INT) -- 按照部门编号进行分区 ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_ partition';
加载数据到分区表 加载数据到分区表时候必须要指定数据所处的分区：
1 2 3 4 # 加载部门编号为20的数据到表中 LOAD DATA LOCAL INPATH "/usr/file/emp20.txt" OVERWRITE INTO TABLE emp_partition PARTITION (deptno=20) # 加载部门编号为30的数据到表中 LOAD DATA LOCAL INPATH "/usr/file/emp30.txt" OVERWRITE INTO TABLE emp_ partition PARTITION (deptno=30)
查看分区目录 这时候我们直接查看表目录，可以看到表目录下存在两个子目录，分别是 deptno=20 和 deptno=30,这就是分区目录，分区目录下才是我们加载的数据文件。
1 # hadoop fs -ls hdfs://hadoop001:8020/hive/emp_partition/
这时候当你的查询语句的 where 包含 deptno=20，则就去对应的分区目录下进行查找，而不用扫描全表。
静态分区 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 #静态分区就是手动来操作分区的文件夹 # drop table t_covid;create external table t_covid( dt_value string , country string , state string , country_code string , cases int , deaths int ) partitioned by (dt string) row format delimited fields terminated by ',' ; load data local inpath '/root/hive_data/covid-28.dat' into table t_covid partition (dt= '2021-01-28' ); load data local inpath '/root/hive_data/covid-29.dat' into table t_covid partition (dt= '2021-01-29' ); load data local inpath '/root/hive_data/covid-30.dat' into table t_covid partition (dt= '2021-01-30' ); select * from t_covid; select * from t_covid where dt= '2021-01-28' ; select * from t_covid where dt= '2021-01-28' or dt= '2021-01-29' ; # drop table t_covid2;create table t_covid2( dt_value string , country string , state string , country_code string , cases int , deaths int ) partitioned by (month string,dt string) row format delimited fields terminated by ',' ; load data local inpath '/root/hive_data/covid-28.dat' into table t_covid2 partition (month = '2021-01' ,dt= '2021-01-28' ); load data local inpath '/root/hive_data/covid-29.dat' into table t_covid2 partition (month = '2021-01' ,dt= '2021-01-29' ); load data local inpath '/root/hive_data/covid-30.dat' into table t_covid2 partition (month = '2021-01' ,dt= '2021-01-30' ); load data local inpath '/root/hive_data/2_month/covid-28.dat' into table t_covid2 partition (month = '2021-02' , dt= '2021-02-28' ); load data local inpath '/root/hive_data/2_month/covid-29.dat' into table t_covid2 partition (month = '2021-02' , dt= '2021-02-29' ); load data local inpath '/root/hive_data/2_month/covid-30.dat' into table t_covid2 partition (month = '2021-02' , dt= '2021-02-30' ); select * from t_covid2; select * from t_covid2 where month = '2021-02' ; select * from t_covid2 where month = '2021-02' and dt = '2021-02-28' ; alter table t_covid2 add partition (month = '2021-03' ,dt= '2021-03-28' );
注意：添加分区之后就可以在hdfs文件系统当中看到表下面多了一个文件夹
动态分区 1 # 动态分区就是Hive可以根据数据本身的特点来自动创建分区文件夹
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 set hive.exec.dynamic.partition= true ; set hive.exec.dynamic.partition.mode= nonstrict; use myhive; create table myhive.test1( id int , date_val string, name string, score int ) row format delimited fields terminated by '\t' ;; load data local inpath '/root/hive_data/dynamic_partition/a.txt' into table test1; select * from test1;drop table test2;create table myhive.test2( id int , name string, score int ) partitioned by (dt string) row format delimited fields terminated by '\t' ;; insert overwrite table test2select id,name,score,date_val from test1; select * from test2;select * from test2 where dt= '2022-01-01' ;select * from test2 where dt= '2022-01-02' ;select * from test2 where dt= '2022-01-03' ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 drop table if exists test3;create table test3( id int , date_val string, name string, sex string, score int ) row format delimited fields terminated by '\t' ; ; load data local inpath '/root/hive_data/dynamic_partition/b.txt' overwrite into table test3; select * from test3;drop table test4;create table test4( id int , name string, score int ) partitioned by (dt string,gender string) row format delimited fields terminated by '\t' ; ; insert overwrite table test4select id,name,score,date_val,sex from test3; select * from test4;select * from test4 where dt= '2022-01-01' and gender= 'f' ;select * from test4 where gender= 'f' ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 drop table if exists test5;create table test5( dt_value string , country string , state string , country_code string , cases int , deaths int ) row format delimited fields terminated by ',' ; ; load data local inpath '/root/hive_data/dynamic_partition/c.txt' overwrite into table test5; select * from test5;drop table test6;create table test6( country string , state string , country_code string , cases int , deaths int ) partitioned by (year string,month string, dt string) row format delimited fields terminated by ',' ; ; insert overwrite table test6select country,state,country_code,cases,deaths, substring (dt_value,1 ,4 ), substring (dt_value,6 ,2 ), substring (dt_value,-2 ,2 ) from test5; select * from test6;select * from test6 where year = '2021' and month = '01' ;
分桶表
分桶就是将数据划分到不同的文件，其实就是MapReduce的分区
将数据按照指定的字段进行分成多个桶中去，说白了就是将数据按照字段进行划分，可以将数据按照字段划分到多个文件当中去
作用：
1、提高Join的效率
如果2张表，没有处理的话，关联的时候A表的字段会去全表扫描B表，效率很低。而分桶后，扫描桶相同的，扫描数据量少了，效率高了。
桶相同的进行join，就避免了全部扫描是否匹配，提高了效率。
ps: 分桶要可以排序，更加高效
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 新冠数据:covid19.dat set hive.enforce.bucketing= true ;drop table t_covid_common;create table t_covid_common( dt_value string , country string , state string , country_code string , cases int , deaths int ) row format delimited fields terminated by ',' ; load data local inpath '/root/hive_data/covid19.dat' overwrite into table t_covid_common; select * from t_covid_common;drop table t_covid_bucket;create table t_covid_bucket( dt_value string , country string , state string , country_code string , cases int , deaths int ) clustered by (country_code) into 5 buckets row format delimited fields terminated by ',' ;insert overwrite table t_covid_bucketselect * from t_covid_common cluster by (country_code);
简介 分区提供了一个隔离数据和优化查询的可行方案，但是并非所有的数据集都可以形成合理的分区，分区的数量也不是越多越好，过多的分区条件可能会导致很多分区上没有数据。同时 Hive 会限制动态分区可以创建的最大分区数，用来避免过多分区文件对文件系统产生负担。鉴于以上原因，Hive 还提供了一种更加细粒度的数据拆分方案：分桶表 (bucket Table)。
分桶表会将指定列的值进行哈希散列，并对 bucket（桶数量）取余，然后存储到对应的 bucket（桶）中。
理解分桶表 单从概念上理解分桶表可能会比较晦涩，其实和分区一样，分桶这个概念同样不是 Hive 独有的，对于 Java 开发人员而言，这可能是一个每天都会用到的概念，因为 Hive 中的分桶概念和 Java 数据结构中的 HashMap 的分桶概念是一致的。
当调用 HashMap 的 put() 方法存储数据时，程序会先对 key 值调用 hashCode() 方法计算出 hashcode，然后对数组长度取模计算出 index，最后将数据存储在数组 index 位置的链表上，链表达到一定阈值后会转换为红黑树 (JDK1.8+)。下图为 HashMap 的数据结构图：
图片引用自：HashMap vs. Hashtable
创建分桶表 在 Hive 中，我们可以通过 CLUSTERED BY 指定分桶列，并通过 SORTED BY 指定桶中数据的排序参考列。下面为分桶表建表语句示例：
1 2 3 4 5 6 7 8 9 10 11 12 CREATE EXTERNAL TABLE emp_bucket( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) CLUSTERED BY (empno) SORTED BY (empno ASC ) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_bucket' ;
加载数据到分桶表 这里直接使用 Load 语句向分桶表加载数据，数据时可以加载成功的，但是数据并不会分桶。
这是由于分桶的实质是对指定字段做了 hash 散列然后存放到对应文件中，这意味着向分桶表中插入数据是必然要通过 MapReduce，且 Reducer 的数量必须等于分桶的数量。由于以上原因，分桶表的数据通常只能使用 CTAS(CREATE TABLE AS SELECT) 方式插入，因为 CTAS 操作会触发 MapReduce。加载数据步骤如下：
1、设置强制分桶
1 set hive.enforce.bucketing = true ;
在 Hive 0.x and 1.x 版本，必须使用设置 hive.enforce.bucketing = true，表示强制分桶，允许程序根据表结构自动选择正确数量的 Reducer 和 cluster by column 来进行分桶。
2、CTAS导入数据
1 INSERT INTO TABLE emp_bucket SELECT * FROM emp;
可以从执行日志看到 CTAS 触发 MapReduce 操作，且 Reducer 数量和建表时候指定 bucket 数量一致：
查看分桶文件 bucket(桶) 本质上就是表目录下的具体文件：
分区表和分桶表结合使用 分区表和分桶表的本质都是将数据按照不同粒度进行拆分，从而使得在查询时候不必扫描全表，只需要扫描对应的分区或分桶，从而提升查询效率。两者可以结合起来使用，从而保证表数据在不同粒度上都能得到合理的拆分。下面是 Hive 官方给出的示例：
1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE page_view_bucketed( viewTime INT , userid BIGINT , page_url STRING, referrer_url STRING, ip STRING ) PARTITIONED BY (dt STRING) CLUSTERED BY (userid) SORTED BY (viewTime) INTO 32 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001' COLLECTION ITEMS TERMINATED BY '\002' MAP KEYS TERMINATED BY '\003' STORED AS SEQUENCEFILE;
此时导入数据时需要指定分区：
1 2 3 INSERT OVERWRITE page_view_bucketed PARTITION (dt='2009-02-25') SELECT * FROM page_view WHERE dt='2009-02-25';
一般前期采集分区，后续数据分析查询慢时分桶
Hive 视图和索引 视图 简介 Hive 中的视图和 RDBMS 中视图的概念一致，都是一组数据的逻辑表示，本质上就是一条 SELECT 语句的结果集。视图是纯粹的逻辑对象，没有关联的存储 (Hive 3.0.0 引入的物化视图除外)，当查询引用视图时，Hive 可以将视图的定义与查询结合起来，例如将查询中的过滤器推送到视图中。
创建视图 1 2 3 4 5 CREATE VIEW [IF NOT EXISTS ] [db_name.]view_name [(column_name [COMMENT column_comment], ...) ] [COMMENT view_comment] [TBLPROPERTIES (property_name = property_value, ...)] AS SELECT ...;
在 Hive 中可以使用 CREATE VIEW 创建视图，如果已存在具有相同名称的表或视图，则会抛出异常，建议使用 IF NOT EXISTS 预做判断。在使用视图时候需要注意以下事项：
视图是只读的，不能用作 LOAD / INSERT / ALTER 的目标；
在创建视图时候视图就已经固定，对基表的后续更改（如添加列）将不会反映在视图；
删除基表并不会删除视图，需要手动删除视图；
视图可能包含 ORDER BY 和 LIMIT 子句。如果引用视图的查询语句也包含这类子句，其执行优先级低于视图对应字句。例如，视图 custom_view 指定 LIMIT 5，查询语句为 select * from custom_view LIMIT 10，此时结果最多返回 5 行。
创建视图时，如果未提供列名，则将从 SELECT 语句中自动派生列名；
创建视图时，如果 SELECT 语句中包含其他表达式，例如 x + y，则列名称将以_C0，_C1 等形式生成；
1 CREATE VIEW IF NOT EXISTS custom_view AS SELECT empno, empno+ deptno , 1 + 2 FROM emp;
查看视图 1 2 3 4 5 6 show tables;desc view_name;desc formatted view_name;
删除视图 1 DROP VIEW [IF EXISTS ] [db_name.]view_name;
删除视图时，如果被删除的视图被其他视图所引用，这时候程序不会发出警告，但是引用该视图其他视图已经失效，需要进行重建或者删除。
修改视图 1 ALTER VIEW [db_name.]view_name AS select_statement;
被更改的视图必须存在，且视图不能具有分区，如果视图具有分区，则修改失败。
修改视图属性 语法：
1 2 3 4 ALTER VIEW [db_name.]view_name SET TBLPROPERTIES table_properties; table_properties: : (property_name = property_value, property_name = property_value, ...)
示例：
1 ALTER VIEW custom_view SET TBLPROPERTIES ('create' = 'heibaiying' ,'date' = '2019-05-05' );
索引 简介 Hive 在 0.7.0 引入了索引的功能，索引的设计目标是提高表某些列的查询速度。如果没有索引，带有谓词的查询（如’WHERE table1.column = 10’）会加载整个表或分区并处理所有行。但是如果 column 存在索引，则只需要加载和处理文件的一部分。
索引原理 在指定列上建立索引，会产生一张索引表（表结构如下），里面的字段包括：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。在查询涉及到索引字段时，首先到索引表查找索引列值对应的 HDFS 文件路径及偏移量，这样就避免了全表扫描。
1 2 3 4 5 6 7 +--------------+----------------+----------+--+ | col_name | data_ type | comment | +--------------+----------------+----------+--+ | empno | int | 建立索引的列 | | _bucketname | string | HDFS 文件路径 | | _ offsets | array<bigint > +--------------+----------------+----------+--+
创建索引 1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE INDEX index_name ON TABLE base_table_name (col_name, ...) AS index_type [WITH DEFERRED REBUILD] [IDXPROPERTIES (property_name= property_value, ...)] [IN TABLE index_table_name] [ [ ROW FORMAT ...] STORED AS ... | STORED BY ... ] [LOCATION hdfs_path] [TBLPROPERTIES (...)] [COMMENT "index comment"];
查看索引 1 2 SHOW FORMATTED INDEX ON table_name;
删除索引 删除索引会删除对应的索引表。
1 DROP INDEX [IF EXISTS ] index_name ON table_name;
如果存在索引的表被删除了，其对应的索引和索引表都会被删除。如果被索引表的某个分区被删除了，那么分区对应的分区索引也会被删除。
重建索引 1 ALTER INDEX index_name ON table_name [PARTITION partition_spec] REBUILD;
重建索引。如果指定了 PARTITION，则仅重建该分区的索引。
索引案例 创建索引 在 emp 表上针对 empno 字段创建名为 emp_index,索引数据存储在 emp_index_table 索引表中
1 2 3 4 create index emp_index on table emp(empno) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table emp_index_table ;
此时索引表中是没有数据的，需要重建索引才会有索引的数据。
重建索引 1 alter index emp_index on emp rebuild;
Hive 会启动 MapReduce 作业去建立索引，建立好后查看索引表数据如下。三个表字段分别代表：索引列的值、该值对应的 HDFS 文件路径、该值在文件中的偏移量。
自动使用索引 默认情况下，虽然建立了索引，但是 Hive 在查询时候是不会自动去使用索引的，需要开启相关配置。开启配置后，涉及到索引列的查询就会使用索引功能去优化查询。
1 2 3 SET hive.input.format= org.apache.hadoop.hive.ql.io.HiveInputFormat;SET hive.optimize.index.filter= true ;SET hive.optimize.index.filter.compact.minsize= 0 ;
查看索引
索引的缺陷 索引表最主要的一个缺陷在于：索引表无法自动 rebuild，这也就意味着如果表中有数据新增或删除，则必须手动 rebuild，重新执行 MapReduce 作业，生成索引表数据。
同时按照官方文档 的说明，Hive 会从 3.0 开始移除索引功能，主要基于以下两个原因：
具有自动重写的物化视图 (Materialized View) 可以产生与索引相似的效果（Hive 2.3.0 增加了对物化视图的支持，在 3.0 之后正式引入）。
使用列式存储文件格式（Parquet，ORC）进行存储时，这些格式支持选择性扫描，可以跳过不需要的文件或块。
ORC 内置的索引功能可以参阅这篇文章：Hive 性能优化之 ORC 索引–Row Group Index vs Bloom Filter Index
Hive 常用 DML 操作 在Hive中建表成功之后，就会在HDFS上创建一个与之对应的文件夹，且文件夹名字就是表名；
文件夹父路径是由参数hive.metastore.warehouse.dir控制，默认值是/user/hive/warehouse；
我们可以在web端直接暴力上传但是命令行操作是要会的。（不推荐）
我们可以用hadoop的上传命令，将数据化数据文件上传到hive的对应库对应表目录下。但是这样相当于跳过了hive，直接使用hadoop命令了。（不推荐）
1 hadoop fs -put 1.txt /user/hive/warehouse/hive_test.db/t_1
官方推荐使用hive的LOAD加载数据。（推荐）
加载文件数据到表 语法:
1 2 3 4 5 6 7 8 LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)] -- 从本地加载数据 数据位于HS2（node1）本地文件系统 本质是hadoop fs -put上传操作 LOAD DATA LOCAL INPATH '/opt/data/hive3_data/students.txt' INTO TABLE student_ local; --从HDFS加载数据 数据位于HDFS文件系统根目录下 本质是hadoop fs -mv 移动操作 --先把数据上传到HDFS上 hadoop fs -put /root/hivedata/students.txt / LOAD DATA INPATH '/students.txt' INTO TABLE student_HDFS;
LOCAL 关键字代表从本地(hive服务所在机器的本地)文件系统加载文件，省略则代表从 HDFS 上加载文件：
filepath 可以是文件路径 (在这种情况下 Hive 会将文件移动到表中)，也可以目录路径 (在这种情况下，Hive 会将该目录中的所有文件移动到表中)；
如果使用 OVERWRITE 关键字，则将删除目标表（或分区）的内容，使用新的数据填充；不使用此关键字，则数据以追加的方式加入；
加载的目标可以是表或分区。如果是分区表，则必须指定加载数据的分区；
加载文件的格式必须与建表时使用 STORED AS 指定的存储格式相同。
使用建议：
不论是本地路径还是 URL 都建议使用完整的 。虽然可以使用不完整的 URL 地址，此时 Hive 将使用 hadoop 中的 fs.default.name 配置来推断地址，但是为避免不必要的错误，建议使用完整的本地路径或 URL 地址；
加载对象是分区表时建议显示指定分区 。在 Hive 3.0 之后，内部将加载 (LOAD) 重写为 INSERT AS SELECT，此时如果不指定分区，INSERT AS SELECT 将假设最后一组列是分区列，如果该列不是表定义的分区，它将抛出错误。为避免错误，还是建议显示指定分区。
示例：
新建分区表：
1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE emp_ptn( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ) ) PARTITIONED BY (deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
从 HDFS 上加载数据到分区表：
1 LOAD DATA INPATH "hdfs://hadoop001:8020/mydir/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno= 20 );
emp.txt 文件可在本仓库的 resources 目录中下载
加载后表中数据如下,分区列 deptno 全部赋值成 20：
beenline执行加载可以看到加载过程的日志，学习时可以使用beenline客户端看一看
查询结果插入到表
在hive中，用标准insert语法插入，可以，但是非常不推荐。hive中insert into走的是MR，插入1条测试下来都需要45s。直接把结构化数据准备好，LOAD插入它不香吗。
语法：
1 2 3 4 5 INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1= val1, partcol2= val2 ...) [IF NOT EXISTS ]] select_statement1 FROM from_statement; INSERT INTO TABLE tablename1 [PARTITION (partcol1= val1, partcol2= val2 ...)] select_statement1 FROM from_statement;
Hive 0.13.0 开始，建表时可以通过使用 TBLPROPERTIES（“immutable”=“true”）来创建不可变表 (immutable table) ，如果不可以变表中存在数据，则 INSERT INTO 失败。（注：INSERT OVERWRITE 的语句不受 immutable 属性的影响）;
可以对表或分区执行插入操作。如果表已分区，则必须通过指定所有分区列的值来指定表的特定分区；
从 Hive 1.1.0 开始，TABLE 关键字是可选的；
从 Hive 1.2.0 开始 ，可以采用 INSERT INTO tablename(z，x，c1) 指明插入列；
可以将 SELECT 语句的查询结果插入多个表（或分区），称为多表插入。语法如下：
1 2 3 4 5 FROM from_statementINSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1= val1, partcol2= val2 ...) [IF NOT EXISTS ]] select_statement1 [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS ]] select_statement2] [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
动态插入分区：
1 2 3 4 5 INSERT OVERWRITE TABLE tablename PARTITION (partcol1[= val1], partcol2[= val2] ...) select_statement FROM from_statement; INSERT INTO TABLE tablename PARTITION (partcol1[= val1], partcol2[= val2] ...) select_statement FROM from_statement;
在向分区表插入数据时候，分区列名是必须的，但是列值是可选的。如果给出了分区列值，我们将其称为静态分区，否则它是动态分区。动态分区列必须在 SELECT 语句的列中最后指定，并且与它们在 PARTITION() 子句中出现的顺序相同。
注意：Hive 0.9.0 之前的版本动态分区插入是默认禁用的，而 0.9.0 之后的版本则默认启用。以下是动态分区的相关配置：
配置
默认值
说明
hive.exec.dynamic.partitiontrue需要设置为 true 才能启用动态分区插入
hive.exec.dynamic.partition.modestrict在严格模式 (strict) 下，用户必须至少指定一个静态分区，以防用户意外覆盖所有分区，在非严格模式下，允许所有分区都是动态的
hive.exec.max.dynamic.partitions.pernode100
允许在每个 mapper/reducer 节点中创建的最大动态分区数
hive.exec.max.dynamic.partitions1000
允许总共创建的最大动态分区数
hive.exec.max.created.files100000
作业中所有 mapper/reducer 创建的 HDFS 文件的最大数量
hive.error.on.empty.partitionfalse如果动态分区插入生成空结果，是否抛出异常
示例:
新建 emp 表，作为查询对象表
1 2 3 4 5 6 7 8 9 10 11 12 13 CREATE TABLE emp( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"; load data local inpath "/usr/file/emp.txt" into table emp;
完成后 emp 表中数据如下：
为清晰演示，先清空 emp_ptn 表中加载的数据：
静态分区演示：从 emp 表中查询部门编号为 20 的员工数据，并插入 emp_ptn 表中，语句如下：
1 2 INSERT OVERWRITE TABLE emp_ptn PARTITION (deptno= 20 ) SELECT empno,ename,job,mgr,hiredate,sal,comm FROM emp WHERE deptno= 20 ;
完成后 emp_ptn 表中数据如下：
接着演示动态分区：
1 2 3 4 5 6 set hive.exec.dynamic.partition.mode= nonstrict;INSERT OVERWRITE TABLE emp_ptn PARTITION (deptno) SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno FROM emp WHERE deptno= 30 ;
完成后 emp_ptn 表中数据如下：
insert+select需要保证查询结果列的数目和需要插入数据表格的列数目一致，如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为NULL。
使用SQL语句插入值 1 2 INSERT INTO TABLE tablename [PARTITION (partcol1[= val1], partcol2[= val2] ...)] VALUES ( value [, value ...] )
使用时必须为表中的每个列都提供值。不支持只向部分列插入值（可以为缺省值的列提供空值来消除这个弊端）；
如果目标表表支持 ACID 及其事务管理器，则插入后自动提交；
不支持支持复杂类型 (array, map, struct, union) 的插入。
更新和删除数据 语法：
更新和删除的语法比较简单，和关系型数据库一致。需要注意的是这两个操作都只能在支持 ACID 的表，也就是事务表上才能执行。
1 2 3 4 5 UPDATE tablename SET column = value [, column = value ...] [WHERE expression]DELETE FROM tablename [WHERE expression]
示例:
1. 修改配置
首先需要更改 hive-site.xml，添加如下配置，开启事务支持，配置完成后需要重启 Hive 服务。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 <property > <name > hive.support.concurrency</name > <value > true</value > </property > <property > <name > hive.enforce.bucketing</name > <value > true</value > </property > <property > <name > hive.exec.dynamic.partition.mode</name > <value > nonstrict</value > </property > <property > <name > hive.txn.manager</name > <value > org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value > </property > <property > <name > hive.compactor.initiator.on</name > <value > true</value > </property > <property > <name > hive.in.test</name > <value > true</value > </property >
2. 创建测试表
创建用于测试的事务表，建表时候指定属性 transactional = true 则代表该表是事务表。需要注意的是，按照官方文档 的说明，目前 Hive 中的事务表有以下限制：
必须是 buckets Table;
仅支持 ORC 文件格式；
不支持 LOAD DATA …语句。
1 2 3 4 5 6 CREATE TABLE emp_ts( empno int , ename String ) CLUSTERED BY (empno) INTO 2 BUCKETS STORED AS ORC TBLPROPERTIES ("transactional"= "true");
3. 插入测试数据
1 INSERT INTO TABLE emp_ts VALUES (1 ,"ming"),(2 ,"hong");
插入数据依靠的是 MapReduce 作业，执行成功后数据如下：
4. 测试更新和删除
1 2 3 4 5 UPDATE emp_ts SET ename = "lan" WHERE empno= 1 ;DELETE FROM emp_ts WHERE empno= 2 ;
更新和删除数据依靠的也是 MapReduce 作业，执行成功后数据如下：
查询结果写出到文件系统 语法：
1 2 3 INSERT OVERWRITE [LOCAL ] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format] SELECT ... FROM ...
OVERWRITE 关键字表示输出文件存在时，先删除后再重新写入；
和 Load 语句一样，建议无论是本地路径还是 URL 地址都使用完整的；
写入文件系统的数据被序列化为文本，其中列默认由^A 分隔，行由换行符分隔。如果列不是基本类型，则将其序列化为 JSON 格式。其中行分隔符不允许自定义，但列分隔符可以自定义，如下：
1 2 3 4 5 6 7 insert overwrite local directory './test-04' row format delimited FIELDS TERMINATED BY '\t' COLLECTION ITEMS TERMINATED BY ',' MAP KEYS TERMINATED BY ':' select * from src;
示例：
这里我们将上面创建的 emp_ptn 表导出到本地文件系统，语句如下：
1 2 3 4 INSERT OVERWRITE LOCAL DIRECTORY '/usr/file/ouput' ROW FORMAT DELIMITEDFIELDS TERMINATED BY '\t' SELECT * FROM emp_ptn;
导出结果如下：
Hive 常用 DQL 操作
使用hive SQL本质就是分析数据，数据分析最重要的就是查询
数据准备 为了演示查询操作，这里需要预先创建三张表，并加载测试数据。
数据文件 emp.txt 和 dept.txt 可以从本仓库的resources 目录下载。
员工表 1 2 3 4 5 6 7 8 9 10 11 12 13 14 CREATE TABLE emp( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ), deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"; LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp;
部门表 1 2 3 4 5 6 7 8 9 10 CREATE TABLE dept( deptno INT , dname STRING, loc STRING ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";LOAD DATA LOCAL INPATH "/usr/file/dept.txt" OVERWRITE INTO TABLE dept;
分区表 这里需要额外创建一张分区表，主要是为了演示分区查询：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 CREATE EXTERNAL TABLE emp_ptn( empno INT , ename STRING, job STRING, mgr INT , hiredate TIMESTAMP , sal DECIMAL (7 ,2 ), comm DECIMAL (7 ,2 ) ) PARTITIONED BY (deptno INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"; LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno= 20 ) LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno= 30 ) LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno= 40 ) LOAD DATA LOCAL INPATH "/usr/file/emp.txt" OVERWRITE INTO TABLE emp_ptn PARTITION (deptno= 50 )
单表查询 在查询过程中的执行顺序
from > where > group（含聚合）> having >order > select
SELECT 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 SELECT * FROM emp;select county, cases, deaths from t_usa_covid19;select state, cases + 10 as new_casesfrom t_covid; select state, cases + 10 new_casesfrom t_covid; set hive.compute.query.using.stats= false ; set hive.stats.column.autogather= false ;set hive.exec.mode.local.auto= true ; select count (* ) from t_covid; select count (1 ) from t_covid; select count (state) from t_covid; select max (cases) max_cases from t_covid;select min (cases) min_cases from t_covid;select sum (cases) total_cases from t_covid;select round(avg (cases), 2 ) from t_covid;select * from t_covid;select * from t_covid limit 3 ; select * from t_covid limit 3 ,2 ;
嵌套查询可以用with写法，看着更加清晰
WHERE 1 2 3 4 5 SELECT * FROM emp WHERE empno > 7782 AND deptno = 10 ;select * from t_usa_covid19 where length(state) > 10 ;
注意：where条件中不能使用聚合函数
1 2 3 4 5 6 -- 报错: SemanticException:Not yet supported place for UDAF ‘sum' --聚合函数要使用它的前提是结果集已经确定。 --而where子句还处于“确定”结果集的过程中，因而不能使用聚合函数。 select state,sum(deaths) from t_usa_ covid19 where sum(deaths) >100 group by state; --可以使用Having实现 select state,sum(deaths) from t_usa_ covid19 group by state having sum(deaths) > 100;
where 中还可以使用运算符，控制判断(is null)、between and、in等
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 select * from t_covidwhere deaths between 1000 and 2000 ; select * from t_covidwhere deaths between 1000 and 2000 ; select * from t_covidwhere state = 'Alaska' or state = 'New Mexico' ; select * from t_covidwhere state in ('Alaska' , 'New Mexico' );select * from t_covid where state like 'S%' ;select * from t_covid where state like '%s%' ;select * from t_covid where state rlike '[s]' ; select * from t_covidwhere cases >= 50000 and deaths >= 1000 ; select * from t_covidwhere state = 'Alaska' or state = 'New Mexico' ; select * from t_covidwhere state not in ('Alaska' , 'New Mexico' );select * from t_covidwhere deaths not between 1000 and 2000 ;
DISTINCT Hive 支持使用 DISTINCT 关键字去重。
1 2 3 4 5 SELECT DISTINCT job FROM emp;SELECT DISTINCT job1,job2 FROM emp;
分区查询 分区查询 (Partition Based Queries)，可以指定某个分区或者分区范围。
1 2 3 SELECT emp_ptn.* FROM emp_ptnWHERE emp_ptn.deptno >= 20 AND emp_ptn.deptno <= 40 ;
LIMIT
实际工作中，对于陌生的表，可能有几亿行数据，你直接select *，那就有的乐了。看你终端能不能接收，服务器缓存会不会崩等等。
dataGrip，默认会对select *对分页处理。YYDS
1 2 3 4 5 6 SELECT * FROM emp ORDER BY sal DESC LIMIT 5 ;select * from t_usa_covid19 where count_date = "2021-01-28" and state = "California" limit 2 ,3 ;
GROUP BY Hive 支持使用 GROUP BY 进行分组聚合操作。
1 2 3 4 set hive.map.aggr= true ;SELECT deptno,SUM (sal) FROM emp GROUP BY deptno;
hive.map.aggr 控制程序如何进行聚合。默认值为 false。如果设置为 true，Hive 会在 map 阶段就执行一次聚合。这可以提高聚合效率，但需要消耗更多内存。
ORDER AND SORT 可以使用 ORDER BY 或者 Sort BY 对查询结果进行排序，排序字段可以是整型也可以是字符串：如果是整型，则按照大小排序；如果是字符串，则按照字典序排序。ORDER BY 和 SORT BY 的区别如下：
使用 ORDER BY 时会有一个 Reducer 对全部查询结果进行排序，可以保证数据的全局有序性；
使用 SORT BY 时只会在每个 Reducer 中进行排序，这可以保证每个 Reducer 的输出数据是有序的，但不能保证全局有序。
由于 ORDER BY 的时间可能很长，如果你设置了严格模式 (hive.mapred.mode = strict)，则其后面必须再跟一个 limit 子句。
注 ：hive.mapred.mode 默认值是 nonstrict ，也就是非严格模式。
1 2 SELECT empno, deptno, sal FROM emp ORDER BY deptno ASC , sal DESC ;
HAVING 可以使用 HAVING 对分组数据进行过滤。
1 2 3 4 5 SELECT deptno,SUM (sal) FROM emp GROUP BY deptno HAVING SUM (sal)> 9000 ;select state,sum (deaths) as cnts from t_usa_covid19 where count_date = "2021-01-28" group by state having cnts> 10000 ;
DISTRIBUTE BY 如果想要把具有相同 Key 值的数据分发到同一个 Reducer 进行处理，这可以使用 DISTRIBUTE BY 字句。需要注意的是，DISTRIBUTE BY 虽然能把具有相同 Key 值的数据分发到同一个 Reducer，但是不能保证数据在 Reducer 上是有序的。情况如下：
把以下 5 个数据发送到两个 Reducer 上进行处理：
Reducer1 得到如下乱序数据：
Reducer2 得到数据如下：
如果想让 Reducer 上的数据是有序的，可以结合 SORT BY 使用 (示例如下)，或者使用下面我们将要介绍的 CLUSTER BY。
1 2 SELECT empno, deptno, sal FROM emp DISTRIBUTE BY deptno SORT BY deptno ASC ;
distribute by 对数据进行分区， sort by 对每个分区进行排序
排序好像不是很严谨(hash冲突可能分到同一个区)，后续有更好用的开窗函数
CLUSTER BY 如果 SORT BY 和 DISTRIBUTE BY 指定的是相同字段 ，且 SORT BY 排序规则是 ASC ，此时可以使用 CLUSTER BY 进行替换，同时 CLUSTER BY 可以保证数据在全局是有序的。
1 SELECT empno, deptno, sal FROM emp CLUSTER BY deptno ;
多表联结查询 Hive 支持内连接，外连接，左外连接，右外连接，笛卡尔连接，这和传统数据库中的概念是一致的，可以参见下图。
需要特别强调：JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定，否则就会先做笛卡尔积，再过滤，这会导致你得不到预期的结果 (下面的演示会有说明)。
INNER JOIN
INNER JOIN 和 JOIN 和 '表a,表b where 关联条件'(隐式连接)是等价的
1 2 3 4 5 6 7 8 9 10 11 12 SELECT e.* ,d.* FROM emp e JOIN dept d ON e.deptno = d.deptno WHERE empno= 7369 ;SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
LEFT OUTER JOIN
LEFT OUTER JOIN(早期写法) 和 LEFT JOIN 是等价的。
1 2 3 4 SELECT e.* ,d.* FROM emp e LEFT OUTER JOIN dept dON e.deptno = d.deptno;
RIGHT OUTER JOIN 1 2 3 4 SELECT e.* ,d.* FROM emp e RIGHT OUTER JOIN dept dON e.deptno = d.deptno;
执行右连接后，由于 40 号部门下没有任何员工，所以此时员工信息为 NULL。这个查询可以很好的复述上面提到的——JOIN 语句的关联条件必须用 ON 指定，不能用 WHERE 指定。你可以把 ON 改成 WHERE，你会发现无论如何都查不出 40 号部门这条数据，因为笛卡尔运算不会有 (NULL, 40) 这种情况。
FULL OUTER JOIN 1 2 3 SELECT e.* ,d.* FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;
LEFT SEMI JOIN LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。
JOIN 子句中右边的表只能在 ON 子句中设置过滤条件;
查询结果只包含左边表的数据，所以只能 SELECT 左表中的列。
1 2 3 4 5 6 7 8 SELECT emp.* FROM emp LEFT SEMI JOIN dept ON emp.deptno = dept.deptno AND dept.loc= "NEW YORK";SELECT emp.* FROM empWHERE emp.deptno IN (SELECT deptno FROM dept WHERE loc= "NEW YORK");
JOIN 笛卡尔积连接，这个连接日常的开发中可能很少遇到，且性能消耗比较大，基于这个原因，如果在严格模式下 (hive.mapred.mode = strict)，Hive 会阻止用户执行此操作。
1 SELECT * FROM emp JOIN dept;
JOIN优化 STREAMTABLE 在多表进行联结的时候，如果每个 ON 字句都使用到共同的列（如下面的 b.key），此时 Hive 会进行优化，将多表 JOIN 在同一个 map / reduce 作业上进行。同时假定查询的最后一个表（如下面的 c 表）是最大的一个表，在对每行记录进行 JOIN 操作时，它将尝试将其他的表缓存起来，然后扫描最后那个表进行计算。因此用户需要保证查询的表的大小从左到右是依次增加的。
1 `SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key) JOIN c ON (c.key = b.key)`
然后，用户并非需要总是把最大的表放在查询语句的最后面，Hive 提供了 /*+ STREAMTABLE() */ 标志，用于标识最大的表，示例如下：
1 2 3 4 SELECT e.* ,d.* FROM emp e JOIN dept dON e.deptno = d.deptnoWHERE job= 'CLERK' ;
MAPJOIN 如果所有表中只有一张表是小表，那么 Hive 把这张小表加载到内存中。这时候程序会在 map 阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在 map 就进行了 JOIN 操作，从而可以省略 reduce 过程，这样效率可以提升很多。Hive 中提供了 /*+ MAPJOIN() */ 来标记小表，示例如下：
1 2 3 4 SELECT e.* ,d.* FROM emp e JOIN dept dON e.deptno = d.deptnoWHERE job= 'CLERK' ;
SELECT的其他用途 查看当前数据库：
1 SELECT current_database()
本地模式 在上面演示的语句中，大多数都会触发 MapReduce, 少部分不会触发，比如 select * from emp limit 5 就不会触发 MR，此时 Hive 只是简单的读取数据文件中的内容，然后格式化后进行输出。在需要执行 MapReduce 的查询中，你会发现执行时间可能会很长，这时候你可以选择开启本地模式。
1 2 SET hive.exec.mode.local.auto= true ;
启用后，Hive 将分析查询中每个 map-reduce 作业的大小，如果满足以下条件，则可以在本地运行它：
作业的总输入大小低于：hive.exec.mode.local.auto.inputbytes.max（默认为 128MB）；
map-tasks 的总数小于：hive.exec.mode.local.auto.tasks.max（默认为 4）；
所需的 reduce 任务总数为 1 或 0。
因为我们测试的数据集很小，所以你再次去执行上面涉及 MR 操作的查询，你会发现速度会有显著的提升。
Hive 函数 介绍 Hive内建了不少函数，用于满足用户不同使用需求，提高SQL编写效率：
使用show functions查看当下可用的所有函数；
通过describe function extended funcname来查看函数的使用方式。
Hive的函数分为两大类：内置函数（Built-in Functions） 、用户定义函数UDF（User-Defined Functions）：
用户定义函数UDF分类标准
UDF（User-Defined-Function）普通函数，一进一出
UDAF（User-Defined Aggregation Function）聚合函数，多进一出
UDTF（User-Defined Table-Generating Functions）表生成函数，一进多出
UDF分类标准扩大化 UDF分类标准本来针对的是用户自己编写开发实现的函数。UDF分类标准可以扩大到Hive的所有函数中：包括内置函数和用户自定义函数。
因为不管是什么类型的函数，一定满足于输入输出的要求，那么从输入几行和输出几行上来划分没有任何问题。
千万不要被UD（User-Defined）这两个字母所迷惑，照成视野的狭隘。
比如Hive官方文档中，针对聚合函数的标准就是内置的UDAF类型。
自定义UDF 编程步骤：
（1）继承org.apache.hadoop.hive.ql.exec.UDF
（2）需要实现evaluate函数；evaluate函数支持重载
注意：
（1）UDF必须要有返回类型，可以返回null，但是返回类型不能为void；
（2）UDF中常用Text/LongWritable等类型，不推荐使用java类型；
代码：
1 2 3 4 5 6 7 8 9 10 11 12 <dependencies > <dependency > <groupId > org.apache.hive</groupId > <artifactId > hive-exec</artifactId > <version > 3.1.2</version > </dependency > <dependency > <groupId > org.apache.hadoop</groupId > <artifactId > hadoop-common</artifactId > <version > 3.1.4</version > </dependency > </dependencies >
开发java类继承UDF，并重载evaluate 方法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 public class MyUDF extends UDF { public String evaluate (String phoneNumStr) { String regex = "1[35789][0-9]{9}" ; boolean flag = phoneNumStr.matches(regex); if (!flag){ return null ; }else { String str1 = phoneNumStr.substring(0 ,3 ); String str2 = phoneNumStr.substring(7 ); return str1 + "****" +str2; } }
将我们的项目打包，并上传到hive的lib目录下，并重命名我们的jar包名称
1 2 cd /export/server/hive/lib mv day19_udf-1.0-SNAPSHOT.jar my_udf.jar
hive客户端添加jar
1 hive> add jar /export/server/hive/lib/my_udf.jar
设置函数与我们的自定义函数关联-临时函数
1 hive> create temporary function my_jiami as 'cn.itcast.udf.MyUDF';
使用自定义函数
1 hive>select my_jiami(phone_num) from test_user;
1 2 3 4 5 6 7 8 1. 把自定义函数的jar上传到hdfs中. hadoop fs -mkdir /hive_func hadoop fs -put my_udf.jar /hive_func 2. 创建永久函数 hive> create function my_jiami2 as 'cn.itcast.udf.MyUDF' using jar 'hdfs://node1:8020/hive_func/my_udf.jar'; 3. 验证 hive>select my_jiami2(phone_num) from test_user;
1 2 3 4 -- 删除临时函数 drop temporary function if exists encryptPhoneNumber; -- 删除永久函数,不会删除HDFS上的jar包 drop function if exists my_lower2;
自定义UDTF 自定义一个UDTF，实现将一个任意分隔符的字符串切割成独立的单词,例如:
1 2 3 4 5 6 7 8 源数据： "zookeeper,hadoop,hdfs,hive,MapReduce" 目标数据: zookeeper hadoop hdfs hive MapReduce
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 import org.apache.hadoop.hive.ql.exec.UDFArgumentException;import org.apache.hadoop.hive.ql.metadata.HiveException;import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import java.util.ArrayList;import java.util.List;import java.util.function.ObjDoubleConsumer;public class MyUDTF extends GenericUDTF { private final transient Object[] forwardListObj = new Object [1 ]; @Override public StructObjectInspector initialize (StructObjectInspector argOIs) throws UDFArgumentException { List<String> fieldNames = new ArrayList <>(); fieldNames.add("column_01" ); List<ObjectInspector> fieldOIs = new ArrayList <ObjectInspector>() ; fieldOIs.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector); return ObjectInspectorFactory.getStandardStructObjectInspector(fieldNames, fieldOIs); } @Override public void process (Object[] objects) throws HiveException { String args = objects[0 ].toString(); String splitKey = objects[1 ].toString(); String[] fields = args.split(splitKey); for (String field : fields) { forwardListObj[0 ] = field; forward(forwardListObj); } } @Override public void close () throws HiveException { } }
添加我们的jar包
1 2 3 4 5 6 7 8 9 10 11 12 将打包的jar包上传到node3主机/export/data/hive-2.1.0/lib目录,并重命名我们的jar包名称 cd /export/data/hive/lib mv original-day_10_hive_udtf-1.0-SNAPSHOT.jar my_udtf.jar hive的客户端添加我们的jar包,将jar包添加到hive的classpath下 hive> add jar /export/server/hive/lib/my_udtf.jar; 创建临时函数与开发后的udtf代码关联 hive>create temporary function my_udtf as 'cn.itcast.udf.MyUDTF'; 使用自定义udtf函数 hive>select my_udtf("zookeeper,hadoop,hdfs,hive,MapReduce",",") word;
字符串函数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 ------------String Functions 字符串函数------------ select length("zuoer"); -- 5 select reverse("zuoer"); -- reouz select concat("angela","baby"); --带分隔符字符串连接函数：concat_ws(separator, [string | array(string)]+) select concat_ ws('.', 'www', array('nbchen', 'com'));--字符串截取函数：substr(str, pos[, len]) 或者 substring(str, pos[, len]) select substr("angelababy",-2); --pos是从1开始的索引，如果为负数则倒着数 select substr("angelababy",2,2); -- 字符串转大写 select upper('abCDE'); select ucase('abCDE'); -- 字符串转小写 select lower('abCDE'); select lcase('abCDE'); -- 去除空格 select trim(' abc '); select ltrim(' abc '); select rtrim(' abc '); -- 正则替换 select regexp_replace('foobar','oo|ar',''); --分割字符串函数: split(str, regex) --split针对字符串数据进行切割 返回是数组array 可以通过数组的下标取内部的元素 注意下标从0开始的 select split('apache hive', ' '); select split('apache hive', ' ')[0]; select split('apache hive', ' ')[1]; -- URL路径解析 select parse_ url('https://www.baidu.com/info/s?word=bigdata&tn=25017023_2_ pg', 'HOST'); -- www.baidu.comselect parse_url('https://www.baidu.com/info/s?word=bigdata&tn=25017023_ 2_pg', 'PATH'); -- /info/s select parse_ url('https://www.baidu.com/info/s?word=bigdata&tn=25017023_2_ pg', 'QUERY'); -- word=bigdata&tn=25017023_2_ pgselect parse_url('https://www.baidu.com/info/s?word=bigdata&tn=25017023_ 2_pg', 'QUERY','word'); -- bigdata select parse_ url('https://www.baidu.com/info/s?word=bigdata&tn=25017023_2_ pg', 'QUERY','tn'); -- 25017023_2_ pg-- json数据解析 select get_json_ object('{"name": "zhangsan","age": 18, "preference": "music"}', '$.name'); select get_json_ object('{"name": "zhangsan","age": 18, "preference": "music"}', '$.age'); select get_json_ object('{"name": {"aaa":"bbb"}}', '$.name.aaa');
日期函数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 ----------- Date Functions 日期函数 ----------------- --获取当前日期: current_date select current_ date();--获取当前UNIX时间戳函数: unix_timestamp select unix_ timestamp();--日期转UNIX时间戳函数: unix_timestamp select unix_ timestamp("2011-12-07 13:01:03");--指定格式日期转UNIX时间戳函数: unix_timestamp select unix_ timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');--UNIX时间戳转日期函数: from_unixtime select from_ unixtime(1618238391);select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); select from_ unixtime(unix_timestamp('2023年4月13日 13点01分03秒','yyyy年MM月dd日 HH点mm分ss秒'), 'yyyy-MM-dd HH:mm:ss'); --日期比较函数: datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd' select datediff('2012-12-08','2012-05-09'); --日期增加函数: date_ addselect date_add('2012-02-28',10); --日期减少函数: date_ subselect date_sub('2012-01-1',10); # date_ format处理的是时间字符串，from_unixtime处理的是unix时间戳 select date_ format('2020-1-1 1:1:1', 'yyyy-MM-dd HH:mm:ss'); -- 2020-01-01 01:01:01-- to_date 返回日期时间字段中的日期部分 select to_ date('2011-12-08 10:03:01'); -- 2011-12-08-- year 返回日期中的年 select year('2011-12-08 10:03:01'); --2011 -- month 返回日期中的月份 select month('2011-12-08 10:03:01');-- 12 -- quarter 返回日期中的季度 select quarter('2011-12-08 10:03:01');-- 4 -- day 返回日期中的天 select day('2011-12-08 10:03:01');-- 8 -- weekofyear 返回日期在当前的周数 select weekofyear('2011-12-08 10:03:01'); --49
数学函数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 ----Mathematical Functions 数学函数------------- --取整函数: round 返回double类型的整数值部分 （遵循四舍五入） select round(3.1415926); --指定精度取整函数: round(double a, int d) 返回指定精度d的double类型 select round(3.1415926,4); --取随机数函数: rand 每次执行都不一样 返回一个0到1范围内的随机数 select rand(); --指定种子取随机数函数: rand(int seed) 得到一个稳定的随机数序列 select rand(3); -- 向下取整 select floor(3.9); -- 向上取整 select ceil(3.1); -- 取随机数 select floor((rand() * 100) + 1); -- 1到100之间的随机数 -- 绝对值 select abs(-12); -- 几次方运算 select pow(2,4);
条件函数 1 2 3 4 5 6 7 8 9 10 11 12 13 14 -----Conditional Functions 条件函数------------------ select * from student limit 3; --if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull) select if(1=2,100,200); select if(sex ='男','M','W') from student limit 3; --空值转换函数: nvl(T value, T default_value) select nvl("allen","itcast"); select nvl(null,"itcast"); --条件转换函数: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; select case sex when '男' then 'male' else 'female' end from student limit 3;
转换函数 1 2 3 4 5 6 7 8 9 cast(表达式 as 数据类型) 类似java中的强转 可以将"20190607"这样类型的时间数据转化成int类型数据。 select cast(12.35 as int); select cast('20190607' as int); select cast('2020-12-05' as date); # 转为字符串 select cast(12.35 as string);
行转列 1、行转列是指多行数据转换为一个列的字段。
1 2 3 4 5 6 7 8 9 10 11 concat(str1,str2,...) --字段或字符串拼接 concat_ws(sep, str1,str2) --以分隔符拼接每个字符串 collect_ set(col)/collect_list(col) --将某字段的值进行去重汇总，产生array类型字段 行转多列： select name, max(case when subject = '数学' then score else null end) math, max(case when subject='英语' then score else null end) english, max(case when subject='语文' then score else null end) chinese from t1 group by name;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 字段: deptno ename 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER # 建表 create table emp(deptno int , ename string ) row format delimited fields terminated by '\t' ; # 插入数据 load data local inpath "/export/data/hivedatas/emp.txt" into table emp; # 转换 select deptno,concat_ws("|",collect_set(ename)) as ems from emp group by deptno;
行转列，COLLECT_SET**(col )：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array**类型字段。
列转行（表生成函数）
explode(col)：将hive一列中复杂的array或者map结构拆分成多行。
explode(ARRAY) 数组的每个元素生成一行
explode(MAP) map中每个key-value对，生成一行，key为一列，value为一列
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 # 数据 10 CLARK| KING| MILLER20 SMITH| JONES| SCOTT| ADAMS| FORD30 ALLEN| WARD| MARTIN| BLAKE| TURNER| JAMES# 建表 create table emp2(deptno int , names array < string> ) row format delimited fields terminated by '\t' collection items terminated by '|' ; # 插入数据 load data local inpath '/export/data/hivedatas/emp2.txt' into table emp2; # 查询数据 select * from emp;
1 select explode(names) as name from emp;
用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
1 select deptno,name from emp2 lateral view explode(names) tmp_tb as name;
reflect函数可以支持在sql中调用java中的静态方法
使用java.lang.Math当中的Max求两列中最大值
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 create table test_reflect(col1 int ,col2 int ) row format delimited fields terminated by ',' ;1 ,2 4 ,3 6 ,4 7 ,5 5 ,6 load data local inpath '/export/data/hivedatas/test_reflect.txt' into table test_udf; select reflect("java.lang.Math","max",col1,col2) from test_udf;
开窗函数 ROW_NUMBER,RANK,DENSE_RANK => 既有明细，又可以分组
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 cookie1,2018 -04 -10 ,1 cookie1,2018 -04 -11 ,5 cookie1,2018 -04 -12 ,7 cookie1,2018 -04 -13 ,3 cookie1,2018 -04 -14 ,2 cookie1,2018 -04 -15 ,4 cookie1,2018 -04 -16 ,4 cookie2,2018 -04 -10 ,2 cookie2,2018 -04 -11 ,3 cookie2,2018 -04 -12 ,5 cookie2,2018 -04 -13 ,6 cookie2,2018 -04 -14 ,3 cookie2,2018 -04 -15 ,9 cookie2,2018 -04 -16 ,7 CREATE TABLE test_window_func1(cookieid string, createtime string, pv INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ; load data local inpath '/export/data/hivedatas/test1.txt' into table test_window_func1;
1 2 3 4 5 6 7 ROW_NUMBER () 从1 开始，按照顺序，生成分组内记录的序列SELECT cookieid, createtime, pv, ROW_NUMBER () OVER (PARTITION BY cookieid ORDER BY pv desc ) AS rn FROM itcast_t1;
1 2 3 4 5 6 7 8 9 10 11 RANK () 生成数据项在分组中的排名，排名相等会在名次中留下空位DENSE_RANK () 生成数据项在分组中的排名，排名相等会在名次中不会留下空位SELECT cookieid, createtime, pv, RANK () OVER (PARTITION BY cookieid ORDER BY pv desc ) AS rn1,DENSE_RANK () OVER (PARTITION BY cookieid ORDER BY pv desc ) AS rn2,ROW_NUMBER () OVER (PARTITION BY cookieid ORDER BY pv DESC ) AS rn3 FROM itcast_t1 WHERE cookieid = 'cookie1' ;
SUM,AVG,MIN,MAX
从那几行加到那几行，挺好用的
可以做到累加，历史平均，历史最差，历史最好等等效果
1 2 3 4 5 6 7 8 9 10 11 12 13 create table test_window_func2(cookieid string, createtime string, pv int ) row format delimited fields terminated by ',' ; load data local inpath '/export/data/hivedatas/test1.txt' into table test_window_func2; SET hive.exec.mode.local.auto= true ;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 select cookieid,createtime,pv,sum (pv) over (partition by cookieid order by createtime) as pv1 from test_window_func2;select cookieid,createtime,pv,sum (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as pv2from test_window_func2;select cookieid,createtime,pv,sum (pv) over (partition by cookieid) as pv3from test_window_func2; select cookieid,createtime,pv,sum (pv) over (partition by cookieid order by createtime rows between 3 preceding and current row ) as pv4from test_window_func2;select cookieid,createtime,pv,sum (pv) over (partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5from itcast_t2;select cookieid,createtime,pv,sum (pv) over (partition by cookieid order by createtime rows between current row and unbounded following) as pv6from itcast_t2; 13 号= 10 号+ 11 号+ 12 号+ 13 号， 14 号= 11 号+ 12 号+ 13 号+ 14 号 14 号= 14 号+ 15 号+ 16 号= 2 + 4 + 4 = 10
AVG,MIN,MAX和SUM用法一样
1 2 3 4 5 6 7 8 9 10 11 select cookieid,createtime,pv,avg (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as pv2from itcast_t2;select cookieid,createtime,pv,max (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as pv2from itcast_t2;select cookieid,createtime,pv,min (pv) over (partition by cookieid order by createtime rows between unbounded preceding and current row ) as pv2from itcast_t2;
比如你想将上一行(lag)/下一行(lead)和当前行放到一行就有用了
1 2 3 4 5 6 7 8 9 10 11 lag()/lead() lead(field, num, defaultvalue) field: 需要查找的字段 num: 往后查找的num行的数据 defaultvalue: 没有符合条件的默认值 over() 表示lag()与lead()操作的数据都在over()的范围内，里面可以使用以下子句 partition by 语句(用于分组) order by 语句()用于排序) 如：over(partition by a order by b) 表示以a字段进行分组，再以b字段进行排序，对数据进行查询
有一个用户购物信息表，里面有name(用户姓名)，orderdate(购买日期)，cost (消费额)三个字段，表数据如下：
需求：查询顾客上次的购买时间？
思路：先根据name字段进行分组，根据orderdate字段进行组内排序，排完序后使用lag函数取出当前orderdate字段前1行同字段的值，作为新字段值，如果没有设置默认值。
sql如下：
Hive 调优 本地模式
一般用关于测试，如果数据量查过了设置阈值，则不会走本地模式
大多数的Hadoop Job是需要Hadoop提供的完整的可扩展性来处理大数据集的。不过，有时Hive的输入数据量是非常小的。在这种情况下，为查询触发执行任务时消耗可能会比实际job的执行时间要多的多。对于大多数这种情况，Hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。
用户可以通过设置hive.exec.mode.local.auto的值为true，来让Hive在适当的时候自动启动这个优化。
1 2 3 4 5 6 set hive.stats.column.autogather=false; set hive.exec.mode.local.auto=true; --开启本地mr --设置local mr的最大输入数据量，当输入数据量小于这个值时采用local mr的方式，默认为134217728，即128M set hive.exec.mode.local.auto.inputbytes.max=51234560; --设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4 set hive.exec.mode.local.auto.input.files.max=10;
案例实操：
1 2 3 4 5 6 7 8 9 --1）开启本地模式，并执行查询语句 hive (default)> set hive.exec.mode.local.auto=true; hive (default)> select * from score cluster by sid; 18 rows selected (1.568 seconds) --2）关闭本地模式，并执行查询语句 hive (default)> set hive.exec.mode.local.auto=false; hive (default)> select * from score cluster by sid; 18 rows selected (11.865 seconds)
空key处理 有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，不需要进行无意义的join,我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：
环境准备：
1 2 3 4 5 6 7 8 create table ori(id bigint, time_val bigint, uid string, keyword string, url_ rank int, click_num int, click_ url string) row format delimited fields terminated by '\t'; create table nullidtable(id bigint, time_val bigint, uid string, keyword string, url_ rank int, click_num int, click_ url string) row format delimited fields terminated by '\t'; create table jointable(id bigint, time_valbigint, uid string, keyword string, url_ rank int, click_num int, click_ url string) row format delimited fields terminated by '\t'; load data local inpath '/export/data/hivedatas/hive_big_ table/*' into table ori; load data local inpath '/export/data/hivedatas/hive_have_null_id/* ' into table nullidtable;
1 2 3 4 5 6 7 8 9 10 11 不过滤： INSERT OVERWRITE TABLE jointable SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id; 结果： No rows affected (152.135 seconds) 过滤： INSERT OVERWRITE TABLE jointable SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id; 结果： No rows affected (141.585 seconds)
有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，业务上无法把null掉，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。防止数据倾斜。例如：
1 2 3 4 5 6 7 8 set hive.exec.reducers.bytes.per.reducer=32123456; set mapreduce.job.reduces=7; INSERT OVERWRITE TABLE jointable SELECT a.* FROM nullidtable a LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id; No rows affected (41.668 seconds) 52.477
结果：这样的后果就是所有为null值的id全部都变成了相同的字符串，及其容易造成数据的倾斜（所有的key相同，相同key的数据会到同一个reduce当中去）
为了解决这种情况，我们可以通过hive的rand函数，随记的给每一个为空的id赋上一个随机值，这样就不会造成数据倾斜
1 2 3 4 5 6 7 8 9 10 11 12 set hive.exec.reducers.bytes.per.reducer=32123456; set mapreduce.job.reduces=7; INSERT OVERWRITE TABLE jointable SELECT a.* FROM nullidtable a LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id; No rows affected (42.594 seconds) 或者用if if(id is null,floor((rand() * 1000000)+1),id)
hive sql优化 Map端join
因为是拉取小表到内存中，所以必须要有小表参与
思考: 在执行JOIN的SQL的时候, SQL会被翻译为MR. 请思考, 翻译后MR默认是如何进行Join操作的呢?
1 2 3 思考 : 这种reduce join操作, 存在那些弊端呢? 1- 可能会存在数据倾斜的问题 2- 所有的数据的合并都是在reduce端完成的, 而reduce数量相对比较少的, 导致reduce的压力比较大
思考: 如何提升Join的效率呢? Map Join
默认情况下，Map阶段同一Key数据分发给一个reduce，当一个key数据过大时就倾斜了。
并不是所有的聚合操作都需要在Reduce端完成，很多聚合操作都可以先在Map端进行部分聚合，最后在Reduce端得出最终结果。
1 2 3 4 5 6 7 开启Map端聚合参数设置 --（1）是否在Map端进行聚合，默认为True set hive.map.aggr = true; --（2）在Map端进行聚合操作的条目数目（阈值） set hive.groupby.mapaggr.checkinterval = 100000; --（3）有数据倾斜的时候进行负载均衡（默认是false） set hive.groupby.skewindata = true;
当选项设定为 true，生成的查询计划会有两个MR Job。第一个MR Job中，Map的输出结果会随机分布到Reduce中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key有可能被分发到不同的Reduce中，从而达到负载均衡的目的；第二个MR Job再根据预处理的数据结果按照Group By Key分布到Reduce中（这个过程可以保证相同的Group By Key被分布到同一个Reduce中），最后完成最终的聚合操作。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Map Join: 将核心Join操作, 都集中在Map端进行处理, 而Map的数量是由读取文件的切片来决定的, 会保证每一个mapTask的读取数量基本是差不多, 可以大幅降低数据倾斜发生, Map的数量会随着读取文件数量增大而变多,以此来提升Join的执行效率 弊端 : 1) 比较耗费内存 2) 要求整个Join中, 必须得有小表 使用条件的 : set hive.auto.convert.join=true; -- 开启Map Join的支持, 默认就是True -- 小表数据量的最大阈值: 默认 20971520(20M) set hive.auto.convert.join.noconditionaltask.size=512000000 默认值为 20971520 如果不满足条件, HIVE会自动使用 reduce Join 操作 -- 结论是： 1、Map端的Join当前的Hive已经默认支持 2、你什么都不需要做也可以使用Map端Join，但是这个多大的表算小表可以自己定义，默认是20M set hive.auto.convert.join.noconditionaltask.size=512000000
小表 JOIN 大表 在hive中先后顺序无所谓, 但是在HIVE建议小表在前, 大表在后):
1 2 3 4 5 6 使用条件的 : set hive.auto.convert.join=true; -- 开启Map Join的支持, 默认就是True -- 小表数据量的最大阈值: 默认 20971520(20M) set hive.auto.convert.join.noconditionaltask.size=512000000 默认值为 20971520 支持所有表类型(内部表, 外部表, 分桶表, 分区表)
中型表 JOIN 大表 比如大于50M但是有没有大于1G这样的表
方案一: 如果中型表能对数据提前过滤, 尽量提前过滤掉, 过滤后, 又可能就已经满足了Map Join条件(并不一定可用)
方案二: Bucket Map Join
1 2 3 4 5 6 使用条件 : 1- Join两个表必须是分桶表 2- 开启Bucket Map Join 支持: set hive.optimize.bucketmapjoin = true; 3- 一个表的分桶数量是另一个表的分桶数量的整倍数 4- 分桶列 必须 是 Join条件的列 5- 必须建立在Map Join场景中
1 2 3 结论是： 1、Bucket Map Join :的表必须都是分桶表，分桶数量是整数倍 2、开启Bucket Map Join ：set hive.optimize.bucketmapjoin = true;
大表 JOIN 大表
解决方案: SMB(Sort Merge Bucket) Map Join
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 使用条件: 1 - 两个表必须都是分桶表 2 - 开启SMB Map Join 支持 set hive.optimize.bucketmapjoin = true ; set hive.auto.convert.sortmerge.join= true ; set hive.auto.convert.sortmerge.join.noconditionaltask= true ; set hive.optimize.bucketmapjoin.sortedmerge = true ; set hive.optimize.bucketmapjoin.sortedmerge = true 3 - 两个表的分桶的数量是一致的 4 - 分桶列 必须是 Join 条件列 , 同时必须保证按照分桶字段列进行排序操作 set hive.enforce.sorting= true ; 5 - 应用在 Bucket Map Join 场景中 建表的时候: create table test_smb_2( mid string, age_id string ) CLUSTERED BY (mid) SORTED BY (mid) INTO 500 BUCKETS;
Count(distinct)
禁用distinct，有需要的用开窗函数，distinct可能执行10分钟，开窗只要2分钟（底层有分组）
数据量小的时候无所谓，数据量大的情况下，由于COUNT DISTINCT操作需要用一个Reduce Task来完成，这一个Reduce需要处理的数据量太大，就会导致整个Job很难完成，一般COUNT DISTINCT使用先GROUP BY再COUNT的方式替换
1 2 3 4 5 方式1： SELECT count(DISTINCT id) FROM bigtable; 方式2 SELECT count(id) FROM (SELECT id FROM bigtable GROUP BY id) a;
虽然会多用一个Job来完成，但在数据量大的情况下，这个绝对是值得的。
一般可以理解为小于一个block(128M)为数据量小，小表
避免笛卡尔积 尽量避免笛卡尔积，即避免join的时候不加on条件，或者无效的on条件，Hive只能使用1个reducer来完成笛卡尔积。
如何解决数据倾斜问题 何为数据倾斜:
1 在hive中, 执行一条SQL语句, 最终会翻译为MR, MR中mapTask和reduceTask都可能存在多个, 数据倾斜主要指的是整个MR中reduce阶段有多个reduce , 每个reduce拿到的数据的条数并不均衡, 导致某一个或者某几个拿到了比其他的reduce更多的数据, 到底处理数据压力集中在某几个reduce上, 形成数据倾斜问题
在hive中执行什么SQL操作的时候可能会出现数据倾斜
1 2 1) 执行多表join的操作 - 多表 2) 执行group by的操作 - 单表
怎么发现数据倾斜？
思考: 如何才能知道发生了数据倾斜呢?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 倾斜出现后, 出现的问题, 程序迟迟无法结束, 或者说翻译MR中reduceTask有多个, 大部分的reduceTask都执行完成了, 只有其中一个或者某几个没有执行完成, 此时认为发生了数据倾斜 ``` * 方式一: 通过 yarn查看(运行过程中)或jobhistory查看(程序结束后)     ```properties 看的就是这个reduce的执行时间, 如果这个时间比其他reduce的时候都大的多, 认为出现了数据倾斜问题 ``` * 还可以通过HUE查看  ```shell -- 演示数据倾斜 set mapreduce.job.reduces=10; select city_id,count(*) from yp_dws.dws_sale_daycount group by city_id; ``` #### join数据倾斜的处理  出现倾斜的根本原因: ```properties 在reduce中,某一个, 或者某几个的分组k2对应value的数据比较多, 从而引起数据倾斜问题 ``` * 解决方案一:```properties 可以通过 map join, bucket map join 以及 SMB Map join 解决 注意: 通过map join来解决数据倾斜, 但是map join使用是存在条件的, 如果无法满足这些条件, 无法使用map join ``` * 解决方案二:```properties 思路: 将那些产生倾斜的k2和对应value数据, 从当前这个MR移植出去, 单独找一个MR来处理即可, 处理后, 和之前MR的汇总结果即可 关键问题: 如何找出那些存在倾斜的k2数据 运行期处理方案: 思路: 在执行MR的时候, 会动态统计每一个k2的值出现的重复的数量, 当这个重复的数量达到一定阈值后, 认为当前这个k2的数据存在数据倾斜, 自动将其剔除, 交由给一个单独的MR来处理即可, 两个MR处理完成后, 将结果, 基于union all 合并在一起即可 实操 : set hive.optimize.skewjoin=true; set hive.skewjoin.key=100000; -- 此参考在实际生产环境中, 需要进行调整在合理的值 适用于: 并不清楚那个key容易产生倾斜, 此时可以交由系统来动态检测 编译期处理方案: 思路: 在创建这个表的时候,我们就可以预知到后续插入到这个表中, 那些key的值会产生倾斜, 在建表的时候, 将其提前配置设置好即可, 在后续运行的时候, 程序会自动将设置的k2的值数据单独找一个MR来进行单独的处理操作, 处理后, 再和原有MR进行union all的合并操作 实操: set hive.optimize.skewjoin.compiletime=true; 建表 CREATE TABLE list_bucket_single (key STRING, value STRING) -- 倾斜的字段和需要拆分的key值 SKEWED BY (key) ON (1,5,6) -- 为倾斜值创建子目录单独存放 [STORED AS DIRECTORIES]; 在实际生产环境中, 应该使用那种方式呢? 两种方式都会使用的 一般来说, 会将两个都开启, 编译期的明确在编译期将其设置好, 编译期不清楚, 通过 运行期动态捕获即可 ``` * union all优化方案```properties 说明: 不管是运行期, 还是编译期的join倾斜解决, 最终都会运行多个MR , 最终将多个MR的结果, 通过union all进行汇总, union all也是单独需要一个MR来运行的 解决方案: 让每一个MR在运行完成后, 直接将结果输出到目的地即可, 默认是输出到临时文件目录下, 通过union all合并到最终目的地 set hive.optimize.union.remove=true; ``` #### group by 数据倾斜 * 为什么在执行group by的时候 可能会出现数据倾斜```properties 假设目前有这么一个表: sid sname cid s01 张三 c01 s02 李四 c02 s03 王五 c01 s04 赵六 c03 s05 田七 c02 s06 周八 c01 s07 李九 c01 s08 老夯 c03 需求: 请计算每个班级有多少个人 select cid, count(1) from stu group by cid; 翻译后MR是如何处理SQL的呢? map 阶段: mapTask 1 k2 v2 c01 {s01 张三 c01} c02 {s02 李四 c02} c01 {s03 王五 c01} c03 {s04 赵六 c03} mapTask 2 k2 v2 c02 {s05 田七 c02} c01 {s06 周八 c01} c01 {s07 李九 c01} c03 {s08 老夯 c03} reduce 阶段 reduceTask 1 : 接收 c01 和 c03 接收到的数据: k2 v2 c01 {s01 张三 c01} c01 {s03 王五 c01} c03 {s04 赵六 c03} c01 {s06 周八 c01} c01 {s07 李九 c01} c03 {s08 老夯 c03} 分组后: c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01},{s07 李九 c01}] c03 [{s04 赵六 c03},{s08 老夯 c03}] 结果: c01 4 c03 2 reduceTask 2 : 接收 c02 接收到的数据: k2 v2 c02 {s02 李四 c02} c02 {s05 田七 c02} 分组后: c02 [{s02 李四 c02} , {s05 田七 c02}] 结果: c02 2 以上整个计算过程中, 发现 其中一个reduce接收到的数据量比另一个reduce接收的数据量要多得多, 认为出现了数据倾斜问题 ``` 思考: 如何解决group by的数据倾斜呢? ```properties 解决方案一: 基于MR的combiner(规约)减轻数据倾斜问题 (小combiner) 假设目前有这么一个表: sid sname cid s01 张三 c01 s02 李四 c02 s03 王五 c01 s04 赵六 c03 s05 田七 c02 s06 周八 c01 s07 李九 c01 s08 老夯 c03 需求: 请计算每个班级有多少个人 select cid, count(1) from stu group by cid; 翻译后MR是如何处理SQL的呢? map 阶段: mapTask 1 k2 v2 c01 {s01 张三 c01} c02 {s02 李四 c02} c01 {s03 王五 c01} c03 {s04 赵六 c03} 规约操作: 跟reduce的操作是一致的 输出: k2 v2 c01 2 c02 1 c03 1 mapTask 2 k2 v2 c02 {s05 田七 c02} c01 {s06 周八 c01} c01 {s07 李九 c01} c03 {s08 老夯 c03} 规约操作: 跟reduce的操作是一致的 输出: k2 v2 c02 1 c01 2 c03 1 reduce 阶段 reduceTask 1 : 接收 c01 和 c03 接收到数据: k2 v2 c01 2 c03 1 c01 2 c03 1 分组处理: c01 [2,2] c03 [1,1] 结果: c01 4 c03 2 reduceTask 2 : 接收 c02 接收到数据: k2 v2 c02 1 c02 1 分组后: c02 [1,1] 结果: c02 2 通过规约来解决数据倾斜, 发现处理后, 两个reduce中从原来相差3倍, 变更为相差2倍 , 减轻了数据倾斜问题 如何配置呢? 只需要在hive中开启combiner使用配置即可: set hive.map.aggr=true; ``` 解决方案二: 基于负载均衡的方案解决 (大的combiner) ```properties 解决方案一: 基于MR的combiner(规约)减轻数据倾斜问题 (小combiner) 假设目前有这么一个表: sid sname cid s01 张三 c01 s02 李四 c02 s03 王五 c01 s04 赵六 c03 s05 田七 c02 s06 周八 c01 s07 李九 c01 s08 老夯 c03 需求: 请计算每个班级有多少个人 select cid, count(1) from stu group by cid; 翻译后MR是如何处理SQL的呢? 第一个MR的操作: map 阶段: mapTask 1 k2 v2 c01 {s01 张三 c01} c02 {s02 李四 c02} c01 {s03 王五 c01} c03 {s04 赵六 c03} mapTask 2 k2 v2 c02 {s05 田七 c02} c01 {s06 周八 c01} c01 {s07 李九 c01} c03 {s08 老夯 c03} mapTask执行完成后, 在进行分发数据到达reduce, 默认将相同的k2的数据发往同一个reduce, 此时采用方案是随机分发, 保证每一个reduce拿到相等的数据条数即可 reduce阶段: reduceTask 1: 接收到数据: k2 v2 c01 {s01 张三 c01} c02 {s02 李四 c02} c01 {s03 王五 c01} c01 {s06 周八 c01} 分组操作: c01 [{s01 张三 c01},{s03 王五 c01},{s06 周八 c01}] c02 [{s02 李四 c02}] 结果: c01 3 c02 1 reduceTask 2: 接收到数据: k2 v2 c03 {s04 赵六 c03} c01 {s07 李九 c01} c02 {s05 田七 c02} c03 {s08 老夯 c03} 分组操作: c03 [{s04 赵六 c03},{s08 老夯 c03}] c01 [{s07 李九 c01}] c02 [{s05 田七 c02}] 结果: c03 2 c01 1 c02 1 第二个MR进行处理: 严格按照相同k2发往同一个reduce map阶段: mapTask 1: k2 v2 c01 3 c02 1 c03 2 c01 1 c02 1 reduce阶段: reduceTask 1: 接收 c01 和 c03 接收到数据: k2 v2 c01 3 c01 1 c03 2 结果: c01 4 c03 2 reduceTask 2: 接收 c02 接收到的数据: k2 v2 c02 1 c02 1 结果: c02 2 通过负载均衡来解决数据倾斜, 发现处理后, 两个reduce中从原来相差3倍, 变更为相差1.5倍 , 减轻了数据倾斜问题 如何操作: 只需要在hive中开启负载均衡使用配置即可: set hive.groupby.skewindata=true; 注意: 使用第二种负载均衡的解决group by的数据倾斜, 一定要注意, sql语句中不能出现多次 distinct操作, 否则hive直接报错 错误信息: Error in semantic analysis: DISTINCT on different columns notsupported with skew in data ```  ### HIVE其他优化 hive的并行优化的内容 ```properties 1) 并行编译: hive.driver.parallel.compilation : 是否开启并行编译的操作 hive.driver.parallel.compilation.global.limit: 设置最大同时编译几个会话的SQL 如果设置为 0 或者 负值 , 此时无限制 设置方式: 建议在CM上设置 hive.driver.parallel.compilation true hive.driver.parallel.compilation.global.limit 15 说明: 默认情况下, 如果hive有多个会话窗口, 而且多个窗口都在提交SQL, 此时hive默认只能对一个会话的SQL进行编译, 其他会话的SQL需要等待, 这样效率相对比较低 2) 并行执行: 一个SQL语句在提交给hive之后, SQL有可能会被翻译为多个阶段, 在这个过程中, 有可能出现多个阶段互不干扰的情况,这个时候, 可以安排多个阶段并行执行的操作, 以此来提升效率 如何设置呢? 推荐在 会话中设置 set hive.exec.parallel=true; 是否开启并行执行 set hive.exec.parallel.thread.number=16; 最大运行并行多少个阶段 注意: 开启了此配置, 并不代表着所有SQL一定会并行的执行, 因为是否并行执行还取决于SQL中多个阶段之间是否有依赖, 只有在没有依赖的时候, 才可能并行执行 例子: union all select * from A order by c union all select * from B where xxx; hive常规的做法: 先执行第一个阶段 : select * from A order by c 得出临时结果 接着执行第二个阶段: select * from B where xxx; 得出临时结果 最后, 将两个语句的结果 合并在一起 ``` * 小文件合并的操作: ```properties 当HDFS中小文件过多后, 会导致整个HDFS的存储容量的下降, 因为每一个小文件都会有一个元数据, 而元数据是存储在namenode的内存中, 当内存一旦满了, 即使datanode上还有空间, 那么也是无法存储了 MR角度: 当小文件过多后, 就会导致读取数据时候, 产生大量文件的切片, 从而导致有多个mapTask的执行, 而每个mapTask处理数据量比较的少, 导致资源的浪费, 导致执行时间较长, 因为一旦没有资源, 所有map只能串行化执行 hive的解决方案: 在执行完成后, 输出的结果的文件尽量的少一些, 避免出现小文件过多问题, 可以通过设置让执行的SQL输出的文件竟可能少一些 hive.merge.mapfiles: 是否开启map端的小文件合并操作, 指的 MR只有map没有reduce的操作时候 hive.merge.mapredfiles: 是否开启reduce端的小文件合并操作, 指的普通MR hive.merge.size.per.task: 设置文件的大小 合并后的文件最大值 比如 128M hive.merge.smallfiles.avgsize: 当输出文件的平均大小小于此设置值时，启动一个独立的map-reduce任务进行文件merge，默认值为16M。 说明: 以上配置均可直接在CM上进行配置即可 思考点: 在执行SQL的时候, 什么样SQL 可能会出现多个reduce情况? group by join 例如: 比如一个MR输出 10个文件 1M 10M 50M 2M 3M 30M 10M 5M 6M 20M 此时请问, 是否会进行文件合并工作呢? 发现得出平均值是小于16M 的 所以会执行小文件合并操作 思考: 是将所有的文件合并为一个文件, 还是怎么做呢? 128M 8M ``` * 矢量化查询(批量化): ```properties 配置: set hive.vectorized.execution.enabled=true; 默认值为true 说明: 一旦开启了矢量化查询的工作,hive执行引擎在读取数据时候, 就会采用批量化读取工作, 一次性读取1024条数据进行统一的处理工作, 从而减少读取的次数(减少磁盘IO次数), 从而提升效率 注意: 要求表的存储格式必须为 ORC ``` * 读取零拷贝: ```properties 一句话: 在读取数据的时候, 能少读一点 尽量 少读一些(没用的数据尽量不读) 配置: set hive.exec.orc.zerocopy=true; 默认中为false 例子: 看如下这个SQL: 假设A表中 a b c三个字段 select a,b from A where a =xxx; ``` * 关联优化器: 如果多个MR之间的操作的数据都是一样的, 同样shuffle操作也是一样的, 此时可以共享shuffle > 一个SQL最终翻译成MR , 请问 有没有可能翻译为多个MR的情况呢? 非常有可能 > > 每一个MR的中间有可能都会执行shuffle的操作, 而shuffle其实比较消耗资源操作(内部存在多次 io操作) ```properties 配置项: set hive.optimize.correlation=true; ``` 总结优化点: ```properties set hive.exec.parallel=true; --是否开启并行执行 set hive.exec.parallel.thread.number=16; --最大运行并行多少个阶段 set hive.vectorized.execution.enabled=true; -- 矢量化查询 set hive.exec.orc.zerocopy=true; -- 读取零拷贝 set hive.optimize.skewjoin.compiletime=true; -- 开启编译期优化 set hive.optimize.skewjoin=true; -- 开启运行期数据倾斜的处理 默认值为false set hive.skewjoin.key=100000; set hive.optimize.union.remove=true; -- union优化 set hive.groupby.skewindata=true; -- 开启负载均衡优化 set hive.optimize.correlation=true; --开启关联优化器 ``` ### **并行执行** Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么job可能就越快完成。 通过设置参数hive.exec.parallel值为true，就可以开启并发执行。不过，在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。 ```markdown set hive.exec.parallel=true; --打开任务并行执行 set hive.exec.parallel.thread.number=16; --同一个sql允许最大并行度，默认为8。 ``` 当然，得是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来。 ### **严格模式** Hive提供了一个严格模式，可以防止用户执行那些可能意向不到的不好的影响的查询。 通过设置属性hive.mapred.mode值为默认是非严格模式nonstrict 。开启严格模式需要修改hive.mapred.mode值为strict，开启严格模式可以禁止3种类型的查询。 ```markdown set hive.mapred.mode = strict; --开启严格模式 set hive.mapred.mode = nostrict; --开启非严格模式 ``` 配置文件修改:hive-site.xml ```xml <property> <name>hive.mapred.mode</name> <value>strict</value> </property> ``` 1）**对于分区表，在where语句中必须含有分区字段作为过滤条件来限制范围，否则不允许执行** 。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。 2）**对于使用了order by语句的查询，要求必须使用limit语句** 。因为order by为了执行排序过程会将所有的结果数据分发到同一个Reducer中进行处理，强制要求用户增加这个LIMIT语句可以防止Reducer额外执行很长一段时间。 3）**限制笛卡尔积的查询** 。对关系型数据库非常了解的用户可能期望在执行JOIN查询的时候不使用ON语句而是使用where语句，这样关系数据库的执行优化器就可以高效地将WHERE语句转化成那个ON语句。**不幸的是，Hive并不会执行这种优化** ，因此，如果表足够大，那么这个查询就会出现不可控的情况。 ### 存储方式orc和压缩方式snappy 大数据场景下存储格式压缩格式尤为关键，可以提升计算速度，减少存储空间，降低网络io，磁盘io，所以要选择合适的压缩格式和存储格式,存储方式和压缩方式之前已经讲过，这里不再描述。 > 选择合适的存储格式和压缩方式有利于数据分析 orc存储+snappy压缩 ## Hive 练习 ### 陌陌聊天数据分析 #### 背景介绍 陌陌作为聊天平台每天都会有大量的用户在线，会出现大量的聊天数据，通过对聊天数据的统计分析，可以更好的对用户构建精准的用户画像，为用户提供更好的服务以及实现高ROI的平台运营推广，给公司的发展决策提供精确的数据支撑。 #### 目标需求 基于Hadoop和Hive实现聊天数据统计分析，构建聊天数据分析报表 - 统计今日总消息量- 统计今日每小时消息量、发送和接收用户数- 统计今日各地区发送消息数据量- 统计今日发送消息和接收消息的用户数- 统计今日发送消息最多的Top10用户- 统计今日接收消息最多的Top10用户- 统计发送人的手机型号分布情况- 统计发送人的设备操作系统分布情况#### 数据内容 数据大小：两个文件共14万条数据 列分隔符：制表符 `\t` 数据字典及样例数据  #### 基于Hive数仓实现需求开发 ##### 建库建表、加载数据 ```markdown --如果数据库已存在就删除 drop database if exists db_msg cascade ; --创建数据库 create database db_msg ; --切换数据库 use db_msg ; --列举数据库 show databases ; --如果表已存在就删除 drop table if exists db_msg.tb_msg_source ; --建表 create table db_msg.tb_msg_source( msg_time string comment "消息发送时间" , sender_name string comment "发送人昵称" , sender_account string comment "发送人账号" , sender_sex string comment "发送人性别" , sender_ip string comment "发送人ip地址" , sender_os string comment "发送人操作系统" , sender_phonetype string comment "发送人手机型号" , sender_network string comment "发送人网络类型" , sender_gps string comment "发送人的GPS定位" , receiver_name string comment "接收人昵称" , receiver_ip string comment "接收人IP" , receiver_account string comment "接收人账号" , receiver_os string comment "接收人操作系统" , receiver_phonetype string comment "接收人手机型号" , receiver_network string comment "接收人网络类型" , receiver_gps string comment "接收人的GPS定位" , receiver_sex string comment "接收人性别" , msg_type string comment "消息类型" , distance string comment "双方距离" , message string comment "消息内容" ) --指定分隔符为制表符 row format delimited fields terminated by '\t' ; # 加载数据 hdfs dfs -mkdir -p /momo/data/ hdfs dfs -put /opt/data/hive3_data/data1.tsv /momo/data/ hdfs dfs -put /opt/data/hive3_data/data2.tsv /momo/data/ # 加载到Hive表中 load data inpath '/momo/data/data1.tsv' into table db_msg.tb_msg_source; load data inpath '/momo/data/data2.tsv' into table db_msg.tb_msg_source; # 查询校验数据 select msg_time,sender_name,sender_ip,sender_phonetype,receiver_name,receiver_network from tb_msg_source limit 10; ``` ##### ETL数据清洗 经过观察分析，数据存在以下问题 ```markdown -- 问题1：当前数据中，有一些数据的字段为空，不是合法数据 => 过滤 where select msg_time, sender_name, sender_gps from db_msg.tb_msg_source where length(sender_gps) = 0 limit 10; -- 问题2：需求中，需要统计每天、每个小时的消息量，但是数据中没有天和小时字段，只有整体时间字段，不好处理 => 截取 substr select msg_time from db_msg.tb_msg_source limit 10 -- 问题3：需求中，需要对经度和维度构建地区的可视化地图，但是数据中GPS经纬度为一个字段，不好处理 => 分隔 split select sender_gps from db_msg.tb_msg_source limit 10; ``` - ctas方法: 需要将清洗后的数据存到一张新的表，方便后续分析使用```markdown -- Create table …… as select …… # ETL实现 --如果表已存在就删除 drop table if exists db_msg.tb_msg_etl; --将Select语句的结果保存到新表中 create table db_msg.tb_msg_etl as select *, substr(msg_time,0,10) as dayinfo, substr(msg_time,12,2) as hourinfo, --获取天和小时 split(sender_gps,",")[0] as sender_lng, split(sender_gps,",")[1] as sender_lat --提取经度纬度 from db_msg.tb_msg_source --过滤字段为空的数据 where length(sender_gps) > 0 ; ``` 查看清洗后的结果集： ```markdown select msg_time,dayinfo,hourinfo,sender_gps,sender_lng,sender_lat from db_msg.tb_msg_etl limit 10; ``` ##### 需求指标统计 1. 实际开发中，拿到业务需求指标，如何下手？ 1. 正确解读业务需求，避免歧义 2. 确定待查询的数据表-->from 表 3. 找出分析的维度-->group by 分组的字段 4. 找出计算的指标-->聚合的字段 5. 其他细节点（过滤、排序等）```markdown # 查询类SQL编写思路举例 表：t_user(id，name，age，sex，city) 需求：统计每个城市男女人数与男女平均年龄 分组字段：每个城市、男女 也就意味着同一个城市，性别相同的人应该分到同一组，因此这里需要根据两个字段进行分组 聚合字段：人数、平均年龄 count(id)就是统计每个分组中的条数-->人数 avg(age)就是统计每个分组中年龄的平均值-->平均年龄 ``` 2. SQL层面如何编写查询语句？- 统计今日总消息量```markdown --保存结果表 create table if not exists tb_rs_total_msg_cnt comment "今日消息总量" as select dayinfo, count(*) as total_msg_cnt from db_msg.tb_msg_etl group by dayinfo; ```  - 统计今日每小时消息量、发送和接收用户数```markdown --保存结果表 create table if not exists tb_rs_hour_msg_cnt comment "每小时消息量趋势" as select dayinfo, hourinfo, count(*) as total_msg_cnt, count(distinct sender_account) as sender_usr_cnt, count(distinct receiver_account) as receiver_usr_cnt from db_msg.tb_msg_etl group by dayinfo,hourinfo; ```  - 统计今日各地区发送消息数据量```markdown --保存结果表 create table if not exists tb_rs_loc_cnt comment "今日各地区发送消息总量" as select dayinfo, sender_gps, cast(sender_lng as double) as longitude, -- 转换为double，方便后续计算 cast(sender_lat as double) as latitude, count(*) as total_msg_cnt from db_msg.tb_msg_etl group by dayinfo,sender_gps,sender_lng,sender_lat; ```  - 统计今日发送消息和接收消息的用户数```markdown --保存结果表 create table if not exists tb_rs_usr_cnt comment "今日发送消息人数、接受消息人数" as select dayinfo, count(distinct sender_account) as sender_usr_cnt, count(distinct receiver_account) as receiver_usr_cnt from db_msg.tb_msg_etl group by dayinfo; ```  - 统计今日发送消息最多的Top10用户```markdown --保存结果表 create table if not exists tb_rs_susr_top10 comment "发送消息条数最多的Top10用户" as select dayinfo, sender_name as username, count(*) as sender_msg_cnt from db_msg.tb_msg_etl group by dayinfo,sender_name order by sender_msg_cnt desc limit 10; ```  - 统计今日接收消息最多的Top10用户```markdown --保存结果表 create table if not exists tb_rs_rusr_top10 comment "接受消息条数最多的Top10用户" as select dayinfo, receiver_name as username, count(*) as receiver_msg_cnt from db_msg.tb_msg_etl group by dayinfo,receiver_name order by receiver_msg_cnt desc limit 10; ```  - 统计发送人的手机型号分布情况```markdown --保存结果表 create table if not exists tb_rs_sender_phone comment "发送人的手机型号分布" as select dayinfo, sender_phonetype, count(distinct sender_account) as cnt from tb_msg_etl group by dayinfo,sender_phonetype; ```  - 统计发送人的设备操作系统分布情况```markdown --保存结果表 create table if not exists tb_rs_sender_os comment "发送人的OS分布" as select dayinfo, sender_os, count(distinct sender_account) as cnt from tb_msg_etl group by dayinfo,sender_os; ```  ### FineBI实现可视化报表 #### 安装 - 官网：https://www.finebi.com/只需要设置一下JVM内存大小，其他的下一步即可。  关注公众号，获取激活码 设置管理员账号，直接登录 #### 配置数据源 我们的数据源在hive中，需要和fineBI对接。 FineBI与Hive集成的官方文档：https://help.fanruan.com/finebi/doc-view-301.html ##### 驱动配置 - 问题：如果使用FineBI连接Hive，读取Hive的数据表，需要在FineBI中添加Hive的驱动jar包- 解决：将Hive的驱动jar包放入FineBI的`webapps\webroot\WEB-INF\lib` 目录下 ##### 插件安装 - 问题：我们自己放的Hive驱动包会与FineBI自带的驱动包产生冲突，导致FineBI无法识别我们自己的驱动包- 解决：安装FineBI官方提供的驱动包隔离插件 安装插件后重新fineBI 新建连接找到hive数据源  #### 制作可视化报表 1.添加包和业务组  2.添加数据库，点击选择表  3.数据有了，BI样式其实很丰富，需要纠结的可能是要选择什么样的图表展示数据。  > FineBI就是上去玩玩用用的那种，重点还是数据处理，没有数据，这些图表就是无根之水。 ## Hive 面试题 - 1.你用的hive版本- 2.hive和hadoop的关系- 3.hive中内部表和外部表的区别- 4.hive汇总分区表和分桶表的区别- 5.hive表加载数据的方式有哪些- 6.你用过的哪些hive的开窗函数，什么时候使用开窗函数``` 分组聚合时，还需要保留数据的明细时，就可以用开窗 ``` - 7.笔试：行转列，列转行代码，collect_set和collect_ list区别- 8.hive自定义UDF的步骤- 9.项目中hive数据文件存储格式和压缩算法是什么- 10.hive的调优方式hive ``` 表A left join 表B on a.test1 = b.test2 left join 表C on b.test3 = c.test4 1.一个key的记录数如果超过1亿，就会有作业中断（内存溢出），或者长时间运行（数据倾斜） 对关联表的test1、2、3、4做group by，观察key 的数据量。大的就会有问题 2.test1只关联出50%test2 则test2在关联test3，就会数据倾斜有问题。 解决办法是b和c先处理为一个temp临时表。再和a关联。 ``` ## hive常用工具表 ### hive日历维度表  ```markdown create table if not exists dim_date ( date_id string comment '日期(yyyymmdd)' ,datestr string comment '日期(yyyy-mm-dd)' ,date_name string comment '日期名称中文' ,weekid int comment '周(0-6,周日~周六)' ,week_cn_name string comment '周_名称_中文' ,week_en_name string comment '周_名称_英文' ,week_en_nm string comment '周_名称_英文缩写' ,yearmonthid string comment '月份id(yyyymm)' ,yearmonthstr string comment '月份(yyyy-mm)' ,monthid int comment '月份id（1-12）' ,monthstr string comment '月份' ,month_cn_name string comment '月份名称_中文' ,month_en_name string comment '月份名称_英文' ,month_en_nm string comment '月份名称_简写_英文' ,quarterid int comment '季度id（1-4）' ,quarterstr string comment '季度名称' ,quarter_cn_name string comment '季度名称_中文' ,quarter_en_name string comment '季度名称_英文' ,quarter_cn_nm string comment '季度名称_简写中文' ,quarter_en_nm string comment '季度名称_简写英文' ,yearid int comment '年份id' ,year_cn_name string comment '年份名称_中文' ,year_en_name string comment '年份名称_英文' ,month_start_date string comment '当月1号(yyyy-mm-dd)' ,month_end_date string comment '当月最后日期(yyyy-mm-dd)' ,month_timespan int comment '月跨天数' ,week_of_year int comment '当年第几周' ,workday_flag string comment '是否工作日(周一至周五Y，否则:N)' ,weekend_flag string comment '是否周末(周六和周日Y,否则:N)' ,week_start_date string comment '当周起始日期' ,week_end_date string comment '当周结束日期' )comment '日期维度表' stored as parquet; insert overwrite table dim_date select date_id ,datestr ,concat(yearid,'年',monthid,'月',substr(datestr,9,2),'日') as date_name ,weekid ,case weekid when 0 then '星期日' when 1 then '星期一' when 2 then '星期二' when 3 then '星期三' when 4 then '星期四' when 5 then '星期五' when 6 then '星期六' end as week_cn_name ,case weekid when 0 then 'Sunday' when 1 then 'Monday' when 2 then 'Tuesday' when 3 then 'Wednesday' when 4 then 'Thurday' when 5 then 'Friday' when 6 then 'Saturday' end as week_en_name ,case weekid when 0 then 'Sun' when 1 then 'Mon' when 2 then 'Tues' when 3 then 'Wed' when 4 then 'Thur' when 5 then 'Fri' when 6 then 'Sat' end as week_en_nm ,substr(date_id,1,6) as yearmonthid ,substr(datestr,1,7) as yearmonthstr ,monthid ,concat(yearid,'年',monthid,'月') as monthstr ,concat(monthid,'月') as month_cn_name ,case monthid when 1 then 'January' when 2 then 'February' when 3 then 'March' when 4 then 'April' when 5 then 'May' when 6 then 'June' when 7 then 'July' when 8 then 'August' when 9 then 'September' when 10 then 'October' when 11 then 'November' when 12 then 'December' end as month_en_name ,case monthid when 1 then 'Jan' when 2 then 'Feb' when 3 then 'Mar' when 4 then 'Apr' when 5 then 'May' when 6 then 'Jun' when 7 then 'Jul' when 8 then 'Aug' when 9 then 'Sept' when 10 then 'Oct' when 11 then 'Nov' when 12 then 'Dec' end as month_en_nm ,quarterid ,concat(yearid,quarterid) as quarterstr ,concat(yearid,'年第',quarterid,'季度') as quarter_cn_name ,concat(yearid,'Q',quarterid) as quarter_en_name ,case quarterid when 1 then '第一季度' when 2 then '第二季度' when 3 then '第三季度' when 4 then '第四季度' end as quarter_cn_nm ,concat('Q',quarterid) as quarter_en_nm ,yearid ,concat(yearid,'年') as year_cn_name ,yearid as year_en_name ,month_start_date ,month_end_date ,datediff(month_end_date,month_start_date) + 1 as month_timespan ,week_of_year ,case when weekid in (1,2,3,4,5) then 'Y' else 'N' end as workday_flag ,case when weekid in (0,6) then 'Y' else 'N' end as weekend_flag --,date_sub(datestr,weekid-1) as week_start_date --周起始日期 ,case when weekid = 0 then date_sub(datestr,6) else date_sub(datestr,weekid-1) end as week_start_date --周起始日期 --,date_add(datestr,6-weekid+1) as week_end_date --周结束日期 ,case when weekid = 0 then datestr else date_add(datestr,6-weekid+1) end as week_end_date --周结束日期 from ( select from_unixtime(unix_timestamp(datestr,'yyyy-MM-dd'),'yyyyMMdd') as date_id ,datestr as datestr ,pmod(datediff(datestr, '2012-01-01'), 7) as weekid ,concat(substr(datestr,1,4),substr(datestr,6,2)) as yearmonthid ,substr(datestr,1,7) as yearmonthstr ,cast(substr(datestr,6,2) as int) as monthid ,case when cast(substr(datestr,6,2) as int) <= 3 then 1 when cast(substr(datestr,6,2) as int) <= 6 then 2 when cast(substr(datestr,6,2) as int) <= 9 then 3 when cast(substr(datestr,6,2) as int) <= 12 then 4 end as quarterid ,substr(datestr,1,4) as yearid ,date_sub(datestr,dayofmonth(datestr)-1) as month_start_date --当月第一天 ,last_day(date_sub(datestr,dayofmonth(datestr)-1)) month_end_date --当月最后一天 ,weekofyear(datestr) as week_of_year from ( select date_add('1900-01-01',t0.pos) as datestr from ( select posexplode( split( repeat('o', datediff(from_unixtime(unix_timestamp('20501231','yyyymmdd'),'yyyy-mm-dd'), '1900-01-01')), 'o' ) ) ) t0 ) t ) A ; ```
微信
支付宝
