Hive的DDL和DML管理库表结构以及数据

HIVE DDL

1. 数据库DDL

   -- 新建数据库并指定文件夹
   create database test comment 'Just for test' location '/abcd' with dbproperties('aaa'='bbb');
   

   -- 查询数据库列表
   show databases;
   -- 查询某一个数据库详情
   desc database test;
   -- 查询更详细信息
   desc database extended test;
   

   -- 修改数据库（只能修改属性）
   alter database test set dbproperties('aaa'='ccc');
   

   -- 删除数据库
   drop database test;
   -- 强制删除（慎用）
   drop database test cascade;
   

2. 表的DDL

   -- 普通建表
   create table student2(id int comment 'id', name string COMMENT 'nnnn')
   COMMENT 'student2 shi wo'
   row format delimited fields terminated by '\t'
              collection items terminated by '_'
              map keys         terminated by ':'
   tblproperties('aaa'='bbb');
   
   -- 根据查询结果建表
   create table xxx as 
   select name, 
          friends[0]             friend1, 
          children['xiao song']  xiaosong, 
          address.street 
   from test;
   
   -- 拷贝表结构
   create table stu3 like student2;
   

   -- 查询表列表
   show tables;
   
   -- 查询表结构
   desc student2;
   
   -- 查询更详细的信息
   desc formatted student2;
   

   -- 重命名表格
   alter table student2 rename to stuxxx;
   
   -- 修改列信息
   alter table stuxxx change column id idx bigint comment 'idxxx';
   
   -- 追加列信息
   alter table stuxxx add columns (age int comment 'age', daughter string comment 'nver');
   
   -- 替换列信息
   alter table stuxxx replace columns (id string comment 'abc', name string comment 'abcd');
   

   -- 删除表
   drop table stuxxx;
   
   -- 删除表但不删除数据
   truncate table stuxxx;
   

HIVE DML

1. 插入数据

   -- 创建表格student
   create table student (id int, name string) row format delimited fields terminated by '\t';
   
   -- 从本地文件系统将数据导入表格
   load data local inpath '/opt/module/hive/datas/student.txt' into table student;
   
   -- 从本地文件系统中将数据覆盖导入表格
   load data local inpath '/opt/module/hive/datas/student.txt' overwrite into table student;
   
   -- 从HDFS将数据导入表格（覆盖）(会将数据移动进表格的数据文件夹)
   load data inpath '/datas/student.txt' overwrite into table student;
   

   -- 创建一张student2表格
   create table student2 (id int, name string) row format delimited fields terminated by '\t';
   
   -- insert插入
   insert into student2 values (1001, 'zhangsan'),(1002, 'lisi');
   
   -- insert 查询结果(最常用，记住)
   insert into student2 select id, name from student where id > 1002;
   
   -- insert 查询结果(覆盖)
   insert overwrite table student2 select id, name from student where id > 1002;
   

   -- 将查询结果直接建表（不常用）
   create table student3 as select id, name from student;
   
   -- 通过location指定地址加载数据
   -- 首先将student数据上传到/datas
   create external table student4 (id int, name string) 
   row format delimited fields terminated by '\t'
   location '/datas';
   

2. 导出数据

   -- insert导出(了解即可)
   insert overwrite local directory '/opt/module/hive/datas/export/student' select id, name from student;
   
   -- 指定分隔符
   insert overwrite local directory '/opt/module/hive/datas/export/student2' 
   row format delimited fields terminated by ','
   select id, name from student;
   
   -- 向HDFS导出
   insert overwrite directory '/stu2_out' 
   row format delimited fields terminated by ','
   select id, name from student;
   

   -- import 和 export(了解即可)
   export table student to '/stu_out';
   import table stuin from '/stu_out';
   

内部表和外部表

-- 普通的建表(管理表)
create table student (id int, name string) row format delimited fields terminated by '\t';

-- 创建外部表
create external table stu_ex (id int, name string) row format delimited fields terminated by '\t';

-- 向外部表中插入数据
insert into stu_ex select id,name from student;

-- 删除普通表格时，数据会一起删掉
drop table student;

-- 删除外部表，数据不会删
drop table stu_ex;

-- 外部表和内部表转换
create table stu_ex (id int, name string) row format delimited fields terminated by '\t';

-- 通过修改表将内部表转化为外部表(key value都要大写)
alter table stu_ex set tblproperties('EXTERNAL'='TRUE');

-- 将外部表改回管理表
alter table stu_ex set tblproperties('EXTERNAL'='FALSE');