hive介绍

1.数仓概念

　　数据仓库(简称数仓，DW)是一个用于存储，分析，报告的数据系统

　　数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持

　　数据仓库本身并不“生产”任何数据，其数据来源于不同外部系统

　　同时数据仓库自身也不需要“消费”任何的数据，其结果开放给各个外部应用使用

　　这也是为什么叫“仓库”，而不叫“工厂”的原因

数据仓库为何而来，解决什么问题？

　　先下结论：为了分析数据而来，分析结果给企业决策提供支撑

　　联机事务处理系统(OLTP)主要任务是执行联机事务处理，其本质特征是前台接收的用户数据可以立即传送到后台进行处理，并在很短时间内给出处理结果

　　关系型数据库是OLTP的典型应用：比如oracle，mysql，sql server

OLTP环境开展分析可行嘛？

　　OLTP系统的核心是面向业务，支持业务，支持事务。所有的业务操作可以分为读，写两种操作，一般来说读的压力明显大于写的压力。如果在OLTP环境直接开展各种分析，需要考虑以下问题：
　　1）数据分析也是对数据进行读取操作，会让读取压力倍增

　　2）OLTP仅存储数周或数月的数据

　　3）数据分散在不同系统不同表中，字段类型属性不统一

数据仓库的搭建：

　　如数仓定义所说，数仓是一个用于存储，分析，报告的数据系统，目的是构建面向分析的集成化数据环境，我们把这种面向分析，支持分析的系统称之为OLAP(联机分析)系统，当然，数据仓库是OLAP系统的一种实现

 

2.数仓主要特征

　　面向主题：主题是一个抽象的概念，是较高层次上数据综合，归类并进行分析利用的抽象

　　集成性：主题相关的数据通常会分布在多个操作型系统中，彼此分散，独立，异构。需要集成到数仓主题下

　　非易失性：也叫非易变性，数据仓库是分析数据的平台，而不是创造数据的平台

　　时变性：数据仓库的数据需要随着时间更新，以适应决策的需要

　　1）面向主题性：

　　　　主题是一个抽象的概念，是较高层次上数据综合，归类并进行分析利用的抽象。在逻辑意义上，它是对应企业中某一宏观分析领域所涉及的分析对象

　　　　传统OLTP系统对数据的划分并不适用于决策分析，而基于主题组织的数据则不同，它们被划分为各自独立的领域，每个领域有各自的逻辑内涵但互不交叉，在抽象层次上对数据进行完整，一致和准确的描述

　　2）集成性

　　　　主题相关的数据通常会分布在多个操作型系统中，彼此分散，独立，异构

　　　　因此数据进入数据仓库之前，必然要经过统一与综合，对数据进行抽取，清理，转换和汇总，这一步是数据仓库建设中最关键，最复杂的一步，所要完成的工作有：

　　　　　　要统一源数据中所有矛盾之处，如字段的同名异义，单位不统一，字长不一致

　　　　　　进行数据综合和计算，数据仓库中的数据综合工作可以从原有数据库抽取数据时产生，但许多是在数据仓库内部生成的，即进入数据仓库 以后进行综合生成

　　3）非易失性，非易变性

　　　　数据仓库是分析数据的平台，而不是创造数据的平台。我们是通过数仓去分析数据中的规律，而不是去创造修改其中的规律，因此数据进入数据仓库中，它便稳定且不会改变。

　　　　数据仓库的数据反映的是一段相当长的时间内历史数据的内容。数据仓库的用户对数据的操作大多是数据查询或比较复杂的挖掘，一旦数据进入数据仓库中，一般情况下被较长时间保存

　　　　数据仓库中一般有大量的查询操作，但修改和删除的操作很少

　　4）时变性

　　　　数据仓库包含各种粒度的历史数据，数据可能与某个特定日期，星期，月份，季度或年份有关

　　　　当业务变化后会失去时效性，因此数据仓库的数据需要随着时间更新，以适应决策的需要

　　　　从这个角度讲，数据仓库建设是一个项目，更是一个过程

 

3.数据仓库主流开发语言sql

　　结构化查询语言简称sql，是一种数据库查询和程序设计语言，用于存取数据以及查询，更新和管理数据

　　结构化数据也称为行数据，是由二维表结构来逻辑表达和实现的数据，严格地遵循数据格式与长度规范，主要通过关系型数据库进行存储和管理

　　与结构化数据相对的是不适合由数据库二维表来表现的非结构化数据，包括所有的办公文档，XML,HTML，各类报表，图片和音频等

　　SQL主要语法分为两个部分：数据定义语言(DDL)和数据操作语言(DML)

　　　　DDL语法使我们有能力创建或删除表，以及数据库，索引等各种对象，但是不涉及表中具体数据操作：CREATE，DROP

　　　　DMl语法使我们有能力针对表中的数据进行插入，更新，删除，查询操作：SELECT，UPDATE，DELETE，INSERT

 

4.Apache hive

　　Apache hive 是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化，半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言(HQL)，用于访问并分析存储在Hadoop文件中的大型数据集

　　Hive的核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop集群执行

　　1）为什么使用Hive

　　使用Hadoop MapReduce直接处理数据所面临的问题：

　　　　人员学习成本太高，需要掌握JAVA语言

　　　　MapReduce实现复杂查询逻辑开发难度太大

　　使用Hive处理数据的好处

　　　　操作接口采用类sql语法，提供快速开发能力(简单，容易上手)

　　　　避免直接写MapReduce，减少开发人员的学习成本

　　　　支持自定义函数，功能扩展很方便

　　　　背靠Hadoop，擅长存储分析海量数据集

　　2）Hive与Hadoop关系

　　从功能来说，数据仓库软件，至少需要具备下述两种能力：存储数据的能力，分析数据的能力

　　Apache Hive作为一款大数据时代的数据仓库软件，当然也具备上述两种能力，只不过Hive并不是自己实现了上述两种能力，而是借助Hadoop。Hive利用 HDFS存储数据，利用MapReduce查询分析数据

　　这样发现Hive没啥用，不过是套壳Hadoop罢了。其实不然，Hive的最大的魅力在于用户专注于编写HQL,，Hive帮你转换成MapReduce程序完成对数据的分析

　　3）Hive功能模拟实现底层猜想

　　映射信息记录：
　　　　1.映射在数学上称之为一种对应关系，比如y=x+1，对于每一个x的值都有与之对应的y值

　　　　2.在hive中能够写sql处理的前提是针对表，而不是针对文件，因此需要将文件和表之间的对应关系描述记录清楚。映射信息专业 的说法称之为元数据信息(元数据是指用来描述数据的数据metadata)

　　　　3.具体来看，要记录的元数据信息包括：表对应着哪个文件(位置信息)，表的列对应着文件的哪个字段(顺序信息)，文件字段之间的分隔符是什么

　　sql语法解析，编译：

　　　　1.用户写完sql之后，hive需要对sql进行语法校验，并且根据记录的元数据信息解读sql背后的含义，制定执行计划

　　　　2.并且把执行计划换成MapReduce程序来具体执行，把执行的结果封装返回给用户

　　Hive能将数据文件映射成为一张表，这个映射是指什么？

　　　　映射指的是：文件和表之间的对应关系

　　Hive软件本身到底承担了什么功能职责？

　　　　SQL语法解析编译成为MapReduce

　　4）Apache hive架构图，各组件功能

　　　　1.用户接口：包括CLI,JDBC/ODBC,WebGUI。其中，CLI(command line interface)为shell命令行，Hive中的hrift服务器允许外来客户端通过网络与Hive进行交互，类似于JDBC或ODBC协议，WebGUI是通过浏览器访问Hive

　　　　2.元数据存储：通常是存储在关系数据库如mysql/derby中。Hive中的元数据包括表的名字，表的列和表的分区及其属性，表的属性(是否为外部表)，表的数据所在目录等

　　　　3.Driver驱动程序，包括语法解析器，计划编译器，优化器，执行器：完成HQL查询语句从词法分析，语法分析，编译，优化以及查询计划的生成。生成的查询计划存储在HDFS中，并在随后有执行引擎调用执行

　　　　4.执行引擎：Hive本身并不直接处理数据文件，而是通过执行引擎处理。当下Hive支持MapReduce，Tez，Spark三种执行引擎

　　5）Apache hive安装部署

　　　　1.元数据：又称为中介数据，中继数据，是描述数据的数据。主要是描述数据属性的信息，用来支持数据存储位置，历史数据，资源查找，文件记录等功能

　　　　2.Hive Metadata即Hive的元数据：包含用Hive创建的database，table，表的位置，类型，属性，字段顺序类型等元信息。元数据存储在关系型数据库中，如Hive内置的Derby，或者第三方如MySql等

　　　　3.Hivestore即元数据服务。Metastore服务的作用是管理metastore元数据 ，对外暴露服务地址，让各种客户端通过连接Metastore服务，由Metastore再去连接Mysql数据库来存取元数据。有了metastore服务，就可以有多个客户端同时连接，而且这些客户端不需要知道MySql数据库的用户名和密码，只需要连接metastore服务即可，某种程度上也保证了hive元数据的安全

　　　　4.metastore配置方式(本次使用企业推荐模式——远程模式部署)

　　　　　　metastore服务配置有3种模式：内嵌模式，本地模式，远程模式

　　　　　　区分3种配置方式的关键是弄清楚两个问题：

　　　　　　Metastore服务是否需要单独配置，单独启动？
　　　　　　Metastore是存储在内置的derby中，还是第三方RDBMS，比如Mysql

　　　　在生产模式中，建议用远程模式来配置Hive Metastore。在这种情况下，其它依赖hive的软件都可以通过Metastore访问hive。由于还可以完全屏蔽数据库层，因此这也带来了更好的可管理性/安全性

　　　　5.hive安装已完成

 

数据库操作报错：

1）FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. MetaException(message:Got exception:org.apache.hadoop.security.AccessControlException Permission denied: user=root, access=WRITE, inode="/data/${ip}/20180713":hadoop:supergroup:drwxr-xr-x

问题分析与解决：

根据报错信息是hdfs文件的权限问题，命令进入集群执行的用户为root，而hdfs文件所有者为hdfs。

要么以用户hdfs执行命令，要么调整hdfs文件的权限

解决方案1：

vim /etc/profile
增加内容：

export HADOOP_USER_NAME=hadoop

 解决方案2：

vim  HADOOP_HOME/etc/hadoop/hdfs-site.xml
增加内容:

<property>
<name>dfs.permissions.enabled</name>
<value>false</value>
<description>
If "true", enable permission checking in HDFS.
If "false", permission checking is turned off,
but all other behavior is unchanged.
Switching from one parameter value to the other does not change the mode,
owner or group of files or directories.
</description>
</property>

 

2）Hadoop本地操作HDFS不能上传文件，Couldn't find datanode to write file. Forbidden

问题原因
1、datanode没有启动

2、NameNode节点存放的是文件目录，也就是文件夹、文件名称，本地可以通过公网访问 NameNode，所以可以进行文件夹的创建，当上传文件需要写入数据到DataNode时，NameNode 和DataNode 是通过局域网进行通信，NameNode返回地址为 DataNode 的私有 IP，本地无法访问。

解决
1、此原因是namenode和datanode的clusterID不一致导致datanode无法启动
这个问题的原因可能是使用hadoop namenode -format格式化时格式化了多次造成那么spaceID不一致
（1）、停止集群（没有配环境变量切换到/sbin目录下）
./stop-dfs.sh

./stop-yarn.sh
（2）、删除在hdfs中配置的data目录（即在core-site.xml中配置的hadoop.tmp.dir对应文件件）下面的所有数据;
rm -rf /hadoop/data/tmp
（3）、重新格式化namenode
hdfs namenode -format 　　#只在namenode节点执行
（4）、重新启动hadoop集群（没有配环境变量切换到hadoop目录下的sbin目录下）
./start-dfs.sh　　# 本机在hadoop1机器

./start-yarn.sh　　# 本机在hadoop3机器
2、关闭所有机子的防火墙
systemctl stop firewalld.service 关闭防火墙
systemctl disable firewalld.service 永久关闭防火墙

 

 

 

 

5.Hive数据库操作

　1）数据库建库，切换库操作：

　　数据定义语言(DDL)，是sql语言集中对数据库内部的对象结构进行创建，删除，修改等操作语言，这些数据库对象包括database，table等

　　DDL核心语法由CREATE，ALTER与DROP三个所组成，DDL并不涉及表内部数据的操作

　　基于Hive的设计，使用特点，HQL中的create语法(尤其是create table)将是学习掌握hive ddl语法的重点，建表是否成功直接影响数据文件是否映射成功，进而影响后续是否可以基于sql分析数据　　

　　在hive中，默认的数据库叫做default，存储数据位置位于hdfs的/usr/hive/warehouse下

　　用户自己创建的数据库存储目录位置是/usr/hive/warehouse/database_name.db

 

Hive SQL之数据库与建库：

 　　创建数据库：create database 数据库名;　　

　　切换数据库：use 数据库名;

　　删除数据库：drop database 数据库名;　　# 默认行为是RESTRICT，这意味着仅在数据库为空时才删除它，反之使用CASCADE

 

Hive SQL之表与建表：

　　1.数据类型

　　Hive数据类型指的是表中列的字段类型

　　整体分为两类：原生数据类型(primitive data type)和复杂数据类型(complex data type)

　　最常用的数据类型是字符串string和数字类型int

 

　　2.分隔符指定语法

　　1）ROW FORMAT DELIMITED语法用于指定字段之间等相关的分隔符，这样hive才能正常读取解析数据；或者说只有分隔符指定正确，解析数据成功，我们才能在表中看到数据

-- 1.创建一张表，将射手结构化数据文件在HIVE中映射成功
-- 表名
-- 字段，名称，类型，顺序
-- 字段之间的分隔符需要指定
create table itheima.t_archer(
    id int comment "ID编号",
    name string comment "英雄名称",
    hp_max int comment "最大生命",
    mp_max int comment "最大法力",
    attack_max int comment "最高物攻",
    defense_max int comment "最大物防",
    attack_range string comment "攻击范围",
    role_main string comment "主要定位",
    role_assist string comment "次要定位"
)
row format delimited
fields terminated by "\t"; --字段之间的分隔符是tab键,制表符

　　2）LazySimpleSerDe是Hive默认的，包含4种子语法，分别用于指定字段之间，集合元素之间，map映射kv之间，换行的分隔符号

 　  3）Hive建表时如果没有row format语法指定分隔符，则采用默认分隔符；默认的分隔符是"\001"，是一种特殊的字符，使用的是ASCII编码的值，键盘是打不出来的

 

　　3.常用show语句

　　1）显示所有数据库

　　show databases;

　　show schemas;

　　2）显示当前数据库下所有的表

　　show tables;

　　show tables in 指定某个数据库;

　　3）查询显示一张表的元数据信息

　　desc formatted 表名;

 

　　4.注释comment中文乱码解决

　　注意：下面sql语句是需要在mysql中执行，修改hive存储的元数据信息(metadata)

　　use hive3;

　　show tables;

alter table hive3.COLUMNS_V2 modify column COMMENT varchar(256) character set utf8;

alter table hive3.TABLE_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

alter table hive3.PARTITION_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

alter table hive3.PARTITION_KEYS modify column PKEY_COMMENT varchar(4000) character set utf8;

alter table hive3.INDEX_PARAMS modify column PARAM_VALUE varchar(4000) character set utf8;

 

 

Hive SQL之DML操作：

　1.DML-LOAD数据加载操作

　　最原始暴力的方式是使用hadoop fs -put|mv 等方式直接将数据移动到表文件下，或者通过web端uplode操作

　　例如：hadoop fs -put 1.txt /user/hive/warehouse/itheima.db/t_1

　　LOAD语法功能：

　　所谓加载是指：将数据文件移动到与Hive表对应的位置，移动时是纯复制，移动操作

　　纯复制，移动指在数据load加载到表中时，hive不会对表中的数据内容进行任何转换，任何操作

　　LOAD语法规则：
　　LOAD DATA [LOCAL] INPATH "filepath" [OVERWRITE] INTO TABLE tablename;

　　注：本地文件系统指的是Hiveserver2服务所在机器的本地Linux文件系统，不是hive客户端所在的本地文件系统

纯复制操作：

-- 用于演示从本地加载数据
create table itheima.student_local(num int,name string,sex string,age int,dept string)
row format delimited fields terminated by ",";

-- 从本地加载数据，数据位于(hadoop1)本地文件系统，本质是hadoop fs -put 上传操作
load data local inpath '/home/hadoop/software/apache-hive-3.1.2-bin/data/student.txt'
into table student_local;

 纯移动操作：

-- 用于演示从HDFS加载数据
create table student_HDFS(num int,name string,sex string,age int,dept string)
row format delimited fields terminated by ",";

-- 先把数据上传到HDFS上，数据位于HDFS文件系统根目录下，本质是hadoop fs -mv 移动操作
load data inpath '/student.txt' into table student_hdfs;

　2.DML-INSERT数据插入操作

　　使用insert语法把数据插入到指定的表中，单独的insert语句效率太低，不推荐使用，最常用的配合是把查询返回的结果插入到另一张表中

　　insert+select：
　　表示将后面查询返回的结果作为内容插入到指定表中

　　1）需要保证查询结果列的数目和需要插入数据表格的列数目一致

　　2）如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但不一定转换成功，失败的数据将为null

　　insert into table tablename select statemet from from_statement;

　3.DML-SELECT数据查询操作

-- 1.select_expr
-- 查询所有字段或指定字段
select * from t_usa_covid19;
select country,cases,deaths from t_usa_covid19;
-- 查询常数返回，此时查询的结果和表中字段无关
select 1 from t_usa_covid19;
-- 查询当前数据库
select current_database();

-- 2.all,distinct
-- 返回所有匹配的行
select state from t_usa_covid19;
-- 相当于
select all state from t_usa_covid19;
-- 返回所有匹配的行，去除重复的结果
select distinct state from t_usa_covid19;
-- 多个字段distinct 整体去重
select distinct country,state from t_usa_covid19;

-- 3.where
-- where后面是一个布尔表达式(结果要么为true,要么为false)，用于查询过滤，当布尔表达式为true时，返回select后面expr表达式的结果，否则返回为空
-- 在where表达式中，可以使用hive支持的任何函数和运算符，但聚合函数除外
-- 布尔表达式
select * from emp where 1>2;
-- 比较运算,比较运算符：= > < <= >= != <>
select * from emp where ename='smith';
-- 逻辑运算
select * from emp where sal>2000 and sal<10000;
select * from emp where sal>2000 or comm<4000;
-- 特殊条件
-- 空值判断：is null
select * from emp where comm is null;
-- between and
select * from emp where sal between 1500 and 5000;
-- in
select * from emp where sal in (3000,5000,12000);

-- 4.聚合操作
-- sql中拥有很多可以用于计数和计算的内建函数，使用语法为：select function(列) from 表;
-- 聚合函数：count,sum,avg,max,min等，注意：count(column)返回某列的行数(不包括null值)，count(*)返回被选行数
-- 聚合函数的最大特点是不管原始数据有多少行记录，经过聚合操作只返回一条数据，这一条数据就是聚合的结果
-- 统计总共有多少个县
select country from t_usa_covid19;
-- 使用as给返回结果起别名
select count(country) as country_cnts from t_usa_covid19;
-- 去重distinct
select count(distinct country) as country_cnts from t_usa_covid19;
-- 统计上海有多少个县
select country(country) from t_usa_covid19 where state = '上海';
-- 统计总死亡病例数
select sum(deaths) from t_usa_covid19 where state = '上海';
-- 统计最高确诊病例数
select max(cases) from t_usa_covid19;

-- 5.group by
-- group by语句用于结合聚合函数，根据一个或多个列对结果集进行分组
-- 如果没有group by语法，则表中的所有行数据当成一组
-- 语法限制
-- 出现在group by中的select_expr的字段:要么是group by分组的字段，要么是被聚合函数应用的字段
-- 原因：避免出现一个字段多个值的含义
-- 1.分组字段出现select_expr中，一定没有歧义，因为就是基于该字段分组的，同一组中必相同
-- 2.被聚合函数应用的字段，也没歧义，因为聚合函数的本质就是多进一出，最终返回一个结果
-- 根据state进行分组，统计每个州有多少个县
select count(country) from t_usa_covid19 where count_date='2022-06-01' group by state;
-- 看下统计的结果属于哪个州
select state,count(country) from t_usa_covid19 where count_date='2022-06-01' group by state;
-- 再看一下每个州的死亡病例数
select state,count(country),sum(deaths) from t_usa_covid19 where count_date ='2022-06-01' group by state;

-- 6.having
-- 1.在sql中增加having子句原因是，where关键字无法与聚合函数一起使用。
-- 2.having子句可以让我们筛选分组后的各组数据，并且可以在having中使用聚合函数，因为where，group by已经执行结束，结果集已经确定
-- 统计2022-06-01死亡病例数大于10000
select state,sum(deaths) from t_usa_covid19 where count_date='2022-06-01' and sum(deaths) > 10000 group by state;
-- where 语句中不能使用聚合函数，语法报错
-- 先where分组前过滤，再进行group by分组，分组后每个分组结果集确定，再使用having过滤
select state,sum(deaths) from t_usa_covid19 where count_date='2022-06-01' group by state having sum(deaths) > 10000;
-- 为了提升查询性能，避免重复计算
select state,sum(deaths) as cnts from t_usa_covid19 where count_date ='2022-06-01' group by state having cnts > 10000;

-- 7.order by
-- order by语句用于根据指定的列对结果集进行排序
-- order by语句默认按照升序(asc)对记录进行排序，如果希望降序排序，则可以使用desc关键字
-- 不写排序规则，默认就是升序asc
select * from t_usa_covid19 where state = '上海' order by cases asc;
-- 降序desc
select * from t_usa_covid19 where state = '上海' order by cases desc;
-- 混合使用
select * from t_usa_covid19 where state='上海' order by cases desc ,deaths asc;

-- 8.LIMIT
-- 1.limit用于限制select语句返回的行数
-- 2.limit接受一个或两个数字参数，这两个参数都必须是非负整数常量
-- 3.第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数。当给出单个参数时，它代表最大行数，并且偏移量默认为0
-- 返回结果集的5条
select * from t_usa_covid19 where count_date='2022-06-01' and state='上海' limit = 5;
-- 返回结果集从第一行到第三行，注意第一个参数的偏移量是从0开始的
select * from t_usa_covid19 where count_date='2022-06-01' and state='上海' limit 2,3;

-- 9.执行顺序
-- 在查询过程中执行顺序：from>where>group by(含聚合)>having>order by>select
-- 1.聚合语句(sum,min,max,avg,count)
-- 2. where子句在查询过程中执行优先级先于聚合语句(sum,min,max,avg,count)
select state,sum(deaths) as cnts from t_usa_covid19 where count_date='2022-06-01' group by state having cnts > 10000 limit 2;-- 10.Hive SQL中join语法

-- 根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张大表把所有类型的数据都放到一起，而是不同类型的数据设计不同的表存储
-- 在这种情况下，有时需要基于多张表查询才能得到最终完整的结果
-- join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据
-- inner join 内连接
-- 内连接是最常见的一种连接，它被称为普通连接，其中inner可以省略：inner join = join
-- 只有进行连接的两个表都存在与连接条件相匹配的数据才会被保留下来
-- 1.inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id = e_a.id
-- 等价于inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id = e_a.id
-- 等价于隐式连接表示法
select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id = e_a.id
-- 2.left join
-- left join中文叫做左外连接(left outer join)或者左连接，其中outer可以省略，left outer join是早期写法
-- left join的核心在于left左，左指的是join关键字左边的表，简称左表
-- join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回
select e.id,e.name,e_a.city,e_a.street
from employee e left outer join employee_address e_a
on e.id = e_a.id
-- 等价于inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e left join employee_address e_a
on e.id = e_a.id



4.Hive函数的使用
-- 使用show functions查看当下可用的所有函数
-- 通过describe function extended funcname来查看函数的使用方式
show functions;
describe function extended count;
-- Hive函数分为两大类：内置函数(Built-in Function)，用户定义函数UDF(user-defined Function)
-- 内置函数可分为：数值类型函数，日期类型函数，字符串类型函数，集合函数，条件函数等
-- 用户定义函数根据输入输出的行数可分为3类：UDF,UDAF,UDTF
-- 根据函数输入输出的行数
-- UDF:普通函数，一进一出
-- UDAF：聚合函数，多进一出
-- UDTF：表生成函数，一进多出

-- Hive内置函数
-- 内置函数指的是Hive开发实现好，直接可以使用的函数，也叫内建函数
-- 1.字符串函数
select length('itheima');
select reverse('itheima');
select concat('angela','baby');
-- 带分隔符字符串连接函数，concat_ws(separator,[string | array(string)])
select concat_ws('.','www',array('itheima','cn'));
select concat_ws('.','www','itheima','cn');
-- 字符串截取函数：substr(str,pos[.len]) 或者 substring(str,pos[.len])
select substr('angelababy',-2);  -- pos是从1开始的索引，如果为负数则倒着数
select substr('angelababy',2,2);
-- 分隔字符串函数：split(str,regex)
-- split针对字符串数据进行切割，返回是数组array,可以通过数组的下标取内部的元素，注意下标从0开始
select split('apache hive',' ');
select split('apache hive',' ')[0];
select split('apache hive',' ')[1];

-- 2.日期函数
-- 获取当前日期：current_date
select current_date();
-- 获取当前unix时间戳函数：unix_timestamp
select unix_timestamp();
-- 日期转Unix时间戳函数：unix_timestamp
select unix_timestamp('2022-06-06 23:01:01');
-- 指定格式日期转unix时间戳函数：unix_timestamp
select unix_timestamp('20220606 23:01:01','yyyyMMdd HH:mm:ss');
-- unix时间戳转日期函数:from_unixtime
select from_unixtime(1654556461);
select from_unixtime(0,'yyyy-mm-dd HH:mm:ss');
-- 日期比较函数：datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
select datediff('2022-06-06','2021-11-11');
-- 日期增加函数：date_add
select date_add('2022-06-06',10);
-- 日期减少函数：date_sub
select date_sub('2022-06-06',10);

-- 3.数字函数
-- 取整函数：round 返回double类型的整数值部分(遵循四舍五入)
select round(3.1415926);
-- 指定精度取整函数:round(double a,int d) 返回指定精度d的double类型
select round(3.1415926,4);
-- 取随机数函数：rand 每次执行都不一样，返回一个0到1范围内的随机数
select rand();
-- 指定种子取随机数函数：rand(int seed) 得到一个稳定的随机数序列
select rand(3);

-- 4.条件函数
-- if条件判断：if(boolean testCondition,T valueTrue,T valueFalseOrNull)
select if(1=2,100,200);
select if(sex='男','M','W') from student limit 3;
-- 空值转换:nvl(T value,T default_value)
select nvl('allen','itheima');
select nvl(null,'itheima');
-- 条件转换函数：case a when b then c end
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end ;
select case sex when '男' then 'male' else 'female' end from student limit 3;

 

5. 基于hive数仓实现需求开发

-- 一.建库建表
-- 创建数据库已存在就删除
drop database if exists db_msg cascade;
-- 创建数据库
create database db_msg;
-- 切换数据库
use db_msg;
-- 列举数据库
show databases;

-- 创建表已存在就删除
drop table if exists db_msg.tb_msg_source;
-- 建表
create table db_msg.tb_msg_source(
    ...
)
-- 指定分隔符为制表符
row format delimited fields terminated by '\t';

-- 加载数据方式一：
-- HDFS上创建目录
hdfs dfs -mkdir -p /momo/data
-- 上传到HDFS
hdfs dfs -put /export/data/data1.tsv /momo/data/
hdfs dfs -put /export/data/data2.tsv /momo/data/

-- 加载数据方式二：
-- 上传数据文件到hadoop1服务器本地文件系统
-- shell：mkdir -p /root/hivedata
-- 加载数据到表中
load data local inpath '/root/hivedata/data1.tsv' into table db_msg.tb_msg_source;
load data local inpath '/root/hivedata/data2.tsv' into table db_msg.tb_msg_source;
-- 查询表，验证数据文件是否映射成功
select * from db_msg.tb_msg_source limit 10;
-- 统计行数
select count(*) from db_msg.tb_msg_source;

-- 二.ETL数据清洗
-- ETL数据清洗
-- 需求1：对字段为空的不合法数据进行过滤：where过滤
-- 需求2：通过时间字段构建天和小时：substr函数
-- 需求3：从GPS的经纬度中提取经度和纬度：split函数
select msg_time,substr(msg_time,1,10) as dayinfo,substr(msg_time,12,2) as hourinfo,
       sender_gpe,split(sender_gpe,',')[0] as sender_lng,split(sender_gpe,',')[1] as sender_lat
from tb_msg_source where length(sender_gpe) > 0 -- 过滤为空的非法的数据
limit 5;
-- 需求4：将etl以后的结果保存到另一张新的Hive表中：create table ... as select ...
-- CTAS 一步到位
create table t_test
as select msg_time,sender_name from tb_msg_source limit 5;

-- 创建新表保存处理后的数据
-- 如果表已存在则删除
drop table if exists db_msg.tb_msg_source_etl;
-- 将select语句的结果保存到新表中
create table db_msg.tb_msg_source_etl as
select
    *,
    substr(msg_time,1,10) as dayinfo,-- 获取天
    substr(msg_time,12,2) as hourinfo, -- 获取小时
    sender_gps,split(sender_gps,',')[0] as sender_lng, -- 获取经度
    split(sender_gps,',')[1] as sender_lat -- 获取纬度
from db_msg.tb_msg_source
-- 过滤字段为空的非法的数据
where length(sender_gps) > 0;
-- 验证etl的结果
select
    msg_time,dayinfo,hourinfo,sender_gps,sender_lng,sender_lat
from tb_msg_source_etl limit 10;


-- sql编写思路与指标计算
-- 1.正确解读业务需求，避免歧义
-- 2.确定待查询的数据表---from 表
-- 3.找出分析的维度---group by 分组的字段
-- 4.找出计算的指标---聚合的字段
-- 5.其它细节(过滤，排序等)

-- 需求：统计今日总消息量
create table if not exists tb_rs_total_msg_cnt
comment "今日消息总量"
as
select
    dayinfo,
    count(*) as total_msg_cnt
from db_msg.tb_msg_source_etl
group by dayinfo;

select * from tb_rs_total_msg_cnt;-- 结果验证


-- 需求：统计今日每小时消息量，发送和接收用户量
create table if not exists tb_rs_hour_msg_cnt
comment "每小时消息量趋势"
as
select
    dayinfo,
    hourinfo,
    count(*) as total_msg_cnt,
    count(distinct sender_account) as sender_usr_cnt,
    count(distinct receiver_account) as receiver_usr_cnt
from db_msg.tb_msg_source_etl
group by dayinfo, hourinfo;

select * from tb_rs_hour_msg_cnt;-- 结果验证

-- 需求：统计今日各地区消息量
create table if not exists tb_rs_loc_cnt
comment "今日各地区发消息量"
as
select
    dayinfo,
    sender_gps,
    cast(sender_lng as double) as longitude,
    cast(sender_lat as double) as latitude,
    count(*) as total_msg_cnt
from tb_msg_source_etl
group by dayinfo,sender_gps,longitude,latitude;

select * from tb_rs_loc_cnt;

-- 需求：统计今日发送消息和接收消息的用户数
create table if not exists tb_rs_usr_cnt
comment "今日发送消息人数，接受消息人数"
as
select
    dayinfo,
    count(distinct sender_account) as sender_usr_cnt,
    count(distinct receiver_account) as receiver_usr_cnt
from tb_msg_source_etl
group by dayinfo;

select * from tb_rs_usr_cnt;


-- 需求：统计今天发送消息最多的top10用户
create table if not exists tb_rs_usr_top10
comment "发送消息条数最多的Top10用户"
as
select
    dayinfo,
    sender_name as username,
    count(*) as sender_msg_cnt
from tb_msg_source_etl
group by dayinfo, sender_name
order by sender_msg_cnt
limit 10;

select * from tb_rs_usr_top10;


-- 需求：统计发送人的操作系统分布
create table if not exists tb_rs_sender_os
comment "发送人的os分布"
as
select
    dayinfo,
    sender_os,
    count(distinct sender_account) as cnt
from tb_msg_source_etl
group by dayinfo, sender_os;

select * from tb_rs_sender_os;