[关闭]
@hadoopMan 2015-12-03T23:04:32.000000Z 字数 5408 阅读 1315

Hive表的创建及测试

Hive表的创建及测试

本文主要完成如下内容：
1） Hive中创建表的三种方式，应用场景说明及练习截图
2） 内部表和外部表的区别，练习截图
3） 分区表的功能 、创建，如何向分区表中加载数据、如何检索分区表中的数据区表中的数据 ，练习截图

1，创删数据库

1，创建数据库

  1. create database IF NOT EXISTS db_1128 ;

创建数据库.png-6.1kB

2，删除数据库

  1. drop database IF EXISTS db_1128 ;

2，创建表

创建表的语法

  1. ##
  2. CREATE TABLE [IF NOT EXISTS] [db_name.]table_name
  3.   [(col_name data_type [COMMENT col_comment], ...)]
  4.   [COMMENT table_comment]
  5.   [
  6.    [ROW FORMAT row_format] 
  7.    [STORED AS file_format]
  8.   ]
  9.   [LOCATION hdfs_path]
  10.   [AS select_statement];

2.1 Create直接创建

  1. CREATE TABLE IF NOT EXISTS db_1128.bf_log_1128(
  2. ip string COMMENT 'remote ip address',
  3. user string ,
  4. req_url string 
  5. )
  6. COMMENT 'BeiFeng Web Access Logs'
  7. ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
  8. STORED AS textfile ;

创建表.png-16kB
查看表信息：

  1. desc [formatted] bf_log_1128;

加载数据：

  1. load data local inpath '/opt/datas/bf_log.txt' into table db_1128.bf_log_1128 ;

加载数据.png-20.7kB
该种方法主要应用于：
创建一个新的表。

2.2 使用like关键字创建表

创建的表与已有的表一致

  1. CREATE TABLE IF NOT EXISTS db_1128.bf_log_1129_like
  2. LIKE db_1128.bf_log_1128 ;

like.png-14.8kB

2.3 使用AS关键字创建一张表

  1. CREATE TABLE IF NOT EXISTS db_1128.bf_log_ctas 
  2. AS select ip, req_url from db_1128.bf_log_1128 ;

AS.png-43.1kB
AS创建成功.png-12.9kB
hive分析的数据结果存放位置
1，存储在hdfs
2，存储在hive表中，临时表

  1.     drop table if exists db_1128.bf_log_ctas;
  2.     CREATE TABLE db_1128.bf_log_ctas 
  3.     AS select ip, req_url from db_1128.bf_log_1128 ;

注：创建一张表，只保留SELECT语句查询出来的字段，并加载数据到表中。

2.4 删除表

  1. drop table mytable [purge] ;

purge参数用于删除元数据丢失的表。

3，创建雇员表和部门表

3.1 创建雇员表

  1. CREATE TABLE IF NOT EXISTS db_1128.emp(
  2. empno int,
  3. ename string,
  4. job string,
  5. mgr int,
  6. hiredate string,
  7. sal double,
  8. comm double,
  9. deptno int
  10. )
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  12. STORED AS textfile ;

3.2 创建部门表

  1. CREATE TABLE IF NOT EXISTS db_1128.dept(
  2. deptno int,
  3. dname string,
  4. loc string
  5. )
  6. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  7. STORED AS textfile ;

创建雇员表和部门表.png-26.4kB

3.3 为两个表导入数据

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

导入数据到雇员表和部门表.png-62.1kB

3.4 清空表里面的数据

实际是删除了对应目录下的数据存储文件而保存了目录

  1. truncate table emp ;
  2. select * from emp ;

清空表数据.png-9.7kB

3.5 从HDFS文件系统上导入数据

创建目录并上传数据

  1. dfs -mkdir -p /user/hadoop/hive/datas;
  2. dfs -put /opt/datas/emp;

加载数据

  1. load data inpath '/user/hadoop/hive/datas/' overwrite into table emp ;

这种方式，会删除源文件。
HDFS导入数据.png-39.6kB

4，Hive的外部表

4.1，创建外部表

  1. CREATE EXTERNAL TABLE IF NOT EXISTS db_1128.emp_ext( 
  2. empno int, 
  3. ename string, 
  4. job string, 
  5. mgr int, 
  6. hiredate string, 
  7. sal double, 
  8. comm double, 
  9. deptno int 
  10. ) 
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
  12. STORED AS textfile ;

4.2，往外部表中加载数据

  1. load data local inpath '/opt/datas/emp.txt' overwrite into table emp_ext ;

外部表.png-29.4kB

4.3，对比外部表与内部表的差异

删除表

  1.     drop table emp ;
  2.     drop table emp_ext ;

4.4，到Hive表在mysql的元数据查看

  1. show databases;
  2. use metastore;
  3. show tables;
  4. desc TBLS;
  5. select TBL_ID, DB_ID,TBL_NAME,TBL_TYPE from TBLS;

表的元数据.png-8kB

4.5，在HDFS上查看表的数据

在浏览器中输入

  1. miaodonghua1.host:50070

表的数据文件.png-7.2kB

4.6，外部表与内部表的结果

内部表：
内部表也称之为MANAGED_TABLED;
默认存储在/use/hive/warehouse下，也可以通过location指定。
删除表时，会删除表数据以及元数据。
外部表：
外表称之为EXTERNAL_TABLE
在创建表时，可以自己指定目录位置(LOCATION)
删除表时只会删除表的元数据不会删除表的数据。
应用
日志存储文件可以用外部表存储。

4.7，外部表可以直接指定路径

创建目录并上传文件

  1.  dfs -mkdir -p /user/hadoop/hivewarehouse/emp;
  2. dfs -mv /user/hive/warehouse/db_1128.db/emp_ext/emp.txt  /user/hadoop/hivewarehouse/emp;

指定目录的方式创建外部表

  1. CREATE EXTERNAL TABLE IF NOT EXISTS db_1128.emp_ext2(
  2. empno int,
  3. ename string,
  4. job string,
  5. mgr int,
  6. hiredate string,
  7. sal double,
  8. comm double,
  9. deptno int
  10. )
  11. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  12. STORED AS textfile 
  13. LOCATION '/user/hive/warehouse/emp';

查看表信息：

  1. select * from db_1128.emp_ext2;

外部表与内部表的区别.png-32kB

5，分区表

分区表实际上就是对应一个HDFS文件系统上的独立文件夹，该文件夹下是该分区所有的数据文件。hive中的分区就是分目录，把一个大的数据集根据业务需要分割成更小的数据集。

在查询时通过WHERE子句中的表达式来选择查询所需要的指定分区，这样查询效率会提高很多。

5.1，创建分区表

  1. CREATE TABLE IF NOT EXISTS db_1128.emp_partition(
  2. empno int,
  3. ename string,
  4. job string,
  5. mgr int,
  6. hiredate string,
  7. sal double,
  8. comm double,
  9. deptno int
  10. )
  11. PARTITIONED BY (month string, day string)
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  13. STORED AS textfile ;

分区表创建.png-16.7kB

5.2，分区表加载数据

  1. load data local inpath '/opt/datas/emp.txt' overwrite into table db_1128.emp_partition  partition(month = '201511',day = '28');

分区数据导入成功.png-9.5kB
查询分区表的数据

  1. select * from emp_partition where month='201511' and day='28';

查询分区表数据.png-37.1kB

6，外分区表

6.1，创建外部分区表

  1. CREATE EXTERNAL TABLE IF NOT EXISTS db_1128.emp_part2(
  2. empno int,
  3. ename string,
  4. job string,
  5. mgr int,
  6. hiredate string,
  7. sal double,
  8. comm double,
  9. deptno int
  10. )
  11. PARTITIONED BY (day string)
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
  13. STORED AS textfile 
  14. LOCATION '/user/hadoop/hivewarehouse/emp_part2';

创建外部分区表.png-25.5kB

6.2，创建目录并上传数据

  1. dfs -mkdir /user/hadoop/hivewarehouse/emp_part2/day=28/ ;
  2. dfs -put /opt/datas/emp.txt /user/hadoop/hivewarehouse/emp_part2/day=28 ;

6.3，实现分区数据：

  1. alter table emp_part2 add partition(day = '28');

查看外部分区
查看外部分区.png-6.3kB
查看外部分区数据.png-30.8kB

7，企业中的应用

7.1，创建分区表

日志文件分区表按三级分区
month/day/time

  1. CREATE EXTERNAL TABLE IF NOT EXISTS db_1128.emp_part_logs(
  2. empno int,
  3. ename string,
  4. job string,
  5. mgr int,
  6. hiredate string,
  7. sal double,
  8. comm double,
  9. deptno int
  10. )
  11. PARTITIONED BY (month string,day string,time string)
  12. ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
  13. STORED AS textfile 
  14. LOCATION '/user/hadoop/hivewarehouse/emp_part_logs';

7.2，业务需求分析：

1)创建分区表
2)第二天需要分析统计
3)11-29:00前数据加载到表中

1，写脚本实现数据加载：

写脚本，通过下面指令上传数据到分区表中(不会使用此方式)：

  1. load data local inpath '/opt/datas/emp00.txt' overwrite into table db_1128.emp_part_logs  partition(month = '201511',day = '28',time = '00');

2，写shell脚本

这种方式可每日无需修改直接使用来实现数据加载
1，在hdfs上创建相应目录

  1. dfs -mkdir -p /user/hadoop/hivewarehouse/emp_part_logs/month=201511/day=28/time=00 ;

2，put数据到对应的目录中区

  1. dfs -put /opt/datas/emp.txt/ /user/hadoop/hivewarehouse/emp_part_logs/month=201511/day=28/time=00;

3,分区数据。

  1. alter table emp_part_logs add partition(month='201511',day='28',time='00') ;

shell脚本实现代码如下

  1. #!/bin/bash
  2. i=1
  3. while [ $i -le 24 ]
  4. do
  5. 	export dmonth=$(date '+%Y%m')
  6.     #export dmonth   
  7.     echo $dmonth
  8. 	export dday=$(date '+%d')
  9.     #export dday
  10.     newpath="/user/hadoop/hivewarehouse/emp_part_logs/month=${dmonth}/day=${dday}/time=${i}"
  11. 	echo $newpath
  12.     #export newpath
  13.     /opt/modules/hadoop-2.5.0/bin/hdfs dfs -mkdir -p ${newpath}
  14. 	/opt/modules/hadoop-2.5.0/bin/hdfs dfs -put /opt/datas/emp.txt ${newpath}
  15.     /opt/modules/hive-0.13.1/bin/hive -S -e "use db_1128;alter table emp_part_logs add partition(month = '${dmonth}',day = '${dday}',time = '${i}') ;"
  17.     let i++
  18. done

执行成功后：
web结果.jpg-73.9kB

  1. show partitions emp_part_logs ;
  2. select * from emp_part_logs where time=1;

看分区结果.jpg-152.6kB

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注