Hive数据仓库如何使用

Hive是一个数据仓库基础工具在hadoop中用来处理结构化数据。它架构在Hadoop之上，总归为大数据，并使得查询和分析方便。并提供简单的sql查询功能，可以将sql语句转换为mapReduce任务进行运行。

Hive表类型测试

内部表

数据准备，先在hdfs上准备文本文件，逗号分割，并上传到/test目录，然后在Hive里创建表，表名和文件名要相同。

$ cat /tmp/table_test.csv1,user1,10002,user2,20003,user3,30004,user4,40005,user5,5000

Hive创建表

hive> CREATE TABLE table_test ( id int, name string, value INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

前半部分跟我们使用SQL语法差不多，后面的设置表示我们以’,’为分隔符导入数据。

Hive加载HDFS数据

$ hive -e 'load data local inpath '/tmp/table_test.csv' into table db_test.table_test'Loading data to table db_test.table_testOKTime taken: 0.148 seconds

同一个文件可以多次加载（追加数据），同时会在HDFS数据目录下多生成一个文件。另外这里加载数据local关键字表示我们从本地文件加载，如果不加local表示从HDFS中加载数据。

Hive查看数据

hive> select * from table_test;OK1       user1   10002       user2   20003       user3   30004       user4   40005       user5   5000Time taken: 0.058 seconds, Fetched: 5 row(s)

你也可以使用select id from table_test，但是注意在Hive中除了select * from table之外可以使用全表扫描之外，其余任何查询都需要走MapRedure。

查看HDFS数据文件

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/table_test/Found 1 items-rwxrwxrwx   2 root supergroup         65 2017-06-15 22:27 /user/hive/warehouse/db_test.db/table_test/table_test.csv

注意文件权限属主为root，这是因为我是在root用户下进入hive的，一般在Hadoop用户下进入hive命令行进行创建表。

从HDFS加载数据到Hive,先上传数据到HDFS集群中

[hadoop@hadoop-nn ~]$ hdfs dfs -mkdir /test[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /test/table_test.csv

创建表

[hadoop@hadoop-nn ~]$ hivehive> CREATE TABLE hdfs_table ( id int, name string, value INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

加载数据

hive> LOAD DATA INPATH '/test/table_test.csv' OVERWRITE INTO TABLE db_test.hdfs_table;Loading data to table db_test.hdfs_tableOKTime taken: 0.343 secondshive> select * from db_test.hdfs_table;OK1       user1   10002       user2   20003       user3   30004       user4   40005       user5   5000Time taken: 0.757 seconds, Fetched: 5 row(s)

注意，如果从HDFS加载数据到Hive后，原有的HDFS的数据文件就不会存在了。

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /test/table_test.csvls: `/test/table_test.csv': No such file or directory

查看HDFS数据文件

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/hdfs_table/Found 1 items-rwxrwxrwx   2 hadoop supergroup         65 2017-06-15 22:54 /user/hive/warehouse/db_test.db/hdfs_table/table_test.csv

再次上传一个文件到对应表的目录（/user/hive/warehouse/db_test.db/hdfs_table）下

[hadoop@hadoop-nn ~]$ cat /tmp/table_test.csv6,user6,6000[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /user/hive/warehouse/db_test.db/hdfs_table/table_test_20170616.csv

再次查看Hive表

hive> select * from db_test.hdfs_table;OK1       user1   10002       user2   20003       user3   30004       user4   40005       user5   50006       user6   6000Time taken: 0.053 seconds, Fetched: 6 row(s)

可以看到，我们追加的一个表信息也显示出来了。

分区表

创建分区表时，需要给定一个分区字段，这个分区字段可以是已经存在的，也可以是不存在（如果不存在创建表时会自动添加）。Hive分区概念跟MySQL分区差不多。下面创建一个以月为分区的分区表。

CREATE TABLE par_table ( id int, name string, value INT) partitioned by (day int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

查看表信息

hive> desc par_table;OKid                      int                                        name                    string                                      value                   int                                        day                     int                                                       # Partition Information          # col_name              data_type               comment                           day                     int                                        Time taken: 0.023 seconds, Fetched: 9 row(s)

加载数据到Hive分区表中，需要指定对应的分区表进行数据加载

hive> LOAD DATA LOCAL INPATH '/tmp/table_test.csv' OVERWRITE INTO TABLE db_test.par_table PARTITION (day='22');Loading data to table db_test.par_table partition (day=22)OKTime taken: 0.267 secondshive> LOAD DATA LOCAL INPATH '/tmp/table_test.csv' OVERWRITE INTO TABLE db_test.par_table PARTITION (day='23');Loading data to table db_test.par_table partition (day=23)OKTime taken: 0.216 seconds

查看HDFS数据文件展示样式

[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/par_table/Found 1 itemsdrwxrwxrwx   - hadoop supergroup          0 2017-06-16 01:12 /user/hive/warehouse/db_test.db/par_table/day=22drwxrwxrwx   - hadoop supergroup          0 2017-06-16 01:12 /user/hive/warehouse/db_test.db/par_table/day=23

可以看到多了对应的分区目录了。

查询数据，查询时有点不太一样，如果给定一个where条件指定分区字段（也就是根据查询字段来进行分区），这样就只会查询这个分区的内容，不需要加载所有表。如果查询字段不是分区字段，那么就需要扫描所有的分区了。如下两个示例：

hive> select * from db_test.par_table;OK6       user6   6000    226       user6   6000    23Time taken: 0.054 seconds, Fetched: 2 row(s)hive> select * from db_test.par_table where day=22;OK6       user6   6000    22Time taken: 0.068 seconds, Fetched: 1 row(s)

外部表

Hive支持外部表，外部表跟内部表和分区表不同。只需要在HDFS中有了对应的文件，然后在Hive就可以创建一个表并指定对应的目录就可以直接查数据了，而不需要执行数据加载任务。下面来测试看看：

先在HDFS中创建目录和上传文件：

[hadoop@hadoop-nn ~]$ hdfs dfs -mkdir -p /hive/external[hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /hive/external/ext_table.csv

然后在Hive中直接创建表：

CREATE EXTERNAL TABLE ext_table ( id int, name string, value INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/hive/external';

此时，直接查询此表，不需要加载数据了

hive> select * from ext_table;OK6       user6   6000Time taken: 0.042 seconds, Fetched: 1 row(s)

Hive还支持桶表，这里就不说了，很少用，有兴趣自行查看资料。

最后来一个MapReduce处理Hive的过程

hive> select count(*) from table_test;WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.Query ID = hadoop_20170616021047_9c0dc1bf-383f-49ad-83e2-e2e5dfdcb20cTotal jobs = 1Launching Job 1 out of 1Number of reduce tasks determined at compile time: 1In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=In order to limit the maximum number of reducers: set hive.exec.reducers.max=In order to set a constant number of reducers: set mapreduce.job.reduces=Starting Job = job_1497424827481_0004, Tracking URL = Http://master:8088/proxy/application_1497424827481_0004/Kill Command = /usr/local/hadoop/bin/hadoop job  -kill job_1497424827481_0004Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 12017-06-16 02:10:52,914 Stage-1 map = 0%,  reduce = 0%2017-06-16 02:10:57,062 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.11 sec2017-06-16 02:11:02,204 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.53 secMapReduce Total cumulative CPU time: 2 seconds 530 msecEnded Job = job_1497424827481_0004MapReduce Jobs Launched:Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.53 sec   HDFS Read: 7980 HDFS Write: 102 SUCCESSTotal MapReduce CPU Time Spent: 2 seconds 530 msecOK10Time taken: 15.254 seconds, Fetched: 1 row(s)

可以好好看一下处理过程，由于是测试环境所以MP时间很久。

视图

另外Hive也支持视图，使用非常简单，如下配置：

hive> create view view_test as select * from table_test;OKTime taken: 0.054 secondshive> select * from view_test;OKd1      user1   1000d1      user2   2000d1      user3   3000d2      user4   4000d2      user5   5000Time taken: 0.057 seconds, Fetched: 5 row(s)

Hive元数据信息

然后我们来查看一下Hive元数据表信息，在Mysql的hive库下的DBS表中存储Hive创建的库信息：

mysql> select * from DBS;+-------+-----------------------+---------------------------------------------------+---------+------------+------------+| DB_ID | DESC                  | DB_LOCATION_URI                                   | NAME    | OWNER_NAME | OWNER_TYPE |+-------+-----------------------+---------------------------------------------------+---------+------------+------------+|     1 | Default Hive database | hdfs://master:8020/user/hive/warehouse            | default | public     | ROLE       ||     6 | NULL                  | hdfs://master:8020/user/hive/warehouse/db_test.db | db_test | hadoop     | USER       |+-------+-----------------------+---------------------------------------------------+---------+------------+------------+2 rows in set (0.00 sec)DB_ID：库ID，具有唯一性。DESC：库描述信息。DB_LOCATION_URI：库在HDFS的URI地址。NAME：库名称。OWNER_NAME：库的所有者，用什么系统用户登录Hive创建的，其所有者就是谁，一般要在Hadoop用户下登录Hive。OWNER_TYPE：库的所有者类型。在hive库下的TBLS表中存储我们创建的表的元数据信息：mysql> select * from TBLS;+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+| TBL_ID | CREATE_TIME | DB_ID | LAST_ACCESS_TIME | OWNER  | RETENTION | SD_ID | TBL_NAME   | TBL_TYPE       | VIEW_EXPANDED_TEXT | VIEW_ORIGINAL_TEXT |+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+|     11 |  1497579800 |     6 |                0 | root   |         0 |    11 | table_test | MANAGED_TABLE  | NULL               | NULL               ||     16 |  1497581548 |     6 |                0 | hadoop |         0 |    16 | hdfs_table | MANAGED_TABLE  | NULL               | NULL               ||     26 |  1497584489 |     6 |                0 | hadoop |         0 |    26 | par_table  | MANAGED_TABLE  | NULL               | NULL               ||     28 |  1497591914 |     6 |                0 | hadoop |         0 |    31 | ext_table  | EXTERNAL_TABLE | NULL               | NULL               |+--------+-------------+-------+------------------+--------+-----------+-------+------------+----------------+--------------------+--------------------+4 rows in set (0.00 sec)解释几个重要参数：TBL_ID：表ID，具有唯一性。CREATE_TIME：表创建时间。DB_ID：所属库的ID。LAST_ACCESS_TIME：最后一次访问时间。OWNER：表的所有者，用什么系统用户登录Hive创建的，其所有者就是谁，一般要在Hadoop用户下登录Hive。TBL_NAME：表名称。TBL_TYPE：表类型，MANAGED_TABLE表示受托管的表（如内部表、分区表、桶表），EXTERNAL_TABLE表示外部表，两个有个很大的区别就是受托管的表，当你执行DROP TABLE动作时，会把Hive元数据信息连同HDFS数据也一同删除。而外部表执行DROP TABLE时不会删除HDFS的数据，只是把元数据信息删除了。