大数据时代--Hive数据操作之insert操作

前面文章中讲解了Hive的数据定义语言，接下来就是数据操作语言，数据定义语言主要包含了Hive表结构的一些操作：包含创建表、删除表、修改表的结构、增加列等等一系列操作。数据操作语言主要是对表中的数据的增删改查。

数据插入

通过查询语句进行插入，这里不像是MySql可以一条一条的进行插入操作，在Hive中我试了几次都没有成功，搜了下也都是通过select语句进行插入的。这里也算是和普通的sql不同的地方吧。

hive> insert overwrite table salaries select * from salaries_external where year = 2012;FAILED: SemanticException [Error 10004]: Line 1:70 Invalid table alias or column reference 'year': (pos ible column names are: yearid, teamid, lgid, playerid, salary)hive> insert overwrite table salaries select * from salaries_external where yearid = 2012;Total jobs = 3Launching Job 1 out of 3Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_1474609415284_0001, Tracking URL = http://hadoopwy1:8088/proxy/application_147460941 284_0001/Kill Command = /usr/local/hadoop2/bin/hadoop job  -kill job_1474609415284_0001Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02016-09-23 03:59:05,119 Stage-1 map = 0%,  reduce = 0%2016-09-23 03:59:33,232 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.13 secMapReduce Total cumulative CPU time: 2 seconds 130 msecEnded Job = job_1474609415284_0001Stage-4 is selected by condition resolver.Stage-3 is filtered out by condition resolver.Stage-5 is filtered out by condition resolver.Moving data to: hdfs://hadoopnodeservice1/tmp/hive-root/hive_2016-09-23_03-57-59_773_846459559745817905 -1/-ext-10000Loading data to table default.salariesrmr: DEPRECATED: Please use 'rm -r' instead.Deleted hdfs://hadoopnodeservice1/user/hive/warehouse/salariesTable default.salaries stats: [numFiles=1, numRows=1696, totalSize=52808, rawDataSize=51112]MapReduce Jobs Launched:Job 0: Map: 1   Cumulative CPU: 2.13 sec   HDFS Read: 1354022 HDFS Write: 52885 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 130 msecOKTime taken: 98.887 seconds

看中间的过程看到，存在一个Mapper的过程，没有启动Reduce过程。部分数据如下：

2012    WAS     NL      carrobr01       580000.02012    WAS     NL      desmoia01       512500.02012    WAS     NL      espinda01       506000.02012    WAS     NL      storedr01       498750.02012    WAS     NL      bernaro01       493500.02012    WAS     NL      ramoswi01       491250.02012    WAS     NL      rodrihe03       491000.02012    WAS     NL      detwiro01       485000.02012    WAS     NL      stammcr01       485000.02012    WAS     NL      marrech01       481000.02012    WAS     NL      matthry01       481000.02012    WAS     NL      lombast02       481000.0Time taken: 0.077 seconds, Fetched: 1696 row(s)

插入数据并指定其分区

该命令要求我们创建表的时候，就已经按照某种形式分区了。

hive> describe salaries_partition;OKteamid                  stringplayerid                stringlgid                    stringsalary                  floatyearid                  int# Partition Information# col_name              data_type               commentyearid                  intTime taken: 0.122 seconds, Fetched: 10 row(s)

在这里我按照年进行了分区，在下面的插入语句中，注意yearid不需要你查询出来的，因为在上面的表中已经告诉了他按照yearid进行分区的，如果再查询出yearid的话会抛出异常：说你的表中是4列，而查询出来的数据却有五列，没办法给你对应呀。

hive> insert overwrite table salaries_partition partition(yearid = 2012) select ex.teamid, ex.playerid, ex.lgid, ex.salary from salaries_external ex where ex.yearid = 2012;Total jobs = 3Launching Job 1 out of 3Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_1474609415284_0005, Tracking URL = http://hadoopwy1:8088/proxy/application_1474609415284_0005/Kill Command = /usr/local/hadoop2/bin/hadoop job  -kill job_1474609415284_0005Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02016-09-23 05:10:09,350 Stage-1 map = 0%,  reduce = 0%2016-09-23 05:10:25,157 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.79 secMapReduce Total cumulative CPU time: 1 seconds 790 msecEnded Job = job_1474609415284_0005Stage-4 is selected by condition resolver.Stage-3 is filtered out by condition resolver.Stage-5 is filtered out by condition resolver.Moving data to: hdfs://hadoopnodeservice1/tmp/hive-root/hive_2016-09-23_05-09-55_703_8904828298967067898-1/-ext-10000Loading data to table default.salaries_partition partition (yearid=2012)Partition default.salaries_partition{yearid=2012} stats: [numFiles=1, numRows=1696, totalSize=44328, rawDataSize=42632]MapReduce Jobs Launched:Job 0: Map: 1   Cumulative CPU: 1.79 sec   HDFS Read: 1354022 HDFS Write: 44427 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 790 msecOKTime taken: 32.188 seconds

动态分区插入

hive> insert overwrite table salaries_partition partition(yearid) select ex.teamid, ex.playerid, ex.lgid, ex.salary from salaries_external ex;FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

这里出现了异常，说是现在是strict状态，不允许你这样做，你需要把strict状态改成nonstrict状态才行。

这里设置了strict模式后仍然是会出现问题的。需要设置以下三个参数。

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

<strong>hive> set hive.exec.max.dynamic.partitions.pernode=3000;hive> insert overwrite table salaries_partition partition(yearid) select ex.yearid,  ex.teamid, ex.playerid, ex.lgid, ex.salary from salaries_external ex;Total jobs = 1Launching Job 1 out of 1Number of reduce tasks not specified. Estimated from input data size: 1In order to change the average load for a reducer (in bytes):  set hive.exec.reducers.bytes.per.reducer=<number>In order to limit the maximum number of reducers:  set hive.exec.reducers.max=<number>In order to set a constant number of reducers:  set mapreduce.job.reduces=<number>Starting Job = job_1474609415284_0007, Tracking URL = http://hadoopwy1:8088/proxy/application_1474609415284_0007/Kill Command = /usr/local/hadoop2/bin/hadoop job  -kill job_1474609415284_0007Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12016-09-23 06:20:01,525 Stage-1 map = 0%,  reduce = 0%2016-09-23 06:20:32,504 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 3.1 sec2016-09-23 06:20:55,706 Stage-1 map = 100%,  reduce = 68%, Cumulative CPU 5.39 sec2016-09-23 06:20:58,864 Stage-1 map = 100%,  reduce = 70%, Cumulative CPU 6.02 sec2016-09-23 06:21:02,023 Stage-1 map = 100%,  reduce = 72%, Cumulative CPU 6.53 sec2016-09-23 06:21:05,182 Stage-1 map = 100%,  reduce = 73%, Cumulative CPU 6.99 sec2016-09-23 06:21:08,338 Stage-1 map = 100%,  reduce = 75%, Cumulative CPU 7.61 sec2016-09-23 06:21:10,432 Stage-1 map = 100%,  reduce = 76%, Cumulative CPU 8.12 sec2016-09-23 06:21:13,578 Stage-1 map = 100%,  reduce = 77%, Cumulative CPU 8.57 sec2016-09-23 06:21:16,729 Stage-1 map = 100%,  reduce = 78%, Cumulative CPU 9.08 sec2016-09-23 06:21:19,883 Stage-1 map = 100%,  reduce = 79%, Cumulative CPU 9.55 sec2016-09-23 06:21:23,085 Stage-1 map = 100%,  reduce = 80%, Cumulative CPU 10.01 sec2016-09-23 06:21:26,218 Stage-1 map = 100%,  reduce = 81%, Cumulative CPU 10.45 sec2016-09-23 06:21:32,505 Stage-1 map = 100%,  reduce = 82%, Cumulative CPU 11.36 sec2016-09-23 06:21:37,774 Stage-1 map = 100%,  reduce = 83%, Cumulative CPU 12.21 sec2016-09-23 06:21:44,050 Stage-1 map = 100%,  reduce = 84%, Cumulative CPU 13.06 sec2016-09-23 06:21:47,205 Stage-1 map = 100%,  reduce = 85%, Cumulative CPU 13.48 sec2016-09-23 06:21:50,352 Stage-1 map = 100%,  reduce = 86%, Cumulative CPU 13.92 sec2016-09-23 06:21:53,538 Stage-1 map = 100%,  reduce = 87%, Cumulative CPU 14.3 sec2016-09-23 06:21:56,694 Stage-1 map = 100%,  reduce = 88%, Cumulative CPU 14.72 sec2016-09-23 06:21:59,850 Stage-1 map = 100%,  reduce = 89%, Cumulative CPU 15.15 sec2016-09-23 06:22:05,074 Stage-1 map = 100%,  reduce = 90%, Cumulative CPU 16.09 sec2016-09-23 06:22:08,220 Stage-1 map = 100%,  reduce = 91%, Cumulative CPU 16.51 sec2016-09-23 06:22:14,509 Stage-1 map = 100%,  reduce = 92%, Cumulative CPU 17.33 sec2016-09-23 06:22:17,665 Stage-1 map = 100%,  reduce = 93%, Cumulative CPU 17.77 sec2016-09-23 06:22:20,830 Stage-1 map = 100%,  reduce = 94%, Cumulative CPU 18.24 sec2016-09-23 06:22:26,128 Stage-1 map = 100%,  reduce = 95%, Cumulative CPU 19.1 sec2016-09-23 06:22:29,284 Stage-1 map = 100%,  reduce = 96%, Cumulative CPU 19.42 sec2016-09-23 06:22:35,616 Stage-1 map = 100%,  reduce = 97%, Cumulative CPU 20.3 sec2016-09-23 06:22:41,909 Stage-1 map = 100%,  reduce = 98%, Cumulative CPU 21.12 sec2016-09-23 06:22:48,204 Stage-1 map = 100%,  reduce = 99%, Cumulative CPU 21.91 sec2016-09-23 06:22:59,670 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 23.45 sec2016-09-23 06:24:00,528 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 23.88 secMapReduce Total cumulative CPU time: 23 seconds 880 msecEnded Job = job_1474609415284_0007Loading data to table default.salaries_partition partition (yearid=null)Failed with exception Number of dynamic partitions created is 2881, which is more than 1000. To solve this try to set hive.exec.max.dynamic.partitions to at least 2881.FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTaskMapReduce Jobs Launched:Job 0: Map: 1  Reduce: 1   Cumulative CPU: 28.11 sec   HDFS Read: 1354022 HDFS Write: 1191371 SUCCESSTotal MapReduce CPU Time Spent: 28 seconds 110 msec</strong>

导出数据

将数据导出到文件中。

hive> insert overwrite local directory '/usr/local/temp'    > select ex.yearid, ex.teamid, ex.playerid, ex.salary    > from salaries_external ex;Total jobs = 1Launching Job 1 out of 1Number of reduce tasks is set to 0 since there's no reduce operatorStarting Job = job_1474609415284_0008, Tracking URL = http://hadoopwy1:8088/proxy/application_1474609415284_0008/Kill Command = /usr/local/hadoop2/bin/hadoop job  -kill job_1474609415284_0008Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 02016-09-23 06:54:25,754 Stage-1 map = 0%,  reduce = 0%2016-09-23 06:54:42,450 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.56 secMapReduce Total cumulative CPU time: 1 seconds 560 msecEnded Job = job_1474609415284_0008Copying data to local directory /usr/local/tempCopying data to local directory /usr/local/tempMapReduce Jobs Launched:Job 0: Map: 1   Cumulative CPU: 1.56 sec   HDFS Read: 1354022 HDFS Write: 1302986 SUCCESSTotal MapReduce CPU Time Spent: 1 seconds 560 msecOKTime taken: 44.155 seconds