Hive Metadata description and Application

Hive Data storage

1. Underlying data

• The underlying data is stored in distributed storage (HDFS).

2. Metadata

• Metadata information is maintained in MetaStore.
• Hive uses Derby as the default metadata storage database. Derby cannot be accessed by multiple clients at the same time. Therefore, you need to replace Derby with MySQL.

Hive related metadata table

1. Hive version table

(1) the version table

2. Database table

(1) the DBS table

3. Tables and visual charts

(1) the TBLS table

(2) TTABLE_PARAMS table

4. File storage table

(1) the SDS table

(2) the SERDES table

(3) SERDE_PARAMS table

5. Field table

(1) COLUMNS_V2 table

6, partition table

(1) table PARTITIONS

(2) PARTITION_KEYS table

(3) PARTITION_KEY_VALS table

(4) PARTITION_PARAMS table

Three, the application

1. Delete all metadata information based on the tablename passed in

• (1) Stored procedure:

• The stored procedure name is T1, and the input is tbl_id

•   DELIMITER // 
    drop procedure if exists t1;
    create  procedure t1 ( in tbinput int) 
    begin
        declare v_sd_id int ;
        declare v_part_id int ;
        declare v_cd_id int ;
        declare v_serde_id int ;
  
        select tbinput;
        select SD_ID into v_sd_id from tbls where TBL_ID = tbinput;
        select part_id  into v_part_id from partitions where tbl_id = tbinput;
        select cd_id , serde_id into v_cd_id,v_serde_id from sds where sd_id = v_sd_id;
        select v_sd_id,v_part_id,v_cd_id,v_serde_id;
  
        if v_part_id is not  null then 
            delete from partition_params where part_id = v_part_id;
            delete from partition_key_vals where part_id = v_part_id;
        end if;
  
        delete from serdes where serde_id = v_serde_id;
        delete from serde_params where serde_id = v_serde_id;
        delete from columns_v2 where cd_id = v_cd_id;
        delete from sds where sd_id = v_sd_id;
        delete from partitions where tbl_id = tbinput;
        delete from partition_keys where tbl_id = tbinput;
        delete from table_params where tbl_id = tbinput;
        delete from tbls where tbl_id = tbinput;
    end ;
    //
    delimiter ; 
  
    mysql> call t1(tbl_id);
  Copy the code

• (2) Shell encapsulation:

• hive_mysql_delete.sh #! /bin/bash source /usr/local/mysql.bash_profile mysql -uroot -p password Hive database -e "SET FOREIGN_KEY_CHECKS = 0; call t1(tbl_id); SET FOREIGN_KEY_CHECKS = 1; quit" >> /tmp/mysql_delete.logCopy the code