HIVE的模板

HIVE的使用模板

1、显示所有数据库

show databases;

2、显示数据库所有的表

use mydatabase;
show tables;

3、查询表中的内容

select * from mytable;

4、显示表的结构

desc mytable;

5、创建数据库

create database mydatabase;

6、清空表内容

truncate table mytable;

7、创建表

1、创建一张普通的表

create table mytable(id int,name string)row format delimited fields terminated by '\t';

create table if not exists mytable(id int.name string)row format delimited fields terminated by '\t';

2、创建一张分区表

create table mytable(id int,name string)partitioned by(date string,hour string) \
row format delimited fields terminated by '\t';

3、创建一张分桶表

set hive.enforce.bucketing=true;
set mapreduce.job.reduces=-1;
create table table_buck(id int,name string)clustered by(id) into 4 buckets row format delimited fields terminated by '\t';
create table table_test(id int,name string)row format delimited fields terminated by '\t';
load data local inpath '/data/datas/test01.txt' into table table_test;
insert into table table_buck select id,name from table_test;

4、创建一张内部表

create table mytable(id int,name string)row format delimited fields terminated by '\t' LOCATION '一个表的HDFS的路径';

5、创建一张外部表

create EXTERNAL table mytable(id int,name string)row format delimited fields terminated by '\t' LOCATION '/user/hive/warehouse/mysql';

6、创建一张表使用正则表达式分开

生成正则表达式的网址

create table IF NOT EXISTS qianfeng_source (
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_sent string,
request_body string,
http_referer string,
http_user_agent string,
http_x_forwarded_for string,
host string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES ("input.regex" = "(\"[^ ]*\") (\"[-|^ ]*\") (\"[^}]*\") (\"[^}]*\") (\"[0-9]*\") (\"[0-9]*\") ([^ ]*) (\"[^ ]*\") (\"[^}]*\") (\"[-|^ ]*\") (\"[^ ]*\")")
STORED AS TEXTFILE;

PS：

正则表达式中每个 （） 之间是用你的分隔符来区分的，比如我这次的分隔符是空格，那么我两个 （） 之间就有一个空格。
括号里面 [] 包含的是我 （） 里面的内容， ^ 表示非 ！，0-9 表示数字。

8、数据导入到表中及从HIVE导入到HDFS中

本地文件导入到表中

load data local inpath '/data/datas/test.txt' into table mytable;

1、将数据从一张表导入到另一张表中

insert into table mytable select id,name from mytable01;

2、用as将数据从一张表导入到另一张表中

create table mytable as select * from mytable01;

3、用like将数据从一张表导入到另一张表中

create table mytable like mytable01;

4、导入到分区表中

insert overwrite table mytable partition(date='20180915')select * from mytable2;

5、导入到分桶表中

insert into table table_buck select id,name from table_test;

6、将数据导入到HDFS中

insert overwrite directory '/test/' select * from movie_z_r_l_m;

9、排序查询数据

1、order by 按照指定字段进行排序

升序

select * from mytable order by id;

降序

select * from mytable order by id desc;

2、sort by 按照指定字段进行排序 （查询速度超级慢）作用是存放在文件中的

升序

select * from mytable sort by id;

还是升序，加了desc也还是升序

select * from mytable sort by id desc

将数据按照某个字段升序排序导入到文件中

insert overwrite local directory '/data/datas/test07.txt' select * from table_test sort by id ;

将数据按照某个字段降序排序导入到文件中

insert overwrite local directory '/data/datas/test08.txt' select * from table_test sort by id desc;

**PS:**在这里发现，我的mapreduce.jobreduces设置的是几我导入数据后表中如果再次往文件中导入的话我就会分成几个文件（按照sort by 的字段划分的，前面有的文件为空），并且会将我的文件自动转化为文件夹存储。
如果我把reduce数目设置成一个也会变成文件夹存放进去。
如果我写一个不存在的文件也会执行代码，然后创建那个文件夹，在那个文件夹里面存放表中的数据。

3、分区排序

设置严格模式的意义在于防止计算机一次性大量分区消耗计算机的性能。

insert overwrite local directory '/data/datas/test09.txt' select * from table_test distribute by id;

4、动态分区：

set hive.exec.dynamic.partition.mode；
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table meituan_part2 partition(date,hour) select * from meituan_qingxi;

5、cluster by : sort+distribute的组合(两个字段必须相同)

insert overwrite local directory '/data/datas/test10.txt' select * from table_test cluster by id;

10、join连接

内连接

select e.empno,e.name,d.deptno from emp e join dept d on e.deptno=d.deptno;

左外连接

select  e.empno,e.name,d.deptno from emp e left join dept d on e.deptno=d.deptno;

右外连接

select  e.empno,e.name,d.deptno from emp e right dept d on e.deptno=d.deptno;

满外连接

select e.empno,e.name,d.deptno from emp e full join dept d on e.deptno=d.deptno;

11、聚合函数

count 、sum、avg、max、min、group by、having(常跟group by一起写，在组里再添加条件)

求平均工资，最高工资，对员工进行分组，按照部门进行分组，分组过后的部门中，平均薪资大于2000的

select avg(sal) as avg_sal,max(sal) as max_sal from emp group by deptno having avg_sal>2000;

查询出全部学生的数量

select count(distinct id) num from student;

12、自定义函数

1、写一个自定义类继承UDF类转化为自定义函数

public class MyFunction extends UDF{
    
    public Text evaluate(Text str){
        
        return new Text();
    }
}

PS：遇到的问题
在这里写函数的时候方法名一定要用evaluate这个是自定义函数方法的方法名，不然系统认不出来，能导入包，但是使用函数的时候就会报错

2、添加jar包

add jar /data/datas/myjar.jar;

3、创建function

create temporary function 自定义函数名称 as 'java类的路径'  (例如：'org.hive.udf.MyUdf)  ; 

4、查看函数

查看系统自带的函数

show functions;

显示自带函数的用法

desc function my_function;

详细显示自带函数的用法

desc function extended my_function;

5、使用函数

select my_function() from mytable;

检查函数
在类中写一个main方法，调用函数，并输出。

6、删除函数

退出来就可以，添加进去的自定义函数只是临时的。

7、Linux自带的时间戳函数

y:年 m:月 d:日

h:小时 m:分钟 s:

unix_timestamp（）

格式严格要求：yyyy-mm-dd hh:mm:ss

如果是：20180915 12：32：34 的格式

用unix_timestamp（time,“yyyymmdd HH:mm:ss”）来完成。“里面为自定义格式”