《hadoop权威指南》关于hive的第一个小例子的演示

本文是《hadoop权威指南》关于hive的小例子，通过这个例子可以很好地看出来hive是个什么东西。

前提是已经配置好hive的远程连接版本的环境，我是用了MYSQL数据库保存元数据。

环境要求：

-配置好了Hadoop的HDFS文件系统，启动hdfs和yarn

-配置好了hive的远程连接模式

-配置好了MySQL用于metadata的储存

输入文件下载： https://github.com/tomwhite/hadoop-book/blob/master/input/ncdc/micro-tab/sample.txt

第一步，创建一个表格records，表格名字和数据源的字段，年份，温度和quality 。

Logging initialized using configuration in file:/usr/local/hive/conf/hive-log4j.properties

hive> Create table records(year String,temperature INT,quality INT)

    > ROW FORMAT DELIMITED

    > FIELDS TERMINATED BY '\t'

    > ;

OK

第二部，把保存在linux上的数据上传到刚才创建的表格中。

注意：数据是没有固定格式的，因为目前input是用分隔符“\t”分割的。所以上一步中使用了（FIELDS TERMINATED BY '\t'）来

HIVE没有专门数据格式，用户只要创建表的时候告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据

hive> LOAD DATA LOCAL INPATH 'sample.txt'

    > OVERWRITE INTO TABLE records;

Loading data to table default.records

Table default.records stats: [numFiles=1, numRows=0, totalSize=51, rawDataSize=0]

OK

Time taken: 6.03 seconds

执行HiveQL语句，从刚才数据中抽取每年的温度最高值

整个过程和MapReduce一致，一共耗费30秒。

hive> SELECT year,MAX(temperature)

    >  FROM records

    > WHERE temperature !=999 AND quality IN (0,1,4,5,9)

    > GROUP BY year;

Query ID = root_20171107090403_c61a6f9a-05d4-4d0f-a97b-d37fb83ef65d

Total jobs = 1

Launching Job 1 out of 1

Number of reduce tasks not specified. Estimated from input data size: 1

In 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_1510015112691_0001, Tracking URL = http://server71:8088/proxy/application_1510015112691_0001/

Kill Command = /usr/local/hadoop/bin/hadoop job  -kill job_1510015112691_0001

Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1

2017-11-07 09:05:58,529 Stage-1 map = 0%,  reduce = 0%

2017-11-07 09:06:59,061 Stage-1 map = 0%,  reduce = 0%

2017-11-07 09:07:11,068 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 16.88 sec

2017-11-07 09:07:53,824 Stage-1 map = 100%,  reduce = 67%, Cumulative CPU 20.75 sec

2017-11-07 09:08:03,489 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 28.83 sec

MapReduce Total cumulative CPU time: 28 seconds 830 msec

Ended Job = job_1510015112691_0001

MapReduce Jobs Launched:

Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 28.83 sec   HDFS Read: 8355 HDFS Write: 17 SUCCESS

Total MapReduce CPU Time Spent: 28 seconds 830 msec

OK

1949    111

1950    22

Time taken: 243.092 seconds, Fetched: 2 row(s)

ive的