search
HomeDatabaseMysql TutorialHive SQL解析/执行计划生成流程分析

Hive SQL解析/执行计划生成流程分析

Jun 07, 2016 pm 04:29 PM
hivesqlanalyzeimplementprocessgenerateparseplan

最近在研究Impala,还是先回顾下Hive的SQL执行流程吧。 Hive有三种用户接口: cli (Command line interface) bin/hive或bin/hive –service cli 命令行方式(默认) hive-server/hive-server2 bin/hive –service hiveserver 或bin/hive –service hiveserve

最近在研究Impala,还是先回顾下Hive的SQL执行流程吧。

Hive有三种用户接口:

cli (Command line interface) bin/hive或bin/hive –service cli 命令行方式(默认)
hive-server/hive-server2 bin/hive –service hiveserver 或bin/hive –service hiveserver2 通过JDBC/ODBC和Thrift访问(Impala通过这种方式借用hive-metastore)
hwi (Hive web interface) bin/hive –service hwi 通过浏览器访问

在hive shell中输入“show tables;”实际执行的是:

bin/hadoop jar hive/lib/hive-cli-0.9.0.jar org.apache.hadoop.hive.cli.CliDriver -e 'SHOW TABLES;'

CLI入口函数:cli.CliDriver.main()

读入参数->建立SessionState并导入配置->处理输入文件中指令CliDriver.processFile();或交互型指令CliDriver.processLine()->解析输入CliDriver.processCmd()

(1) 如果是quit或者exit,退出

(2) 以source开头的,读取外部文件并执行文件中的HiveQL

(3) !开头的命令,执行操作系统命令(如!ls,列出当前目录的文件信息)

(4) list,列出jar/file/archive

(5) 其他命令,则生成调用相应的CommandProcessor处理,进入CliDriver.processLocalCmd()

下面看看CliDriver.processLocalCmd()这个函数:

set/dfs/add/delete指令交给指定的CommandProcessor处理,其余的交给org.apache.hadoop.hive.ql.Driver.run()处理

org.apache.hadoop.hive.ql.Driver类是查询的起点,run()方法会先后调用compile()和execute()两个函数来完成查询,所以一个command的查询分为compile和execute两个阶段。

Compile

(1)利用antlr生成的HiveLexer.java和HiveParser.java类,将HiveQL转换成抽象语法树(AST)。

首先使用antlr工具将srcqlsrcjavaorgapachehadoophiveqlparsehive.g编译成以下几个文件:HiveParser.java,?Hive.tokens,?Hive__.g,?HiveLexer.java

HiveLexer.java和HiveParser.java分别是词法和语法分析类文件,Hive__.g是HiveLexer.java对应的词法分析规范,Hive.tokens定义了词法分析后所有的token。

然后沿着“Driver.compile()->ParseDriver.parse(command, ctx)->HiveParserX.statement()->antlr中的API”这个调用关系把输入的HiveQL转化成ASTNode类型的语法树。HiveParserX是由antlr生成的HiveParser类的子类。

(2)利用对应的SemanticAnalyzer类,将AST树转换成Map-reduce task。主要分为三个步骤:

a) AST -> operator DAG

b) optimize operator DAG

c) oprator DAG -> Map-reduce task

首先接着上一步生成的语法树ASTNode,?SemanticAnalyzerFactory会根据ASTNode的token类型生成不同的SemanticAnalyzer (所有这些SemanticAnalyzer都继承自BaseSemanticAnalyzer)

1) ExplainSemanticAnalyzer

2) LoadSemanticAnalyzer

3) ExportSemanticAnalyzer

4) DDLSemanticAnalyzer

5) FunctionSemanticAnalyzer

6) SemanticAnalyzer

然后调用BaseSemanticAnalyzer.analyze()->BaseSemanticAnalyzer. analyzeInternal()。

下面以最常见的select * from table类型的查询为例,进入的子类是SemanticAnalyzer. analyzeInternal(),这个函数的逻辑如下:

1) doPhase1():将sql语句中涉及到的各种信息存储起来,存到QB中去,留着后面用。

2) getMetaData():获取元数据信息,主要是sql中涉及到的 表 和 元数据 的关联

3) genPlan():生成operator tree/DAG

4) optimize:优化,对operator tree/DAG 进行一些优化操作,例如列剪枝等(目前只能做rule-based optimize,不能做cost-based optimize)

5) genMapRedTasks():将operator tree/DAG 通过一定的规则生成若干相互依赖的MR任务

Execute

将Compile阶段生成的task信息序列化到plan.xml,然后启动map-reduce,在configure时反序列化plan.xml

实例分析:

在hive中有这样一张表:

uid

fruit_name

count

a

apple

5

a

orange

3

a

apple

2

b

banana

1

执行如下的查询:

SELECT uid, SUM(count) FROM logs GROUP BY uid

通过explain命令可以查看执行计划:

EXPLAIN SELECT uid, SUM(count) FROM logs GROUP BY uid;

依照hive.g的语法规则,生成AST如下:

ABSTRACT SYNTAX TREE:
(
  TOK_QUERY
  (TOK_FROM (TOK_TABREF (TOK_TABNAME logs)))
  (
    TOK_INSERT
    (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE))
    (
      TOK_SELECT
      (TOK_SELEXPR (TOK_TABLE_OR_COL uid))
      (TOK_SELEXPR (TOK_FUNCTION sum (TOK_TABLE_OR_COL count)))
    )
    (TOK_GROUPBY (TOK_TABLE_OR_COL uid))
  )
)

生成的执行计划operator tree/DAG如下:

STAGE DEPENDENCIES:
  Stage-1 is a root stage
  Stage-0 is a root stage
STAGE PLANS:
  Stage: Stage-1
    Map Reduce
      Alias -> Map Operator Tree:
        logs
          TableScan // 扫描表
            alias: logs
            Select Operator //选择字段
              expressions:
                    expr: uid
                    type: string
                    expr: count
                    type: int
              outputColumnNames: uid, count
              Group By Operator //在map端先做一次聚合,减少shuffle数据量
                aggregations:
                      expr: sum(count) //聚合函数
                bucketGroup: false
                keys:
                      expr: uid
                      type: string
                mode: hash
                outputColumnNames: _col0, _col1
                Reduce Output Operator //输出key,value给reduce
                  key expressions:
                        expr: _col0
                        type: string
                  sort order: +
                  Map-reduce partition columns:
                        expr: _col0
                        type: string
                  tag: -1
                  value expressions:
                        expr: _col1
                        type: bigint
      Reduce Operator Tree:
        Group By Operator
          aggregations:
                expr: sum(VALUE._col0) //聚合
          bucketGroup: false
          keys:
                expr: KEY._col0
                type: string
          mode: mergepartial
          outputColumnNames: _col0, _col1
          Select Operator //选择字段
            expressions:
                  expr: _col0
                  type: string
                  expr: _col1
                  type: bigint
            outputColumnNames: _col0, _col1
            File Output Operator //输出到文件
              compressed: false
              GlobalTableId: 0
              table:
                  input format: org.apache.hadoop.mapred.TextInputFormat
                  output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
  Stage: Stage-0
    Fetch Operator
      limit: -1

Hive优化策略:

1. 去除查询中不需要的column

2. Where条件判断等在TableScan阶段就进行过滤

3. 利用Partition信息,只读取符合条件的Partition

4. Map端join,以大表作驱动,小表载入所有mapper内存中

5. 调整Join顺序,确保以大表作为驱动表

6. 对于数据分布不均衡的表Group by时,为避免数据集中到少数的reducer上,分成两个map-reduce阶段。第一个阶段先用Distinct列进行shuffle,然后在reduce端部分聚合,减小数据规模,第二个map-reduce阶段再按group-by列聚合。

7. 在map端用hash进行部分聚合,减小reduce端数据处理规模。

参考文献:

http://fatkun.com/2013/01/hive-group-by.html

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Atom editor mac version download

Atom editor mac version download

The most popular open source editor

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor