数据"/>
hive sql插入一行数据
Hive的insert语句能够从查询语句中获取数据，并同时将数据Load到目标表中。现在假定有一个已有数据的表staged_employees(雇员信息全量表)，所属国家cnty和所属州st是该表的两个属性，我们做个试验将该表中的数据查询出来插入到另一个表employees中。
1
2
3
4
INSERT OVERWRITE TABLE employees
PARTITION(country='中国',state='北京')
SELECT *FROM staged_employees se
WHERE sety='中国'ANDse.st='北京';
由于使用了OVERWRITE关键字，目标表中原来相同partition中的所有数据被覆盖，如果目标表中没有partition，则整个表会被覆盖。
如果把OVERWRITE关键字删掉，或者替换成INTO，则hive会追加而不是替代原分区或原表中的数据，这个特性在Hive v0.8.0之后才支持。
当数据已经存在于hdfs上但不是我们想要的格式的时候，当进行的计算需要分好多步骤有必要存储中间数据的时候，或者原数据没有分区、有很多无效列需要过滤的时候，可以使用insert..select句型来完成这一转换过程。
由于一个国家有很多个省份，如果想根据(国家country，地区partition)两个维度对数据进行分区的话，这条SQL语句的执行个数应该等于地区的数目，比如中国有23个省就要对该SQL语句执行23次。因此hive对这个SQL语句进行了改造，只需要扫描一次原表就可以生成不同的输出(多路输出)。比如下面的SQL语句扫描了一次原始数据表，但是同时生成了3个省份的结果数据：
1
2
3
4
5
6
7
8
9
10
FROM staged_employees se
INSERT OVERWRITE TABLE employees
PARTITION(country='中国',state='河北省')
SELECT *WHERE sety='中国'ANDse.st='河北省'
INSERT OVERWRITE TABLE employees
PARTITION(country='中国',state='陕西省')
SELECT *WHERE sety='中国'ANDse.st='陕西省'
INSERT OVERWRITE TABLE employees
PARTITION(country='中国',state='河南省')
SELECT *WHERE sety='US'ANDse.st='河南省';
通过缩进可以很清楚的看到，我们扫描了一次staged_employees表但是执行了3次不同的insert语句，这条大SQL语句是这么执行的：先通过from staged_employees表获取一条记录，然后执行每一个select子句，如果select子句验证通过则执行相应的insert语句。注意这里的三条select子句是完全独立执行的，并不是if .. then .. else的关系，这就意味着这3条select子句在某种情况下可能同时通过where检测。
通过这种结构，原始表的数据能被拆分到目标表的不同partition中去。
如果原表的一条记录满足于其中一个给定的select .. where .. 子句，则该记录将被写到目标表的固定分区中。其实更进一步，每条Insert语句能将数据写到不同的数据表中，不管这个表是否分区都一样。
于是，就像一个过滤器一样，原表的一些数据被写到了很多输出地址，而剩下的数据会被丢弃。
当然，你也可以混用Insert overwrite和insert into两种不同的方法写出数据。
向动态分区插入数据
但是问题还是没有解决，中国有23个省，那么我们就需要写23个insert into .. select ..where子句，这非常不现实。于是hive的一种叫做动态分区的特性就出现了，它能够根据select出来的参数自动推断将数据插入到那个分区中去。本文上面的两种SQL语句设定分区的方式都叫做静态分区插入。
将上一个SQL语句进行改动，会得到以下简洁的新SQL语句：
1
2
3
4
INSERT OVERWRITE TABLE employees
PARTITION(country,state)
SELECT...,sety,se.st
FROM staged_employees se;
hive先获取select的最后两个位置的sety和se.st参数值，然后将这两个值填写到Insert语句partition中的两个country和state变量中，即动态分区是通过位置来对应分区值的。原始表select出来的值和输出partition的值的关系仅仅是通过位置来确定的，和名字并没有关系，比如这里sety和county的名称完全没有关系。
上面的这条SQL语句是对两个分区同时进行了动态设定，如果staged_employees表中有100个国家，每个国家有100个地区，那么该SQL语句自动对每个国家和地区建立相应的partition并插入数据，如果用手写的话不现实。
只要位置正确，你可以混用动态分区和静态分区值设定，比如下面这个例子，你可以静态指定一个country值，但是state值采用动态的方法设定：
1
2
3
4
5
INSERT OVERWRITE TABLE employees
PARTITION(country='US',state)
SELECT...,sety,se.st
FROM staged_employees se
WHERE sety='US';
注意：静态分区值必须在动态分区值的前面！
使用hive动态分区的参数设定
动态分区功能默认是关闭的，而当它是打开状态时，默认会工作在“strict”模式下，这种模式下要求至少指定一个静态分区的值。这样做是为了防止设计了大量partition的糟糕情况，举个例子你使用时间戳来进行分区，竟然每一秒钟都产生一个分区！还有其他的一些属性设定用来限制类似的情况出现，如下表所示：
名称
默认值
描述
hive.exec.dynamic.partition
false
设置为true用于打开动态分区功能
hive.exec.dynamic.partition.mode
strict
设置为nonstrict能够让所有的分区都动态被设定，否则的话至少需要指定一个分区值
hive.exec.max.dynamic.partitions.pernode
100
能被每个mapper或者reducer创建的最大动态分区的数目，如果一个mappre或者reducer试图创建多余这个值的动态分区数目，会引发错误
hive.exec.max.dynamic.partitions
+1000
被一条带有动态分区的SQL语句所能创建的动态分区总量，如果超出限制会报出错误
hive.exec.max.created.files
100000
全局能被创建文件数目的最大值，专门有一个hadoop计数器来跟踪该值，如果超出会报错
举个例子，使用全动态分区的SQL语句序列如下所示，需要先设定一些必要的参数才可以：
1
2
3
4
5
6
7
8
9
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=1000;
---文章来自疯狂的蚂蚁www.crazyant
INSERT OVERWRITE TABLE employees
PARTITION(country,state)
SELECT...,se.cty,se.st
FROM staged_employees se;
总结
使用from .. insert.. select ..where结构能够从一个数据表中抽取数据，将结果插入到不同的表和分区中，而使用动态分区能够让hive根据select最末几个位置的值自动设定目标分区的值，使用动态分区需要设定一些hive运行参数。
更多推荐
hive sql插入一行数据
发布评论