Hive的表的创建及外部表、分区表介绍

Hive的表的创建及外部表、分区表详细介绍

（1）创建表的三种方式

# 指定标的类型和表的名称CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name # 指定每列的名称和类型[(col_name data_type [COMMENT col_comment], ...)]# 对表的说明[COMMENT table_comment]# 指定分区表的列名称，列类型[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]# 指定每列的分隔符、数据类型的类型[[ROW FORMAT row_format] [STORED AS file_format]| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ]# 指定数据文件存储在HDFS的什么位置[LOCATION hdfs_path]# 表的属性设置[TBLPROPERTIES (property_name=property_value, ...)] # 子查询[AS select_statement];

1、创建员工表和部门表

CREATE TABLEIF NOT EXISTS emp(empno INT ,ename string ,job string ,mgr INT ,hiredate string ,sal DOUBLE ,comm DOUBLE ,deptno INT) ROW format delimited FIELDS TERMINATED BY '\t';

CREATE TABLEIF NOT EXISTS dept(deptno INT ,dname string ,loc string) ROW format delimited FIELDS TERMINATED BY '\t';

2、第二种创健方式：把子查询的结果作为一张新表。适用于需要保存查询的中间结果

创建格式
与第一种格式相同，需要注意 子查询要放在最后。

      CREATE TABLE IF NOT EXISTS empcopy       AS       SELECT * FROM emp;

3、第三种创建方式：复制另外一个表的结构。
创建格式
create table tablename1 like tablename2; 创建表1且和表2 的表结构和属性完全一样，但不复制表2中的数据，所以表1的数据为空。

      CREATE TABLE IF NOT EXISTS empcopy2       like emp;

（3）加载数据
格式：load data [local] inpath 'filepath' [overwrite] into table table_name [partition(partcol=val1,partcol2=val2...)]
参数解释：local表示是否从本地文件系统寻找文件，默认的是从hdfs上寻找文件；overwrite表示是否覆盖表中已有的数据。

hive > load data local inpath '/opt/datas/emp.txt' overwrite into table emp;hive > load data local inpath '/opt/datas/dept.txt' overwrite into table dept;

（4）Hive两种表类型：内部表和外部表

4.1、 创建外部表方法一

如果在创建表时不指定external关键字，则默认的是创建管理表(内部表)，内部表对应的目录必须存储在hive的数据仓库中。

4.2、创建外部表方法二

还有一种表是指定external关键字的表，即外部表，外部表的数据文件可以自己指定目录（不在局限于hive数据仓库中），指定对的目录下面的数据文件及为这个表的数据文件。

如何创建外部表。location指定的是hdfs上的目录，该目录表示的是这个外部表目录，这个目录下的文件即为这个表的数据文件

CREATE external TABLE test_user(user_id INT COMMENT 'userID' ,user_name string COMMENT 'userName') ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LOCATION '/hive_hdfs_local_data';

4.3、内部表和外部表的区别

执行删除表命令操作时，对于内部表，会删除元数据和存储的数据，而对于外部表只会删除元数据库里的元数据信息，而不会删除存储的数据。这样可以防止多个部门同时使用一个表时，一个部门删除数据文件导致其他部门不能使用的情况。方便共享数据。
内部表默认存储在数据仓库中，而外部表一般通过location自己指定目录，便于存放在仓库外。

（5）Hive分区表

为什么有分区表？
如果把一年或者一个月的日志文件存放在一个表下，那么数据量会非常的大，当查询这个表中某一天的日志文件的时候，查询速度还非常的慢，这时候可以采用分区表的方式，把这个表根据时间点再划分为小表。这样划分后，查询某一个时间点的日志文件就会快很多，因为这是不需要进行全表扫描。
hive中的分区表
hive中的分区是根据“分区列”的值对表的数据进行粗略的划分，hive中一个表对应一个目录，再根据分区列在这个表目录下创建子目录，每个子目录名就是分区列的名字。分区列定义与表中字段相似，但是与表中的字段无关，是独立的列。这样就加快了数据查询的速度，因为不会对这个表中进行全盘扫描了。
总之，建立分区可以理解为给hive表建立了一个索引，查询hive表时可以以分区作为条件，而不需要遍历整张表。

5.1、创建分区表

CREATE TABLEIF NOT EXISTS emp_part(empno INT ,ename string ,job string ,mgr INT ,hiredate string ,sal DOUBLE ,comm DOUBLE ,deptno INT) partitioned by (date_str string) ROW format delimited FIELDS TERMINATED BY '\t';

5.2、分区和不分区创建同样字段的表格，最终形成的表格结构会有如下不同：即创建分区的hive表比不创建的多一个分区字段

hive> desc emp_part;OKempno               intename               stringjob                 stringmgr                 inthiredate            stringsal                 doublecomm                doubledeptno              intdate_str            string# Partition Information# col_name            data_type           commentdate_str            stringTime taken: 0.105 seconds, Fetched: 14 row(s)hive> desc emp;OKempno               intename               stringjob                 stringmgr                 inthiredate            stringsal                 doublecomm                doubledeptno              intTime taken: 0.06 seconds, Fetched: 8 row(s)hive>

5.3、向分区表中加载数据

hive> load data local inpath '/Users/a6/Applications/apache-hive-2.3.0-bin/result_data/dept_info.txt' into table emp_part partition(date_str='2017-11-03')    > ;Loading data to table yyz_workdb.emp_part partition (date_str=2017-11-03)OKTime taken: 0.809 seconds

再次加载数据

hive> load data local inpath '/Users/a6/Applications/apache-hive-2.3.0-bin/result_data/dept_info_new.txt' into table emp_part partition(date_str='2017-11-04')    > ;Loading data to table yyz_workdb.emp_part partition (date_str=2017-11-04)OKTime taken: 0.549 seconds

5.3.1、查看两次加载数据后的hive表的结果

hive> select * from emp_part;OK1007李华前端开发2002017100110000.020000.01102017-11-031008小明数据挖掘工程2012017062617000.023000.01202017-11-031007李华前端开发2002017100110000.020000.01102017-11-041008小明数据挖掘工程2012017062617000.023000.01202017-11-04Time taken: 0.164 seconds, Fetched: 4 row(s)

5.3.2、显示该表格的分区信息

hive> show partitions emp_part;OKdate_str=2017-11-03date_str=2017-11-04Time taken: 0.109 seconds, Fetched: 2 row(s)

5.3.3、另一种查看表格分区表的格式的方法：

show create table 表名;
如果是这个表有分区的话，可以看到显示的内容里有partition，partition里面跟的就是分区列名。

hive> show create table emp_part;OKCREATE TABLE `emp_part`(  `empno` int,  `ename` string,  `job` string,  `mgr` int,  `hiredate` string,  `sal` double,  `comm` double,  `deptno` int)PARTITIONED BY (  `date_str` string)ROW FORMAT SERDE  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'WITH SERDEPROPERTIES (  'field.delim'='\t',  'serialization.format'='\t')STORED AS INPUTFORMAT  'org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION  'hdfs://localhost:9002/user/hive/warehouse/yyz_workdb.db/emp_part'TBLPROPERTIES (  'transient_lastDdlTime'='1509778415')Time taken: 0.097 seconds, Fetched: 24 row(s)

5.3.4 通过hadoop命令行查看已经创建的hive表的分区信息

localhost:result_data a6$ hadoop dfs -ls /user/hive/warehouse/yyz_workdb.db/emp_partDEPRECATED: Use of this script to execute hdfs command is deprecated.Instead use the hdfs command for it.17/11/04 17:32:21 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicableFound 2 itemsdrwxr-xr-x   - a6 supergroup          0 2017-11-04 15:08 /user/hive/warehouse/yyz_workdb.db/emp_part/date_str=2017-11-03drwxr-xr-x   - a6 supergroup          0 2017-11-04 15:16 /user/hive/warehouse/yyz_workdb.db/emp_part/date_str=2017-11-04localhost:result_data a6$localhost:result_data a6$ hadoop dfs -ls /user/hive/warehouse/yyz_workdb.db/emp_part/date_str=2017-11-03DEPRECATED: Use of this script to execute hdfs command is deprecated.Instead use the hdfs command for it.17/11/04 17:37:16 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicableFound 1 items-rwxr-xr-x   1 a6 supergroup        138 2017-11-04 15:08 /user/hive/warehouse/yyz_workdb.db/emp_part/date_str=2017-11-03/dept_info.txtlocalhost:result_data a6$ hadoop dfs -cat /user/hive/warehouse/yyz_workdb.db/emp_part/date_str=2017-11-03/dept_info.txtDEPRECATED: Use of this script to execute hdfs command is deprecated.Instead use the hdfs command for it.17/11/04 17:37:29 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable1007李华前端开发2002017100110000200001102017-10-021008小明数据挖掘工程2012017062617000230001202017-06-28

每一个分区都会有一个独立的文件夹，下面是该分区所有的数据文件

参考链接：http://www.jianshu.com/p/265456b606dc