Hive

简述CloudCanal 最近对于全周期数据流动进行了初步摸索，买通了Hive 指标端的实时同步，为实时数仓的构建提供了反对，这篇文章简要做下分享。 基于长期表的增量合并形式基于 HDFS 文件写入形式长期表对立 Schema工作级的长期表基于长期表的增量合并形式Hive 指标端写入形式和 Doris类似，须要在指标表上额定增加一个 __op（0：UPSERT，1：DELETE）字段作为标记位，理论写入时会先将源端的变更先写入长期表，最终合并到理论表中。 CloudCanal 的设计外围在于，每个同步表对应两张长期表，通过交替合并的形式，确保在一张长期表进行合并时，另一张可能接管新变更，从而晋升同步效率和并发性。 Hive 提供了两种合并形式：INSERT OVERWRITE（所有版本均反对），MERGE INTO（Hive 2.2.0 之后反对且须要是 ACID 表） -- INSERT OVERWRITE 语法INSERT OVERWRITE [LOCAL] DIRECTORY directory1 [ROW FORMAT row_format] [STORED AS file_format]SELECT ... FROM ...-- MERGE INTO 语法MERGE INTO <target table > AS T USING < source expression / table > AS SON <boolean expression1> WHEN MATCHED [AND <boolean expression2>] THENUPDATE SET <set clause list> WHEN MATCHED [AND <boolean expression3>] THENDELETE WHEN NOT MATCHED [AND <boolean expression4>] THEN INSERT VALUES<value list>工作级的长期表在大数据场景下，多表汇聚的状况非常广泛，CloudCanal 在构建长期表时，利用源端的订阅 Schema Table 信息，创立不同的长期表。 ...

omnidata-hive-connector介绍 omnidata-hive-connector是一种将大数据组件Hive的算子下推到存储节点上的服务，从而实现近数据计算，缩小网络带宽，晋升Hive的查问性能。目前反对Hive on Tez。omnidata-hive-connector已在openEuler社区开源。 OmniData架构 OmniData是算子下推的总称。OmniData次要由以下四个局部组成： 1.  OmniData Client属于开源的局部，为不同的引擎提供相应的插件。对于Hive引擎，omnidata-hive-connector作为OmniData Client，并通过HAF注解和编译插件能力，实现主动下推工作到存储节点的OmniData Server中。 2.  Haf Host为lib库，部署在计算节点，对外提供工作卸载的能力，把工作下推到Haf Offload。 3. Haf Offload为lib库，部署在存储节点提供工作执行的能力，用来执行OmniData Server的作业。 4. OmniData Server提供算子下推的执行能力，接管Haf Host下推下来的工作。 omnidata-hive-connector性能 1.  实现将Hive的Filter、Aggregation和Limit算子下推到存储节点进行计算，提前将数据过滤，升高网络传输数据量，晋升性能。 2.  实现插件化的形式注册给Hive，以松耦合的形式实现算子的下推，可通过个性开关激活或使能。 3.  实现HDFS/Ceph文件系统的下推。 4.  实现Orc/Parquet文件存储格局的下推。 5.  实现Hive内置UDF（cast、instr、length、lower、replace、substr和upper）的下推。 omnidata-hive-connector利用场景 在大数据组件Hive典型硬件配置的存算拆散场景下，别离运行规范测试用例TPC-H和omnidata-hive-connector。 能够看出运行omnidata-hive-connector后，10条SQL性能均匀晋升40%以上。 omnidata-hive-connector次要优化办法 1.  基于数据选择率，做到动静的下推。 omnidata-hive-connector通过Hive统计信息计算数据选择率（选择率越低，过滤的数据量越多），通过参数设置下推的选择率阈值，omnidata-hive-connector就可能动静地将选择率低于阈值的算子推到存储节点上执行，实现存储节点在本地读取数据进行计算，再将计算过滤之后的数据集通过网络返回到计算节点，晋升网络传输效率，优化性能。除了数据选择率，还会依据算子是否反对，残余资源是否足够等条件进行判断。 2.  Filter的局部下推。 当一个Filter中同时存在反对下推和不反对下推的算子时，omnidata-hive-connector对不反对的算子结构成新的Filter走原生Hive计算流程，对反对下推的算子从新计算数据选择率并依据新的选择率判断是否下推。 3.  存算协同，正当利用计算资源。 omnidata-hive-connector将算子下推到存储节点执行计算，能够无效地升高计算节点的CPU使用率，并且能将存储节点的CPU应用起来，晋升总体计算效率。以TPC-H的SQL为例，优化前计算节点CPU均匀使用率60%以上，优化后，计算节点CPU均匀使用率在40%左右。 4.  提前过滤数据，缩小网络传输。   数据的提前过滤是omnidata-hive-connector性能收益的次要起源，在存储节点过滤数据，缩小网络传输，缩小计算节点解决的数据量。 以TPC-H的SQL为例，SQL中含有多个Filter，优化前的算子须要跨网络从远端存储节点读取近60亿行的数据；优化后只须要传输过滤后的无效数据4000万行。执行效率晋升60%以上。 omnidata-hive-connector后续布局 本我的项目已开源在openEuler社区，omnidata-hive-connector个性会踊跃地反对新性能，后续布局如下： 1. 反对timestamp和decimal数据格式。 2. 反对BloomFilter算子的下推。 3. 反对用户自定义函数的下推。 代码地址： https://gitee.com/openeuler/omnidata-hive-connector 欢送感兴趣的敌人们参加到openEuler Bigdata SIG，探讨大数据畛域技术。

先说一些废话总结一下Hive面试宝典中的要点，不便读者疾速过一遍Hive面试所须要的知识点。本文请搭配 Hive面试宝典 来食用更美味哟 ┗( ▔, ▔ )┛ 不便本人系统性回顾，依据*的数量来标记重要性+ 简略理解 ++ 相熟把握 +++ 须要精通Hive的介绍（+） Hive和Hadoop的关系Hive的特点Hive的毛病Hive常见的利用场景Hive和mysql的区别Hive的架构（+）Hive的数据（++） Hive的数据模型Hive的底层如何存储Null值Hive中元数据metadata和元数据商店metastore)`的作用Hive有哪些保留元数据metadata的形式Hive元数据存储形式中，本地模式和近程模式的区别Hive的数据类型Hive的隐式类型转换规定Hive数据存储所应用的文件格式Hive中应用的压缩算法什么是数据可宰割对于压缩模式阐明Hive的装置与应用（+） 如何在Hive中集成HBase如何通过 HiveSQL 来间接读写 HBaseHive的分区和分桶（++） 什么是Hive分区Hive分区的长处Hive分区的毛病什么是Hive分桶对于Hive索引的阐明Hive分桶的长处Hive分桶的毛病Hive中动态分区和动静分区的区别Hive动静分区的参数设定Hive的外部表和内部表（+） 什么是Hive的外部表和内部表Hive外部表和内部表的区别是什么生产环境中为什么倡议应用内部表Hive SQL（+++） Hive中的SQL如何转化成MapReduce工作的什么状况下Hive不走MapReduce工作Hive中如何查问A表中B表不存在的数据Hive中有哪些连贯查问以及如何应用Hive中左连贯和内连贯的区别Hive中左连贯的底层原理Hive查问时候 ON 和 WHERE 有什么区别Hive 函数（+++） 如何应用UDF/UDAF/UDTF为什么应用UDF/UDAF/UDTF你写过什么样的UDF/UDAF/UDTHive自定义函数实现了什么函数什么接口Hive中如何去重Hive中排序函数的应用形式及区别请阐明以下罕用函数 split / coalesce / collect list / collect set 的性能请形容工作中罕用的Hive罕用函数及应用场景Hive 运维（+） 如何监控一个提交后的Hive状态Hive 优化（+++） 请阐明你在工作中如何进行Hive优化HiveSQL优化 ———— Hive单表查问优化HiveSQL优化 ———— Hive多表查问优化HiveSQL优化 ———— Hive其余查问优化Hive数据歪斜 ———— 单表携带了 Group By 字段的查问Hive数据歪斜 ———— 两表或多表的 join 关联时，其中一个表较小，然而 key 集中Hive数据歪斜 ———— 两表或多表的 join 关联时，有 Null值 或 无意义值Hive数据歪斜 ———— 两表或多表的 join 关联时，数据类型不对立Hive数据歪斜 ———— 独自解决歪斜keyHiveJob优化 ———— HiveMap优化计划HiveJob优化 ———— HiveReduce优化计划Hive整体优化计划我是 fx67ll.com，如果您发现本文有什么谬误，欢送在评论区探讨斧正，感谢您的浏览！ 如果您喜爱这篇文章，欢送拜访我的 本文github仓库地址，为我点一颗Star，Thanks~ :) 转发请注明参考文章地址，非常感谢！！！ ...

先说一些废话总结一下Hive面试宝典，不便读者疾速过一遍Hive面试所须要的知识点 Hive的介绍Hive和Hadoop的关系Hive利用hdfs存储数据，利用MapReduce查问数据Hive的数据存储在hdfs上，简略的说Hive就是hdfs的简略一种映射，比方：Hive的一张表映射hdfs上的一个文件，Hive的一个数据库就映射为hdfs上的文件夹Hive是一个计算框架，他是MapReduce的一种封装，实际上他的底层还是MR，Hive就是用人们相熟的sql对数据进行剖析的Hive执行程序是运行在Yarn上的Hive的特点Hive能够自在的扩大集群的规模，个别状况下不须要重启服务（世界上最大的Hadoop集群在Yahoo!，2009年的规模在4000台节点左右）Hive反对用户自定义函数，用户能够依据本人的需要来实现本人的函数（可能会引申自定义函数）良好的容错性，节点呈现问题SQL仍可实现执行（可能会拓展数据歪斜相干问题，或者间接问你你工作中有没有遇到这样的问题）Hive的毛病Hive的HQL表达能力无限。迭代式算法无奈表白；数据挖掘方面不善于Hive的效率比拟低。Hive主动生成的MapReduce作业，通常状况下不够智能化；Hive调优比拟艰难，粒度较粗Hive执行提早 Hive 在查问数据的时候，因为没有索引，须要扫描整个表，因而提早较高另外一个导致 Hive 执行提早高的因素是 MapReduce框架，因为MapReduce 自身具备较高的提早，因而在利用MapReduce 执行Hive查问时，也会有较高的提早绝对的，数据库的执行提早较低。当然，这个低是有条件的，即数据规模较小，当数据规模大到超过数据库的解决能力的时候，Hive的并行计算显然能体现出劣势Hive常见的利用场景日志剖析：大部分互联网公司应用Hive进行日志剖析，包含百度、淘宝等 统计网站一个时间段内的pv、uv多维度数据分析海量结构化数据离线剖析Hive和mysql的区别Hive采纳了类SQL的查询语言HQL（hive query language），除了HQL之外，其余无任何类似的中央，Hive是为了数据仓库设计的存储地位：Hive在Hadoop上；mysql将数据存储在设施或本地零碎中数据更新：Hive不反对数据的改写和增加，是在加载的时候就曾经确定好了；数据库能够CRUD索引：Hive无索引，每次扫描所有数据，底层是MR，并行计算，实用于大数据量；mysql有索引，适宜在线查问数据执行：Hive底层是MarReduce；mysql底层是执行引擎可扩展性：Hive：大数据量，缓缓扩去吧；mysql:绝对就很少了Hive的架构# Hive架构繁难示意Meta Store -> Client (CLI/JDBC/WebGUI + Driver/驱动 + SQL Parser/解析器 + Physical Plan/编译器 + QueryOptimizer/优化器 + Execution/执行器) ->MapReduce ->HDFS用户接口：Hive 对外提供了三种服务模式，即 Hive 命令行模式（CLI），Hive 的 Web 模式（WUI），Hive 的近程服务（Client） 其中最罕用的是 CLI shell 命令行，CLI 启动的时候，会同时启动一个Hive正本WUI 是通过浏览器拜访 Hive，默认端口是9999Client 是Hive的客户端，，在启动 Client模式 的时候，须要指出 Hive Server 所在节点，并且在该节点启动 Hive ServerJDBC/ODBC用 JAVA 实现，与传统数据库 JDBC 相似元数据存储：通常是存储在关系数据库如 mysql , derby中 Hive中的元数据包含表的名字，表的列和分区及其属性，表的属性（是否为内部表等），表的数据所在目录等解释器、编译器、优化器、执行器 解释器、编译器、优化器实现 HQL 查问语句从词法剖析、语法分析、编译、优化以及查问打算的生成生成的查问打算存储在 HDFS 中，并在随后有 MapReduce 调用执行（留神！！！蕴含的查问，比方select from tbl不会生成MapRedcue工作）===============================================================解析器（parser）：将查问字符串转化为解析树表达式===============================================================编译器（physical plan）：分为语义分析器（semantic analyzer）和 逻辑策略生成器（logical plan generator）语义分析器（semantic analyzer）：将解析树表达式转换为基于块（block-based）的外部查问表达式逻辑策略生成器（logical plan generator）：将外部查问表达式转换为逻辑策略，这些策略由逻辑操作树组成===============================================================优化器（optimizer）：通过逻辑策略结构多路径并以不同形式重写Hive的数据Hive的数据模型Hive中所有的数据都存储在hdfs中，没有专门的数据存储格局（可反对TextFile，SequenceFile，ParquetFile，RCFILE等）只须要在创立表的时候通知Hive数据中的列分隔符和行分隔符，Hive就能够解析数据Hive中蕴含以下数据模型：DB、Table、External Table、Partition、Bucket ...

数新网络官网已全新上线，欢送点击拜访www.datacyber.com 数新网络_让每个人享受数据的价值 01 数据迁徙场景不同平台之间的迁徙，比方apache hadoop到cdh数据迁徙；集群数据个体迁徙，因为业务倒退迅速，以后集群可能有比拟大的业务压力，须要把数据整体迁徙到更大的集群；数据的准实时同步，为了保证数据的双备份可用，须要定期的同步数据，保障两个集群的数据周期内根本完全一致。这样做的益处是如果某一天A集群宕机了，能够把线上应用的集群间接切到B集群而不会造成影响。02 hadoop集群间数据命令hadoop distcphdfs://master1:8020/foo/barhdfs://master2:8020/bar/foo 03 什么是distcp？是如何实现的？Distcp是hadoop外部自带的一个程序，用于hdfs之间的数据拷贝。Distcp是作为一个 MapReduce作业来实现的，该复制作业是通过集群中并行运行的 map来实现。每个文件通过一个map进行复制，并且 distcp试图为每一个 map 调配大抵相等的数据来执行，即把文件划分为大抵相等的块。默认状况下，每个集群节点最多调配20个map工作。 04 distcp罕用的参数 编辑 编辑 05 Distcp劣势个性 带宽限流：Distcp是反对带宽限流的，使用者能够通过命令参数bandwidth来为程序进行限流，原理相似于HDFS中数据Balance程序的限流。增量数据同步：对于增量数据同步的需要，在distcp中也失去了很好的实现。通过update，append和diff 2个参数能很好地解决。官网的参数应用阐明：Update: Update target, copying only missing files or directories;Append: Reuse existing data in target files and append new data to them if possible;Diff: Use snapshot diff report to identify the difference between source and target.高效的性能：执行的分布式个性高效的MR组件。06 hive数据迁徙 1. hive数据export到hdfs export table tablename to '/tmp/export/tablename'; 2. 数据复制 hadoop distcp -D ipc.client.fallback-to-simple-auth-allowed=true-D dfs.checksum.combine.mode=COMPOSITE_CRChdfs://master1:8020/tmp/export/tablenamehdfs://master2:8020/tmp/export/tablename 3. 新集群创立表并且导入数据 在源hive show create table tbName显示建表语句，用语句在指标hive建表，而后导入数据：LODA DATA [LOCAL] INPATH ‘filepath’; 4. 验证数据是否雷同 select count(*) from 'tablename' 本期分享就到这里，欢送关注咱们理解更多精彩内容~

1、Hive事务外围流程1.1、时序图 1.2、外围流程图 2、Hive事务HDFS文件存储分析2.1、表构造前提是设置 set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;应用DbTxnManager Db事务管理器 CREATE TABLE employee (id int, name string, salary int)STORED AS ORC TBLPROPERTIES ('transactional' = 'true');2.2、插入数据解析INSERT INTO employee VALUES(1, 'Jerry', 5000),(2, 'Tom', 8000),(3, 'Kate', 6000);INSERT语句会在一个事务中运行。它会创立名为delta的目录，寄存事务的信息和表的数据 /user/hive/warehouse/employee/delta_0000001_0000001_0000/user/hive/warehouse/employee/delta_0000001_0000001_0000/_orc_acid_version/user/hive/warehouse/employee/delta_0000001_0000001_0000/bucket_00000目录名称的格局为delta_minWID_maxWID_stmtID，即 delta前缀、写事务的 ID 范畴、以及语句ID。具体来说： 所有INSERT语句都会创立delta目录。UPDATE语句也会创立delta目录，但会先创立一个delete目录，即先删除、后插入。delete 目录的前缀是 delete_deltaHive会为所有的事务生成一个全局惟一的ID，包含读操作和写操作。针对写事务（INSERT、DELETE等），Hive还会创立一个写事务ID（Write ID），该ID在表范畴内惟一。写事务ID会编码到delta和delete目录的名称中语句ID(Statement ID）则是当一个事务中有多条写入语句时应用的，用作惟一标识。(MERGE语句可能会生成多个Statement ID)再看文件内容，_orc_acid_version 的内容是 2，即以后 ACID 版本号是 2。它和版本 1 的次要区别是 UPDATE 语句采纳了 split-update 个性，即上文提到的先删除、后插入。这个个性可能使 ACID 表反对条件下推等性能，具体能够查看 HIVE-14035。bucket_00000 文件则是写入的数据内容。因为这张表没有分区和分桶，所以只有这一个文件。事务表都以 ORC 格局存储的，咱们能够应用 hive自带工具 来查看文件的内容： hive --orcfiledump -d /user/hive/warehouse/employee/delta_0000001_0000001_0000{"operation":0,"originalTransaction":1,"bucket":536870912,"rowId":0,"currentTransaction":1,"row":{"id":1,"name":"Jerry","salary":5000}}{"operation":0,"originalTransaction":1,"bucket":536870912,"rowId":1,"currentTransaction":1,"row":{"id":2,"name":"Tom","salary":8000}}{"operation":0,"originalTransaction":1,"bucket":536870912,"rowId":2,"currentTransaction":1,"row":{"id":3,"name":"Kate","salary":6000}}输入内容被格式化为了一行行的 JSON 字符串，咱们能够看到具体数据是在 row 这个键中的，其它键则是 Hive 用来实现事务个性所应用的，具体含意为： ...

1、UDF的实现办法Hive 提供了两个实现 UDF 的形式： 1.1、继承UDF类长处： 实现简略反对Hive的根本类型、数组和Map反对函数重载毛病： 逻辑较为简单，只适宜用于实现简略的函数这种形式编码少，代码逻辑清晰，能够疾速实现简略的UDF1.2、继承GenericUDF类长处： 反对任意长度、任意类型的参数能够依据参数个数和类型实现不同的逻辑能够实现初始化和敞开资源的逻辑(initialize、close)毛病： 实现比继承UDF要简单一些与继承 UDF 相比，GenericUDF 更加灵便，能够实现更为简单的函数2、继承UDF类实现形式pom.xml <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.journey.udf</groupId> <artifactId>hive-udf</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>hive-udf</name> <url>http://maven.apache.org</url> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <maven.compiler.encoding>UTF-8</maven.compiler.encoding> <java.version>1.8</java.version> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> </properties> <dependencies> <dependency> <groupId>org.apache.hive</groupId> <artifactId>hive-exec</artifactId> <version>3.1.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.69</version> </dependency> </dependencies> <build> <plugins> <!-- assembly打包插件 --> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-assembly-plugin</artifactId> <version>3.0.0</version> <executions> <execution> <id>make-assembly</id> <phase>package</phase> <goals> <goal>single</goal> </goals> </execution> </executions> <configuration> <archive> <manifest> </manifest> </archive> <descriptorRefs> <descriptorRef>jar-with-dependencies</descriptorRef> </descriptorRefs> </configuration> </plugin> </plugins> </build></project>package com.journey.udf;import org.apache.hadoop.hive.ql.exec.UDF;/** * UDF曾经废除，倡议用GenericUDF */public class MyUDF extends UDF { public String evaluate(String value) { return "journey_" + value; }}3、继承GenericUDF类实现形式package com.journey.udf;import com.alibaba.fastjson.JSON;import com.alibaba.fastjson.JSONObject;import org.apache.commons.codec.digest.DigestUtils;import org.apache.hadoop.hive.ql.exec.Description;import org.apache.hadoop.hive.ql.exec.UDFArgumentException;import org.apache.hadoop.hive.ql.metadata.HiveException;import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.PrimitiveObjectInspector;import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;import org.apache.hadoop.hive.serde2.objectinspector.primitive.StringObjectInspector;import org.apache.hadoop.io.Text;@Description(name = "my_hash", value = "returns hash of the given value")public class MyHashUDF extends GenericUDF { private static final String MD2_HASH_TYPE = "md2"; private static final String MD5_HASH_TYPE = "md5"; public static final String UDF_NAME = "my_hash"; private PrimitiveObjectInspector primitiveObjectInspector; @Override public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException { PrimitiveObjectInspector primitiveObjectInspector = ((PrimitiveObjectInspector) arguments[1]); this.primitiveObjectInspector = ((PrimitiveObjectInspector) arguments[1]); // 简略来说就是标识evaluate的类型，应用ObjectInspector来进行封装，这里其实就是输出类型是什么返回类型就是什么 // 也能够通过 PrimitiveObjectInspectorFactory.xxx，来指定类型的返回 PrimitiveObjectInspector columnType = ((PrimitiveObjectInspector) arguments[0]); ObjectInspector ret = PrimitiveObjectInspectorFactory.getPrimitiveWritableObjectInspector(columnType.getPrimitiveCategory()); return ret; } @Override public Object evaluate(DeferredObject[] arguments) throws HiveException { Text result = new Text(); String propertiesJsonString = (String) primitiveObjectInspector.getPrimitiveJavaObject(arguments[1].get()); JSONObject propertiesJson = JSON.parseObject(propertiesJsonString); String hashType = propertiesJson.getString("hashType"); DeferredObject col = arguments[0]; StringObjectInspector stringColumn = (StringObjectInspector) primitiveObjectInspector; String colVal = stringColumn.getPrimitiveJavaObject(col.get()); switch (hashType) { case MD2_HASH_TYPE: result.set(DigestUtils.md2Hex(colVal)); break; case MD5_HASH_TYPE: result.set(DigestUtils.md5Hex(colVal)); break; default: throw new UnsupportedOperationException("hash type must be one of [" + MD2_HASH_TYPE + ",}" + MD5_HASH_TYPE + "]"); } return result; } @Override public String getDisplayString(String[] children) { return getStandardDisplayString(UDF_NAME, children); }}4、三种加载形式4.1、SPI机制间接加载为零碎函数 ...

疑难 :hive not null，是不是感觉not null是基于column的，所以就感觉not null是column中的一个字段，道歉，还真不是，口说无凭，上代码看一下 表格信息: // 表private String tableName; // required// dbprivate String dbName; // required// 表的所属，基于用于、者角色或者组private String owner; // required// 表创立事件private int createTime; // required// 最初拜访工夫private int lastAccessTime; // required// 保留字段，默认0private int retention; // required// 存储相干，location,inputformat,outputformat,序列化反序列化器private StorageDescriptor sd; // required// 这个就是列信息了private List<FieldSchema> partitionKeys; // required// table properties信息private Map<String,String> parameters; // required// 视图相干private String viewOriginalText; // requiredprivate String viewExpandedText; // required// 表类型，治理表、内部表、虚构视图和物化视图private String tableType; // required// 表的权限相干private PrincipalPrivilegeSet privileges; // optional// session级别的长期表，create temporary table txx(id int);private boolean temporary; // optional// rewrite简略来说其实就是一个SQL能不能要应用物化视图来优化查问private boolean rewriteEnabled; // optional// 物化视图相干，物化视图关联的表等等private CreationMetadata creationMetadata; // optional// 默认是hiveprivate String catName; // optional// 用户、角色或者所属组private PrincipalType ownerType; // optionalFieldSchema列信息: ...

1、景象insert into test123 values(2,'aa1'); 发现每条insert语句都会执行两次 alter_table_with_environmentContext2023-05-23T09:45:02,453 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: ql.Driver (:()) - Starting task [Stage-2:DEPENDENCY_COLLECTION] in serial mode2023-05-23T09:45:02,453 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: ql.Driver (:()) - Starting task [Stage-0:MOVE] in serial mode2023-05-23T09:45:02,454 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: exec.Task (:()) - Loading data to table ccc.test123 from hdfs://master-52600d0:8020/journey/test123/.hive-staging_hive_2023-05-23_09-44-50_674_1941813141939530227-1/-ext-100002023-05-23T09:45:02,454 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - xxxx getTable: dbName:ccc, tableName:test1232023-05-23T09:45:02,477 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - xxxx getTable: dbName:ccc, tableName:test1232023-05-23T09:45:02,507 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.xxxxCatalogHiveClient (:()) - alter_table_with_environmentContext: dbName: ccc, tblName: test123, newTable: Table(tableName:test123, dbName:ccc, owner:hdfs, createTime:1684805491, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:), FieldSchema(name:name, type:string, comment:)], location:hdfs://master-52600d0:8020/journey/test123/, inputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.ql.io.orc.OrcSerde, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=292, EXTERNAL=FALSE, numRows=1, rawDataSize=90, COLUMN_STATS_ACCURATE={"BASIC_STATS":"true"}, numFiles=1, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, catName:hive, ownerType:USER), EnvironmentContext : EnvironmentContext(properties:{DO_NOT_UPDATE_STATS=true})2023-05-23T09:45:02,537 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - create or alter table: {"database":"ccc","tableName":"test123","tableType":"MANAGED","locationId":"_ccc_test123","relativePath":"","storage":{"format":"ORC","tableProperties":{"serialization.format":"1"},"rewriteEnabled":false,"dataLakeType":"none"},"columns":[{"name":"id","type":"INT","nullable":true,"secretLevel":"L0","description":""},{"name":"name","type":"STRING","nullable":true,"secretLevel":"L0","description":""}],"partitions":[],"ownerType":"USER","owner":"hdfs","properties":{"totalSize":"292","EXTERNAL":"FALSE","numRows":"1","rawDataSize":"90","COLUMN_STATS_ACCURATE":"{\"BASIC_STATS\":\"true\"}","numFiles":"1","bucketing_version":"2"}}2023-05-23T09:45:02,593 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: exec.MoveTask (:()) - Releasing 2 locks2023-05-23T09:45:02,616 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: ql.Driver (:()) - Starting task [Stage-3:STATS] in serial mode2023-05-23T09:45:02,616 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: stats.BasicStatsTask (:()) - Executing stats task2023-05-23T09:45:02,617 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: fs.FSStatsPublisher (:()) - created : hdfs://master-52600d0:8020/journey/test123/.hive-staging_hive_2023-05-23_09-44-50_674_1941813141939530227-1/-ext-100012023-05-23T09:45:02,622 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - ugi current userName: hdfs2023-05-23T09:45:02,623 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.xxxxCatalogHiveClient (:()) - Connected to xxxx metastore.2023-05-23T09:45:02,623 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.RetryingMetaStoreClient (:()) - RetryingMetaStoreClient proxy=class com.baidubce.xxxx.catalog.metastore.xxxxCatalogHiveClient ugi=hdfs (auth:SIMPLE) retries=24 delay=5 lifetime=02023-05-23T09:45:02,623 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - xxxx getTable: dbName:ccc, tableName:test1232023-05-23T09:45:02,642 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: FileOperations (:()) - Read stats for : ccc.test123/ numRows 12023-05-23T09:45:02,642 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: FileOperations (:()) - Read stats for : ccc.test123/ rawDataSize 912023-05-23T09:45:02,642 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.xxxxCatalogHiveClient (:()) - alter_table_with_environmentContext: dbName: ccc, tblName: test123, newTable: Table(tableName:test123, dbName:ccc, owner:hdfs, createTime:1684805491, lastAccessTime:0, retention:0, sd:StorageDescriptor(cols:[FieldSchema(name:id, type:int, comment:), FieldSchema(name:name, type:string, comment:)], location:hdfs://master-52600d0:8020/journey/test123/, inputFormat:org.apache.hadoop.hive.ql.io.orc.OrcInputFormat, outputFormat:org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat, compressed:false, numBuckets:-1, serdeInfo:SerDeInfo(name:null, serializationLib:org.apache.hadoop.hive.ql.io.orc.OrcSerde, parameters:{serialization.format=1}), bucketCols:[], sortCols:[], parameters:{}, skewedInfo:SkewedInfo(skewedColNames:[], skewedColValues:[], skewedColValueLocationMaps:{}), storedAsSubDirectories:false), partitionKeys:[], parameters:{totalSize=585, EXTERNAL=FALSE, numRows=2, rawDataSize=181, COLUMN_STATS_ACCURATE={"BASIC_STATS":"true"}, numFiles=2, bucketing_version=2}, viewOriginalText:null, viewExpandedText:null, tableType:MANAGED_TABLE, catName:hive, ownerType:USER), EnvironmentContext : EnvironmentContext(properties:{DO_NOT_UPDATE_STATS=true})2023-05-23T09:45:02,642 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - tblLocation ->hdfs://master-52600d0:8020/journey/test123/2023-05-23T09:45:02,654 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - dbLocation ->bos://01bmr/2023-05-23T09:45:02,669 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: metastore.XxxCatalogProxy (:()) - create or alter table: {"database":"ccc","tableName":"test123","tableType":"MANAGED","locationId":"_ccc_test123","relativePath":"","storage":{"format":"ORC","tableProperties":{"serialization.format":"1"},"rewriteEnabled":false,"dataLakeType":"none"},"columns":[{"name":"id","type":"INT","nullable":true,"secretLevel":"L0","description":""},{"name":"name","type":"STRING","nullable":true,"secretLevel":"L0","description":""}],"partitions":[],"ownerType":"USER","owner":"hdfs","properties":{"totalSize":"585","EXTERNAL":"FALSE","numRows":"2","rawDataSize":"181","COLUMN_STATS_ACCURATE":"{\"BASIC_STATS\":\"true\"}","numFiles":"2","bucketing_version":"2"}}2023-05-23T09:45:02,721 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: stats.BasicStatsTask (:()) - Table ccc.test123 stats: [numFiles=2, numRows=2, totalSize=585, rawDataSize=181]2023-05-23T09:45:02,721 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: stats.BasicStatsTask (:()) - Table ccc.test123 stats: [numFiles=2, numRows=2, totalSize=585, rawDataSize=181]2023-05-23T09:45:02,724 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: ql.Driver (:()) - Completed executing command(queryId=hdfs_20230523094450_11173651-ff84-44f4-8348-659ab663fa07); Time taken: 11.463 seconds2023-05-23T09:45:02,724 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: ql.Driver (:()) - OK2023-05-23T09:45:02,969 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: CliDriver (:()) - Time taken: 12.25 seconds2023-05-23T09:45:02,969 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: conf.HiveConf (HiveConf.java:getLogIdVar(5050)) - Using the default value passed in for log id: daa8d359-22c3-4d7d-90de-613688f0191a2023-05-23T09:45:02,969 INFO [daa8d359-22c3-4d7d-90de-613688f0191a main]: session.SessionState (SessionState.java:resetThreadName(453)) - Resetting thread name to main为什么会有两次alter_table_with_environmentContext的打印输出？原理 ...

作者：vivo 互联网搜寻团队- Deng Jie随着技术的一直的倒退，大数据畛域对于海量数据的存储和解决的技术框架越来越多。在离线数据处理生态系统最具代表性的分布式解决引擎当属Hive和Spark，它们在分区策略方面有着一些相似之处，但也存在一些不同之处。 一、概述随着技术的一直的倒退，大数据畛域对于海量数据的存储和解决的技术框架越来越多。在离线数据处理生态系统最具代表性的分布式解决引擎当属Hive和Spark，它们在分区策略方面有着一些相似之处，但也存在一些不同之处。本篇文章将剖析Hive与Spark分区策略的异同点、它们各自的优缺点，以及一些优化措施。 二、Hive和Spark分区概念在理解Hive和Spark分区内容之前，首先，咱们先来回顾一下Hive和Spark的分区概念。在Hive中，分区是指将表中的数据划分为不同的目录或者子目录，这些目录或子目录的名称通常与表的列名相关联。比方，一个名为“t\_orders\_name”的表能够依照日期分为多个目录，每个目录名称对应一个日期值。这样做的益处是能够大大提高查问效率，因为只有波及到特定日期的查问才须要扫描对应的目录，而不须要去扫描整个表。Spark的分区概念与Hive相似，然而有一些不同之处，咱们将在后文中进行探讨。 在Hive中，分区能够基于多个列进行，这些列的值组合造成目录名称。例如，如果咱们将“t\_orders\_name”表依照日期和地区分区，那么目录的名称将蕴含日期和地区值的组合。在Hive中，数据存储在分区的目录下，而不是存储在表的目录下。这使得Hive能够快速访问须要的数据，而不用扫描整个表。另外，Hive的分区概念也能够用于数据分桶，分桶是将表中的数据划分为固定数量的桶，每个桶蕴含雷同的行。 而与Hive不同的是，Spark的分区是将数据分成小块以便并行计算解决。在Spark中，分区的数量由Spark执行引擎依据数据大小和硬件资源主动计算得出。Spark的分区数越多，能够并行处理的数据也就越多，因而也能更快的实现计算工作。然而，如果分区数太多，将会导致过多的任务调度和数据传输开销，从而升高整体的性能。因而，Spark分区数的抉择应该思考数据大小、硬件资源和计算工作复杂度等因素。 三、Hive和Spark分区的利用场景在理解Hive和Spark的分区概念之后，接下来，咱们来看看Hive和Spark分区在不同的利用场景中有哪些不同的劣势。 3.1 Hive分区Hive分区实用于大数据场景，能够对数据进行多级分区，以便更细粒度地划分数据，进步查问效率。例如，在游戏平台的充值数据中，能够依照道具购买日期、道具付款状态、游戏用户ID等多个维度进行分区。这样能够不便的进行数据统计、剖析和查问操作，同时防止繁多分区数据过大导致的性能问题。 3.2 Spark分区Spark分区实用于大规模数据处理场景，能够充分利用集群资源进行并行计算解决。比方，在机器学习算法的训练过程中，能够将大量数据进行分区，而后并行处理每个分区的数据，从而进步算法的训练速度和效率。另外，Spark的分布式计算引擎也能够反对在多个节点上进行数据分区和计算，从而进步整个集群的计算能力和效率。 简而言之，Hive和Spark分区在大数据处理和分布式计算场景这都有宽泛的利用，能够通过抉择适合的分区策略和优化措施，进一步提高数据处理的效率和性能。 四、如何抉择分区策略在相熟了Hive和Spark的分区概念以及利用场景后。接下来，咱们来看看在Hive和Spark中如何抉择分区策略。分区策略的抉择对数据处理的效率和性能有着重要的影响。上面将别离论述Hive和Spark分区策略的优缺点以及如何抉择分区策略。 4.1 Hive分区策略长处： Hive的分区策略能够进步查问效率和数据处理性能，特地是在大数据集上体现突出。另外，Hive还反对多级分区，容许更细粒度的数据划分。毛病： 在Hive中，分区是以目录的模式存在的，这会导致大量的目录和子目录，如果分区过多，将会占用过多的存储空间。此外，Hive的分区策略须要在创立表时进行设置，如果数据分布呈现变动，须要从新设置分区策略。4.2 Spark分区策略长处： Spark的分区策略能够依据数据大小和硬件资源主动计算分区数，这使得计算工作能够并行计算解决，从而进步了解决效率和性能。毛病： 如果分区数设置不当，将会导致过多的任务调度和数据传输开销，从而影响整体性能。此外，Spark的分区策略也须要依据数据大小、硬件资源和计算工作复杂度等因素进行调整。4.3 分区策略抉择在理论我的项目开发应用中，抉择适合的分区策略能够显著进步数据处理的效率和性能。然而，如何抉择分区策略须要依据具体情况进行思考，这里总结了一些分区策略抉择的场景： 数据集大小：如果数据集较大，能够思考应用Hive的多级划分策略，以便更细粒度的划分数据，进步查问效率。如果数据集较小，能够应用Spark主动计算分区策略，以便充分利用硬件资源并进步计算效率。 计算工作复杂度：如果计算工作比较复杂，例如须要进行多个JOIN操作，能够应用Hive的分桶策略，以便放慢数据访问速度，缩小JOIN操作的开销。 硬件资源：分区策略的抉择也须要思考硬件资源的限度。如果硬件资源比拟短缺，能够减少分区数以进步计算效率。如果硬件资源比拟缓和，须要缩小分区数以防止任务调度和数据传输的开销。 综上所述，抉择适合的分区策略须要依据具体的状况进行思考，包含数据集大小、计算工作复杂度和硬件资源等因素。在理论应用中，能够通过试验和调试来找到最佳的分区策略。 五、如何优化分区性能除了抉择适合的分区策略之外，还能够通过一些优化措施来进一步提高分区的性能。在Spark中，大多数的Spark工作能够通过三个阶段来表述，它们别离是读取输出数据、应用Spark解决、放弃输入数据。Spark尽管理论数据处理次要产生在内存中，然而Spark应用的是存储在HDFS上的数据来作为输出和输入，工作的调度执行会应用大量的 I/O，存在性能瓶颈。 而Hive分区数据是存储在HDFS上的，然而HDFS对于大量小文件反对不太敌对，因为在每个NameNode内存中每个文件大略有150字节的存储开销，而整个HDFS集群的IOPS数量是有下限的。当文件写入达到峰值时，会对HDFS集群的基础架构的某些局部产生性能瓶颈。 5.1 通过缩小 I/O 带宽来优化性能在Hadoop集群中，它依附大规模并行 I/O 来反对数千个并发工作。比方现有一个大小为96TB的数据节点，磁盘的大小有两种，它们别离是8TB和16TB。具备8TB磁盘的数据节点有12块这样的磁盘，而具备16TB磁盘的数据节点有6块这样的磁盘。咱们能够假如每个磁盘的均匀读写吞吐量约为100MB/s，而这两种不同的磁盘散布，它们对应的带宽和IOPS，具体详情如下表所示： 5.2 通过设置参数来优化性能在Hadoop集群中，每个数据节点为每个卷运行一个卷扫描器，用于扫描块的状态。因为卷扫描器与应用程序竞争磁盘资源，因而限度其磁盘带宽很重要。配置 dfs.block.scanner.volume.bytes.per.second 属性值来定义卷扫描器每秒能够扫描的字节数，默认为1MB/s。 比方设置带宽为5MB/s，扫描12TB所须要的工夫为：12TB / 5MBps = (12 1024 1024 / (3600 * 24)) = 29.13天。 5.3 通过优化Spark解决分区工作来晋升性能如果，当初须要从新计算历史分区的数据表，这种场景通常用于修复谬误或者数据品质问题。在解决蕴含一年数据的大型数据集（比方1TB以上）时，可能会将数据分成几千个Spark分区来进行解决。尽管，从外表上看，这种解决办法并不是最合适的，应用动静分区并将数据后果写入依照日期分区的Hive表中将产生多达上百万个文件。 上面，咱们将工作分区数放大，现有一个蕴含3个分区的Spark工作，并且想将数据写入到蕴含3个分区的Hive表。在这种状况下，心愿发送的是将3个文件写入到HDFS中，所有数据都存储在每个分区的单个文件中。最终会生成9个文件，并且每个文件都有1个记录。应用动静分区写入Hive表时，每个Spark分区都由执行程序来并行处理。 解决Spark分区数据时，每次执行程序在给定的Spark分区中遇到新的分区时，它都会关上一个新文件。默认状况下，Spark对数据会应用Hash或者Round Robin分区器。当利用于任意数据时，能够假如这两种办法在整个Spark分区中绝对平均且随机散布数据。如下图所示： 现实状况下，指标文件大小应该大概是HDFS块大小的倍数，默认状况下是128MB。在Hive中，提供了一些配置参数来主动将后果写入到正当大小的文件中，从开发者的角度来看简直是通明的，比方设置属性 hive.merge.smallfiles.avgsize 和hive.merge.size.per.task 。然而，Spark中不存在此类性能，因而，咱们须要本人开发实现，来确定一个数据集，应该写入多少文件。 5.3.1 基于大小的计算实践上，这是最间接的办法，设置指标大小，估算数据的大小，而后进行划分。然而，在很多状况下，文件被写入磁盘时会进行压缩，并且其格局与存储在 Java 堆中的记录格局有所不同。这意味着估算写入磁盘时内存的记录大小不是一件容易的事件。尽管能够应用 Spark SizeEstimator应用程序通过内存中的数据的大小进行估算。然而，SizeEstimator会思考数据帧、数据集的外部耗费，以及数据的大小。总体来说，这种形式不太容易精确实现。 ...

最近在针对某零碎进行性能优化时，发现了一个hive on spark 模式下应用 HikariCP 数据库连接池造成的资源泄露问题，该问题具备普适性，故顺便拿进去跟大家分享下。 1 问题形容在微服务中，咱们广泛会应用各种数据库连接池技术以放慢获取数据库连贯并执行数据查问的速度，这实质是一种空间换工夫的无效的性能优化的思路。推而广之，在大数据场景下通过JDBC拜访HiveServer2并提交数据查问SQL语句时，也很容易想到同样应用数据库连接池技术以放慢作业速度。然而相比一般的RDBMS，Hive的JDBC连贯更重，以HIVE ON SPARK模式运行作业时更是如此，因为当连贯底层须要执行SQL时，HS2会向YARN申请CONTAINER资源，而后启动分布式的SPARK ON YARN集群并分布式地执行编译好的SQL，当该SQL执行结束后并不会立刻开释SAPRK ON YANR资源，而是会期待一段时间以复用这些 SPARK ON YARN资源执行客户端通过该连贯提交的新的SQL，只有当该JDBC连贯敞开时，或者达到了配置的超时工夫而客户端仍没有提交新的SQL时，才会彻底开释这些 SPARK ON YARN 资源。当业务代码应用了数据库连接池技术时，因为其敞开JDBC连贯时实质上只是将连贯归还给了连接池而没有真是敞开底层的JDBC连贯，所以连贯背地的 SPARK ON YARN资源并不会被及时开释也就是造成了资源泄露，此时其它作业向YARN申请资源时就须要排队期待，从而影响了其它作业的执行。本案列中该零碎应用了HikariCP 数据库连接池，且没有配置数据库连贯的闲暇超时时长（idletimeout)，真正失效的闲暇超时时长是Hikari源码层面配置的默认值10分钟，所以每个连贯底层的SQL作业执行结束后都须要10分钟才真正开释了背地的SPARK ON YARN资源，从而造成了其它作业对YANR资源的排队和期待。（话说你占着资源却不应用，不就是站着那个啥不干那个啥么~_~） 2 问题解决方案针对该问题进行剖析，解决方案有多个，如下别离进行形容。 2.1 解决方案1该计划的思路是彻底摒弃数据库连接池，因为一般而言，大数据作业善于的是大数据量和简单逻辑的解决，其作业执行速大都在分钟级别以上，数据库连接池节俭的1到2秒钟简直微不足道，所以思考到这些应用上的弊病罗唆弃之不必。 2.2 解决方案2该计划的思路是配置HS2 背地SPARK ON YARN集群的SESSION超时工夫，从而更快地开释 SPARK ON YARN 资源。然而通过测试并查看源码发现该超时工夫的最小值是30分钟不能设置更小值，所以起不了成果。相干参数有： hive.spark.session.timeout:默认值30m最小值30m；hive.spark.session.timeout.period:默认值60s最小值60s; 2.3 解决方案3该计划的思路是配置Spark on yarn的动静资源分配机制，从而使得spark on yarn集群在没有SQL作业须要执行时并不会占用太多YARN资源。然而因为SPARK 集群至多须要1个CONTAINER以执行DRIVER，所以该计划只能缓解问题不能彻底解决问题。相干参数有： spark.dynamicAllocation.enabled,须要配置为true;spark.dynamicAllocation.minExecutors:默认为1能够进一步调整为0；2.4 解决方案4该计划的思路是配置客户端和HS2 之间的SESSION超时工夫，从而让HS2被动断开客户端的JDBC连贯并开释背地的Spark ON YARN资源。经测试该计划可行，但批改相干参数须要重启hs2服务过程且该参数的批改会影响所有用户作业，个别集群管理员可能会有异议；相干参数有： hive.server2.session.check.interval:不同版本默认值不同，比方15m/6h;hive.server2.idle.session.timeout:不同版本默认值不同，比方4h/12h/7d;hive.server2.idle.operation.timeout:不同版本默认值不同，比方2h/6h;hive.server2.idle.session.check.operation：true 如下日志可见，session timeout 被敞开后，spark session 也别清理了： 2.5 解决方案5该计划的思路是调整数据库连接池的相干参数尤其是最小连接数和闲暇超时工夫，从而更快地更踊跃地被动敞开闲暇的数据库连贯，比方将IdleTimeout配置为30秒，将最小连接数MinimumIdle配置为0，则SQL作业运行结束30秒后就会敞开所有连贯，也就会开释所有SPARK ON YARN资源，从而解决了资源泄露问题；相干hikariConfig参数有： MinimumIdle：最小连接数；MaximumPoolSize：最大连接数；IdleTimeout：闲暇超时工夫；ConnectionTimeout：获取连贯超时工夫；ValidationTimeout：连贯无效行验证超时工夫；KeepaliveTime：闲暇连贯保活间隔时间；MaxLifetime：连贯最大工夫；示例代码如下： 3 知识点总结大数据作业善于的是大数据量和简单逻辑的解决，其作业执行速大都在分钟级别以上，数据库连接池节俭的1到2秒钟简直微不足道，所以大数据作业个别不应用数据库连接池；当应用数据库连接池时，因为相比一般的RDBMS，Hive的JDBC连贯更重，以 HIVE ON SPARK 模式运行作业时更是如此，所以肯定要及时开释JDBC连贯从而及时开释背地的 YARN资源，从而防止资源泄露问题引起其它作业长时间期待YARN资源；应用数据库连接池时，为及时开释JDBC连贯从而及时开释背地的YARN资源，个别能够调整数据库连接池的相干参数，尤其是最小连接数和闲暇超时工夫，从而更快地更踊跃地被动敞开闲暇的数据库连贯，比方将IdleTimeout配置为30秒，将最小连接数MinimumIdle配置为0，则SQL作业运行结束30秒后就会敞开所有连贯，也就会开释所有SPARK ON YARN资源，从而解决了资源泄露问题。

分享一个 HIVE SQL 性能优化点-应用公共表表达式 CTE 替换长期表hive 作业的性能优化是一个永恒的话题，其优化办法也有很多，在此分享一个优化点，即编写 SQL 时应用公共表表达式 CTE 替换长期表，经测试优化成果还不错，尤其是波及到当量IO的场景。 1. CTE 优化点概述应用公共表表达式CTE (Common Table Expression) 替换长期表（create temporary table temp1 as xx），以进步 SQL 作业的运行效率，并晋升代码的可浏览性和易维护性； 2. CTE 性能优化成果某SQL，应用一个160万数据的长期表的状况下，代码优化前 73秒,优化后47秒，性能晋升大略有35%；某客户现场某 SQL 脚本优化前43分钟，优化后11分钟；（代码较长故不在此提供，外围要点是代码优化前应用了5个长期表，优化后应用了5个CTE）；具体的性能晋升状况，跟长期表的个数，长期表的数据量，以及集群的网络和磁盘IO性能无关；3. CTE 性能优化原理CTE 在性能上相似于一个没有长久化元数据到 hms 也没有长久化数据到 HDFS 的长期表或视图，CTE 通过把查问后果集保留在内存/本地磁盘中，防止了应用长期表的一系列开销，从而达到了性能优化的目标: 应用长期表的系列开销，包含创立和删除 HMS中元数据的开销，也包含三正本模式创立和删除HDFS上的数据的开销；(应用长期表时会创立 hms中的元数据和 hdfs中的数据，session会话完结时会删除长期表在hms中的元数据和 hdfs中的数据)；当数据量比拟大时，Hdfs文件的创立和销毁，波及到大量网络IO和磁盘IO，个别开销都是比拟大的，对咱们的大部分大数据利用来说，性能瓶颈个别是在IO而不是CPU；应用 CTE 后，SQL代码不再简短且构造清晰，从而也进步了代码的可浏览性和易维护性； 4. CTE 语法WITH cte_name1 AS (select_statement1), cte_name2 AS (select statment2) sql_containing_cte_name: cte_name 是公共表表达式的名字;select_statement是一个残缺的SELECT语句;sql_containing_cte_name是蕴含了刚刚定义的公共表表达式的SQL语句;CTE 的 scope 生命周期是 with 子句后的单条 select/insert 语句，所以定义了一个 CTE 当前只能在紧跟着的单条 SQL 中应用，后续的 SQL 语句中该 CTE 的定义是生效的；5. CTE 优化点施行细节应用公共表表达式CTE替换长期表，比方： ...

谓词下推（Predicate Pushdown，简称PPD），是指尽量将SQL查问中的谓词（where条件）提前执行，缩小后续操作的数量和计算量。在Hive中默认是开启的，也能够通过配置 SET hive.optimize.ppd=true; 显式开启。例如，对于如下SQL select a.*, b.* from a join b on (a.col1 = b.col1)where a.col1 > 20 and b.col2 > 40如果开启了谓词下推，在join之前会先执行where语句中对两个表的过滤操作，从而缩小读取和计算的数据量。否则，会先join再过滤。 除了SQL语句执行层面，在文件存储层面(ORC、Parquet等格局），也会有谓词下推的思维。 以ORC为例，每个ORC文件保留了三个层级的索引： file level：蕴含整个文件每列的统计信息stripe level：每个stripe内每列的统计信息row level：在同一个stripe内每10000行进行统计，失去每列的统计信息file 和 stripe 级别的统计信息保留在 file footer 中，能够疾速过滤 where条件中不须要读取的文件。row 级别索引蕴含了每个row group的列统计信息和row group的起始地位。 列统计信息根本都会蕴含其取值数量、是否蕴含null，大部分根本类型的列还会蕴含最小值（min）、最大值（max），数值型的列还有总和（sum）。所有层级的索引都能够用于对须要读取的数据进行过滤。例如，有一个查问语句心愿获取年龄大于100岁的记录，where条件中的语句应该为 age > 100，那么执行时只会读取蕴含年龄大于100岁的文件、stripe或row groups。 但要留神有些操作会使文件存储层面的谓词下推生效，在写代码的时候须要留神。例如，有个日志工夫字段 log_time，其存储的数据格式为 2023-01-01 10:00:00，当初须要取某一天的数据，可能会利用 to_date 函数： select * from table_a where to_date(log_time) = '2023-01-09';但下面的语句就无奈利用ORC文件索引来疾速过滤不须要的文件、stripe或row group，因为对字段 log_time 调用了函数 to_date，导致在真正开始运算前无奈晓得 to_date(log_time) 的理论值。如果批改为应用如下写法，则能够利用文件层面的疾速过滤： select * from table_a where log_time >= '2023-01-09'; and log_time < '2023-01-10';另外，下面咱们提到文件、strip、row group的统计信息会存储相应数据中是否蕴含null，因而倡议应用null而不是"-"、""等来示意空逻辑。 ...

简述hive on spark就是应用hive记录元数据，以及解析sql。应用spark的rdd真正执行计算工作。hdfs负责存储spark负责执行hive负责数据管理 版本apache-hive-3.1.3-bin.tarspark-3.3.1-bin-hadoop3spark-3.3.1-bin-without-hadoop 坑因为初步接触大数据，有些常识性的坑，列在上面。 hive目前只反对jdk1.8，所以hadoop、hive、spark集群里的jdk都得用1.8spark依赖的jar，上传到hdfs集群的时候，要用without-hadoop版本的。spark官网能够找到对应的版本hive适配3.x版本的spark，必须本人用源码编译win环境编译不太不便，因为很多sh脚本win环境下不能间接执行。我这里装了centos虚拟机，桌面版idea编译的hive sql执行失败的时候查看日志。拜访你集群yarn的治理界面，点击进去每次hive连贯，外面log有对应日志配置文件hive-site.xml <?xml version="1.0"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://192.168.0.121:3306/metastore?useSSL=false</value> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.cj.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.metastore.warehouse.dir</name> <value>/user/hive/warehouse</value> </property> <property> <name>hive.metastore.schema.verification</name> <value>false</value> </property> <property> <name>hive.server2.thrift.port</name> <value>10000</value> </property> <property> <name>hive.server2.thrift.bind.host</name> <value>hadoop-4</value> </property> <property> <name>hive.metastore.event.db.notification.api.auth</name> <value>false</value> </property> <property> <name>hive.cli.print.header</name> <value>true</value> </property> <property> <name>hive.cli.print.current.db</name> <value>true</value> </property><property> <name>spark.yarn.jars</name> <value>hdfs://hadoop-4:8020/spark-jars/*</value></property> <property> <name>hive.execution.engine</name> <value>spark</value></property><property> <name>hive.spark.client.connect.timeout</name> <value>100000ms</value></property><property><name>hive.auto.convert.join</name><value>false</value><description>Enables the optimization about converting common join into mapjoin</description></property></configuration>spark-defaults.conf spark.master yarnspark.eventLog.enabled truespark.eventLog.dir hdfs://hadoop-4:8020/spark-historyspark.executor.memory 1gspark.driver.memory 1gspark-env.sh ...

线上 hive on spark 作业执行超时问题排查案例分享大家好，在此分享一个某业务零碎的线上 hive on spark 作业在高并发下频现作业失败问题的起因剖析和解决办法，心愿对大家有所帮忙。 1 问题景象某业务零碎中，HIVE SQL 以 hive on spark 模式运行在 yarn上指定的资源队列下，在业务高峰期发现局部 SQL 会报错，但重试有时又可能胜利。作业具体报错信息，和示例截图如下： SQLfailed to create spark client for spark session xx: java.util.concurrent.TimeoutException: client xx timed out waiting for connection from the remote spark driver 备注： hive 作业的执行引擎通过参数 hive.execution.engine 指定，可选值为 mr/spark/tez；CDP/HIVE3.0等新版本不再反对 hive on mr/spark, 只反对 hive on tez;HIVE 作业在 YARN 上的队列应用参数mapreduce.job.queuename/mapred.job.queue.name 指定；2 问题起因HIVE ON SPARK 作业在高并发下运行失败报超时谬误，然而重试有时又可能胜利，绝大多数都是因为 yarn 中对应的资源队列上没有足够的资源以启动 spark集群，所以客户端连无可连报了超时谬误。 具体的超时工夫，有两个相干参数进行管制： hive.spark.client.server.connect.timeout：该参数是服务端配置，用来管制 hive 客户端跟近程 spark 集群中的 spark driver 建设连贯的超时工夫，默认90秒（这里的 hive客户端是启动 spark 集群的客户端，集体认为其实就是 hs2);hive.spark.client.connect.timeout： 该参数是客户端配置，用来管制近程 spark 集群中的 spark driver 跟 hive 客户端建设连贯的工夫，默认1秒； ...

如何更改 datax 以反对hive 的 DECIMAL 数据类型?1. JAVA 数据类型 - float/double 与 BigDecimal大家晓得，JAVA中能够用来存储小数的数字类型，次要包含： 根本数据类型 float/double;根本数据类型的对应包装类 java.lang.Float/java.lang.Double;java.math 包下的类 java.math.BigDecimal一般来讲，在不须要齐全准确的计算结果的场景下，能够间接应用 float 或 double 数据类型，其运算效率更高；而在须要准确计算结果的场景下，则必须应用 BigDecimal类，比方金融场景下波及金额的计算，就必须是齐全准确的计算： Decimal data types store exact representations of numeric values, while DOUBLE data types store very close approximations of numeric values.Decimal types are needed for use cases in which the (very close) approximation of a DOUBLE is insufficient, such as financial applications, equality and inequality checks, and rounding operations.Decimal types are also needed for use cases that deal with numbers outside the DOUBLE range.在应用 BigDecimal 时，以下细节须要留神： ...

线上数据问题排查案例分享-因为 HMS 和底层 orc 文件中某字段的数据精度不统一造成的数据失落问题大家好，我是明哥，本篇文章跟大家分享一次线上数据问题的排查案例，并总结下背地的技术背景和最佳实际，心愿对大家有所帮忙。 1. 避坑指南和最佳实际先说下通过本案例总结的避坑指南和最佳实际。 hive 的元数据服务 hms 和表底层的 orc 文件中都存储了表的元数据信息，两者人造是割裂的，当两者信息不统一时，容易呈现各种奇怪的数据问题；为防止潜在的数据问题，开发人员须要确保 hms 和 orc 文件中元数据信息的统一，为此应尽量避免更改 hive orc 表字段的数据类型（尤其须要防止对已存储了数据的 orc 表字段做数据类型不兼容的批改）;为防止潜在的数据问题，应用同步工具如 datax 等间接同步数据到 orc 文件时，orc 文件也应该尽量应用对应表在 HMS 中定义的数据类型和数据精度来进行数据同步；为防止潜在的数据问题，应用 SQL 语句进行数据查问操作时，在数据类型不统一时，应尽量应用 CAST 进行显示转换而不依赖 HIVE SQL 的隐式转换机制；2. 问题景象某客户现场局部 HIVE ORC 表在各层的数据条数不统一，存在数据失落问题。 比方对于 hive orc 表 hs_sr.sr_xxx/hs_ods.ods_xxx/hs_tmp.tmp_pxxx， 应用 count(1) 查问，发现 hs_sr 和 hs_ods 记录条数统一，但 hs_tmp 层则短少了400条左右数据。 进一步查问发现，指定条件如 prod_code 查问某条数据时，在 hs_sr 层能够查到该条数据但在 hs_tmp 层查不到该条数据，而在 hs_ods 层查问该条数据时， 只有查问字段列表中蕴含 prod_scale 字段就会查不到该条数据，而查问字段列表不蕴含 prod_scale 字段就能够查问到该条数据。 备注：*本案例中，数据链路为应用 datax 从上游交易系统 oracle 采集到 hive 中的 hs_sr 层，而后应用 hive sql 对 hs_sr 层进行加工解决后写入到 hs_ods 层，再通过 hive sql 进一步加工解决后写到 hs_tmp 层。 ...

背景随着大数据业务的倒退，基于 Hive 的数仓体系逐步难以满足日益增长的业务需要，一方面已有很大体量的用户，然而在实时性，功能性上重大缺失；另一方面 Hudi，Iceberg 这类零碎在事务性，快照治理上带来微小晋升，然而对曾经存在的 Hive 用户有较大的迁徙老本，并且难以满足流式计算毫秒级提早的需要。为了满足网易内外部客户对于流批一体业务的需要，网易数帆基于 Apache Iceberg 研发了新一代流式湖仓，相较于 Hudi，Iceberg 等传统湖仓，它提供了流式更新，维表 Join，partial upsert 等性能，并且将 Hive，Iceberg，音讯队列整合为一套流式湖仓服务，实现了开箱即用的流批一体，能帮忙业务平滑地从 Hive 过渡到 Streaming Lakehouse。 Arctic是什么 Arctic 是搭建在 Apache Iceberg 之上的流式湖仓服务 （ Streaming LakeHouse Service )。相比 Iceberg、Hudi、Delta 等数据湖，Arctic 提供了更加优化的 CDC，流式更新，OLAP 等性能，并且联合了 Iceberg 高效的离线解决能力，Arctic 能服务于更多的流批混用场景。Arctic 还提供了包含构造自优化、并发抵触解决、标准化的湖仓治理性能等，能够无效缩小数据湖在治理和优化上累赘。 Arctic Table 依赖 Iceberg 作为根底表格局，然而 Arctic 没有倾入 Iceberg 的实现，而是将 Iceberg 做为 lib 应用，同时 Arctic 作为专门为流批一体计算设计的流式湖仓，Arctic Table 还封装了音讯队列作为表的一部分，在流式计算场景下能够提供更低的音讯提早，并且提供了流式更新，主键唯一性保障等性能。 流体一批的解决方案在实时计算中，因为低提早的要求，业务通常采纳 Kafka 这类音讯队列作为流表计划，然而在离线计算中，通常采纳 Hive 作为离线表，并且因为音讯队列不反对 AP 查问，通常还须要额定的 OLAP 零碎如 Kudu 以反对实时计算链接的最终数据输入。这就是典型的 Lambda 架构： ...

大数据平台 CDP 中如何配置 hive 作业的 YARN 队列以确保SLA?大家晓得，在生产环境的大数据集群中，在向资源管理器YARN提交作业时，咱们个别会将作业提交到管理员指定的队列去执行，以利用 YARN 队列的资源隔离性确保作业可能取得足够的资源进行执行，从而确保SLA。 1 CDH 中如何指定 HIVE 作业执行时的 YARN 队列？在以往的的大数据平台 CDH中: YARN 默认应用的资源调度器是偏心调度器org.apache.hadoop.yarn.server.resourcemanager.scheduler.fair.FairScheduler;HIVE 作业个别也是应用 hive on mr或hive on spark 执行引擎;处于平安考量，个别举荐HIVE应用kerberos认证和sentry受权，此时要求敞开hive 代理性能,即hive.server2.enable.doAs=false；此时为确保不同业务用户的 HIVE 作业提交到不同的 YARN 队列中，个别有两种抉择： 在提交作业前手动指定队列，个别是通过 set mapreduce.job.queuename/mapred.job.queue.name/mapred.job.queuename = xxx 手工指定；（mapreduce.job.queuename is the new parameter name; mapred.job.queuename is changed to mapred.job.queue.name in hive-3.0.0 by HIVE-17584, and picked up by cdh in 2.1.1-cdh6.3.2)在 CDH 服务端"动静资源池配置"中配置"搁置规定"，指定应用基于用户名匹配到的指定队列（个别是 root.users.[username]);比方某业务用户dap提交的 hive 作业，在没有手动指定队列时，其应用的队列是 root.users.dap,达到了资源隔离的目标:（此时 HIVE 应用 kerberos 认证和 sentry 受权，敞开了 hive 代理性能,即 hive.server2.enable.doAs=false）： ...

始终能在网上看到很多小视频和小文章，上来就讲sql优化，要用 group by，不要用 distinct，然而事实果然是 group by 优于 distinct 吗？对所有数据库和查问引擎来说都是这样吗？显然不是，这篇文章就梳理一下在 hive 和 mysql 中二者的区别！hive 中 group by 和 distinct 孰优孰劣？首先申明一下，hive是什么：hive 不是数据库，hive 只是一个数据仓库工具，能够用来查问、转化和加载数据，是能够调用 mapreduce 工作、用类 mysql 语法查问HDFS数据的一个工具。再来说 mapreduce 是什么，mapreduce 是分而治之的一种编程模型，实用于大规模数据集的并行计算，当解决一个查问工作时，先调用 map 工作并行处理，最初用 reduce 工作归约后果。那么对于一张200w+的表，咱们来看一下group by 和 distinct 的执行过程： set mapreduce.map.java.opts="-Dfile.encoding=UTF-8"; set mapreduce.reduce.java.opts="-Dfile.encoding=UTF-8";select province,city from dulux_dataset_67 group by province,city set mapreduce.map.java.opts="-Dfile.encoding=UTF-8"; set mapreduce.reduce.java.opts="-Dfile.encoding=UTF-8";select distinct province,city from dulux_dataset_67 这里你可能就要问了，不是通常说 group by 的效率高于distinct吗？为什么distinct 和 group by 都调用了 18 个 reduce 工作，distinct 的工夫还破费的少一些，其实在数据量不大的状况下，distinct 和 group by的差异不是很大，然而对于count(distinct *) 来说会产生数据歪斜，因为 hive 默认在解决COUNT这种“全聚合(full aggregates)”计算时，它会疏忽用户指定的Reduce Task数，而强制应用 1，会产生数据歪斜 ...

记录 hive 三类函数：取整函数、随机抽样（rand()函数)、数组蕴含函数（array_contains)、later view explode函数取整函数round(A,B) :四舍五入，A是整型或者浮点型参数，B是要保留的小数位数，B是可选参数，不写的话默认对浮点数保留一位小数 select round(-1.23,1)> -1.2select round(2.546,2)> 2.55select round(2.34)> 2.3floor(A)：取坐标轴上的左值 select floor(-1.3) > -2select floor(1.3) > 1ceil(A) ：取坐标轴上的右值 ,select floor(-1.3) select ceil(-1.3) > -1select ceil(1.3) > 2抽样函数(rand())在大规模数据量的数据集上有两种应用形式，能够随机抽取小数据量的数据进行数据分析和建模： order by rand() 是对全局的数据做排序，会把所有数据放进一个reducer中解决，如果数据量十分大，可能会比拟耗时： select * from table_name where col=xxx order by rand() limit num;在rand() 前指定 distribute 和 sort 关键字能够保证数据在mapper和reduce阶段都是随机的，distribute by是管制在map端如何拆分数据给reduce端的，sort by是部分排序，会在每个reduce端做排序，实用于数据量较大的状况： select * from table_name where col=xxx distribute by rand() sort by rand() limit num;数组蕴含函数，用于判断数组中是否蕴含某个值，返回true 或 falsearray_contains(数组，值)，返回布尔类型值 select *from dulux_dataset_101 where array_contains(mark,' 10')limit 10later view exploadexplode() 是UDTF 函数的一种，能够将一行数据依照分隔符变成多行，然而不能和其它列一起查出，例如dulux_dataset_101中mark字段是个数组 ...

明天在应用Hive On Tez的时候发现一个问题，那就是咱们无奈设置Tez运行在Yarn下面的Job Name。依照设置MR失常的步骤来设置Job Name发现也是有效的。 这让我很蛊惑，于是想了一下是不是因为TezSession的时候曾经写死了导致的，然通过将Hive的源码下载下来（我用的是Hive-3.1.2的源码，下载地址：https://mirror.bit.edu.cn/apa...） 将源码下载当前，依照MR的源码HiveSession来推理，想的应该是TezSession，于是去查找这个类，现实总是很好的，然而事实往往相同，于是又想到了有一个SessionState的类，于是又衍生一个想法是不是也有一个叫TesSessionState的类呢？而后查找源码发现还真的有这个类，那么咱们初步定位到了在这个类中，而后开始浏览该类ql/src/java/org/apache/hadoop/hive/ql/exec/tez/TezSessionState.java的源码发现： final TezClient session = TezClient.newBuilder("HIVE-" + sessionId, tezConfig) .setIsSession(true).setLocalResources(commonLocalResources) .setCredentials(llapCredentials).setServicePluginDescriptor(servicePluginsDescriptor) .build();在源码中写死了Job Name是"HIVE-" + sessionId也就是咱们在页面中看到的HIVE-随机生成的UUID，咱们自定义Job Name也很简略，批改源码如下： String tezJobName = tezConfig.get("tez.job.name", "HIVE-" + sessionId);LOG.info("Tez Job Name " + tezJobName);final TezClient session = TezClient.newBuilder(tezJobName, tezConfig) .setIsSession(true).setLocalResources(commonLocalResources) .setCredentials(llapCredentials).setServicePluginDescriptor(servicePluginsDescriptor) .build();批改源码后执行以下maven命令进行源码从新编译 mvn clean package -DskipTests -U -X留神：如果批改了maven下载仓库为国内镜像的话，肯定要将maven地方仓库加到pom.xml文件中（根目录的）否则会呈现下载jar问题将编译后的ql/target/hive-exec-3.1.2.jar替换原有的Hive中的lib依赖即可！！！ 此时咱们应用set tez.job.name=tezAppName;即可，在Yarn界面中即可看到Application Name曾经改成了咱们自定义的名称。 应用hive --hiveconf tez.job.name=tezAppName

简介：Delta Lake 是 DataBricks 公司开源的、用于构建湖仓架构的存储框架。可能反对 Spark，Flink，Hive，PrestoDB，Trino 等查问/计算引擎。作为一个凋谢格局的存储层，它在提供了批流一体的同时，为湖仓架构提供牢靠的，平安的，高性能的保障。 DeltaLake简介Delta Lake 是 DataBricks 公司开源的、用于构建湖仓架构的存储框架。可能反对 Spark，Flink，Hive，PrestoDB，Trino 等查问/计算引擎。作为一个凋谢格局的存储层，它在提供了批流一体的同时，为湖仓架构提供牢靠的，平安的，高性能的保障。 Delta Lake 要害个性： ACID事务：通过不同等级的隔离策略，Delta Lake 反对多个 pipeline 的并发读写；数据版本治理：Delta Lake 通过 Snapshot 等来治理、审计数据及元数据的版本，并进而反对 time-travel 的形式查问历史版本数据或回溯到历史版本；开源文件格式：Delta Lake 通过 parquet 格局来存储数据，以此来实现高性能的压缩等个性；批流一体：Delta Lake 反对数据的批量和流式读写；元数据演变：Delta Lake 容许用户合并 schema 或重写 schema，以适应不同期间数据结构的变更；丰盛的DML：Delta Lake 反对 Upsert，Delete 及 Merge 来适应不同场景下用户的应用需要，比方 CDC 场景；文件构造湖表较于一般 Hive 表一个很大的不同点在于：湖表的元数据是自治理的，存储于文件系统。下图为 Delta Lake 表的文件构造。 Delta Lake 的文件构造次要有两局部组成： _delta_log目录：存储 deltalake 表的所有元数据信息，其中：每次对表的操作称一次 commit，包含数据操作（Insert/Update/Delete/Merge）和元数据操作（增加新列/批改表配置），每次 commit 都会生成一个新的 json 格局的 log 文件，记录本次 commit 对表产生的行为（action），如新增文件，删除文件，更新后的元数据信息等； 默认状况下，每10次 commit 会主动合并成一个 parquet 格局的 checkpoint 文件，用于减速元数据的解析，及反对定期清理历史的元数据文件； ...

前言初衷如何高效地应用 HiveQL ，将 HiveQL 使用到极致。 在大数据如此风行的明天，不只是业余的数据人员，须要常常地跟 SQL 打交道，即便是产品、经营等非技术搭档，也会或多或少地应用过 SQL ，如何高效地施展 SQL 的能力，继而施展数据的能力，变得尤为重要。 HiveQL 倒退到明天曾经颇为成熟，作为一种 SQL 方言，其反对大多数查问语法，具备较为丰盛的内置函数，同时还反对开窗函数、用户自定义函数、反射机制等诸多高级个性。面对一个简单的数据场景，或者有人技术娴熟，抉择应用 HiveQL 高级个性解决，如：编写用户自定义函数扩大 SQL 的数据处理能力；或者有人抉择敬而远之，转向应用其余非 SQL 类型的解决方案。本文并不探讨不同形式的优劣，而是尝试独辟蹊径，不是强调偏远的语法个性或是简单的 UDF 实现，而是强调 通过灵便的、发散性的数据处理思维，就能够用最简略的语法，解决简单的数据场景。 适宜人群不论是数据开发初学者还是资深人员，本篇文章或者都能有所帮忙，不过更适宜中级、高级读者浏览。 本篇文章重点介绍数据处理思维，并没有波及到过多高阶的语法，同时为了防止主题发散，文中波及的函数、语法个性等，不会破费篇幅进行专门的介绍，读者能够按本身状况自行理解。 内容构造本篇文章将围绕数列生成、区间变换、排列组合、间断判断等主题进行介绍，并附以案例进行理论使用解说。每个主题之间有轻微的前后依赖关系，顺次浏览更佳。 提示信息本篇文章波及的 SQL 语句只应用到了 HiveQL 根本的语法个性，实践上能够在目前的支流版本中运行，同时特意注明，运行环境、兼容性等问题不在本篇文章关注范畴内。 疾速制作测试数据生成用户拜访日志表 visit_log ，每一行数据表示一条用户拜访日志。该表将被用作下文各类场景的测试数据。 -- SQL - 1with visit_log as ( select stack ( 6, '2022-01-01', '101', '湖北', '武汉', 'Android', '2022-01-01', '102', '湖南', '长沙', 'IOS', '2022-01-01', '103', '四川', '成都', 'Windows', '2022-01-02', '101', '湖北', '孝感', 'Mac', '2022-01-02', '102', '湖南', '邵阳', 'Android', '2022-01-03', '101', '湖北', '武汉', 'IOS' ) as (dt, user_id, province, city, device_type))select * from visit_log;数列数列是最常见的数据模式之一，理论数据开发场景中遇到的根本都是无限数列，也是本节将要重点介绍的内容。本节将从最简略的递增数列开始，找出个别办法并推广到更泛化的场景。 ...

在大规模数据量的数据分析及建模工作中，往往针对全量数据进行开掘剖析时会非常耗时和占用集群资源，因而个别状况下只须要抽取一小部分数据进行剖析及建模操作。 Hive全套视频学习材料：http://www.atguigu.com/download.shtml Hive提供了数据取样（SAMPLING）的性能，可能依据肯定的规定进行数据抽样，目前反对数据块抽样，分桶抽样和随机抽样，具体如下所示： 数据块抽样（tablesample()函数）1） tablesample(n percent) 依据hive表数据的大小按比例抽取数据，并保留到新的hive表中。如：抽取原hive表中10%的数据（留神：测试过程中发现，select语句不能带where条件且不反对子查问，可通过新建两头表或应用随机抽样解决） create table xxx_new as select * from xxx tablesample(10 percent)2）tablesample(n M) 指定抽样数据的大小，单位为M。3）tablesample(n rows) 指定抽样数据的行数，其中n代表每个map工作均取n行数据，map数量可通过hive表的简略查问语句确认（关键词：number of mappers: x) 2.分桶抽样hive中分桶其实就是依据某一个字段Hash取模，放入指定数据的桶中，比方将表table_1依照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。创立分桶表的要害语句为：CLUSTER BY语句。分桶抽样语法： TABLESAMPLE (BUCKET x OUT OF y [ON colname])其中x是要抽样的桶编号，桶编号从1开始，colname示意抽样的列，y示意桶的数量。例如：将表随机分成10组，抽取其中的第一个桶的数据 select * from table_01 tablesample(bucket 1 out of 10 on rand()) 随机抽样（rand()函数）1）应用rand()函数进行随机抽样，limit关键字限度抽样返回的数据，其中rand函数前的distribute和sort关键字能够保证数据在mapper和reducer阶段是随机散布的，案例如下： select * from table_name where col=xxx distribute by rand() sort by rand() limit num; ...

1.装置前提1.曾经装置好Mysql，并且设置MySQL的服务是开机自启的。 # Centos中MySQL服务的启动、敞开与重启systemctl status mysqld.servicesystemctl stop mysqld.servicesystemctl start mysqld.service2.MySQL连贯信息：用户名：root明码：123456 3.Hive是依赖Hadoop平台的，所以在初始化hive的时候须要启动Hadoop平台 4.应用xftp将hive的软件包上传到/root目录下 2.解压# 进入root目录下cd /root# 解压tar -zxvf apache-hive-2.3.4-bin.tar.gz # 为了不便之后的操作，进行改名mv apache-hive-2.3.4-bin hive# 将解压之后的文件挪动到/usr/local目录mv hive /usr/local3.配置环境变量# 更改零碎的环境变量文件vi /etc/profile# 追加以下内容export HIVE_HOME=/usr/local/hiveexport PATH=$PATH:$HIVE_HOME/bin# 批改之后肯定要使配置文件失效source /etc/profile4.启动hdfsstart-dfs.sh5.拷贝Mysql的连贯驱动拷贝到hive装置目录的lib目录下# 在其余的环境中须要依据本人实际上传的门路cp mysql-connector-java-5.1.47-bin.jar /usr/local/hive/lib/6.批改配置文件: hive-site.xml# 进入hive的配置文件目录cd /usr/local/hive/confvi hive-site.xml<!-- 小技巧，能够复制hive-default.xml.template中的文件头，并且启动的配置项也能够应用含糊搜寻去匹配--><?xml version="1.0" encoding="UTF-8" standalone="no"?><?xml-stylesheet type="text/xsl" href="configuration.xsl"?><configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://master:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</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>123456</value> </property> <!-- 打印表头信息，不指定字段时，显示"表名.字段" --> <property> <name>hive.cli.print.header</name> <value>true</value> </property> <!-- 打印数据库信息--> <property> <name>hive.cli.print.current.db</name> <value>true</value> </property> <!-- 解决hive报错FAILED: SemanticException Cartesian products are disabled for safety的问题 开启hive shell模式 --> <property> <name>hive.mapred.mode</name> <value>nonstrict</value> </property> <property> <name>hive.strict.checks.cartesian.product</name> <value>false</value> </property> <!--配置hiveserver2的服务 --> <property> <name>hive.server2.thrift.bind.host</name> <value>master</value> </property></configuration>7.批改配置文件：hive-env.sh进入到Hive的装置目录下的conf文件夹中[root@master soft]$ cd /usr/local/hive/conf重命名hive-env.sh.template[root@master conf]$ mv hive-env.sh.template hive-env.sh批改hive-env.sh[root@master conf]$ vi hive-env.sh 如下，批改此文件中HADOOP_HOME、HIVE_CONF_DIR的值（依据本人机器的理论状况配置）# 间接在最初追加即可# 配置 HADOOP_HOME 的门路export HADOOP_HOME=/usr/local/hadoop# 配置 HIVE_CONF_DIR 的门路export HIVE_CONF_DIR=/usr/local/hive/conf8.批改配置文件：hive-log4j.properties创立hive日志存储目录[root@master hive]$ mkdir -p /usr/local/hive/logs/重命名生成文件hive-log4j.properties[root@master opt]# cd /usr/local/hive/conf/[root@master conf]$ pwd/usr/local/hive/conf[root@master conf]$ mv hive-log4j.properties.template hive-log4j.properties[root@master conf]$ vi hive-log4j.properties # 批改文件批改此文件的hive.log.dir属性的值#更改以下内容，设置咱们的hive的日志文件寄存的门路，便于排查问题hive.log.dir=/usr/local/hive/logs/9.初始化hive如果始终初始化失败，能够查看你以后是否在之前的时候曾经初始化了一次，在MySQL中生成了元数据信息，能够尝试先把元数据仓库删除，再去初始化。schematool -dbType mysql -initSchema10.启动hive留神：启动Hive命令行之前的时候须要先启动Hadoop集群;hive-cli命令行测试 ...

1．在默认状况下（set mapreduce.job.reduces=-1）,理论运行计算过程中reducer的数量会由所读取文件的大小来决定。文件默认大小是256M，即每256M对应一个reduce。比方当文件大小为1G时，会启用4个reducer解决数据；当文件大小为400M时，会启用2个reducer来解决。 2.在进行分区或者sort by 操作时，须要设置mapreduce.job.reduces的数量，此时理论启用的reducer的个数等于设置值。 3.1 在进行分桶操作的状况下，当 set mapreduce.job.reduces=-1或0时，此时理论启用rediucer的数量会等于桶的个数i。 3.2在进行分桶操作的状况下，当桶的个数是i时，并且0< set mapreduce.job.reduces<=i时，启用reducer的数量正好是i的因数。在i相邻的两个因数之间，启用reducer的个数是不变的。详见下表: 当i为偶数时： 当i为奇数时： 关键词：大数据培训

一、Hive介绍1.1 Hive介绍1.1.1 OLTP和OLAP数据处理大抵能够分成两大类：联机事务处理OLTP（on-line transaction processing）、联机剖析解决OLAP（On-Line Analytical Processing）。OLTP是传统的关系型数据库的次要利用，次要是根本的、日常的事务处理，例如银行交易。OLAP是数据仓库零碎的次要利用，反对简单的剖析操作，偏重决策反对，并且提供直观易懂的查问后果。 OLTP 零碎强调数据库内存效率，强调内存各种指标的命令率，强调绑定变量，强调并发操作；OLAP 零碎则强调数据分析，强调SQL执行市场，强调磁盘I/O，强调分区等。1.1.2 OLTP和OLAP区别我的项目OLTPOLAP用户操作人员，底层管理人员决策人员，高级管理人员性能日常操作解决剖析决策DB设计面向利用面向主题数据以后的，最新的新界的，二维的分立的历史的，汇集的，多维的，集成的对立的存取读写数十条记录读写百万条记录工作单位简略的事务简单查问用户数上千个上百万个DB大小100MB-GB100GB-TB工夫要求具备实时性对工夫要求不严格次要利用数据库数据仓库1.1.3 Hive构造Apache Hive™数据仓库软件反对应用SQL读取、写入和治理驻留在分布式存储中的大型数据集。构造能够投影到已 经存储的数据上。提供了一个命令行工具和JDBC驱动程序来将用户连贯到Hive。 Hive不能独自应用，它依赖HDFS。Hive不存储数据，它的数据存储到HDFS。Hive自身也不具备剖析能力，它依赖 了底层的分布式计算框架（Mapreduce/spark）。 hive自身最开始facebook公布的。最终要mapreduce来解决来自它自身社交平台的海量数据。hive纳入到apache 的基金会。 以下为hive结构图： hive有3中应用形式： CLi命令行JDBC/ODBC api接口：应用之前要启动Thrift server服务Web GUI页面1.1.4 Hive元数据管理Hive元数据管理默认应用Derby，只是Hive内嵌的数据库，反对单会话。也能够指定其余数据库治理元数据，如Mysql Derby：内嵌自带，单会话MySQL：反对多会话，需要额定进行配置1.2 Hive装置与应用1.2.1 Hive装置1、上传hive安装包apache-hive-1.2.1-bin.tar.gz到linux2、上传mysql的驱动jar包mysql-connector-java-5.1.47-bin.jar到linux3、解压hive安装包4、批改hive-env.sh.template 名称为 hive-env.sh5、批改hive-env.sh HADOOP_HOME=/opt/hadoop-2.8.1 export HIVE_CONF_DIR=/opt/hive-1.2.1/conf6、批改环境变量 export HIVE_HOME=/opt/hive-1.2.1 export PATH=$PATH:$JAVA_HOME/bin:$HADOOP_HOME/bin:$HADOOP_HOME/sbin:$HIVE_HOME/bin1.2.2 Hive启动启动hive之前要先启动hdfs和yarn，如果hdfs启动了然而hive还是无奈连贯，请查看你的hdfs是否处于平安模式。如果是平安模式，请先退出hdfs的平安模式之后，再尝试连贯hive。 CLi启动： $hiveBeeline启动： # 启动Thrift$ hive --service hiveserver2 & (&：作用是在后盾启动)$ hive --service metastore &#启动Beeline$ beeline$ beeline> !connect jdbc:hive2://hdp22:10000$ Enter username for jdbc:hive2://hdp22:10000: root$ Enter password for jdbc:hive2://hdp22:10000: 123456或$ beeline -u jdbc:hive2://hdp22:100001.2.3 MYSQL治理元数据（可选）mysql创立hive用户 ...

在大规模数据量的数据分析及建模工作中，往往针对全量数据进行开掘剖析时会非常耗时和占用集群资源，因而个别状况下只须要抽取一小部分数据进行剖析及建模操作。Hive提供了数据取样（SAMPLING）的性能，可能依据肯定的规定进行数据抽样，目前反对数据块抽样，分桶抽样和随机抽样，具体如下所示： 1.数据块抽样（tablesample()函数） 1） tablesample(n percent) 依据hive表数据的大小按比例抽取数据，并保留到新的hive表中。如：抽取原hive表中10%的数据（留神：测试过程中发现，select语句不能带where条件且不反对子查问，可通过新建两头表或应用随机抽样解决） create table xxx_new as select * from xxx tablesample(10 percent)2）tablesample(n M) 指定抽样数据的大小，单位为M。3）tablesample(n rows) 指定抽样数据的行数，其中n代表每个map工作均取n行数据，map数量可通过hive表的简略查问语句确认（关键词：number of mappers: x) 2.分桶抽样 hive中分桶其实就是依据某一个字段Hash取模，放入指定数据的桶中，比方将表table_1依照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。创立分桶表的要害语句为：CLUSTER BY语句。分桶抽样语法： TABLESAMPLE (BUCKET x OUT OF y [ON colname])其中x是要抽样的桶编号，桶编号从1开始，colname示意抽样的列，y示意桶的数量。例如：将表随机分成10组，抽取其中的第一个桶的数据 select * from table_01 tablesample(bucket 1 out of 10 on rand()) 随机抽样（rand()函数）1）应用rand()函数进行随机抽样，limit关键字限度抽样返回的数据，其中rand函数前的distribute和sort关键字能够保证数据在mapper和reducer阶段是随机散布的，案例如下： select * from table_name where col=xxx distribute by rand() sort by rand() limit num; ...

Hive作业在运行时会在HDFS的长期目录产生大量的数据文件，这些数据文件会占用大量的HDFS空间。这些文件夹用于存储每个查问的长期或两头数据集，并且会在查问实现时通常由Hive客户端清理。然而，如果Hive客户端异样终止，可能会导致Hive作业的长期或两头数据集无奈清理，从而导致Hive作业长期目录占用大量的HDFS空间。本篇文章Fayson次要介绍如何解决清理Hive作业产生的临时文件。 测试环境 1.CM和CDH版本为5.15 2.Hive作业长期目录阐明 Hive作业在运行时会在HDFS的指定目录下生成作业长期或两头数据集存储目录，大数据培训此长期目录门路有hive-site.xml配置中的hive.exec.scratchdir参数定义： Hive 0.2.0到0.8.0版本中默认值为：/tmp/${user.name} Hive0.8.1到0.14.0版本中默认值为：/tmp/hive-${user.name} Hive0.14.1及更高版本中默认值为：/tmp/hive/${user.name} 以后Fayson的CDH5.15.0中Hive版本为1.1.0，Hive作业的长期或两头数据集在HDFS的/tmp/hive/${user.name}目录下: 3.清理Hive作业长期及目录 因为Hive客户端的作业异样退出客户端无奈失常的清理HDFS的长期目录，从而导致长期数据文件占用了大量的HDFS空间。因而，须要在Hive作业长期目录中删除不须要的目录和已实现作业的目录文件。 留神：清理长期Hive作业产生的长期目录时，不能删除任何正在运行的Hive作业这样可能会导致作业运行失败。只能够删除不再与以后正在执行的Hive作业相关联的文件和目录。 1.创立一个定时清理作业的脚本hive_clean.sh内容如下： [root@cdh3 clean]# vim hive-clean.sh !/bin/bashkinit -kt /opt/cloudera/clean/hdfs.keytab hdfs/cdh3.fayson.com@FAYSON.COM yarn jar /opt/cloudera/parcels/CDH/lib/solr/contrib/mr/search-mr--job.jar org.apache.solr.hadoop.HdfsFindTool -find '/tmp/hive/' -type d -name '' -mtime +7 | xargs hdfs dfs -rm -r -f -skipTrash 2.将hive-clean.sh脚本放在/opt/cloudera/clean目录下 留神：Fayson的集群启用了Kerberos，所以这里应用了hdfs用户的keytab，确保执行HDFS命令时有权限删除不必用户创立的临时文件和目录。 3.在Linux中配置crontab定时工作，脚本如下： [root@cdh3 ~]# chmod +x /opt/cloudera/clean/hive-clean.sh [root@cdh3 ~]# crontab -e/1 * /opt/cloudera/clean/hive-clean.sh > /opt/cloudera/clean/clean.log 2>&1 & 这里配置的crontab为每分钟执行一次脚本能够将执行频率调整至1天即“ /1 *” 4.查看执行日志 总结 1.Hive作业在运行时会将长期或两头后果存储HDFS，如果Hive客户端作业异样退出会导致HDFS上存储的长期数据不能被革除而导致HDFS空间被占用。 2.能够通过创立定时工作定期的将Hive作业的临时文件和目录删除 ...

关系运算符比拟两边的后果并返回一个后果值：TRUE或FALSE值，这取决于两边的操作数之间的比拟是否无效，因为比拟的两边有可能呈现null值的状况。除了is null,is not null之外，null在任何关系运算符中呈现后果都是null值。 等值比拟: =语法：A=B 操作类型：所有根本类型 形容: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B相等，则为TRUE；否则为FALSE hive> select 1 from t_fin_demo where 1=1; 不等值比拟: <>语法: A <> B  或！= 操作类型: 所有根本类型 形容: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE 举例： hive> select 1 from t_fin_demo  where 1 <> 2; 小于比拟: <语法: A < B 操作类型: 所有根本类型 形容: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from t_fin_demo  where 1 < 2; 小于等于比拟: <=语法: A <= B 操作类型: 所有根本类型 形容: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A小于或者等于表达式B，则为TRUE；否则为FALSE 举例： hive> select 1 from  t_fin_demo  where 1 <= 1; 大于比拟: >语法: A > B ...

间接上干货，HiveSQL高级进阶技巧，重要性显而易见。把握这10个技巧，你的SQL程度将有一个质的晋升，达到一个较高的档次！1.删除：insert overwrite tmp select * from tmp where id != '666';复制代码2.更新：间接上干货，HiveSQL高级进阶技巧，重要性显而易见。把握这10个技巧，你的SQL程度将有一个质的晋升，达到一个较高的档次！insert overwrite tmp select id,label, if(id = '1' and label = 'grade','25',value) as value from tmp where id != '666';复制代码3.行转列：-- Step03：最初将info的内容切分select id,split(info,':')[0] as label,split(info,':')[1] as valuefrom (-- Step01：先将数据拼接成“heit:180,weit:60,age:26” select id,concat('heit',':',height,',','weit',':',weight,',','age',':',age) as value from tmp) as tmp-- Step02：而后在借用explode函数将数据收缩至多行lateral view explode(split(value,',')) mytable as info;复制代码4.列转行1：select tmp1.id as id,tmp1.value as height,tmp2.value as weight,tmp3.value as age from (select id,label,value from tmp2 where label = 'heit') as tmp1joinon tmp1.id = tmp2.id(select id,label,value from tmp2 where label = 'weit') as tmp2joinon tmp1.id = tmp2.id(select id,label,value from tmp2 where label = 'age') as tmp3on tmp1.id = tmp3.id;复制代码5.列转行2：selectid,tmpmap['height'] as height,tmpmap['weight'] as weight,tmpmap['age'] as agefrom ( ...

少数介绍数据歪斜的文章都是以大篇幅的实践为主，并没有给出具体的数据歪斜案例。当工作中遇到了歪斜问题，这些实践很难间接利用，导致咱们面对歪斜时还是手足无措。 明天咱们不扯大篇实践，间接以例子来实际，排查是否呈现了数据歪斜，具体是哪段代码导致的歪斜，怎么解决这段代码的歪斜。 当执行过程中工作卡在 99%，大概率是呈现了数据歪斜，然而通常咱们的 SQL 很大，须要判断出是哪段代码导致的歪斜，能力利于咱们解决歪斜。通过上面这个非常简单的例子来看下如何定位产生数据歪斜的代码。 表构造形容先来理解下这些表中咱们须要用的字段及数据量： 表的字段十分多，此处仅列出咱们须要的字段 第一张表：user_info （用户信息表，用户粒度） 字段名 字段含意 字段形容userkey 用户 key 用户标识idno 用户的身份证号 用户实名认证时获取phone 用户的手机号 用户注册时的手机号name 用户的姓名 用户的姓名user_info 表的数据量：1.02 亿，大小：13.9G，所占空间：41.7G（HDFS三正本） 第二张表：user_active （用户沉闷表，用户粒度） 字段名 字段含意 字段形容userkey 用户 key 用户没有注册会调配一个 keyuser_active_at 用户的最初沉闷日期 从埋点日志表中获取用户的最初沉闷日期user_active 表的数据量：1.1 亿 第三张表：user_intend（用户意向表，此处只取近六个月的数据，用户粒度） 字段名 字段含意 字段形容phone 用户的手机号 有动向的用户必须是手机号注册的用户intend_commodity 用户动向次数最多的商品 客户对某件商品动向次数最多intend_rank 用户动向等级 用户的购买志愿等级，级数越高，动向越大user_intend 表的数据量：800 万 第四张表：user_order（用户订单表，此处只取近六个月的订单数据，用户粒度） 字段名 字段含意 字段形容idno 用户的身份证号 下订单的用户都是实名认证的order_num 用户的订单次数 用户近六个月下单次数order_amount 用户的订单总金额 用户近六个月下单总金额user_order 表的数据量：640 万 需要需要非常简单，就是将以上四张表关联组成一张大宽表，大宽表中蕴含用户的根本信息，沉闷状况，购买动向及此用户下订单状况。代码依据以上需要，咱们以 user_info 表为根底表，将其余表关联为一个宽表，代码如下：select a.userkey, a.idno, a.phone, a.name, b.user_active_at, c.intend_commodity, c.intend_rank, d.order_num, d.order_amountfrom user_info aleft join user_active b on a.userkey = b.userkeyleft join user_intend c on a.phone = c.phoneleft join user_order d on a.idno = d.idno;执行上述语句，在执行到某个 job 时工作卡在 99%： ...

这期来讲一下Hive日期和工夫函数，因为在一些业务的剖析当中，常常要接触到一些和工夫无关的剖析，例如最近几个月，某项指标的变动状况，那么日期和工夫函数还是须要把握的。上面这个我是依照2021-05-27日期来做的例子： -- 以后日期： select current_date();后果：2021-05-27 -- 以后工夫： select current_timestamp();后果：2021-05-27 15:54:56.052 PRC select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:dd:ss');后果：2021-05-27 16:27:30 --以后工夫戳： select unix_timestamp();后果：1622102134 --工夫戳转日期： select from_unixtime(1622102134,'yyyy-MM-dd HH:dd:ss');后果：2021-05-27 15:27:34 -- 昨天日期： select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);后果：2021-05-26 -- 今天日期： select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),1);后果：2021-05-28 -- 前两天日期： select regexp_replace(to_date(date_sub(from_unixtime(unix_timestamp()),2)),'-','')后果：2021-05-25 --两个日期之间差值（差值为天数）: select datediff('2021-05-28','2021-05-26');后果：2 --字符串转工夫： select to_date('2021-05-01 11:00:12');后果：2021-05-01 --转换日期格局： select date_format(current_date(),'yyyyMMdd');后果：20210527 --utc工夫转换： select from_utc_timestamp(current_timestamp(),'PRC');后果：2021-05-28 00:52:07.000 --获取秒： select second('2021-05-27 16:52:07');后果：7 --获取分： select minute('2021-05-27 16:52:07');后果：52 --获取时： select hour('2021-05-27 16:52:07');后果：16 --获取天： select day('2021-05-27 16:52:07');后果：27 --获取周： select weekofyear('2021-05-27 16:52:07');后果：21 ...

Hive的优化次要分为：配置优化、SQL语句优化、工作优化等计划。其中在开发过程中次要波及到的可能是SQL优化这块。 优化的核心思想是： 缩小数据量（例如分区、列剪裁）防止数据歪斜（例如加参数、Key打散）防止全表扫描（例如on增加加上分区等）缩小job数（例如雷同的on条件的join放在一起作为一个工作）本文首发在公众号【五分钟学大数据】HQL语句优化1. 应用分区剪裁、列剪裁在分区剪裁中，当应用外关联时，如果将副表的过滤条件写在Where前面，那么就会先全表关联，之后再过滤。 select a.* from a left join b on a.uid = b.uid where a.ds='2020-08-10' and b.ds='2020-08-10'下面这个SQL次要犯了两个谬误： 副表(上方b表)的where条件写在join前面，会导致先全表关联在过滤分区。注：尽管a表的where条件也写在join前面，然而a表会进行谓词下推，也就是先执行where条件，再执行join，然而b表不会进行谓词下推！on的条件没有过滤null值的状况，如果两个数据表存在大批量null值的状况，会造成数据歪斜。正确写法： select a.* from a left join b on (d.uid is not null and a.uid = b.uid and b.ds='2020-08-10') where a.ds='2020-08-10'如果null值也是须要的，那么须要在条件上转换，或者独自拿进去 select a.* from a left join b on (a.uid is not null and a.uid = b.uid and b.ds='2020-08-10') where a.ds='2020-08-10' union all select a.* from a where a.uid is null 或者： ...

在咱们编写 SQL 时候，经常须要应用到长期表。 而后咱们依据这个长期表，进行之后的操作，然而创立长期表有肯定的开销。 1.WITH 创立长期表如果这个长期表并不需要保留，并且下文只须要用无限的几次，咱们能够采纳上面的办法。 with as 也叫做子查问局部，首先定义一个 sql 片段，该 sql 片段会被整个 sql 语句所用到，为了让 sql 语句的可读性更高些，作为提供数据的局部，也经常用在 union 等汇合操作中。 with as 就相似于一个视图或长期表，能够用来存储一部分的 sql 语句作为别名，不同的是 with as 属于一次性的，而且必须要和其余 sql 一起应用才能够！ 其最大的益处就是适当的进步代码可读性，而且大数据培训如果 with 子句在前面要屡次应用到，这能够大大的简化 SQL；更重要的是：一次剖析，屡次应用，这也是为什么会提供性能的中央，达到了“少读”的指标。 WITH t1 AS ( SELECT * FROM carinfo ), t2 AS ( SELECT * FROM car_blacklist )SELECT *FROM t1, t2留神： 1.1 这里必须要整体作为一条 sql 查问，即 with as 语句后不能加分号，不然会报错。 1.2 with 子句必须在援用的 select 语句之前定义,同级 with 关键字只能应用一次,多个只能用逗号宰割 1.3 如果定义了 with 子句，但其后没有跟 select 查问，则会报错！ ...

咱们本期内容大部分HQL操作都须要依赖如下两张表，具体的数据内容如下： course student 1、SELECT查问语句 SELECT 查问语句比较简单，前面跟要查问的字段，如下所示： hive (hypers)> select name from student;OKnameRoseJackJimmyTomJerry能够为查问语句中的列和表加上别名，如下所示： hive (hypers)> select t.name from student t;OKt.nameRoseJackJimmyTomJerry能够应用如下语句进行嵌套查问： hive (hypers)> select a.name, b.coursename > from (select stuid, name from student) a > join (select stuid, coursename from course) b on a.stuid = b.stuid; OKa.name b.coursenameRose C语言Jack JavaJimmy 高等数学Tom 离散数学Jerry C++能够应用正则表达式指定查问的列，如下所示： hive (hypers)> select t.* from student t;OKt.stuid t.name t.sex t.age15317408 Rose 1 2115317412 Jack 0 2015317432 Jimmy 1 2115317423 Tom 1 2015317478 Jerry 0 1915317467 Alice 0 20能够应用 LIMIT 限度查问的后果条数，如下所示： ...

Hive 咱们在上一节讲到，MapReduce 尽管只有 map 和 reduce 这两个函数，但简直能够满足任何大数据分析和机器学习的场景。不过，简单的计算可能须要应用多个 job 能力实现，这些 job 之间还须要依据其先后依赖关系进行作业的编排，开发比较复杂。 在 Hadoop 呈现之前，大部分的数据分析人员根本都用 SQL 语句剖析数据库中的数据，如果让这些数据分析人员重新学习一下 Hadoop 反对的开发语言，将会消耗微小的人力老本和学习老本。 所以，就有聪慧的小伙伴想到，如果能依据 SQL 主动生成 MapReduce，就能够极大升高大数据技术在数据分析畛域的利用门槛。 基于这样的一个需要场景，Hive 横空出世。Hive的架构 首先让咱们来看下 Hive 的架构 。Hive能够通过CLI，JDBC和 ODBC 等客户端进行拜访。除此之外，Hive还反对 WUI 拜访Hive外部执行流程：解析器（解析SQL语句）、编译器（把SQL语句编译成MapReduce程序）、优化器（优化MapReduce程序）、执行器（将MapReduce程序运行的后果提交到HDFS）Hive的元数据保留在数据库中，如保留在MySQL，SQLServer，PostgreSQL，Oracle及Derby等数据库中。Hive中的元数据信息蕴含表名，列名，分区及其属性，表的属性（包含是否为内部表），表数据所在目录等。Hive将大部分 HiveSQL语句转化为 MapReduce 作业提交到 Hadoop上执行；多数 HiveSQL 语句不会转化为MapReduce作业，间接从DataNode上获取数据后依照程序输入。 通晓了[大数据培训](http://www.atguigu.com)中Hive架构，咱们接下来通过一条常见的 SQL 语句，来看看 Hive 是如何将其转换成 MapReduce 来计算的。MapReduce 实现 SQL 的原理SELECT pageid, age, count(1) FROM pv_users GROUP BY pageid, age;1. 就像所展现的一样，这是一条很常见的 SQL 统计分析语句，用于统计不同年龄的用户拜访不同网页的趣味偏好，具体数据输出和执行的后果示例如图所示： 看这个示例咱们就会发现，这个计算场景和 WordCount 很像。事实上也的确如此，咱们能够用 MapReduce 实现这条 SQL 的解决。 这样一条很有实用价值的 SQL 就被很简略的 MapReduce 计算过程解决好了。 map 函数输入的 key 是表的行记录，value 是 1，reduce 函数对雷同的行进行记录，也就是针对具备雷同 key 的 value 汇合进行求和计算，最终失去 SQL 的输入后果。 Hive要做的就是将SQL翻译成MapReduce程序代码。实际上，Hive内置了很多Operator，每个Operator实现一个特定的计算过程，Hive将这些Operator结构成一个有向无环图DAG，而后依据这些Operator之间是否存在shuffle将其封装到map或者reduce函数中，之后就能够提交给MapReduce执行了。 Operator 组成的 DAG 如下图 所示，这是一个蕴含 where 查问条件的 SQL，where 查问条件对应一个 FilterOperator。 值得注意的是，有些 HiveQL 语句不会被 Hive 转化成 MapReduce 作业，Hive 只会从 DataNode 将数据获取到了之后，依照程序顺次输入。select * from user;1.Hive 如何实现 join 操作 ...

Hive on Spark是指应用Spark代替传统MapReduce作为Hive的执行引擎，在HIVE-7292提出。Hive on Spark的效率比on MR要高不少，然而也须要正当调整参数能力最大化性能，本文简略列举一些调优项。为了符合实际状况，Spark也采纳on YARN部署形式来阐明。 Driver参数 spark.driver.cores 该参数示意每个Executor可利用的CPU外围数。其值不宜设定过大，因为Hive的底层以HDFS存储，而HDFS有时对高并发写入解决不太好，容易造成race condition。依据咱们的实际，设定在3~6之间比拟正当。 假如咱们应用的服务器单节点有32个CPU外围可供使用。思考到零碎根底服务和HDFS等组件的余量，个别会将YARN NodeManager的yarn.nodemanager.resource.cpu-vcores参数设为28，也就是YARN可能利用其中的28核，此时将spark.executor.cores设为4最合适，最多能够正好调配给7个Executor而不造成节约。又假如yarn.nodemanager.resource.cpu-vcores为26，那么将spark.executor.cores设为5最合适，只会残余1个核。 因为一个Executor须要一个YARN Container来运行，所以还需保障spark.executor.cores的值不能大于单个Container能申请到的最大外围数，即yarn.scheduler.maximum-allocation-vcores的值。   spark.executor.memory/spark.yarn.executor.memoryOverhead 这两个参数别离示意每个Executor可利用的堆内内存量和堆外内存量。堆内内存越大，Executor就能缓存更多的数据，在做诸如map join之类的操作时就会更快，但同时也会使得GC变得更麻烦。Hive官网提供了一个计算Executor总内存量的教训公式，如下： yarn.nodemanager.resource.memory-mb * (spark.executor.cores / yarn.nodemanager.resource.cpu-vcores) 其实就是按外围数的比例调配。在计算出来的总内存量中，80%~85%划分给堆内内存，残余的划分给堆外内存。   假如集群中单节点有128G物理内存，yarn.nodemanager.resource.memory-mb（即单个NodeManager可能利用的主机内存量）设为120G，那么总内存量就是：120 1024 (4 / 28) ≈ 17554MB。再按8:2比例划分的话，最终spark.executor.memory设为约13166MB，spark.yarn.executor.memoryOverhead设为约4389MB。大数据培训   与上一节同理，这两个内存参数相加的总量也不能超过单个Container最多能申请到的内存量，即yarn.scheduler.maximum-allocation-mb。 spark.executor.instances 该参数示意执行查问时一共启动多少个Executor实例，这取决于每个节点的资源分配状况以及集群的节点数。若咱们一共有10台32C/128G的节点，并依照上述配置（即每个节点承载7个Executor），那么实践上讲咱们能够将spark.executor.instances设为70，以使集群资源最大化利用。然而实际上个别都会适当设小一些（举荐是理论值的一半左右），因为Driver也要占用资源，并且一个YARN集群往往还要承载除了Hive on Spark之外的其余业务。 spark.dynamicAllocation.enabled 下面所说的固定调配Executor数量的形式可能不太灵便，尤其是在Hive集群面向很多用户提供剖析服务的状况下。所以更举荐将spark.dynamicAllocation.enabled参数设为true，以启用Executor动态分配。 Driver参数spark.driver.cores 该参数示意每个Driver可利用的CPU外围数。绝大多数状况下设为1都够用。 spark.driver.memory/spark.driver.memoryOverhead 这两个参数别离示意每个Driver可利用的堆内内存量和堆外内存量。依据资源充裕水平和作业的大小，个别是将总量管制在512MB~4GB之间，并且沿用Executor内存的“二八调配形式”。例如，spark.driver.memory能够设为约819MB，spark.driver.memoryOverhead设为约205MB，加起来正好1G。 Hive参数绝大部分Hive参数的含意和调优办法都与on MR时雷同，但仍有两个须要留神。  hive.auto.convert.join.noconditionaltask.size 咱们晓得，当Hive中做join操作的表有一方是小表时，如果hive.auto.convert.join和hive.auto.convert.join.noconditionaltask开关都为true（默认即如此），就会主动转换成比拟高效的map-side join。而hive.auto.convert.join.noconditionaltask.size这个参数就是map join转化的阈值，在Hive on MR下默认为10MB。 然而Hive on MR下统计表的大小时，应用的是数据在磁盘上存储的近似大小，而Hive on Spark下则改用在内存中存储的近似大小。因为HDFS上的数据很有可能被压缩或序列化，使得大小减小，所以由MR迁徙到Spark时要适当调高这个参数，以保障map join失常转换。个别会设为100~200MB左右，如果内存富余，能够更大点。 hive.merge.sparkfiles 小文件是HDFS的天敌，所以Hive原生提供了合并小文件的选项，在on  MR时是hive.merge.mapredfiles，然而on Spark时会改成hive.merge.sparkfiles，留神要把这个参数设为true。至于小文件合并的阈值参数，即hive.merge.smallfiles.avgsize与hive.merge.size.per.task都没有变动。

已知顾客拜访店铺的拜访日志user_idshopu1au2bu1bu1au3cu4bu1au2cu5bu4bu6cu2cu1bu2au2au3au5au5au5a建表、导表create table visit(user_id string,shop string) row format delimited fields terminated by '\t';load data local inpath '/data/visit.dat' into table visit;求每个店铺的UV（访客数）select shop , count(distinct user_id) as uv from visit group by shop;shopUVshopuva4b4c3求每个店铺拜访次数top3的访客信息。输入店铺名称、访客id、拜访次数1.先求出每个店铺，每个访客的拜访次数select shop, user_id, count(*) as ct from visit group by shop, user_id;shopuser_idctau13bu12au22bu21cu22au31cu31bu42au53bu51cu612.计算每个店铺被用户拜访次数排名select shop,user_id,ct,rank() over(partition by shop order by ct) rkfrom (select shop, user_id, count(*) as ct from visit group by shop, user_id) as t1;shopuser_idctrkau311au222au533au133bu511bu211bu423bu123cu611cu311cu2233.取每个店铺的前三名select shop, user_id, ct from(select shop,user_id,ct,rank() over(partition by shop order by ct) rkfrom (select shop, user_id, count(*) as ct from visit group by shop, user_id) as t1) as t2 where rk <= 3;shopuser_idctau31au22au13au53bu21bu51bu12bu42cu31cu61cu22The End

简介： 随着互联网技术的日渐倒退、数据规模的扩充与简单的需要场景的产生，传统的大数据架构无奈承载。 作者 | 志羽起源 | 阿里技术公众号 一 前言传统的大数据技术起源于 Google 三架马车 GFS、MapReduce、Bigtable，以及其衍生的开源分布式文件系统 HDFS，分布式计算引擎 MapReduce，以及分布式数据库 HBase。最后的大数据技术与需要往往集中在超大规模数据存储、数据处理、在线查问等。在这个阶段，很多公司会抉择自建机房部署 Hadoop 的形式，大数据技术与需要集中在离线计算与大规模存储上，常见的体现形式有 T+1 报表，大规模数据在线查问等。 随着互联网技术的日渐倒退、数据规模的扩充与简单的需要场景的产生，传统的大数据架构无奈承载。大数据架构在近些年的演进次要体现下以下几方面： 规模化：这里的规模化次要体现在大数据技术的应用规模上和数据规模的增长。大数据技术的应用规模增长代表越来越多的简单需要产生，而数据规模的增长决定了传统的准大数据技术（如 MySQL）无奈解决所有问题。因而，拿存储组件举例来说，通常会划分到不同的数据分层，面向规模、老本、查问和剖析性能等不同维度的优化偏差，以满足多样性的需要。 实时化：传统的 T+1 的离线大数据技术无奈满足举荐、监控类近实时的需要，整个大数据生态和技术架构在过来十年产生了很大的升级换代。就存储上来说，传统的 HDFS 文件存储、Hive 数仓无奈满足低成本，可更新迭代的需要，因而滋生出 Hudi 等数据计划。就计算上来说，传统的 MapReduce 批处理的能力无奈做到秒级的数据处理，先后呈现 Storm 较原始的实时处理和 Spark Streaming 的微批处理，目前由 Flink 基于 Dataflow 模型的实时计算框架在实时计算畛域占据相对主导地位。 云原生化：传统的公司往往会抉择自建机房，或者在云上购买机器部署实例这种云托管的模式，但这种架构存在低谷期利用率低，存储计算不拆散导致的存储和计算弹性差，以及降级灵便度低等各种问题。云原生大数据架构就是所谓的数据湖，其本质就是充分利用云上的弹性资源来实现一个对立治理、对立存储、弹性计算的大数据架构，改革了传统大数据架构基于物理集群和本地磁盘的计算存储架构。其次要技术特色是存储和计算拆散和 Serverless。在云原生大数据架构中，每一层架构都在往服务化的趋势演进，存储服务化、计算服务化、元数据管理服务化等。每个组件都被要求拆分成不同的单元，具备独立扩大的能力，更凋谢、更灵便、更弹性。 本篇文章将基于云原生大数据架构的场景，具体探讨实时计算中的维表和后果表的架构选型。 二 大数据架构中的实时计算1 实时计算场景大数据的高速倒退曾经超过 10 年，大数据也正在从计算规模化向更加实时化的趋势演进。实时计算场景次要有以下几种最常见的场景： 实时数仓：实时数仓次要利用在网站 PV / UV 统计、交易数据统计、商品销量统计等各类交易型数据场景中。在这种场景下，实时计算工作通过订阅业务实时数据源，将信息实时秒级剖析，最终出现在业务大屏中给决策者应用，不便判断企业经营情况和流动促销的状况。 实时举荐：实时举荐次要是基于 AI 技术，依据用户爱好进行个性化举荐。常见于短视频场景、内容资讯场景、电商购物等场景。在这种场景下，通过用户的历史点击状况实时判断用户爱好，从而进行针对性举荐，以达到减少用户粘性的成果。 数据 ETL：实时的 ETL 场景常见于数据同步工作中。比方数据库中不同表的同步、转化，或者是不同数据库的同步，或者是进行数据聚合预处理等操作，最终将后果写入数据仓库或者数据湖进行归档积淀。这种场景次要是为后续的业务深度剖析进行后期筹备工作。 实时诊断：这种常见于金融类或者是交易类业务场景。在这些场景中，针对行业的独特性，须要有反作弊监管，依据实时短时间之内的行为，断定用户是否为舞弊用户，做到及时止损。该场景对时效性要求极高，通过实时计算工作对异样数据检测，实时发现异常并进行及时止损。 2 Flink SQL 实时计算实时计算须要后盾有一套极其弱小的大数据计算能力，Apache Flink 作为一款开源大数据实时计算技术应运而生。因为传统的 Hadoop、Spark 等计算引擎，实质上是批计算引擎，通过对无限的数据集进行数据处理，其解决时效性是不能保障的。而 Apache Flink ，从设计之初就以定位为流式计算引擎，它能够实时订阅实时产生的流式数据，对数据进行实时剖析解决并产生后果，让数据在第一工夫施展价值。 ...

前言依据对用户ID、拜访日期、拜访次数的若干条数据，剖析得出每个用户的累计拜访次数，波及字符串替换、工夫函数、开窗、求和等技术。 1. 如果有用户拜访数据（用户ID，拜访日期，拜访次数）userIdvisitDatevisitCountu012017/1/215u022017/1/236u032017/1/228u042017/1/203u012017/1/236u012017/2/218u022017/1/236u012017/2/2242. 心愿失去每个月用户的拜访累计次数用户id月份小计累积u012017-011111u012017-021223u022017-011212u032017-0188u042017-01333. 启动hive，选库，建表create table action (userId string, visitDate string, visitCount int) row format delimited fields terminated by "\t";4. 将数据导入表load data local inpath '/data/action.dat' into table action;5. 转换日期字符串为规范格局select userId, date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn, visitCountfrom action;useridmnvisitcountu012017-015u022017-016u032017-018u042017-013u012017-016u012017-028u022017-016u012017-0246. 计算每人单月访问量select userId, mn, sum(visitCount) mn_countfrom (select userId, date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn, visitCount from action) as t1group by userId,mn;useridmnmn_countu012017-0111u012017-0212u022017-0112u032017-018u042017-0137. 按月累计访问量select userId, mn, mn_count, sum(mn_count) over(partition by userId order by mn)from (select userId, mn, sum(visitCount) mn_count from (select userId, date_format(regexp_replace(visitDate,'/','-'),'yyyy-MM') mn, visitCount from action) as t1 group by userId,mn) as t2;useridmnmn_countsum_window_0u012017-011111u012017-021223u022017-011212u032017-0188u042017-0133The End

一、批改引擎默认解决引擎是MapReduce 能够批改解决引擎位Spark 批改为Spark引擎后速度晋升10多倍 二、参数设置2.1、启动时提醒上面须要设置In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number>In order to set a constant number of reducers: set mapreduce.job.reduces=<number>reduce数量由以下三个参数决定，mapred.reduce.tasks(强制指定reduce的工作数量) hive.exec.reducers.bytes.per.reducer（每个reduce工作解决的数据量，默认为1000^3=1G） hive.exec.reducers.max（每个工作最大的reduce数，默认为999） 计算reducer数的公式很简略N=min( hive.exec.reducers.max ，总输出数据量/ hive.exec.reducers.bytes.per.reducer ) 只有一个reduce的场景： a、没有group by 的汇总 b、order by c、笛卡尔积 三、Fetch抓取Fetch抓取是指，Hive中对某些状况的查问能够不用应用MapReduce计算。例如：SELECT * FROM employees;在这种状况下，Hive能够简略地读取employee对应的存储目录下的文件，而后输入查问后果到控制台。在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性批改为more当前，在全局查找、字段查找、limit查找等都不走mapreduce。 <property> <name>hive.fetch.task.conversion</name> <value>more</value> <description> Expects one of [none, minimal, more]. Some select queries can be converted to single FETCH task minimizing latency. Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins. 0. none : disable hive.fetch.task.conversion 1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns) </description></property>案例实战： ...

基本原理 Hive是基于Hadoop的一个数据仓库工具，能够将结构化的数据文件映射为一张数据库表，并提供简略的SQL查问性能，能够将SQL语句转换为MapReduce工作进行运行。 其长处是学习成本低，能够通过类SQL语句疾速实现简略的MapReduce统计，不用开发专门的MapReduce利用，非常适宜数据仓库的统计分析。Hive构建在基于动态批处理的Hadoop之上，因为Hadoop通常都有较高的提早并且在作业提交和调度的时候须要大量的开销。因而，Hive并不适宜那些须要低提早的利用，它最适宜利用在基于大量不可变数据的批处理作业，例如，网络日志剖析。Hive的特点是：可伸缩（在Hadoop集群上动静的增加设施）、可扩大、容错、输入格局的涣散耦合。Hive将元数据存储在关系型数据库（RDBMS）中，比方MySQL、Derby中。Hive有三种模式连贯到数据，其形式是：单用户模式，多用户模式和近程服务模式。（也就是内嵌模式、本地模式、近程模式）。零碎环境Linux Ubuntu 20.04OpenJDK-11.0.11hadoop-3.2.2mysql-8.0.25mysql-connector-java-8.0.25.jar 前提条件在Hadoop和mysql装置实现的根底上，搭建hive。 装置步骤1.在Linux本地创立/data/hive1文件夹用来寄存安装包。 mkdir -p /data/hive1切换到/data/hive1的目录下，应用wget命令下载apache-hive-2.3.8-bin.tar.gz和mysql-connector-java-8.0.25.jar。 cd /data/hive1wget https://mirrors.tuna.tsinghua.edu.cn/apache/hive/hive-2.3.8/apache-hive-2.3.8-bin.tar.gzwget https://dev.mysql.com/downloads/file/?id=5046462.将/data/hive1目录下的apache-hive-2.3.8-bin.tar.gz，解压缩到/apps目录下。 tar -xzvf /data/hive1/apache-hive-2.3.8-bin.tar.gz -C /apps/再切换到/apps目录下，将/apps/hive-1.1.0-cdh5.4.5，重命名为hive。 cd /apps mv /apps/apache-hive-2.3.8-bin.tar.gz/ /apps/hive 3.应用vim关上用户环境变量。 sudo vim ~/.bashrc将Hive的bin目录，增加到用户环境变量PATH中，而后保留退出。 #hive config export HIVE_HOME=/apps/hive export PATH=$HIVE_HOME/bin:$PATH执行source命令，使Hive环境变量失效。 source ~/.bashrc 4.因为Hive须要将元数据，存储到Mysql中。所以须要拷贝/data/hive1目录下的mysql-connector-java-8.0.25.jar到hive的lib目录下。 cp /data/hive1/mysql-connector-java-8.0.25.jar/apps/hive/lib/ 5.上面配置Hive，切换到/apps/hive/conf目录下，并创立Hive的配置文件hive-site.xml。 cd /apps/hive/conf touch hive-site.xml 应用vim关上hive-site.xml文件。 vim hive-site.xml并将下列配置项，增加到hive-site.xml文件中。 <property>#javax.jdo.option.ConnectionDriverName：连贯数据库的驱动包。 <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> </property><property>#javax.jdo.option.ConnectionUserName：数据库用户名。 <name>javax.jdo.option.ConnectionUserName</name> <value>root</value></property> <property>#javax.jdo.option.ConnectionPassword：连贯数据库的明码。 <name>javax.jdo.option.ConnectionPassword</name> <value>123456</value> </property><property> <name>hive.server2.thrift.port</name> <value>10000</value></property><property> <name>hive.server2.thrift.bind.host</name> <value>127.0.0.1</value></property>因为Hive的元数据会存储在Mysql数据库中，所以须要在Hive的配置文件中，指定mysql的相干信息。 javax.jdo.option.ConnectionURL：数据库链接字符串。javax.jdo.option.ConnectionDriverName：连贯数据库的驱动包。javax.jdo.option.ConnectionUserName：数据库用户名。javax.jdo.option.ConnectionPassword：连贯数据库的明码。此处的数据库的用户名及明码，须要设置为本身零碎的数据库用户名及明码。 6.另外，还须要通知Hive，Hadoop的环境配置。所以咱们须要批改hive-env.sh文件。 首先咱们将hive-env.sh.template重命名为hive-env.sh。 mv /apps/hive/conf/hive-env.sh.template /apps/hive/conf/hive-env.sh 应用vim关上hive-env.sh文件。 vim hive-env.sh追加Hadoop的门路，以及Hive配置文件的门路到文件中。 ...

[当你有些统计需要实现不了时，能够看看这些技巧哦～] sum 函数加 over 关键字：用法：sum(count(1)) over(partition by talent_account_type order by create_date rows between unbounded preceding and current row) sys_total 形容：依据 talen_account_type 维度来分组统计总和，按 create_date 枯燥递增形式来累加 group by 的扩大妙用：在最初减少 with CUBE 能够依据多维度几几组合维度来统计，能够统计某一个维度的所有数据。通常能够用 coalesce(create_date,'所有日期') 来解决 null 的字段，以减少可读性。 动态创建一个任意维度+日期值的表：用法：select time, talent_typefrom (select date_add(get_date(-30),a.rk) time,1 talent_typefrom(select row_number()over(order by 1) as rk from vipdw.dw_vccp_media_ds limit 30) a)union (select date_add(get_date(-30),a.rk) time,2 talent_typefrom(select row_number()over(order by 1) as rk from vipdw.dw_vccp_media_ds limit 30) a)（轻易 from 一个表就行，原理大略是利用查出来30条数据，而后动静再拼多一个日期加维度的字段，而后只用这两个字段） Ps：通常能够用于创立该表后，再用这个表 left join 一些数据，配合 IFNULL(xxx, 0) 函数，能够达到统计有些日期没数据的统计表也能连着日期来展现 ...

一、拉链表原理1、引入在数据仓库的数据模型设计过程中，常常会遇到这样的需要：（1）数据量比拟大。（2）表中的局部字段会被update,如用户的地址，产品的形容信息，订单的状态等等。（3）须要查看某一个工夫点或者时间段的历史快照信息，比方，查看某一个订单在历史某一个工夫点的状态。（4）查看某一个用户在过来某一段时间内，更新过几次等等。（5）变动的比例和频率不是很大，比方，总共有1000万的会员，每天新增和发生变化的有10万左右。（6）如果对这边表每天都保留一份全量，那么每次全量中会保留很多不变的信息，对存储是极大的节约。拉链表，既能满足反馈数据的历史状态，又能够最大水平的节俭存储。 2、拉链表概念拉链表是针对数据仓库设计中表存储数据的形式而定义的，顾名思义，所谓拉链，就是记录历史。记录一个事物从开始，始终到以后状态的所有变动的信息。 3、举例说明上面有一张原始表，表外面的有5条数据，该数据含有orderid、create_time、mod_time三列，别离示意订单编号、订单创立工夫、订单批改工夫。能够看出该数据都是在2020-11-14统计进去的，当天只能统计出前一天的数据。有了一张原始表之后，咱们创立一个拉链表，所来记录之后所有的订单更新以及新建的详情，如下图所示，咱们能够看出拉链表比原始表多了两列，别离为start_time、end_time,别离示意为订单的失效日期以及生效日期。其中一开始失效日期就是创立日期，而生效日期咱们设置为很大的数字，示意只有该条订单不被批改，那就是永恒失效。创立原始表和初步的拉链表后，到15号开始统计14号的数据，又有两条记录，入下图所示。因为咱们每天产生的记录都会依照日期将数据保留在分区表中，所以该分区表最初多了一列示意分区名，其实就是日期。更新原始表如下图所示，咱们发现在14号的记录中，2号订单在14号被批改过一次，同时又创立了一个6号订单 而后咱们须要将14号数据的分区表和初步设计的拉链表开始整合，进行左外连贯(left join),拉链表left join 分区表,连贯后的表外面end_time字段为null的值从新设置为永恒失效日期。 为什么要这样设置呢？（1）首先进行左外链接，后果是只有2号订单可能和拉链表匹配的上，阐明只有2号订单在14号产生了更新，那咱们就将拉链表中的2号订单的end_time改成14号，而其余4个订单的end_time会为null，阐明没有产生更新，那咱们就将其设置为永恒失效日期。（2）咱们还发现在14日的数据中除了批改2号订单，又创立了一个6号订单，所以在拉链表中咱们在最初间接退出一个6号订单。 最终将14号的记录更新后，拉链表如下图所示。 依此类推，咱们开始收集15号的记录数据，导入分区表中，如下图所示。发现2号订单又更新了一次，4号订单也更新了一次，同时又创立了一个7号订单。 更新原始表如下图与所示。将2号和4号订单的end_time改成15号，同时减少一个7号订单信息。 更新拉链表,让新的14号的拉链表和15号的数据分区表做left join,以及之后的整合。 这边有一个很重要的一点:就是2号订单之前曾经批改过一次，end_time工夫为14号，当初15号又批改一次，那之前14号的批改的那条2号订单数据其实曾经生效，新的2号订单end_time为15号；与此同时，4号订单做了第一次的更新，所以将4号订单的end_time改成15号即可；最初又创立一个7号订单。如下图所示 最初一步就是将15号数据中批改2号订单和4号订单的数据从新增加到拉链表中。并且从新设置end_time为永恒失效日期。 最终拉链表展现：咱们能够看出某个订单在哪一天产生了变更，到了变更的那一天，依照订单号查找订单，就能够查出该订单编号之后有没有再变更，该过程就造成了一张拉链表。以2号订单为例：咱们发现，2号订单在13号的时候被创立，14号的时候批改过一次，而后去统计15号的数据，发现2号订单在15号的时候又被批改过一次，最初2号订单是否还会被批改须要收集查看之后每天的数据分区表。

Hive Sql 大全本文根本涵盖了Hive日常应用的所有SQL，因为SQL太多，所以将SQL进行了如下分类：一、DDL语句（数据定义语句）： 对数据库的操作：蕴含创立、批改数据库 对数据表的操作：分为外部表及内部表，分区表和分桶表 二、DQL语句（数据查问语句）： 单表查问、关联查问 hive函数：蕴含聚合函数，条件函数，日期函数，字符串函数等 行转列及列转行：lateral view 与 explode 以及 reflect 窗口函数与剖析函数 其余一些窗口函数 文章首发于公众号【五分钟学大数据】，大数据畛域原创技术号，每周更新大数据技术文及面试真题解析，关注后可支付精心制作大数据面试宝典！ hive的DDL语法对数据库的操作创立数据库:create database if not exists myhive;阐明：hive的表寄存地位模式是由hive-site.xml当中的一个属性指定的 :hive.metastore.warehouse.dir创立数据库并指定hdfs存储地位 :create database myhive2 location '/myhive2';批改数据库: alter database myhive2 set dbproperties('createtime'='20210329');阐明：能够应用alter database 命令来批改数据库的一些属性。然而数据库的元数据信息是不可更改的，包含数据库的名称以及数据库所在的地位查看数据库详细信息查看数据库根本信息hive (myhive)> desc database myhive2;查看数据库更多详细信息hive (myhive)> desc database extended myhive2;删除数据库 删除一个空数据库，如果数据库上面有数据表，那么就会报错drop database myhive2;强制删除数据库，蕴含数据库上面的表一起删除drop database myhive cascade; ### 对数据表的操作#### 对治理表(外部表)的操作:- 建外部表:hive (myhive)> use myhive; -- 应用myhive数据库hive (myhive)> create table stu(id int,name string);hive (myhive)> insert into stu values (1,"zhangsan");hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi"); -- 一次插入多条数据hive (myhive)> select * from stu; ...

hive通过将查问划分成一个或多个MapReduce工作达到并行处理的目标。每个工作都可能具备多个mapper和reducer工作，其中至多有一些是能够并行执行的。 确定最佳的mapper个数和reducer个数取决于多个变量，例如输出的数据量大小以及对这些数据执行的操作类型等。 放弃平衡性是很有必要的，对于Spark/Hadoop这样的大数据系统来讲，数据量大并不可怕,可怕的是数据歪斜，每个节点解决的运算不平衡。 如果有太多的mapper或reducer工作，就会导致启动阶段、调度和运行job过程中产生过多的开销；而如果设置的数量太少，那就有可能没充分利用好集群外在并行性。 mapred.reduce.tasks所提交 Job 的 reduer 的个数，应用 Hadoop Client 的配置。1 hive.mapred.modeMap/Redure 模式，如果设置为 strict，将禁止3中类型的查问：1.分区表的where筛选条件必须含有分区字段；2.对应用了order by语句的查问，必须应用limit语句(order by语句为执行排序会将所有的后果集数据散发到同一个reducer中进行解决，减少limit语句能够避免reducer额定执行很长时间)3.限度笛卡儿积的查问，就是有where语句，而没有on语句。'nonstrict' hive.merge.mapfiles在Map-only的工作完结时合并小文件是否开启合并 Map 端小文件，当Hive输出由很多个小文件组成，因为每个小文件都会启动一个map工作，如果文件过小，会使得map工作启动和初始化的工夫大于逻辑解决的工夫，造成资源节约，甚至OOM。为此，当咱们启动一个工作，发现输出数据量小但工作数量多时，须要留神在Map前端进行输出合并。当然，在咱们向一个表写数据时，也须要留神输入文件大小true hive.merge.mapredfiles是否开启合并 Map/Reduce 小文件，即是否在Map-Reduce的工作完结时合并小文件false hive.exec.parallel是否开启 map/reduce job的并发提交。false hive.limit.optimize.enable当应用LIMIT语句时，其能够对数据源进行抽样，防止执行整个查问语句，而后再返回局部后果但这个性能有个毛病，有可能输出中有用的数据永远不会被解决到。 hive.exec.reducers.bytes.per.reducer每一个 reducer 的均匀负载字节数。1000000000 hive.exec.reducers.max设置reducer个数的下限，能够阻止某个查问耗费过多的reducer资源，对这个属性值大小的设定，一个倡议的计算公式如下：(集群总Reduce槽位个数*1.5) / (执行中查问的均匀个数)1.5倍数是一个教训系数，用于避免未充分利用集群的状况。999 hive.exec.rowoffsethive提供了2种虚构列：一种用于将要进行划分的输出文件名，另一种用于文件中的块内偏移量。当hive产生了非预期的或null的返回后果时，能够通过这些虚构列诊断查问。通过这些“字段”，用户能够查看到哪个文件甚至哪些数据导致呈现问题：SELECTINPUT_FILE_NAME,BLOCK_OFFSET_INSIDE_FILE,ROW_OFFSET_INSIDE_BLOCK,lineFROM hive_textWHERE line LIKE '%hive%' LIMIT 2;true hive.multigroupby.singlemr一个特地的优化，是否将查问中的多个group by操作组装到单个MapReduce工作中。false hive.exec.dynamic.partition是否关上动静分区。false hive.exec.dynamic.partition.mode关上动静分区后，动静分区的模式，有 strict 和 nonstrict 两个值可选，strict 要求至多蕴含一个动态分区列，nonstrict 则无此要求。strict hive.exec.max.dynamic.partitions所容许的最大的动静分区的个数。1000 hive.exec.max.dynamic.partitions.pernode单个 reduce 结点所容许的最大的动静分区的个数。100 hive.exec.default.partition.name默认的动静分区的名称，当动静分区列为''或者null时，应用此名称。'''__HIVE_DEFAULT_PARTITION__' hive.exec.mode.local.auto决定 Hive 是否应该主动地依据输出文件大小，在本地运行（在GateWay运行）true hive.exec.mode.local.auto.inputbytes.max如果 hive.exec.mode.local.auto 为 true，当输出文件大小小于此阈值时能够主动在本地模式运行，默认是 128兆。134217728L hive.exec.mode.local.auto.tasks.max如果 hive.exec.mode.local.auto 为 true，当 Hive Tasks（Hadoop Jobs）小于此阈值时，能够主动在本地模式运行。4 ...

不懂hive中的explain，阐明hive还没入门，学会explain，可能给咱们工作中应用hive带来极大的便当！ 实践本节将介绍 explain 的用法及参数介绍HIVE提供了EXPLAIN命令来展现一个查问的执行打算,这个执行打算对于咱们理解底层原理，hive 调优，排查数据歪斜等很有帮忙 应用语法如下： EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] queryexplain 前面能够跟以下可选参数，留神：这几个可选参数不是 hive 每个版本都反对的 EXTENDED：加上 extended 能够输入无关打算的额定信息。这通常是物理信息，例如文件名。这些额定信息对咱们用途不大CBO：输入由Calcite优化器生成的打算。CBO 从 hive 4.0.0 版本开始反对AST：输入查问的形象语法树。AST 在hive 2.1.0 版本删除了，存在bug，转储AST可能会导致OOM谬误，将在4.0.0版本修复DEPENDENCY：dependency在EXPLAIN语句中应用会产生无关打算中输出的额定信息。它显示了输出的各种属性AUTHORIZATION：显示所有的实体须要被受权执行（如果存在）的查问和受权失败LOCKS：这对于理解零碎将取得哪些锁以运行指定的查问很有用。LOCKS 从 hive 3.2.0 开始反对VECTORIZATION：将详细信息增加到EXPLAIN输入中，以显示为什么未对Map和Reduce进行矢量化。从 Hive 2.3.0 开始反对ANALYZE：用理论的行数正文打算。从 Hive 2.2.0 开始反对在 hive cli 中输出以下命令(hive 2.3.7)： explain select sum(id) from test1;失去后果（请逐行看完，即便看不懂也要每行都看）： STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 depends on stages: Stage-1STAGE PLANS: Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: test1 Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: id (type: int) outputColumnNames: id Statistics: Num rows: 6 Data size: 75 Basic stats: COMPLETE Column stats: NONE Group By Operator aggregations: sum(id) mode: hash outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator sort order: Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE value expressions: _col0 (type: bigint) Reduce Operator Tree: Group By Operator aggregations: sum(VALUE._col0) mode: mergepartial outputColumnNames: _col0 Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE table: input format: org.apache.hadoop.mapred.SequenceFileInputFormat output format: org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe Stage: Stage-0 Fetch Operator limit: -1 Processor Tree: ListSink看完以上内容有什么感触，是不是感觉都看不懂，不要焦急，上面将会具体解说每个参数，置信你学完上面的内容之后再看 explain 的查问后果将熟能生巧。 ...

自定义函数hive内置函数不满足业务能够应用java写自定义函数导入hiveUDF函数：大部分都是， 一进一出UDAF 多进一出 如：聚合函数UDTF 一进多出 如：explodehive调优压缩压缩计划罕用的gzip,bzip2,lzo,snappy，思考的不仅是压缩后体积，而应该再联合解压和压缩的速度 设置不同阶段的输入 输出map，map输入到reduce时，reduce输入时能够压缩配置压缩参数能够到mapred-site.xml,core-site.xml中 io.compression.codecsmapreduce.map.output.compressmapreduce.output存储格局textFile行,sequenceFile行,orc列,parquet列是罕用textFile,orc 只有textFile反对load加载行式存储，列式存储 空间，查问速度 行存储在查找时只须要找到其中一个值其余的数据在相邻地位列存储能够有针对性的查找，而舍弃一些无关数据查找的损耗 压缩显著缩小不必要的io开销数据空间小，读盘更快自在的压缩算法，更灵便 其余优化fetch本地抓去，能不走mr尽量不走能本地mr尽量本地mrhive join优化编写sql时:1.x之前小表在join后面。之后都一样小表和大表join hdfs提供distribute block cache分布式块缓存主动开启mapjoin配置大小表阈值中大型表join大表 先过滤（看是否能够转为小表）再joinbucket join: 分桶的mapjoin计划大表和大表 先过滤再join，缩小map到reduce数据传输量如果大表中的数据有很多空值，须要解决， 因为空值会导致reduce容易呈现数据歪斜 计划一：提前过滤null计划二：随机数替换nullSMB JOIN ：分桶表排序 MAPJOIN计划sql优化列裁剪分区裁剪 操作分区表，倡议携带上分区字段，从而缩小扫描量，晋升效率，同时如果表能过滤，就先过滤再joincombiner解决数据歪斜两次mr，将数据不管男女平均分发给两个reduce防止count(distinct) ，对整个表进行聚合操作翻译后只有一个reduce，若此时执行一个distinct（reduce中执行），而去重操作必须在reduce端才能够，数据量大的话，reduce端会接受大量数据，导致执行效率变慢select count(ip) from (select ip from table group by ip) tmp;执行两个mr前面那个可用于去重，先group by再count尽管会多一个job然而在数据量大的状况下会更优笛卡尔积：hive中多表join不给on(一边关联，一边过滤)，不能用where(先笛卡尔再条件过滤)动静分区sql查问原始表中，必须要将分区字段搁置在整个表后果最初面敞开hive严格语法模式insert into table 分区表2 partition(month) select * from 分区表1;动静歪斜如何调整map数量和reduce数量 放大map数： map之前合并文件调大文件切片增大map数：- combiner默认hive会主动调整reduce数量 hive.exec.reducers.max 默认999hive.exec.reducers.bytes.per.reducer 调小reducer增多order by只有一个reduce,没有group by的聚合只有一个reduce,笛卡尔积也只有一个(关联的时候用了where)并行执行执行hive sql时可能会被翻译为多个mr，并且多个mr之间没有任何关联，那么此时能够运行多个mr并行执行晋升效率set hive.exec.parallel=true; -- 关上工作并行执行执行set hive.exec.parallel.thread.number = 16; -- 最大并行读，默认为8select * from Aunion allselect * from B;严格模式（数据量大的状况下）： 限度（执行效率差的）sql的申请分区表查问，不携带分区字段应用order by 不必limitsql中呈现笛卡尔内置的jvm重用让mr可重复使用资源容器揣测执行关掉，否则失败反复申请资源做雷同的工作，依然有大概率失败

hive配置及外部函数配置全局：hive-site.xml外部命令： ./hive -hiveconf xxx会话设置(仅会话阶段失效)： jdbc:hive2://node3:10000> set mapreduce.job.reduces=3;条件函数if(条件,true return 执行内容, false return 执行内容)case case a when b then c [when d then e]* [else f] end 如果a=b返回c,否则判断是否a=d则返回e,都不对返回fcase when a then b [when c then d]* [else e] end 如果a条件成立返回d,若果c条件成立返回d,都不成立返回e转换函数cast函数 在应用hive进行SMALLINT与INT比拟时会隐式转换SMALLINT为INT，但咱们不能将更大级别的INT转为SMALLINT除非应用cast ； select cast(100 as SMALLINT);任何整数类型都能够隐式地转换成一个范畴更大的类型。TINYINT,SMALLINT,INT,BIGINT,FLOAT和STRING都能够隐式地转换成DOUBLE；是的你没看出，STRING也能够隐式地转换成DOUBLE！然而你要记住，BOOLEAN类型不能转换为其余任何数据类型！order by,sort by,distribute by,cluster by当存在多个分区，distribute by与order by 不能共用，要换成sort by，如果是升序，此时能够间接换成cluster by行列转换函数 concat 连贯简略类型的字符串concat_ws(连贯符号,简单类型输出)collect_set | collect_list set操作在收集过程中，会去掉反复的数据，list不会需要 #数据20 SMITH30 ALLEN30 WARD20 JONES30 MARTIN30 BLAKE10 CLARK20 SCOTT10 KING30 TURNER20 ADAMS30 JAMES20 FORD10 MILLERcreate table emp(deptno int,ename string) row format delimited fields terminated by '\t';select deptno,concat_ws("|",collect_set(ename)) from emp group by deptno;#其中select collect_set(ename) from emp group by deptno;+-----------------------------------------------------+--+| ["CLARK","KING","MILLER"] || ["SMITH","JONES","SCOTT","ADAMS","FORD"] || ["ALLEN","WARD","MARTIN","BLAKE","TURNER","JAMES"] |+-----------------------------------------------------+--+列转行 ...

hive窗口函数/剖析函数在sql中有一类函数叫做聚合函数,例如sum()、avg()、max()等等,这类函数能够将多行数据依照规定汇集为一行,一般来讲汇集后的行数是要少于汇集前的行数的。然而有时咱们想要既显示汇集前的数据,又要显示汇集后的数据,这时咱们便引入了窗口函数。窗口函数又叫OLAP函数/剖析函数，窗口函数兼具分组和排序功能。 窗口函数最重要的关键字是 partition by 和 order by。 具体语法如下：over (partition by xxx order by xxx) sum,avg,min,max 函数筹备数据 建表语句:create table bigdata_t1(cookieid string,createtime string, --day pv int) row format delimited fields terminated by ',';加载数据：load data local inpath '/root/hivedata/bigdata_t1.dat' into table bigdata_t1;cookie1,2018-04-10,1cookie1,2018-04-11,5cookie1,2018-04-12,7cookie1,2018-04-13,3cookie1,2018-04-14,2cookie1,2018-04-15,4cookie1,2018-04-16,4开启智能本地模式SET hive.exec.mode.local.auto=true;SUM函数和窗口函数的配合应用：后果和ORDER BY相干,默认为升序。 #pv1select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1 from bigdata_t1;#pv2select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2from bigdata_t1;#pv3select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as pv3from bigdata_t1;#pv4select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4from bigdata_t1;#pv5select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5from bigdata_t1;#pv6select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6from bigdata_t1;pv1: 分组内从终点到以后行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号pv2: 同pv1pv3: 分组内(cookie1)所有的pv累加pv4: 分组内以后行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号， 13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号pv5: 分组内以后行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21pv6: 分组内以后行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13， 14号=14号+15号+16号=2+4+4=10如果不指定rows between,默认为从终点到以后行; ...

1 CASE WHEN示例 咱们有这样一组数据 data1.txtid name dept project1001 zhangsan sale A1002 lisi sale A1003 wangwu IT B1004 zhaoliu IT A1004 zhaosi sale B 求出 每个部门加入a、b我的项目各有多少人？ 创立表 create table emp_pro(id int,name string,dept string,project string)row format delimited fields terminated by "\t";导入数据 load data local inpath into '/home/v2admin/demo/demo1.txt' into table emp_pro;查看下咱们的表的内容 +-------------+---------------+---------------+------------------+| emp_pro.id | emp_pro.name | emp_pro.dept | emp_pro.project |+-------------+---------------+---------------+------------------+| 1001 | zhangsan | sale | A || 1002 | lisi | sale | A || 1003 | wangwu | IT | B || 1004 | zhaoliu | IT | A || 1004 | zhaosi | sale | B |+-------------+---------------+---------------+------------------+创立表和导入数据没有问题，接下来开始实现咱们的需要 ...

1.内置运算符和函数hive内置一些函数，咱们能够通过show functions 来查看 0: jdbc:hive2://hadoop10:10000> show functions;+------------------------------+| tab_name |+------------------------------+| ! || != |......| udftoshort || udftostring || unbase64 || xpath_number || xpath_short || xpath_string || year || | || ~ |+------------------------------+貌似很多的样子，那么怎么晓得这个函数怎么用呢？ -- 查看函数帮忙这么多函数也不能总是查手册，有没有便捷的形式，给咱们提供一些应用帮忙呢？当然有显示函数帮忙的办法 0: jdbc:hive2://hadoop10:10000> desc function abs;+-------------------------------------------+| tab_name |+-------------------------------------------+| abs(x) - returns the absolute value of x |+-------------------------------------------+貌似太少了，有没有更具体的呢？那看上面的具体显示函数的用法 0: jdbc:hive2://hadoop10:10000> desc function extended abs;+----------------------------------------------------+| tab_name |+----------------------------------------------------+| abs(x) - returns the absolute value of x || Example: || > SELECT abs(0) FROM src LIMIT 1; || 0 || > SELECT abs(-5) FROM src LIMIT 1; || 5 || Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs || Function type:BUILTIN |+----------------------------------------------------+这个就更具体了，还有应用的示例，很是不便。 ...

本文源码：GitHub || GitEE 一、Hive根底简介1、根底形容 Hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，是一个能够对Hadoop中的大规模存储的数据进行查问和剖析存储的组件，Hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查问性能，能将SQL语句转变成MapReduce工作来执行，应用成本低，能够通过相似SQL语句实现疾速MapReduce统计，使MapReduce变得更加简略，而不用开发专门的MapReduce应用程序。hive非常适宜对数据仓库进行统计分析。 2、组成与架构 用户接口：ClientCLI、JDBC拜访Hive、WEBUI浏览器拜访Hive。 元数据：Hive将元数据存储在数据库中，如mysql、derby。Hive中的元数据包含表的名字，表的列和分区以及属性，表的属性（是否为内部表等），表的数据所在目录等。 驱动器：基于解释器、编辑器、优化器实现HQL查问语句从词法剖析、语法分析、编译、优化以及查问打算的生成。 执行器引擎：ExecutionEngine把逻辑执行打算转换成能够运行的物理打算。 Hadoop底层：基于HDFS进行存储，应用MapReduce进行计算，基于Yarn的调度机制。 Hive收到给客户端发送的交互申请，接管到操作指令(SQL)，并将指令翻译成MapReduce，提交到Hadoop中执行，最初将执行后果输入到客户端。 二、Hive环境装置1、筹备安装包 hive-1.2，依赖Hadoop集群环境，地位放在hop01服务上。 2、解压重命名 tar -zxvf apache-hive-1.2.1-bin.tar.gzmv apache-hive-1.2.1-bin/ hive1.23、批改配置文件 创立配置文件 [root@hop01 conf]# pwd/opt/hive1.2/conf[root@hop01 conf]# mv hive-env.sh.template hive-env.sh增加内容 [root@hop01 conf]# vim hive-env.shexport HADOOP_HOME=/opt/hadoop2.7export HIVE_CONF_DIR=/opt/hive1.2/conf配置内容一个是Hadoop门路，和hive配置文件门路。 4、Hadoop配置 首先启动hdfs和yarn；而后在HDFS上创立/tmp和/user/hive/warehouse两个目录并批改赋予权限。 bin/hadoop fs -mkdir /tmpbin/hadoop fs -mkdir -p /user/hive/warehousebin/hadoop fs -chmod g+w /tmpbin/hadoop fs -chmod g+w /user/hive/warehouse5、启动Hive [root@hop01 hive1.2]# bin/hive6、根底操作 查看数据库 hive> show databases ;抉择数据库 hive> use default;查看数据表 hive> show tables;创立数据库应用 ...

本文纲要 UDF 简介Hive作为一个sql查问引擎，自带了一些根本的函数，比方count(计数)，sum(求和)，有时候这些根本函数满足不了咱们的需要，这时候就要写hive hdf(user defined funation)，又叫用户自定义函数。编写Hive UDF的步骤： 增加相干依赖，创立我的项目，这里我用的管理工具是maven，所以我创立的也是一个maven 我的项目(这个时候你须要抉择适合的依赖版本，次要是Hadoop 和 Hive,能够应用hadoop version和hive --version 来别离查看版本)继承org.apache.hadoop.hive.ql.exec.UDF类，实现evaluate办法，而后打包；应用 add办法增加jar 包到分布式缓存，如果jar包是上传到$HIVE_HOME/lib/目录以下，就不须要执行add命令了；通过create temporary function创立长期函数，不加temporary就创立了一个永恒函数；在SQL 中应用你创立的UDF；UDF分词这个是一个比拟常见的场景，例如公司的产品有每天都会产生大量的弹幕或者评论，这个时候咱们可能会想去剖析一下大家最关怀的热点话题是什么，或者是咱们会剖析最近一段时间的网络趋势是什么，然而这里有一个问题就是你的词库建设的问题，因为你应用通用的词库可能不能达到很好的分词成果，尤其有很多网络风行用语它是不在词库里的，还有一个就是停用词的问题了，因为很多时候停用词是没有意义的，所以这里咱们须要将其过滤，而过滤的形式就是通过停用词词表进行过滤。 这个时候咱们的解决方案次要有两种，一种是应用第三方提供的一些词库，还有一种是自建词库，而后有专人去保护，这个也是比拟常见的一种状况。 最初一个就是咱们应用的分词工具，因为目前支流的分词器很多，抉择不同的分词工具可能对咱们的分词后果有很多影响。 分词工具1：Elasticsearch的开源中文分词器 IK Analysis(Star:2471) IK中文分词器在Elasticsearch上的应用。原生IK中文分词是从文件系统中读取词典，es-ik自身可扩大成从不同的源读取词典。目前提供从sqlite3数据库中读取。es-ik-plugin-sqlite3应用办法： 1. 在elasticsearch.yml中设置你的sqlite3词典的地位： ik_analysis_db_path: /opt/ik/dictionary.db 2：开源的java中文分词库 IKAnalyzer(Star:343) IK Analyzer 是一个开源的，基于java语言开发的轻量级的中文分词工具包。从2006年12月推出1.0版开始， IKAnalyzer曾经推出了4个大版本。最后，它是以开源我的项目Luence为利用主体的，联合词典分词和文法剖析算法的中文分词组件。从3.0版本开始，IK倒退为面向Java的专用分词组件，独立于Lucene我的项目 3：java开源中文分词 Ansj(Star:3019) Ansj中文分词 这是一个ictclas的java实现.基本上重写了所有的数据结构和算法.词典是用的开源版的ictclas所提供的.并且进行了局部的人工优化 分词速度达到每秒钟大概200万字左右，准确率能达到96%以上。 目前实现了.中文分词. 中文姓名辨认 . 词性标注、用户自定义词典,关键字提取，主动摘要，关键字标记等性能。 能够利用到自然语言解决等方面,实用于对分词成果要求高的各种我的项目. 4：结巴分词 ElasticSearch 插件(Star:188) elasticsearch官网只提供smartcn这个中文分词插件，成果不是很好，好在国内有medcl大神（国内最早钻研es的人之一）写的两个中文分词插件，一个是ik的，一个是mmseg的 5：Java分布式中文分词组件 - word分词(Star:672) word分词是一个Java实现的分布式的中文分词组件，提供了多种基于词典的分词算法，并利用ngram模型来打消歧义。能精确辨认英文、数字，以及日期、工夫等数量词，能辨认人名、地名、组织机构名等未登录词 6：Java开源中文分词器jcseg(Star:400) Jcseg是什么？ Jcseg是基于mmseg算法的一个轻量级开源中文分词器，同时集成了关键字提取，要害短语提取，要害句子提取和文章主动摘要等性能，并且提供了最新版本的lucene, solr, elasticsearch的分词接口， Jcseg自带了一个 jcseg.properties文件... 7：中文分词库Paoding 庖丁中文分词库是一个应用Java开发的，可联合到Lucene利用中的，为互联网、企业内部网应用的中文搜索引擎分词组件。Paoding填补了国内中文分词方面开源组件的空白，致力于此并希翼成为互联网网站首选的中文分词开源组件。 Paoding中文分词谋求分词的高效率和用户良好体验。 8：中文分词器mmseg4j mmseg4j 用 Chih-Hao Tsai 的 MMSeg 算法(http://technology.chtsai.org/... )实现的中文分词器，并实现 lucene 的 analyzer 和 solr 的TokenizerFactory 以不便在Lucene和Solr中使... ...

在将hive client切换到beeline梳理hive上的udf的时候，发现工程中好多工作的代码都是间接注册办法，没有显示的add jar,起初在hive的conf目录下发现了.hiverc文件中有两行add jar的命令，霎时好奇怎么加载的这个文件，一开始认为是在bin/hive脚本中加载的，起初发现不是，是在client启动的时候CliDriver中记录的，加载的代码如下： public void processInitFiles(CliSessionState ss) throws IOException { boolean saveSilent = ss.getIsSilent(); ss.setIsSilent(true); for (String initFile : ss.initFiles) { int rc = processFile(initFile); if (rc != 0) { System.exit(rc); } } if (ss.initFiles.size() == 0) { if (System.getenv("HIVE_HOME") != null) { String hivercDefault = System.getenv("HIVE_HOME") + File.separator + "bin" + File.separator + HIVERCFILE; if (new File(hivercDefault).exists()) { int rc = processFile(hivercDefault); if (rc != 0) { System.exit(rc); } console.printError("Putting the global hiverc in " + "$HIVE_HOME/bin/.hiverc is deprecated. Please "+ "use $HIVE_CONF_DIR/.hiverc instead."); } } if (System.getenv("HIVE_CONF_DIR") != null) { String hivercDefault = System.getenv("HIVE_CONF_DIR") + File.separator + HIVERCFILE; if (new File(hivercDefault).exists()) { int rc = processFile(hivercDefault); if (rc != 0) { System.exit(rc); } } } if (System.getProperty("user.home") != null) { String hivercUser = System.getProperty("user.home") + File.separator + HIVERCFILE; if (new File(hivercUser).exists()) { int rc = processFile(hivercUser); if (rc != 0) { System.exit(rc); } } } } ss.setIsSilent(saveSilent); }

Prepareplease install JDK-1.8, Hadoop3.1 and get Hive-3.1 source code first JDKJDK-version=1.8export JAVA_HOME=/path/to/jdk/home Hadoophadoop-version=3.1export HADOOP_HOME=/path/to/hadoop/home Hive Source Codehive-version=3.1 IntelliJ IDEAStart Hadoopcd $HADOOP_HOME./sbin/start-all.shHive package installcd /path/to/hive/sourcemvn clean package -DskipTests -Phadoop-2 -PdistHive configonly when the last step finished(hive package install) cd $HIVE_SRC/packaging/target/apache-hive-{version}-bin/apache-hive-{version}-bin/conf# find HADOOP_HOME in hive-env.sh, replace it to our $HADOOP_HOMEcd $HIVE_SRC/packaging/target/apache-hive-{version}-bin/apache-hive-{version}-bin# init metadata database(using derby, if you need mysql, you should install mysql first)bin/schematool -dbType derby -initSchemaHive debugstart hive debug console(listening port 8000 default) ...

Hadoop压缩配置MR反对的压缩编码压缩格局工具算法文件扩展名是否可切分对应的编码/解码器压缩算法原始文件大小压缩文件大小压缩速度DEFAULT无DEFAULT.deflate否org.apache.hadoop.io.compress.DefaultCodec GzipgzipDEFAULT.gz否org.apache.hadoop.io.compress.GzipCodecgzip8.3GB1.8GB17.5MB/sbzip2bzip2bzip2.bz2是org.apache.hadoop.io.compress.BZip2Codecbzip28.3GB1.1GB2.4MB/sLZOlzopLZO.lzo是com.hadoop.compression.lzo.LzopCodecLZO8.3GB2.9GB49.3MB/sSnappy无Snappy.snappy否org.apache.hadoop.io.compress.SnappyCodec 压缩参数配置要在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,org.apache.hadoop.io.compress.Lz4Codec输出压缩Hadoop应用文件扩展名判断是否反对某种编解码器mapreduce.map.output.compressfalsemapper输入这个参数设为true启用压缩mapreduce.map.output.compress.codecorg.apache.hadoop.io.compress.DefaultCodecmapper输入应用LZO、LZ4或snappy编解码器在此阶段压缩数据mapreduce.output.fileoutputformat.compressfalsereducer输入这个参数设为true启用压缩mapreduce.output.fileoutputformat.compress.codecorg.apache.hadoop.io.compress. DefaultCodecreducer输入应用规范工具或者编解码器，如gzip和bzip2mapreduce.output.fileoutputformat.compress.typeRECORDreducer输入SequenceFile输入应用的压缩类型：NONE和BLOCK 开启Map输入阶段压缩开启map输入阶段压缩能够缩小job中map和Reduce task间数据传输量。具体配置如下： 案例实操： 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．执行查问语句hive (default)> select count(ename) name from emp;开启Reduce输入阶段压缩当Hive将输入写入到表中时，输入内容同样能够进行压缩。属性hive.exec.compress.output管制着这个性能。用户可能须要放弃默认设置文件中的默认值false，这样默认的输入就是非压缩的纯文本文件了。用户能够通过在查问语句或执行脚本中设置这个值为true，来开启输入后果压缩性能。 案例实操： 1．开启hive最终输入数据压缩性能 hive (default)>set hive.exec.compress.output=true; 2．开启mapreduce最终输入数据压缩 hive (default)>set mapreduce.output.fileoutputformat.compress=true; 3．设置mapreduce最终数据输入压缩形式 hive (default)> set mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec; 4．设置mapreduce最终数据输入压缩为块压缩 hive (default)> set mapreduce.output.fileoutputformat.compress.type=BLOCK; 5．测试一下输入后果是否是压缩文件 hive (default)> insert overwrite local directory '/opt/module/datas/distribute-result' select * from emp distribute by deptno sort by empno desc; ...

hive运行具体报错WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = root_20201212183932_270ed399-a426-4f98-88d2-3c33c5433379Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number>In order to set a constant number of reducers: set mapreduce.job.reduces=<number>Cannot run job locally: Input Size (= 94395226) is larger than hive.exec.mode.local.auto.inputbytes.max (= 50000000)Starting Job = job_1607739316117_0009, Tracking URL = http://bigdata130:8088/proxy/application_1607739316117_0009/Kill Command = /opt/server/hadoop-2.7.7/bin/hadoop job -kill job_1607739316117_0009Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12020-12-12 18:39:35,991 Stage-1 map = 0%, reduce = 0%2020-12-12 18:39:50,312 Stage-1 map = 100%, reduce = 100%Ended Job = job_1607739316117_0009 with errorsError during job, obtaining debugging information...Examining task ID: task_1607739316117_0009_m_000000 (and more) from job job_1607739316117_0009Task with the most failures(4): -----Task ID: task_1607739316117_0009_m_000000URL: http://0.0.0.0:8088/taskdetails.jsp?jobid=job_1607739316117_0009&tipid=task_1607739316117_0009_m_000000-----Diagnostic Messages for this Task:Error: Java heap spaceFAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTaskMapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 HDFS Read: 0 HDFS Write: 0 FAILTotal MapReduce CPU Time Spent: 0 msec解决方案计划1设置 set io.sort.mb=10; 默认值是100io.sort.mb 的作用 排序所应用的内存数量。 默认值：100M，须要与mapred.child.java.opts相配 默认：-Xmx200m。 不能超过mapred.child.java.opt设置，否则会OOM。 ...

上一篇内容讲了资源参数优化，本篇持续阐明spark driver以及spark shuffle相干的参数调优。 扩大spark driver动静资源分配 在 Facebook，Spark 集群启用了动静资源分配（Dynamic Executor Allocation），以便更好的应用集群资源，而且在 Facebook 外部，Spark 是运行在多租户的集群上，所以这个也是十分适合的。比方典型的配置如下： spark.dynamicAllocation.enabled = truespark.dynamicAllocation.executorIdleTimeout = 2mspark.dynamicAllocation.minExecutors = 1spark.dynamicAllocation.maxExecutors = 2000点击增加图片形容（最多60个字）编辑 多线程事件处理 在 Spark 2.3 版本之前，事件处理是单线程的架构，也就是说，事件队列外面的事件得一个一个解决。如果你的作业很大，并且有很多 tasks，很可能会导致事件处理呈现提早，进一步导致作业性能呈现问题，甚至使以后作业失败。为了解决这个问题，SPARK-18838 这个 ISSUE 引入了多线程事件处理架构，每个事件都有其独自的单线程 executor service 去解决，这样就能够大大减少事件处理延时的问题。另外，因为每类事件都有独自的事件队列，所以会减少 Driver 端的内存占用。 点击增加图片形容（最多60个字）编辑 更好的 Fetch 失败解决 在 Spark 2.3 版本之前，如果 Spark 探测到 fetch failure，那么它会把产生这个 shuffle 文件的 Executor 移除掉。然而如果这个 Executor 所在的机器有很多 Executor，而且是因为这台机器挂掉导致 fetch failure，那么会导致很多的 fetch 重试，这种解决机制很低下。SPARK-19753 这个 ISSUE 使得 Spark 能够把上述场景所有 Executor 的 shuffle 文件移除，也就是不再去重试就晓得 shuffle 文件不可用。 ...

hive函数其余罕用1.NVL: 空字段赋值给值为NULL的数据赋值，它的格局是NVL( string1, replace_with)。它的性能是如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。 2.concat: 字符串拼接能够在参数中传入多个string类型的字符串，一旦有一个参数为null，返回Null！ Tips:最好曾经做好数据荡涤，使得没有null数据3.concat_ws:应用指定的分隔符实现字符串拼接！concat_ws(分隔符,[string | array<string>]+) 4. collect_set:collect_set(列名) ： 将此列的多行记录合并为一个set汇合，去重 5. collect_list:collect_list(列名) ： 将此列的多行记录合并为一个set汇合，不去重 6. explode:explode(列名) 参数只能是array或map!将array类型参数转为1列N行将map类型参数转为2列N行列转行： 1列1行 转为 1列N行 留神:explode函数属于UDTF，UDTF在应用时，不能和其余表达式一起呈现在select子句后！只能独自呈现在select子句后！案例：movie_info.movie | movie_info.category 《疑犯追踪》 | ["悬疑","动作","科幻","剧情"]冀望后果：《疑犯追踪》 悬疑《疑犯追踪》 动作《疑犯追踪》 科幻《疑犯追踪》 剧情《Lie to me》 悬疑-------------不容许----------select movie,explode(category) from movie_info--------不符合要求--------------select movie,col1from movie_infojoin (select explode(category) col1from movie_info) tmp解决：①先explode②须要将炸裂后的1列N行，在逻辑上仍然视作1列1行，理论是1列N行，和movie进行笛卡尔集 这个操作在hive中称为侧写(lateral VIEW) Lateral view explode() 长期表名 as 长期列名 select movie,col1from movie_info Lateral view explode(category) tmp1 as col1Tips：select前面只能写分组后的字段和汇集函数！汇集函数： 多进一出

企业级调优Fetch抓取Fetch抓取是指，Hive中对某些状况的查问能够不用应用MapReduce计算。例如：SELECT * FROM employees;在这种状况下，Hive能够简略地读取employee对应的存储目录下的文件，而后输入查问后果到控制台。在hive-default.xml.template文件中hive.fetch.task.conversion默认是more，老版本hive默认是minimal，该属性批改为more当前，在全局查找、字段查找、limit查找等都不走mapreduce。 <property> <name>hive.fetch.task.conversion</name> <value>more</value> <description> Expects one of [none, minimal, more]. Some select queries can be converted to single FETCH task minimizing latency. Currently the query should be single sourced not having any subquery and should not have any aggregations or distincts (which incurs RS), lateral views and joins. 0. none : disable hive.fetch.task.conversion 1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only 2. more : SELECT, FILTER, LIMIT only (support TABLESAMPLE and virtual columns) </description> </property>案例实操：1.把hive.fetch.task.conversion设置成none，而后执行查问语句，都会执行mapreduce程序。 ...

一、数据库1 创立库语句CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; 2查询数据库2.1 显示数据库show databases; 2.2 过滤显示查问的数据库show databases like 'd*'; 2.3 显示数据库信息desc database ods;      2.4 显示数据库详细信息，extendeddesc database extended ods; 2.5 切换数据库详细信息，useuse dwd; 3 批改数据库3.1 批改数据库属性，AlterALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …); 3.2 删除数据库，drop3.2.1 删除空的数据库 drop database dwd; 3.2.2删除非空的数据库，应用cascade强制删除。 drop database dwd cascade; 二、表1 建表语句CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [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] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] [AS select_statement] ...

Hive DML1、插入数据1.1. Loading files into tables--加载本地数据到hive表 load data local inpath '/root/data/data' into table psn;--(/root/data/data指的是本地 linux目录)--加载hdfs数据文件到hive表 load data inpath '/data/data' into table psn;--(/data/data指的是hdfs的目录)留神：1、load操作不会对数据做任何的转换批改操作2、从本地linux load数据文件是复制文件的过程3、从hdfs load数据文件是挪动文件的过程4、load操作也反对向分区表中load数据，只不过须要增加分区列的值 1.2. Insertinsert形式运行MR程序，通过程序将数据输入到表目录！在某些场景，必须应用insert形式来导入数据： 向分桶表插入数据如果指定表中的数据，不是以纯文本模式存储，须要应用insert形式导入语法： insert into|overwrite table 表名 select xxx | values(),(),() insert into: 向表中追加新的数据insert overwrite： 先清空表中所有的数据，再向表中增加新的数据 非凡状况： 多插入模式(从一张源表查问，向多个指标表插入) from 源表 insert xxxx 指标表 select xxx insert xxxx 指标表 select xxx insert xxxx 指标表 select xxx举例： from deptpart2 insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc 从查问语句中获取数据插入某张表语法： Standard syntax: 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 extension (multiple inserts): FROM from_statement INSERT 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] ...; FROM from_statement INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 [INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] [INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...; Hive extension (dynamic partition inserts): 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;--留神：这种形式插入数据的时候须要事后创立好后果表--从表中查问数据插入后果表 ...

筹备环境： hadoop根底环境（3.1.3）  Hive版本 3.1.2 下载地址：https://hive.apache.org/downloads.html Hive3.X版本反对Hadoop3.X,Hive2.0版本反对Hadoop2.X 1、上传安装包，解压文件 tar -zxvf   对应包名 2、 创立软连贯 ln -s  apache-hive-1.2.2-bin hive 3、批改环境变量 vim ~/.bash_profile 批改实现使环境变量立刻失效 source ~/.bash_profile 4、批改配置文件名 mv hive-env.sh.template hive-env.sh 5、进入该文件，配置hadoop门路与hive_conf门路 6、在hdfs上创立相干目录，附加相干权限 hdfs dfs -mkdir -p  /user/hive/warehouse hdfs dfs -mkdir /tmp hdfs dfs -chmod g+w /tmp  /user/hive/warehouse 7、启动hive（在此之前须要保障hdfs、yarn已启动） 这里须要留神 1）如果hadooplib中的相干jar包与hive中的jar包抵触会导致启动失败：相干问题见链接 http://www.bubuko.com/infodetail-3286965.html 2)执行查看库hql语句，一开始没有执行初始化库操作导致启动失败，hive存储元数据的默认库为derby。 https://blog.csdn.net/lz6363/article/details/95805446 初始化相干指令 schematool -dbType derby -initSchema 至此hive初步环境搭建实现。 8、更改hive寄存元数据数据库为mysql，这里首先介绍mysql数据库的装置步骤 1）首先下载下载并装置MySQL官网的 Yum Repository wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm ...

1 分区表[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 1.1 分区表在建表时，指定了PARTITIONED BY ，这个表称为分区表PARTITIONED BY 以什么分区 1.2 分区概念MR： 在MapTask输入key-value时，为每个key-value计算一个区号，同一个分区的数据，会被同一个reduceTask解决这个分区的数据，最终生成一个后果文件！通过分区，将MapTask输入的key-value通过reduce后，扩散到多个不同的后果文件中！Hive: 将表中的数据，扩散到表目录下的多个子目录(分区目录)中 1.3 分区意义分区的目标是为了就数据，扩散到多个子目录中，在执行查问时，能够只抉择查问某些子目录中的数据，放慢查问效率！只有分区表才有子目录(分区目录)分区目录的名称由两局部确定： 分区列列名=分区列列值将输出导入到指定的分区之后，数据会附加上分区列的信息！分区的最终目标是在查问时，应用分区列进行过滤！2 分区表操作2.1 创立分区表多级分区表，有多个分区字段create external table if not exists learnwork.deptpart2(deptno int,dname string,loc int)PARTITIONED BY(area string,province string)row format delimited fields terminated by '\t'; create external table if not exists learnwork.deptpart3(deptno int,dname string,loc int)PARTITIONED BY(area string)row format delimited fields terminated by '\t'location 'hdfs://hadoop101:9000/deptpart3'; 2.2 分区的查问show partitions 表名 2.3 创立分区alter table 表名 add partition(分区字段名=分区字段值) ; ...

阐明doris 未应用 物化视图，应用的是聚合类型动静分区表。tidb 未应用分区表数据量7800w步骤doris 建表语句：CREATE TABLE `biz_cms_flow_v4` ( `dt` date NULL COMMENT "", `dn` int(11) NOT NULL COMMENT "站点1-acc 2-jianshe 3-med", `addtime` int(11) NOT NULL COMMENT "增加工夫", `adduser` varchar(255) NULL COMMENT "用户名", `innercode` varchar(255) NULL COMMENT "栏目编码", `url` varchar(255) NULL COMMENT "地址", `title` varchar(32766) NOT NULL COMMENT "题目", `original` int(11) NOT NULL COMMENT "是否原创 0 非原创 1 原创", `timestamp` DATETIME, `pv` bigint(20) SUM NULL DEFAULT "0" COMMENT "pv", `uv` bigint(20) SUM NULL DEFAULT "0" COMMENT "访客") ENGINE=OLAPAGGREGATE KEY(`dt`, `dn`, `addtime`, `adduser`, `innercode`, `url`, `title`, `original` ,timestamp)COMMENT "OLAP"PARTITION BY RANGE(`dt`) (PARTITION p20200620 VALUES [('2020-06-20'), ('2020-06-21')),PARTITION p20200621 VALUES [('2020-06-21'), ('2020-06-22')),PARTITION p20200622 VALUES [('2020-06-22'), ('2020-06-23')),PARTITION p20200623 VALUES [('2020-06-23'), ('2020-06-24')),PARTITION p20200624 VALUES [('2020-06-24'), ('2020-06-25')),PARTITION p20200625 VALUES [('2020-06-25'), ('2020-06-26')),PARTITION p20200626 VALUES [('2020-06-26'), ('2020-06-27')),PARTITION p20200627 VALUES [('2020-06-27'), ('2020-06-28')),PARTITION p20200628 VALUES [('2020-06-28'), ('2020-06-29')),PARTITION p20200629 VALUES [('2020-06-29'), ('2020-06-30')),PARTITION p20200630 VALUES [('2020-06-30'), ('2020-07-01')),PARTITION p20200701 VALUES [('2020-07-01'), ('2020-07-02')),PARTITION p20200702 VALUES [('2020-07-02'), ('2020-07-03')),PARTITION p20200703 VALUES [('2020-07-03'), ('2020-07-04')),PARTITION p20200704 VALUES [('2020-07-04'), ('2020-07-05')),PARTITION p20200705 VALUES [('2020-07-05'), ('2020-07-06')),PARTITION p20200706 VALUES [('2020-07-06'), ('2020-07-07')),PARTITION p20200707 VALUES [('2020-07-07'), ('2020-07-08')),PARTITION p20200708 VALUES [('2020-07-08'), ('2020-07-09')),PARTITION p20200709 VALUES [('2020-07-09'), ('2020-07-10')),PARTITION p20200710 VALUES [('2020-07-10'), ('2020-07-11')),PARTITION p20200711 VALUES [('2020-07-11'), ('2020-07-12')),PARTITION p20200712 VALUES [('2020-07-12'), ('2020-07-13')),PARTITION p20200713 VALUES [('2020-07-13'), ('2020-07-14')),PARTITION p20200714 VALUES [('2020-07-14'), ('2020-07-15')),PARTITION p20200715 VALUES [('2020-07-15'), ('2020-07-16')),PARTITION p20200716 VALUES [('2020-07-16'), ('2020-07-17')),PARTITION p20200717 VALUES [('2020-07-17'), ('2020-07-18')),PARTITION p20200718 VALUES [('2020-07-18'), ('2020-07-19')),PARTITION p20200719 VALUES [('2020-07-19'), ('2020-07-20')),PARTITION p20200720 VALUES [('2020-07-20'), ('2020-07-21')),PARTITION p20200721 VALUES [('2020-07-21'), ('2020-07-22')),PARTITION p20200722 VALUES [('2020-07-22'), ('2020-07-23')),PARTITION p20200723 VALUES [('2020-07-23'), ('2020-07-24')),PARTITION p20200724 VALUES [('2020-07-24'), ('2020-07-25')),PARTITION p20200725 VALUES [('2020-07-25'), ('2020-07-26')),PARTITION p20200726 VALUES [('2020-07-26'), ('2020-07-27')),PARTITION p20200727 VALUES [('2020-07-27'), ('2020-07-28')),PARTITION p20200728 VALUES [('2020-07-28'), ('2020-07-29')),PARTITION p20200729 VALUES [('2020-07-29'), ('2020-07-30')),PARTITION p20200730 VALUES [('2020-07-30'), ('2020-07-31')),PARTITION p20200731 VALUES [('2020-07-31'), ('2020-08-01')),PARTITION p20200801 VALUES [('2020-08-01'), ('2020-08-02')),PARTITION p20200802 VALUES [('2020-08-02'), ('2020-08-03')),PARTITION p20200803 VALUES [('2020-08-03'), ('2020-08-04')),PARTITION p20200804 VALUES [('2020-08-04'), ('2020-08-05')),PARTITION p20200805 VALUES [('2020-08-05'), ('2020-08-06')),PARTITION p20200806 VALUES [('2020-08-06'), ('2020-08-07')),PARTITION p20200807 VALUES [('2020-08-07'), ('2020-08-08')),PARTITION p20200808 VALUES [('2020-08-08'), ('2020-08-09')),PARTITION p20200809 VALUES [('2020-08-09'), ('2020-08-10')),PARTITION p20200810 VALUES [('2020-08-10'), ('2020-08-11')),PARTITION p20200811 VALUES [('2020-08-11'), ('2020-08-12')),PARTITION p20200812 VALUES [('2020-08-12'), ('2020-08-13')),PARTITION p20200813 VALUES [('2020-08-13'), ('2020-08-14')),PARTITION p20200814 VALUES [('2020-08-14'), ('2020-08-15')),PARTITION p20200815 VALUES [('2020-08-15'), ('2020-08-16')),PARTITION p20200816 VALUES [('2020-08-16'), ('2020-08-17')),PARTITION p20200817 VALUES [('2020-08-17'), ('2020-08-18')),PARTITION p20200818 VALUES [('2020-08-18'), ('2020-08-19')),PARTITION p20200819 VALUES [('2020-08-19'), ('2020-08-20')),PARTITION p20200820 VALUES [('2020-08-20'), ('2020-08-21')),PARTITION p20200821 VALUES [('2020-08-21'), ('2020-08-22')),PARTITION p20200822 VALUES [('2020-08-22'), ('2020-08-23')),PARTITION p20200823 VALUES [('2020-08-23'), ('2020-08-24')),PARTITION p20200824 VALUES [('2020-08-24'), ('2020-08-25')),PARTITION p20200825 VALUES [('2020-08-25'), ('2020-08-26')),PARTITION p20200826 VALUES [('2020-08-26'), ('2020-08-27')),PARTITION p20200827 VALUES [('2020-08-27'), ('2020-08-28')),PARTITION p20200828 VALUES [('2020-08-28'), ('2020-08-29')),PARTITION p20200829 VALUES [('2020-08-29'), ('2020-08-30')),PARTITION p20200830 VALUES [('2020-08-30'), ('2020-08-31')),PARTITION p20200831 VALUES [('2020-08-31'), ('2020-09-01')),PARTITION p20200901 VALUES [('2020-09-01'), ('2020-09-02')),PARTITION p20200902 VALUES [('2020-09-02'), ('2020-09-03')),PARTITION p20200903 VALUES [('2020-09-03'), ('2020-09-04')),PARTITION p20200904 VALUES [('2020-09-04'), ('2020-09-05')),PARTITION p20200905 VALUES [('2020-09-05'), ('2020-09-06')),PARTITION p20200906 VALUES [('2020-09-06'), ('2020-09-07')),PARTITION p20200907 VALUES [('2020-09-07'), ('2020-09-08')),PARTITION p20200908 VALUES [('2020-09-08'), ('2020-09-09')),PARTITION p20200909 VALUES [('2020-09-09'), ('2020-09-10')),PARTITION p20200910 VALUES [('2020-09-10'), ('2020-09-11')),PARTITION p20200911 VALUES [('2020-09-11'), ('2020-09-12')),PARTITION p20200912 VALUES [('2020-09-12'), ('2020-09-13')),PARTITION p20200913 VALUES [('2020-09-13'), ('2020-09-14')),PARTITION p20200914 VALUES [('2020-09-14'), ('2020-09-15')),PARTITION p20200915 VALUES [('2020-09-15'), ('2020-09-16')),PARTITION p20200916 VALUES [('2020-09-16'), ('2020-09-17')),PARTITION p20200917 VALUES [('2020-09-17'), ('2020-09-18')),PARTITION p20200918 VALUES [('2020-09-18'), ('2020-09-19')),PARTITION p20200919 VALUES [('2020-09-19'), ('2020-09-20')),PARTITION p20200920 VALUES [('2020-09-20'), ('2020-09-21')),PARTITION p20200921 VALUES [('2020-09-21'), ('2020-09-22')),PARTITION p20200922 VALUES [('2020-09-22'), ('2020-09-23')),PARTITION p20200923 VALUES [('2020-09-23'), ('2020-09-24')),PARTITION p20200924 VALUES [('2020-09-24'), ('2020-09-25')),PARTITION p20200925 VALUES [('2020-09-25'), ('2020-09-26')),PARTITION p20200926 VALUES [('2020-09-26'), ('2020-09-27')),PARTITION p20200927 VALUES [('2020-09-27'), ('2020-09-28')),PARTITION p20200928 VALUES [('2020-09-28'), ('2020-09-29')),PARTITION p20200929 VALUES [('2020-09-29'), ('2020-09-30')),PARTITION p20200930 VALUES [('2020-09-30'), ('2020-10-01')),PARTITION p20201001 VALUES [('2020-10-01'), ('2020-10-02')),PARTITION p20201002 VALUES [('2020-10-02'), ('2020-10-03')),PARTITION p20201003 VALUES [('2020-10-03'), ('2020-10-04')),PARTITION p20201004 VALUES [('2020-10-04'), ('2020-10-05')),PARTITION p20201005 VALUES [('2020-10-05'), ('2020-10-06')),PARTITION p20201006 VALUES [('2020-10-06'), ('2020-10-07')),PARTITION p20201007 VALUES [('2020-10-07'), ('2020-10-08')),PARTITION p20201008 VALUES [('2020-10-08'), ('2020-10-09')),PARTITION p20201009 VALUES [('2020-10-09'), ('2020-10-10')),PARTITION p20201010 VALUES [('2020-10-10'), ('2020-10-11')),PARTITION p20201011 VALUES [('2020-10-11'), ('2020-10-12')),PARTITION p20201012 VALUES [('2020-10-12'), ('2020-10-13')),PARTITION p20201013 VALUES [('2020-10-13'), ('2020-10-14')),PARTITION p20201014 VALUES [('2020-10-14'), ('2020-10-15')),PARTITION p20201015 VALUES [('2020-10-15'), ('2020-10-16')),PARTITION p20201016 VALUES [('2020-10-16'), ('2020-10-17')),PARTITION p20201017 VALUES [('2020-10-17'), ('2020-10-18')),PARTITION p20201018 VALUES [('2020-10-18'), ('2020-10-19')),PARTITION p20201019 VALUES [('2020-10-19'), ('2020-10-20')),PARTITION p20201020 VALUES [('2020-10-20'), ('2020-10-21')),PARTITION p20201021 VALUES [('2020-10-21'), ('2020-10-22')),PARTITION p20201022 VALUES [('2020-10-22'), ('2020-10-23')),PARTITION p20201023 VALUES [('2020-10-23'), ('2020-10-24')),PARTITION p20201024 VALUES [('2020-10-24'), ('2020-10-25')),PARTITION p20201025 VALUES [('2020-10-25'), ('2020-10-26')),PARTITION p20201026 VALUES [('2020-10-26'), ('2020-10-27')),PARTITION p20201027 VALUES [('2020-10-27'), ('2020-10-28')),PARTITION p20201028 VALUES [('2020-10-28'), ('2020-10-29')),PARTITION p20201029 VALUES [('2020-10-29'), ('2020-10-30')),PARTITION p20201030 VALUES [('2020-10-30'), ('2020-10-31')),PARTITION p20201031 VALUES [('2020-10-31'), ('2020-11-01')),PARTITION p20201101 VALUES [('2020-11-01'), ('2020-11-02')),PARTITION p20201102 VALUES [('2020-11-02'), ('2020-11-03')),PARTITION p20201103 VALUES [('2020-11-03'), ('2020-11-04')),PARTITION p20201104 VALUES [('2020-11-04'), ('2020-11-05')),PARTITION p20201105 VALUES [('2020-11-05'), ('2020-11-06')),PARTITION p20201106 VALUES [('2020-11-06'), ('2020-11-07')),PARTITION p20201107 VALUES [('2020-11-07'), ('2020-11-08')),PARTITION p20201108 VALUES [('2020-11-08'), ('2020-11-09')),PARTITION p20201109 VALUES [('2020-11-09'), ('2020-11-10')),PARTITION p20201110 VALUES [('2020-11-10'), ('2020-11-11')),PARTITION p20201111 VALUES [('2020-11-11'), ('2020-11-12')))DISTRIBUTED BY HASH(`addtime`) BUCKETS 10PROPERTIES ("replication_num" = "3","dynamic_partition.enable" = "true","dynamic_partition.time_unit" = "DAY","dynamic_partition.end" = "3","dynamic_partition.prefix" = "p","dynamic_partition.replication_num" = "3","dynamic_partition.buckets" = "10"); hive 长期表CREATE EXTERNAL TABLE `tmp_v1.tmp_zdh_test_biz_cms_flow_tmp_v20`( `dt` string, `dn` BIGINT, `addtime` BIGINT , `adduser` string, `innercode` string, `url` string, `title` string, `original` BIGINT, `timestamp` TIMESTAMP, `pv` BIGINT, `uv` BIGINT )ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' ;荡涤 hive 表adm_v1.adm_cms_flow_daily 数据，导入tmp_v1.tmp_zdh_test_biz_cms_flow_tmp_v20。insert overwrite table tmp_v1.tmp_zdh_test_biz_cms_flow_tmp_v20select from_unixtime(unix_timestamp(dt,'yyyyMMdd'),'yyyy-MM-dd') as dt,case dn when 'acc' then 1 when 'jianshe' then 2 when 'med' then 3 else 4 end as dn1,addtime,adduser,innercode,url,title,original,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') ,cast(pv as bigint) as pv1,cast(uv as bigint) as uv1 from adm_v1.adm_cms_flow_daily;验证数据量 ...

内存溢出Client端内存溢出Client端产生内存溢出执行上面的看是很简略的一条sql语句：hive> select count(1) from test_tb_1_1; Query ID = hdfs_20180802104347_615d0836-cf41-475d-9bec-c62a1f408b21Total jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes):set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers:set hive.exec.reducers.max=<number>In order to set a constant number of reducers:set mapreduce.job.reduces=<number>FAILED: Execution Error, return code -101 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask. Java heap space报错起因：该语句会进行全表全分区扫描，如果该表的分区数很多，数据量很大，可能就会呈现客户端内存不足的报错。注：客户端报内存溢出的判断根据，通过查看客户端输入来的日志中，还没有打印出作业的application id 信息（信息款式如下)就报内存溢出的异样了，在ResourceManager上也查看不到该作业的任何信息。 ...

明天在应用Hive On Tez的时候发现一个问题，那就是咱们无奈设置Tez运行在Yarn下面的Job Name。依照设置MR失常的步骤来设置Job Name发现也是有效的。 这让我很蛊惑，于是想了一下是不是因为TezSession的时候曾经写死了导致的，然通过将Hive的源码下载下来（我用的是Hive-3.1.2的源码，下载地址：https://mirror.bit.edu.cn/apa...） 将源码下载当前，依照MR的源码HiveSession来推理，想的应该是TezSession，于是去查找这个类，现实总是很好的，然而事实往往相同，于是又想到了有一个SessionState的类，于是又衍生一个想法是不是也有一个叫TesSessionState的类呢？而后查找源码发现还真的有这个类，那么咱们初步定位到了在这个类中，而后开始浏览该类ql/src/java/org/apache/hadoop/hive/ql/exec/tez/TezSessionState.java的源码发现： final TezClient session = TezClient.newBuilder("HIVE-" + sessionId, tezConfig) .setIsSession(true).setLocalResources(commonLocalResources) .setCredentials(llapCredentials).setServicePluginDescriptor(servicePluginsDescriptor) .build();在源码中写死了Job Name是"HIVE-" + sessionId也就是咱们在页面中看到的HIVE-随机生成的UUID，咱们自定义Job Name也很简略，批改源码如下： String tezJobName = tezConfig.get("tez.job.name", "HIVE-" + sessionId);LOG.info("Tez Job Name " + tezJobName);final TezClient session = TezClient.newBuilder(tezJobName, tezConfig) .setIsSession(true).setLocalResources(commonLocalResources) .setCredentials(llapCredentials).setServicePluginDescriptor(servicePluginsDescriptor) .build();批改源码后执行以下maven命令进行源码从新编译 mvn clean package -DskipTests -U -X留神：如果批改了maven下载仓库为国内镜像的话，肯定要将maven地方仓库加到pom.xml文件中（根目录的）否则会呈现下载jar问题将编译后的ql/target/hive-exec-3.1.2.jar替换原有的Hive中的lib依赖即可！！！ 此时咱们应用set tez.job.name=tezAppName;即可，在Yarn界面中即可看到Application Name曾经改成了咱们自定义的名称。 应用hive --hiveconf tez.job.name=tezAppName

hive是基于Hadoop的一个数据仓库工具，用来进行数据的ETL，这是一种能够存储、查问和剖析存储在Hadoop中的大规模数据的机制。hive能将结构化的数据文件映射为一张数据库表，并提供SQL查问性能。Hive SQL是一品种SQL语言，与关系型数据库所反对的SQL语法存在渺小的差别。本文比照MySQL和Hive所反对的SQL语法，发现雷同的SQL语句在Hive和MySQL中输入后果的会有所不同。 两个整数除除法是SQL引擎难以解释的算术运算。如果将两个整数相加，相减或相乘，则始终会失去一个整数。值得注意的是，如果将两个整数相除，不同的SQL查问引擎输入的后果不尽相同。在Hive和MySQL中，运算两个整数相除，输入的后果都是decimal类型。 -- Hive中查问select 10/3 -- 输入：3.3333333333333335-- 在MySQL中查问select 10/3 -- 输入：3.3333如果应用上面的形式，则会返回整形类型 -- Hive中查问select 10 div 3 -- 输入：3-- 在MySQL中查问select 10 div 3 -- 输入：3辨别大小写当咱们比拟两个字符串时，在不同的SQL引擎会产生不同的后果。须要留神的是，在字符串比拟中，Apache Hive是辨别大小写，看上面的例子。 -- Hive中查问select 'Bigdata' = 'bigdata' -- 输入false-- 在MySQL中查问select 'Bigdata' = 'bigdata' -- 输入1能够看出：雷同的SQL语句，如果应用MySQL，则同一查问将返回1，因为在进行字符串比拟时MySQL不辨别大小写。这意味着只有它们具备雷同的字母，MySQL便会将两个字符串解释为雷同的字符串。 咱们再来看一下另外一个景象，当咱们把表名写成大写的，会呈现什么景象呢？ 这取决于所应用的SQL引擎，在援用数据库中的表时须要留神辨别大小写。如果应用Hive，则在援用表时无需放心大小写，因为它们始终将字母转换为小写字母。然而在MySQL中会报1146 - Table 'XX' doesn't exist的谬误。 -- 假如Hive、MySQL中有一张test表-- 在Hive中查问select * from Test -- 失常输入后果-- 在MySQL中查问select * from Test -- 报错：1146 - Table 'Test' doesn't exist在GROUP BY中应用别名假如有如下查问： ...

Hive中变量和属性命名空间： hivevar：用户自定义变量（Hive0.8及之后的版本）。可读可写。hiveconf：Hive相干的配置属性。可读可写。system：Java定义的配置属性。可读可写。env：Shell环境定义的环境变量。只可读。--defind key=value 等价于 --hivevar key=valuehivevar前缀可选，但system:或env:必须有。 在 CLI 中，应用 set 命令显示或批改变量值。 set env:HOME;// 设置变量set hivevar:foo=bar;// 获取变量set hivevar:foo; 参数： -v： set; // 打印 hivevar、hiveconf、system、env中所有变量set -v; // 还打印 Hadoop 中所定义的所有属性-e:一次应用命令，多个查问用分号分隔。在Hive Shell外执行。hive -e "select * from table limit 3 "; -S:在Hive Shell外执行。开启静默模式。在输入后果中去掉“OK”、“Time taken”等行和其余无关紧要的输入信息。hive -S -e "select * from table limit 3 " > /tmp/query -f:执行指定文件中一个或多个查问。在Hive Shell外执行:hive -f /path/to/file/withqueries.hql;在Hive Shell内执行:source /path/to/file/withqueries.hql; -i:指定一个文件，当 CLI 启动时，在命令提示符呈现前会先执行这个文件。 !:在Hive Shell内执行。在命令前加上 ! 并且以分号结尾能够应用 bash shell 命令。Hive CLI 不能应用交互式命令，不反对管道性能和主动补全性能。! /bin/echo "ha ha"; ...

元数据管理是数据仓库的外围，它不仅定义了数据仓库有什么，还指明了数据仓库中数据的内容和地位，刻画了数据的提取和转换规则，存储了与数据仓库主题无关的各种商业信息。本文次要介绍Hive Hook和MetaStore Listener，应用这些性能能够进行主动的元数据管理。通过本文你能够理解到： 元数据管理Hive Hooks 和 Metastore ListenersHive Hooks根本应用Metastore Listeners根本应用元数据管理元数据定义依照传统的定义，元数据（ Metadata ）是对于数据的数据。元数据买通了源数据、数据仓库、数据利用，记录了数据从产生到生产的全过程。元数据次要记录数据仓库中模型的定义、各层级间的映射关系、监控数据仓库的数据状态及ETL 的工作运行状态。在数据仓库零碎中，元数据能够帮忙数据仓库管理员和开发人员十分不便地找到他们所关怀的数据，用于领导其进行数据管理和开发工作，进步工作效率。将元数据按用处的不同分为两类：技术元数据（ Technical Metadata)和业务元数据（ Business Metadata ）。技术元数据是存储对于数据仓库零碎技术细节的数据，是用于开发和治理数据仓库应用的数据。 元数据分类技术元数据分布式计算零碎存储元数据如Hive表、列、分区等信息。记录了表的表名。分区信息、责任人信息、文件大小、表类型，以及列的字段名、字段类型、字段备注、是否是分区字段等信息。 分布式计算零碎运行元数据相似于Hive 的Job 日志，包含作业类型、实例名称、输入输出、SQL 、运行参数、执行工夫等。 任务调度元数据工作的依赖类型、依赖关系等，以及不同类型调度工作的运行日志等。 业务元数据业务元数据从业务角度形容了数据仓库中的数据，它提供了介于使用者和理论零碎之间的语义层，使得不懂计算机技术的业务人员也可能“ 读懂”数据仓库中的数据。常见的业务元数据有：如维度及属性、业务过程、指标等的规范化定义，用于更好地治理和应用数据；数据利用元数据，如数据报表、数据产品等的配置和运行元数据。 元数据利用数据的真正价值在于数据驱动决策，通过数据领导经营。通过数据驱动的办法，咱们可能判断趋势，从而开展无效口头，帮忙本人发现问题，推动翻新或解决方案的产生。这就是数据化经营。同样，对于元数据，能够用于领导数据相干人员进行日常工作，实现数据化“经营”。比方对于数据使用者，能够通过元数据让其疾速找到所须要的数据；对于ETL 工程师，能够通过元数据领导其进行模型设计、工作优化和工作下线等各种日常ETL 工作；对于运维工程师，能够通过元数据领导其进行整个集群的存储、计算和系统优化等运维工作。 Hive Hooks 和 Metastore ListenersHive Hooks对于数据治理和元数据管理框架，业界有许多开源的零碎，比方Apache Atlas，这些开源的软件能够在简单的场景下满足元数据管理的需要。其实Apache Atlas对于Hive的元数据管理，应用的是Hive的Hooks。须要进行如下配置： <property> <name>hive.exec.post.hooks</name> <value>org.apache.atlas.hive.hook.HiveHook<value/></property>通过Hook监听Hive的各种事件，比方创立表，批改表等，而后依照特定的格局把收集的数据推送到Kafka，最初生产元数据并存储。 Hive Hooks分类那么，到底什么是Hooks呢？ Hooks 是一种事件和音讯机制， 能够将事件绑定在外部 Hive 的执行流程中，而无需从新编译 Hive。Hook 提供了扩大和继承内部组件的形式。依据不同的 Hook 类型，能够在不同的阶段运行。对于Hooks的类型，次要分为以下几种： hive.exec.pre.hooks从名称能够看出，在执行引擎执行查问之前被调用。这个须要在 Hive 对查问打算进行过优化之后才能够应用。应用该Hooks须要实现接口：org.apache.hadoop.hive.ql.hooks.ExecuteWithHookContext，具体在hive-site.xml中的配置如下： <property> <name>hive.exec.pre.hooks</name> <value>实现类的全限定名<value/></property>hive.exec.post.hooks在执行打算执行完结后果返回给用户之前被调用。应用时须要实现接口：org.apache.hadoop.hive.ql.hooks.ExecuteWithHookContext，具体在hive-site.xml中的配置如下： <property> <name>hive.exec.post.hooks</name> <value>实现类的全限定名<value/></property>hive.exec.failure.hooks在执行打算失败之后被调用。应用时须要实现接口：org.apache.hadoop.hive.ql.hooks.ExecuteWithHookContext,具体在hive-site.xml中的配置如下： <property> <name>hive.exec.failure.hooks</name> <value>实现类的全限定名<value/></property>hive.metastore.init.hooksHMSHandler初始化是被调用。应用时须要实现接口：org.apache.hadoop.hive.metastore.MetaStoreInitListener，具体在hive-site.xml中的配置如下： <property> <name>hive.metastore.init.hooks</name> <value>实现类的全限定名<value/></property>hive.exec.driver.run.hooks在Driver.run开始或完结时运行，应用时须要实现接口：org.apache.hadoop.hive.ql.HiveDriverRunHook，具体在hive-site.xml中的配置如下： ...

insert overwrite table ... partition(platform_id)selectif(user_id regexp('.*:.*'),user_id,concat(platform_id,':',user_id)) asuser_id,...,last_onlined_at,created_at,rating_score,updated_at,noise,platform_id from `XXX`;concatconcat(platform_id,':',user_id)) asuser_idconcat拼接别名为user_id ifif()相当于三目运算符if（条件表达式，后果1，后果2）相当于java中的三目运算符,只是if前面的表达式类型能够不一样。if中的等于条件用“=”或“==”均可 REGEXP_EXTRACTregexp_extract(string A, string pattern, int index)返回值: string 阐明：将字符串A依照pattern正则表达式的规定拆分，返回index指定的字符，index从1开始计。 REGEXP_REPLACE语法: regexp_replace(string A, string B, string C) 操作类型: strings 返回值: string 阐明: 将字符串A中的合乎java正则表达式B的局部替换为C。 hive> select regexp_replace('h234ney', '\\d+', 'o');OKhoney

在企业中应用Hive构建离线数仓是一种非常广泛的计划。只管Hive的应用场景是通过批处理的形式解决大数据，通常对解决工夫不敏感。然而在资源无限的状况下，咱们须要关注Hive的性能调优，从而不便数据的疾速产出。同时，对于Hive的性能调优，也是面试中比拟常见的问题，因而把握Hive性能调优的一些办法，不仅可能在工作中晋升效率而且还能够在面试中怀才不遇。本文会通过四个方面介绍Hive性能调优，次要包含： 性能调优的工具设计优化数据存储优化作业优化性能调优的工具HQL提供了两个查看查问性能的工具：explain与analyze，除此之外Hive的日志也提供了十分具体的信息，不便查看执行性能和报错排查。 善用explain语句explain语句是查看执行打算常常应用的一个工具，能够应用该语句剖析查问执行打算，具体应用语法如下： EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] hql_query下面的执行语句中，有4个可选的关键字，其具体含意如下： FORMATTED：对执行打算进行格式化，返回JSON格局的执行打算EXTENDED：提供一些额定的信息，比方文件的门路信息DEPENDENCY：以JSON格局返回查问所依赖的表和分区的列表，从Hive0.10开始应用，如下图 AUTHORIZATION：列出须要被受权的条目，包含输出与输入，从Hive0.14开始应用,如下图 一个典型的查问执行打算次要包含三局部，具体如下： Abstract Syntax Tree (AST)：形象语法树，Hive应用一个称之为antlr的解析生成器，能够主动地将HQL生成为形象语法树Stage Dependencies：会列出运行查问所有的依赖以及stage的数量Stage Plans：蕴含了十分重要的信息，比方运行作业时的operator 和sort orders举个栗子假如有一张表： CREATE TABLE employee_partitioned( name string, work_place ARRAY<string>, gender_age STRUCT<gender:string,age:int>, skills_score MAP<string,int>, depart_title MAP<STRING,ARRAY<STRING>>)PARTITIONED BY (Year INT, Month INT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '|'COLLECTION ITEMS TERMINATED BY ','MAP KEYS TERMINATED BY ':';查看执行打算： EXPLAINSELECT gender_age.gender, count(*)FROM employee_partitionedWHERE YEAR=2020GROUP BY gender_age.genderLIMIT 2;执行打算概览： 如上图：Map/Reduce operator tree是形象语法树AST局部；**STAGEDEPENDENCIES包含三个阶段：Stage-0 、Stage-1及Stage-2，其中Stage-0 是root stage，即Stage-1与Stage-2依赖于Stage-0；STAGE PLANS**局部，Stage-1与Stage2都蕴含一个Map Operator Tree和一个Reduce Operator Tree，Stage-0不蕴含map和reduce，仅仅是一个fetch数据的操作。 ...

在企业中应用Hive构建离线数仓是一种非常广泛的计划。只管Hive的应用场景是通过批处理的形式解决大数据，通常对解决工夫不敏感。然而在资源无限的状况下，咱们须要关注Hive的性能调优，从而不便数据的疾速产出。同时，对于Hive的性能调优，也是面试中比拟常见的问题，因而把握Hive性能调优的一些办法，不仅可能在工作中晋升效率而且还能够在面试中怀才不遇。本文会通过四个方面介绍Hive性能调优，次要包含： 性能调优的工具设计优化数据存储优化作业优化性能调优的工具HQL提供了两个查看查问性能的工具：explain与analyze，除此之外Hive的日志也提供了十分具体的信息，不便查看执行性能和报错排查。 善用explain语句explain语句是查看执行打算常常应用的一个工具，能够应用该语句剖析查问执行打算，具体应用语法如下： EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] hql_query下面的执行语句中，有4个可选的关键字，其具体含意如下： FORMATTED：对执行打算进行格式化，返回JSON格局的执行打算EXTENDED：提供一些额定的信息，比方文件的门路信息DEPENDENCY：以JSON格局返回查问所依赖的表和分区的列表，从Hive0.10开始应用，如下图 AUTHORIZATION：列出须要被受权的条目，包含输出与输入，从Hive0.14开始应用,如下图 一个典型的查问执行打算次要包含三局部，具体如下： Abstract Syntax Tree (AST)：形象语法树，Hive应用一个称之为antlr的解析生成器，能够主动地将HQL生成为形象语法树Stage Dependencies：会列出运行查问所有的依赖以及stage的数量Stage Plans：蕴含了十分重要的信息，比方运行作业时的operator 和sort orders举个栗子假如有一张表： CREATE TABLE employee_partitioned( name string, work_place ARRAY<string>, gender_age STRUCT<gender:string,age:int>, skills_score MAP<string,int>, depart_title MAP<STRING,ARRAY<STRING>>)PARTITIONED BY (Year INT, Month INT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '|'COLLECTION ITEMS TERMINATED BY ','MAP KEYS TERMINATED BY ':';查看执行打算： EXPLAINSELECT gender_age.gender, count(*)FROM employee_partitionedWHERE YEAR=2020GROUP BY gender_age.genderLIMIT 2;执行打算概览： 如上图：Map/Reduce operator tree是形象语法树AST局部；**STAGEDEPENDENCIES包含三个阶段：Stage-0 、Stage-1及Stage-2，其中Stage-0 是root stage，即Stage-1与Stage-2依赖于Stage-0；STAGE PLANS**局部，Stage-1与Stage2都蕴含一个Map Operator Tree和一个Reduce Operator Tree，Stage-0不蕴含map和reduce，仅仅是一个fetch数据的操作。 ...

在企业中应用Hive构建离线数仓是一种非常广泛的计划。只管Hive的应用场景是通过批处理的形式解决大数据，通常对解决工夫不敏感。然而在资源无限的状况下，咱们须要关注Hive的性能调优，从而不便数据的疾速产出。同时，对于Hive的性能调优，也是面试中比拟常见的问题，因而把握Hive性能调优的一些办法，不仅可能在工作中晋升效率而且还能够在面试中怀才不遇。本文会通过四个方面介绍Hive性能调优，次要包含： 性能调优的工具设计优化数据存储优化作业优化性能调优的工具HQL提供了两个查看查问性能的工具：explain与analyze，除此之外Hive的日志也提供了十分具体的信息，不便查看执行性能和报错排查。 善用explain语句explain语句是查看执行打算常常应用的一个工具，能够应用该语句剖析查问执行打算，具体应用语法如下： EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] hql_query下面的执行语句中，有4个可选的关键字，其具体含意如下： FORMATTED：对执行打算进行格式化，返回JSON格局的执行打算EXTENDED：提供一些额定的信息，比方文件的门路信息DEPENDENCY：以JSON格局返回查问所依赖的表和分区的列表，从Hive0.10开始应用，如下图 AUTHORIZATION：列出须要被受权的条目，包含输出与输入，从Hive0.14开始应用,如下图 一个典型的查问执行打算次要包含三局部，具体如下： Abstract Syntax Tree (AST)：形象语法树，Hive应用一个称之为antlr的解析生成器，能够主动地将HQL生成为形象语法树Stage Dependencies：会列出运行查问所有的依赖以及stage的数量Stage Plans：蕴含了十分重要的信息，比方运行作业时的operator 和sort orders举个栗子假如有一张表： CREATE TABLE employee_partitioned( name string, work_place ARRAY<string>, gender_age STRUCT<gender:string,age:int>, skills_score MAP<string,int>, depart_title MAP<STRING,ARRAY<STRING>>)PARTITIONED BY (Year INT, Month INT)ROW FORMAT DELIMITEDFIELDS TERMINATED BY '|'COLLECTION ITEMS TERMINATED BY ','MAP KEYS TERMINATED BY ':';查看执行打算： EXPLAINSELECT gender_age.gender, count(*)FROM employee_partitionedWHERE YEAR=2020GROUP BY gender_age.genderLIMIT 2;执行打算概览： 如上图：Map/Reduce operator tree是形象语法树AST局部；**STAGEDEPENDENCIES包含三个阶段：Stage-0 、Stage-1及Stage-2，其中Stage-0 是root stage，即Stage-1与Stage-2依赖于Stage-0；STAGE PLANS**局部，Stage-1与Stage2都蕴含一个Map Operator Tree和一个Reduce Operator Tree，Stage-0不蕴含map和reduce，仅仅是一个fetch数据的操作。 ...

作者介绍：胡梦宇，知乎数据架构平台开发工程师背景Apache Hive 是基于 Apache Hadoop 的一个数据仓库工具，能够将结构化的数据文件映射为一张数据库表，并且提供了 Hive SQL 进行查问和剖析，在离线数仓中被宽泛应用。Hive Metastore 是 Hive 的元信息管理工具，它提供了操作元数据的一系列接口，其后端存储个别选用关系型数据库如 Derby、 MySQL 等。当初很多除了 Hive 之外计算框架都反对以 Hive Metastore 为元数据中心来查问底层 Hadoop 生态的数据，比方 Presto、Spark、Flink 等等。在知乎，咱们是将元信息存储在 MySQL 内的，随着业务数据的一直增长，MySQL 内曾经呈现单表数据量两千多万的状况，当用户的工作呈现 Metastore 密集操作的状况时，往往会呈现迟缓甚至超时的景象，极大影响了工作的稳定性。长此以往，MySQL 在将来的某一天肯定会不堪重负，因而优化 Hive 的元数据库势在必行。在去年，咱们做过数据治理，Hive 表生命周期治理，定期去删除元数据，冀望可能缩小 MySQL 的数据量，缓解元数据库的压力。然而通过实际，发现该计划有以下毛病： 数据的增长远比删除的要快，治标不治本；在删除超大分区表（分区数上百万）的分区时，会对 MySQL 造成肯定的压力，只能单线程去做，否则会影响其余失常的 Hive 查问，效率极其低下；在知乎，元信息删除是随同数据一起删除的（删除 HDFS 过期数据，节约老本），Hive 的用户可能存在建表不标准的状况，将分区门路挂错，导致误删数据。因而，咱们须要寻找新的技术计划来解决这个问题。 技术选型已有计划业内目前有两种计划可供借鉴： 对 MySQL 进行分库分表处理，将一台 MySQL 的压力摊派到 MySQL 集群；对 Hive Metastore 进行 Federation，采纳多套 Hive Metastore + MySQL 的架构，在 Metastore 后方设置代理，依照肯定的规定，对申请进行散发。然而通过调研，咱们发现两种计划都有肯定的缺点： 对 MySQL 进行分库分表，首先面临的间接问题就是须要批改 Metastore 操作 MySQL 的接口，波及到大量高风险的改变，后续对 Hive 的降级也会更加简单；对 Hive Metastore 进行 Federation，只管不须要对 Metastore 进行任何改变，然而须要额定保护一套路由组件，并且对路由规定的设置须要认真思考，切分现有的 MySQL 存储到不同的 MySQL 上，并且可能存在切分不平均，导致各个子集群的负载不平衡的状况；咱们每天都会同步一份 MySQL 的数据到 Hive，用作数据治理，生命周期治理等，同步是利用外部的数据同步平台，如果采纳下面两种计划，数据同步平台也须要对同步逻辑做额定的解决。最终计划其实问题次要在于，当数据量减少时，MySQL 受限于单机性能，很难有较好的体现，而将单台 MySQL 扩大为集群，复杂度将会呈几何倍回升。如果可能找到一款兼容 MySQL 协定的分布式数据库，就能完满解决这个问题。因而，咱们抉择了 TiDB。 ...

背景在做**订单表的时候，发现订单产品部分的人群属性是在最内层的json中，但是人群的标识是不固定的值，也就是说json里面的key是不固定的，并且没个里面可能有多个key。get_json_object不能处理这种case。 {"1":{"Price":{"name":"CNY","amount":169.0},"settlePrice":{"name":"CNY","amount":147.0}},"2":{"Price":{"name":"CNY","amount":92.6},"settlePrice":{"name":"CNY","amount":80.0}}} 例如上述的json，处理完毕以后希望得到的结果是 1 Price 169 settlePrice 1472 Price 92.6 settlePrice 80 调研从网上搜类似的问题，得到的答案大搜是用java去写一个udf方法，先获取json的key然后根据具体的key去json中取对应的值。理论上这个办法是可行的，因为部门的udf工程权限和发布流程都很长，所以一个udf方法从开发到上线可能半天就过去了，开发效率得不到保证。如果能写脚本话的udf方法就会快很多。 hive UDF 方法假设当前我们已经解析出了，json,order_id,sku_id,from_date,to_date 等几个字段在上级表中，接下来需要解析将json中的信息解析出来，python的uft如下： # coding=utf-8# __author__ = 'zongrun.yang'import sysreload(sys)sys.setdefaultencoding('utf8') import jsonimport osimport sys AR_PRICE = 'ARPrice'SETTLE_PRICE = 'settlePrice'AMOUNT = 'amount'for line in sys.stdin: line,order_id,sku_id,from_date,to_date = line.strip('\n').split('\t') band_prices = json.loads(line) result = [] for key in band_prices.keys(): unit = band_prices[key] ar_price = unit[AR_PRICE][AMOUNT] settle_price = unit[SETTLE_PRICE][AMOUNT] result.append(order_id+'\t'+key+'\t'+str(ar_price)+'\t'+str(settle_price)+'\t'+sku_id+'\t'+from_date+'\t'+to_date) print '\n'.join(result)在hive中加载上述文件： add file ./test.py; 在hive sql中使用： ...

1、执行hive，进入hive窗口2、执行show databases,查看所有的database;3、执行use origin_ennenergy_onecard; 则使用origin_ennenergy_onecard数据库4、执行show create table M_BD_T_GAS_ORDER_INFO_H;则可以查看table在hdfs上的存储路径如下：hive (origin_ennenergy_onecard)> show create table M_BD_T_GAS_ORDER_INFO_H;OKCREATE TABLE M_BD_T_GAS_ORDER_INFO_H(fguid string,fstationno string,fstationname string,fgunno int,fserialno int,fgas double,fprice double,fmoney double,fsumgas double,ftradedatetime date,fstopdatetime date,fsavedatetime date,ffueltype string,recorddate date)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ‘\t’STORED AS INPUTFORMAT’org.apache.hadoop.mapred.TextInputFormat’OUTPUTFORMAT’org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’LOCATION’hdfs://mycluster/user/hive/warehouse/origin_ennenergy_onecard.db/m_bd_t_gas_order_info_h’ —–标红部分为hdfs的路径TBLPROPERTIES (‘COLUMN_STATS_ACCURATE’=‘true’,’numFiles’=‘6’,’numRows’=‘3546198’,‘rawDataSize’=‘435279808’,’totalSize’=‘438826006’,’transient_lastDdlTime’=‘1468831756’)Time taken: 0.279 seconds, Fetched: 30 row(s)备注：hive其他命令：show functions —–>查看所有的hive函数desc tablesname ——>查看table的表结构感谢 “sborgite"提醒！

delect:用于删除特定行条件,你可以从给定表中删除所有的行TRUNCATE:truncate用于删除所有的行，这个行为在hive元存储删除数据是不可逆的DROP:删除hive中的表truncate 不能删除外部表！因为外部表里的数据并不是存放在Hive Meta store中truncate:truncate table table_name;例子：truncate table employees;

问题导读1.测试insert报错，该如何解决？2.hive delete和update报错，该如何解决？3.什么情况下才允许delete和update？首先用最普通的建表语句建一个表： hive>create table test(id int,name string)row format delimited fields terminated by ',';复制代码测试insert： insert into table test values (1,'row1'),(2,'row2');复制代码结果报错： java.io.FileNotFoundException: File does not exist: hdfs://127.0.0.1:9000/home/hadoop/git/hive/packaging/target/apache-hive-0.14.0-SNAPSHOT-bin/ apache-hive-0.14.0-SNAPSHOT-bin/lib/curator-client-2.6.0.jar         at org.apache.hadoop.hdfs.DistributedFileSystem$17.doCall(DistributedFileSystem.java:1128)         at org.apache.hadoop.hdfs.DistributedFileSystem$17.doCall(DistributedFileSystem.java:1120)         at org.apache.hadoop.fs.FileSystemLinkResolver.resolve(FileSystemLinkResolver.java:81)         at org.apache.hadoop.hdfs.DistributedFileSystem.getFileStatus(DistributedFileSystem.java:1120)         at org.apache.hadoop.mapreduce.filecache.ClientDistributedCacheManager.getFileStatus(ClientDistributedCacheManager.java:288)         at org.apache.hadoop.mapreduce.filecache.ClientDistributedCacheManager.getFileStatus(ClientDistributedCacheManager.java:224)         at org.apache.hadoop.mapreduce.filecache.ClientDistributedCacheManager.determineTimestamps(ClientDistributedCacheManager.java:99)         at org.apache.hadoop.mapreduce.filecache.ClientDistributedCacheManager.determineTimestampsAndCacheVisibilities(ClientDistributedCacheManager.java:57)         at org.apache.hadoop.mapreduce.JobSubmitter.copyAndConfigureFiles(JobSubmitter.java:265)         at org.apache.hadoop.mapreduce.JobSubmitter.copyAndConfigureFiles(JobSubmitter.java:301)         at org.apache.hadoop.mapreduce.JobSubmitter.submitJobInternal(JobSubmitter.java:389)         at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1285)         at org.apache.hadoop.mapreduce.Job$10.run(Job.java:1282)         at java.security.AccessController.doPrivileged(Native Method)         ......复制代码貌似往hdfs上找jar包了，小问题，直接把lib下的jar包上传到hdfs hadoop fs -mkdir -p /home/hadoop/git/hive/packaging/target/apache-hive-0.14.0-SNAPSHOT-bin/apache-hive-0.14.0-SNAPSHOT-bin/lib/ hadoop fs -put $HIVE_HOME/lib/* /home/hadoop/git/hive/packaging/target/apache-hive-0.14.0-SNAPSHOT-bin/apache-hive-0.14.0-SNAPSHOT-bin/lib/ 复制代码接着运行insert，没有问题，接下来测试delete hive>delete from test where id = 1;复制代码报错！：FAILED: SemanticException [Error 10294]: Attempt to do update or delete using transaction manager that does not support these operations.说是在使用的转换管理器不支持update跟delete操作。原来要支持update操作跟delete操作，必须额外再配置一些东西，见：https://cwiki.apache.org/conflue ... tersforTransactions根据提示配置hive-site.xml:     hive.support.concurrency – true     hive.enforce.bucketing – true     hive.exec.dynamic.partition.mode – nonstrict     hive.txn.manager – org.apache.hadoop.hive.ql.lockmgr.DbTxnManager     hive.compactor.initiator.on – true     hive.compactor.worker.threads – 1复制代码配置完以为能够顺利运行了，谁知开始报下面这个错误： FAILED: LockException [Error 10280]: Error communicating with the metastore复制代码与元数据库出现了问题，修改log为DEBUG查看具体错误： 4-11-04 14:20:14,367 DEBUG [Thread-8]: txn.CompactionTxnHandler (CompactionTxnHandler.java:findReadyToClean(265)) - Going to execute query <select cq_id, cq_database, cq_table, cq_partition, cq_type, cq_run_as from COMPACTION_QUEUE where cq_state = 'r'> 2014-11-04 14:20:14,367 ERROR [Thread-8]: txn.CompactionTxnHandler (CompactionTxnHandler.java:findReadyToClean(285)) - Unable to select next element for cleaning, Table 'hive.COMPACTION_QUEUE' doesn't exist 2014-11-04 14:20:14,367 DEBUG [Thread-8]: txn.CompactionTxnHandler (CompactionTxnHandler.java:findReadyToClean(287)) - Going to rollback 2014-11-04 14:20:14,368 ERROR [Thread-8]: compactor.Cleaner (Cleaner.java:run(143)) - Caught an exception in the main loop of compactor cleaner, MetaException(message :Unable to connect to transaction database com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'hive.COMPACTION_QUEUE' doesn't exist     at sun.reflect.GeneratedConstructorAccessor19.newInstance(Unknown Source)     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)     at java.lang.reflect.Constructor.newInstance(Constructor.java:526)     at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)复制代码在元数据库中找不到COMPACTION_QUEUE这个表，赶紧去mysql中查看，确实没有这个表。怎么会没有这个表呢？找了很久都没找到什么原因，查源码吧。在org.apache.hadoop.hive.metastore.txn下的TxnDbUtil类中找到了建表语句，顺藤摸瓜，找到了下面这个方法会调用建表语句： private void checkQFileTestHack() {     boolean hackOn = HiveConf.getBoolVar(conf, HiveConf.ConfVars.HIVE_IN_TEST) ||         HiveConf.getBoolVar(conf, HiveConf.ConfVars.HIVE_IN_TEZ_TEST);     if (hackOn) {       LOG.info("Hacking in canned values for transaction manager");       // Set up the transaction/locking db in the derby metastore       TxnDbUtil.setConfValues(conf);       try {         TxnDbUtil.prepDb();       } catch (Exception e) {         // We may have already created the tables and thus don't need to redo it.         if (!e.getMessage().contains("already exists")) {           throw new RuntimeException("Unable to set up transaction database for" +               " testing: " + e.getMessage());         }       }     }   }复制代码什么意思呢，就是说要运行建表语句还有一个条件：HIVE_IN_TEST或者HIVE_IN_TEZ_TEST.只有在测试环境中才能用delete，update操作，也可以理解，毕竟还没有开发完全。终于找到原因，解决方法也很简单：在hive-site.xml中添加下面的配置： <property> <name>hive.in.test</name> <value>true</value> </property>复制代码OK,再重新启动服务，再运行delete： hive>delete from test where id = 1;复制代码又报错： FAILED: SemanticException [Error 10297]: Attempt to do update or delete on table default.test that does not use an AcidOutputFormat or is not bucketed复制代码说是要进行delete操作的表test不是AcidOutputFormat或没有分桶。估计是要求输出是AcidOutputFormat然后必须分桶网上查到确实如此，而且目前只有ORCFileformat支持AcidOutputFormat，不仅如此建表时必须指定参数('transactional' = true)。感觉太麻烦了。。。。于是按照网上示例建表： hive>create table test(id int ,name string )clustered by (id) into 2 buckets stored as orc TBLPROPERTIES('transactional'='true');复制代码insert hive>insert into table test values (1,'row1'),(2,'row2'),(3,'row3');复制代码delete hive>delete from test where id = 1;复制代码update hive>update test set name = 'Raj' where id = 2;复制代码OK!全部顺利运行，不过貌似效率太低了，基本都要30s左右，估计应该可以优化，再研究研究...

Oracle SQL Developer从http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html下载SQL Developer 4.1.5，并解压；从http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-15.html下载Hive JDBC Driver for Oracle SQL Developer，并解压，进入解压后的目录，将Cloudera_HiveJDBC4_2.5.15.1040.zip解压。打开sqldeveloper.exe，点击”工具”–>“首选项”,在”数据库”–>”第三方JDBC驱动”中，添加Hive JDBC驱动：添加后重启sqldeveloper。再次打开sqldeveloper后，点击”新建连接”之后，多了”Hive”数据库：连接Hive: Oracle SQL Developer 从http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html下载SQL Developer 4.1.5，并解压；从http://www.cloudera.com/downloads/connectors/hive/jdbc/2-5-15.html下载Hive JDBC Driver for Oracle SQL Developer，并解压，进入解压后的目录，将Cloudera_HiveJDBC4_2.5.15.1040.zip解压。打开sqldeveloper.exe，点击”工具”–>“首选项”,在”数据库”–>”第三方JDBC驱动”中，添加Hive JDBC驱动： 添加后重启sqldeveloper。再次打开sqldeveloper后，点击”新建连接”之后，多了”Hive”数据库： 连接Hive: