(3)hive的基本操作

Hive的基本操作
一般情况下hive所操作的数据文件是已经存在的(也可以是外部导入的)，常见的web日志文件格式有多种(如josn格式)。

注意：Hive所创建的数据库和数据表都是在HDFS里的某个目录
如果是数据库，那么在HDFS里就是：/user/hive/warehouse/库名称.db
如果是数据表，那么在HDFS里就是：/user/hive/warehouse/库名称.db/表名称
Hive下默认有一个库default,如果不建库，直接建表，则表建在defaule库下。

1．Hive的基本操作
(1)建库命令：CREATE SCHEMA 库名；
(2)建表命令：CREATE TABLE 表名(字段名称、类型)；
如：CREATE TABLE tuoguan_tbl (flied string);
数据表里的内容，实质就是HDFS里的某个文件，需要把这个文件解析为数据表的格式。

(3)创建普通表，每行的字段用逗号分隔
create table web_log(id int, name string, address string) row format delimited fields terminated by ',';
(4)查看表的命令：show tables；
(5)查看表中数据的命令：select *不需要转换为mapreduce
select * from tuoguan_tbl;
(6)查看表结构命令：Desc 表名称；
(7)举例：
在Linux文件系统/home/oracle下有一个文件t_hive.txt（文本以tab分隔）
#查看数据文件的内容（文本以tab分隔）
~ vi t_hive.txt
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
#创建新表
hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
OK
Time taken: 0.489 seconds
#导入数据t_hive.txt到t_hive表
hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_hive ; Copying data from file:/home/cos/demo/t_hive.txt
Copying file: file:/home/cos/demo/t_hive.txt
Loading data to table default.t_hive
Deleted hdfs://:9000/user/hive/warehouse/t_hive
OK
Time taken: 0.397 seconds
#查看表
hive> show tables;
OK
t_hive
Time taken: 0.099 seconds
#查看表数据
hive> select * from t_hive;
OK
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
Time taken: 0.264 seconds
#查看表结构
hive> desc t_hive;
OK
a int
b int
c int
Time taken: 0.1 seconds
#修改表，增加一个字段
hive> ALTER TABLE t_hive ADD COLUMNS (new_col String);
OK
Time taken: 0.186 seconds
hive> desc t_hive;
OK
a int
b int
c int
new_col string
Time taken: 0.086 seconds
#重命名表名
~ ALTER TABLE t_hive RENAME TO t_hadoop;
OK
Time taken: 0.45 seconds
hive> show tables;
OK
t_hadoop
Time taken: 0.07 seconds
#删除表
hive> DROP TABLE t_hadoop;
OK
Time taken: 0.767 seconds
#查看表
hive> show tables;
OK
Time taken: 0.064 seconds
(8)如果不想把HDFS里的文件进行移动，则可以创建外部表：
create external table web_log2 (id int, name string, address string) Location ‘/user/weblog/’; 2.将json格式的web日志文件user_movie.json导入到Hive的某个表中
方法一：使用第三方jar包
(1)使用一个第三方的jar包json-serde-1.3.6-SNAPSHOT-jar-with-dependencies.jar(老师给的),将其复制到HIVE_HOME/lib目录下
(2)创建表user_movie
create table user_movie(custid string, sno string, genreid string, movieid string) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS TEXTFILE;
(3)将json文件user_movie.json导入到表user_movie中
首先将json文件上传到Linux文件系统/home/oracle目录下面，然后在Linux命令下执行如下命令：（本地的文件直接导入到HDFS相应的目录里）
或者在hive命令中执行：（从HDFS里直接导入数据，这个会把HDFS里的文件移动到HIVE 表的相应目录里）
方法二：使用hive自带的jar包hive-hcatalog-core-1.2.1.jar
需要把HIVE_HOME/hcatalog/share/hcatalog/hive-hcatalog-core-1.2.1.jar复制到
HIVE_HOME/lib，然后在hive命令下用下面方法建表。

create table user_movie2(custid string, sno string, genreid string, movieid string) ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;
其他都一样。

3.数据导入
还以刚才的t_hive为例。

#创建表结构
hive> CREATE TABLE t_hive (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
从操作本地文件系统加载数据(LOCAL)
hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hive.txt' OVERWRITE INTO TABLE t_hive ; Copying data from file:/home/cos/demo/t_hive.txt
Copying file: file:/home/cos/demo/t_hive.txt
Loading data to table default.t_hive
Deleted hdfs://:9000/user/hive/warehouse/t_hive
OK
Time taken: 0.612 seconds
#在HDFS中查找刚刚导入的数据
~ hadoop fs -cat /user/hive/warehouse/t_hive/t_hive.txt
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
从HDFS加载数据
创建表t_hive2
hive> CREATE TABLE t_hive2 (a int, b int, c int) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
#从HDFS加载数据
hive> LOAD DATA INPATH '/user/hive/warehouse/t_hive/t_hive.txt' OVERWRITE INTO TABLE t_hive2;
Loading data to table default.t_hive2
Deleted hdfs://:9000/user/hive/warehouse/t_hive2
OK
Time taken: 0.325 seconds
#查看数据
hive> select * from t_hive2;
OK
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
Time taken: 0.287 seconds
从其他表导入数据
hive> INSERT OVERWRITE TABLE t_hive2 SELECT * FROM t_hive ;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201307131407_0002, Tracking URL =
:50030/jobdetails.jsp?jobid=job_201307131407_0002
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job_201307131407_0002
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2013-07-16 10:32:41,979 Stage-1 map = 0%, reduce = 0%
2013-07-16 10:32:48,034 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:49,050 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:50,068 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:51,082 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:52,093 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:53,102 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.03 sec 2013-07-16 10:32:54,112 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.03 sec MapReduce Total cumulative CPU time: 1 seconds 30 msec
Ended Job = job_201307131407_0002
Ended Job = -314818888, job is filtered out (removed at runtime).
Moving data to:
hdfs://:9000/tmp/hive-cos/hive_2013-07-16_10-32-31_323_5732404975764 014154/-ext-10000
Loading data to table default.t_hive2
Deleted hdfs://:9000/user/hive/warehouse/t_hive2
Table default.t_hive2 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 56, raw_data_size: 0]
7 Rows loaded to t_hive2
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.03 sec HDFS Read: 273 HDFS Write: 56 SUCCESS Total MapReduce CPU Time Spent: 1 seconds 30 msec
OK
Time taken: 23.227 seconds
hive> select * from t_hive2;
OK
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
Time taken: 0.134 seconds
创建表并从其他表导入数据
#删除表
hive> DROP TABLE t_hive;
#创建表并从其他表导入数据
hive> CREATE TABLE t_hive AS SELECT * FROM t_hive2 ;
Total MapReduce jobs = 2
Launching Job 1 out of 2
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201307131407_0003, Tracking URL =
:50030/jobdetails.jsp?jobid=job_201307131407_0003
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job_201307131407_0003
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2013-07-16 10:36:48,612 Stage-1 map = 0%, reduce = 0%
2013-07-16 10:36:54,648 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.13 sec 2013-07-16 10:36:55,657 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.13 sec 2013-07-16 10:36:56,666 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.13 sec 2013-07-16 10:36:57,673 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.13 sec 2013-07-16 10:36:58,683 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.13 sec 2013-07-16 10:36:59,691 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 1.13 sec MapReduce Total cumulative CPU time: 1 seconds 130 msec
Ended Job = job_201307131407_0003
Ended Job = -670956236, job is filtered out (removed at runtime).
Moving data to:
hdfs://:9000/tmp/hive-cos/hive_2013-07-16_10-36-39_986_1343249562812 540343/-ext-10001
Moving data to: hdfs://:9000/user/hive/warehouse/t_hive
Table default.t_hive stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 56, raw_data_size: 0]
7 Rows loaded to
hdfs://:9000/tmp/hive-cos/hive_2013-07-16_10-36-39_986_1343249562812 540343/-ext-10000
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 1.13 sec HDFS Read: 272 HDFS Write: 56 SUCCESS Total MapReduce CPU Time Spent: 1 seconds 130 msec
OK
Time taken: 20.13 seconds
hive> select * from t_hive;
OK
16 2 3
61 12 13
41 2 31
17 21 3
71 2 31
1 1
2 34
11 2 34
Time taken: 0.109 seconds
仅复制表结构不导数据
hive> CREATE TABLE t_hive3 LIKE t_hive;
hive> select * from t_hive3;
OK
Time taken: 0.077 seconds
4.数据导出
从HDFS复制到HDFS其他位置
~ hadoop fs -cp /user/hive/warehouse/t_hive /
~ hadoop fs -ls /t_hive
Found 1 items
-rw-r--r-- 1 cos supergroup 56 2013-07-16 10:41 /t_hive/000000_0
~ hadoop fs -cat /t_hive/000000_0
1623
611213
41231
17213
71231
11234
11234
通过Hive导出到本地文件系统
hive> INSERT OVERWRITE LOCAL DIRECTORY '/tmp/t_hive' SELECT * FROM t_hive;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Starting Job = job_201307131407_0005, Tracking URL =
:50030/jobdetails.jsp?jobid=job_201307131407_0005
Kill Command = /home/cos/toolkit/hadoop-1.0.3/libexec/../bin/hadoop job
-Dmapred.job.tracker=hdfs://:9001 -kill job_201307131407_0005
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0 2013-07-16 10:46:24,774 Stage-1 map = 0%, reduce = 0%
2013-07-16 10:46:30,823 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:31,833 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:32,844 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:33,856 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:34,865 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:35,873 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 0.87 sec 2013-07-16 10:46:36,884 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 0.87 sec
MapReduce Total cumulative CPU time: 870 msec
Ended Job = job_201307131407_0005
Copying data to local directory /tmp/t_hive
Copying data to local directory /tmp/t_hive
7 Rows loaded to /tmp/t_hive
MapReduce Jobs Launched:
Job 0: Map: 1 Cumulative CPU: 0.87 sec HDFS Read: 271 HDFS Write: 56 SUCCESS Total MapReduce CPU Time Spent: 870 msec
OK
Time taken: 23.369 seconds
#查看本地操作系统
hive> ! cat /tmp/t_hive/000000_0;
hive> 1623
611213
41231
17213
71231
11234
11234
5.Hive查询HiveQL
注：以下代码将去掉map,reduce的日志输出部分。

普通查询：排序，列别名，嵌套子查询
hive> FROM (
> SELECT b,c as c2 FROM t_hive
> ) t
> SELECT t.b, t.c2
> WHERE b>2
> LIMIT 2;
12 13
21 3
连接查询：JOIN
hive> SELECT t1.a,t1.b,t2.a,t2.b
> FROM t_hive t1 JOIN t_hive2 t2 on t1.a=t2.a
> WHERE t1.c>10;
1 1
2 1 12
11 2 11 2
41 2 41 2
61 12 61 12
71 2 71 2
聚合查询1：count, avg
hive> SELECT count(*), avg(a) FROM t_hive;
7 31.142857142857142
聚合查询2：count, distinct
hive> SELECT count(DISTINCT b) FROM t_hive;
3
聚合查询3：GROUP BY, HAVING
#GROUP BY
hive> SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c
16.0 2 3
56.0 2 62
11.0 2 34
61.0 12 13
1.0 12 34
17.0 21 3
#HAVING
hive> SELECT avg(a),b,sum(c) FROM t_hive GROUP BY b,c HAVING sum(c)>30 56.0 2 62
11.0 2 34
1.0 12 34
6.Hive视图
Hive视图和数据库视图的概念是一样的，我们还以t_hive为例。

hive> CREATE VIEW v_hive AS SELECT a,b FROM t_hive where c>30;
hive> select * from v_hive;
41 2
71 2
1 12
11 2
删除视图
hive> DROP VIEW IF EXISTS v_hive;
OK
Time taken: 0.495 seconds
7.Hive分区表
分区表是数据库的基本概念，但很多时候数据量不大，我们完全用不到分区表。

Hive是一种OLAP数据仓库软件，涉及的数据量是非常大的，所以分区表在这个场景就显得非常重要！！
下面我们重新定义一个数据表结构：t_hft
创建数据
~ vi /home/cos/demo/t_hft_20130627.csv
000001,092023,9.76
000002,091947,8.99
000004,092002,9.79
000005,091514,2.2
000001,092008,9.70
000001,092059,9.45
~ vi /home/cos/demo/t_hft_20130628.csv
000001,092023,9.76
000002,091947,8.99
000004,092002,9.79
000005,091514,2.2
000001,092008,9.70
000001,092059,9.45
创建数据表
DROP TABLE IF EXISTS t_hft;
CREATE TABLE t_hft(
SecurityID STRING,
tradeTime STRING,
PreClosePx DOUBLE
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
创建分区数据表
根据业务：按天和股票ID进行分区设计
DROP TABLE IF EXISTS t_hft;
CREATE TABLE t_hft(
SecurityID STRING,
tradeTime STRING,
PreClosePx DOUBLE
) PARTITIONED BY (tradeDate INT)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
导入数据
#20130627
hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hft_20130627.csv' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=20130627);
Copying data from file:/home/cos/demo/t_hft_20130627.csv
Copying file: file:/home/cos/demo/t_hft_20130627.csv
Loading data to table default.t_hft partition (tradedate=20130627)
#20130628
hive> LOAD DATA LOCAL INPATH '/home/cos/demo/t_hft_20130628.csv' OVERWRITE INTO TABLE t_hft PARTITION (tradeDate=20130628);
Copying data from file:/home/cos/demo/t_hft_20130628.csv
Copying file: file:/home/cos/demo/t_hft_20130628.csv
Loading data to table default.t_hft partition (tradedate=20130628)
查看分区表
hive> SHOW PARTITIONS t_hft;
tradedate=20130627
tradedate=20130628
Time taken: 0.082 seconds
查询数据
hive> select * from t_hft where securityid='000001';
000001 092023 9.76 20130627
000001 092008 9.7 20130627
000001 092059 9.45 20130627
000001 092023 9.76 20130628
000001 092008 9.7 20130628
000001 092059 9.45 20130628
hive> select * from t_hft where tradedate=20130627 and PreClosePx<9;
000002 091947 8.99 20130627
000005 091514 2.2 20130627。