大数据Hive表的基本操作

本文转载自微信公众号「java大数据与数据仓库」，作者柯同学。转载本文请联系Java大数据与数据仓库公众号。 

1. 创建表

create table语句遵从sql语法习惯，只不过Hive的语法更灵活。例如，可以定义表的数据文件存储位置，使用的存储格式等。

1. create table if not exists test.user1( 
2. name string comment 'name', 
3. salary float comment 'salary', 
4. address struct<country:string, city:string> comment 'home address' 
5. ) 
6. comment 'description of the table' 
7. partitioned by (age int) 
8. row format delimited fields terminated by '\\t' 
9. stored as orc; 

没有指定external关键字，则为管理表，跟MySQL一样，if not exists如果表存在则不做操作，否则则新建表。comment可以为其做注释，分区为age年龄，列之间分隔符是\\t，存储格式为列式存储orc，存储位置为默认位置，即参数hive.metastore.warehouse.dir(默认：/user/hive/warehouse)指定的hdfs目录。

2. 拷贝表

使用like可以拷贝一张跟原表结构一样的空表，里面是没有数据的。

1. create table if not exists test.user2 like test.user1; 

3. 查看表结构

通过desc [可选参数] tableName命令查看表结构，可以看出拷贝的表test.user1与原表test.user1的表结构是一样的。

1. hive> desc test.user2; 
2. OK 
3. name                    string                  name                 
4. salary                  float                   salary               
5. address                 struct<country:string,city:string>  home address         
6. age                     int                                          
7.  
8. # Partition Information          
9. # col_name                data_type               comment              
10.  
11. age                     int          

也可以加formatted，可以看到更加详细和冗长的输出信息。

1. hive> desc formatted test.user2; 
2. OK 
3. # col_name                data_type               comment              
4.  
5. name                    string                  name                 
6. salary                  float                   salary               
7. address                 struct<country:string,city:string>  home address         
8.  
9. # Partition Information          
10. # col_name                data_type               comment              
11.  
12. age                     int                                          
13.  
14. # Detailed Table Information          
15. Database:               test                      
16. Owner:                  hdfs                      
17. CreateTime:             Mon Dec 21 16:37:57 CST 2020      
18. LastAccessTime:         UNKNOWN                   
19. Retention:              0                         
20. Location:               hdfs://nameservice2/user/hive/warehouse/test.db/user2     
21. Table Type:             MANAGED_TABLE             
22. Table Parameters:          
23.     COLUMN_STATS_ACCURATE   {\\"BASIC_STATS\\":\\"true\\"} 
24.     numFiles                0                    
25.     numPartitions           0                    
26.     numRows                 0                    
27.     rawDataSize             0                    
28.     totalSize               0                    
29.     transient_lastDdlTime   1608539877           
30.  
31. # Storage Information          
32. SerDe Library:          org.apache.Hadoop.hive.ql.io.orc.OrcSerde     
33. InputFormat:            org.apache.hadoop.hive.ql.io.orc.OrcInputFormat   
34. OutputFormat:           org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat      
35. Compressed:             No                        
36. Num Buckets:            -1                        
37. Bucket Columns:         []                        
38. Sort Columns:           []                        
39. Storage Desc Params:          
40.     field.delim             \\t                   
41.     serialization.format    \\t            

4. 删除表

这跟sql中删除命令drop table是一样的：

1. drop table if exists table_name; 

对于管理表(内部表)，直接把表彻底删除了;对于外部表，还需要删除对应的hdfs文件才会彻底将这张表删除掉，为了安全，通常hadoop集群是开启回收站功能的，删除外表表的数据就在回收站，后面如果想恢复也是可以恢复的，直接从回收站mv到hive对应目录即可。

5. 修改表

大多数表属性可以通过alter table来修改。

5.1 表重命名

1. alter table test.user1 rename to test.user3; 

5.2 增、修、删分区

增加分区使用命令alter table table_name add partition(...) location hdfs_path

1. alter table test.user2 add if not exists 
2. partition (age = 101) location '/user/hive/warehouse/test.db/user2/part-0000101' 
3. partition (age = 102) location '/user/hive/warehouse/test.db/user2/part-0000102' 

修改分区也是使用alter table ... set ...命令

1. alter table test.user2 partition (age = 101) set location '/user/hive/warehouse/test.db/user2/part-0000110' 

删除分区命令格式是alter table tableName drop if exists partition(...)

1. alter table test.user2 drop if exists partition(age = 101) 

5.3 修改列信息

可以对某个字段进行重命名，并修改位置、类型或者注释：

修改前：

1. hive> desc user_log; 
2. OK 
3. userid                  string                                       
4. time                    string                                       
5. url                     string             

修改列名time为times，并且使用after把位置放到url之后，本来是在之前的。

1. alter table test.user_log 
2. change column time times string 
3. comment 'salaries' 
4. after url; 

再来看表结构：

1. hive> desc user_log; 
2. OK 
3. userid                  string                                       
4. url                     string                                       
5. times                   string                  salaries             

time -> times，位置在url之后。

5.4 增加列

hive也是可以添加列的：

1. alter table test.user2 add columns ( 
2. birth date comment '生日', 
3. hobby string comment '爱好' 
4. ); 

5.5 删除列

删除列不是指定列删除，需要把原有所有列写一遍，要删除的列排除掉即可：

1. hive> desc test.user3; 
2. OK 
3. name                    string                  name                 
4. salary                  float                   salary               
5. address                 struct<country:string,city:string>  home address         
6. age                     int                                          
7.  
8. # Partition Information          
9. # col_name                data_type               comment              
10.  
11. age                     int               

如果要删除列salary，只需要这样写：

1. alter table test.user3 replace columns( 
2. name string, 
3. address struct<country:string,city:string> 
4. ); 

这里会报错：

1. FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replacing columns cannot drop columns for table test.user3. SerDe may be incompatible 

这张test.user3表是orc格式的，不支持删除，如果是textfile格式，上面这种replace写法是可以删除列的。通常情况下不会轻易去删除列的，增加列倒是常见。

5.6 修改表的属性

可以增加附加的表属性，或者修改属性，但是无法删除属性：

1. alter table tableName set tblproperties( 
2.     'key' = 'value' 
3. ); 

举例：这里新建一张表：

1. create table t8(time string,country string,province string,city string) 
2. row format delimited fields terminated by '#'  
3. lines terminated by '\\n'  
4. stored as textfile; 

这条语句将t8表中的字段分隔符'#'修改成'\\t';

1. alter table t8 set serdepropertyes('field.delim'='\\t'); 
   大数据技术的战略意义不在于掌握庞大的数据信息，而在于对这些含有意义的数据进行专业化处理。换而言之，如果把大数据比作一种产业，那么这种产业实现盈利的关键，在于提高对数据的“加工能力”，通过“加工”实现数据的“增值”。