hive——基本命令

1，建表

一般表

hive> create table student(name string,id int)
    > row format delimited
    > fields terminated by '\t'
    >lines terminated by '\n'
    >stored as textfile
    > location '/home/student';

插入数据

hive> insert into table student values('zhong');

清空表

hive> truncate table student;

删除表

hive> drop table student;

复合表 （array)

hive> create table students(work array<string>)
    > row format delimited
    > fields terminated by '\t';

复合表（map）

hive> create table maps(vals map<string,int>)
    > row format delimited
    > fields terminated by '\t'
    > map keys terminated by ',';

复合表（struct)

hive> create table structs(student struct<name:string,age:int>)
    > row format delimited
    > fields terminated by '\t'
    > collection items terminated by ',';

外部表

关键字：external，指定保存路径，

hive> create external table exs(name string)
    > row format delimited
    > fields terminated by '\t'
    > location '/home/exs';

分区表

hive> create table par(name string)
    > partitioned by (city string,date_time string)
    > row format delimited
    > fields terminated by '\t';

查看分区结构

hive> desc par;

添加分区

hive> alter table par add partition(city='lz',date_time='2020-5-27');

查看分区

hive> show partitions par;

修改分区

hive> alter table par partition(city='lz',date_time='2020-5-27')rename to partition(city='dip',date_time='2018-9-1');

删除分区

hive> alter table par drop partition(city='lz',date_time='2020-5-27');

分桶表

2，修改表结构

重命名表

hive> alter table student rename to new_student;

增加字段

hive> alter table students add columns(newcol int);

修改字段

hive> alter table students change newcol news string;

删除字段

hive> alter table students replace columns(work array<string>);
#只写需要保留的字段，其他的字段会删除

3，添加数据和导出数据

3.1本地上传

hive> load data local inpath '/home/data/word.txt' into table word;

3.2hdfs上传

hive> load data inpath '/lmq/word.txt' into table word;

3.3导入其他表数据

hive> insert into table wo > select * from word;

3.41导出数据到hdfs

[root@hadoop data]# hadoop fs -copyToLocation /user/hive/warehouse/lzy/word /lmq/words
hive> insert overwrite directory '/lmq/words'
    > select * from word;

3.42导出的本地

hive> insert overwrite local directory '/home/data/words.txt'
    > row format delimited
    > fields terminated by '\t'
    > select * from word;

4，查看数据

mysql> select * from TBLS;表

mysql> select * from SDS;#元数据
使自己写的分区有用
ALTER TABLE book add  PARTITION (category = 'jp') location 
'/user/hive/warehouse/park.db/book/category=jp';

大小
select size()

去重
hive> select distinct(vals['tom']) from m2 where vals['tom'] is not null; #字符串
hive> select collect_set(num) from ex2; #数据去重，返回数组
分组去重
select name,collect_set(ip) from ex2 group by name;

查询基础

多表查询

from a
join b on a.id=b.id
join c on a.name=c.name

基础

where name='李'
group by id
having total >=50 #字段值大于50
order by id desc
limit 10; #前十个