hive查询时间复杂度

1、使用Tez引擎

Apache Tez Engine是一个可扩展的框架，用于构建高性能批处理和交互式数据处理。它由YARN在Hadoop中 调度。Tez通过提高处理速度和保持MapReduce扩展到数PB数据的能力来改进MapReduce job。

通过设置hiveexecutionengine 为tez：可以在环境中启用Tez引擎：

set hiveexecutionengine=tez;

2、使用向量化

向量化通过在单个 *** 作中获取 1024 行而不是 每次只获取单行来改善 scans, aggregations, filters 和 join 这类 *** 作的性能。

我们可以通过执行以下命令在环境中启用向量化：

set hivevectorizedexecutionenabled=true;

set hivevectorizedexecutionreduceenabled=true;

3、使用ORCFile

Hive 支持 ORCfile，这是一种新的表存储格式，在读取，写入和处理数据时，ORCFile格式优于Hive文件格式，它通过 predicate push-down, compression 等技术来提高查询速度。

在 HIVE 表中使用 ORCFile，将有益于获得 HIVE 快速响应的查询。

ORCFile 格式通过对原始数据存储量压缩75％，提供了高效的存储 Hive 数据的方法。

举例，考虑两个大表 A 和 B（存储为 TextFIle，这里没有指定一些列），使用一个简单的查询，如：

SELECT AcustomerID,

Aname,

Aage,

Aaddress

JOIN Brole,

Bdepartment,

Bsalary ON AcustomerID=BcustomerID;

由于表 A 和表 B 都存储为 TextFile，因此执行此查询可能需要很长时间。

将这些表存储格式转换为 ORCFile 格式通常会明显减少查询时间：

CREATE TABLE A_ORC (

customerID int,

name string,

age int,

address string

) STORED AS ORC tblproperties (“orccompress" = “SNAPPY”)

;

INSERT INTO TABLE A_ORC

SELECT

FROM A

;

CREATE TABLE B_ORC (

customerID int,

ROLE string,

salary float,

department string

) STORED AS ORC tblproperties (“orccompress" = “SNAPPY”)

;

INSERT INTO TABLE B_ORC

SELECT

FROM B

;

SELECT A_ORCcustomerID,

A_ORCname,

A_ORCage,

A_ORCaddress

JOIN B_ORCrole,

B_ORCdepartment,

B_ORCsalary ON A_ORCcustomerID=B_ORCcustomerID

;

ORC 支持压缩存储（使用 ZLIB 或如上所示使用 SNAPPY），但也支持不压缩存储。

4、使用分区

通过分区，数据存储在 HDFS 上的单独单个文件夹中。Hive 将查询分区数据集，而不是 扫描表的所有数据集。

创建临时表并将数据加载到临时表中

CREATE TABLE Employee_Temp(

EmloyeeID int,

EmployeeName Varchar(100),

Address Varchar(100),

STATE Varchar(100),

City Varchar(100),

Zipcode Varchar(100)

) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE;

LOAD DATA INPATH '/home/hadoop/hive' INTO TABLE Employee_Temp;

创建分区表

Create Table Employee_Part(

EmloyeeID int,

EmployeeName Varchar(100),

Address Varchar(100),

State Varchar(100),

Zipcode Varchar(100))

PARTITIONED BY (City Varchar(100))

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

STORED AS TEXTFILE;

启用动态分区的命令

SET hiveexecdynamicpartition = true;

SET hiveexecdynamicpartitionmode = nonstrict;

从临时表导入数据到分区表

INSERT Overwrite TABLE Employee_Part Partition(City)

SELECT EmployeeID,

EmployeeName,

Address,

STATE,

City,

Zipcode

FROM Emloyee_Temp;

5、使用 分桶

桶表介绍：>

说明：hive 的表存放位置模式是由 hive-sitexml 当中的一个属性指定的，默认是存放在该配置文件设置的路径下，也可在创建数据库时单独指定存储路径。

数据库有一些描述性的属性信息，可以在创建时添加：

查看数据库的键值对信息

修改数据库的键值对信息

与mysql查询语句是一样的语法

删除一个空数据库，如果数据库下面有数据表，那么就会报错

强制删除数据库，包含数据库下面的表一起删除(请谨慎 *** 作)

[]里的属性为可选属性，不是必须的，但是如果有可选属性，会使 sql 语句的易读性更好，更标准与规范。

例如：[comment '字段注释信息'][comment '表的描述信息']等，[external]属性除外

1 CREATE TABLE

创建一个指定名字的表，如果相同名字的表已存在，则抛出异常提示：表已存在，使用时可以使用IF NOT EXISTS语句来忽略这个异常。

如果创建的表名已存在，则不会再创建，也不会抛出异常提示：表已存在。否则则自动创建该表。

2 EXTERNAL

顾名思义是外部的意思，此关键字在建表语句中让使用者可以创建一个外部表，如果不加该关键字，则默认创建内部表。

外部表在创建时必须同时指定一个指向实际数据的路径（LOCATION），Hive在创建内部表时，会将数据移动到数据仓库指向的路径；

若创建外部表，仅记录数据所在的路径，不对数据的位置作任何改变。

内部表在删除后，其元数据和数据都会被一起删除。

外部表在删除后，只删除其元数据，数据不会被删除。

3 COMMENT

用于给表的各个字段或整张表的内容作解释说明的，便于他人理解其含义。

4 PARTITIONED BY

区分表是否是分区表的关键字段，依据具体字段名和类型来决定表的分区字段。

5 CLUSTERED BY

依据column_name对表进行分桶，在 Hive 中对于每一张表或分区，Hive 可以通过分桶的方式将数据以更细粒度进行数据范围划分。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

6 SORTED BY

指定表数据的排序字段和排序规则，是正序还是倒序排列。

7 ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '

指定表存储中列的分隔符，这里指定的是' '，也可以是其他分隔符。

8 STORED AS SEQUENCEFILE|TEXTFILE|RCFILE

指定表的存储格式，如果文件数据是纯文本格式，可以使用STORED AS TEXTFILE，如果数据需要压缩，则可以使用STORED AS SEQUENCEFILE。

9 LOCATION

指定 Hive 表在 hdfs 里的存储路径，一般内部表（Managed Table）不需要自定义，使用配置文件中设置的路径即可。

如果创建的是一张外部表，则需要单独指定一个路径。

1 使用create table语句创建表

例子：

2 使用create table as select语句创建表

例子：

使用 create table as select 语句来创建新表sub_student，此时sub_student 表的结构及表数据与 t_student 表一模一样， 相当于直接将 t_student 的表结构和表数据复制一份到 sub_student 表。

注意：

(1) select 中选取的列名（如果是 则表示选取所有列名）会作为新表 sub_student 的列名。

(2) 该种创建表的方式会改变表的属性以及结构，例如不能是外部表，只能是内部表，也不支持分区、分桶。

如果as select后的表是分区表，并且使用select ，则分区字段在新表里只是作为字段存在，而不是作为分区字段存在。

在使用该种方式创建时，create 与 table 之间不能加 external 关键字，即不能通过该种方式创建外部目标表，默认只支持创建内部目标表。

(3) 该种创建表的方式所创建的目标表存储格式会变成默认的格式textfile。

3使用like语句创建表

例子：

注意：

(1) 只是将 t_student 的表结构复制给 sub1_student 表。

(2) 并不复制 t_student 表的数据给 sub1_student 表。

(3) 目标表可以创建为外部表，即:

用到from_unixtime和unix_timestamp两种函数：

from_unixtime：时间戳转日期函数

用法：from_unixtime(bigint unixtime[, stringformat])

返回值: string

substr(from_unixtime(unix_timestamp()),1,10)

结果为：2017-01-03

select from_unixtime(unix_timestamp('20180905','yyyymmdd'),'yyyy-mm-dd')

from dwceshi_data

结果如下：

2018-09-05

2018-09-05转成20180905

select from_unixtime(unix_timestamp('2018-09-05','yyyy-mm-dd'),'yyyymmdd')

from dwceshi_data

结果如下：

20180905

用法:unix_timestamp(string date)

注意：里面格式必须是yyyy-MM-dd HH:mm:ss，如果不是会返回null值

返回值: bigint

from dwceshi_data;

结果如下：

1536120063

获取当前日期的时间戳：

select unix_timestamp()

from dwceshi_data;

结果如下：

1536126324

hive表中，存放着无法直接识别的字符串格式的时间，如'20170728102031',要计算两个时间相差的秒数。

1、先将字符串调整为hive可以识别的格式，即将形如'20170728102031' 转成 '2017-07-28 10:20:31'。 因为hive的 regexp_replace 不支持子语句，没法一次转换，只能用万能的 substr 和拼接函数来写了

select concat(substr('20170728102031',1,4),'-',

substr('20170728102031',5,2),'-',

substr('20170728102031',7,2),' ',

substr('20170728102031',9,2),':',

substr('20170728102031',11,2),':',

substr('20170728102031',13,2))

select unix_timestamp(concat(substr('20170728102031',1,4),'-',substr('20170728102031',5,2),'-',

substr('20170728102031',7,2),' ',

substr('20170728102031',9,2),':',

substr('20170728102031',11,2),':',

substr('20170728102031',13,2))) - unix_timestamp(concat(substr('20170728112031',1,4),'-',

substr('20170728112031',5,2),'-',

substr('20170728112031',7,2),' ',

substr('20170728112031',9,2),':',

substr('20170728112031',11,2),':',

substr('20170728112031',13,2)))

hive > select create_time ,datediff(from_unixtime(unix_timestamp(),’yyyy-MM-dd HH:mm:ss’), create_time) from test;

结果：当前时间是2017-11-16 与create_time的11-10之间差了6天，输出6；

hive >select datediff(’2012-12-08′,’2012-05-09′) from dual;

213

日期时间转日期函数: to_date语法: to_date(string timestamp) 返回：string

hive> select to_date(’2011-12-08 10:03:01′) from dual;

2011-12-08

2011

以下可以取到 month，hour，minute，second 用法和上面的一样

日期转周函数: weekofyear语法: weekofyear (string date) 返回值: int 说明: 返回日期在当前的周数。

hive> select weekofyear(’2011-12-08 10:03:01′) from dual;

49

日期增加函数: date_add语法:

date_add(string startdate, int days)

返回值: string

说明: 返回开始日期startdate增加days天后的日期

举例：

hive> select date_add(’2012-12-08′,10) from dual;

2012-12-18

日期减少函数: date_sub语法: date_sub (string startdate, int days)

返回值: string

说明: 返回开始日期startdate减少days天后的日期。

举例：

hive> select date_sub(’2012-12-08′,10) from dual;

2012-11-28

所以我们利用其中的hour和datediff来获取create_time与当前时间的小时差：

hive> select create_time,

(hour(from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'))-

hour(create_time)+(datediff(from_unixtime(unix_timestamp(),

'yyyy-MM-dd HH:mm:ss'), create_time))24) as hour_dValue

Hive用的好，才能从数据中挖掘出更多的信息来。用过hive的朋友，我想或多或少都有类似的经历：一天下来，没跑几次hive，就到下班时间了。Hive在极大数据或者数据不平衡等情况下，表现往往一般，因此也出现了presto、spark-sql等替代品。这里重点讲解hive的优化方式，例如

一 表连接优化

二 用insert into替换union all

如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%。示例参考如下：

可以改写为：

三 order by & sort by

order by : 对查询结果进行全局排序消耗时间长,需要set hivemapredmode=nostrict

sort by : 局部排序，并非全局有序，提高效率。

四 transform+python

一种嵌入在hive取数流程中的自定义函数，通过transform语句可以把在hive中不方便实现的功能在python中实现，然后写入hive表中。示例语法如下：

如果除python脚本外还有其它依赖资源，可以使用ADD ARVHIVE。

五 limit 语句快速出结果

一般情况下，Limit语句还是需要执行整个查询语句，然后再返回部分结果。有一个配置属性可以开启，避免这种情况—对数据源进行抽样

缺点：有可能部分数据永远不会被处理到

六 本地模式

对于小数据集，为查询触发执行任务消耗的时间>实际执行job的时间，因此可以通过本地模式，在单台机器上（或某些时候在单个进程上）处理所有的任务。

可以通过设置属性hiveexecmodelocalauto的值为true，来让Hive在适当的时候自动启动这个优化，也可以将这个配置写在$HOME/hiverc文件中。

当一个job满足如下条件才能真正使用本地模式：

七 并行执行

Hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。 不过，如果某些阶段不是互相依赖，是可以并行执行的。

会比较耗系统资源。

八 调整mapper和reducer的个数

假设input目录下有1个文件a,大小为780M,那么hadoop会将该文件a分隔成7个块（6个128m的块和1个12m的块），从而产生7个map数

假设input目录下有3个文件a,b,c,大小分别为10m，20m，130m，那么hadoop会分隔成4个块（10m,20m,128m,2m），从而产生4个map数。

即如果文件大于块大小(128m),那么会拆分，如果小于块大小，则把该文件当成一个块。

map执行时间：map任务启动和初始化的时间+逻辑处理的时间。

减少map数

若有大量小文件（小于128M），会产生多个map，处理方法是：

前面三个参数确定合并文件块的大小，大于文件块大小128m的，按照128m来分隔，小于128m,大于100m的，按照100m来分隔，把那些小于100m的（包括小文件和分隔大文件剩下的）进行合并。

set hiveinputformat=orgapachehadoophiveqlioCombineHiveInputFormat; – 执行前进行小文件合并。

增加map数

当input的文件都很大，任务逻辑复杂，map执行非常慢的时候，可以考虑增加Map数，来使得每个map处理的数据量减少，从而提高任务的执行效率。

set mapredreducetasks=

一般根据输入文件的总大小,用它的estimation函数来自动计算reduce的个数：reduce个数 = InputFileSize / bytes per reducer

九 严格模式

十 数据倾斜

表现：

任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。单一reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最长时长远大于平均时长。

原因：

解决方案：参数调节

在过去几年中，主要受到围绕Stinger计划的Hive社区创新的推动，Hive查询时间得到了显着改善，使Hive能够以速度和规模支持批量和交互式工作负载。

但是，许多使用者仍然不熟悉以最快速度运行Hive查询的基本技术和最佳实践。本文中，将重点介绍一些常使用的简单技术，以提高HIVE查询的性能。

Hive可以使用Apache Tez执行引擎而不是Map-reduce引擎。不会详细介绍这里提到的使用Tez的许多好处; 相反，提出一个简单的建议：如果在您的环境中默认情况下没有打开它，请在Hive查询的开头使用Tez设置为“true”

Hive支持ORCfile，这是一种新的表存储格式，通过谓词下推，压缩等技术实现极佳的速度提升。

对每个HIVE表使用ORCFile应该是一个明智的选择，对于获得HIVE查询的快速响应时间非常有益。

作为一个例子，考虑两个大表A和B（存储为文本文件，这里没有指定一些列），以及一个简单的查询 :

此查询可能需要很长时间才能执行，因为表A和B都存储为TEXT。将这些表转换为ORCFile格式通常会显着缩短查询时间：

ORC支持压缩存储（使用ZLIB或如上所示使用SNAPPY），但也支持未压缩存储。

将基表转换为ORC通常是取决于所在团队获取数据的职责，由于其他优先级，可能需要一些时间来更改完整的获取数据过程。ORCFile的好处是如此明显，以至于推荐如上所示的自助式方法 - 将A转换为A_ORC，将B转换为B_ORC并以此方式进行连接，以便立即从更快的查询中受益，而不依赖于其他团队。

矢量化查询执行通过一次批量执行1024行而不是每行一行来提高扫描，聚合，过滤器和连接等 *** 作的性能。

这个功能在Hive 013中引入，显着缩短了查询执行时间，并且可以通过两个参数设置轻松启用：

在提交最终执行之前，Hive会优化每个查询的逻辑和物理执行计划。这些优化不是基于查询的成本 - 也就是说，直到运行时。

最近添加到Hive，基于成本的优化，基于查询成本执行进一步优化，从而导致可能不同的决策：如何订购联接，执行哪种类型的联接，并行度等。

要使用基于成本的优化（也称为CBO），请在查询开头设置以下参数

然后，通过运行Hive的“analyze”命令为CBO准备数据，以收集我们想要使用CBO的表的各种统计信息。

例如，在tweet数据表中，希望收集有关该表的统计信息以及大约2列：“sender”和“topic”：

使用HIVE 014（在HDP 22上），analyze命令的工作速度要快得多，而且您不需要指定每一列，因此只需如下：

现在使用此表执行查询应该会导致不同的执行计划由于成本计算和Hive创建的不同执行计划而更快。

SQL是一种强大的声明性语言。与其他声明性语言一样，编写SQL语句的方法不止一种。尽管每个语句的功能都相同，但它可能具有截然不同的性能特征

每条记录代表一次点击事件，希望找到每个sessionID的最新网址。

有人使用如下方式：

在上面的查询中，构建一个子查询来收集每个会话中最新事件的时间戳，然后使用内部联接来过滤掉其余的事件。

虽然查询是一个合理的解决方案 - 从功能的角度来看 - 事实证明，有一种更好的方法来重写这个查询，如下所示

在这里，使用Hive的OLAP功能（OVER和RANK）来实现相同的功能，但没有使用表连接。

显然，删除不必要的连接几乎总能带来更好的性能，而且当使用大数据时，这比以往任何时候都更重要。在很多情况下查询不是最优的 - 所以仔细查看每个查询并考虑重写是否可以使它更好更快。

更多内容信息 >

   hive中有两种表：外部表和内部表（managed and external）。可以通过 desc formatted table_name 命令来查看表的信息，来辨别表是外部表还是内部表。 在hive默认创建到表是内部表，外部表创建需要加 EXTERNAL 命令，如： CREATE EXTERNAL table_name 。

   内部表的文件，元数据和统计信息等由hive进行管理，一般被存储在 hivemetastorewarehousedir 目录下，当表被删除或者分区被删除，相对应的数据和元数据就会被删除。一般用来当做临时表。

外部表与内部表相反，可以指定location，可以不基于hive来 *** 作外部表文件。当表被删除或者分区被删除时对应的数据还会存在。只是hive删除了其元信息,表的数据文件依然存在于文件系统中。若是表被删除，可以重新建这个表，指定location到数据文件处，然后通过msck repair table table_name命令刷新数据的元信息到hive中，也就是恢复了数据。

   msck repair table 的详细用法就不讲了，可以参考 HIVE常用命令之MSCK REPAIR TABLE命令简述

以上就是关于hive查询时间复杂度全部的内容，包括:hive查询时间复杂度、[hive]一种基于Hive日志分析的大数据存储优化方法_王正也_百度文库、Hive 数据库表的基本 *** 作，必须掌握的基本功等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！