hive中关于数据库和表的常见操作详解

桃扇骨 2022-05-25 10:40 438阅读 0赞

hive是属于批处理系统，所以最大的缺点就是不支持行级别的数据插入，删除，更新，也不支持事务机制。hive中数据库的概念本质上也仅仅是mysql（或别的存储元数据的数据库）表中一个目录或者命令空间（namespace），但是对于大集群来说，这又必不可少，因为只有这样才能将生产表组成逻辑组，形成组织层次结构。

### 官网关于数据库和表的操作：[官网关于数据库和表的常见操作][Link 1]   **1.hive关于数据库的操作** ###

1.hive中创建数据库
     hive> create database（或者用schema） fdm_sor; 
     注意这里database和schema是一样的，database是hive后续版本后加入的名称。可以互替。
     #或者防止库存在报错，加上判断语句
     hive>  create database if not exists fdm_sor;
      因为hive中数据实际存放在hdfs上，所以每个hive数据库创建时
      hdfs上hive仓库中/user/hive/warehouse中都会建立一个对应的子目录。
      比如 /user/hive/warehouse/fdm_sor.db,但是对于默认default数据库，则没有自己的目录。
      但是一般创建数据库时也可以指定存储目录（不常用）
     hive> create database fdm_sor_1 
         > location '/home/robot/fdm_sor.db'
       此外还可以创建数据库时给数据库添加描述，尤其很多数据库时，方便后期维护和管理
     hive>create database fdm_sor2 comment '这是我的测试数据库'
    
     2.显示hive中有哪些数据库，以及使用特定数据库
     hive> show databases;
     hive> show databases like 'fdm_.*'  --查看以fdm_开头的数据库名，可以使用正则表达式模糊查询
     hive> use fdm_sor;   --使用fdm_sor数据库，如果不指定使用数据库，默认使用default数据库
    
     3.查看数据库详情
     hive (fdm_sor)> describe database fdm_sor;
        db_name comment location        owner_name      owner_type      parameters
        fdm_sor         hdfs://hadoop102:9000/user/hive/warehouse/fdm_sor.db    robot   USER
     同理如果想查看更加详细的数据库详情，可以使用describe database extended fdm_sor;
    
     4.查看当前数据库下所有的表
     hive>show tables   
     hive > show tables like 'aa.*'
    
     5.删除和修改数据库
     hive (staff)> drop database staff;
       FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException
      (message:Database staff is not empty. One or more tables exist.)
     hive中不允许直接删除一个有表的数据库，用户要想删除数据库，要先删除表，再删除数据库。或者直接加cascade关键字，强制执行
     hive> drop database if exists aaa cascade ;
         OK
        Time taken: 0.169 seconds
    
    6.修改数据库，使用alter
    
    1.ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, ...);   -- (Note: SCHEMA added in Hive 0.14.0)
     
    2.ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;   -- (Note: Hive 0.13.0 and later; SCHEMA added in Hive 0.14.0)
      
    3.ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path; -- (Note: Hive 2.2.1, 2.4.0 and later)

### **2.hive中关于表的常见操作** ###

**1.关于创建表的操作见博客：**  
  [hive创建表的常见操作和各种属性的使用][hive]  
  [Hive中创建表的各种方式以及区别详解][Hive]  
   
**  2.删除表**  
  hive> drop table if exists aaa;  
  对于管理表（内部表）表的元数据和表内的数据都会被删除，如果hdfs开启了回收站功能，会被移到.trash目录下  
  对于外部表，表的元数据信息同样会被删除，只是表中的数据还会存放在hdfs上，不会被删除

### **3.修改表，只修改表的模式，不会修改表中数据（这是比较常用的命令）** ###

1.给表重命名,但是表的数据存放位置不会变
    hive> alter table aaa rename to bbbb;
    
    2.给表增加，修改和删除分区
    hive> alter table aaa add if not exists partition(year='2011',month='11')  
    hive>alter table aaa add  partition (statis_date='201111') location‘/user/hive/warehouse/201111’
                              partition(statis_date='201112')location 'user/hive/warehouse/201112'   
    删除特定分区，如果是内部表，元数据和数据同时被删除，如果是外部表分区内数据不会被删除
     hive> alter table aaa drop partition(statis_date='2011112' )
    
    3.给表增加列：新增的字段在最后面,对于原表中的数据，给新增的字段赋值为null.
    hive> alter table  staff add columns (
          id  int comment'编号',
          name string comment'名字'
         )
    4.调整表中字段的位置:使用first ,after调增字段位置，比如讲grade调整到id后面呢
    hive (staff)> alter table staff_hive change column  grade  grade int  after id ;
    hive (staff)> select *  from staff_hive;                                        
    staff_hive.id   staff_hive.grade        staff_hive.name staff_hive.sex
    1       NULL    male    NULL
    2       NULL    male    NULL
    3       NULL    female  NULL
    4       NULL    male    NULL
    注意：调整字段位置式，如grade即使不更改字段名，也要写上旧的字段名和字段类型。
          特别注意是这种修改只修改元数据信息，不会修改表中数据位置。
          如果不对应的修改数据位置，则会出现匹配不上，null值的情况。
    5.表中删除或替换列（实际几乎不用,因为replace语句只能修改内置SerDe模块的表：DynamicSerde,MetadataTypedColumnsSerde）
    alter table t_fin_demo replace columns(
    .....
    )
    
    6.修改表的属性
    hive>alter table staff set tblproperties('aa'='张飞')
    
    7.修改表的存储属性：比如给修改表的存储格式和SerDe属性，可以精确到分区，实际也很少用。
    --1.比如如下，我们将表中某个分区的存储属性改成rcfile，默认使用的是textfile;
    alter table t_fin_demo partition(statis_date ='201902')
    set fileformat rcfile;
    
    --2.查看建表属性 ：存储格式为textfile.
    hive (fdm_sor)> show create table t_fin_Demo;
    CREATE  TABLE `t_fin_Demo`(
      `name` string, 
      `id_type` string, 
      .....................
      `create_by` string)
    PARTITIONED BY ( 
      `statis_date` string)
    ROW FORMAT DELIMITED 
      FIELDS TERMINATED BY ',' 
    STORED AS INPUTFORMAT 
      'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 
      'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
    .......................
    --3.查看分区属性,发现当前分区的存储格式改成rcfile。
    hive (fdm_sor)> describe formatted  t_fin_Demo partition(statis_date ='201902');
    
    # Storage Information            
    SerDe Library:          org.apache.hadoop.hive.serde2.columnar.ColumnarSerDe     
    InputFormat:            org.apache.hadoop.hive.ql.io.RCFileInputFormat   
    OutputFormat:           org.apache.hadoop.hive.ql.io.RCFileOutputFormat  
    Compressed:             No                       
    Num Buckets:            -1                       
    Bucket Columns:         []                       
    Sort Columns:           []                       
    Storage Desc Params:             
            field.delim             ,                   
            serialization.format    ,

[Link 1]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterPartition
[hive]: https://blog.csdn.net/qq_26442553/article/details/79747906
[Hive]: https://blog.csdn.net/qq_26442553/article/details/78805443