hive的基本操作（重点）

缺乏、安全感 2022-07-15 06:28 383阅读 0赞

hive的基本表操作  
1.创建管理表  
create table \[if not exists\] db01.student(  
id int,  
name string,  
age int,  
...  
)  
row format delimited fields terminated by '\\t';  
  
2.加载数据  
load data \[local\] inpath 'filepath' \[overwrite\] into table tableName;  
  
3.创建外部表  
create external table \[if not exists\] db01.student(  
id int,  
name string,  
age int,  
...  
)  
row format delimited fields terminated by '\\t'  
location 'hdfspath';

注意:location后面跟的是hdfs的目录,不能够填文件名.

4.分区表  
    select \* from tableName where id=?;  
    分区表的优势就是  
    1.查询的速度更快  
    2.管理文件的结构更合理  
  
日志文件  
    20161025  
    20161026  
    1.每天加载日志文件到hive表  
    2.每天需要统计日志中的有用信息(pv uv ip)  
    3.将日志文件以日期的形式来分区  
    4.统计有效信息的范围可以通过分区来指定  
需求:将学生表按照省份province来划分分区  
一级分区表:  
create table if not exists db01.student\_par(  
id int,  
name string,  
age int  
)  
partitioned by (province string)  
row format delimited fields terminated by '\\t';  
  
在创建表的时候,只是指定了分区的字段,并未指定分区的范围,  
分区的范围在加载 数据的时候进行指定  
语法：  
LOAD DATA \[LOCAL\] INPATH 'filepath'  
\[OVERWRITE\] INTO TABLE tablename  
\[PARTITION (partcol1=val1, partcol2=val2 ...)\]  
  
load data local inpath '/opt/data/stu.txt'  
overwrite into table db01.student\_par  
partition (province='jiangsu');  
  
load data local inpath '/opt/data/stu2.txt'  
overwrite into table db01.student\_par  
partition (province='zhejiang');  
  
查看分区信息:  
    show partitions tableName  
  
查询分区信息  
    select \* from student\_par where province='jiangsu';  
  
二级分区表创建  
create table student\_par2(  
id int,  
name string,  
age int  
)  
partitioned by (province string, city string)  
row format delimited fields terminated by '\\t';  
  
二级分区表的加载  
load data local inpath '/opt/data/stu.txt'  
overwrite into table student\_par2  
partition (province='jiangsu',city='xuzhou');  
  
load data local inpath '/opt/data/stu2.txt'  
overwrite into table student\_par2  
partition (province='shandong',city='jinan');  
  
load data local inpath '/opt/data/stu3.txt'  
overwrite into table student\_par2  
partition (province='America',city='Los');  
  
load data local inpath '/opt/data/stu4.txt'  
overwrite into table student\_par2  
partition (province='America',city='NewYork');  
  
查询二级分区表  
    select \* from student\_par2

where province='America' and city='NewYork'

==========================================

创建表，直接上传数据  
create table if not exists db01.student2(  
id int,  
name string,  
age int  
)  
row format delimited fields terminated by '\\t';  
  
hdfs dfs -put stu.txt /user/hive/warehouse/db01.db/student2  
  
在创建之后，直接上传文件也可以完成加载数据  
  
创建一个分区表：  
create table student\_par3(  
id int,  
name string,  
age int  
)  
partitioned by (province string, city string)  
row format delimited fields terminated by '\\t';  
  
创建对应目录：  
hdfs dfs -mkdir -p /user/hive/warehouse/db01.db/student\_par3/province=America/city=Los;  
hdfs dfs -mkdir -p /user/hive/warehouse/db01.db/student\_par3/province=America/city=NewYork  
  
存放数据：  
hdfs dfs -put stu3.txt /user/hive/warehouse/db01.db/student\_par3/province=America/city=Los;  
hdfs dfs -put stu4.txt /user/hive/warehouse/db01.db/student\_par3/province=America/city=NewYork  
  
添加分区信息(add partition)  
alter table student\_par3 add partition (province='America',city='Los');  
alter table student\_par3 add partition (province='America',city='NewYork');  
  
  
==========================================================================  
外部分区表  
create external table student\_ext\_par(  
id int,  
name string,  
age int  
)  
partitioned by (province string, city string)  
row format delimited fields terminated by '\\t';  
  
将数据上传到 hdfs  
hdfs dfs -mkdir -p /nicole/input/student\_ext\_par/province=America/city=Los;  
hdfs dfs -mkdir -p /nicole/input/student\_ext\_par/province=America/city=NewYork  
  
hdfs dfs -put stu3.txt /nicole/input/student\_ext\_par/province=America/city=Los;  
hdfs dfs -put stu4.txt /nicole/input/student\_ext\_par/province=America/city=NewYork  
  
关联数据  
alter table student\_ext\_par add partition (province='America',city='Los') location '/nicole/input/student\_ext\_par/province=America/city=Los';  
alter table student\_ext\_par add partition (province='America',city='NewYork') location '/nicole/input/student\_ext\_par/province=America/city=NewYork';  
  
  
alter table student\_ext\_par add partition (province='America',city='Miami') location '/nicole/input/student\_ext\_par/test1';  
alter table student\_ext\_par add partition (province='America',city='Las') location '/nicole/input/student\_ext\_par/test2';  
虽然外部表的分区信息与实际关联的hdfs的路径信息并没有一个必须的对应关系，  
但是仍然建议对应分区来创建hdfs的路径信息，好处便于管理。  
  
删除分区的命令    drop partition  
alter table student\_ext\_par drop partition (province='America',city='Las')  
  
  
======================================================  
创建 加载 hive表  
第一种方式：  
create table s1 (  
id int,  
name string  
)  
row format delimited fields terminated by '\\t';  
  
加载数据  
load data \[local\] inpath 'path' \[overwrite\] into table s1;  
  
第二种方式:  
create table student\_like like student;  
  
加载数据  
load data \[local\] inpath 'path' \[overwrite\] into table student\_like;  
  
第三种方式：  
create table student\_as as select \* from student;  
直接创建并加载数据  
这种方式经常用于创建一个临时表  
  
举例，创建emp临时表  
create table emp\_as as select empno as no,empname as name, empjob as job from emp;  
  
第四种方式：  
insert语句插入表数据之前必须创建表  
create table emp\_insert(  
no int,  
name string,  
job string  
)  
row format delimited fields terminated by '\\t';  
  
insert into table tableName            追加  
insert overwrite table tableName    覆盖  
  
insert into table emp\_insert select empsalary,empname,empjob from emp;  
  
========================================================

**往hive表中导入数据的几种方式**

第一种方式：从本地到hive  
    load data local inpath 'path/file' \[overwrite\] into table 表名称 ;  
  
第二种方式：从hdfs到hive  
    load data inpath 'path/file' into table 表名称 ;  
  
第三种方式：创建表的时候使用as直接加载数据  
    create table db\_01.emp\_as as select \* from emp ;  
  
第四种方式：使用insert命令加载  
    insert into table 表名 select \* from emp  
    insert overwrite table 表名 select \* from emp       
  
第五种方式：创建表的时候通过location指定  
    create table 表名(...)  
    partioned by  
    row format ..  
    location "" ;  
  
**从hive导出表的几种方式：**  
第一种：往本地导出  
    insert overwrite local directory 'localpath' 查询语句;  
举例：  
insert overwrite local directory "/opt/data/hive" select \* from emp;  
insert overwrite local directory '/opt/data/hive/aaa' row format delimited fields terminated by '\\t' select \* from emp;  
注意：  
1.在用这种方式导出数据的时候，必须切换目录才能看到文件  
2.文件000000\_0会覆盖目录中的所有其他文件  
3.如果不适用row format语句指定分隔符,会使用默认分隔符分隔字段  
4.指定目录使用单引号、双引号都可以  
  
第二种：往hdfs导出  
insert overwrite directory "/nicole/input/emp/temp" select \* from emp;  
  
存在的问题：  
    在0.13.1版本中不支持直接导出到hdfs可以指定分隔符  
  
第三种：  
$ bin/hive -e "select \* from db01.emp" > /opt/data/hive/emp.txt  
  
第四种：  
    使用sqoop来导出  
      
      
练习：  
drop table if exists emp;  
create table emp(  
empno int,  
empname string,  
empjob string,  
managerno int,  
empdate string,  
empsalary double,  
empreward double,  
deptno int  
)  
row format delimited fields terminated by '\\t';  
  
load data local inpath '/opt/data/emp.txt' overwrite into table emp;  
  
emp表、dept表  
1.求出每个部门的最高薪资  
    select max(empsalary),deptno from emp group by deptno;  
2.求出每个部门的最高薪资,部门名称  
select  
max(e.empsalary) salary,e.deptno,d.deptname  
from emp as e  
join  
dept as d  
on  
e.deptno=d.deptno  
group by  
e.deptno,d.deptname;  
  
3.显示部门名称,部门最高薪资,部门所在的城市  
select  
max(e.empsalary) salary,e.deptno,d.deptname,d.deptcity  
from emp as e  
join  
dept as d  
on  
e.deptno=d.deptno  
group by  
e.deptno,d.deptname,d.deptcity;  
  
  
4.显示部门名称,部门最高薪资,且薪资必须大于等于3000的  
select  
max(e.empsalary) salary,e.deptno,d.deptname,d.deptcity  
from emp as e  
join  
dept as d  
on  
e.deptno=d.deptno  
group by  
e.deptno,d.deptname,d.deptcity  
having salary >= 3000;  
  
5.从绩效工资小于10000的员工中，按部门查看最高基本工资，  
显示部门名称以及基本工资大于等于3000的  
select  
max(e.empsalary) salary,e.deptno,d.deptname,d.deptcity  
from emp as e  
join  
dept as d  
on e.deptno=d.deptno  
where e.empreward < 10000  
group by  
e.deptno,d.deptname,d.deptcity  
having salary >= 3000;  
  
  
join  
    select \* from emp join dept;        --笛卡尔积m\*n  
      
inner join    内连接  
  
left join    左连接  
    select \* from emp left join dept on emp.deptno=dept.deptno;  
  
right join    右连接  
    select \* from emp right join dept on emp.deptno=dept.deptno;  
  
==========================================================================  
explain  
    解析执行计划  
  
语法：explain 查询语句  
    explain select \* from emp right join dept on emp.deptno=dept.deptno;  
  
  
  
常用的函数：  
hive (db01)> show functions;              查看所有函数  
hive (db01)> desc function max;          查看函数描述  
hive (db01)> desc function extended sum;查看函数详细描述  
  
concat 连接字符串函数  
hive (db01)> select concat(empname,empjob) from emp;  
hive (db01)> select concat(empname,"\_",empjob) from emp;  
  
  
substr 截取字符串函数  
hive (db01)> select substr(empdate,1,4) from emp;  
    1:代表从第一位开始取  
    4:代表取长度为4的string  
  
  
时间相关函数  
day  
mouth  
year  
hour  
    hive (db01)> select hour("2010-10-10 10:11:12");  
minute  
second  
  
hive (db01)> select year(empdate),month(empdate),day(empdate),hour(empdate) from emp;  
\_c0     \_c1     \_c2     \_c3  
1980    12      17      NULL  
1981    2       20      NULL  
有就能取出来，没有就返回NULL  
  
Synonyms: dayofmonth  
date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'.  
  
  
unix\_timestamp函数  
    将时间转换为自从1970年1月1日至今的秒数  
hive (db01)> select unix\_timestamp("2016-10-26 15:23:30");  
    1477466610  
  
from\_unixtime函数  
    将unix时间转换为日期时间(格式2016-10-26 15:23:30)  
hive (db01)> select from\_unixtime(1477466610)  
    2016-10-26 15:23:30  
  
  
cast函数  
    从日志上获取到了ms值：1477466610456ms  
    cast(1477466610456/1000 as int)  
      
  
case when  
语法：  
case  
when 条件 then 返回值  
when 条件 then 返回值  
...  
else 返回值  
end  
  
举例：  
select empname,  
case  
when empreward>=10000 then "rich"  
when empreward<10000 and empreward >=5000 then "just so so"  
else "pool"  
end  
from emp;  
  
  
hiveserver2    基于thrift软件架构的服务器  
  
修改hive-site.xml配置文件      
  
<property>  
  <name>hive.server2.thrift.port</name>  
  <value>10000</value>  
</property>  
  
  
<property>  
  <name>hive.server2.thrift.bind.host</name>  
  <value>nicole02.com.cn</value>  
</property>  
  
  
<property>  
  <name>hive.server2.long.polling.timeout</name>  
  <value>5000</value>  
</property>  
  
      
启动hiveserver2  
$ bin/hive --service hiveserver2    \#启动服务器  
  
$ bin/beeline        \#启动客户端  
beeline> help        \#查看命令  
    > !connect jdbc:hive2://192.168.234.150:10000  
                    \# 连接数据库  
Enter username for jdbc:hive2://192.168.234.150:10000: nicole  
Enter password for jdbc:hive2://192.168.234.150:10000: \*\*\*\*\*  
    \#注:输入的用户名和密码是linux使用的用户名密码  
    \#如果不输入也可以进入，但是没有权限  
      
    \#进入之后,显示如下:  
0: jdbc:hive2://192.168.234.150:10000>  
    \#输入的命令与hive无异  
      
2.2.2Drop Partitions  
ALTER TABLE table\_name DROP partition\_spec, partition\_spec,...  
  
ALTER TABLE c02\_clickstat\_fatdt1 DROP PARTITION (dt='20101202');  
2.2.3Rename Table  
ALTER TABLE table\_name RENAME TO new\_table\_name  
这个命令可以让用户为表更名。数据所在的位置和分区名并不改变。换而言之，老的表名并未“释放”，对老表的更改会改变新表的数据。  
2.2.4Change Column  
ALTER TABLE table\_name CHANGE \[COLUMN\] col\_old\_name col\_new\_name column\_type \[COMMENT col\_comment\] \[FIRST|AFTER column\_name\]  
这个命令可以允许改变列名、数据类型、注释、列位置或者它们的任意组合  
Eg:  
  
2.2.5Add/Replace Columns  
ALTER TABLE table\_name ADD|REPLACE COLUMNS (col\_name data\_type \[COMMENT col\_comment\], ...)  
  
ADD是代表新增一字段，字段位置在所有列后面(partition列前);REPLACE则是表示替换表中所有字段。  
Eg:  
hive> desc xi;  
OK  
id      int  
cont    string  
dw\_ins\_date     string  
Time taken: 0.061 seconds  
hive> create table xibak like xi;                           
OK  
Time taken: 0.157 seconds  
hive> alter table xibak replace columns (ins\_date string);     
OK  
Time taken: 0.109 seconds  
hive> desc xibak;  
OK  
ins\_date        string  
2.3Create View  
CREATE VIEW \[IF NOT EXISTS\] view\_name \[ (column\_name \[COMMENT column\_comment\], ...) \]  
\[COMMENT view\_comment\]  
\[TBLPROPERTIES (property\_name = property\_value, ...)\]\[？？？？？？\]  
AS SELECT ...