数据准备,先在HDFS上准备文本文件,逗号分割,并上传到/test目录,然后在Hive里创建表,表名和文件名要相同。
$ cat /tmp/table_test.csv 1,user1,1000 2,user2,2000 3,user3,3000 4,user4,4000 5,user5,5000
hive> CREATE TABLE table_test ( id int, name string, value INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;
前半部分跟我们使用SQL语法差不多,后面的设置表示我们以’,’为分隔符导入数据。
$ hive -e 'load data local inpath '/tmp/table_test.csv' into table db_test.table_test' Loading data to table db_test.table_test OK Time taken: 0.148 seconds
同一个文件可以多次加载(追加数据),同时会在HDFS数据目录下多生成一个文件。另外这里加载数据local关键字表示我们从本地文件加载,如果不加local表示从HDFS中加载数据。
hive> select * from table_test; OK 1 user1 1000 2 user2 2000 3 user3 3000 4 user4 4000 5 user5 5000 Time taken: 0.058 seconds, Fetched: 5 row(s)
你也可以使用select id from table_test,但是注意在Hive中除了select * from table之外可以使用全表扫描之外,其余任何查询都需要走MapRedure。
[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命令行进行创建表。
[hadoop@hadoop-nn ~]$ hdfs dfs -mkdir /test [hadoop@hadoop-nn ~]$ hdfs dfs -put /tmp/table_test.csv /test/table_test.csv
[hadoop@hadoop-nn ~]$ hive hive> 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_table OK Time taken: 0.343 seconds
hive> select * from db_test.hdfs_table; OK 1 user1 1000 2 user2 2000 3 user3 3000 4 user4 4000 5 user5 5000 Time taken: 0.757 seconds, Fetched: 5 row(s)
注意,如果从HDFS加载数据到Hive后,原有的HDFS的数据文件就不会存在了。
[hadoop@hadoop-nn ~]$ hdfs dfs -ls /test/table_test.csv ls: `/test/table_test.csv': No such file or directory
[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.csv 6,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> select * from db_test.hdfs_table; OK 1 user1 1000 2 user2 2000 3 user3 3000 4 user4 4000 5 user5 5000 6 user6 6000 Time 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; OK id 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> 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) OK Time taken: 0.267 seconds hive> 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) OK Time taken: 0.216 seconds
[hadoop@hadoop-nn ~]$ hdfs dfs -ls /user/hive/warehouse/db_test.db/par_table/ Found 1 items drwxrwxrwx - hadoop supergroup 0 2017-06-16 01:12 /user/hive/warehouse/db_test.db/par_table/day=22 drwxrwxrwx - 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; OK 6 user6 6000 22 6 user6 6000 23 Time taken: 0.054 seconds, Fetched: 2 row(s) hive> select * from db_test.par_table where day=22; OK 6 user6 6000 22 Time 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; OK 6 user6 6000 Time 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-e2e5dfdcb20c Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In 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_0004 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 2017-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 sec 2017-06-16 02:11:02,204 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.53 sec MapReduce Total cumulative CPU time: 2 seconds 530 msec Ended Job = job_1497424827481_0004 MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 2.53 sec HDFS Read: 7980 HDFS Write: 102 SUCCESS Total MapReduce CPU Time Spent: 2 seconds 530 msec OK 10 Time taken: 15.254 seconds, Fetched: 1 row(s)
可以好好看一下处理过程,由于是测试环境所以MP时间很久。
另外Hive也支持视图,使用非常简单,如下配置:
hive> create view view_test as select * from table_test; OK Time taken: 0.054 seconds hive> select * from view_test; OK d1 user1 1000 d1 user2 2000 d1 user3 3000 d2 user4 4000 d2 user5 5000 Time taken: 0.057 seconds, Fetched: 5 row(s)
然后我们来查看一下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的数据,只是把元数据信息删除了。
原创文章,作者:ItWorker,如若转载,请注明出处:https://blog.ytso.com/119388.html