Hive
Day01
Hive是由Facebook开源，构建于hadoop之上的数据仓库工具，将结构化的数据映射成一张表格，提供了类SQL查询语句，底层可以转化为Mapreduce去执行。
一、Hive体系
Hive是构建于hadoop之上的数据仓库工具，将结构化的数据映射成一张表格，提供了类SQL的查询语句，底层可以转化成MapReduce去执行。
1、用户接口
CLI command Line 元数据
java JDBC/ODBC 元数据
Web UI 浏览器 元数据
2、Metastore
Hive将元数据存储在数据库中，比如mysql ,derby.Hive中的元数据包括表的名称，表的列和分区及其属性，表的数据所在的目录
3.驱动器：Driver
包含：解析器、编译器、优化器、执行器；
4.Hive数据存储在HDFS，大部分的查询、计算由mapreduce完成
5.Hive数据仓库于数据库的异同
(1）.由于Hive采用了SQL的查询语言HQL，因此很容易将Hive理解为数据库。其实从结构上来看，Hive和数据库除了拥有类似的查询语言，再无类似之处。
（2）数据存储位置。 hdfs raw local fs
（3）数据格式。 分隔符
（4）数据更新。hive读多写少。Hive中不支持对数据的改写和添加，所有的数据都是在加载的时候中确定好的。
INSERT INTO … VALUES添加数据，使用UPDATE … SET修改数据 不支持的
HDFS 一次写入多次读取
（5） 执行。hive通过MapReduce来实现的 而数据库通常有自己的执行引擎。
（6）执行延迟。由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致Hive执行延迟高的因素是MapReduce框架
（7）可扩展性
（8）数据规模。
二、Hive的安装模式
1、嵌入模式
2、本地模式
3、远程模式
三、安装Hive
（一）Linux安装MySQL是在root用户下安装的mysql
1）首先卸载自带的MySQL数据库
# rpm -qa | grep mysql
# rpm -e mysql-libs-5.1.71-1.el6.x86_64 --nodeps
2)通过yum安装mysql
# yum install -y mysql-server mysql mysql-devel
3)启动mysql服务（mysqld）
# service mysqld start
4)设置为开机启动
# chkconfig --list | grep mysqld //查看是否为开机
# chkconfig mysqld on //设置开机启动
5)登陆mysql数据库
# mysql -u root -p
6）设置或密码
方式一：
# /usr/bin/mysqladmin -u root password 'root'
方式二：
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');
7)授权root的权限
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';
mysql> grant all privileges on *.* to 'root'@'chao.com' identified by 'root';
all privileges:添加所有权限
第一个*:所有数据库
第二个*:所有表
第一个'root':代表root用户
%:代表其他的外部主机
'root123' ：代表root用户密码
## 刷新权限
flush privileges;
（二）安装Hive
1解压hive的tar包
$ tar -zxf hive-0.13.1-bin.tar.gz -C ../modules/
2.创建/tmp和hive数据仓库在HDFS之上的目录
需要开启namenode datanode
$ bin/hdfs dfs -mkdir -p /user/hive/warehouse
$ bin/hdfs dfs -mkdir /tmp //默认已经创建了
修改目录的权限（增加组用户的写权限）
bin/hdfs dfs -chmod g+w /user/hive/warehouse
bin/hdfs dfs -chmod g+w /tmp
3.${HIVE_HOME}/conf/ 重命名生成配置文件
$ cp hive-env.sh.template hive-env.sh
$ cp hive-default.xml.template hive-site.xml
$ cp hive-log4j.properties.template hive-log4j.properties
4.${HIVE_HOME}/conf/ 修改hive-env.sh
JAVA_HOME=/opt/modules/jdk1.7.0_67
HADOOP_HOME=/opt/modules/hadoop-2.5.0
export HIVE_CONF_DIR=/opt/modules/apache-hive/conf
5)${HIVE_HOME}/conf/ 修改hive-site.xml
--JDBC连接的四要素：ConnectionURL DriverName UserName Password
|
<--!132--> <property> <name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://[hostname]:3306/metastore?createDatabaseIfNotExist=true</value> <description>JDBC connect string for a JDBC metastore</description> </property> <--!138--> <property> <name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.jdbc.Driver</value> <description>Driver class name for a JDBC metastore</description> </property> <--!162--> <property> <name>javax.jdo.option.ConnectionUserName</name> <value>root</value> <description>username to use against metastore database</description> </property> <--!168--> <property> <name>javax.jdo.option.ConnectionPassword</name> <value>root123</value> <description>password to use against metastore database</description> </property> |
6）拷贝jdbc驱动包到${HIVE_HOME}/lib
$ cp mysql-connector-java-5.1.34-bin.jar ../ruan/hive/lib/
四、Hive基本操作
一）启动Hive的条件
1.检查hadoop的相关进程
$ jps
26514 SecondaryNameNode
27934 ResourceManager
28033 NodeManager
26232 NameNode
28590 Jps
26329 DataNode
2.启动进入Hive CLI
${HIVE_HOME}/bin存放的hive的启动命令
$ bin/hive
启动之后检查mysql数据库中metastore数据库是否自动创建成功
3.Hive表创建
//创建数据库
create database db01;
//创建表
create table student(
id int,
name string,
age int
)
row format delimited fields terminated by ' '; //指定表格字段的分隔符
//加载数据
load data local inpath ‘/user/hive/student.txt’ into table student;
//查询数据
select * from student;
3.修改${HIVE_HOME}/conf/ 修改hive-site.xml
显示当前所在的数据库名和查询结果的字段名
|
<--!55--> <property> <name>hive.cli.print.header</name> <value>true</value> <description>Whether to print the names of the columns in query output.</description> </property> <--!61--> <property> <name>hive.cli.print.current.db</name> <value>true</value> <description>Whether to include the current database in the Hive prompt.</description> </property> |
创建/删除/清空表基本语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later)
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[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]
[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)]
ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
[STORED AS DIRECTORIES]
[
[ROW FORMAT row_format]
[STORED AS file_format]
| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later)
]
http://chao.com:50075/browseDirectory.jsp
表类型一、管理表或内部表Table Type: MANAGED_TABLE
create table if not exists dept2(
deptno int,
deptname string,
address string
)
row format delimited fields terminated by ' ';
//加载HDFS文件到Hive表中
load data inpath '/input/dept.txt' into table dept;
//用来指定原文件的列分隔符
row format delimited fields terminated by ' ';
load 如果操作的HDFS上的文件，代表着会移动或者剪切文件
desc formatted dept; //描述表结构信息
Location: hdfs://bigdata.ibeifeng.com:8020/user/hive/warehouse/db01.db/dept
Table Type: MANAGED_TABLE
表类型二、外部表
create external table emp(
empno int,
empname string,
empjob string,
mgno int,
birthday string,
salary float,
bonus float,
depno int
)
row format delimited fields terminated by ' '
location '/input/demo';
//描述表结构
desc formatted emp;
Location: hdfs://bigdata.ibeifeng.com:8020/input/demo
Table Type: EXTERNAL_TABLE
删除内部表
drop table dept;
删除外部表
drop table emp;
清空表数据
truncate table student;
内部表和外部表的区别：
创建表
外部表创建表的时候，不会移动数到数据仓库目录中（/user/hive/warehouse），只会记录表数据存放的路径
内部表会把数据复制或剪切到表的目录下
删除表
外部表在删除表的时候只会删除表的元数据信息不会删除表数据
内部表删除时会将元数据信息和表数据同时删除
表类型三、分区表
create table emp_part(
empno int,
empname string,
empjob string,
mgrno int,
birthday string,
salary float,
bonus float,
deptno int
)
partitioned by (province string)
row format delimited fields terminated by ' ';
//向分区表加载数据
load data local inpath '/home/gu/mystudent/emp.txt ' into table emp_part partition (province='CHICAGO');
//描述表信息
desc formatted emp_part;
//查询全表数据
select * from emp_part;
//查询分区字段表数据
select * from part where province='CHICAGO';
//查看分区信息
show partitions emp_part;
//增加分区
aler table emp_part add [if not exist] partition(provine='zhejiang',city='hangzhou')
//删除分区
aler table emp_part drop [if exist] partition(provine='zhejiang',city='hangzhou')
分区表创建表的时候需要指定分区字段，分区字段与普通字段的区别：分区字段会在HDFS表目录下生成一个分区字段名称的目录，而普通字段则不会，查询的时候可以当成普通字段来使用，一般不直接和业务直接相关。
（二）与其他文件系统的交互
1.与Linux系统交互
!ls /opt/sofware/;
2.与hdfs文件系统的交互
dfs -ls /input
dfs -mkdir /hive
3. show databases --显示数据库; //可以直接使用--对语句进行注释
Day02
二级分区表/管理表
create table emp_part1(
empno int,
empname string,
empjob string,
mgrno int,
birthday string,
salary float,
bonus float,
deptno int
)
partitioned by (day string,hour string)
row format delimited fields terminated by ' ';
load data local inpath '/home/user01/emp.txt' into table emp_part1 partition (day='20170308',hour='9');
load data local inpath '/home/user01/emp.txt' into table emp_part1 partition (day='20170308',hour='10');
load data local inpath '/home/user01/emp.txt' into table emp_part1 partition (day='20170308',hour='14');
load data local inpath '/home/user01/emp.txt' into table emp_part1 partition (day='20170309',hour='10');
select * from emp_part1;
select * from emp_part1 where day='20170308'
select * from emp_part1 where day='20170308' and hour='14';
***分区可以理解为分类，通过分类把不同类型，时间，地域的数据放到不同的目录下。
分类的标准就是分区字段，可以一个，也可以多个。
分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全表扫描。
##增加分区
alter table emp_part1 add partition (day='20170306',hour='0');
删除分区
alter table emp_part1 drop partition (day='20170306',hour='0');
【扩展】
[动态分区表]
严格模式：static partitioned by (county string,states string)
非严格模式：partitioned by (county string,states string)
【扩展】
表类型四：桶表
将内部表，外部表和分区表进一步组织成桶表
可以将表的列通过Hash算法进一步分解成不同的文件存储
create table test_bucket_table(
id int,
name string
)
clustered by (id) into 5 bucket;
表类型五：临时表 TEMPORARY hive-0.14以后支持
========================================
一、Hive创建表与加载表,导出的数据的方式
（一）创建表的方式
##方式一 create + load
create [external] table table_name(
col1_name col1_type,
...
coln_name coln_type
)
row format delimited fields terminated by ' ';
//load加载数据
laod data [local] inpth '本地文件(linux)/HDFS' [overwrite] into table table_name;
##方式二 like + load
##复制表结构
create table tableB like tableA; //首先必须要有tableA
//load加载数据
laod data [local] inpth '本地文件(linux)/HDFS' [overwrite] into table table_name;
*** ##方式三 as 创建表的同时加载数据
create table tableB row format delimited filelds termianted by ',' as select * from tableA; //首先必须要有tableA
create table emp_as row format delimited fields terminated by ',' as select empno,empname,salary from emp_part1;
*** ##方式四 create + insert
1.创建表
create table emp_insert(
id int,
name string,
job string,
salary float
)
row format delimited fields terminated by ',';
2.insert into 加载数据
insert into table emp_insert select empno,empname,empjob,salary from emp_part1 where day='20170308' and hour='14';
（二）加载数据的方式
加载方式一
1.加载本地文件到Hive表 --使用存储介质（移动硬盘）
laod data local inpth '本地文件(linux)' [overwrite] into table table_name;
2.加载HDFS文件到hive表 --通过Flume等日志收集框架
laod data inpth 'HDFS文件' [overwrite] into table table_name;
3.加载数据示覆盖已有的数据
laod data [local] inpth '文件' overwrite into table；
**hdfs dfs -put 本地目录及文件 表在HDFS所在的目录
desc formatted table_name; --找到table_name在HDFS上的目录
4.创建表时通过select查询语句加载数据
create table tableB row format delimited filelds termianted by ',' as select * from tableA; 5.先创建表，通过insert into table table_namea select * fom tableB
6.创建外部表示通过location指定数据所在目录
create extrnal table_name(
col1_name col1_type,
...
coln_name coln_type
)
row format delimited fields terminated by ' ';
location 'HDFS上的目录'
（三）几种导出数据的方式
1.insert overwrite ... 导出到本地目录
insert overwrite local directory '/home/user01/export' row format delimited fields terminated by ' ' select * from emp_part1;
2.insert overwrite ... 导出到HDFS之上
insert overwrite directory '/export' select * from emp_part1 where day='20170308';
3.hive -e 'HQL query' >> test
bin/hive -e 'select * from db01.student' >> test.txt
4)sqoop
三、基本查询
SELECT [DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY col_list]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY col_list]
]
[LIMIT number]
1.查询表中的某几个字段
select empno,empname from emp;
2.distinct 去重
select distinct(depno) from emp;
3.count 统计计数
select count(empno) from emp;
select count(*) from emp;
select count(1) from emp;
4.别名
select count(empno) as empnonum from emp a;
5.where
** and
** or
** between ... and ..
** + - * /
** < > = >= <=
** is null is not null
select * from emp where depno=20;
select * from emp where depno=20 and salary > 2000;
select * from emp where depno=20 or empno=7521;
select * from emp where salary between 2000 and 5000;
select * from emp where salary > 3000;
select empname, salary, bonus,(salary+bonus) as tolsal from emp;
select *
6.limit from emp limit 2;
7.聚合函数
** count
count(*) -所有值不全为null时，加1操作
count(1) -不管有没有值，只要有这条记录，值就加1
count(col) -col列里面的值为null，值不会加1，这个列的值不为null，才加1
select count(bonus) from emp;
** avg 平均值
** sum 求和
** max 最大值
** min 最小值
select avg(salary) salary from emp;
select depno,avg(salary) salary from emp group by depno;
select depno,max(salary) salary from emp group by depno;
select depno,min(salary) salary from emp group by depno;
8.group by
* 求每个部门的薪资总和
select depno,sum(salary) sum_salary from emp group by depno;
* 求每个部门的薪资最高的员工，薪水
【错误】select empname,max(salary) salary from emp group by depno;
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'
Error: Error while compiling statement: FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname' (state=42000,code=10025)
***注意：group by 后面没有出现的字段，不能直接放到select后面，为了构成语法，可以结合聚合函数使用。
FAILED: SemanticException [Error 10025]: Line 1:7 Expression not in GROUP BY key 'empname'
【语法正确-结果错误,须用子查询方式】select max(empname),max(salary) max_sal from emp group by deptno;
select e.empno,e.empname,e.salary,e.depno from emp e where e.salary in (select max(salary) salary from emp group by depno);
elect e.empname from emp e where e.deptno in (select d.deptno from dept d where d.deptname='SALES' or d.deptname='ACCOUNTING');
Hive的子查询
Hive只支持where和from后面的子查询
-语法中的括号
-合理的书写风格
-hive只支持where和from语句中的子查询
-主查询和子查询可以不是同一张表
-子查询中的空值问题
9.having 条件过滤
select depno, avg(salary) avg_sal from emp group by depno hvaing avg_sal >3000;
10.union union all
必须保证union all 前后select 查询字段的个数和字段类型相同
select depno from emp
union all
select depno from dept;
11 join (map join, reduce join, SMB join)
* on 条件 --等值连接
查询员工姓名、部门名称及员工薪资
select a.empname,b.deptname,a.salary from emp a join dept b on a.depno=b.depno;
//左连
select a.empname,b.deptname,a.salary from emp a left join dept b on a.depno=b.depno;
//右连
select a.empname,b.deptname,a.salary from emp a right join dept b on a.depno=b.depno;
两张表中没有共同字段做jion
select * from student join dept on 1=1;
四、数据类型 https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types
基本数据类型 和 复杂类型
基本数据类型
Numeric Types
TINYINT
SMALLINT
INT/INTEGER
BIGINT
FLOAT
DOUBLE
DOUBLE PRECISION
DECIMAL
Date/Time Types
TIMESTAMP
DATE
INTERVAL
String Types
STRING
VARCHAR
CHAR
Misc Types
BOOLEAN
BINARY (Note: Only available starting with Hive 0.8.0)
复杂数据类型：
- Array：数组类型，由一系列相同数据类型的元素组成
- Map:集合类型，包括key -> Value 键值对，可以通过key来访问元素[key相同的会被覆盖]
- Struct：结构类型，可以包含不同数据类型的元素。这些元素可以通过“点语法”的方式来得到所需要的元素
【扩展】
create table people_movie(
name string, --学生名称
movie map<string,string> ) --电影名称，女一号
row format delimited fields terminated by " "
collection items terminated by ","
map keys terminated by ":";
vi movie.txt
A ABC:2016-05,EFG:2016-09
B OPQ:2015-06,XYZ:2016-04
select * from people_movie;
A {"ABC":"2016-05","EFG":"2016-09"}
B {"OPQ":"2015-06","XYZ":"2016-04"}
create table student(
sid int,
sname string,
grade arrary<float>)
id name grade(英语，语文，数学)
{1 Tom [80, 90, 75]}
create table student3(
sid int,
info struct<name:string,age:int,sex:string>);
{1， {'Tom',10,'男'}}
五、Hiveserver2 应用场合：beeline java代码JDBC
hiveserver：这个可以让Hive以提供Trift服务的服务器形式来运行，可以允许许多不同语言编写（C python java）的客户端进行通信。使用需要启动HiveServer服务以和客户端联系，我们可以通过设置HIVE_PORT环境变量来设置服务器所监听的端口号，在默认的情况下，端口为10000。可以通过下面方式来启动HiveServer：
bin/hive --service hiveserver2 &
hiveServer/HiveServer2
1：简单介绍
两者都允许远程客户端使用多种编程语言，通过HiveServer或者HiveServer2，客户端可以在不启动CLI的情况下对Hive中的数据进行操作，连这个和都允许远程客户端使用多种编程语言如java，python等向hive提交请求，取回结果（从hive0.15起就不再支持hiveserver了），但是在这里我们还是要说一下hiveserver
HiveServer或者HiveServer2都是基于Thrift的，但HiveSever有时被称为Thrift server，而HiveServer2却不会。既然已经存在HiveServer，为什么还需要HiveServer2呢？这是因为HiveServer不能处理多于一个客户端的并发请求，这是由于HiveServer使用的Thrift接口所导致的限制，不能通过修改HiveServer的代码修正。因此在Hive-0.11.0版本中重写了HiveServer代码得到了HiveServer2，进而解决了该问题。HiveServer2支持多客户端的并发和认证，为开放API客户端如JDBC、ODBC提供更好的支持。
2：两者的区别
Hiveserver1 和hiveserver2的JDBC区别：
version Connection URL Driver Class
HiveServer2 jdbc:hive2://: org.apache.hive.jdbc.HiveDriver
HiveServer1 jdbc:hive://: org.apache.hadoop.hive.jdbc.HiveDriver
修改${HIVE_HOME}/conf/hive-site.xml
|
<--!2157--> <property> <name>hive.server2.long.polling.timeout</name> <value>5000</value> </property> <--!2170--> <property> <name>hive.server2.thrift.port</name> <value>10000</value> </property> <--!2177--> <property> <name>hive.server2.thrift.bind.host</name> <value>bigdata.ibeifeng.com</value> </property> |
2.在//mysql数据库中创建一个普通用户
（等同于操作hdfs的用户名和密码）
//创建用户
CREATE USER 'gu'@'chao.com' IDENTIFIED BY 'gu';
//为新建用户授权访问hive在mysql的hive的元数据的数据库
GRANT ALL ON metastore.* TO 'chao'@'chao.com' IDENTIFIED BY 'chao';
GRANT ALL ON metastore.* TO 'chao'@'%' IDENTIFIED BY 'chao';
//刷新授权
flush privileges;
3.beeline的使用
启动hiveserver2
bin/hive --service hiveserver2 //前台命令，会驻留到控制台
可以使用如下命令将前台转后台
bin/hive --service hiveserver2 & //前台转后台，启动完成，按回车可以继续进行其他操作
使用如下命令确认hiveserver2进程是否启动成功
netstat -antp | gerp 10000
//其中13154就是hiveserver2的进程
(Not all processes could be identified, non-owned process info
will not be shown, you would have to be root to see it all.)
tcp 0 0 192.168.7.7:10000 0.0.0.0:* LISTEN 13154/java
结束进程的命令：$ kill -9 13154
$ bin/beeline
beeline>!connect jdbc:hive2://bigdata.ibeifeng.com:10000
Enter username for jdbc:hive2://bigdata.ibeifeng.com:10000: --输入mysql的用户名
Enter password for jdbc:hive2://bigdata.ibeifeng.com:10000:--输入mysql密码
|
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
import org.apache.log4j.Logger;
/** * Handle data through hive on eclipse * @author urey * @time 20131226 19:14 */ public class HiveJdbcClient {
private static String driverName = "org.apache.hive.jdbc.HiveDriver"; private static String url = "jdbc:hive2://[hostname]:10000/default"; private static String user = ""; private static String password = ""; private static String sql = ""; private static ResultSet res; private static final Logger log = Logger.getLogger(HiveJdbcClient.class);
public static void main(String[] args) { try { Class.forName(driverName); //Connection conn = DriverManager.getConnection(url, user, password); //默认使用端口10000, 使用默认数据库，用户名密码默认 Connection conn = DriverManager.getConnection("jdbc:hive://[hostname]:10000/metastore", "user01", "1233456"); Statement stmt = conn.createStatement();
// 创建的表名 String tableName = "testHiveDriverTable";
/** 第一步:存在就先删除 **/ sql = "drop table " + tableName; stmt.executeQuery(sql);
/** 第二步:不存在就创建 **/ sql = "create table " + tableName + " (key int, value string) row format delimited fields terminated by ' '"; stmt.executeQuery(sql);
// 执行“show tables”操作 sql = "show tables '" + tableName + "'"; System.out.println("Running:" + sql); res = stmt.executeQuery(sql); System.out.println("执行“show tables”运行结果:"); if (res.next()) { System.out.println(res.getString(1)); }
// 执行“describe table”操作 sql = "describe " + tableName; System.out.println("Running:" + sql); res = stmt.executeQuery(sql); System.out.println("执行“describe table”运行结果:"); while (res.next()) { System.out.println(res.getString(1) + " " + res.getString(2)); }
// 执行“load data into table”操作 String filepath = "/home/hadoop/file/test2_hive.txt"; sql = "load data local inpath '" + filepath + "' into table " + tableName; System.out.println("Running:" + sql); res = stmt.executeQuery(sql);
// 执行“select * query”操作 sql = "select * from " + tableName; System.out.println("Running:" + sql); res = stmt.executeQuery(sql); System.out.println("执行“select * query”运行结果:"); while (res.next()) { System.out.println(res.getInt(1) + " " + res.getString(2)); }
// 执行“regular hive query”操作 sql = "select count(1) from " + tableName; System.out.println("Running:" + sql); res = stmt.executeQuery(sql); System.out.println("执行“regular hive query”运行结果:"); while (res.next()) { System.out.println(res.getString(1));
}
conn.close(); conn = null; } catch (ClassNotFoundException e) { e.printStackTrace(); log.error(driverName + " n5ot found!", e); System.exit(1); } catch (SQLException e) { e.printStackTrace(); log.error("Connection error!", e); System.exit(1); }
} } |
Day03
SQOOP SQL-TO-Hadoop
Apache Sqoop(TM) is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases.
1.SQOOP的使用要点
RDBMS：关系数据库访问操作四要素
1）jdbcurl //连接url
2）username //用户名
3）password //密码
4）tablename //操作表名
Ways:以HDFS/hive为参照，确定导入导出方向
1）improt mysql --> Hive/HDFS //导入
2）export Hive/HDFS --> mysql //导出
数据存放的载体
1）HDFS ；
path
2）hive
tablename
操作数据的目的
* RDBMS 导入到HDFS/hive上面
**把关系型数据库的表（历史数据）导入到HDFS/Hive做数据分析
* hive 表的数据导出到RDBMS
** Hive的分析结果导出到MySQL做业务展示
一、搭建SQOOP
1.${SQOOP_HOME}/conf/
$ cp sqoop-env-template.sh sqoop-env.sh //拷贝模板生成配置环境目录
=============修改sqoop-env.sh 配置文件
#Set path to where bin/hadoop is available
export HADOOP_COMMON_HOME=/opt/modules/cdh/hadoop-2.5.0-cdh5.3.6
#Set path to where hadoop-*-core.jar is available
export HADOOP_MAPRED_HOME=/opt/modules/cdh/hadoop-2.5.0-cdh5.3.6
#set the path to where bin/hbase is available
#export HBASE_HOME=
#Set the path to where bin/hive is available
export HIVE_HOME=/opt/modules/cdh/hive-0.13.1-cdh5.3.6
#Set the path for where zookeper config dir is
export ZOOCFGDIR=/opt/modules/cdh/zookeeper-3.4.5-cdh5.3.6/conf
==========================
2.拷贝mysql的jdbc驱动包到${SQOOP_HOME}/lib
$ cp mysql-connector-java-5.1.34-bin.jar /opt/modules/cdh/sqoop-1.4.5-cdh5.3.6/lib/
##测试连接--MySQL
bin/sqoop list-databases
--connect jdbc:mysql://bigdata.ibeifeng.com:3306
--username root
--password root123
Preparing to use a MySQL streaming resultset.
information_schema
cdhmetastore
metastore
mysql
test
3.SQOOP的导入
首先在MySQL中使用下面的命令创建测试用的数据库和表，同时为表插入数据
create database testdb;
use testsb;
create table user(
id int not null auto_increment,
account varchar(10) default null,
password varchar(10) default null,
primary key(id)
);
insert into user (account,password) values ("aaa","123");
insert into user values ("aaa","123");
update user set password="213" where id=2;
insert into user values (null,"ccc","321");
insert into user values (null,"ddd","456");
insert into user values (null,"eee","546");
insert into user(account,password) values("fff","654");
insert into user(account,password) values("ggg","789");
insert into user(account,password) values("hhh","879");
insert into user(account,password) values("iii","987");
1）导入 MySQL -> HDFS
(1) 全表导入
bin/sqoop import
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
--password root123
--table user
--target-dir /input/import
--num-mappers 1
--fields-terminated-by ","
bin/sqoop import
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
-P //不使用明文指定数据库连接密码
--table user
--target-dir /input/import //指定数据导入到HDFS上的目录
--delete-target-dir //如果目标目录已存在，则先删除
--num-mappers 1 //指定使用导入数据的map个数
--fields-terminated-by "," //目标文件的分隔符
(2) query，where子句必须有$CONDITONS(固定写法) 查询导入方式
bin/sqoop import
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
-P
--query 'select id,account from user where account="fff" and $CONDITIONS'
--target-dir /input/query
-m 1
--delete-target-dir
--fields-terminated-by " "
(3)增量导入
bin/sqoop import
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
--password root123
--table user
--target-dir /input/append
-m 1
--fields-terminated-by " "
--check-column id
--incremental append
--last-value 2
--append and --delete-target-dir can not be used together.
2)MySQL -> Hive
bin/sqoop import
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
--password root123
--table user
-m 1
--delete-target-dir
--fields-terminated-by " "
--hive-import
--hive-overwrite
--hive-database db01
--hive-table user
3).从HDFS、Hive表导出到Mysql
由于hive表的数据库和表其实就是HDFS上的目录和文件，所以从Hive表导出数据就是从HDFS对应的目录导出数据
先在mysql中创建表接收导出数据
create table hive2mysql(
deptno int,
deptname varchar(20),
address varchar(20)
);
bin/sqoop export
--connect jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username root
--password root123
--table hive2mysql
--num-mappers 1
--export-dir /user/hive/warehouse/db01.db/dept
--fields-terminated-by " "
4)可以将Sqoop的命令选项写在文件，通过【--options-file】指定文件，进行运行程序。
vim sqoop_script
export
--connect
jdbc:mysql://bigdata.ibeifeng.com:3306/testdb
--username
root
--password
root123
--table
hive2mysql
--num-mappers
1
--export-dir
/user/hive/warehouse/db01.db/dept
--fields-terminated-by
" "
$ bin/sqoop --options-file ~/sqoop_script
======================================
Hive 自定义函数函数
使用内置的函数无法完成分析任务，那么需要写自定义函数
show functions; //查看自带的所有的内置函数
desc function upper; //查看具体的某个函数的用法
desc function extended upper; //带有具体案例
##分三类
## UDF 一进一出 处理原文件内容某些字段包含 [] ""
## UDAF 多进一出 sum() avg() max() min()
## UDTF 一进多出 ip -> 国家 省 市
UDF函数的开发
** 必须继承UDF类
** 重写evaluate函数 支持重载
** 必须要有返回类型,可以返回null,但是返回类型不能为void
** 建议使用Text/LongWritable
## 1.创建一个maven项目
## 2.修改pom.xml文件
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-jdbc</artifactId>
<version>0.13.1</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>0.13.1</version>
</dependency>
## 3.替换repository
## 4.包含hive的依赖的jar的repository
## 代码实现(注意必须实现一个名为evaluate的方法)
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public class SalaryUDF extends UDF{
public Text evaluate(Text salaryText){
Text text = new Text();
//1.判断salaryText是否为null
if (salaryText == null) {
return null;
}
//2.判断salaryText是否可转换为一个double类型
double salary = 0;
try {
salary = Double.valueOf(salaryText.toString());
} catch (NumberFormatException e) {
e.printStackTrace();
return null;
}
if (salary > 3000) {
text.set("大于3000的一组...");
return text;
}else if (salary <= 3000 && salary > 2000) {
text.set("小于等于3000并且大于2000的一组...");
return text;
}else {
text.set("小于等于2000的一组");
return text;
}
}
}
5.编写使用UDF
1、编程
2、把程序到出为jar包放到目标机器上去：
hive> add jar /home/beifeng/jars/lower.jar ;
3、创建临时函数：
hive> CREATE TEMPORARY FUNCTION my_lower AS 'org.apache.hadoop.udf.Lower';
4、使用指定函数：
hive> show fuctions ;
hive> select my_lower(ename) from emp ;
Day04
内容：
1.离线分析平台的应用
2.常见内置函数和自定义函数
3.压缩
BI
一、Hive在离线分析平台的应用
1.网站
电商网站：
京东、淘宝、一号店
游戏网站
4399 17173 【游戏推广 游戏排行榜 账号交易】
旅游网站
途牛 【报团，景点推荐，售票】
在线教育
学员的行为（浏览课程，课程咨询，购买课程）
2.统计分析指标 网站 KPI指标
基本指标：PV UV 独立IP 二跳率 客户转化 会话时长
3.统计数据的来源
应用服务器的日志文件
【扩展】
WEB服务器：只负责处理静态网页HTML，通过HTTP协议
应用服务器：负责 处理JSP，Servlet，负责业务逻辑页面跳转
【网站埋点技术：jsSDK JavaSDK】 【爬虫】
4.日志收集系统
Flume
Kafka
【扩展了解ELK】：日志收集平台
5.ETL过滤清洗
写MR程序
过滤空值，脏数据
字段解析 补全
6.Hive统计分析 （思路） 【扩展了解：impala】
1）.创建什么样的表
2）.根据业务（7个：用户模块，订单模块，地域维度模块），创建中间表，大表拆小表，统计分析模块可以减少数据量的反复加载
【多维度分析：时间，地域维度，平台，浏览器维度】
周期性统计分析：天 //粒度问题 最小的分析时间单位 /h 每小时
【案例】
1.创建二级分区表
//创建数据库
create database db_track;
//创建二级分区表
create table tracklog(
id string,
url string,
referer string,
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
sessionId string,
trackerU string,
trackerType string,
ip string,
trackerSrc string,
cookie string,
orderCode string,
trackTime string,
endUserId string,
firstLink string,
sessionViewNo string,
productId string,
curMerchantId string,
provinceId string,
cityId string,
fee string,
edmActivity string,
edmEmail string,
edmJobId string,
ieVersion string,
platform string,
internalKeyword string,
resultSum string,
currentPage string,
linkPosition string,
buttonPosition string
)
partitioned by (date string, hour string)
row format delimited fields terminated by ' ';
load data local inpath '/home/user01/tracklogs/20150828/2015082818' into table tracklog partition(date="20150828",hour="18");
load data local inpath '/home/user01/tracklogs/20150828/2015082819' into table tracklog partition(date="20150828",hour="19");
2.根据不同的统计分析业务需求创建中间表或者临时表，以及结果表
//创建统计结果表
create table static_PVUV(
date string,
hour string,
pv int,
uv int
)
row format delimited fields terminated by ' ';
//统计分析并插入结果表中
insert overwrite table static_PVUV select date,hour,count(url),count(distinct guid) from tracklog where date="20150828" group by date,hour;
20150828 18 64972 23938
20150828 19 61162 22330
3）统计分析结果数据导出到mysql，以便于作图形化展示
借助SQOOP
create database daily_log;
use daily_log;
create table static_PVUV(
date int,
hour int,
pv int,
uv int
);
4)Sqoop导出数据
vim hive2mysql.opt
export
--connect
jdbc:mysql://bigdata.ibeifeng.com:3306/daily_log
--username
root
--password
root123
--table
static_PVUV
--input-fields-terminated-by
--export-dir
/user/hive/warehouse/db_track.db/static_pvuv //在hive中使用desc formatted static_PVUV;来确认表的目录所在
{$SQOOP_HOME}下
bin/sqoop --options-file /home/user01/hive2mysql.opt
+----------+------+-------+-------+
| date | hour | pv | uv |
+----------+------+-------+-------+
| 20150828 | 19 | 61162 | 22330 |
| 20150828 | 18 | 64972 | 23938 |
+----------+------+-------+-------+
5）使用shell脚本结合定时脚本周期性自动执行
#!/bin/sh
##执行环境变量,以便于脚本中可以使用。“.”的后面有一个空格,代表执行这个脚本文件
. /etc/profile
# 定义Hive目录
HIVE_DIR=/opt/modules/cdh/hive-0.13.1-cdh5.3.6
# 定义数据源目录
DATA_LOG=/home/user01/tracklogs
# 定义昨天的日期,按照固定格式,可以使用echo $(date --date="1 day ago" +%Y%m%d)尝试
YESTERDAY=$(date --date="1 day ago" +%Y%m%d)
# for循环中do...done中间的循环体执行的次数等于in后面元素的个数
# 在我们这里,hql文件被调用了2次
for LOG_DAY in `ls $DATA_LOG/$YESTERDAY`
do
#分别取出文件名中的日期和小时
DATE=${LOG_DAY:0:8}
HOUR=${LOG_DAY:8:2}
#在测试时,可以使用echo打印下面命令
$HIVE_DIR/bin/hive -e "load data local inpath '$DATA_LOG/$YESTERDAY/${DATE}${HOUR}' into table db_log.track_log partition (date='$DATE',hour='$HOUR')"
done
===crontable 定时任务
30 0 * * * /bin/sh /home/user01/loaddata.sh
二、Hive的内置函数
1.查看内置函数的相关指令
查看所有的内置函数：
show functions;
显示函数的描述信息
desc function substr;
显示函数的扩展描述信息
desc function extended substr;
2.内置函数分类
Hive的函数
数学函数
round -四舍五入
select round(12.3456, 2), round(12.3456, 0), round(12.3456, -1), round(12.3456, -2);
ceil --向上取整
select ceil(12.3456)
floor --向下取整
select floor(12.3456)
字符函数
lower --将所有字符转换成小写
upper() --将所有字符转换成大写
select lower('Hello World'),upper('Hello World');
length --字符传串长度
concat --连接字符串
select concat(empname,salary) form emp ;
substr --字符串截取
select substr(birthday,1,4) from emp;
trim --去空格
lpad --左填充 第一个代表被填充的对象，第二个代表填充之后的总位数，第三个代表用什么填充
rpad --右填充
select lpad('Hello',10,'lilei,'),rpad('Hello',10,'lilei');
日期函数
unix_timestamp --将时间转化为时间戳
select unix_timestamp('2017-03-13 15:22:30');
to_date --抽取date或者日期中的date部分
select to_date('2017-03-13 15:22:30');
year
month
day
select year('2017-03-13 15:22:30'),month('2017-03-13 15:22:30'),day('2017-03-13 15:22:30');
weekofyear --指定的日期输入一年中的哪一周
select weekofyear('2017-03-13 15:22:30')；
datediff --指定的两个日期之间的相差的时间
select atediff('2017-03-13 15:22:30','2016-03-13 15:22:30');
date_add --指定的日期加上一个数字的日期
date_sub --指定的日期减去一个数字的日期
select date_add('2017-03-13 15:22:30',2),date_sub('2017-03-13 15:22:30',2);
类型转换函数
cast
select cast(1422/1000 as int) from emp;
条件函数
coalesce -c从左到右返回第一个不为null的值
case a when b then c [when d then e]* [else f] end
应用案例：
涨工资，总裁+1000，经理+800，其他员工+400
select empname,empjob,salary,case empjob when'PRESIDENT' then salary+1000 when 'MANAGER' then salary+800 else salary+400 end form emp;
特殊函数：
窗口函数：lead lag FIRST_VALUE
分析函数:RANK ROW_NUMBER
三、四大排序
1、order by //可以指定desc 降序 asc 升序
order by会对输入做全局排序，因此只有一个Reducer(多个Reducer无法保证全局有序)，然而只有一个Reducer，会导致当输入规模较大时，消耗较长的计算时间。
mapreduce 默认的分区算法hash 取模
a c e reduce-0
b d f reduce-0
a b c d e f
e.g.1:
create table temperature(
year int,
temper float
)
row format delimited fields terminated by ' ';
temperature.year temperature.temper
2008 32.0
2008 21.0
2008 31.5
2008 17.0
2013 34.0
2015 32.0
2015 33.0
2015 15.9
2015 31.0
2015 19.9
2015 27.0
2016 23.0
2016 39.9
2016 32.0
2.sort by
sort by不是全局排序，其在数据进入reducer前完成排序，因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只会保证每个reducer的输出有序，并不保证全局有序。sort by不同于order by，它不受Hive.mapred.mode属性的影响，sort by的数据只能保证在同一个reduce中的数据可以按指定字段排序。使用sort by你可以指定执行的reduce个数(通过set mapred.reduce.tasks=n来指定)，对输出的数据再执行归并排序，即可得到全部结果。
/设置reduce个数为3;
set mapred.reduce.tasks=3;
//查询此次任务中reduce的个数;
set mapred.reduce.tasks;
insert overwrite local directory '/home/user01/sort' row format delimited fields terminated by ' ' select * from temperature sort by year;
[user01@hadoop sort]$ ls
000000_0 000001_0 000002_0
[user01@hadoop sort]$ cat 000000_0
2008 31.5
2008 21.0
2015 31.0
2015 32.0
2015 33.0
2016 23.0
[user01@hadoop sort]$ cat 000001_0
2008 17.0
2013 34.0
2015 19.9
2015 15.9
2016 39.9
2016 32.0
[user01@hadoop sort]$ cat 000002_0
2008 32.0
2015 27.0
3.distribute by
distribute by是控制在map端如何拆分数据给reduce端的。hive会根据distribute by后面列，对应reduce的个数进行分发，默认是采用hash算法。sort by为每个reduce产生一个排序文件。在有些情况下，你需要控制某个特定行应该到哪个reducer，这通常是为了进行后续的聚集操作。distribute by刚好可以做这件事。因此，distribute by经常和sort by配合使用。
//根据年份和气温对气象数据进行排序，以确保所有具有相同年份的行最终都在一个reducer分区中
注：Distribute by和sort by的使用场景
1.Map输出的文件大小不均。
2.Reduce输出文件大小不均。
3.小文件过多。
4.文件超大。
$ cat distribute/000000_0
2016,23.0
2016,39.9
2016,32.0
2013,34.0
$ cat distribute/000001_0
2008,32.0
2008,21.0
2008,31.5
2008,17.0
$ cat distribute/000002_0
2015,31.0
2015,19.9
2015,27.0
2015,32.0
2015,33.0
2015,15.9
//二者结合使用
select * from temperature distribute by year sort by year asc, temper desc;
2013 34.0
2016 39.9
2016 32.0
2016 23.0
2008 32.0
2008 31.5
2008 21.0
2008 17.0
2015 33.0
2015 32.0
2015 31.0
2015 27.0
2015 19.9
2015 15.9
4.cluster by
cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是倒叙排序，不能指定排序规则为ASC或
者DESC。
select * from cluster by year;
四、分组排名
TOPN
row number
说明：
row_number() over ([partition col1] [order by col2])
rank() over ([partition col1] [order by col2])
dense_rank() over ([partition col1] [order by col2])
它们都是根据col1字段分组，然后对col2字段进行排序，对排序后的每行生成一个行号，这个行号从1开始递增
col1、col2都可以是多个字段，用‘,‘分隔
区别：
1）row_number：不管col2字段的值是否相等，行号一直递增，比如：有两条记录的值相等，但一个是第一，一个是第二
2）rank：上下两条记录的col2相等时，记录的行号是一样的，但下一个col2值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
3）dense_rank：上下两条记录的col2相等时，下一个col2值的行号递增1，比如：有两条并列第一，下一个是第二
select empname,empjob,salary,deptno,row_number() over(partition by deptno order by salary asc ) rank from emp;
empname empjob salary deptno rank
MILLER CLERK 1300.0 10 1
CLARK MANAGER 2450.0 10 2
KING PRESIDENT 5000.0 10 3
SMITH CLERK 800.0 20 1
ADAMS CLERK 1100.0 20 2
JONES MANAGER 2975.0 20 3
SCOTT ANALYST 3000.0 20 4
FORD ANALYST 3000.0 20 5
JAMES CLERK 950.0 30 1
MARTIN SALESMAN 1250.0 30 2
WARD SALESMAN 1250.0 30 3
TURNER SALESMAN 1500.0 30 4
ALLEN SALESMAN 1600.0 30 5
BLAKE MANAGER 2850.0 30 6
//测试原数据
Hive TopN
a chinese 98
a english 90
a math 90
d chinese 88
c english 82
c math 98
b math 79
b chinese 79
b english 79
z english 90
z math 89
z chinese 80
e math 99
e english 87
d english 90
create table t(name string, sub string, score int) row format delimited fields terminated by ' ';
load data local inpath "/home/user01/grades.txt" into table t;
//为每个学生的各门功课成绩排名
1、row_number
select *,row_number() over (partition by name order by score desc) as rank from t;
t.name t.sub t.score rank
a chinese 98 1
a english 90 2
a math 90 3
b chinese 79 1
b english 79 2
b math 79 3
c math 98 1
c english 82 2
d english 90 1
d chinese 88 2
e math 99 1
e english 87 2
z english 90 1
z math 89 2
z chinese 80 3
2.rank //排序字段相同的记录使用相同的排名，下一个从值的行号递增N（N是重复的次数），比如：有两条并列第一，下一个是第三，没有第二
select *,rank() over (partition by name order by score asc) as rank from t;
t.name t.sub t.score rank
a english 90 1
a math 90 1
a chinese 98 3
b chinese 79 1
b english 79 1
b math 79 1
c english 82 1
c math 98 2
d chinese 88 1
d english 90 2
e english 87 1
e math 99 2
z chinese 80 1
z math 89 2
z english 90 3
3、dense_rank //排序字段相同的记录使用相同的排名，下一个值的行号递增1，如下：a的english和math并列第一，下一个chinese是第三，没有第二，b的三门都一样
select *,dense_rank() over (partition by name order by score asc) as rank from t;
t.name t.sub t.score rank
a english 90 1
a math 90 1
a chinese 98 2
b chinese 79 1
b english 79 1
b math 79 1
c english 82 1
c math 98 2
d chinese 88 1
d english 90 2
e english 87 1
e math 99 2
z chinese 80 1
z math 89 2
z english 90 3
业务实例：
统计每个学科的前二名
select * from (select *, row_number() over(partition by name order by score desc) as rank from t )t where rank <=2;
select *,row_number() over () as rank from t rank <=3;
select area, barnd, yuan, row_number() over (partition by area order by yuan desc) as rank from order where rank <=3;
各地区热门商品统计
按地区 分组 再按各个商品的销量进行降序排名
北京 iphone7 70000 1
北京 xiaomi5 60000 2
北京 mate9 50000 3
北京 手机膜 40000 4
...
上海 xiaomi5 70000 1
上海 iphone7 60000 2
上海 mate9 50000 3
上海 手机膜 40000 4
..
五、Hive的数据压缩
1.Hive使用的文件格式
: SEQUENCEFILE//序列化 #二进制格式，无法使用cat查看
| TEXTFILE -- (Default, depending on hive.default.fileformat configuration)
| RCFILE -- (Note: Available in Hive 0.6.0 and later) Row存储 Columnar字段也就是列
| ORC -- (Note: Available in Hive 0.11.0 and later) O optimized 优化
| PARQUET -- (Note: Available in Hive 0.13.0 and later)
| AVRO -- (Note: Available in Hive 0.14.0 and later)
可以将默认TEXTFILE 文件格式的表映射完成之后，另外保存成其他格式ORC PARQUE
create table if not exists file_text(
t_time string,
t_url string,
t_uuid string,
t_refered_url string,
t_ip string,
t_user string,
t_city string
)
row format delimited fields terminated by ' '
stored as textfile;
load data local inpath '/home/user01/page_views.data' into table file_text;
//默认的TEXTFILE格式大小
dfs -du -s -h /user/hive/warehouse/db01.db/file_text;
18.1 M 18.1 M /user/hive/warehouse/db01.db/file_text
//存储为ORC格式
create table if not exists file_orc row format delimited fields terminated by ' ' stored as ORC as select * from file_text;
dfs -du -s -h /user/hive/warehouse/db01.db/file_orc;
2.6 M 2.6 M /user/hive/warehouse/db01.db/file_orc
//存储为parquet格式
create table if not exists file_parquet row format delimited fields terminated by ' ' stored as PARQUET as select * from file_text;
dfs -du -s -h /user/hive/warehouse/db01.db/file_parquet;
13.1 M 13.1 M /user/hive/warehouse/db01.db/file_parquet
2.压缩
减少磁盘存储压力，负载
减少IO负载
1）.首先，要保证hadoop是支持压缩
检查是否支持压缩算法
$ bin/hadoop checknative
Native library checking:
hadoop: false
zlib: false
snappy: false
lz4: false
bzip2: false
openssl: false
snappy 压缩比和速度相当于适中的
2）编译hadoop源码：mvn package -Pdist,native,docs -DskipTests -Dtar -Drequire.snappy
3）##替换$HADOOP_HOME/lib/native
$ tar -zxf cdh5.3.6-snappy-lib-natirve.tar.gz -C ./
再次检查 $ bin/hadoop checknative
Native library checking:
hadoop: true /opt/modules/cdh/hadoop-2.5.0-cdh5.3.6/lib/native/libhadoop.so.1.0.0
zlib: true /lib64/libz.so.1
snappy: true /opt/modules/cdh/hadoop-2.5.0-cdh5.3.6/lib/native/libsnappy.so.1
lz4: true revision:99
bzip2: true /lib64/libbz2.so.1
openssl: true /usr/lib64/libcrypto.so
启动dfs，yarn，historyserver，然后提交一个job
$ bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0-cdh5.3.6.jar pi 1 2
完成之后，web界面查看完成这个任务的配置
mapreduce.map.output.compress false
mapreduce.map.output.compress.codec org.apache.hadoop.io.compress.DefaultCodec
4）配置mapred-site.xml，在原有的下方增加如下内容
<property>
<name>mapreduce.map.output.compress</name>
<value>true</value>
</property>
<property>
<name>mapreduce.map.output.compress.codec </name>
<value>org.apache.hadoop.io.compress.SnappyCodec</value>
</property>
结束所有进程，重新启动所有进程dfs，yarn，historyserver，再提交一个job
$ bin/yarn jar share/hadoop/mapreduce/hadoop-mapreduce-examples-2.5.0-cdh5.3.6.jar pi 3 5
完成之后，web界面重新查看完成这个任务的配置
mapreduce.map.output.compress true job.xml ⬅ mapred-site.xml
mapreduce.map.output.compress.codec org.apache.hadoop.io.compress.SnappyCodec
/启用压缩snappy+存储为ORC格式
方式一 在MapReduce的shuffle阶段启用压缩
set hive.exec.compress.output=true;
set mapred.output.compress=true;
set mapred.output.compression.codec=org apache.hadoop.io.compress.SnappyCodec;
create table if not exists file_orc_snappy(
t_time string,
t_url string,
t_uuid string,
t_refered_url string,
t_ip string,
t_user string,
t_city string
)
row format delimited fields terminated by ' '
stored as ORC
tblproperties("orc.compression"="Snappy");
insert into table file_orc_snappy select * from file_text;
方式二：对reduce输出的结果文件进行压缩
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.codec=org apache.hadoop.io.compress.SnappyCodec;
create table if not exists file_orc_snappy2(
t_time string,
t_url string,
t_uuid string,
t_refered_url string,
t_ip string,
t_user string,
t_city string
)
row format delimited fields terminated by ' '
stored as orc
tblproperties("orc.compression"="Snappy");
insert into table file_orc_snappy2 select * from file_text;
Day05
1.【案例：结合正则表达式 映射特殊格式的表格】
2.UD函数
3.Hive优化
4.案例
"27.38.5.159" "-" "31/Aug/2015:00:04:37 +0800" "GET /course/view.php?id=27 HTTP/1.1" "303" "440" - "http://chao.com/user.php?act=mycourse" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36" "-" "learn.ibeifeng.com"
("[^ ]") ("-[^ ]")
^ 一个字符串的开始
$ 一个字符串的结束
"^The" "There" "The cat"
$e 以字符e结尾的字符串
一个或者一序列字符重复出现的次数
* 0次或多次 {0,}
+ 1次或多次 {1,}
? 至多出现一次 0次或者1次 {0,1}
{}使用范围，用大括号，表示重复出现次数的范围
“ab{2}” 表示一个字符串有一个a跟着2个b
ab{2，4}
[] 表示某些字符允许在一个字符串中某一个特定位置出现
^[a-zA-Z]:标表示一个以字母开头的字符串
| 表示“或”操作
hi|hello 一个字符串里有"hi"或者"hello"
(b|cd)ef 表示"bef"或"cdef"
[^] 表示不希望出现的字符
$ () "" 希望匹配这些字符的时候，应该在特殊字符前面加转义字符""
("[^ ]*") ("-|[^ ]*") ("[^]]*") ("[^]]*") ("[0-9]*") ("[0-9]*") (-|[^ ]*) ("[^ ]*") ("[^"]*") ("-|[^ ]*") ("[^ ]*")
"27.38.5.159" "-" "31/Aug/2015:00:04:37 +0800" "GET /course/view.php?id=27 HTTP/1.1" "303" "440" - "http://chao.com/user.php?act=mycourse" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36" "-" "learn.ibeifeng.com"
drop table if exists apachelog;
CREATE TABLE apachelog (
remote_addr string,
remote_user string,
time_local string,
request string,
status string,
body_bytes_set 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;
Eclipse 开发UDF函数
1.替换本地repository目录
${MAVEN_HOME}/conf/settings.xml
<localRepository>~.m2 epository</localRepository>
$tar -zxf repository.tar.gz -C ~.m2 epository
2.Eclispe windows -> prefereneces -> [键入maven] ->替换为自己安装的maven3.0.5 -> User settings -> Reindex
3.原来的maven工程中的porm.xml 添加两组和hive相关的<dependency>
4.在项目名称上右键-> Maven -> Update Project -> force update ...
注意顺序：
先替换repository,接下来修改porm.xml或者创建maven工程
否则，会自动下载jar文件，如果一旦网络出错，jar文件可能就是损坏的，这个时候需要先将出错文件删除掉再重新下，因此不推荐自己去下载
创建临时函数的方式一：
//将导出的jar文件加入到hive的calss path
add jar /home/user01/RemoveQuote.jar;
//创建临时函数 removequote--可以自定义 但是必须指定对应的带包名的主类
CREATE TEMPORARY FUNCTION removequote as 'hive.RemoveQuoteUDF';
//查看创建的函数是否存在
show functions;
//将导出的jar文件加入到hive的calss path
add jar /home/user01/TimeFormat.jar;
Added /home/user01/TimeFormat.jar to class path
Added resource: /home/user01/TimeFormat.jar
//创建临时函数 注意：临时函数名称不能和内置函数名相同
CREATE TEMPORARY FUNCTION TimeFormat as 'hive.TimeFormatUDF';
创建临时函数的方式二：
在hive中使用（第二种方式）
将linux中的jar包上传到自定义位置的hdfs的文件系统
hive (mydb)> create [temporary] function my_lower as 'com.ibeifeng.bigdata.test.hive.udf.Lower' using jar 'hdfs/path';
注：关于'hdfs/path' ：
hdfs://hostname:8020/自定义的文件位置
【对比】
未作任何处理
select remote_addr,time_local,request from apachelog_extract limit 2;
"27.38.5.159" "31/Aug/2015:00:04:37 +0800" "GET /course/view.php?id=27 HTTP/1.1"
"27.38.5.159" "31/Aug/2015:00:04:37 +0800" "GET /login/index.php HTTP/1.1"
//使用removequot临时函数去除字段双引号
select removequote(remote_addr) remote_addr,removequote(time_local) time_local,request from apachelog_extract limit 2
27.38.5.159 31/Aug/2015:00:04:37 +0800 "GET /course/view.php?id=27 HTTP/1.1"
27.38.5.159 31/Aug/2015:00:04:37 +0800 "GET /login/index.php HTTP/1.1"
//使用removequote和timeformat，同时进行去除双引号和格式化时间
select removequote(remote_addr) remote_addr,timeformat(time_local) time_local,request from apachelog_extract limit 2;
27.38.5.159 2015-08-31 00:04:37 "GET /course/view.php?id=27 HTTP/1.1"
27.38.5.159 2015-08-31 00:04:37 "GET /login/index.php HTTP/1.1"
Hive优化
1. fetch task任务不走MapReduce，可以在hive配置文件中设置最大化和最小化fetch task任务；通常在使用hiveserver2时调整为more；
设置参数的优先级：在命令行或者代码设置参数 > hive-site.xml>hive-default.xml
set hive.fetch.task.conversion=more; //单次交互模式下有效，
bin/hive --hiveconf hive.fetch.task.conversion=more
上面的两种方法都可以开启了Fetch任务，但是都是临时起作用的；如果你想一直启用这个功能，可以在${HIVE_HOME}/conf/hive-site.xml里面加入以下配置：
<property>
<name>hive.fetch.task.conversion</name>
<value>more</value>
<description>
Some select queries can be converted to single FETCH task
minimizing latency.Currently the query should be single
sourced not having any subquery and should not have
any aggregations or distincts (which incurrs RS),
lateral views and joins.
1. minimal : SELECT STAR, FILTER on partition columns, LIMIT only
2. more : SELECT, FILTER, LIMIT only (+TABLESAMPLE, virtual columns)
</description>
</property>
2. strict mode：严格模式设置，严格模式下将会限制一些查询操作
文件格式，ORC PARQUET 等
分区表
select 查询不加where过滤条件，不会执行
开启严格模式
hive提供的严格模式，禁止3种情况下的查询模式。
a：当表为分区表时，where字句后没有分区字段和限制时，不允许执行。
b：当使用order by语句时，必须使用limit字段，因为order by 只会产生一个reduce任务。
c：限制笛卡尔积的查询。sql语句不加where不会执行
<property>
<name>hive.mapred.mode</name>
<value>nonstrict</value>
<description>The mode in which the Hive operations are being performed.
In strict mode, some risky queries are not allowed to run. They include:
Cartesian Product.
No partition being picked up for a query.
Comparing bigints and strings.
Comparing bigints and doubles.
Orderby without limit.
</description>
</property>
3. MapReduce过程的map、shuffle、reduce端的snappy压缩
需要先替换hadoop的native本地包开启压缩
在mapred-site.xml文件设置启用压缩及压缩编码
在执行SQL执行时设置启用压缩和指定压缩编码
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.codec=org apache.hadoop.io.compress.SnappyCodec;
4. 大表拆分成子表，提取中间结果集，减少每次加载数据
多维度分析，多个分析模块
每个分析模块涉及字段不一样，而且并不是表的全部字段
5. 分区表及外部表
设计二级分区表（一级字段为天，二级字段设置小时）
创建的的是外部表，创建表时直接指定数据所在目录即可，不用再用load加载数据
6. 设置map和reduce个数：默认情况下一个块对应一个map任务，map数据我们一般不去调整，reduce个数根据reduce处理的数据量大小进行适当调整
体现“分而治之”的思想
set mapred.reduce.tasks=3;
7. 优化sql语句，如先过滤再join，先分组再做distinct
8. 并行执行：一个sql有1、2、3 三个job，其中1、2job是在join后再与3job结果进行join，那么1、2job在join同时3job可以同时并行执行
分布式并行化
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
eg:
select num
from
(select count(city) as num from city
union all
select count(province) as num from province
)tmp;
9. JVM重用：一个job可能有多个map reduce任务，每个任务会开启一个JVM虚拟机，默认情况下一个任务对应一个JVM，任务运行完JVM即销毁，我们可以设置JVM重用参数，一般不超过5个，这样一个JVM内可以连续运行多个任务
JVM重用是Hadoop调优参数的内容，对Hive的性能具有非常大的影响，特别是对于很难避免小文件的场景或者task特别多的场景，这类场景大多数执行时间都很短。hadoop默认配置是使用派生JVM来执行map和reduce任务的，这是jvm的启动过程可能会造成相当大的开销，尤其是执行的job包含有成千上万个task任务的情况。
JVM重用可以使得JVM实例在同一个JOB中重新使用N次，N的值可以在Hadoop的mapre-site.xml文件中进行设置（建议参考5~10）
mapred.job.reuse.jvm.num.tasks
也可在hive的执行设置：
***set mapred.job.reuse.jvm.num.tasks=10;
10. 推测执行：例如一个MapReduce应用有10个任务（map 及reduce），其中9个任务已经完成，那么application Master会在另外启动一个相同的任务来运行未完成的那个，最后哪个先运行完成就把另一个kill掉
启用speculative最大的好处是，一个map执行的时候，系统会在其他空闲的服务器上启动相同的map来同时运行，哪个运行的快就使用哪个的结果，另一个运行慢的在有了结果之后就会被kill。
mapred.map.tasks.speculative.execution=true
mapred.reduce.atasks.specultive.execution=true
hive.mapred.reduce.tasks.speculative.execution=true;
11.查看执行计划
***explain [extended] hql
eg:
explain select no,count(*) from testudf group by no;
explain extended select no,count(*) from testudf group by no;
hive入门学习：explain执行计划的理解 - HAHA的专栏 - 博客频道 - CSDN.NET
http://blog.csdn.net/liyaohhh/article/details/50697076
数据倾斜
对于普通的join操作，会在map端根据key的hash值，shuffle到某一个reduce上去，在reduce端做join连接操作，内存中缓存join左边的表，遍历右边的表，依次做join操作。所以在做join操作时候，将数据量多的表放在join的右边。
当数据量比较大，并且key分布不均匀，大量的key都shuffle到一个reduce上了，就出现了数据的倾斜。
常见的数据倾斜出现在group by和join..on..语句中。
join（数据倾斜）
在进行两个表join的过程中，由于hive都是从左向右执行，要注意讲小表在前，大表在后（小表会先进行缓存）。
key 河南 value 考生
map/reduce程序执行时，reduce节点大部分执行完毕，但是有一个或者几个reduce节点运行很慢，导致整个程序的处理时间很长，这是因为某一个key的条数比其他key多很多（有时是百倍或者千倍之多），这条key所在的reduce节点所处理的数据量比其他节点就大很多，从而导致某几个节点迟迟运行不完，此称之为数据倾斜。
hive在跑数据时经常会出现数据倾斜的情况，使的作业经常reduce完成在99%后一直卡住，最后的１%花了几个小时都没跑完，这种情况就很可能是数据倾斜的原因，
hive.groupby.skewindata=true;
如果是group by过程出现倾斜应将此项设置true。
hive.optimize.skewjion=true;
如果是join 过程中出现倾斜应将此项设置为true
不影响结果可以考虑过滤空值
可以在空值前面加随机散列
4.1Map-side Join
mapJoin的主要意思就是，当链接的两个表是一个比较小的表和一个特别大的表的时候，我们把比较小的table直接放到内存中去，然后再对比较大的表格进行map操作。join就发生在map操作的时候，每当扫描一个大的table中的数据，就要去去查看小表的数据，哪条与之相符，继而进行连接。这里的join并不会涉及reduce操作。map端join的优势就是在于没有shuffle，真好。在实际的应用中，我们这样设置：
***1. set hive.auto.convert.join=true;
这样设置，hive就会自动的识别比较小的表，继而用mapJoin来实现两个表的联合。看看下面的两个表格的连接。
这里的第一句话就是运行本地的map join任务，继而转存文件到XXX.hashtable下面，在给这个文件里面上传一个文件进行map join，之后才运行了MR代码去运行计数任务。说白了，在本质上mapjoin根本就没有运行MR进程，仅仅是在内存就进行了两个表的联合。
hive.optimize.skewjoin=true; 如果是join过程出现倾斜应该设置为true
此时会将join语句转化为两个mapreduce任务，第一个会给jion字段加随机散列
set hive.skewjoin.key=100000; 这个是join的键对应的记录条数超过这个值则会进行优化。
mapjoin使用场景
1.关联操作中有一张表非常小
2.不等值的链接操作
自动执行
set hive.auto.convert.join=true;
hive.mapjoin.smalltable.filesize=25;默认值是25mb
手动执行 A为小表
select /*+mapjoin(A)*/ f.a,f.b from A t join B f on(f.a==t.a)
hive入门学习：join的三种优化方式 - HAHA的专栏 - 博客频道 - CSDN.NET
http://blog.csdn.net/liyaohhh/article/details/50697519
4.2Reduce-side Join
***hive join操作默认使用的就是reduce join
Reduce-side Join原理上要简单得多，它也不能保证相同key但分散在不同dataset中的数据能够进入同一个Mapper，整个数据集合的排序在Mapper之后的shuffle过程中完成。相对于Map-side Join，它不需要每个Mapper都去读取所有的dataset，这是好处，但也有坏处，即这样一来Mapper之后需要排序的数据集合会非常大，因此shuffle阶段的效率要低于Map-side Join。
***reduce side join是一种最简单的join方式，其主要思想如下：
在map阶段，map函数同时读取两个文件File1和File2，为了区分两种来源的key/value数据对，对每条数据打一个标签（tag）,比如：tag=0表示来自文件File1，tag=2表示来自文件File2。即：map阶段的主要任务是对不同文件中的数据打标签。
在reduce阶段，reduce函数获取key相同的来自File1和File2文件的value list， 然后对于同一个key，对File1和File2中的数据进行join（笛卡尔乘积）。即：reduce阶段进行实际的连接操作。
***4.3 SMB Join（sort merge bucket）
SMB 存在的目的主要是为了解决大表与大表间的 Join 问题，分桶其实就是把大表化成了“小表”，然后 Map-Side Join 解决之，这是典型的分而治之的思想。
对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
smb是sort merge bucket操作，首先进行排序，继而合并，然后放到所对应的bucket中去，bucket是hive中和分区表类似的技术，就是按照key进行hash，相同的hash值都放到相同的buck中去。再进行两个表联合的时候。我们首先进行分桶，在join会大幅度的对性能进行优化。也就是说，在进行联合的时候，是table1中的一小部分和table2中的一小部分进行联合，table联合都是等值连接，相同的key都放到了同一个bucket中去了，那么在联合的时候就会大幅度的减小无关项的扫描。
分桶：
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
表优化数据目标：相同数据尽量聚集在一起
两个表以相同方式划分捅。
两个表的桶个数是倍数关系。
create table ordertab(cid int,price,float)clustered by(cid) into 32 buckets;
create table customer(id int,first string)clustered by(id) into 32 buckets;
select price from ordertab t join customer s on t.cid=s.id
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmeger=true;
set hive.input.formtat=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
[小心得，小体会]
hive (db01)> set hive.exec.mode.local.auto=true;
hive (db01)> set hive.exec.mode.local.auto;
hive.exec.mode.local.auto=true
bin/hive --hiveconf hive.fetch.task.conversion=more;
bin/hive --hiveconf hive.exec.mode.local.auto=true;
Hive远程模式
生产环境下用
存放元数据的MySQL数据库服务器和Hive服务器不在同一台上，甚至放在不同的操作系统上。
【问题】
如何确保元数据的安全？
1.定期备份Mysql
2.搭建高可用的Mysql集群
修改hive-site.xml
<property>
<name>hive.metastore.uris</name>
<value>thrift://[hostname]:9083</value>
<description>Thrift URI for the remote metastore. Used by metastore client to connect to remote metastore.</description>
</property>
开启metastore服务或者进程
$ bin/hive --service metastore &
$ bin/hive --service hiveserver2 & //beeline jdbc
检测是否启动（查看端口号）
netstat -antp | grep 9083
netstat -antp | grep 100000
结束进程
使用上述查看端口对应的进程的命令找出pid，然后使用kill -9 pid
*****设置成远程模式之后必须保证先启动metastore服务或者进程，之后才能进行bin/hive
日志分析【扩展练习-重要的是理解思路】
1) UV：Unique Visited--- count(distinct guid)
访问您网站的一台电脑客户端为一个访客。00:00-24:00内相同的客户端只被计算一次。
2) PV：Page View--- count(url)
即页面浏览量或点击量，用户每次刷新即被计算一次。
3) 登录人数：
登录网站访问的人数[会员]，endUserId有值的数量
4) 访客人数：
没有登录访问的人数，endUserId为空的数量
5) 平均访问时长：
访客平均在网站停留的时间
trackTime --> max - min
6) 二跳率： pv>1的访问量/总访问量
平均浏览2个页面及以上(pv>1)的用户数 / 用户总数
二跳率的概念是当网站页面展开后，用户在页面上产生的首次点击被称为“二跳”，二跳的次数即为“二跳量”。二跳量与浏览量的比值称为页面的二跳率。
7) 独立IP：---count(distinct ip)
独立IP表示，拥有特定唯一IP地址的计算机访问您的网站的次数，因为这种统计方式比较容易实现，具有较高的真实性，所以成为大多数机构衡量网站流量的重要指标。比如你是ADSL拨号上网的，你拨一次号都自动分配一个ip，这样你进入了本站，那就算一个ip，当你断线了而没清理cookies，之后又拨 了一次号，又自动分配到一个ip，你再进来了本站，那么又统计到一个ip，但是UV（独立访客)没有变，因为2次都是你进入了本站。
思路分析：
第一步：创建临时表1：session_info_tmp1
可根据sessionid和guid分组（同一个sessionid也肯定是同一个guid），获取以下字段信息：
sessionid：用户session号
guid：用户全局唯一id号
endUserId：会员id（登陆则有，不登录则是空）
使用max（endUserId）可以获取非空的字段值（非空字符串>空字符串）
pv：通过count（url）获取
stay_time：通过最大的tracktime-最小 的tracktime获得
min_trackTime：通过分组中最小tracktime获取
ip：同一个sessionId都应该有同一个ip，通过max（ip）获取
provinceId：同一个sessionId都应该有同一个provinceId，通过max（provinceId）获取
注：由于使用sessionId分组，trackerU、landing_url、landing_url_ref这三个字段都不一定是一个，所以在临时表1中不加入此三字段，然而这三个字段是会话信息表中要求实现的，所以创建临时表2
第二步：创建临时表2：session_info_tmp2
通过track_log获取所有sessionId、tracktime、trackerU、landing_url、landing_url_ref这五个字段，sessionId、tracktime这两个字段都是用来join的时候使用。
trackerU：渠道id
landing_url：首次登陆的url
landing_url_ref：首次登陆的渠道url（从哪个url而来）
第三步：join临时表1和临时表2，得到会话跟踪表session_info
由于在一次会话期间可能产生多个trackerU、landing_url、landing_url_ref的值，而我们实际需要的是这个session一开始的渠道id，在临时表1中按sessionId分组保留每个分组中最小的tracktime，关联临时表2中的tracktime，从而取出表2中每个分组中初始tracktime对应的trackerU、landing_url、landing_url_ref的值。
create database db_track;
create table track_log(
id string,
url string,
referer string, //外部链接
keyword string,
type string,
guid string,
pageId string,
moduleId string,
linkId string,
attachedInfo string,
sessionId string,
trackerU string,
trackerType string,
ip string,
trackerSrc string,
cookie string,
orderCode string,
trackTime string,
endUserId string,
firstLink string,
sessionViewNo string,
productId string,
curMerchantId string,
provinceId string,
cityId string,
fee string,
edmActivity string,
edmEmail string,
edmJobId string,
ieVersion string,
platform string,
internalKeyword string,
resultSum string,
currentPage string,
linkPosition string,
buttonPosition string
)
partitioned by (date string,hour string)
row format delimited fields terminated by ' ';
load data local inpath '/home/user01/Downloads/2015082818' into table track_log partition (date="20150828",hour="18");
load data local inpath '/home/user01/Downloads/2015082819' into table track_log partition (date="20150828",hour="19");
create table static_PVUV(
date string,
hour string,
pv int,
uv int
)
row format delimited fields terminated by ' ';
insert overwrite table static_PVUV select date,hour,count(url),count(distinct guid)from track_log where date="20150828" group by date,hour;
20150828 18 64972 23938
20150828 19 61162 22330
//在mysql中创建用于接收使用sqoop导出的表格
create table static_PVUV(
date int,
hour int,
pv int,
uv int
);
网盟
http://baike.baidu.com/link?url=3lgUc2Ti-dNlHHT2WpHLBoDPJM7GWzeJW9R564CMSZKJL-F7kn98dsj55JoyQTMizJcX6GMY83KI-M-CmTJQ1aMQ4V2pwgZpPrg4ExQz2k_
深入分析
1、借助上个案例创建的表
2、设计一张会话信息表
create table db_track.session_info(
session_id string, //会话id
guid string, //唯一用户id
trackerU string, //网盟用户id
landing_url string,
landing_url_ref string,
user_id string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
partitioned by (date string)
row format delimited fields terminated by " " ;
3、生成中间表1
--创建中间表1
create table session_info_tmp1(
session_id string,
guid string,
endUserId string,
pv string,
stay_time string,
min_trackTime string,
ip string,
provinceId string
)
row format delimited fields terminated by " " ;
--导入数据
insert overwrite table session_info_tmp1
select
sessionId,
max(guid),
max(endUserId),
count(url),
max(unix_timestamp(trackTime))-min(unix_timestamp(trackTime)),
min(trackTime),
max(ip),
max(provinceId)
from track_log where date='20150828'
group by sessionId ;
4、生成临时表2
create table session_info_tmp2(
session_id string,
trackTime string,
trackerU string,
landing_url string,
landing_url_ref string
)
row format delimited fields terminated by " " ;
--导入数据
insert overwrite table session_info_tmp2
select
sessionId,
trackTime,
trackerU,
url,
referer
from track_log where date='20150828' ;
5、得出会话信息表结果（join）
insert overwrite table session_info partition (date='20150828')
select
a.session_id ,
a.guid ,
b.trackerU ,
b.landing_url ,
b.landing_url_ref,
a.endUserId ,
a.pv ,
a.stay_time ,
a.min_trackTime ,
a.ip ,
a.provinceId
from session_info_tmp1 a join session_info_tmp2 b
on a.session_id=b.session_id and a.min_trackTime=b.trackTime ;
** 求出了每个session的数据
6、求出需求表结果
日期 UV PV 登录人数 游客人数 平均访问时长 二跳率 独立IP
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
二跳率
count(case when pv >=2 then guid else null end ) / count(guid)
*****************************************************************
create table visit_users (
date string,
uv string,
pv string,
login_users string,
visit_users string,
stay_time string,
perofsecvis string,
ip string
)
row format delimited fields terminated by " " ;
insert overwrite table visit_users select
date date,
count(distinct guid) uv,
sum(pv) pv,
count(case when user_id != '' then user_id else null end) login_users,
count(case when user_id = '' then user_id else null end) visit_users,
avg(stay_time) stay_time,
count(case when pv >=2 then guid else null end ) / count(guid) ,
max(ip) ip
from session_info where date='20150828'
group by date ;
Exec Result：
visit_users.date visit_users.uv visit_users.pv visit_users.login_users visit_users.visit_users visit_users.stay_time visit_users.perofsecvis visit_users.ip
20150828 23928 68036.0 12081 12721 50.10636239012983 0.522538504959277599.244.129.62
Exec Result：
visit_users.date visit_users.uv visit_users.pv visit_users.login_users visit_users.visit_users visit_users.stay_time visit_users.perofsecvis visit_users.ip
20150828 23928 68036.0 12081 12721 50.10636239012983 0.522538504959277599.244.129.62
7、导入mysql表
sqoop --> mysql
select sal,
case when comm is null then 0
else comm end
from emp ;
总结
mysql> create table visit_users(
date varchar(20),
uv int(12),
pv double,
login_users int,
visit_users int,
stay_time double,
perofsecvis double,
ip varchar(20)
);
bin/sqoop export
--connect jdbc:mysql://localhost.Hdfs-Cdh:3306/db_160729
--username root
--password root123
--table visit_users
--input-fields-terminated-by " "
--export-dir /user/hive/warehouse/db_track.db/visit_users