hive 表操作语句

Hive与表操作有关的语句

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]

CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXIST 选项来忽略这个异常。
EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCE 。
有分区的表可以在创建的时候使用 PARTITIONED BY 语句。一个表可以拥有一个或者多个分区，每一个分区单独存在一个目录下。而且，表和分区都可以对某个列进行 CLUSTERED BY 操作，将若干个列放入一个桶（bucket）中。也可以利用SORT BY 对数据进行排序。这样可以为特定应用提高性能。
创建普通的表：
create table test_table (id int,name string,no int) row format delimited fields terminated by ',' stored as textfile ;//指定了字段的分隔符，hive只支持单个字符的分隔符。hive默认的分隔符是\001
创建带有partition的表：
create table test_partition (id int,name string,no int)partitioned by (dt string) row format delimited fields terminated by ',' stored as textfile ;
load data local inpath '/home/zhangxin/hive/test_hive.txt' overwrite into table test_partition partition (dt='2012-03-05');
创建带有Bucket的表：
create table test_bucket (id int,name string,no int)partitioned by (dt string) clustered by (id) into 3 buckets row format delimited fields terminated by ',' stored as textfile ;
创建external表：
create external table test_external (id int,name string,no int)row format delimited fields terminated by ',' location '/home/zhangxin/hive/test_hive.txt';
创建与已知表相同结构的表 Like：只复制表的结构，而不复制表的内容。
create table test_like_table like test_bucket;

2.删除表：
Drop Table tablename；

删除一个内部表的同时会同时删除表的元数据和数据。删除一个外部表，只删除元数据而保留数据。

3.修改已经存在的表：
alter table
Alter table 语句允许用户改变现有表的结构。用户可以增加列/分区,表本身重命名。

1》增加分区 Add Partitions：
ALTER TABLE table_name ADD partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ]
其中partition_spec 的格式为：PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
用户可以用 ALTER TABLE ADD PARTITION 来向一个表中增加分区。当分区名是字符串时加引号。

alter table test_partition add partition (dt='2012-03-06') location '/home/zhangxin/hive/test_hive.txt';

2》删除分区 drop Partition：
ALTER TABLE table_name DROP partition_spec, partition_spec,...
用户可以用 ALTER TABLE DROP PARTITION 来删除分区。分区的元数据和数据将被一并删除。

alter table test_partition drop partition (dt='2012-03-06')

3》对表进行重命名 rename to：
ALTER TABLE table_name RENAME TO new_table_name
这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。换而言之，老的表名并未“释放”，对老表的更改会改变新表的数据。
alter table test_partition rename to new_test_partition;

4》对表中的某一列进行修改，包括列的名称/列的数据类型/列的位置/列的注释
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
这个命令可以允许用户修改一个列的名称、数据类型、注释或者位置
create table test_col_change (a int,b int, c int);

修改列的名称，后面一定要加上数据类型：
alter table test_col_change change a a1 int;

alter table test_col_change change b b1 string;

ALTER TABLE test_col_change CHANGE a a1 INT; 将 a 列的名字改为 a1.

ALTER TABLE test_col_change CHANGE a a1 STRING AFTER b; 将 a 列的名字改为 a1，a 列的数据类型改为 string，并将它放置在列 b 之后。新的表结构为： b int, a1 string, c int.

ALTER TABLE test_col_change CHANGE b b1 INT FIRST; 会将 b 列的名字修改为 b1, 并将它放在第一列。新表的结构为： b1 int, a string, c int.

注意：对列的改变只会修改 Hive 的元数据，而不会改变实际数据。用户应该确定保证元数据定义和实际数据结构的一致性。

5》添加/替换列Add/Replace Columns
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
ADD COLUMNS 允许用户在当前列的末尾增加新的列，但是在分区列之前。

alter table test_col_change add columns (d int);
describe test_col_change;
OK
a1 int
b1 string
c int
d int

REPLACE COLUMNS 删除以后的列，加入新的列。只有在使用 native 的 SerDE（DynamicSerDe or MetadataTypeColumnsetSerDe）的时候才可以这么做。
alter table test_col_change replace columns (c int);
describe test_col_change;
OK
c int

6》修改表的属性Alter Table Properties:

ALTER TABLE table_name SET TBLPROPERTIES table_properties
table_properties: : (property_name = property_value, property_name = property_value, ... )
用户可以用这个命令向表中增加 metadata，目前 last_modified_user，last_modified_time 属性都是由 Hive 自动管理的。用户可以向列表中增加自己的属性。可以使用 DESCRIBE EXTENDED TABLE 来获得这些信息。
alter table test_col_change set tblproperties ('key1'='value1');
可以通过 describe extended test_col_change; 查看表的属性信息。

7》修改表的序列化和反序列化属性：
ALTER TABLE table_name SET SERDE serde_class_name [WITH SERDEPROPERTIES serde_properties]
ALTER TABLE table_name SET SERDEPROPERTIES serde_properties
serde_properties: : (property_name = property_value, property_name = property_value, ... )

这个命令允许用户向 SerDe 对象增加用户定义的元数据。Hive 为了序列化和反序列化数据，将会初始化 SerDe 属性，并将属性传给表的 SerDe。如此，用户可以为自定义的 SerDe 存储属性。

8》修改表的文件存储格式组织方式：
ALTER TABLE table_name SET FILEFORMAT file_format
ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name, ...)] INTO num_buckets BUCKETS
这个命令修改了表的物理存储属性。

4.将数据加载到表中：
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
当数据被加载至表中时，不会对数据进行任何转换。Load 操作只是将数据复制/移动至 Hive 表对应的位置。
filepath 可以是：
相对路径，例如：project/data1
绝对路径，例如： /user/hive/project/data1
包含模式的完整 URI，例如：hdfs://namenode:9000/user/hive/project/data1
加载的目标可以是一个表或者分区。如果表包含分区，必须指定每一个分区的分区名。
filepath 可以引用一个文件（这种情况下，Hive 会将文件移动到表所对应的目录中）或者是一个目录（在这种情况下，Hive 会将目录中的所有文件移动至表所对应的目录中）。
如果指定了 LOCAL，那么：load 命令会去查找本地文件系统中的 filepath。如果发现是相对路径，则路径会被解释为相对于当前用户的当前路径。用户也可以为本地文件指定一个完整的 URI，比如：file:///user/hive/project/data1.load 命令会将 filepath 中的文件复制到目标文件系统中。
目标文件系统由表的位置属性决定。被复制的数据文件移动到表的数据对应的位置。如果没有指定 LOCAL 关键字，如果 filepath 指向的是一个完整的 URI，hive 会直接使用这个 URI。 否则：如果没有指定 schema 或者 authority，Hive 会使用在 hadoop 配置文件中定义的 schema 和 authority，fs.default.name 指定了 Namenode 的 URI。如果路径不是绝对的，Hive 相对于 /user/ 进行解释。Hive 会将 filepath 中指定的文件内容移动到 table （或者 partition）所指定的路径中。如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容（如果有）会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。