Hive基本操作-DDL

Hive基本操纵-DDL

一、简介
Hive是基于Hadoop分布式文件系统的，它的数据存储在Hadoop分布式文件系统中。Hive本身是没有专门的数据存储格式，也没有为数据建立索引，只需要在创建表的时候告诉Hive数据中的列分隔符和行分隔符。
Hive中主要包含以下几种数据模型：Table、External Table外部表，Partition分区，Bucket桶。
表：Hive中的表和关系型数据库中的表在概念上很类似，每个表在HDFS中都有相应的目录用来存储表的数据，这个目录可以通过${HIVE_HOME}/conf/hive-site.xml配置文件中的hive.metastore.warehouse.dir属性来配置，这个属性默认的值是/user/hive/warehouse（这个目录在HDFS上），我们可以根据实际的情况来修改这个配置。如果我有一个表wyp，那么在HDFS中会创建/user/hive/warehouse/wyp目录（这里假定hive.metastore.warehouse.dir配置为/user/hive/warehouse）；wyp表所有的数据都存放在这个目录中。这个例外是外部表。
外部表：Hive中的外部表和表很类似，但是其数据不是放在自己表所属的目录中，而是存放到别处，这样的好处是如果你要删除这个外部表，该外部表所指向的数据是不会被删除的，它只会删除外部表对应的元数据；而如果你要删除表，该表对应的所有数据包括元数据都会被删除。
分区：在Hive中，表的每一个分区对应表下的相应目录，所有分区的数据都是存储在对应的目录中。比如wyp表有dt和city两个分区，则对应dt=20131218,city=BJ对应表的目录为/user/hive/warehouse/dt=20161218/city=BJ，所有属于这个分区的数据都存放在这个目录中。
桶：对指定的列计算其hash，根据hash值切分数据，目的是为了并行，每一个桶对应一个文件（注意和分区的区别）。比如将wyp表id列分散至16个桶中，首先对id列的值计算hash，对应hash值为0和16的数据存储的HDFS目录为：/user/hive/warehouse/wyp/part-00000；而hash值为2的数据存储的HDFS 目录为：/user/hive/warehouse/wyp/part-00002。

二、基本命令
命令行基本操作：
进入Hive的安装目录下的bin目录：使用hive命令进入hive的命令行模式

1、显示命令

show databases;# 显示当前数据库列表：
use default;    #选择要使用的数据库：
show tables;    #显示当前数据库下的表：
show partitions;
show functions;
desc extended t_name;
desc formatted table_name;

2、Hive的数据类型。

a、基本数据类型：
    整型：tinyint/smallint/int/bigint
    浮点：float/double
    布尔：boolean
    字符串：string
b、复杂数据类型
    Array:数组类型
    Map：集合类型，包含key->value
    struct：结构类型，可以包含不同数据类型的元素
c、时间类型
    Date:
    Timestamp:

三、DDL操作
1、创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name 
   [(col_name data_type [COMMENT col_comment], ...)] 
   [COMMENT table_comment] 
   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] 
   [CLUSTERED BY (col_name, col_name, ...) 
   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] 
   [ROW FORMAT row_format] 
   [STORED AS file_format] 
   [LOCATION hdfs_path]

说明：
1、CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

2、EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

3、 LIKE 允许用户复制现有的表结构，但是不复制数据。

4、 ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char] | SERDE serde_name
[WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, …)]
用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。

5、 STORED AS
SEQUENCEFILE|TEXTFILE|RCFILE
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

6、CLUSTERED BY
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
把表（或者分区）组织成桶（Bucket）有两个理由：
（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

案例：
a、建表（内部）

create table User(
  name string,
  age int,
  tele string
)
row format delimited 
fields terminated by ‘\t’    //列的分隔符
lines terminated by ‘\n’                         //行的分隔符
stored as textfile                           //存储方式，三种
location ‘’;                                //表的存储位置

b、 外部表

create external table user(
)
row format delimited 
fields terminated by ‘\t’    //列的分隔符
lines terminated by ‘\n’                         //行的分隔符
stored as textfile                           //存储方式，三种
location ‘’;                                //表的存储位置

c、 创建分区表

create table student_p(Sno int,Sname string,Sex string,Sage int,Sdept string) 
partitioned by(part string)
row format delimited fields terminated by ','stored as textfile;

d、桶表

create table student(id int,age int,name string)
partitioned by(stat_date string)
clustered by(id)
sorted by(age)
into 2 buckets
row format delimited fields terminated by ‘,’;

2、修改/增加分区

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)

ALTER TABLE table_name DROP partition_spec, partition_spec,...

案例：

alter table student_p add partition(part='a') partition(part='b');

3、重新命名表名

ALTER TABLE table_name RENAME TO new_table_name

4、增加、更新列

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) 

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]