hive表管理

Hive 0.14.0版本，建表管理

https://blog.csdn.net/Thomson617/article/details/86153924

分桶表

分桶对应hdfs目录下的一个个文件,它是将1张大表进行hash(表行索引多分桶数hash,hash值相同的到同一个文件中去),将一份数据拆分成多份,优化查询效率.分桶和MapReduce的分区概念相似
例：

CREATE TABLE page_view(viewTime INT, userid BIGINT,
     page_url STRING, referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'
 PARTITIONED BY(dt STRING, country STRING)
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS
 ROW FORMAT DELIMITED
   FIELDS TERMINATED BY '01'
   COLLECTION ITEMS TERMINATED BY '02'
   MAP KEYS TERMINATED BY '03'
 STORED AS SEQUENCEFILE;

在上面的示例中，page_view表被用户ID分块（聚集），并且在每个桶中，数据按viewTime的递增顺序排序。这样的组织允许用户对集群列进行有效采样 - 在本例中为userid。排序属性允许内部操作员在评估查询时利用更知名的数据结构，同时提高效率。如果任何列是列表或映射，则可以使用MAP KEYS和COLLECTION ITEMS关键字。
CLUSTERED BY和SORTED BY创建命令不会影响数据如何插入表中 - 只会影响它的读取方式。这意味着用户必须小心地通过指定减少器的数量等于桶的数量并在查询中使用CLUSTER BY和SORT BY命令来正确插入数据。

分隔符

指定多字符分隔符。比如指定'$$'分隔符。

add jar /opt/apache-hive-2.1.1-bin/lib/hive-contrib-2.1.1.jar;
list jars;


create EXTERNAL table IF NOT EXISTS tbl_test (
  id                        string ,
  gender                    string ,
  age                       string ,
  level                     string   
)
PARTITIONED BY (id_type STRING, dt INT,  dt_timestamp INT)
ROW FORMAT SERDE 'org.apache.hadoop.hive.contrib.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES ("field.delim"="$$")
STORED AS TEXTFILE
LOCATION '/dm_goddog_tianjin.db/t_ods_waybill_50fields_text'
; 

create table if not exists track(
id int comment'id',
ipname string  comment'ip名称',
zart  decimal(10,5) comment'测试小数')                          
partitioned by (date string,hour string)
row format delimited 
 fields terminated by '	'
 COLLECTION ITEMS TERMINATED BY ','
 MAP KEYS TERMINATED BY ':'
 LINES TERMINATED BY  '
' ;

--删除外部表，清空数据，修改为管理表
ALTER TABLE xxx SET TBLPROPERTIES('EXTERNAL'='False');
desc xxx;
DROP TABLE IF  EXISTS xxx;

表头设置

hive把纯文本放在表对应的位置，就可以查询到数据，但是如果纯文本里面存在表头，会把表头也作为第一行数据。如果又不想在纯文本中去掉表头只要在建表语句中加入如下‘tblproperties ("skip.header.line.count"="1")’即可。
实际建表语句如下所示：

create external table if not exists user(
    name string comment '姓名',
    usr_id  string comment '用户id',
    sex string comment '性别'
)
row format delimited fields terminated by '	'

stored as textfile

tblproperties ("skip.header.line.count"="1")

location '/user/20181018';

分区修复

--方式1：已经有分区，但是找不到目录。
msck repair table dm_goddog.waybill_std;

--方式2：load加载数据时，指定分区
load data local inpath '/home/appdeploy/tianjin/t_ods_waybill_150fields_flume_text/*' overwrite into table dm_goddog.t_ods_waybill_150fields_flume_text  partition ( inc_day = '20200711' );

--方式3：alter修改分区目录
--单分区字段
ALTER TABLE dm_goddog.t_ods_waybill_150fields_flume_text add if not EXISTS PARTITION(`inc_day`='20200601') location 'hdfs://test-cluster/user/hive/warehouse/dm_goddog.db/t_ods_waybill_150fields_flume_text/inc_day=20200601';
--多分区字段
ALTER TABLE dm_goddog_tianjin.t_ods_waybill_125fields_text add if not EXISTS PARTITION(inc_day='20181201',src_company_code='zyh') location 'hdfs://test-cluster/user/hive/warehouse/dm_goddog_tianjin.db/t_ods_waybill_125fields_text/inc_day=20200716/src_company_code=zyh;

修改列，修改列注释

--修改列注释 
alter table fct_user_ctag_today CHANGE COLUMN rpt_tag rpt_tag int comment '1新客，2新转老，3新注册，5老客';

CREATE TABLE test_change (a int, b int, c int);

// 将列名a修改为a1
ALTER TABLE test_change CHANGE a a1 INT; 

// 将列名a修改为a1, 类型为string, 放在字段b后. 新表结构: b int, a1 string, c int
ALTER TABLE test_change CHANGE a a1 STRING AFTER b; 

// 将字段名b修改为b1, 放在第一. 新表结构: b1 int, a string, c int
ALTER TABLE test_change CHANGE b b1 INT FIRST; 

修改表属性： 

alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE');  //内部表转外部表 
alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');  //外部表转内部表

表的重命名：

ALTER TABLE table_name RENAME TO new_table_name

修改表注释:

ALTER TABLE table_name SET TBLPROPERTIES('comment' = '这是表注释!');

 删除多个分区

alter table ods_m.sp_fi_cst_cntr drop partition(inc_day < '20210411')