Hive之HQL数据定义

------------本文笔记整理自《Hadoop海量数据处理：技术详解与项目实战》范东来

HQL数据定义

1.数据库database操作

--创建数据库
> create database test;
> create database if not exists test;
--查看已存在的数据库
> show databases;

--注：数据库在HDFS中的目录，可通过hive-site.xml配置hive.metastore.warehouse.dir指定
--  默认路径：/user/hive/warehouse
--创建数据库，同时指定存放HDFS的路径（不采用默认路径）
> create database test location '/user/hadoop/temp';
--查看数据库信息（包含存储路径）
> describe database test;
OK
test    hdfs://master:9000/user/hive/warehouse/test.db    root    USER

--切换目标数据库
> use test;

--删除数据库，默认不允许删除非空数据库
> drop database if exists test;
--删除数据库（级联删），不推荐使用
> drop database if exists test cascade;

2.表table操作（管理表、外部表、分区表）

2.1.创建表（管理表、外部表）

--查看数据库test中已存在的表
> use test;
> show tables;
--或者
> show tables in test;

--创建表
> create table if not exists test.student (
>   name string comment 'student name 列级注释',
>   age int comment 'student age',
>   course array<string> comment '课程数组',
>   body map<string,float> comment '身体参数的键值对集合',
>   address struct<street:string,city:string,state:string> comment '地址参数结构体'
> ) comment 'the info of student 表级注释'
> row format delimited
> fields terminated by '\001' --列分隔符
> collection items terminated by '\002' --集合元素分隔符
> map keys terminated by '\003' --map键值对分隔符
> lines terminated by '\n' --行分隔符
> stored as textfile --指定文件存储格式（可省略，默认testfile文本）
> location '/user/hive/warehouse/test.db/student'; --指定表在HDFS中的存储位置

--查看表结构信息及列注释
> desc student;
--查看表级别注释及更多详细信息
> desc extended student;
--或者
> desc formatted student; --（推荐，输出的信息已经格式化，方便查看）

--复制另一张表的结构创建表
> create table if not exists test.student2 like test.student;

--管理表：默认建的表都是管理表（托管表），由hive管理表数据，删除表时，hive将删除管理表的元数据和数据。
--外部表：建表时指定为外部表，用于数据需要多种工具（如Pig、Hive）分析使用时，数据并非完全是hive拥有，删除表时，hive将只删除表的元数据，不删除数据。
--外部表创建
> create external table if not exists test.student (
> name string)
> location 'user/test/x'; --外部表不一定要指定为hive外部存储路径

 2.2.创建表（分区表）

--分区表：对表进行分区存储，将表水平切分，能提升查询性能（分区相当于简易索引）
--分区表创建
> create table student (
> name string,
> age int)
> partitioned by (province string, city string); --分区字段设定，不能与表结构中字段重复
--分区表在HDFS上的组织方式
 /user/hive/warehouse/student/province=sichuan/city=chengdu
 /user/hive/warehouse/student/province=sichuan/city=dazhou
 ...
 /user/hive/warehouse/student/province=jiangsu/city=nanjing
 ...

--分区表的查询与普通表一样
--<注1：对于直接命中分区的查询，不会执行MR作业>
--<注2：一般以 创建时间 或者 修改时间 进行分区>
> select * from student where province = 'sichuan' and city = 'chengdu';

--（分区查询限制）设定安全措施：防止执行一条包含所有分区的查询，浪费集群时间资源
> set hive.mapred.mode=strict; --只能生效当前会话
--另一种措施（生效所有会话）：配置hive-site.xml中hive.mapred.mode=strict(默认nostrict)

--查看分区情况
> show partitions student;
--查看分区表详细信息
> describe extended student;

--外部表跟管理表都能设置成分区表
--外部分区表创建
> create external table student (
> name string,
> age int)
> partitioned by (province string, city string); 
--外部分区表有别于普通分区表，需要指定分区键值和存储路径，否则查询不到数据
> alter table student add partition (province = 'sichuan' , city = 'chengdu')
> loaction 'hdfs://master:9000/student/sichuan/chengdu';

2.3.删除/修改表 

--删除表
> drop table test;
--或者
> drop table if exists test;

--修改表：重命名
> alter table test rename to test2;

-***分区删除修改操作
--分区：增加分区（通常是对外部表操作，尽管也能操作管理表）
> alter table test add partition(x='x1',y='y1') location '/user/test/x1/y1';
--分区：修改分区路径
> alter table test add partition(x='x1',y='y1') set location '/user/test/x1/y1';
-*分区：删除分区
> alter table test drop partiton(x='x1',y='y1');
  --注：分区表似乎无法drop掉（重启hive后能删除，不知什么原因），只能删除分区数据

--修改列信息
> alter table test 
> change column id uid int --将id改为uid，类型int
> comment 'the unique id'
> after name; --并移至name字段后

--增加列
> alter table test add columns (new_col int, new_col2 string); --必须加括号"()"

--删除并替换列（删除原先所有列，并替换成新定义列，不需要列数量对应）
--<注：需要保证表数据与修改后的元数据模式匹配，否则数据将会不可用>
> alter table test replace columns (new_col int, new_col2 string);

*2.4修改表存储属性

--更改表注释
> ALTER TABLE table_name SET TBLPROPERTIES ('comment' = new_comment);
--更改表的列分隔符
> ALTER TABLE table_name SET SERDEPROPERTIES ('field.delim' = ',');

参考链接：1.LanguageManualDDL-AddSerDeProperties