hive笔记

一. hive的安装

0 正常启动HDFS和YARN
1.安装mysql
2 查看mysql是否运行

netstat -nltp | grep 3306
tcp 0 0 0.0.0.0:3306

3 systemctl start mysqld 启动mysql服务
4 可以配置mysql的开机自启服务 systemctl enable mysqld
就用以前安装的，但是要注意，开启远程连接权限

[root@doit01 ~]# service mysqld start Starting mysqld: [ OK ]
set password for ‘root’@‘m01’= password(‘root’);

mysql -uroot -p123456
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1; 这个两个设置以后 密码很简单不会报错
开启远程连接权限
mysql > grant all privileges on . to ‘root’@’%’ identified by ‘root’ with grant option; mysql
flush privileges;

授权完成后，测试一下是否成功：在windows上用Navicat连接一下看是否能成功！

上传hive的安装包,解压
tar -zxf apache-hive-2.3.5.tar.gz

修改配置文件
cp hive-env.sh.template hive-env.sh
vi hive-env.sh

export HADOOP_HOME=/opt/apps/hadoop-2.8.5/
# Hive Configuration Directory can be controlled by:
export HIVE_CONF_DIR=/opt/apps/hive-2.3.1/conf/

vi hive-site.xml

> *******-----------------****************** <configuration> <!-- 记录HIve中的元数据信息  记录在mysql中 --> <property>
> <name>javax.jdo.option.ConnectionURL</name>
> <value>jdbc:mysql://linux01:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false</value>
> </property> <property>
> <name>javax.jdo.option.ConnectionDriverName</name>
> <value>com.mysql.jdbc.Driver</value> </property> <!-- mysql的用户名和密码 -->
> <property> <name>javax.jdo.option.ConnectionUserName</name>
> <value>root</value> </property> <property>
> <name>javax.jdo.option.ConnectionPassword</name> <value>root</value>
> </property>
> 
> <property> <name>hive.metastore.warehouse.dir</name>
> <value>/user/hive/warehouse</value> </property>
> 
> <property> <name>hive.exec.scratchdir</name>
> <value>/user/hive/tmp</value> </property> <property>
> <name>hive.querylog.location</name> <value>/user/hive/log</value>
> </property> <!-- 客户端远程连接的端口 --> <property> 
> <name>hive.server2.thrift.port</name>  <value>10000</value>
> </property> <property>  <name>hive.server2.thrift.bind.host</name> 
> <value>0.0.0.0</value> </property> <property>
> <name>hive.server2.webui.host</name> <value>0.0.0.0</value>
> </property> <!-- hive服务的页面的端口 --> <property>
> <name>hive.server2.webui.port</name> <value>10002</value> </property>
> <property>  <name>hive.server2.long.polling.timeout</name> 
> <value>5000</value>                                </property>
> <property> <name>hive.server2.enable.doAs</name> <value>true</value>
> </property> <property> <name>datanucleus.autoCreateSchema</name>
> <value>false</value> </property> <property>
> <name>datanucleus.fixedDatastore</name> <value>true</value>
> </property>
> 
> <property> <name>hive.execution.engine</name> <value>mr</value>
> </property>
> 
> <property> <name>hive.zookeeper.quorum</name>
> <value>lx01,lx02,lx03</value> </property> </configuration>
> 
> *******-----------------******************

vi /opt/apps/hadoop-2.8.5/etc/hadoop/core-site.xml

> <property> <name>dfs.permissions.enabled</name> <value>false</value>
> </property>
> 
> <property> <name>hadoop.proxyuser.root.hosts</name> <value>*</value>
> </property>
> 
> <property> <name>hadoop.proxyuser.root.groups</name> <value>*</value>
> </property>

拷贝一个mysql的jdbc驱动jar包到hive的lib目录中

重启hdp
stop-all.sh
start-all.sh
初始化hive的元数据库
${HIVE_HOME}/bin/schematool -initSchema -dbType mysql

1) 在mysql中多一个hive数据库 记录元数据信息
2) 启动以后在HDFS中的目录中 /user/hive目录

配置hive的环境变量

启动hive
bin/hive
show databsaes ;
create database doit13 ;
create table a (id int , name string) ;
show tables ;

第二种连接方式 (hiveserver2服务 : beeline JDBC连接)

1. hiveserver2 —> netstat -nltp | grep 10000 10002
   http://doit01:10002/
2. clone session
   beeline
   > !connect jdbc:hive2://doit01:10000
   root 当前用户名
   回车
   0: jdbc:hive2://doit01:10000>

一.什么是hive?

是一种基于hadoop的数据仓库工具,可以让用户使用SQL语言查询HDFS中的结构化数据.

二.hive的工作原理?

即:

将数据存储在HDFS中;
通过mapreduce处理和运算数据;
向用户提供SQL语言的接口.

三.建表

DDL–表语句
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)] 分区表
[CLUSTERED BY (col_name, col_name, …) 分桶表
[SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format] row format delimited fields terminated by “分隔符”
[STORED AS file_format] – 数据存储格式 默认文本文件 列式数据格式 ORC parquet
[LOCATION hdfs_path]

四.将数据导入到hive表中

本质:就是把数据上传到表目录中.hdfs dfs -put …
1.将本地磁盘数据导入hive表:

load data [local] inpath ‘本地文件路径’ [overwrite] into table 表名 [partition(partcol1=‘val1’, partcol2=‘val2’ …)];

2.将HDFS中的数据导入hive表:(本质是文件的移动)

load data inpath ‘HDFS文件路径’ into table 表名;
***load data local inpath “文件路径” overwrite into table 表名 ; – 覆盖导入

3 location 文件夹 [文件夹中有数据文件]
4 将 文件上传到表所在的目录
5 insert into tb_a values() XX
insert into tb_a select id , name , age from tb_b ;
6 create table tb_x as select id , name , age from tb_b ;

注:hive不会对数据源的格式进行检查和约束.导入数据时要保证数据格式和表结构要对应.数据每行多出的字段会被舍弃,缺少或者数据类型不匹配的字段会变为NULL.

五.hive数据的导出

1 insert 方式

insert overwrite local  directory "/doit18/demo1"
select * from tb_demo1 ;  -- 导出数据到本地的文件夹下

insert overwrite  directory "/doit18/demo1"
select * from tb_demo1 ; -- 导出数据到HDFS的文件夹下

insert overwrite local  directory "/doit18/demo1"
row format delimited fields terminated by "," 
select * from tb_demo1 ;  -- 导出数据指定数据的字段分隔符
1,liubang,33
2,xiangyu,34
3,hanxin,32
4,范增,67
5,吕雉,27
[root@linux01 demo1]# pwd
/doit18/demo1

2 shell方式

hive -e "sql语句"  >>  本地文件中
hive -f 文件  >  >> 文本文件
设置定任务 调度脚本(SQL)   hive -e/-f

3 命令导出 hdfs dfs -get 表中的文件

hive> dfs -get   -ls 

4 export
5 sqoop 数据迁移工具

6.Spark读取hive表:

1.导入spark-hive依赖 版本与spark-sql一致
2.添加hive-site.xml,hdfs-site.xml,core-site.xml作为资源文件
3. session .enablehivespport hive-site.xml配置:

    <name>hive.metastore.uris</name>
    <value>thrift://linux02:9083</value>
     </property> 

spark会通过hiveserver2服务读取元数据
否则直接通过mysql读元数据，spark的hive依赖版本比较低，会更改mysql元数据中的version属性。
PS:配了这个参数,hive就需要开启元数据服务: bin/hive --service metastore -p 9083 &
spark读取hive表的API:
spark.read.table("表名").limit(10).show(10,false)

六.hive启动方式

hiveserver2 & (后台启动

七.分区表

动态分区
静态分区 是将静态的数据直接导入到指定的分区中 load
动态分区 是根据某个字段的值判断 然后将数据插入到指定的分区中

使用动态分区的基本步骤 :
1 创建一个普通表 加载所有数据
2 开启动态分区功能
set hive.exec.dynamic.partition=true ;
set hive.exec.dynamic.partition.mode=nonstrick; 可以从普通表中导入数
3 创建分区表
4 通过insert select的方式导入数据
insert into tb_person_p partition(addr)
select uid , name , age , gender , address , address from tb_person ;
最后一个字段对应的是分区表的分区字段

Q&A

1.HIVE建表后只能在mysql中看到hive数据库,在HDFS中找不到user目录:
大概率是因为安装hive的这台机器上的hadoop默认文件系统没有设置成HDFS.所以它自动生成到本机根目录下了.修改hadoop配置文件core-site.xml,将defaultFS设置为HDFS://linux01:8020,然后重启hadoop.

2.mysql开启远程连接权限:
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
这个两个设置以后 密码很简单不会报错.注意高版本是下划线,低版本时是’’.policy’’

mysql > grant all privileges on . to ‘root’@’%’ identified by ‘root’ with grant option;
mysql > flush privileges;

mysql > grant all privileges on . (表名,点表示任意表)to ‘root’(用户名)@’%’ (ip地址,%表示任意地址)identified by ‘root’ (自定义远程连接密码,可以和数据库密码不一样)with grant option;

开启远程连接权限

3.hive开启本地模式
对于大多数这种情况，hive可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间会明显被缩短。

如此一来，对数据量比较小的操作，就可以在本地执行，这样要比提交任务到集群执行效率要快很多。

配置如下参数，可以开启Hive的本地模式：

hive> set hive.exec.mode.local.auto=true;(默认为false)

当一个job满足如下条件才能真正使用本地模式：

1.job的输入数据大小必须小于参数：hive.exec.mode.local.auto.inputbytes.max(默认128MB)
2.job的map数必须小于参数：hive.exec.mode.local.auto.tasks.max(默认4)
3.job的reduce数必须为0或者1

但是你会发现job确实是以本地模式运行了（看job名字就能看出来,中间有local字样）,但是还是会报错，各种找不到jar包。

这里还要运行一个语句：

set fs.defaultFS=file:///

然后你再去执行前面的那条语句，可以正常运行了，执行实现只需要几秒钟而已，我们能够很快的看到执行结果了。

HQL笔记

一.hive日期格式常用处理方式

1、Hive中获取当前的时间：

1.	select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')
2.	current_date 当前日期

2、Hive中计算时间差：

select datediff('2018-06-04','2018-06-05');

增加数天:

 date_add(string start_date, int days);
select date_add("2017-06-16 15:00:01",3);   --输出： 2017-06-19

减去数天:

 date_sub(string start_date, int days);
select date_sub("2017-06-16 15:00:01",3);   --输出： 2017-06-13

3.时间格式化:

date_format(time_variable, ‘yyyyMM’ );
常用格式示例：
yyyyMM: 202004
yyyyMMdd:20200401

time_variable 格式可以是:

2016-10-01 00:00:11
2016-06
2016-06-22

4.hive时间数据类型更多详解:https://www.jianshu.com/p/e18a7fd2de5c

二.hive取两表差集

hive常用的求差集方法，左（右）连接 left outer join
示例:

hive> select * from A;
OK
1	2
1	3
2	1
2	3
3	1

hive> select * from B;
OK
1	2
1	4
2	2
2	3

要取出A与B的差集（A-B）

1	3
2	1
3	1

A表左连接B表:

hive> select * from A a left outer join B b on a.uid=b.uid and a.goods=b.goods;
1	2	1	2
1	3	NULL	NULL
2	1	NULL	NULL
2	3	2	3
3	1	NULL	NULL

最后只要取出B的uid和goods为null的行就可以了:

select a.* from A a left outer join B b 
on a.uid=b.uid and a.goods=b.goods
 where b.uid is null and b.goods is null;

注意是is null 而不是 = null.
Hive可以用not in，但只能用于单个字段。

hive> select * from A  where uid not in (select uid from B);
3	1

多字段not in , oracle支持，但hive不支持。

select * from A where (uid,goods) not in (select uid,goods from B);

Hive 可以用not exists.

hive> select * from A  
where not exists 
(select * from B where A.uid=B.uid and A.goods=B.goods);
1	3
2	1
3	1

这两种方式比较耗费资源，应该优先使用连接表的方式.

Q&A

Q1:
有hql代码如下:

with x as (select distinct uname from loginlog_p  where datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),login_date) = 1),
y as (select distinct uname from loginlog_p  where datediff( from_unixtime(unix_timestamp(),'yyyy-MM-dd'),login_date) > 1)
select x.uname from x left outer join y 
on x.uname = y.uname 
where y.uname is null;

使用这种方式取得当前日期和表内日期的差值时,如果加上distinct关键字 天数就会少一天.以上代码中虽然写的日期差值为1,但实际上得出的结果是前天的信息.去掉distinct,差值为1得到的就是昨天的信息.
A1:
问题出在:from_unixtime(unix_timestamp(),'yyyy-MM-dd').把时间戳转换为日期后运算出错.少了一天.具体原因暂时不详.改为current_date取当前日期,结果正确了.

bin() 将整数转换为二进制字符串
pow(),平方函数 返回double型
reverce(),将字符串倒置
count() 计数,一行数据判断一次,只要括号里的表达式非空就计数,不会计入null.

with cube 多维数据立方体
bith rollup 多维数据上卷
groupping sets((),()…) 自定义维度数据立方体

求连续活跃用户或者留存:bitmap/拉链表
复合数据sql: collect_set() 多行转为一列,返回array类型.
named_struct() 构建struct类型数据