云掣科技

云掣科技 查看完整档案

杭州编辑  |  填写毕业院校  |  填写所在公司/组织 www.dtsre.com/ 编辑
编辑

专注于云msp服务

个人动态

云掣科技 发布了文章 · 2020-12-18

MySQL并发控制下的|事务|MVCC|锁机制|解读

一、前言

随着业务发展,对数据库的并发性能要求也越来越高,不仅要做到高并发还需要在保障数据安全,那么今天我们聊一聊 MySQL 在高并发下事务、MVCC、锁机制是如何在高并发情况下维护数据的安全。

二、事务 ACID

  • 为什么需要事务:事务是为了保障用户的数据操作对数据是安全的。比如我们的银行卡余额,我们希望对它的操作是稳定准确的,而且绝对安全。
  • ACID:事务的四大特性原子性、一致性、隔离性、持久性。
  • 原子性:原子性是指一个事务要么全部执行,要么完全不执行。主要是由 innodb 引擎中的 undo 回滚日志来维护。
  • 隔离性:事务在操作过程中不会受到其它事务操作的影响。主要由事务的隔离级别和锁机制共同维护。
  • 持久性:事务操作的结果是具体持久性的,通俗来讲就是提交事务后会持久化存储(落盘)。主要是由 redo log 来维护。
  • 一致性:事务在开始和结束时,数据始终保持一致。由原子性、隔离性、持久性共同维护。

三、多版本并发控制 MVCC

*介绍:数据库的核心方向就是高并发,MySQL 通过并发控制技术来维护高并发的环境下数据的一致性和数据安全。MySQL 并发控制有两种技术方案锁机制(Locking) 和 多版本并发控制 (MVCC)。

  • 锁机制:通过锁机制可以维护数据的一致性,但是整体业务场景大多是读-读、读-写、写-写,三类并发场景,看似容易融合到业务场景后也比较复杂。通过锁机制主要可以帮助我们解决写-写 和 读-读 场景下的并发安全问题 则 MVCC 主要帮助解决 读-写 问题。
  • MVCC:多版本并发控制,侧重优化读-写业务的高并发环境。可以解决写操作时堵塞读操作的并发问题。
  • 一致性非锁定读:指 innodb 引擎通过多版本并发控制的方式来读取,当前执行时间数据库中的行数据。读取正在进行 update 或 delete 操作的行,不会等待锁释放,而是会读取该行的快照数据。快照就是指该行之前的版本数据,主要靠 undo 日志来实现,而 undo 是用于数据库回滚,因此快照读本身是没有开销的。后台 Purge 线程也会自动清理一些不需要的 undo 数据。
  • MVCC 两类读操作:分为两类读情况 快照读(Snapshot Read) 和 当前读(Current Read) 快照读是读取数据的可见版本而当前读则是读取当前数据的最新版本需要加锁从而保障其它事务不会修改当前数据。
  • MVCC 实现策略:我们在设计表过程中通过不会直接删除数据而是设定一个字段来标记,从实现逻辑意义上的删除。MVCC 的实现方式也与此类似。这种数据管理方式叫数据生命周期管理,其中两个指标就是标记数据的变化 和 标记数据可用状态。
  • MVCC 下的 DML过程演示:

Insert:进行 insert 操作,事务 id 假设为 1

Update:MVCC 会先将当前记录标记为已删除在 delete version 字段下设置版本号(原来为空),然后新增一行数据,写入相应的版本号,此时为新版本号为 2 和上一条数据的 delete version 一致,比如将 name 修改为 fantasy,如下表:

Delete:直接将当前数据的 delete version 打上版本号标记为删除

MVCC 解析:刚才只是在逻辑层面上介绍 MVCC 的运作方式 create version 和 delete version 维护的是数据的版本信息和数据可用状态,而实际上还有一个字段是用户 undo回滚的指针,接下来我们介绍源码中 MVCC 的实现方式。默认会给每张表加入三个隐藏字段(内部属性)

DB_TRX_ID:占 6 个字节,记录每一行最近一次修改它的事务 ID
DB_ROLL_OIR:占 7 个字节,记录指向回滚段 undo 日志的指针
DB_ROW_ID:占 6 个字节,当写入数据时,自动维护自增列

将三个字段结合就可以标记数据的周期性和,并定位到对应的事务。这就引出 innodb 中实现 MVCC 两个重要模块 undo 日志用来存储数据的变化 Read View 用来做可见性判断的, 里面保存了对本事务不可见的其他活跃事务

  • 注意:对于 innodb 来讲,无论是更新还是删除,都只是设置行记录上的 deldte BIT 来标记,而并不是真正的删除记录,后续这些记录的清理就需要 Purge 线程来做。还需要注意的是 MVCC 只能在 RC 和 RR 隔离级别下使用,RU 是读未提交状态,所以不存在版本问题,而串行化则会对读取的数据行加锁。

四、隔离级别

  • 为什么需要隔离级别?
    事务之间如果不互相隔离,那么就会出现脏读、不可重复读和幻读。
  • 简单概括脏读、不可重复读和幻读:
    写在前,读在后:脏读;
    读在前,写在后:不可重复读;
    读在前,写在后,再读:幻读。
  • 隔离级别与并发问题的关系如下:

其中串行化隔离级别虽然解决了所有数据问题,但是却带来了并发的性能问题,而读未提交的隔离级别违反了基础事务的安全处理要求,所以我们在选择隔离级别时都会在 RC 和 RR 中选择,MySQL 默认隔离级别为 RR 级别。

-- 查询数据库中的隔离级别

select @@transaction_isolation;

-- 临时设置MySQL数据库中的隔离级别

set global transaction_isolation='READ-COMMITTED';

  • RC 和 RR 的区别:RC 在事务可以读取到其它事务提交的事务数据,而对于 RR 级别来讲,它会保证在一个事务中数据多次的查询结果是不变的,尽管其它事务已经提交了改动。从锁的角度来讲 RC
  • RC 和 RR 的区别:RC 在事务可以读取到其它事务提交的事务数据,而对于 RR 级别来讲,它会保证在一个事务中数据多次的查询结果是不变的,尽管其它事务已经提交了改动。从锁的角度来讲 RC 的性能会优于 RR。
  • RC 和 RR 级别下的快照读:RC 级别下的快照读总是会读取被锁定行的最新版本的一份快照数据,而 RR 级别下的快照读总是会读取事务开始时行版本的数据。这个怎么理解呢?请看如下案例:
    首先打开 MySQL 会话 Session 1 开启一个事务然后查询一条数据

开启另一个 MySQL 会话 Session 2 模拟并发场景,开启事务修改 id1 = 3 中的 id2 为 8023

在 Session 2 中我们修改了 id2 20170831 为 8023 但是还未提交,此时 id1 = 3 的行已经加上了一个 X 排它锁,此时再读取 Session 1 会话中的 id1 = 3 的记录根据 innodb 引擎的特性,即在 RR 和 RC 事务隔离级别下会使用 “非锁定一致性读” 也就是快照读。接者Session 1 未提交的事务再此运行查询 id1 = 3 的 SQL 语句,此时无论此时隔离级别是 RR 和 RC 结果都如下图:

接者我们提交 Session 2 中的事务。

在 Session 2 中的事务提交后,在 Session 1 中再次执行查询 id1 = 3 的 SQL 语句,此时在 RR 和 RC 级别下运行的结果就不同了,RC 事务的隔离级别,总是读取最新版本的快照数据,因为 Session2 提交了事务更新了快照版本,所以 Session 1 在事务中可以读取 Session 2 中已经提交的改动,结果如下:

因为 RR 级别读取数据快照总是读取开始事务前的行版本的快照数据,所以尽管 Session 2 更新了快照版本,RR 级别下事务未提交之前不会受到影响,所以 RR 级别下两次读取数据的结果都相同:

五、锁机制

  • 什么是锁?
    锁是计算机协调多个进程或线程并发访问某一资源的机制。
  • innodb 两种锁:
    共享锁 S:允许一个事务去读一行,阻止其它事务获得相同数据集的排它锁。通俗来讲就是可以重复读,没读完时不允许写。
    排它锁 X:允许获得排它锁的事务更新数据,阻止其它事务获得相同数据集的排他锁和共享锁。通俗来讲就是写的时候不允许其它事务写和读。
  • 发现问题:有两个事务 A 和 B,事务 A 锁住了表中的一行数据,加上行锁 S,即这一行只能读不能写。之后事务 B 又申请整张表的写锁 (mysql 中可以使用 lock table xxx write 锁表),正常逻辑来将,事务 B 就可以修改表中任意行数据,包括事务 A 锁住的那一行数据,实际情况则会发生锁冲突,现在就需要一种机制来判断是否有行锁,比如锁表前先判断每一行数据是否有行锁,但是这种方案在随着数据量增大代价会无限放大,肯定是不取的,而意向锁就是来解决冲突的协调者。
  • 意向锁工作流程:
    事务 A 首先需要申请表的意向锁,成功后申请一行的行锁。
    事务 B 申请排它锁,但是发现表中已经有意向共享锁,说明表中的某行数据已经被锁定,此时申请的写锁会被堵塞。
  • 意向共享锁 IS:事务给某行数据加入共享锁前,需要先申请意向共享锁。通俗来讲,就是一个数据行加共享锁前必须要先取得该表的意向共享锁。
  • 意向排它锁 IX:与上类似,加入排它锁前需要先获得意向排它锁。
  • innodb 行锁:行锁是通过给索引加锁来实现的,不用担心表中是否创建了索引,如果有主键 MySQL 会在主键上创建聚簇索引用于回表查询,如果没有主键则考虑 unique 约束的字段,如果前面两种都不满足,则会创建隐藏列 RowID 作为聚簇索引。如果不通过索引检索数据,那么 innodb 引擎会对表中所有的数据加锁,实际效果与表锁相同,所以要尽可能让所有数据都通过索引来完成,避免行锁升级为表锁。
    innodb 下的三种行锁:
    行锁(Record Lock):对索引加锁,即锁定一行记录。
    间隙锁(Gap Lock):对索引项之间的间隙、对第一条记录前的间隙或最后一条记录后的间隙加锁,即锁定一个范围的记录,不包含记录本身。
    Next-Key Lock:锁定一个范围的记录并包含记录本身。

了解更多

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-11

Hive引擎Spark优化配置参数2

上一篇内容讲了资源参数优化,本篇继续说明spark driver以及spark shuffle相关的参数调优。

扩展spark driver

动态资源分配

在 Facebook,Spark 集群启用了动态资源分配(Dynamic Executor Allocation),以便更好的使用集群资源,而且在 Facebook 内部,Spark 是运行在多租户的集群上,所以这个也是非常合适的。比如典型的配置如下:

  • spark.dynamicAllocation.enabled = true
  • spark.dynamicAllocation.executorIdleTimeout = 2m
  • spark.dynamicAllocation.minExecutors = 1
  • spark.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 文件不可用。

点击添加图片描述(最多60个字)编辑

另外,Spark 最大 Fetch 重试次数也可以通过 spark.max.fetch.failures.per.stage 参数配置。

FetchFailed 会在 ShuffleReader 取数据失败 N 次后抛出,然后由 executor 通过 statusUpdate 传到 driver 端,实际的处理会在 DAGScheduler.handleTaskCompletion,它会重新提交该 Stage 和该 Stage 对应的 ShuffleMapStage,重试次数超过 spark.stage.maxConsecutiveAttempts 时会退出。

RPC 服务线程调优

当 Spark 同时运行大量的 tasks 时,Driver 很容易出现 OOM,这是因为在 Driver 端的 Netty 服务器上产生大量 RPC 的请求积压,我们可以通过加大 RPC 服务的线程数解决 OOM 问题,比如 spark.rpc.io.serverThreads = 64。

spark shuffle相关的参数调优

spark.shuffle.file.buffer

默认值:32k

参数说明:该参数用于设置shuffle write task的BufferedOutputStream的buffer缓冲大小。将数据写到磁盘文件之前,会先写入buffer缓冲中,待缓冲写满之后,才会溢写到磁盘。

调优建议:如果作业可用的内存资源较为充足的话,可以适当增加这个参数的大小(比如64k),从而减少shuffle write过程中溢写磁盘文件的次数,也就可以减少磁盘IO次数,进而提升性能。在实践中发现,合理调节该参数,性能会有1%~5%的提升。

spark.reducer.maxSizeInFlight

默认值:48m

参数说明:该参数用于设置shuffle read task的buffer缓冲大小,而这个buffer缓冲决定了每次能够拉取多少数据。

调优建议:如果作业可用的内存资源较为充足的话,可以适当增加这个参数的大小(比如96m),从而减少拉取数据的次数,也就可以减少网络传输的次数,进而提升性能。在实践中发现,合理调节该参数,性能会有1%~5%的提升。

spark.shuffle.io.maxRetries

默认值:3

参数说明:shuffle read task从shuffle write task所在节点拉取属于自己的数据时,如果因为网络异常导致拉取失败,是会自动进行重试的。该参数就代表了可以重试的最大次数。如果在指定次数之内拉取还是没有成功,就可能会导致作业执行失败。

调优建议:对于那些包含了特别耗时的shuffle操作的作业,建议增加重试最大次数(比如60次),以避免由于JVM的full gc或者网络不稳定等因素导致的数据拉取失败。在实践中发现,对于针对超大数据量(数十亿~上百亿)的shuffle过程,调节该参数可以大幅度提升稳定性。

spark.shuffle.io.retryWait

默认值:5s

参数说明:具体解释同上,该参数代表了每次重试拉取数据的等待间隔,默认是5s。

调优建议:建议加大间隔时长(比如60s),以增加shuffle操作的稳定性。

spark.shuffle.memoryFraction

默认值:0.2

参数说明:该参数代表了Executor内存中,分配给shuffle read task进行聚合操作的内存比例,默认是20%。

调优建议:如果内存充足,而且很少使用持久化操作,建议调高这个比例,给shuffle read的聚合操作更多内存,以避免由于内存不足导致聚合过程中频繁读写磁盘。在实践中发现,合理调节该参数可以将性能提升10%左右。

spark.shuffle.manager

默认值:sort

参数说明:该参数用于设置ShuffleManager的类型。Spark 1.5以后,有三个可选项:hash、sort和tungsten-sort。HashShuffleManager是Spark 1.2以前的默认选项,但是Spark 1.2以及之后的版本默认都是SortShuffleManager了。tungsten-sort与sort类似,但是使用了tungsten计划中的堆外内存管理机制,内存使用效率更高。

调优建议:由于SortShuffleManager默认会对数据进行排序,因此如果你的业务逻辑中需要该排序机制的话,则使用默认的SortShuffleManager就可以;而如果你的业务逻辑不需要对数据进行排序,那么建议参考后面的几个参数调优,通过bypass机制或优化的HashShuffleManager来避免排序操作,同时提供较好的磁盘读写性能。这里要注意的是,tungsten-sort要慎用,因为之前发现了一些相应的bug。

spark.shuffle.sort.bypassMergeThreshold

默认值:200

参数说明:当ShuffleManager为SortShuffleManager时,如果shuffle read task的数量小于这个阈值(默认是200),则shuffle write过程中不会进行排序操作,而是直接按照未经优化的HashShuffleManager的方式去写数据,但是最后会将每个task产生的所有临时磁盘文件都合并成一个文件,并会创建单独的索引文件。

调优建议:当你使用SortShuffleManager时,如果的确不需要排序操作,那么建议将这个参数调大一些,大于shuffle read task的数量。那么此时就会自动启用bypass机制,map-side就不会进行排序了,减少了排序的性能开销。但是这种方式下,依然会产生大量的磁盘文件,因此shuffle write性能有待提高。

spark.shuffle.consolidateFiles

默认值:false

参数说明:如果使用HashShuffleManager,该参数有效。如果设置为true,那么就会开启consolidate机制,会大幅度合并shuffle write的输出文件,对于shuffle read task数量特别多的情况下,这种方法可以极大地减少磁盘IO开销,提升性能。

调优建议:如果的确不需要SortShuffleManager的排序机制,那么除了使用bypass机制,还可以尝试将spark.shffle.manager参数手动指定为hash,使用HashShuffleManager,同时开启consolidate机制。在实践中尝试过,发现其性能比开启了bypass机制的SortShuffleManager要高出10%~30%。

spark.reducer.maxBlocksInFlightPerAddress

默认值:Int.MaxValue(2的31次方-1)

限制了每个主机每次reduce可以被多少台远程主机拉取文件块,调低这个参数可以有效减轻node manager的负载。

spark.reducer.maxReqsInFlight

默认值:Int.MaxValue(2的31次方-1)

限制远程机器拉取本机器文件块的请求数,随着集群增大,需要对此做出限制。否则可能会使本机负载过大而挂掉。。

spark.reducer.maxReqSizeShuffleToMem

默认值:Long.MaxValue

shuffle请求的文件块大小 超过这个参数值,就会被强行落盘,防止一大堆并发请求把内存占满。

spark.shuffle.compress

默认压缩 true

是否压缩map输出文件

spark.shuffle.spill.compress

默认:true

shuffle过程中溢出的文件是否压缩,使用spark.io.compression.codec压缩。

了解更多

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-10

Hive引擎Spark优化配置参数1

Hive是大数据领域常用的组件之一,主要是大数据离线数仓的运算,关于Hive的性能调优在日常工作和面试中是经常涉及的的一个点,因此掌握一些Hive调优是必不可少的一项技能。影响Hive效率的主要有数据倾斜、数据冗余、job的IO以及不同底层引擎配置情况和Hive本身参数和HiveSQL的执行等因素。本文主要描述在底层引擎为Spark时,经常会用到的、常见的配置参数。

资源参数优化

所谓的Spark资源参数调优,其实主要就是对Spark运行过程中各 个使用资源的地方,通过调节各种参数,来优化资源使用的效率,从而提升Spark作业的执行性能。以下参数就是Spark中主要的资源参数,每个参数都对 应着作业运行原理中的某个部分,我们同时也给出了一个调优的参考值。

num-executors

  • 参数说明:该参数用于设置Spark作业总共要用多少个Executor进程来执行。Driver在向YARN集群管理器申请资源时,YARN集 群管理器会尽可能按照你的设置来在集群的各个工作节点上,启动相应数量的Executor进程。这个参数非常之重要,如果不设置的话,默认只会给你启动少 量的Executor进程,此时你的Spark作业的运行速度是非常慢的。
  • 参数调优建议:每个Spark作业的运行一般设置50~100个左右的Executor进程比较合适,设置太少或太多的Executor进程都不好。设置的太少,无法充分利用集群资源;设置的太多的话,大部分队列可能无法给予充分的资源。

executor-memory

  • 参数说明:该参数用于设置每个Executor进程的内存。Executor内存的大小,很多时候直接决定了Spark作业的性能,而且跟常见的JVM OOM异常,也有直接的关联。
  • 参数调优建议:每个Executor进程的内存设置4G~8G较为合适。但是这只是一个参考值,具体的设置还是得根据不同部门的资源队列来定。可 以看看自己团队的资源队列的最大内存限制是多少,num-executors乘以executor-memory,就代表了你的Spark作业申请到的总 内存量(也就是所有Executor进程的内存总和),这个量是不能超过队列的最大内存量的。此外,如果你是跟团队里其他人共享这个资源队列,那么申请的 总内存量最好不要超过资源队列最大总内存的1/3~1/2,避免你自己的Spark作业占用了队列所有的资源,导致别的同学的作业无法运行。

executor-cores

  • 参数说明:该参数用于设置每个Executor进程的CPU core数量。这个参数决定了每个Executor进程并行执行task线程的能力。因为每个CPU core同一时间只能执行一个task线程,因此每个Executor进程的CPU core数量越多,越能够快速地执行完分配给自己的所有task线程。
  • 参数调优建议:Executor的CPU core数量设置为2~4个较为合适。同样得根据不同部门的资源队列来定,可以看看自己的资源队列的最大CPU core限制是多少,再依据设置的Executor数量,来决定每个Executor进程可以分配到几个CPU core。同样建议,如果是跟他人共享这个队列,那么num-executors * executor-cores不要超过队列总CPU core的1/3~1/2左右比较合适,也是避免影响其他同学的作业运行。

driver-memory

  • 参数说明:该参数用于设置Driver进程的内存。
  • 参数调优建议:Driver的内存通常来说不设置,或者设置1G左右应该就够了。唯一需要注意的一点是,如果需要使用collect算子将RDD的数据全部拉取到Driver上进行处理,那么必须确保Driver的内存足够大,否则会出现OOM内存溢出的问题。

spark.default.parallelism

  • 参数说明:该参数用于设置每个stage的默认task数量。这个参数极为重要,如果不设置可能会直接影响你的Spark作业性能。
  • 参数调优建议:Spark作业的默认task数量为500~1000个较为合适。很多同学常犯的一个错误就是不去设置这个参数,那么此时就会导致 Spark自己根据底层HDFS的block数量来设置task的数量,默认是一个HDFS block对应一个task。通常来说,Spark默认设置的数量是偏少的(比如就几十个task),如果task数量偏少的话,就会导致你前面设置好的 Executor的参数都前功尽弃。试想一下,无论你的Executor进程有多少个,内存和CPU有多大,但是task只有1个或者10个,那么90% 的Executor进程可能根本就没有task执行,也就是白白浪费了资源!因此Spark官网建议的设置原则是,设置该参数为num- executors * executor-cores的2~3倍较为合适,比如Executor的总CPU core数量为300个,那么设置1000个task是可以的,此时可以充分地利用Spark集群的资源。

spark.storage.memoryFraction

  • 参数说明:该参数用于设置RDD持久化数据在Executor内存中能占的比例,默认是0.6。也就是说,默认Executor 60%的内存,可以用来保存持久化的RDD数据。根据你选择的不同的持久化策略,如果内存不够时,可能数据就不会持久化,或者数据会写入磁盘。
  • 参数调优建议:如果Spark作业中,有较多的RDD持久化操作,该参数的值可以适当提高一些,保证持久化的数据能够容纳在内存中。避免内存不够 缓存所有的数据,导致数据只能写入磁盘中,降低了性能。但是如果Spark作业中的shuffle类操作比较多,而持久化操作比较少,那么这个参数的值适 当降低一些比较合适。此外,如果发现作业由于频繁的gc导致运行缓慢(通过spark web ui可以观察到作业的gc耗时),意味着task执行用户代码的内存不够用,那么同样建议调低这个参数的值。

spark.shuffle.memoryFraction

  • 参数说明:该参数用于设置shuffle过程中一个task拉取到上个stage的task的输出后,进行聚合操作时能够使用的Executor 内存的比例,默认是0.2。也就是说,Executor默认只有20%的内存用来进行该操作。shuffle操作在进行聚合时,如果发现使用的内存超出了 这个20%的限制,那么多余的数据就会溢写到磁盘文件中去,此时就会极大地降低性能。
  • 参数调优建议:如果Spark作业中的RDD持久化操作较少,shuffle操作较多时,建议降低持久化操作的内存占比,提高shuffle操作 的内存占比比例,避免shuffle过程中数据过多时内存不够用,必须溢写到磁盘上,降低了性能。此外,如果发现作业由于频繁的gc导致运行缓慢,意味着 task执行用户代码的内存不够用,那么同样建议调低这个参数的值。

资源参数的调优,没有一个固定的值,需要同学们根据自己的实际情况(包括Spark作业中的shuffle操作数量、RDD持久化操作数量以及spark web ui中显示的作业gc情况),同时参考本篇文章中给出的原理以及调优建议,合理地设置上述参数。

资源参数参考示例

以下是一份spark-submit命令的示例,大家可以参考一下,并根据自己的实际情况进行调节:

./bin/spark-submit    --master yarn-cluster    --num-executors 100    --executor-memory 6G    --executor-cores 4    --driver-memory 1G    --conf spark.default.parallelism=1000    --conf spark.storage.memoryFraction=0.5    --conf spark.shuffle.memoryFraction=0.3 

了解更多

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-08

Hive性能调优工具

HQL提供了两个查看查询性能的工具:explainanalyze,除此之外Hive的日志也提供了非常详细的信息,方便查看执行性能和报错排查。

工具一:explain

explain语句是查看执行计划经常使用的一个工具,可以使用该语句分析查询执行计划,具体使用语法如下:

EXPLAIN [FORMATTED|EXTENDED|DEPENDENCY|AUTHORIZATION] hql_query 

其中,关键字的含义:

  • FORMATTED:对执行计划进行格式化,返回JSON格式的执行计划
  • EXTENDED:提供一些额外的信息,比如文件的路径信息
  • DEPENDENCY:以JSON格式返回查询所依赖的表和分区的列表,从Hive0.10开始使用,如下图
  • image.png
  • AUTHORIZATION:列出需要被授权的条目,包括输入与输出,从Hive0.14开始使用,如下图

image.png

一个典型的查询执行计划主要包括三部分,具体如下:

  • Abstract Syntax Tree (AST):抽象语法树,Hive使用一个称之为antlr的解析生成器,可以自动地将HQL生成为抽象语法树
  • Stage Dependencies:会列出运行查询所有的依赖以及stage的数量
  • Stage Plans:包含了非常重要的信息,比如运行作业时的operator 和sort orders

比如:

EXPLAIN SELECT cc_call_center_id,  count(*) FROM tpcds_bin_partitioned_parquet_30.call_center WHERE cc_call_center_sk = 2 GROUP BY cc_call_center_id LIMIT 2;

查看执行计划:
STAGE DEPENDENCIES: Stage-1 is a root stage Stage-0 depends on stages: Stage-1 STAGE PLANS: Stage: Stage-1 Map Reduce Map Operator Tree: TableScan alias: call_center filterExpr: (cc_call_center_sk = 2) (type: boolean) Statistics: Num rows: 6 Data size: 186 Basic stats: COMPLETE Column stats: NONE Filter Operator predicate: (cc_call_center_sk = 2) (type: boolean) Statistics: Num rows: 3 Data size: 93 Basic stats: COMPLETE Column stats: NONE Select Operator expressions: cc_call_center_id (type: string) outputColumnNames: cc_call_center_id Statistics: Num rows: 3 Data size: 93 Basic stats: COMPLETE Column stats: NONE Group By Operator aggregations: count() keys: cc_call_center_id (type: string) mode: hash outputColumnNames: _col0, _col1 Statistics: Num rows: 3 Data size: 93 Basic stats: COMPLETE Column stats: NONE Reduce Output Operator key expressions: _col0 (type: string) sort order: + Map-reduce partition columns: _col0 (type: string) Statistics: Num rows: 3 Data size: 93 Basic stats: COMPLETE Column stats: NONE TopN Hash Memory Usage: 0.1 value expressions: _col1 (type: bigint) Reduce Operator Tree: Group By Operator aggregations: count(VALUE._col0) keys: KEY._col0 (type: string) mode: mergepartial outputColumnNames: _col0, _col1 Statistics: Num rows: 1 Data size: 31 Basic stats: COMPLETE Column stats: NONE Limit Number of rows: 2 Statistics: Num rows: 1 Data size: 31 Basic stats: COMPLETE Column stats: NONE File Output Operator compressed: false Statistics: Num rows: 1 Data size: 31 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: 2 Processor Tree: ListSink

其中包含了STAGE DEPENDENCIES、STAGE PLANS、Map Operator Tree和Reduce Operator Tree就是抽象语法树部分,STAGE DEPENDENCIES包含两个stage,其中Stage-1是根stage,Stage-0依赖与Stage-1,STAGE PLANS中Stage-1用于计算,Stage-0只是用于拉取数据。

工具二:analyze

analyze语句可以收集一些详细的统计信息,比如表的行数、文件数、数据的大小等信息。这些统计信息作为元数据存储在hive的元数据库中。Hive支持表、分区和列级别的统计(与Impala类似),这些信息作为Hive基于成本优化策略(Cost-Based Optimizer (CBO))的输入,该优化器的主要作用是选择耗费最小系统资源的查询计划。其实,在Hive3.2.0版本中,可以自动收集这些统计信息,当然也可以通过analyze语句进行手动统计表、分区或者字段的信息。具体的使用方式如下:

1.收集表的统计信息(非分区表),当指定NOSCAN关键字时,会忽略扫描文件内容,仅仅统计文件的数量与大小,速度会比较快

ANALYZE TABLE 表名  COMPUTE STATISTICS; 

ANALYZE TABLE 表名  COMPUTE STATISTICS NOSCAN;

2.收集分区表的统计信息

ANALYZE TABLE 表名 PARTITION(分区1,分区2) COMPUTE STATISTICS;

收集指定分区信息

ANALYZE TABLE 表名 PARTITION(分区1='xxx',分区2='yyy') COMPUTE STATISTICS;

3.收集表的某个字段的统计信息

ANALYZE TABLE 表名 COMPUTE STATISTICS FOR COLUMNS 字段名 ; 

可以通过设置:SET hive.stats.autogather=true,进行自动收集统计信息,对于INSERT OVERWRITE/INTO操作的表或者分区,可以自动收集统计信息。值得注意的是,LOAD操作不能够自动收集统计信息

一旦这些统计信息收集完毕,可以通过DESCRIBE EXTENDED/FORMATTED语句查询统计信息,具体使用如下:

1、查看一个分区的信息

DESCRIBE FORMATTED 表名 PARTITION(分区1='xxx',分区2='yyy');

2、查看一张表的信息

DESCRIBE FORMATTED 表名;

3、查看表中的字段信息

DESCRIBE FORMATTED 表名.列名;

了解更多

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-05

Hive常见语句及函数

一、数据库

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;

image.png

2.2 过滤显示查询的数据库

show databases like 'd*';

image.png

2.3 显示数据库信息

desc database ods;

image.png     

2.4 显示数据库详细信息,extended

desc database extended ods;

image.png

2.5 切换数据库详细信息,use

use dwd;

3 修改数据库

3.1 修改数据库属性,Alter

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);

3.2 删除数据库,drop

3.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]

(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。

(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。

(3)COMMENT:为表和列添加注释。

(4)PARTITIONED BY创建分区表

(5)CLUSTERED BY创建分桶表

(6)SORTED BY不常用,对桶中的一个或多个列另外排序

(7)ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。

SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。

(8)STORED AS指定存储文件类型

常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)

如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。

(9)LOCATION :指定表在HDFS上的存储位置。

(10)AS:后跟查询语句,根据查询结果创建表。

(11)LIKE允许用户复制现有的表结构,但是不复制数据。

(12)TBLPROPERTIES设置表的属性,在表明压缩类型时用过。(例如”orc.compress”=”SNAPPY”)

2 查询表信息

2.1 查看表的元数据信息 desc

DESCRIBE  FORMATTED orders;

image.png

2.2 修改 alter

2.2.1 修改表名: alter table dept rename to depts;

2.2.2 修改表属性:

alter table depts set tblproperties ('EXTERNAL'='TRUE') alter table depts set tblproperties ('EXTERNAL'='FALSE')

2.2.3 修改列,注意如果修改的字段类型和之前的字段类型不一致,之前的数据就无法显示

修改列名和列数据类型:alter table depts change dept_name dname string ; 修改位置放置第一位:alter table depts change id did string first; 修改位置指定某一列后面:alter table depts change dname dname string after did;

2.2.4 添加列(慎用)

alter table depts add columns(daddress string);

2.2.5 添加分区

alter table depts add partition(dt=20200713); alter table depts add partition(dt=20200713) location '/user/test/20200713.txt';

2.2.6 修改分区

alter table depts partition(dt=20170404) rename to partition(dt=20170405); alter table depts partition(dt=20170404) set location '/user/test/depts.txt';

2.2.7 删除分区

alter table depts drop if exists partition(dt=20170404);

2.3 查询表数据

Hive中的SELECT基础语法和标准SQL语法基本一致,支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等;

具体语法:

[WITH CommonTableExpression (, CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

2.3.1 常用关键词简介

(1) ORDER BY和SORT BY

ORDER BY用于全局排序,就是对指定的所有排序键进行全局排序,使用ORDER BY的查询语句,最后会用一个Reduce Task来完成全局排序。

explain select id,name from emp where deptid = 1001

image.png

explain select id,name from emp where deptid = 1001 order by id

image.png

image.png

SORT BY用于分区内排序,即每个Reduce任务内排序。

image.png

image.png

设定了2个reduce,从结果可以看出,每个reduce内做了排序。设定一个reduce,从结果看和order by一致。

(2)DISTRIBUTE BY和CLUSTER BY

distribute by:按照指定的字段或表达式对数据进行划分,输出到对应的Reduce或者文件中。

cluster by:除了兼具distribute by的功能,还兼具sort by的排序功能。

(3)GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。

(4)子查询

子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名。

SELECT col FROM ( SELECT a+b AS col FROM t1 ) t2

where 语句中也支持子查询。

SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT A FROM T1 WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

WITH..AS..也叫做子查询部分,语句允许hive定义一个sql片段,供整个sql使用,即将子查询作为一个表的语法,叫做Common Table Expression(CTE)

with q1 as (select from src where key= '5'), q2 as (select from src s2 where key = '4') select from q1 union all select from q2; with q1 as ( select key, value from src where key = '5') from q1 insert overwrite table s1 select * from q1;

(5)Hive查询中有两个虚拟列:INPUT__FILE__NAME:数据对应的HDFS文件名;BLOCK__OFFSET__INSIDE__FILE:该行记录在文件中的偏移量;

image.png

(6)HAVING 语句

having与where不同点

·where后面不能写分组函数,而having后面可以使用分组函数。

·having只用于group by分组统计语句。

(7) JOIN语句

Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联,还支持LEFT SEMI JOIN和CROSS JOIN,但这两种JOIN类型也可以用前面的代替。

·内关联(JOIN)

·左外关联(LEFT [OUTER] JOIN)

·右外关联(RIHGHT [OUTER] JOIN)

(8)LIKE语句

如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B的正则语法,则为TRUE;否则为FALSE。B中字符”_”表示任意单个字符,而字符”%”表示任意数量的字符。

年龄表

image.png

查询年龄为1开头的数据:select * from ages where  age like '1%';

image.png

查询年龄第二位是1的数据:select * from ages where  age like '_1%';

image.png

RLIKE子句是Hive中LIKE功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

2.3.2 常用函数简介

(1) 字符串连接函数,concat

语法: concat(string A, string B...)

返回值: string

说明:返回输入字符串连接后的结果,支持任意个输入字符串

举例:select concat('abc','def','gh') from ages ;

image.png

(2)带分隔符字符串连接函数:concat_ws

语法: concat_ws(string SEP, string A, string B...)

返回值: string

说明:返回输入字符串,连接后的结果,SEP表示各个字符串间的分隔符

举例:select concat_ws(',','abc','def','gh') from ages ;

image.png

除此之外还可以将数组的形式转换为字符串concat_ws(string SEP, array)

举例:select concat_ws('|',array('ads','des','ss')) from ages ;

image.png

(3)集合去重函数:collect_set

语法: collect_set(col)

返回值: array

说明: 将col字段进行去重,合并成一个数组。

举例:cookies表

image.png

select cookieid,collect_set(name) from cookies groupby cookieid ;

image.png

(4) UDTF一进多出

列转行:explode

语法: explode(ARRAY)

返回值: 多行

说明:将数组转换为多行

举例:select explode(array('ads','des','ss'))from ages;image.png

除此之外还可以将map拆分为多行。

(5)UDAF多进一出

聚合函数count,sum,avg等

(6)窗口函数

一般和聚合函数配合使用

OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。

举例:

orders表

image.png

image.png

image.png

总结:over是对分的组得到结果的count ,因为搜到了4个人,所以count数为4。直接count是分组后对组中的数据进行count。

image.png

image.pngimage.png

总结:添加partition by name 表示按照name分组进行sum求和。

(7) 排序函数

RANK() 排序相同时会重复,总数不会变

DENSE_RANK() 排序相同时会重复,总数会减少

ROW_NUMBER() 会根据顺序计算

举例:scores表

image.png

select name, subject, score, rank() over(partition by subject order by score desc) rp, dense_rank() over(partition by subject order by score desc) drp, row_number() over(partition by subject order by score desc) rmp from scores;

得到结果:

image.png

(8)函数总结大全

一、关系运算:

  1. 等值比较: =
  2. 等值比较:<=>
  3. 不等值比较: <>和!=
  4. 小于比较: <
  5. 小于等于比较: <=
  6. 大于比较: >
  7. 大于等于比较: >=
  8. 区间比较
  9. 空值判断: IS NULL
  10. 非空判断: IS NOT NULL
  11. LIKE比较: LIKE
  12. JAVA的LIKE操作: RLIKE
  13. REGEXP操作: REGEXP

二、数学运算:

  1. 加法操作: +
  2. 减法操作: –
  3. 乘法操作: *
  4. 除法操作: /
  5. 取余操作: %
  6. 位与操作: &
  7. 位或操作: |
  8. 位异或操作: ^

9.位取反操作: ~

三、逻辑运算:

  1. 逻辑与操作: AND 、&&
  2. 逻辑或操作: OR 、||
  3. 逻辑非操作: NOT、!

四、复合类型构造函数

  1. map结构
  2. struct结构
  3. named_struct结构
  4. array结构
  5. create_union

五、复合类型操作符

  1. 获取array中的元素
  2. 获取map中的元素
  3. 获取struct中的元素

六、数值计算函数

  1. 取整函数: round
  2. 指定精度取整函数: round
  3. 向下取整函数: floor
  4. 向上取整函数: ceil
  5. 向上取整函数: ceiling
  6. 取随机数函数: rand
  7. 自然指数函数: exp
  8. 以10为底对数函数: log10
  9. 以2为底对数函数: log2
  10. 对数函数: log
  11. 幂运算函数: pow
  12. 幂运算函数: power
  13. 开平方函数: sqrt
  14. 二进制函数: bin
  15. 十六进制函数: hex
  16. 反转十六进制函数: unhex
  17. 进制转换函数: conv
  18. 绝对值函数: abs
  19. 正取余函数: pmod
  20. 正弦函数: sin
  21. 反正弦函数: asin
  22. 余弦函数: cos
  23. 反余弦函数: acos
  24. positive函数: positive
  25. negative函数: negative

七、集合操作函数

  1. map类型大小:size
  2. array类型大小:size
  3. 判断元素数组是否包含元素:array_contains
  4. 获取map中所有value集合
  5. 获取map中所有key集合
  6. 数组排序

八、类型转换函数

  1. 二进制转换:binary
  2. 基础类型之间强制转换:cast

九、日期函数

  1. UNIX时间戳转日期函数: from_unixtime
  2. 获取当前UNIX时间戳函数: unix_timestamp
  3. 日期转UNIX时间戳函数: unix_timestamp
  4. 指定格式日期转UNIX时间戳函数: unix_timestamp
  5. 日期时间转日期函数: to_date
  6. 日期转年函数: year
  7. 日期转月函数: month
  8. 日期转天函数: day
  9. 日期转小时函数: hour
  10. 日期转分钟函数: minute
  11. 日期转秒函数: second
  12. 日期转周函数: weekofyear
  13. 日期比较函数: datediff
  14. 日期增加函数: date_add
  15. 日期减少函数: date_sub

十、条件函数

  1. If函数: if
  2. 非空查找函数: COALESCE
  3. 条件判断函数:CASE
  4. 条件判断函数:CASE

十一、字符串函数

1.    字符ascii码函数:ascii

2.    base64字符串

  1. 字符串连接函数:concat

4.    带分隔符字符串连接函数:concat_ws

  1. 数组转换成字符串的函数:concat_ws
  2. 小数位格式化成字符串函数:format_number
  3. 字符串截取函数:substr,substring
  4. 字符串截取函数:substr,substring
  5. 字符串查找函数:instr
  6. 字符串长度函数:length
  7. 字符串查找函数:locate
  8. 字符串格式化函数:printf
  9. 字符串转换成map函数:str_to_map
  10. base64解码函数:unbase64(string str)
  11. 字符串转大写函数:upper,ucase
  12. 字符串转小写函数:lower,lcase
  13. 去空格函数:trim
  14. 左边去空格函数:ltrim
  15. 右边去空格函数:rtrim
  16. 正则表达式替换函数:regexp_replace
  17. 正则表达式解析函数:regexp_extract
  18. URL解析函数:parse_url
  19. json解析函数:get_json_object
  20. 空格字符串函数:space
  21. 重复字符串函数:repeat
  22. 左补足函数:lpad
  23. 右补足函数:rpad
  24. 分割字符串函数: split
  25. 集合查找函数: find_in_set

30.    分词函数:sentences

  1. 分词后统计一起出现频次最高的TOP-K
  2. 分词后统计与指定单词一起出现频次最高的TOP-K

十二、混合函数

  1. 调用Java函数:java_method
  2. 调用Java函数:reflect
  3. 字符串的hash值:hash

十三、XPath解析XML函数

  1. xpath
  2. xpath_string
  3. xpath_boolean
  4. xpath_short, xpath_int, xpath_long
  5. xpath_float, xpath_double, xpath_number

十四、汇总统计函数(UDAF)

  1. 个数统计函数: count
  2. 总和统计函数: sum
  3. 平均值统计函数: avg
  4. 最小值统计函数: min
  5. 最大值统计函数: max
  6. 非空集合总体变量函数: var_pop
  7. 非空集合样本变量函数: var_samp
  8. 总体标准偏离函数: stddev_pop
  9. 样本标准偏离函数: stddev_samp

10.中位数函数: percentile

  1. 中位数函数: percentile
  2. 近似中位数函数: percentile_approx
  3. 近似中位数函数: percentile_approx
  4. 直方图: histogram_numeric
  5. 集合去重数:collect_set
  6. 集合不去重函数:collect_list

十五、表格生成函数Table-Generating Functions (UDTF)

  1. 数组拆分成多行:explode
  2. Map拆分成多行:explode
查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-03

手把手教你搭建Hive环境

准备环境: 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

image.png

修改完成使环境变量立即生效

source ~/.bash_profile

4、修改配置文件名

image.png

mv hive-env.sh.template hive-env.sh

5、进入该文件,配置hadoop路径与hive_conf路径

image.png

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已启动)

image.png

这里需要注意

1)如果hadooplib中的相关jar包与hive中的jar包冲突会导致启动失败:相关问题见链接

http://www.bubuko.com/infodetail-3286965.html

2)执行查看库hql语句,一开始没有执行初始化库操作导致启动失败,hive存储元数据的默认库为derby。

https://blog.csdn.net/lz6363/article/details/95805446

image.png

初始化相关指令

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

image.png

2)安装MySQL服务器

yum -y install mysql57-community-release-el7-10.noarch.rpm

image.png

3)启动mysql并查看mysql状态

systemctl start  mysqld.service

systemctl status mysqld.service

image.png

显示active,启动成功。

4)查看初始密码

grep "password" /var/log/mysqld.log

image.png

5)登录mysql数据库并修改密码

mysql -uroot -p

这里需要注意的是,如果密码设置过于简单会报错

image.png

原因是因为MySQL有密码设置的规范,具体是与validate_password_policy的值有关:

image.png

修改一下密码规则

set global validate_password_policy=0;

set global validate_password_length=1;

此时在修改密码就可以了

ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';

6)查看数据库

show databases;

image.png

9、 在hive/conf目录中创建文件 hive-site.xml

<configuration> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://172.16.25.27:3306/metastore?createDatabaseIfNotExist=true</value> <description>JDBC connect string for a JDBC metastore</description> </property> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> <description>Driver class name for a JDBC metastore</description> </property> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> <description>username to use against metastore database</description> </property> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>root</value> <description>password to use against metastore database</description> </property> </configuration>

10、下载mysql驱动包,将相关依赖jar包导入到/hive/lib文件夹下

下载地址:https://dev.mysql.com/downloads/file/?id=496585

mysql 5.7 用8.0版本的驱动可以,5.1版本也可以

image.png

配置完毕,启动Hive,收工。

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-12-02

Hive架构及Hive SQL的执行流程解读

1、Hive产生背景

  • MapReduce编程的不便性
  • HDFS上的文件缺少Schema(表名,名称,ID等,为数据库对象的集合)

2、Hive是什么

Hive的使用场景是什么?

基于Hadoop做一些数据清洗啊(ETL)、报表啊、数据分析

可以将结构化的数据文件映射为一张数据库表,并提供类SQL查询功能。

Hive是SQL解析引擎,它将SQL语句转译成M/R Job然后在Hadoop执行。

  • 由Facebook开源,最初用于解决海量结构化的日志数据统计问题
  • 构建在Hadoop之上的数据仓库
  • Hive定义了一种类SQL查询语言:HQL(类似SQL但不完全相同)
  • 通常用于进行离线数据处理(早期采用MapReduce)
  • 底层支持多种不同的执行引擎(现在可以直接把Hive跑在Spark上面)

Hive底层的执行引擎有:MapReduce、Tez、Spark

3、Hive 特点

  • Hive 最大的特点是 Hive 通过类 SQL 来分析大数据,而避免了写 MapReduce 程序来分析数据,这样使得分析数据更容易
  • Hive 是将数据映射成数据库和一张张的表,库和表的元数据信息一般存在关系型数据库上(比如 MySQL)
  • Hive 本身并不提供数据的存储功能,数据一般都是存储在 HDFS 上的(对数据完整性、格式要求并不严格)
  • Hive 很容易扩展自己的存储能力和计算能力,这个是继承自 hadoop 的(适用于大规模的并行计算)
  • Hive 是专为 OLAP(在线分析处理) 设计,不支持事务

4、Hive体系架构

Hive是C/S模式

客户端:

Client端有JDBC/ODBC和Thrift Client,可远程访问Hive

可以通过shell脚本的方式访问,或者通过Thrift协议,按照平时编写JDBC的方式完成对Hive的数据操作

Server:CLI、Thrift Server、HWI(Hive web Interface)、Driver、Metastore

  • 其中CLI、Thrift Server、HWI是暴露给Client访问的独立部署的Hive服务
  • Driver、Metastore是Hive内部组件,Metastore还可以供第三方SQL on Hadoop框架使用
  • beeine(Hive 0.11引入),作为Hive JDBC Client访问HiveServer2,解决了CLI并发访问问题

Driver:

输入了sql字符串,对sql字符串进行解析,转化程抽象语法树,再转化成逻辑计划,然后使用优化工具对逻辑计划进行优化,最终生成物理计划(序列化反序列化,UDF函数),交给Execution执行引擎,提交到MapReduce上执行(输入和输出可以是本地的也可以是HDFS/Hbase)见下图的hive架构

Metastore:

Metastore进行元数据管理:Derby(内置 )、Mysql;Derby:Derby只接受一个Hive的会话访问;Mysql:Hive跑在Hadoop之上的,Mysql进行主备(定时同步操作)

image.png

由上图可知,hadoop 和 mapreduce 是 hive 架构的根基。

MetaStore:存储和管理Hive的元数据,使用关系数据库来保存元数据信息。

解析器和编译器:将SQL语句生成语法树,然后再生成DAG形式的Job链,成为逻辑计划

优化器:只提供了基于规则的优化

  • 列过滤:去除查询中不需要的列
  • 行过滤:Where条件判断等在TableScan阶段就进行过滤,利用Partition信息,只读取符合条件的Partition
  • 谓词下推:减少后面的数据量
  • Join方式
    。 Map端join: 调整Join顺序,确保以大表作为驱动表,小表载入所有mapper内存中
    。 shuffle join:按照hash函数,将两张表的数据发送给join
    。对于数据分布不均衡的表Group by时,为避免数据集中到少数的reducer上,分成两个map-reduce阶段。第一个阶段先用Distinct列进行shuffle,然后在reduce端部分聚合,减小数据规模,第二个map-reduce阶段再按group-by列聚合。
    。 sort merge join:排序,按照顺序切割数据,相同的范围发送给相同的节点(运行前在后台创建立两张排序表,或者建表的时候指定)
    。 在map端用hash进行部分聚合,减小reduce端数据处理规模。

执行器:执行器将DAG转换为MR任务。执行器会顺序执行其中所有的Job,如果Job不存在依赖关系,采用并发的方式进行执行。

5、基于Hadoop上的Hive SQL的执行流程

image.png

sql写出来以后只是一些字符串的拼接,所以要经过一系列的解析处理,才能最终变成集群上的执行的作业

1.Parser:将sql解析为AST(抽象语法树),会进行语法校验,AST本质还是字符串

2.Analyzer:语法解析,生成QB(query block)

3.Logicl Plan:逻辑执行计划解析,生成一堆Opertator Tree

4.Logical optimizer:进行逻辑执行计划优化,生成一堆优化后的Opertator Tree

5.Phsical plan:物理执行计划解析,生成tasktree

6.Phsical Optimizer:进行物理执行计划优化,生成优化后的tasktree,该任务即是集群上的执行的作业

  • 结论:经过以上的六步,普通的字符串sql被解析映射成了集群上的执行任务,最重要的两步是 逻辑执行计划优化和物理执行计划优化(图中红线圈画)

查看执行计划。

explain select id,name from emp where deptid = 1001 order by id

image.png

image.png

查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-11-25

MySQL|主从延迟问题排查(二)

二、案例分享二

2.1 问题描述

主库执行insert  select 批量写入操作,主从复制通过row模式下转换为批量的insert大事务操作,导致只读实例CPU资源以及延迟上涨

16:55~17:07

2.2 处理流程

1、接收到只读实例备库延迟告警后,我们观察到只读实例的CPU资源有有明显上涨,同时数据库有大量数据写入操作

image.png

image.png

image.png

2、延迟期间,只读实例的tps的趋势是先下降后上涨,binlog日志量达到12.54G,可以推断出主实例传输过来的批量的写入操作是同一事务中,再加上只读实例配置相对于主实例较低,所以导致这么大的延迟

image.png

2、查看主从延迟期间主实例的情况,可以看到主实例确实执行了大量的数据写入操作,以及主实例审计日志中,我们找到了批量写入操作

image.png

image.png

image.png

3、只读实例延迟趋势17:05后,只读实例tps上涨,同时同步延迟开始下降

image.png

image.png

4、延迟流程描述

  • 16:43 主实例执行insert select批量写入操作,主库执行完毕后,binlog以row的模式将所有的insert操作放在一个事务中传输到只读实例
  • 16:55 只读实例开始应用该大事务中的insert操作,tps跌落,数据库缓存写/日志写上涨
  • 17:05 大事务应用完毕,开始同步延迟期间的binlog操作,正常业务下多个小事务操作,tps上涨明显,延迟开始回落
  • 10:07 主从追平延迟期间的binlog,主从延迟恢复为0
查看原文

赞 0 收藏 0 评论 0

云掣科技 发布了文章 · 2020-11-24

MySQL|主从延迟问题排查(一)

一、案例分享

1.1 问题描述

大查询长时间执行无法释放DML读锁,后续同步主库的DDL操作获取DML写锁资源被阻塞等待,导致后续同步主库的操作堆积,主从延迟增长严重。从同步延迟的监控来看,延迟从17:11开始,17:51:59进行kill大查询操作,直到17:53建议业务方将大查询kill掉后才结束。

1.2 处理流程

1、当接收到只读实例的同步延迟告警后,登录到RDS的管理控制台查看实例当前会话执行情况,判断只读实例当前负载压力。从当前会话截图可以看到,会话并无明显堆积,但是有两个执行时间很久的大查询操作。

2、17:11延迟开始,17:51 kill大查询,17:53主从延迟恢复。我们仍需要排查这个期间主实例和只读实例的运行情况,分析造成主从延迟的具体原因

3、对主实例的排查

1)查看延迟期间主库是否有一些批处理/大事务操作,主库业务业务请求上涨或者有批量的更新操作。对此,我们主要观察主实例的QPS/TPS监控、MySQL_COMDML和日志读写的监控指标。

从以上截图中可以看到,主库TPS在主从延迟期间并没有明显的上涨,说明期间主库业务压力正常;主库MySQL_COMDML和日志读写在主从延迟期间也没有明显的上涨,说明期间主库也没有执行一些批量更新的大事务操作。

2)查看延迟期间主库是否有执行消耗较大的DDL操作。在RDS中若开启了审计日志,我们可以通过时间以及操作类型进行过滤排查

通过对审计日志的搜索,我们搜查到一条对视图定义进行alter的操作,该alter操作仅仅执行了2.32ms,其资源消耗本身并不大。

4、对只读实例的排查

1)查看延迟期间只读实例是否有较大负载压力,从只读实例延迟期间的会话执行情况以及资源消耗可以知道,延迟期间只读实例并无较大负载压力

2)从只读实例的QPS/TPS监控中可以看到,17:51kill掉大查询后只读实例的TPS异常上涨,17:53TPS恢复正常,延迟恢复。说明17:51~17:53期间只读实例在大量应用主库传输过来的binlog日志,恢复主从复制延迟。

5、捕捉延迟期间会话中的异常现象,大查询长时间执行未结束,执行explain操作显示为MDL锁等待,结合我们在主库审计日志中搜索到的alter操作,我们可以推断造成主从延迟的原因可能是只读实例大查询阻塞了从主库传输过来的Alter操作,导致后续延迟一直上涨,并在我们kill掉大查询后恢复。

6、为了印证我们的猜想,我们通过审计日志把相关操作的时间线进行梳理

  • 只读实例view_order_logistics_new相关的大查询执行了3602s还未执行完,一直持有表的DML读锁,不影响表的正常读写操作
  • 17:10 主库执行了ALTER ALGORITHM=UNDEFINED DEFINER=super_sha_prd_db@% SQL SECURITY DEFINER VIEW view_order_logistics_new 的操作
  • 只读实例view_order_logistics_new的大查询仍在执行中,此时主库执行alter操作传输到只读实例,alter操作需要的DML写锁与大查询持有的DML读锁冲突
  • alter操作无法获取到DML写锁从而开始等待锁资源,从主控传输过来的binlog也被阻塞,主从延迟开始上涨
  • 17:51:59 只读实例kill掉了view_order_logistics_new的大查询,只读实例TPS上涨,只读实例开始应用alter操作之后的所有binlog日志
  • 17:53:08 只读实例TPS恢复,应用延迟期间的binlog完毕,主从复制恢复正常

更多技术信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 1 收藏 0 评论 0

云掣科技 发布了文章 · 2020-11-23

MySQL|空间碎片化问题处理

一、空间碎片化严重案例分享

1.1 问题描述

实例磁盘空间近1个月上涨趋势明显,主要是个别日志表存储较大且部分表存在空间碎片化的现象。

1.2 处理流程

1、通过日常巡检以及监控发现某实例磁盘空间近1月上涨趋势明显

image.png

2、在询问业务方是否为正常的业务增长外,查看该实例top表空间的表,排查是否存在异常较大的表

1)查看数据存储量较大的schema

SELECT table_Schema  , round(SUM((DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / 1024 / 1024), 2) AS Total_Size FROM tables GROUP BY table_schema ORDER BY Total_Size DESC;

image.png

2)查看具体schema下top表的空间使用情况

SELECT table_schema, table_name  , round(DATA_LENGTH / 1024 / 1024, 2) AS Data_Size , round(INDEX_LENGTH / 1024 / 1024, 2) AS Index_Size , round(DATA_FREE / 1024 / 1024, 2) AS Free_Size FROM tables WHERE table_schema = 'db_rim' ORDER BY Data_Size DESC LIMIT 10;

image.png

3、从以上截图中我们可以得到信息:db_rim库库下,rim_user_msg_log表的数据量很大,可查看是否可对该表中无效的数据进行清理?rim_user_msg_analyse这张表的碎片化将尽17G,可考虑对这些碎片化空间进行回收

4、最终采取的处理方法为:rim_user_msg_log表直接清空,rim_user_msg_analyse进行碎片化回收

1)清理前

image.png

2)清理后

image.png

3)实例磁盘空间使用趋势

image.png

二、表数据量过大清理案例

2.1 问题描述

通过对TOP表数据量的监控,我们可以看到sys_rest_server_log的数据量已经达到7000w,除空间占用外,我们更关注的是该表对数据库带来的性能隐患。若业务上或者人为不当的查询该表数据,其操作的资源消耗在一定程度上一定会对正常业务造成影响。

image.png

2.2 问题处理

1、该表当前的表现主要有两点,一点是某瞬时写入量极大,另外一点是表日增可达到300w记录数。对于数据库而言这种表行为或业务设计是不合理的,我们需要搞清楚该表究竟是用来记录哪些信息,业务设计是否合理?

通过观察表数据以及与相关开发人员沟通,我们得知该表会记录应用端所有的接口调用信息,我们所观察到的数据增长尖峰也正是由于业务量上涨而导致该表瞬间并发写入大量日志信息。

image.png

image.png

2、对于这种大数据量的日志记录信息,建议最好使用ELK这种日志分析服务,而不是使用数据库进行存储

3、限于开发人员开发能力有限,无法改造业务架构。针对该表日增长、瞬间增长都极大的情况,我们与开发人员沟通该表记录的数据是否可从源端上进行减少。只记录重要信息、非重要信息的接口日志入库进行屏蔽

4、确定表数据记录可从最根本减产后,我们仍需要对目前7000w的数据进行处理,将历史无效数据进行清理,并制定合适的数据清理策略

5、无效历史数据清理/数据清理策略

1、创建备份表: create table sys_rest_server_log_bak_0228_1 like sys_rest_server_log;  
2、表名替换 将源表sys_rest_server_log重命名为备份表,备份表sys_rest_server_log_bak_0208_1重命名为源表sys_rest_server_log,业务数据会写入空的新表,但是在rename期间这部分业务日志写入会失败【需关注】 rename table sys_rest_server_log to sys_rest_server_log_bak_0228_2,sys_rest_server_log_bak_0228_1 to sys_rest_server_log;  
3、将备份表sys_rest_server_log_bak_0208_2中的仍然需要的数据重新写入到sys_rest_server_log,分批次写入,sys_rest_server_log保留近15天数据。程序上可保证主键fd_id唯一,避免主键冲突。 insert into sys_rest_server_log select * from sys_rest_server_log_bak_0228_2 where fd_start_time between 'xxx' and 'xxx';  
4、调整平台数据清理策略,将服务运行日志备份天数改为15天,清除服务运行日志备份天数改为30天  
5、sys_rest_server_log_bak_0228_2表保留80+天记录数据,暂时可以不做清理,等程序维护的sys_rest_server_log_backup有完备的数据后,将sys_rest_server_log_bak_0228_2表清除;

三、总结归纳

2.1 如何判断表碎片化问题是否严重

表数据的频繁更新容易对表空间造成一定的碎片化,我们可以通过information_schema.table表中的data_free字段判断该表碎片化是否严重。

2.2 如何处理碎片化问题

optimize table tableName; alter table tablName engine=innodb;

2.3 大表历史数据清楚过程中的关注点

1、通过创建bak表与源表进行rename替换,rename期间会造成业务对该表数据写入失败 
2、bak表创建的时需关注primayr key 
1)程序上保证主键唯一可不需要关注 
2)若主键使用自增长,建议在创建bak表创建时提高当前的auto_increment指,空余一部分buffer空间,保证后续将源表历史数据写入rename后的新表时主键不冲突 
3、对于此类经常需要进行数据清理的表,建议使用分区表进行存储,后续对数据的处理仅仅需要删除指定分区

更多技术信息请查看云掣官网https://www.dtstack.com/dtsmart/

查看原文

赞 0 收藏 0 评论 0

认证与成就

  • 获得 7 次点赞
  • 获得 1 枚徽章 获得 0 枚金徽章, 获得 0 枚银徽章, 获得 1 枚铜徽章

擅长技能
编辑

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2018-11-21
个人主页被 1.1k 人浏览