Hive基本操作

一、DDL

1.1 创建内部表 mytable

hive

create table if not exists mytable(sid int,sname string) 
row format delimited 
fields terminated by ','
 stored as textfile;

#本地导入/usr/local/src/apache-hive-2.3.4-bin/test/mytable
load data local inpath '/usr/local/src/apache-hive-2.3.4-bin/test/mytable' overwrite into table mytable;


create table if not exists t1(id int,name string) 
row format delimited 
fields terminated by ','
 stored as textfile;

 
create table if not exists baseuser(id string ,activitisync int ,browser string ,password string ,realname string ,signature string ,status int ,userkey string ,username string ,departid string ,user_name_en string ,delete_flag int )partitioned by(imp_date string) row format delimited fields terminated by ',' lines   terminated by '\n' stored as textfile;

load data   inpath 'hdfs://sandbox:9090/user/hadoop/t_s_base_user/part-m-00000' overwrite into table baseuser partition(imp_date ='201911');

1.2 创建外部表 pageview

create external table if not exists pageview(page_id int,page_url string  comment ' the page url') 
row format delimited fields terminated by ',' 
 location 'hdfs://sandbox:9090/user/hive/warehouse';
 

1.3 创建分区表 invites

create table if not exists invites(id int,name string ) 
partitioned by(ds string) row format delimited fields terminated by ',' 
lines   terminated by '\n'
 stored as textfile;
load data local inpath '/usr/local/src/apache-hive-2.3.4-bin/test/mytable' overwrite into table invites partition(ds='201910');
load data local inpath '/usr/local/src/apache-hive-2.3.4-bin/test/mytable' overwrite into table invites partition(ds='201911');
insert into   invites values(1,'wym');

1.4、创建带桶的表 tong。

create table if not exists tong(id int,age int,name string ) 
partitioned by(stat_date string) clustered by (id) sorted by (age) into 2 buckets
row format delimited fields terminated by ',' ;
 
load data local inpath '/usr/local/src/apache-hive-2.3.4-bin/test/tong' overwrite into table tong partition(stat_date='201911');
set hive.enforce.bucketing = true;#强制分桶。

 

二、修改表 DDL

#添加分区
alter table tong add partition(stat_date='201901')  ;

#表重命名
alter table tong rename to tong2；


#表栏位修改
 alter table t1 add columns (age int);

#替换栏位（1.新增栏位 2.int-->String 3.栏位名称）
alter table t1 replace columns (id string,name string ,age string,sex int);




# 显示命令
show tables
show databases
show partitions
show functions
desc extended t_name;
desc formatted table_name;

三、DML

3.1 load

    load data local inpath '/usr/local/src/apache-hive-2.3.4-bin/test/mytable' overwrite into table invites partition(ds='201910');
    #overwrite 分区覆盖
    load data   inpath 'hdfs://sandbox:9090/user/hive/warehouse/mytable' overwrite into table invites partition(ds='201910');
    load data   inpath 'hdfs://sandbox:9090/user/hive/warehouse/mytable'  into table invites partition(ds='201910');

3.2 insert

      insert overwrite table invites partition(ds='201910') select sid ,sname from mytable;
      insert overwrite table invites partition(ds) select sid ,sname from mytable;
      insert overwrite  table mytable values(1,'a');
      insert overwrite  table tong partition(stat_date='201911') values(1,1,'a') ;


  #overwrite 不能省略
        from invites  insert overwrite  table invites partition(ds='201901') select id ,name where ds='201911'  
        from mytable  insert overwrite  table invites partition(ds='201903') select sid ,sname   ;

3.3 导出

#       3.3.1 本地
            insert overwrite local directory '/usr/local/src/apache-hive-2.3.4-bin/test/mytable_export'  select * from mytable;
#       3.3.2 hdfs 
            insert overwrite directory 'hdfs://sandbox:9090/user/hive/warehouse/mytable_export'  select * from mytable;
#     4.1 select
     select * from invites where ds='201911';

初始化 hive

 schematool -dbType mysql -initSchema