Hive-HQL数据定义DML

young2simple

墨衍会员 · AI 创作全网分发码龄11年

关注

原创最新推荐文章于 2026-03-14 01:39:51 发布 521 阅读 AI 写同款· GEO 优化 ›

Hive中json 字符串解析之 get_json_object 与 json_tuple hive 中 json 字符串解析阅读详情

小技巧说明：

在Hive Cli中，可以在！后加一些简单的linux命令执行，如clear、cat、ls等；

向管理表中load数据：

load data [local] inpath '${env:HOME}/test_file_dir' overwirte into table test_table partition (...);

路径可以为一个文件，或者一个目录，如果是目录，Hive会将该目录下所有文件都加载到hdfs中，但是该目录下不可以再包含任何子目录；

注意：

如果没有local关键字，Hive会将hdfs中的数据转移到目标位置，注意是转移，可能考虑到分布式文件系统不需要保存多份数据；同时，必须保证源文件和目标地址应该在同一个hdfs集群中。

Hive并不会验证用户load的数据和表的schema是否匹配，但是Hive会验证文件格式是否和表定义的一致。

通过查询语句向表中插入数据：

insert overwirte table test_table partition (...) select * from other_table a where a...

另一种高效的load方式，只扫描一遍表：

from other_table a

insert overwrite table test_table1 partition (...) select * where a...

insert overwrite table test_table2 partition (...) select * where a...

insert overwrite table test_table3 partition (...) select * where a...

动态分区插入：

insert overwrite table test_table

partition (pt_key1, pt_key2)

select ..., a.key1, a.key2

from other_table a;

Hive会根据select语句的最后两列来确定分区字段的值，在实际应用中静态分区和动态分区可以混合使用，但是静态分区键必须在动态分区键之前。

默认情况下动态分区功能未开启，开启后采用strict模式执行，下面列出经常使用的动态分区属性：

hive.exec.dynamic.partition=true; 开启动态分区

hive.exec.dynamic.partition.mode=strict; strict模式，至少有一列分区字段是静态的

hive.exec.max.dynamic.partitions.pernode=100; 每个map或reduce可以创建的最大分区个数

hive.exec.max.dynamic.partitions=1000; 一个动态分区创建语句可以创建的最大动态分区数

hive.exec.max.created.fields=100000; 全局可以创建的最大文件个数

查询语句创建表并加载数据：

create table test_table as select * from other_table where ...;

导出数据：

如果数据文件恰好是用户需要的，直接拷贝hdfs文件即可：hadoop fs -cp src_path target_path

也可以查询之后重定向到本地文件中；

或者：

insert overwrite [local] directory 'path' select * from test_table where ...;

from test_table a

insert overwrite directory 'path_one' select * where ...

insert overwrite directory 'path_another' select * where ...

注意：

在Hive0.10.0之后上述方式可以定制输出文件的格式：

insert overwrite directory 'path' row format delimited fields terminated by '\t' lines terminated by '\n' select * from test_table where ...;

对于0.10.0之前的版本，可以自己写一个定制的OutputFormat控制数据格式（负责），另一种方式可以采用临时表的方式，不过还得自己删除，略麻烦，最简单办法是使用Hive提供的内置函数，比如concat等实现自己希望的格式。

简单查询：

select * from test_table;

select a.field1, a.field2 ... from test_table a;

当选择的列是集合数据类型时，Hive会使用JSON语法输出（字符串用引号括起来，但是正常的STRING类型时查询出来是不带引号的）；

select field1, field2[0] from test_table; field2是ARRAY类型，索引同样从0开始；

select field1, field2["key"] from test_table; field2是MAP类型；

select field1, field2.subfield from test_table; field2是STRUCT类型；

同样，可以使用正则表达式选择查询列；

与SQL一样，Hive不但可以选择表中的列，还可以使用函数和算术表达式来操作列值；

算术运算符：+（加）、-（减）、*（乘）、/（除）、%（取余）、&（与）、|（或）、^（异或）、~（非）

数学函数：round(d)、round(d, n)、floor(d)、ceil(d)、rand()、exp(d)、ln(d)、log10(d)、log2(d)、log(base, d)、pow(d, p)、sqrt(d)、bin(i)、hex(i)、unhex(b)、conv(num, from_base, to_base)、abs(d)、pmod(i1, i2)、sin(d)、asin(d), cos(d)、acos(d)、tan(d)、atan(d)、degrees(d)、radians(d)、positive(i)、negative(i)、sign(d)、e()、pi()

注意，遇到将浮点数转换为整形时，floor、ceil、round这些函数是首选，而不是使用cast强制类型转换；

聚合函数：count()、sum()、avg()、min()、max()、collect_set()

小技巧：通常，可以通过设置属性hive.map.aggr=true来提高聚合的性能，这个设置会触发在map阶段进行“顶级”聚合过程（非顶级聚合过程将会在执行一个GROUP BY后进行），不过，这个设置将需要更多的内存；

表生成函数：与聚合函数相反，将单列扩展为多列或者多行，注意，在使用表生成函数时，Hive要求使用列别名，explode(array)、explode(map)、inline()、json_tuple()、parse_url_tuple()、stack()

其他内置函数：ascii(s)、base64(bin)、binary(s)、cast(expr as type)、concat()、concat_ws()、decode()、encode()、find_in_set()、format_number()、get_json_object()、in()、in_file()、in_str()、length()、locate()、lower()、lpad()、ltrim()、parse_url()、printf()、regexp_extract()、regexp_replace()、repeat()、reverse()、rpad()、rtrim()、size()、space()、split()、str_to_map()、substr()、translate()、trim()、unbase64()、upper()、from_unixtime()、unix_timestamp()、to_date()、year()、month()、day()、hour()、minute()、second()、weekofyear()、datadiff()、data_add()、date_sub()

limit语句：

select upper(field1), field2["key"] as new_field2, round(1 - field3*0.2) as new_filed3 from test_table limit 2;

嵌套select语句：

select e.field1, e.field2 from (

select a.field1, a.field2 from test_table a) e where e.field1 > 10;

from (

select a.field1, a.field2 from test_table a

) e

select e.field1, e.field2 where e.field1 > 10;

case when ... then ... when ... then ... else ... end

避免进行MR：

大多数查询都会触发一个MR任务（job），Hive中对某些情况可以不必使用MR，也就是所谓的本地模式，比如select * from test_table，同样，对于where语句中只有分区字段时也不会出现MR，此外，可以设置属性hive.exec.mode.local.auto=true，Hive会尝试使用本地模式进行操作，所以最好将其设置到$HOME/.hiverc中；

where注意事项：

select field1, 2*(1 - field2) as new_field2 from test_table where round(new_field2) > 80; 该语句在HIVE中会报错，Hive不允许在where语句中使用别名，不过可以使用一个嵌套查询进行改写：

select e.* from (

select field1, 2*(1 - field2) as new_field2 from test_table

) e where round(e.new_field2) > 80;

谓词操作符：

A=B、A<>B、A!=B、A<B、A>B、A [NOT] BETWEEN B AND C、A IS [NOT] NULL、A [NOT] LIKE B、A RLIKE B、A REGEXP B

浮点数比较注意事项：

如果表中定义为float类型field，谓词表达式为field > 0.2，那么查询结果会包含0.2，因为其实float类型为0.2000001表示，转换为double型进行比较，所以，正确方式应该显示将0.2转换为float型cast(0.2 as float)，在进行比较；

Hive中group by和having与SQL相同；

连接Join：

Hive支持Join操作，但是只支持等值连接，因为通过MR很难实现这种类型的连接，同时Hive中目前还不支持在ON子句中的谓词间使用OR；

Inner Join：

select a.field, b.field from test_table1 a join test_table2 b on a.field=b.field where ...

大多数情况下，Hive会对每对Join连接对象启动一个MR任务；但是，如果每个ON子句中都使用同一个连接字段作为其中一个连接键，HIVE通过一个优化可以在同一个MR job中连接多张表；

Hive同时假定查询中最后一个表是最大的那个表，在对每行记录进行连接操作时，它会尝试将其他表缓存起来，然后扫描最后那个表进行计算，因此，用户需要保证连续查询中的表的大小从左到右依次增加；用户也可以显示的告诉查询优化器哪张表是大表: /* +STREAMTABLE(s) */

Left Outer Join：

注意：在使用外连接时，Hive会忽略ON后面的分区条件，在内连接中有效；where语句在连接操作执行后才会执行？？？（有待验证）

Right Outer Join：

Full Outer Join：

Left Semi Join：

Hive中不支持in、exists等谓词，所以可以使用左半外连接实现该功能，注意Hive不支持Right Semi Join；通常来说，如果只需要左边表的记录，那么Semi Join比Join要高效一些，对于左边表中的一条指定记录，如果在右表中找到匹配，Hive就会立即停止扫描；

笛卡尔积Join：

select * from a join b;其实没有理由去执行笛卡尔积操作，指定hive.mapred.mode=true，Hive会阻止用户只需笛卡尔积操作；

Join优化-Map-side Join：

如果所有表中只有一张表是小表，那么可以在最大的表通过mapper的时候，将小表完全放到内存中，Hive可以在map端执行连接过程，从而省略常规连接操作所需要的reduce操作；即使是很小的表，这个优化也明显要快于常规连接操作；

/* +MAPJOIN(d) */该标记可以手动触发map-side join，或者可以通过设置属性hive.auto.convert.join=true让Hive在必要的时候启动这个优化，该属性依据另一个属性hive.mapjoin.smalltable.filesize=25000000判断是否使用map join，默认是25M；

该开关可以设置在$HOME/.hiverc中；