hive的表基本操作

hive表基本操作

一、建表：

1、内部表：

即是普通表，保存在/user/hive/warehouse/

create table t_student(id int,name string,sex string,age int,deptno string)
row format delimited
fields terminated by ','
lines  terminated by '\n';

fields terminated by 用于字段分隔符

lines terminated by 用于行分隔符

加载数据到内部表：

load data inpath '/tmp.txt' into table t_student

inpath:表示从hdfs文件系统中取数据

不加inpath表示从本地hdfs文件系统中取数据

2、外部表：

create external table t_external(id int,name string,age int)

row format delimited

fields terminated by ','

location "/hivedata";

加载数据：加载数据之后，原本目录数据删除，移动到/user/hive/warehouse/test.db/emp

load data local inpath '/home/hadoop1/student.txt' into table t_external;

指定了location：数据保存在/hivedata,在/user/hive/warehouse/db1.db中看不到数据；

若不指定location，默认保存在/user/hive/warehouse/下

内部表和外部表的区别：

删除外部表后，再次创建相同表：

不需要进行加载数据，直接执行查询，发现可以直接插上上次外部表的数据。所以上面执行的删除表只是删除表的元数据而已。

结论: 外部表的数据不由hive自身负责管理，就算不指定location，虽然数据会被加载到/user/hive/warehouse/，但是不由hive管理。删除数据库中的表并不能删除外部表，无论是保存在哪里都不会删除，保存在hdfs中，需要通过hdfs进行删除

3、分区表：

创建分区内部表

create table t_student(id int,name string,idcar int) partitioned by (country string) row format delimited fields terminated by ',' LINES TERMINATED by char; 

加载数据

load data local inpath '/home/hadoop1/student.txt' into table t_student partition (country="USA");

也可以建立分区外部表：

create external table t_student(id int,name string,age int)partitioned by(country string)
row format delimited
fields terminated by ','
location "/hivedata1";

二、导入数据

1、使用load data

2、批量insert（从其他表获取）

1、查询结果插入hive表：

INSERT OVERWRITE [INTO] TABLE
tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1
FROM from_statement

insert into t_student partition(country="USA")select * from t_user_inf

3、Multi Inserts多重插入

FROM from_statement

INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select statement1

[INSERT OVERWRITE TABLE tablename2 [PARTITION …]

Select statement2] …

from t_ext
insert into table t_student partition(country="China") select id,name,age
insert into table t_user_inf select id,name,age;

overwrite 和into的区别

两者的区别：
insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。

insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。

三、导出数据

1、导出数据到本地

INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT … FROM …

insert overwrite local directory '/root/hivedata/data' select * from t_1;

2、导出到hdfs：

insert overwrite directory '/root/hivedata/data' select * from t_student;

三、文件格式

Hive本身支持的文件格式只有：TextFile，SequenceFile。如果文件数据是纯文本，可以使用[STOREDAS TEXTFILE]。如果数据需要压缩，使用[STORED
AS SEQUENCE] 。通常情况，只要不需要保存序列化的对象，我们默认采用[STORED AS TEXTFILE]。

使用seqencefile

create table t_stu(id int,name string,age int)row format delimited
fields terminated by ','
stored as SEQUENCEFILE;

三、修改表结构：

1、增加/删除分区

增加

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION ‘location1’ ] partition_spec [ LOCATION ‘location2’ ] …

partition_spec:

PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, …)

删除分区

ALTER TABLE table_name DROP partition_spec, partition_spec,…

举例：

alter table t_student add partition(country="China")

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E3HEJIfI-1570800473703)(C:\Users\晓\AppData\Roaming\Typora\typora-user-images\1553485700370.png)]

alter table t_student drop partition(country="USA");

2、重命名表：

ALTER TABLE table_name RENAME TO new_table_name

修改表字段

alter table t_student add columns (car int);

举例：

alter table t_student rename to t_stu;

3、增加/更新列：

语法结构

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)

注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。

举例：

alter table t_student add columns (car int);

alter table t_student replace columns (car int);

四、SELCET操作：

1、order by

对所有查询结果做一次查询

如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。

set hive.mapred.mode 可以查看当前模式

set hive.mapred.mode=strict 设置成严格模式

2、sort by：局部排序

Hive中指定了sort by，那么在每个reducer端都会做排序，也就是说保证了局部有序（每个reducer出来的数据是有序的，但是不能保证所有的数据是有序的，除非只有一个reducer），好处是：执行了局部排序之后可以为接下去的全局排序提高不少的效率（其实就是做一次归并排序就可以做到全局排序了）。

set mapreduce.job.reduces=3 可以设置reducer的个数，默认是1

select * from t_student order by age;

3、distribute by和sort by一起使用

mid	money	name
AA	15.0	商店1
AA	20.0	商店2
BB	22.0	商店3
CC	44.0	商店4

ditribute by是控制map的输出在reducer是如何划分的，用distribute
by 会对指定的字段按照hashCode值对reduce的个数取模，然后将任务分配到对应的reduce中去执行，就是在mapreduce程序中的patition分区过程，默认根据指定key.hashCode()&Integer.MAX_VALUE%numReduce 确定处理该任务的reduce

select mid, money, name from store distribute by mid sort by mid asc, money asc  

我们所有的mid相同的数据会被送到同一个reducer去处理，这就是因为指定了distribute by mid，这样的话就可以统计出每个商户中各个商店盈利的排序了（这个肯定是全局有序的，因为相同的商户会放到同一个reducer去处理）。这里需要注意的是distribute by必须要写在sort by之前。

4、cluster by

cluster by的功能就是distribute by和sort by相结合，如下2个语句是等价的：

1. select *  from t_student cluster by mid
2. select * from t_student distribute by mid sort by mid     

如果需要获得与3中语句一样的效果

select * from t_student cluster by mid sort by money  

注意被cluster by指定的列只能是降序，不能指定asc和desc。

5、hive的bucket：

详情参考 https://blog.youkuaiyun.com/lzm1340458776/article/details/43272379

create table t_bucket(id int,name string,sex string,age int,deptno string)

clustered by (id) sorted by (id desc)  into 4 buckets 

row format delimited

fields terminated by ',';

采样：
TABLESAMPLE(BUCKET x OUT OF y)
y：根据y的大小，决定抽样的比例
x：表示从哪个bucket开始抽取

抽样多少个桶=总的桶数/y

select * from stu_buck tablesample(bucket 2 out of 2 on Sno);

总桶数=4;
抽样多少个桶=4/2=2;

五、多表联合：

1、笛卡尔积：在非严格模式不支持非等值连接

2、Hive支持等值连接

左外连接

​ select * from t_a left join t_b on t_a.id=t_b.id;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-luvrEWM6-1570800473707)(C:\Users\晓\AppData\Roaming\Typora\typora-user-images\1553687219210.png)]

​ 右外连接：select * from t_a right join t_b on t_a.id=t_b.id;

Hive所能胜任的工作。其中最大的限制就是Hive不支持记录级别的更新、插入或者删除操作

六、严格模式:

1、不允许直接查询分区表

如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说，
就是用户不允许扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。

2、order by需要加上limit

3、不支持非等值连接（笛卡尔积）

七、hive数据类型：

集合类型主要包括：array，map，struct等，hive的特性支持集合类型，这特性是关系型数据库所不支持的，利用好集合类型可以有效提升SQL的查询速率。

1、array

建表

create table t_array(id int,name string,hobby array)

row format delimited

fields terminated by ‘,’

collection items terminated by ‘-’;

导入数据

1,zhangsan,唱歌-跳舞-游泳
2,lisi,打游戏-篮球

load data local inpath ‘/home/hadoop1/array.txt’ into table t_array;

select id ,name,hobby[0],hobby[1] from t_array；

注意：array的访问元素和java中是一样的，这里通过索引来访问

2、map：

建表

create table t_map(id int,name string,hobby map<string,string>)
row format delimited
fields terminated by ‘,’
collection items terminated by ‘-’
map keys terminated by ‘:’;

导入数据：

load data local inpath ‘/home/hadoop1/map.txt’ into table t_map;

查询数据

select id,name,hobby[‘唱歌’] from t_map;

注意：map的访问元素中的value和java中是一样的，这里通过key来访问。

3、struct：

建表

create table t_struct(id int,name string,address structcountry:string,city:string)

row format delimited

fields terminated by ‘,’

collection items terminated by ‘-’;

导入数据

load data local inpath ‘/home/hadoop1/struct.txt’ into table t_struct;

1,zhangsan,china-beijing
2,lisi,USA-newyork

查询数据

select id,name,address.country,address.city from t_struct;

总结：struct访问元素的方式是通过.符号

其他操作：

truncate table t_a 清除数据表数据，但是不删除表

y:string,city:string>)

row format delimited

fields terminated by ‘,’

collection items terminated by ‘-’;

导入数据

load data local inpath ‘/home/hadoop1/struct.txt’ into table t_struct;

1,zhangsan,china-beijing
2,lisi,USA-newyork

查询数据

select id,name,address.country,address.city from t_struct;

总结：struct访问元素的方式是通过.符号

其他操作：

truncate table t_a 清除数据表数据，但是不删除表

drop database a cascade 强制删除数据库