快手大数据架构工程师钟亮近日在A2M人工智能与机器学习创新峰会上分享了题为《SQL on Hadoop在快手大数据平台的实践与优化》的演讲，主要从SQLonHadoop的介绍,SQLonHadoop平台概览，快手中的SQLonHadoop使用经验和改进分析，快手SQLonHadoop的未来规划从四个方面介绍了SQLonHadoop的架构。01SQLonHadoop介绍SQLonHadoop。顾名思义，它是一个基于Hadoop生态的SQL引擎架构。事实上，我们经常听到Hive、SparkSQL、Presto和Impala架构。下面简单介绍一下常用的架构。SQLonHadoop-HIVEHIVE，一个数据仓库系统。它将数据结构映射到存储的数据，通过SQL对大规模分布式存储数据进行读写和管理。根据定义的数据模式和输出的Storage，对输入的SQL进行编译优化，生成引擎对应的任务，然后对生成的任务进行调度和执行。目前HIVE支持的引擎类型有：MR、SPARK、TEZ。基于HIVE自身的架构，还有一些额外的服务提供方式，比如HiveServer2和MetaStoreServer都是Thrift架构。另外，HiveServer2为远程客户端提供了提交SQL任务的功能，MetaStoreServer为远程客户端提供了操作元数据的功能。SQLonHadoop简介-SPARKSpark，一个快速、易用、以DAG为执行方式的大规模数据处理统一分析引擎，主要模块分为SQL引擎、流处理、机器学习、图加工。SQLonHadoop介绍-SPARKSQLSPARKSQL基于SPARK的计算引擎，实现数据统一访问，集成Hive，支持标准JDBC连接。SPARKSQL常用于数据交互分析场景。SPARKSQL的主要执行逻辑是先将SQL解析成语法树，然后通过语义分析生成逻辑执行计划，再与元数据交互优化逻辑执行计划，最后将逻??辑执行转化为物理执行计划，即RDDlineage，并执行任务。SQLonHadoop简介-PRESTOPRESTO，一个用于交互式分析查询的开源分布式SQL查询引擎。因为基于内存计算，PRESTO的计算性能要大于MR和SPARK引擎，有大量的IO操作。易于弹性扩展，支持可插拔连接。业界有很多用例，包括FaceBook、AirBnb、美团等，都在大规模使用。SQLonHadoop简介——其他行业解决方案我们看到了这么多的SQLonHadoop架构，说明这个架构是比较实用和成熟的。使用SQLonHadoop架构，可以满足支持海量数据处理的需求。02快手SQLonHadoop平台概览快手SQLonHadoop平台概览——平台规模查询平台每天SQL总量约70万条，DQL总量约18万条。AdHoc集群主要用于交互分析和机器查询，DQL平均耗时300s；AdHoc内部有Loacl任务和加速引擎应用，所以查询要求比较低。ETL集群主要用于ETL处理和报告生成。DQL平均耗时1000s，DQLP50平均耗时100s，DQLP90平均耗时4000s。除了以上两个集群外，其他的小集群主要用于个别服务。快手SQLonHadoop平台概览——服务层服务层应用于上层。上层有四个模块，包括同步服务、ETL平台、AdHoc平台和用户程序。在调度的上层，还有四个方面的数据，比如服务器日志。处理完后会直接连接到HDFS，后面我们会清理它们；服务管理数据和数据库信息，然后会通过同步服务进入到相应的数据源中，而我们会将元数据信息存储在后端的元数据系统中。从网页爬取的数据会存储在hbase中，后面会进行清洗处理。快手SQLonHadoop平台概述——平台组件说明HUE和NoteBook主要提供交互式查询系统。报表系统和BI系统主要是ETL处理和普通报表生成，附加的元数据系统是对外服务。快手目前的引擎支持MR、Presto和Spark。管理系统主要用来管理我们现在的集群。HiveServer2集群路由系统主要用于引擎选型。监控系统和运维系统主要是针对HiveServer2引擎的运维。我们在使用HiveServer2的过程中遇到了很多问题。接下来，我将详细讲解快手是如何优化和实践的。03SQLonHadoopin快手HiveServer2多集群架构目前有多个HiveServer2集群，即AdHoc和ETL集群，以及其他小集群。不同的集群有对应的连接ZK，客户端可以通过ZK连接到HiveServer2集群。为了保证核心任务的稳定性，ETL集群分为核心集群和通用集群。当客户端连接到HS2时，我们将确定任务的优先级。高优先级的任务会被路由到核心集群，低优先级的任务会被路由到通用集群。HiveServer2服务内部流程图BeaconServer服务BeaconServer服务作为后端的HookServer，配合HS2中的Hook，在HS2服务外实现需要的功能。目前支持的模块包括路由、审计、SQL重写、任务控制、错误分析、优化建议等。?Stateless，BeaconServer服务支持横向扩展。根据请求量的大小，可以弹性调整服务的规模。?配置动态加载，BeaconServer服务支持动态配置加载。每个模块都支持切换，服务可以动态加载配置，实现在线和离线。比如路由模块可以根据后端加速引擎集群的资源情况调整路由比例甚至熔断。?无缝升级，BeaconServer服务的后端模块可以独立离线升级，不影响Hook端的HS2服务。SQLonHadoop平台使用中遇到的痛点使用新引擎加速面临的问题Hive支持SPARK和TEZ引擎，但不适用于生产环境。SQLonHadoop的SQL引擎各有优缺点，用户学习和使用的门槛比较高。不同的SQL引擎之间在语法和功能支持上存在差异，需要做大量的测试和兼容工作，完全兼容的成本比较高。不同的SQL引擎各自提供的服务会给数据仓库的沿袭管理、权限控制、运维管理、资源利用带来不便。智能引擎解决方案在Hive中，实现自定义引擎。自动路由功能无需设置引擎，自动选择合适的加速引擎。排除规则匹配的SQL，只将兼容的SQL推送到加速引擎。复用HiveServer2集群架构。智能引擎：主流引擎方案对比智能引擎：HiveServer2自定义执行引擎的模块设计基于HiveServer2，有两种实现方式。JDBC方式是通过JDBC接口向后端加速引擎启动的集群发送SQL。PROXY方式是将SQL下推到本地加速引擎启动的客户端。JDBC启动的后端集群都是基于YARN的，可以实现资源的分时复用。例如，AdHoc集群的资源在夜间自动回收，作为报表系统的资源重新利用。智能引擎：SQL路由方案设计架构路由方案基于HS2的Hook架构，在HS2端实现相应的Hook进行引擎切换；路由服务在后端BeaconServer服务中实现，用于SQL路由规则的匹配处理。不同的集群可以配置不同的路由规则。为了保证计算后路由服务的稳定性，团队还设计了RewriteHook，用于重写AdHoc集群中的SQL，自动添加LIMIT上限，防止大数据量的SCAN。智能引擎：SQL路由规则列表智能引擎：方案优势易于集成，目前主流的SQL引擎都可以轻松实现JDBC和PROXY方式。通过配置，可以方便的集成新的查询引擎，如impala、drill等，引擎的自动选择，降低了用户的引擎使用成本，迁移也更加容易。并且在加速发动机超载时，可以动态调整速比，防止超载影响加速性能。自动降级保证运行的可靠性。SQL路由支持failback模块，可以根据配置选择在路由引擎执行失败后是否回滚到MR运行。模块重用。对于新加入的引擎，HiveServer2定制的血统采集、授权认证、并发锁控制等方案可以复用，大大降低了使用成本。资源多路复用，即席查询占用的资源可以分时动态调整，有效保证集群资源的利用率。智能引擎DQL应用效果HiveServer2FetchTask加速中的性能问题：预排序和逻辑优化查询完成后，本地会轮询结果文件，直到得到LIMIT大小，然后返回。在这种情况下，当小文件较多，大文件在后端时，会导致BadCase，不断与HDFS交互，获取文件信息和文件数据，运行时间大大延长。在Fetch之前，预先对结果文件的大小进行排序，可以将性能提升数百倍。示例：当前有200个文件。有199个小文件，一个记录a，一个大文件混合记录a和test，共200条记录。大文件的名称是小文件之后的索引。FetchTask加速：预排序和逻辑优化Hive有一个SimpleFetchOptimizer优化器，会直接生成FetchTask，减少资源申请时间和调度时间。但是这种优化会有一个瓶颈。如果数据量小，文件数多，返回的item多，有过滤条件可以过滤出大量的结果数据。此时串行读取输入文件，导致查询延迟较大，但并没有加速效果。在SimpleFetchOptimizer优化器中加入了文件数量的判断条件，最后将任务提交到集群环境，通过增加并发来实现加速。示例：读取当前分区有500个文件。优化后的文件数阈值为100。大表Desc表优化一张表有大量的子分区，它的DESC过程会与元数据交互获取所有分区。但是最后返回的结果只有表相关的信息。与元数据交互时，整个DESC的查询出现延迟，元数据压力大时甚至无法返回结果。对于TABLE的DESC过程，直接去掉了与元数据交互获取分区的过程，加速时间与子分区数成正比。示例：desc是一个有100,000个分区的大表。其他改进重用split计算出的数据，跳过reduce估计重复统计输入的过程。对于输入数据量大的任务，调度率提升50%。ParquetSerdeinit加速，跳过同一张表的重复列剪枝优化，防止maptaskopinit超时。新增LazyOutputFormat在记录输出后创建文件，避免产生空文件，导致下游读取大量空文件耗时。statsTask支持多线程聚合统计信息，防止中间文件过多导致聚合过慢而增加运行时间。AdHoc需要开启并行编译，防止SQL串行编译导致整体延迟时间增加的问题。SQLonHadoop平台使用中遇到的痛点SQLonHadoop在快手：常见可用性问题HiveServer2服务启动优化HS2启动时，初始化物化视图功能，轮询整个元数据库，导致一个HS2Long启动时间很慢，从离线状态到重新在线的间隔太长，可用性差。修改物化视图功能延迟延迟加载，在单独的线程加载，不影响HS2的服务启动。物化视图支持加载时获取缓存信息，保证功能的可用性。HS2启动时间从5分钟以上增加到<5秒。HiveServer2配置热加载HS2本身下线和下线的成本很高，需要保证服务上的所有任务都完成后才能进行操作。配置修改可以作为更高频率的操作，需要热加载。在HS2的ThriftServer层，我们增加了一个接口。用运维系统打通后，配置下推更新时会自动调用，使配置的热加载生效。HiveServer2的scratchdir优化HiveServer2的scratchdir主要用于运行时的临时文件存储。scratchdir在HS2中创建会话时创建。当HDFS压力大时，大量的session会阻塞创建scratchdir的进程，导致连接数累积到上限。最终导致HS2服务无法连接新连接，影响服务可用性。对此，我们首先将generalquery和createtemporaytablequery的scratch目录分开，支持createtemporaytablequery的scratch的lazycreation。createtemporaytable时创建大量临时文件，会影响HDFSNameNode的延迟时间，一般查询的scratchdirHDFSNameNode可以正常响应。另外，HS2还支持配置多个scratch，不同的scratch可以设置加载比例，从而实现HDFS的负载均衡。HiveStage并发调度异常修复Hive调度存在两个问题。1、当子任务的非执行状态完成后，如果父任务包含该子任务有多轮，则该子任务会被重复加入到调度队列中。在这种情况下，需要将非执行状态修改为初始化状态。2.在判断子任务是否可执行的过程中，由于状态检测异常导致需要调度的子任务无法正常添加，导致查询丢失stage。对于这种情况，我们的做法是在执行完成后增加一轮stage执行结果状态检查。一旦发现下游某个阶段没有完成，直接抛出错误，实现查询结果状态的完整性检查。其他改进HS2实现了用于接口终止查询的SQL。利用该功能可以及时终止异常SQL。MetastoreJDOQuery查询优化，关键字异常跳过，防止元数据长时间卡住或某些异常查询影响元数据。新增开关控制，强制覆盖external目录，解决insertoverwriteexternal和文件重命名报错问题。hiveparquet的下推增加关闭配置，避免parquet异常下推OR条件，导致结果不正确。ExecuteForArray函数加入超大字符串导致OOM，增加限制优化。新增根据表的schema读取分区数据的功能，避免分区schema非级联修改导致的读取数据异常。SQLonHadoop平台使用中遇到的痛点为什么要开发SQL专家系统有些用户没有开发经验，无法处理处理引擎返回的错误报告。有些错误的错误提示不明确，用户无法正确理解错误原因。故障任务排查成本高，需要对整个Hadoop系统非常熟悉。用户错误的SQL和需要优化的SQL有很多共性。人工维护成本高，但系统分析成本低。SQL专家系统SQL专家系统基于HS2的Hook架构，在BeaconServer后端实现了三个主要模块，分别是SQL规则控制模块、SQL错误分析模块、SQL优化建议模块。SQL专家系统的知识库包含关键字、原因解释、处理方案等几项主要信息，这些信息存储在后台数据库中，一直积累。通过SQL专家系统，后台可以控制查询SQL的异常，避免异常SQL造成资源浪费或影响集群的稳定性。当用户遇到问题时，可以直接获取问题的解决方案，降低使用成本。示例：空分区查询控件。作业诊断系统SQL专家系统可以解决部分HS2任务执行错误诊断需求，但作业健康、任务执行异常等问题原因的判断需要专门的系统来解决。为此，我们设计了一个工作诊断系统。在YARN级别，作业诊断系统分析收集到的不同执行引擎的计数器和配置。在执行层面，提出相关优化建议。作业诊断系统的数据也可以通过API提供给SQL专家系统，补充问题原因进行分析。作业诊断系统提供查询页面查询正在运行的任务。以下是打图输入过多规则的任务查询流程：在作业界面，还可以查看更多的作业诊断信息和作业修改建议。SQLonHadoop平台使用中遇到的痛点SQLonHadoop用在快手：运维常见问题审计分析-架构图审计功能也是BeaconServer服务的一个模块。通过HS2中配置的Hook，将需要的SQL、IP、User等信息发送到后端进行语法分析，提取DataBase、Table、Columns和操作信息，分析后存储到Druid系统中。用户可以通过可视化平台查询一些开放数据。审计分析——热点信息查询热点信息查询会展示一段时间内的热点信息，用户的热点操作，包括访问过哪些库，哪些表，操作过哪些类型。审计分析-沿袭信息查询从下图可以看出，沿袭信息体现了一张表创建的上游依赖关系，一般用于统计一张表的影响范围。审计分析-历史操作查询历史操作可以追溯到一段时间内对某个表的操作。可获取操作的用户、客户端、平台、时间等信息。一般用于跟踪表的增删改查。HiveServer2集群AB切换方案由于HiveServer2服务本身下线和下线的成本很高，如果要进行升级操作，往往需要很长时间，影响可用性。HiveServer2集群的AB切换方案主要依靠A集群在线B集群备用的方式，通过切换ZK上在线的集群机器实现无缝升级操作。HiveServer2集群动态上下线。HiveServer2集群部署了Metrics监控，可以实时跟踪集群服务的使用情况。另外我们对HS2服务进行了改造，实现了HS2ZK下线请求Cancel的接口。当外部Monitor检测到连续内存过高时，会自动触发HS2服务进程的FGC操作。如果内存还是过高，会通过ZK直接下线服务，按照查询提交的时间顺序依次停止查询，直到内存恢复，保证服务中剩余任务的正常运行。HiveServer2集群管理平台HiveServer2需要知道多集群状态下各个集群的状态和各个HS2服务。通过管理平台可以查看版本状态、启动时间、资源使用情况、上下线状态。跟进运维平台，可以更方便的进行一键灰度和升级。快手查询平台改进总结04快手SQLonHadoop未来计划专家系统升级、参数自动调优、SQL优化AdHoc查询缓存加速新引擎研究与应用
