Hive语句

刘超 4天前 ⋅ 4763 阅读   编辑

目录

  1、

  2、索引

  3、

  4、分区

  5、注释

  6、数据
    a、查询
     -- 排除某列的查询

  7、替代语句

  8、统计信息

  9、查询语句

  10、其他

  

一、

  1、创建、修改表

创建表

create table sample_table(

    row_id string,

  name string

row format delimited fields terminated by ','

escaped by '\\'

lines terminated by '\n';

创建表示例

修改表属性语句

alter table table_name set tblproperties(property_name = property_value, property_name = property_value);

修改表属性示例

alter table temp.foreignTradeProductInfo set SERDEPROPERTIES('field.delim'='\u0001');

参数列表

参数 语句 默认值 描述 示例
field.delim row format delimited fields terminated by '' \001 指定列分割符号 'field.delim'='|'
serialization.format 'serialization.format'='|'
line.delim lines terminated by ''   指定行分割符 'line.delim'='\n'
  collection items terminated by ''  
  collection items terminated by '\002'
  map keys terminated by ''   map keys terminated by '\003'
  escaped by ''   指定转义字符,用于将数据进行转义后进行展示 escaped by '\\'
serialization.null.format     指定null实际存储字符  
  stored as orc/parquet
  指定表格式,可以是orc也可以是parquet  
     

  2、模糊搜索表

show tables like '*dm*';

  3、查看表结构

desc formatted table_name;

desc table_name;

  4、重命名表名

alter table table_name rename to new_table_name;

示例

alter table temp.test rename to temp.test1

  5、查看表创建语句

show create table 表名;

示例

show create table student;

  

二、表属性

  1、location

修改location

alter table app_r_day_stat_video set location "hdfs://opera/apps/hive/warehouse/adx_app.db/app_r_day_stat_video";

注意:

a、语句后面加分号,否者可能不生效

  2、Row Format SERDE

org.apache.hadoop.hive.ql.io.orc.OrcSerde orc格式
org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe parquet

  3、STORED AS INPUTFORMAT

org.apache.hadoop.hive.ql.io.orc.OrcInputFormat
org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat

  4、OUTPUTFORMAT

org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat
org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat

  5、TABLPROPERTIES

parquet.compression snappy
transient_lastDdlTime

二、索引

  1、删除索引

create index 索引名 on table 表名(字段名,...) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table 表名;

示例

create index temp_edw_ent_cbuitem_info_index on table temp_edw_ent_cbuitem_info(ent_id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table temp_edw_ent_cbuitem_info_table;

  

  2、查看索引结构

desc 索引表;

注意:

  1、不能再通过show create table查看

示例

desc temp_edw_ent_cbuitem_info_table;

  

  2、删除索引

drop index 索引名 on 表名;

示例

drop index temp_edw_ent_cbuitem_info_index on temp_edw_ent_cbuitem_info;

  

三、

    1、增加列

alter table table_name add columns(col_name data_type [comment col_comment], ...) [cascade];

注意:

  a、add columns允许用户在当前列的末尾增加新的列,但是在分区列之前

  b、cascade会变更新分区的表结构(metadata),同时也变更旧分区的表结构

    2、修改列/删除列

alter table table_name replace columns (col_name data_type [comment col_comment], ...)

删除列advindustry,如下(就是填上要保留的列,删除后partition分区信息需重新生成)

 

 

    3、改变列

可以改变 column name/type/position/comment,语句如下
alter table table_name change [column] col_old_name col_new_name column_type [comment col_comment] [first|after column_name] [cascade]

注释

    a、对列的改变只会修改hive的元数据,而不会改变实际数据。用户应该确定保证元数据定义和实际数据结构的一致性。

    b、cascade会变更新分区的表结构(metadata),同时也变更旧分区的表结构

示例:

    将a列的名字改为a1,a列的数据类型改为string,并将它放置在列b之后。
    alter table test_change change a a1 string after b;

    将 b 列的名字修改为 b1, 并将它放在第一列。
    alter table test_change change b b1 int first

    重名字段

    alter table temp.temp_edw_ent_cbuitem_info change op_scope ce_industry string;

  

四、分区

启动动态分区

set hive.exec.dynamic.partition=true;

set hive.exec.dynamic.partition.mode=nonstrict;

添加分区语法
alter table table_name add [if not exists] partition_spec [location 'location1'] ...

partition_spec:

    partition (partition_col = partition_col_value,partition_col = partiton_col_value, ...)

注意:

    分区名是字符串时加引号

  1、创建分区表

create table `temp.t_app_portal_statistics_temp3_detail`(
  `produc_instance_code` string,
  `product_id` int,
) partitioned by(create_date string);

  

      2、列出表分区

  show partitions t_src_portal_statistics;

       

      3、添加分区

  alter table t_src_portal_statistics add if not exists partition(extract_date='2018-10-29');

     

     4、删除分区

 alter table t_src_portal_statistics drop partition(extract_date='2018-10-29');

     

 alter table src.t_src_sm_cm_link_man drop partition(extract_date<='2018-10-16');

    

alter table src.t_src_boss_bm_org drop partition(extract_date>='2018-11-12');