Hive的基本操作

1.3HIve的表操作（接着昨天的继续学习）

1.3.2显示表

show tables;show tables like 'u*';desc t_person;desc formatted students; // 更加详细

1.3.3加载数据

1、使用hdfs dfs -put '本地数据' 'hive表对应的HDFS目录下'

2、使用 load data

下列命令需要在hive shell里执行

create table IF NOT EXISTS students2( id bigint, name string, age int, gender string, clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';// 将HDFS上的/input1目录下面的数据 移动至 students表对应的HDFS目录下，注意是 **移动、移动、移动**load data inpath '/input1/students.txt' into table students;

• 注意：使用hdfs导入数据至hive，使用的是剪切操作，即原hdfs路径下的文件在被导入到hive中之后，原路径下的文件就不复存在

// 清空表truncate table students;// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 **原文件不会被删除,是复制，不是移动**load data local inpath '/usr/local/soft/data/students.txt' into table students;// overwrite 覆盖加载load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

3、create table xxx as SQL语句

4、insert into table xxxx SQL语句 （没有as）

create table IF NOT EXISTS students3( id bigint, name string, age int, gender string, clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';// 将 students表的数据插入到students2 这是复制 不是移动 students表中的数据不会丢失insert into table students2 select * from students;// 覆盖插入 把into 换成 overwriteinsert overwrite table students2 select * from students;

1.3.4修改列

查询表结构

desc students2;

添加列

alter table students2 add columns (education string);

• 添加列之后，查询表的数据，新增的那一列是没有具体的数据进行映射的，所以全是null
• 当向其中插入新的数据时，新的数据和原先的数据在HDFS中看似是分开的，但是其实就是一个表数据，
• 注意的是新的数据插入之后不会影响之前的数据，在HDFS中没有对应的数据，就相当于没有映射过来就是null

查询表结构

desc students2;

更新列

alter table stduents2 change education educationnew string;

1.3.5删除表

drop table students2;

1.4Hive内外部表

面试题：内部表和外部表的区别？如何创建外部表？工作中使用外部表

1.4.1hive内部表

当创建好表的时候，HDFS会在当前表所属的库中创建一个文件夹

当设置表路径的时候，如果直接指向一个已有的路径,可以直接去使用文件夹中的数据

当load数据的时候，就会将数据文件存放到表对应的文件夹中

而且数据一旦被load，就不能被修改

我们查询数据也是查询文件中的文件,这些数据最终都会存放到HDFS

当我们删除表的时候，表对应的文件夹会被删除，同时数据也会被删除

默认建表的类型就是内部表

// 内部表create table students_internal( id bigint, name string, age int, gender string, clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LOCATION '/input2';hive> dfs -put /usr/local/soft/data/students.txt /input2/;

1.4.1Hive外部表

外部表说明

​外部表因为是指定其他的hdfs路径的数据加载到表中来，所以hive会认为自己不完全独占这份数据

​删除hive表的时候，数据仍然保存在hdfs中，不会删除。

删除外部表只会删除hive中的映射出来的数据表，以及存在MySQL中的数据的元数据信息。

// 外部表create external table students_external( id bigint, name string, age int, gender string, clazz string)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','LOCATION '/hive_test/input3';hive> dfs -put /usr/local/soft/data/students.txt /input3/;

删除表测试一下：

hive> drop table students_internal;Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/CurrentOKTime taken: 0.474 secondshive> drop table students_external;OKTime taken: 0.09 secondshive> 

一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用

外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch......

设计外部表的初衷就是 让 表的元数据 与 数据 解耦

• 操作案例: 分别创建dept，emp，salgrade。并加载数据。

创建数据文件存放的目录

hdfs dfs -mkdir -p /bigdata/hive_test1/depthdfs dfs -mkdir -p /bigdata/hive_test1/emphdfs dfs -mkdir -p /bigdata/hive_test1/salgrade

• 创建dept表

CREATE EXTERNAL TABLE IF NOT EXISTS dept ( DEPTNO int, DNAME string, LOC string) row format delimited fields terminated by ','location '/hive_test/dept';10,ACCOUNTING,NEW YORK20,RESEARCH,DALLAS30,SALES,CHICAGO40,OPERATIONS,BOSTON

• 创建emp表

CREATE EXTERNAL TABLE IF NOT EXISTS emp ( EMPNO int, ENAME string, JOB string, MGR int, HIREDATE date, SAL int, COMM int, DEPTNO int ) row format delimited fields terminated by ',' location '/hive_test/emp'; 7369,SMITH,CLERK,7902,1980-12-17,800,null,207499,ALLEN,SALESMAN,7698,1981-02-20,1600,300,307521,WARD,SALESMAN,7698,1981-02-22,1250,500,307566,JONES,MANAGER,7839,1981-04-02,2975,null,207654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,307698,BLAKE,MANAGER,7839,1981-05-01,2850,null,307782,CLARK,MANAGER,7839,1981-06-09,2450,null,107788,SCOTT,ANALYST,7566,1987-07-13,3000,null,207839,KING,PRESIDENT,null,1981-11-17,5000,null,107844,TURNER,SALESMAN,7698,1981-09-08,1500,0,307876,ADAMS,CLERK,7788,1987-07-13,1100,null,207900,JAMES,CLERK,7698,1981-12-03,950,null,307902,FORD,ANALYST,7566,1981-12-03,3000,null,207934,MILLER,CLERK,7782,1982-01-23,1300,null,10

• 创建salgrade表

CREATE EXTERNAL TABLE IF NOT EXISTS salgrade ( GRADE int, LOSAL int, HISAL int) row format delimited fields terminated by ','location '/hive_test/salgrade';1,700,12002,1201,14003,1401,20004,2001,30005,3001,9999

1.5Hive导出数据

将表中的数据备份

• 将查询结果存放到本地

//创建存放数据的目录mkdir -p /usr/local/soft/bigdata//导出查询结果的数据(导出到Node01上)insert overwrite local directory '/usr/local/soft/bigdata/person_data' select * from t_person;

• 按照指定的方式将数据输出到本地

-- 创建存放数据的目录mkdir -p /usr/local/soft/bigdata-- 导出查询结果的数据insert overwrite local directory '/usr/local/soft/bigdata/hive_test1/person' ROW FORMAT DELIMITED fields terminated by ',' collection items terminated by '-' map keys terminated by ':' lines terminated by '\n' select * from t_person;insert overwrite local directory '/usr/local/soft/bigdata/hive_test1/stu' ROW FORMAT DELIMITED fields terminated by ',' lines terminated by '\n' select clazz,count(1) as count from students group by clazz;

• 将查询结果输出到HDFS

将sql语句的查询结果输出到HDFS中时，在HDFS上是直接将该结果存放在指定的文件夹里。

-- 创建存放数据的目录hdfs dfs -mkdir -p /bigdata/hive_test1/copy-- 导出查询结果的数据insert overwrite directory '/bigdata/copy2' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' select * from students

• 直接使用HDFS命令保存表对应的文件夹

// 创建存放数据的目录hdfs dfs -mkdir -p /bigdata/person// 使用HDFS命令拷贝文件到其他目录hdfs dfs -cp /hive/warehouse/t_person/* /bigdata/person

• 将表结构和数据同时备份
• 将数据导出到HDFS

将数据导出到HDFS中时，会在指定的文件目录下产生一个该数据的元数据文件信息_metadata和一个用于存放真实数据的文件夹 data，在该文件夹下面才是数据文件

//创建存放数据的目录hdfs dfs -mkdir -p /bigdata/copy//导出查询结果的数据export table t_person to '/bigdata/copy';

​删除表结构

drop table t_person;

​恢复表结构和数据

import from '/bigdata;

注意：时间不同步，会导致导入导出失败