HIVEDefault 数据仓库的最原始位置是在 hdfs 上的：/user/hive/warehouse 路径下。 2

适合大数据离线分析，延迟高 sql转化成MapReduce

JAVA_HOME HADOOP_HOME HIVE_HOME 都需要配置

sudo vim /etc/profile source /etc/profile

字段是有分隔符的默认是 ^A 可以自定义分隔符 create table person2 (id int,name varchar(20)) row format delimited fields terminated by '\t';

这样在制定目录下上传文件，也会被hive识别例如

1	sunyj
2	liuh

默认元数据是存储在derby上的

存放mysql中的配置

从文件系统加载数据

首先先准备数据：建立一个文本文件字段用 tab键分割

在HIVE上创建一张表指定分隔符是\t 和上面的文本文件相对应

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

执行从本地文件加载数据命令

load data local inpath '/opt/install/hive/1.txt' into table student；

执行 select * from student 发现数据已经加载到hive中的table中

由上图发现其实数据是存在Hadoop的指定路径下所以也可以通过Hadoop命令将指定结构的文件上传到该路径下也可以被hive识别

hadoop fs -put 1.txt /user/hive/warehouse/student

将元数据保存在mysql中

1 需要安装mysql服务

2.将mysql的驱动放到hive的lib文件夹下因为需要链接mysql数据库

cp mysql-connector-java-5.1.27-bin.jar /opt/install/hive/lib/

3.修改hive的配置信息了在hive的conf目录下创建hive-site.xml

touch hive-site.xml

配置需要链接的数据库地址驱动类用户名密码

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://hadoop102:3306/metastore?createDatabaseIfNotExist=true</value>
	  <description>JDBC connect string for a JDBC metastore</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionDriverName</name>
	  <value>com.mysql.jdbc.Driver</value>
	  <description>Driver class name for a JDBC metastore</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionUserName</name>
	  <value>root</value>
	  <description>username to use against metastore database</description>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionPassword</name>
	  <value>000000</value>
	  <description>password to use against metastore database</description>
	</property>
</configuration>

表属于哪个数据源

元数据说明：

库的信息存在在dbs表中！其中DB_LOCATION_URI指当前库在HDFS上指定的位置！

表的信息存在在tbls表中，通过DB_ID字段指定当前表所属的库，和dbs存在外键关联！

字段信息存储在columns_v2表中！

常用交互命令

bin/hive -e "select id from student;" 不进入hive中执行sql

bin/hive -f /opt/module/datas/hivef.sql 执行文件中的sql查询

数据类型

1 基本数据类型

集合数据类型

开窗函数相关概念

rank dense_rank rownumber

dense rank 密集排序如果重复会1 1 2 3

rank 如果重复不会密集 1 1 3 4

rownuber 就完全不会重复 1 2 3 4

自定义函数 udf udtf

hive基本概念

facebook开源交给apache 处理海量结构化数据数据分析基于hadoop 数据仓库工具

数据存在hdfs---将hdfs上的数据映射为一张表----提供类sql查询分析

本质转化成mapreduce运行

hive操作

hive_sunyj是库名，用文件夹标识数据库
student是表名，一个库下的表用不同的文件夹标识
student表中的数据是一个一个文件
可以通过
load data local inpath '/opt/install/data/student.txt' into table student;
将数据加载到指定的表中

也可以直接通过hdfs命令，将数据文件上传到该目录中，也会被认为是这个表中的数据
hadoop fs -put /opt/install/data/student.txt /hive_sunyj/student/

也可以使用load将hdfs的数据加载到hive中，不加local找的就是hdfs的路径
可以将本地数据上传到hdfs中，然后在从hdfs中加载到hive数仓中进行分析了

常用命令

“-e”不进入 hive 的交互窗口执行 sql 语句

  ./bin/hive -e "select id from student;"

“-f”执行脚本中 sql 语句

./bin/hive -f /opt/install/data/hivef.sql
vim hive.sql
   use hive_sunyj;
   load data local inpath '/opt/install/data/student.txt' into table student;
   select * from student;

常用的属性配置

1 Hive数据仓库的位置配置

Default 数据仓库的最原始位置是在 hdfs 上的：/user/hive/warehouse 路径下。

2）在仓库目录下，没有对默认的数据库 default 创建文件夹。如果某张表属于 default 数据库，直接在数据仓库目录下创建一个文件夹。

3）修改 default 数据仓库原始位置（将 hive-default.xml.template 如下配置信息拷贝到 hive-site.xml 文件中）。

<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>/user/hive/warehouse</value>
  <description>location of default database for the warehouse</description>
</property>

hive运行日志信息配置

Hive 的 log 默认存放在/tmp/sunyj/hive.log 目录下（当前用户名下）

在hive-log4j.properties.template中把运行日志配置到这个目录下

修改 hive 的 log 存放日志到/opt/install/hive/logs （1）修改/opt/install/hive/conf/hive-log4j.properties.template 文件名称为 hive-log4j.properties

   mv hive-log4j.properties.template hive-log4j.properties

（2）在 hive-log4j.properties 文件中修改 log 存放位置

hive.log.dir=/opt/module/hive/log

重启启动hive 就会在对应的路径中产生log文件

查询后信息显示配置

在 hive-site.xml 文件中添加如下配置信息，就可以实现显示当前数据库，以及查询表的头信息配置

<property>
  <name>hive.cli.print.header</name>
  <value>true</value>
</property>
<property>
   <name>hive.cli.print.current.db</name>
   <value>true</value>
</property>

查询的头和当前的数据库可以显示

hive类型数据

基本数据类型

集合数据类型

集合类型实战：

我们通过json来类比，理解下这三种集合结构

{
 "name": "songsong",
 "friends": ["bingbing" , "lili"] ,    //列表 Array, 
 "children": {                         //键值 Map,
       "xiao song": 18 ,
       "xiaoxiao song": 19
 }
 "address": {                          //结构 Struct,
         "street": "hui long guan" ,
           "city": "beijing" 
 } }

Hive 上创建测试表 test

create table test(
    name string,
    friends array<string>,
    children map<string, int>,
    address struct<street:string, city:string>
)
row format delimited 
fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by '\n';

字段解释： row format delimited -- 指定分隔符

fields terminated by ',' -- 列分隔符

collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)

map keys terminated by ':' -- MAP 中的 key 与 value 的分隔符

lines terminated by '\n'; -- 行分隔符

创建本地测试文件 test.txt

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long 
guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao 
yang_beijing

将本地数据导入到hive仓库中

load data local inpath "/opt/install/data/test.txt" into table test;

访问表中的数据

如果想指定访问某个元素

select friends[1],children['xiao song'],address.city from test
where name="songsong";

查询结果

也就是说array的访问元素使用[下标] map的访问元素使用[key] struct访问元素使用.key

数据类型转换

隐式类型转换规则如下

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT。

（2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。

（3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。

（4）BOOLEAN 类型不可以转换为任何其它的类型。

CAST 操作显示进行数据类型转换

例如 CAST('1' AS INT)将把字符串'1' 转换成整数 1；如果强制类型转换失败，如执行 CAST('X' AS INT)，表达式返回空值 NULL。

****ddl语句

创建数据库

  create database scm;

创建一个数据库，数据库在 HDFS 上的默认存储路径是/user/hive/warehouse/*.db

避免要创建的数据库已经存在错误，增加 if not exists 判断

hive (default)> create database db_hive;
FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask. Database db_hive already 
exists
hive (default)> create database if not exists db_hive;

创建一个数据库，指定数据库在 HDFS 上存放的位置

create database db_hive2 location '/db_hive2';

查询数据库

 show databases;
 show databases like 'db_hive*';
 desc database db_hive;  // 详情
 desc database extended db_hive;
 use db_hive; 切换数据库

删除数据库

--删除空数据库
hive>drop database db_hive2;

--如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在
hive> drop database db_hive;
FAILED: SemanticException [Error 10072]: Database does not exist: 
db_hive
hive> drop database if exists db_hive2;


3．如果数据库不为空，可以采用 cascade 命令，强制删除
hive> drop database db_hive;
FAILED: Execution Error, return code 1 from 
org.apache.hadoop.hive.ql.exec.DDLTask. 
InvalidOperationException(message:Database db_hive is not empty. 
One or more tables exist.)
hive> drop database db_hive cascade;

内部表和外部表

删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据

默认创建的表都是所谓的管理表，有时也被称为内部表

Hive 默认情况下会将这些表的数据存储在由配置项 hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive 也会删除这个表中数据

hive中有两种数据：元数据和真实数据  ，每次查询某张表的数据时，先去mysql元数据库中去查询这张表在
hdfs中的路径，然后区查询hdfs对应路径下保存的真实数据，即表数据

管理表：删除表的时候，元数据和真实数据同时删除
外部表：只会删除元数据，对应hdfs的路径下表数据还没有删除
因为表是外部表，所以 Hive 并非认为其完全拥有这份数据

示例操作

创建部门表外部表

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表

create external table if not exists default.emp(
empno int,
ename string,
job string,
mgr int,
hiredate string, 
sal double, 
comm double,
deptno int)
row format delimited fields terminated by '\t';

创建结果：

向外部表中导入数据

load data local inpath 
'/opt/install/data/dept.txt' into table default.dept;

load data local inpath 
'/opt/install/data/emp.txt' into table default.emp;

然后查询时候导入成功
select * from dept;

此时如果删除dept表 drop table dept;

由于是外部表所以只是把元数据删除了，但是表数据在hdfs中依旧存在，只是hive不知道映射关系了

如果我在重新执行一遍创建表的语句，那么会在mysql中重新添加元数据映射关系，这张表会指向原来hdfs的目录，所以，不会是空表，而是原来表中的数据

create external table if not exists default.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

管理表与外部表的互相转换

alter table student2 set tblproperties('EXTERNAL'='TRUE');
alter table student2 set tblproperties('EXTERNAL'='FALSE');
注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

desc formatted student2; 查询表的相关信息

***分区表

Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多

示例操作

创建一张分区表，需要指定分区字段

create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (month string)
row format delimited fields terminated by '\t';

加载数据到分区表中,加载数据就需要制定分区

hive (default)> load data local inpath 
'/opt/install/data/dept.txt' into table default.dept_partition 
partition(month='201709');
hive (default)> load data local inpath 
'/opt/install/data/dept.txt' into table default.dept_partition 
partition(month='201708');
hive (default)> load data local inpath 
'/opt/install/data/dept.txt' into table default.dept_partition 
partition(month='201707’);

通过上图就可以理解分区表就是将数据分在不同的文件夹，而且发现分区字段也会作为表中的字段存在，

select * from dept_partition  --全查询
select * from dept_partition where month='201709'; --单分区查询

多分区联合查询--
 select * from dept_partition where month='201709'
 union
 select * from dept_partition where month='201708'
 union
 select * from dept_partition where month='201707';

分区对应的元数据表是partition表,所以为什么查询指定分区的数据会快，因为hive先去元数据中找到这个分区对应的文件夹名，然后直接取获取这个文件夹的数据，不用全表扫描了

新增和删除分区

增加分区创建单个分区

 alter table dept_partition add partition(month='201706') ;

同时创建多个分区

 alter table dept_partition add partition(month='201705') partition(month='201704');

6．删除分区删除单个分区

alter table dept_partition drop partition (month='201704');

同时删除多个分区

 alter table dept_partition drop partition (month='201705'), partition (month='201706');

新增分区用空格分隔删除多个用逗号分隔

二级分区

创建二级分区表
hive (default)> create table dept_partition2(
 deptno int, dname string, loc string
 )
 partitioned by (month string, day string)
 row format delimited fields terminated by '\t';
 
 加载数据到分区表中
 load data local inpath 
'/opt/install/data/dept.txt' into table
default.dept_partition2 partition(month='201709', day='13');

查询分区数据
select * from dept_partition2 where 
month='201709' and day='13';

二级分区是分层级的目录

思考

首先在hdfs上创建一个目录

hdfs dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201711/day=11

然后在这个目录上传数据

hdfs dfs -put /opt/install/data/dept.txt /user/hive/warehouse/dept_partition2/month=201711/day=11

请问 select * from dept_partition2 where month='201711' and day='11'; 能否查到数据

不能：原因在于虽然存在这个目录，但是hive在元数据中并没有相关信息，所以无法通过元数据和真实数据目录建立联系，

解决方式1：手动添加分区这条命令会在元数据分区表中增加一条元数据信息

alter table dept_partition2 add partition(month='201711', day='11');

这样在查询上述sql时发现where过滤条件是分区字段，就直接到对应的目录中查找数据

解决方式2：修复分区，查询不到的原因就是元数据和真实数据映射没有都存在，需要保证元数据和真实数据都存在，即创建分区时，既要把文件夹路径创建出来，还要在元数据库中把元数据创建出来

 msck repair table dept_partition2; --修复dept_partition2分区表的分区，就是缺元数据的补上

修改表

重命名

alter table dept_partition2 rename to dept_partition3;

添加列

 alter table dept_partition add columns(deptdesc string);

修改列

alter table dept_partition change column deptdesc desc int;

替换列替换之后新表的列就是新设置的列，也就是这个语句是把表原来的列全部替换

alter table dept_partition replace 
columns(deptno string, dname
string, loc string);

数据导入

上传文件到 HDFS

hdfs dfs -put /opt/install/data/student.txt /user/sunyj/hive;

将数据从本地或者hdfs中导入

本地local
load data local inpath '/opt/install/data/student.txt' into table default.student;

hdfs 去掉local
load data inpath '/user/sunyj/hive/student.txt' into table default.student;

覆盖 overwrite into
load data inpath '/user/atguigu/hive/student.txt' overwrite into table 
default.student;

插入数据

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

--基本插入
insert into table student 
partition(month='201709') values(1,'wangwu');


--根据单张表查询结果
insert overwrite table student 
partition(month='201708')
 select id, name from student where month='201709';

根据查询结果创建表（查询的结果会添加到新创建的表中）

create table if not exists student3
as select id, name from student;

创建表时通过 Location 指定加载数据路径

1．创建表，并指定在 hdfs 上的位置
 create table if not exists student5(
 id int, name string
 )
 row format delimited fields terminated by '\t'
 location '/user/hive/warehouse/student5';
 
2．上传数据到 hdfs 上 hive (default)> dfs -put /opt/module/datas/student.txt
/user/hive/warehouse/student5;

3．查询数据
hive (default)> select * from student5;

数据导出

Insert 导出

1．将查询的结果导出到本地
insert overwrite local directory 
'/opt/module/datas/export/student'
 select * from student;
 
2．将查询的结果格式化导出到本地
insert overwrite local directory 
'/opt/module/datas/export/student1'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

3．将查询的结果导出到 HDFS 上(没有 local)
 insert overwrite directory 
'/user/atguigu/student2'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
 select * from student;

清空表中数据

注意：Truncate 只能删除管理表，不能删除外部表中数据
hive (default)> truncate table student;

查询

起别名紧跟列名，也可以在列名和别名之间加入关键字‘AS’

查询名称和部门
 select ename AS name, deptno dn from emp;

排序

order by /sort by/ distribute by/ cluster by;

order by:全局排序一个reducer任务 sort by ：reduce内部排序，设置reducer的个数为多个的时候

设置 reduce 个数
hive (default)> set mapreduce.job.reduces=3;

查看设置 reduce 个数
hive (default)> set mapreduce.job.reduces;

根据部门编号降序查看员工信息
hive (default)> select * from emp sort by empno desc;

将查询结果导入到文件中（按照部门编号降序排序）

hive (default)> insert overwrite local directory 
'/opt/install/data/sortby-result'
select * from emp sort by deptno desc;

总结：由于设置了reduce的个数为3 那么如果将查询结果导入到文件中一定是3个结果文件，因为每个reduce任务会处理一部分数据，那么哪条数据进入哪一个reducer任务？即查询出来的每条数据进入哪一个分区，由于我们没有指定分区规则，所以数据会随机的分配到不同的reduce中，我们可以通过distribute by 指定分区字段

Distribute By：类似 MR 中 partition，进行分区，结合 sort by 使用。
注意，Hive 要求 DISTRIBUTE BY 语句要写在 SORT BY 语句之前。
对于 distribute by 进行测试，一定要分配多 reduce 进行处理，否则无法看到 distribute by
的效果

先按照部门编号分区，再按照员工编号降序排序。

hive (default)>  set mapreduce.job.reduces=3;
hive (default)> insert overwrite local directory 
'/opt/install/data/distribute-result' select * from emp 
distribute by deptno sort by empno desc;



当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。

cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。
但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。 

1）以下两种写法等价
hive (default)> select * from emp cluster by deptno;
hive (default)> select * from emp distribute by deptno sort by 
deptno;

注意：按照部门编号分区，不一定就是固定死的数值，可以是 20 号和 30 号部门分到一
个分区里面去。

分桶表不是重点了解即可

创建分桶表
create table stu_buck(id int, name string)
clustered by(id) into 4 buckets
row format delimited fields terminated by '\t';

分桶的实质就是对分桶的字段做了hash 然后存放到对应文件中

也就是说向分桶表中插入数据的时候必然要执行一次MAPREDUCE

这也就是分桶表的数据基本只能通过从结果集查询插入的方式进行导入

数据分桶的原理

   跟MR中的HashPartitioner的原理一模一样

   MR中：按照key的hash值去模除以reductTask的个数

   Hive中：按照分桶字段的hash值去模除以分桶的个数

   Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

将数据插入分桶表

第一步：

   从hdfs或本地磁盘中load数据，导入中间表

第二步：

   通过从中间表查询的方式的完成数据导入

（1）先建一个普通的 stu 表 中间表
create table stu(id int, name string)
row format delimited fields terminated by '\t';
（2）向普通的 stu 表中导入数据 导入中间表
load data local inpath '/opt/module/datas/student.txt' into 
table stu;

（3）清空 stu_buck 表中数据
truncate table stu_buck;
select * from stu_buck;
（4）导入数据到分桶表，通过子查询的方式
insert into table stu_buck
select id, name from stu;

注意：执行前需要设置hive的参数

hive (default)> set hive.enforce.bucketing=true;
hive (default)> set mapreduce.job.reduces=-1;
hive (default)> insert into table stu_buck
select id, name from stu;

hive在执行的过程中就会根据分桶的个数，来决定开启reduce任务的个数，将数据写进不同的桶中

分桶抽样查询表 stu_buck 中的数据。

hive (default)> select * from stu_buck tablesample(bucket 1 out of 4 on id);

注：tablesample 是抽样语句，语法：TABLESAMPLE(BUCKET x OUT OF y) 。

y 必须是 table 总 bucket 数的倍数或者因子。hive 根据 y 的大小，决定抽样的比例。例如，table 总共分了 4 份，当 y=2 时，抽取(4/2=)2 个 bucket 的数据，当 y=8 时，抽取(4/8=)1/2 个 bucket 的数据。

x 表示从哪个 bucket 开始抽取，如果需要取多个分区，以后的分区号为当前分区号加上y。例如，table 总 bucket 数为 4，tablesample(bucket 1 out of 2)，表示总共抽取（4/2=）2 个bucket 的数据，抽取第 1(x)个和第 3(x+y)个 bucket 的数据。

注意：x 的值必须小于等于 y 的值，否则 FAILED: SemanticException [Error 10061]: Numerator should not be bigger than denominator in sample clause for table stu_buck

常用函数

nvl

如果员工的 comm 为 NULL，则用-1 代替
select nvl(comm,-1) from emp;

时间类

1）date_format:格式化时间

select date_format('2019-06-29','yyyy-MM-dd');
select date_format('2019-06-29','yyyy-MM');

2）date_add:时间跟天数相加

 select date_add('2019-06-29',5);
  select date_add('2019-06-29',-5);

3）date_sub:时间跟天数相减

  select date_sub('2019-06-29',5);
  select date_sub('2019-06-29',-5);
  select date_sub('2019-06-29 12:12:12',5); --结果只会得到年月日 不会有时分秒

4）datediff:两个时间相减

hive (default)> select datediff('2019-06-29','2019-06-24');
OK
_c0
5

hive (default)> select datediff('2019-06-24','2019-06-29');
OK
_c0
-5


hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29');
OK
_c0
-5

hive (default)> select datediff('2019-06-24 12:12:12','2019-06-29 
13:13:13');
OK
_c0
-5

case when

求出不同部门男女各多少人

select 
 dept_id,
 sum(case sex when '男' then 1 else 0 end) male_count,
 sum(case sex when '女' then 1 else 0 end) female_count
from 
 emp_sex
group by
 dept_id;

行转列

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)： 1.如果分隔符是 NULL，返回值也将为 NULL。

2.这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。聚合函数

示例操作

数据准备

name constellation blood_type
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

把星座和血型一样的人归类到一起。结果如下：

射手座,A 大海|凤姐
白羊座,A 孙悟空|猪八戒
白羊座,B 宋宋

创建本地 constellation.txt，导入数据 [atguigu@hadoop102 datas]$ vi constellation.txt

孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A

创建 hive 表并导入数据

create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

load data local inpath "/opt/module/data/person_info.txt" into table person_info;

按需求查询数据

select
 t1.base,
 concat_ws('|', collect_set(t1.name)) name
from
 (select
 name,
 concat(constellation, ",", blood_type) base
 from
 person_info) t1
group by
 t1.base;

列转行

EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

数据准备

《疑犯追踪》    悬疑,动作,科幻,剧情
《Lie to me》   悬疑,警匪,动作,心理,剧情
《战狼2》       战争,动作,灾难

创建 hive 表并导入数据

create table movie_info(
 movie string,
 category array<string>)
row format delimited fields terminated by "\t"
collection items terminated by ",";

load data local inpath "/opt/install/data/movie.txt" into table 
movie_info;

按需求查询数据

select
 movie,
 category_name
from
 movie_info lateral view explode(category) table_tmp as 
category_name;

select
 category_name,
 count(1)
from
 movie_info lateral view explode(category) table_tmp as 
category_name
group by  category_name;

select
 category_name,
 concat_ws('|', collect_set(movie)) movie_names
from
 movie_info lateral view explode(category) table_tmp as 
category_name
group by  category_name;

窗口函数重点*****

1.over（）开窗，就是取一个范围的数据集窗口的大小就是数据集的多少，针对每条记录都会开窗，窗口开多大（这个窗口里的数据怎么取）由over括号的内容决定

2.针对每一行都有一个窗口

distribute by 决定了窗口的最大范围就是：相同name的记录，为什么说是针对每一行开一个窗口呢，因为每条记录的name有可能不同比如第一条的name可能为tom，那么会把name=tom的数据化为一个窗口中，当下一条数据name可能为jack，那么name=jack的数据化为一个窗口中

sort by，将窗口范围内的数据进行排序。加入 sort 之后，会导致窗口大小是动态的在累加的需求时候会用到sort by

两组语法都ok：

窗口可以通过上面的语句指定范围

partition by order by语法给出了一个从分区首行到当前行的窗口，如果业务需要对窗口进行细粒度划分，则需要使用窗口函数

窗口函数中可以指定窗口大小，下表展示了一个商品从5月1日开始到6月1日的销售情况

unbounded preceding指分区的上限——分区的第一行
1 preceding指当前行的上一行
1 following指当前行的下一行
unbounded following指分区的下限——分区的最后一行

业务场景统计每种商品近7天的销售数量思路：采用一个6 preceding到current row的窗口进行业务统计

select series_code,
sales_date,
sales_num,
sum(sales_num) over (partition by series_code order by sales_date rows between 6 preceding and current row) as cum_sales
from lab.code_sales_detail

上述的HQL实现了一种滑动窗口的效果，也就是从分区的顶部开了一个7个元素的窗口在以步长=1的方式往下滑动求和

Tip：以下两个窗口的语义相同

-- 窗口1
over (partition by series_code order by sales_date
rows between unbounded preceding and current row)
-- 窗口2
over (partition by series_code order by sales_date)

补充：sql执行顺序

窗口函数练习

set hive.exec.mode.local.auto=true; 先调成本地模式 加快

准备数据 name，orderdate，cost

jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

创建 hive 表并导入数据

create table business(
name string,
orderdate string,
cost int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

load data local inpath "/opt/install/data/business.txt" into table 
business;
校验是否导入成功
select * from business;

（1）查询在 2017 年 4 月份购买过的顾客及总人数

  --查询4月份的明细
  select * from business where date_format(orderdate,'yyyy-MM')='2017-04';
  
  --按照顾客名进行分组
  select name,
  count(*) over() as total_person
  from business where date_format(orderdate,'yyyy-MM')='2017-04'
  group by name;

（2）查询顾客的购买明细及月购买总额

select name,
orderdate,
cost,
sum(cost) over (partition by name,date_format(orderdate,'MM')) as total_month
from  business;

HIVE

从文件系统加载数据

执行从本地文件加载数据命令

将元数据保存在mysql中

常用交互命令

数据类型

开窗函数相关概念

自定义函数 udf udtf

hive基本概念

hive操作

常用命令

常用的属性配置

1 Hive数据仓库的位置配置

hive运行日志信息配置

查询后信息显示配置

hive类型数据

基本数据类型

集合数据类型

集合类型实战：

数据类型转换

隐式类型转换规则如下

CAST 操作显示进行数据类型转换

****ddl语句

创建数据库

查询数据库

删除数据库

内部表和外部表

示例操作

***分区表

示例操作

新增和删除分区

二级分区

思考

修改表

数据导入

数据导出

Insert 导出

清空表中数据

查询

排序

分桶表 不是重点了解即可

将数据插入分桶表

分桶抽样查询表 stu_buck 中的数据。

常用函数

nvl

时间类

case when

行转列

示例操作

列转行

窗口函数 重点*****

窗口函数练习

分桶表不是重点了解即可

窗口函数重点*****