数据仓库【SQL优化】

这篇具有很好参考价值的文章主要介绍了数据仓库【SQL优化】。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、SQl优化背景

        每个sqlboy工作一段时间后都会面临这一个能力提升问题--Sql优化。本文通过更通俗易懂的话术讲解sql优化的策略内容。提供一个从业务需求探查到最终上线全流程的sql优化方案,如有缺失,希望大家多多交流补充。

数据仓库【SQL优化】,数据仓库之Hive,数据仓库,数据仓库,sql,数据库开发,大数据

二、SQL优化策略

  1. 数据探查阶段
    1. 业务探查:了解业务热点数据
    2. 数据建表:合理分区、分桶
  2. 数据开发阶段:较少输入,避免倾斜
    1. 分区裁剪和列裁剪:减少数据范围
    2. 大小表关联:使用mapJoin,在map端聚合,减少reduce压力
    3. 两大表关联:设计分桶策略,避免数据倾斜
    4. 关联条件类型统一:关联字段类型不一致,会导致数据倾斜
    5. group by 替代distinct:避免一个reduce处理所有的数据
    6. sort by 替代order by:避免全量排序,禁止不必要的Order by排序
    7. 尽量避免使用子查询语句,用in替换or,
    8. 合理使用union all(不去重)替代union(去重需要遍历、排序比较)
  3. 数据治理阶段:到达数据治理阶段一般都是由于数据倾斜导致任务执行时间长或者占用计算资源多。
    1. 缓解策略:加大资源调用,包括调节Map个数、Reduce个数以及每个reduce处理数据量的大小,或者直接增加计算资源(CPU和内存资源),这种策略只是暂时环节了数据倾斜现象,没有从根本上解决问题,可以做个临时策略;
    2. 根除策略:首先跟业务沟通确认,如果是无效数据,可以直接过滤删除;如果是少量固定key倾斜,可以单独做数据处理后合并;最后将倾斜key拼接随机数,人为打散做二次聚合处理,避免数据倾斜单个renduce处理大量数据。
  4. 参数优化:参数优化在日常开发过程中建议积累一个专门的文档,做好标注,遇到问题做针对性条数优化即可
    1. 大小表关联:Map Join,小表太大会造成内存压力
      1.  --启动Map Join自动转换 
         set hive.auto.convert.join=true;
         --开启无条件转Map Join 
         set hive.auto.convert.join.noconditionaltask=true;
         --无条件转Map Join小表阈值,默认值10M,推荐设置为Map Task总内存的三分之一到二分之一 
         set hive.auto.convert.join.noconditionaltask.size=10000000;
        
    2. 大表与大表关联:设计分桶表关联
      1. 适合条件:量表均为分桶表,且关联字段为分桶字段;量表分桶呈倍数关系;分桶内字段是有序的
      2. set hive.optimize.bucketmapjoin.sortedmerge = true;  
        --使用排序合并算法来执行Bucket Map Join操作。排序合并算法可以在两个已经按照分桶列排序的表之间进行连接操作,而无需进行全局排序。这样可以减少排序的开销,提高查询效率
        set hive.optimize.bucketmapjoin = true;
        --它通过将两个或多个表的数据按照相同的桶列进行分桶,并将桶中的数据放在同一个Map任务中进行处理,从而减少了数据的传输和处理开销。
        set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; 
        
    3. 调节reduce并行度:增加reduce的个数
      1.  - -指定Reduce端并行度,默认值为 1,表示用户未指定 
          set mapreduce.job.reduces; 
         - -Reduce端并行度最大值 
          set hive.exec.reducers.max; 
         - -单个Reduce Task计算的数据量,用于估算Reduce并行度
          set hive.exec.reducers.bytes.per.reducer;  --默认1G
        
    4. 小文件合并:小文件过多会造成启动很多reduce但是处理的数据量很少,浪费计算资源
      1. --开启合并map only任务输出的小文件
        set hive.merge.mapfiles=true;
        
        --开启合并map reduce任务输出的小文件
        set hive.merge.mapredfiles=true;
        
        --合并后的文件大小
        set hive.merge.size.per.task=256000000;
        
        --触发小文件合并任务的阈值,若某计算任务输出的文件平均大小低于该值,则触发合并
        set hive.merge.smallfiles.avgsize=16000000;
        
        
    5. 并行计算:没有依赖的Stage可以并行执行,提高计算效率,优点类似Spark的DAG内部窄依赖
      1. --启用并行执行优化,默认是关闭的
        set hive.exec.parallel=true;       
            
        --同一个sql允许最大并行度,默认为8
        set hive.exec.parallel.thread.number=8; 
        
        
    6. CBO优化:类似有向无环图DAG,计算最优执行路径;
      1. --是否启用cbo优化 
        set hive.cbo.enable=true;
        
    7. 建表:列式存储+压缩格式
      1. CREATE TABLE A_T1 ( 
        user_id int, 
        user_name string,
        user_age int, 
        address string 
        ) STORED AS ORC 
        tblproperties (“orc.compress" = “SNAPPY”)
        
    8. 更换计算引擎:Hive、Spark、Tez不同的计算引擎有一定的场景区别
      1. Hive_MR:单纯数据读取、不涉及逻辑加工;数据量大,计算步骤少逻辑简单;运算时间超过90分钟;多次读取同一张大表的任务;
      2. Spark:生成结果集较小;运行时间在40分钟以内的;切分job较多的;需要复杂算子的。
      3. Tez:使用DAG描述认为,减少不必要的MR中间点,减少磁盘IO;在运行过程中可以动态调整任务并行度。

小结

        在编写Hive SQL时,优化是至关重要的。首先要合理设计表结构和分区、分桶,以提高查询效率;其次,使用何时的数据类型和索引,减少数据存储和查询的开销;此外,避免使用不必要的JOIN操作和子查询,尽量使用内置函数和聚合函数来简化查询;最后,通过设置合适的并行度和资源配置,提高作业的执行速度。

        记住,持续优化是一个迭代的过程,不断地调整和改进SQL语句,以获得更好地性能。内化于心,不断成长~文章来源地址https://www.toymoban.com/news/detail-831630.html

到了这里,关于数据仓库【SQL优化】的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 数据库优化:探索 SQL 中的索引

    推荐:使用 NSDT场景编辑器 助你快速搭建可编辑的3D应用场景 在一本书中搜索特定主题时,我们将首先访问索引页面(该页面位于该书的开头),并找到包含我们感兴趣的主题的页码。现在,想象一下在没有索引页的书中找到特定主题是多么不方便。为此,我们必须搜索书中

    2024年02月14日
    浏览(70)
  • 【数据库】sql优化有哪些?从query层面和数据库层面分析

    这类型问题可以称为:Query Optimization,从清华AI4DB的paper list中,该类问题大致可以分为: Query Rewriter Cardinality Estimation Cost Estimation Plan Optimization 从中文的角度理解那就是: 查询重写 基数估计 成本估计 执行计划优化 可以发现,这类型的优化问题,大多数从sql本身,或者说从

    2024年01月17日
    浏览(54)
  • 百万级sql server数据库优化案例分享

            在我们的IT职业生涯中,能有一次百万级的数据库的优化经历是很难得的,如果你遇到了恭喜你,你的职业生涯将会更加完美,如果你遇到并解决了,那么一定足够你炫耀很多年。         这里我将要分享一次完美的百万级数据库优化经历,希望能给在IT行业的小

    2024年02月17日
    浏览(76)
  • 玩转MySQL数据库之SQL优化之慢查询

    本系列为:MySQL数据库详解,为千锋资深教学老师独家创作,致力于为大家讲解清晰MySQL数据库相关知识点,含有丰富的代码案例及讲解。如果感觉对大家有帮助的话,可以【关注】持续追更~ 文末有本文重点总结,技术类问题,也欢迎大家和我们沟通交流! 从今天开始本系列

    2024年02月06日
    浏览(96)
  • MySQL数据库第十四课--------sql优化---------层层递进

    🎂 ✨✨✨✨✨✨🍧🍧🍧🍧🍧🍧🍧🎂 ​🎂 作者介绍: 🎂🎂 🎂 🎉🎉🎉🎉🎉🎉🎉 🎂 🎂作者id:老秦包你会, 🎂 简单介绍:🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂🎂 喜欢学习C语言和python等编程语言,是一位爱分享的博主,有兴趣的小可爱可以来互讨 🎂🎂

    2024年02月12日
    浏览(74)
  • 【SQL Server】数据库开发指南(一)数据库设计

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2023年04月08日
    浏览(92)
  • 【Python开发】FastAPI 10:SQL 数据库操作

    在 FastAPI 中使用 SQL 数据库可以使用多个 ORM 工具,例如 SQLAlchemy、Tortoise ORM 等,类似 Java 的 Mybatis 。这些 ORM 工具可以帮助我们方便地与关系型数据库进行交互,如 MySQL 、PostgreSQL等。本篇文章将介绍如何使用 SQLAlchemy 来完成数据库操作,以便让我们在 FastAPI 项目中方便地进

    2024年02月14日
    浏览(42)
  • 使用DataX工具连接hive数据库:java.sql.SQLException: Could not establish connection to jdbc:hive2://master:1000

    Code:[DBUtilErrorCode-10], Description:[连接数据库失败. 请检查您的 账号、密码、数据库名称、IP、Port或者向 DBA 寻求帮助(注意网络环境).]. - 具体错误信息为:java.sql.SQLException: Could not establish connection to jdbc:hive2://master:10000/datax: Required field \\\'serverProtocolVersion\\\' is unset! Struct:TOpenSessionRe

    2024年04月09日
    浏览(73)
  • 【SQL Server】数据库开发指南(二)MSSQL数据库开发对于库、表、数据类型、约束等相关操作

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2023年04月09日
    浏览(354)
  • 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤

    本系列博文还在更新中,收录在专栏:#MS-SQL Server 专栏中。 本系列文章列表如下: 【SQL Server】 Linux 运维下对 SQL Server 进行安装、升级、回滚、卸载操作 【SQL Server】数据库开发指南(一)数据库设计的核心概念和基本步骤 【SQL Server】数据库开发指南(二)MSSQL数据库开发对

    2024年02月09日
    浏览(74)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包