HIVE的表操作语句

Love The Way You Lie 2022-05-09 11:38 242阅读 0赞

转自：http://hi.baidu.com/zhangxinandala/item/49752510cc7ad08789a95617

**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 formatdelimited

fieldsterminated by ','

stored astextfile;//指定了字段的分隔符，hive只支持单个字符的分隔符。hive默认的分隔符是\\001

**创建带有partition的表：**  
create table test\_partition (

id int,

name string,

no int)

partitioned by(dt string)

row formatdelimited

fieldsterminated by ','

stored astextfile;

load data local inpath '/home/zhangxin/hive/test\_hive.txt' overwrite into tabletest\_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 10 buckets  --将id这一列分到10个桶中。

row formatdelimited

fieldsterminated by ','

stored astextfile;

关于分桶：对列进行分桶，本质上，在进行reduce的时候，会对列的值进行hash，然后将hash的值放到特定的桶中（有点像distribute by）。

注意在写入分桶数据的时候，需要指定：

sethive.enforce.bucketing=true;

更多关于bucketed table的信息，见：

[http://hive.apache.org/docs/r0.8.1/language\_manual/working\_with\_bucketed\_tables.html][http_hive.apache.org_docs_r0.8.1_language_manual_working_with_bucketed_tables.html]

**创建external表：**  
create external table test\_external (

id int,

name string,

no int)

row formatdelimited

fieldsterminated 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; 将 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/ReplaceColumns**  
ALTER TABLE table\_name ADD|REPLACE COLUMNS (col\_name data\_type \[COMMENTcol\_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 orMetadataTypeColumnsetSerDe）的时候才可以这么做。  
alter table test\_col\_change replace columns (c int);  
describetest\_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 SERDEPROPERTIESserde\_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 中的文件名冲突，那么现有的文件会被新文件所替代。

[http_hive.apache.org_docs_r0.8.1_language_manual_working_with_bucketed_tables.html]: http://hive.apache.org/docs/r0.8.1/language_manual/working_with_bucketed_tables.html