Hive 大数据开发（一套打通）

Hive数据分析

1、Hive基本概念

1.1、Hive简介

1.1.1、什么是Hive

Hive由Facebook实现并开源，基于Hadoop的一个数据仓库工具，可以将结构化的数据映射为一张数据库表，并提供HQL(Hive SQL)查询功能，底层数据是存储在HDFS上。Hive的本质是将SQL语句转换为 MapReduce任务运行，使不熟悉MapReduce的用户很方便地利用HQL处理和计算HDFS上的结构化的数据，适用于离线的批量数据计算。

 

Hive依赖于HDFS存储数据，Hive将HQL转换成MapReduce执行

所以说Hive是基于hadoop的一个数据仓库工具，实质就是一款基于HDFS的MapReduce计算框架

1.1.2、为什么使用Hive

直接使用MapReduce所面临的问题：

         人员学习成本太高

         项目周期要求太短

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

 

为什么要使用Hive：

         更友好的接口：操作接口采用类SQL的语法，提供快速开发的能力

         更低的学习成本：避免了写MapReduce，减少开发人员的学习成本

         更好的扩展性：可自由扩展集群规模而无需重启服务，还支持用户自定义函数

1.1.3、Hive特点

优点：

1、可扩展性,横向扩展，Hive可以自由的扩展集群的规模，一般情况下不需要重启服务

横向扩展：通过分担压力的方式扩展集群的规模

纵向扩展：一台服务器cpu i7-6700k 4核心8线程，8核心16线程，内存64G => 128G

2、延展性，Hive支持自定义函数，用户可以根据自己的需求来实现自己的函数

3、良好的容错性，可以保障即使有节点出现问题，SQL语句仍可完成执行

 

缺点：

         1、hive不支持记录级别的增删改操作，但是用户可以通过查询生成新表或者将查询结果导入到文件中（当前选择的hive-1.2.1的版本支持记录级别的插入操作）

2、Hive的查询延时很严重，因为MapReduce Job的启动过程消耗很长时间，所以不能用在交互查询系统中。

3、hive不支持事务（因为不没有增删改，所以主要用来做OLAP（联机分析处理），而不是OLTP（联机事务处理），这就是数据处理的两大级别）。

 

 

1.2、Hive架构

 

基本组成

用户接口：

         CLI，Shell终端命令行，最常用（学习，调试，生产）

         JDBC/ODBC，是Hive的基于JDBC操作提供的客户端，用户（开发员，运维人员）通过这连接至Hive server

         Web UI，通过浏览器访问Hive

 

元数据存储：

         元数据，通俗的讲，就是存储在Hive中的数据的描述信息。

 

Hive中的元数据通常包括：表的名字，表的列和分区及其属性，表的属性（内部表和外部表），表的数据所在目录

 

         Metastore默认存在自带的Derby数据库中。缺点就是不适合多用户操作，并且数据存储目录不固定。数据库跟着Hive走，极度不方便管理

 

         解决方案：通常存我们自己创建的MySQL库（本地 或 远程）

 

解释器，编译器，优化器，执行器

         这四大组件完成HQL查询语句从词法分析，语法分析，编译，优化，以及生成查询计划的生成。生成的查询计划存储在HDFS中，并随后由MapReduce调用执行

 

执行流程

         HiveQL通过命令行或者客户端提交，经过Compiler编译器，运用Metastore中的元数据进行类型检测和语法分析，生成一个逻辑方案(logical plan)，然后通过的优化处理，产生一个MapReduce任务。

1.3、Hive和RDBMS的对比

对比项

Hive

RDBMS

查询语言

HQL

SQL

数据存储

HDFS

Raw Device or Local FS

执行器

MapReduce

Executor

数据插入

支持批量导入/单条插入

支持单条或者批量导入

数据操作

覆盖追加

行级更新删除

处理数据规模

大

小

执行延迟

高

低

分区

支持

支持

索引

0.8版本之后加入简单索引

支持复杂的索引

扩展性

高（好）

有限（差）

数据加载模式

读时模式（快）

写时模式（慢）

应用场景

海量数据查询

实时查询

总结：Hive具有SQL数据库的外表，但应用场景完全不同，Hive只适合用来做海量离线数据统计分析，也就是数据仓库。

1.4、Hive的数据存储

1、Hive的存储结构包括数据库、表、视图、分区和表数据等。数据库，表，分区等等都对应HDFS上的一个目录。表数据对应HDFS对应目录下的文件。

 

2、Hive中所有的数据都存储在 HDFS 中，没有专门的数据存储格式（可支持TextFile，SequenceFile，RCFILE或者自定义格式等）

 

3、  只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据

 

4、Hive中包含以下数据模型：

database：在hdfs中表现为${hive.metastore.warehouse.dir}目录下一个文件夹

table：在hdfs中表现所属database目录下一个文件夹

external table：与table类似，不过其数据存放位置可以指定任意HDFS目录路径

partition：在hdfs中表现为table目录下的子目录

bucket：在hdfs中表现为同一个表目录或者分区目录下根据hash散列之后的多个文件

view：与传统数据库类似，只读，基于基本表创建

 

5、Hive的元数据存储在RDBMS中，除元数据外的其它所有数据都基于HDFS存储。默认情况下，Hive 元数据保存在内嵌的 Derby 数据库中，只能允许一个会话连接，只适合简单的测试。实际生产环境中不适用，为了支持多用户会话，则需要一个独立的元数据库，使用 MySQL 作为元数据库，Hive 内部对 MySQL 提供了很好的支持。

 

6、Hive中的表分为内部表、外部表、分区表和Bucket表。

2、Hive基本使用

1、  创建库：create database mydb;

2、  查看库：show databases;

3、  切换数据库：use mydb;

4、  创建表：create table t_user(id string, name string)

或create table t_user2 (id string, name string) row format delimited fields terminated by ',';

5、  插入数据：insert into table t_user values('001','mazhonghua');

6、  查询数据：select * from t_user;

7、  导入数据（后面会说动静态分区）：

a)       导入HDFS数据： load data inpath '/mingxing.txt' into table t_user1;

b)       导入本地数据：load data local inpath '/root/mingxing.txt' into table t_user1;

 

小技能补充：

1、  进入到用户的主目录，使用命令cat /home/hadoop/.hivehistory可以查看到hive执行的历史命令

 

2、  执行查询时若想显示表头信息时，请执行命令：

Hive> set hive.cli.print.header=true;

 

3、  hive的执行日志的存储目录在${java.io.tmpdir}/${user.name}/hive.log中，假如使用hadoop用户操作的hive，那么日志文件的存储路径为：/temp/hadoop/hive.log

 

3、DDL操作

3.1、库操作

3.1.1、创建库

语法结构：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION hdfs_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

　　

创建库的使用方式：

1、  创建普通库

create database dbname;

2、  创建库的时候检查存与否

create database if not exists dbname;

3、  创建库的时候带注释

create database if not exists dbname comment 'create my db named dbname';

4、  创建带属性的库

create database if not exists dbname with dbproperties ('a'='aaa','b'='bbb');

3.1.2、查看库

、查看有哪些数据库

         show  databases;

2、显示数据库的详细属性信息

         desc  database  [extended]  dbname;

3.1.3、删除库

PS：库里面如果有表的时候是不能删除的，但是用drop的时候一定要注意到底要不要删除

删除库操作：

         drop database dbname;

         drop database if exists dbname;

默认情况下，hive不允许删除包含表的数据库，有两种解决办法：

1、  手动删除库下所有表，然后删除库

2、  使用cascade关键字强制删除所有内容

drop database if exists dbname cascade;

删除数据库 默认行为是RESTRICT，这意味着仅在数据库为空时才删除它。 要删除带有表的数据库（不为空的数据库），我们可以使用CASCADE

3.2、表操作

3.2.1、创建表

1、  建表语句

语法结构：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

   [(col_name data_type [COMMENT col_comment], ...)]

   [COMMENT table_comment]

   [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

   [CLUSTERED BY (col_name, col_name, ...)

   [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

   [ROW FORMAT row_format]  

   [STORED AS file_format]

         [LOCATION hdfs_path]

详情请参见官方网站：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTable

 

2、  建表语句相关解释

CREATE TABLE：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

 

EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（经典面试问题）

 

PARTITIONED BY(分区)在Hive Select查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入partition概念。个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下，分区是以字段的形式在表结构中存在，通过desc table命令（其实就是describe<描述> 可以简写为desc）可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。

 

分区建表分为2种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式

 

LIKE：允许用户复制现有的表结构，但是不复制数据。

示例：create table tableA like tableB（创建一张tableA空表复制tableB的结构）

 

COMMENT（字段描述）：可以为表与字段增加描述

 

ROW FORMAT（行格式）

DELIMITED [FIELDS TERMINATED BY char]

[COLLECTION ITEMS TERMINATED BY char]

          [MAP KEYS TERMINATED BY char]

 [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

 

ROW FORMAT DELIMITED（行格式分隔）语法用于指定字段之间等相关的分隔符，这样Hive才能正确的读取解析数据。用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。

 

SerDe相关语法

- ROW FORMAT这一行所代表的是跟读写文件、序列化SerDe相关的语法，功能有二：
- 使用哪个SerDe类进行序列化；
- 如何指定分隔符。

 

- 其中ROW FORMAT是语法关键字，DELIMITED和SERDE二选其一。

- 如果使用delimited表示使用默认的LazySimpleSerDe类来处理数据。
- 如果数据文件格式比较特殊可以使用ROW FORMAT SERDE serde_name指定其他的Serde类来处理数据,甚至支持用户自定义SerDe类。

 

 

 

LazySimpleSerDe分隔符指定

- LazySimpleSerDe是Hive默认的序列化类，包含4种子语法，分别用于指定字段之间、集合元素之间、map映射 kv之间、换行的分隔符号。
- 在建表的时候可以根据数据的特点灵活搭配使用。

 

 

 

STORED AS（存储为） TEXTFILE | SEQUENCEFILE | RCFILE

如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

A、默认格式，数据不做压缩，磁盘开销大，数据解析开销大。 可结合Gzip、Bzip2使用(系统自动检查，执行查询时自动解压)，但使用这种方式，hive不会对数据进行切分， 从而无法对数据进行并行操作。

 

B、SequenceFile是Hadoop API提供的一种二进制文件支持，文件内容是以序列化的kv对象来组织的，其具有使用方便、可分割、可压缩的特点。 SequenceFile支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。

 

C、RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。相比TEXTFILE和SEQUENCEFILE，RCFILE由于列式存储方式，数据加载时性能消耗较大，但是具有较好的压缩比和查询响应。数据仓库的特点是一次写入、多次读取，因此，整体来看，RCFILE相比其余两种格式具有较明显的优势。

 

CLUSTERED BY（分桶）

对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。

把表（或者分区）组织成桶（Bucket）有两个理由：

（1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

（2）使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

 

 

         LOCATION：指定数据文件存放的hdfs目录

 

3、  Hive建表示例

CREATE TABLE page_view

     (viewTime INT, userid BIGINT, page_url STRING, referrer_url STRING, ip STRING COMMENT 'IP Address of the User')

     COMMENT 'This is the page view table'

     PARTITIONED BY(dt STRING, country STRING)

     CLUSTERED BY(userid) SORTED BY(viewTime) INTO 31 BUCKETS

     ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\t'

     COLLECTION ITEMS TERMINATED BY '\t'

MAP KEYS TERMINATED BY '\t'

LINES TERMINATED BY '\n'

STORED AS TEXTFILE

LOCATION '/myhive'

 

执行命令查看表结构：hive> desc formatted page_view;  

PS： 建表的时候建议使用notepad++复制打开，文件打开单纯复制有可能读不来

# col_name              data_type               comment            

viewtime                int                                        

userid                  bigint                                     

page_url                string                                      

referrer_url             string                                     

ip                     string                  IP Address of the User

                

# Partition Information         

# col_name              data_type               comment            

                

dt                      string                                     

country                 string                                     

                

# Detailed Table Information            

Database:               mytest                  

Owner:                 hadoop                  

CreateTime:             Tue Jan 10 20:15:15 CST 2017    

LastAccessTime:         UNKNOWN                 

Protect Mode:           None                    

Retention:              0                       

Location:               hdfs://hadoop02:9000/myhive    

Table Type:             MANAGED_TABLE           

Table Parameters:               

        comment                 This is the page view table

        transient_lastDdlTime       1484050515         

                

# Storage Information           

SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe      

InputFormat:          org.apache.hadoop.mapred.TextInputFormat        

OutputFormat:        org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat      

Compressed:             No                      

Num Buckets:            31                      

Bucket Columns:         [userid]                

Sort Columns:           [Order(col:viewtime, order:1)]  

Storage Desc Params:            

        colelction.delim        \t                 

        field.delim             \t   

line.delim              \n             

        mapkey.delim          \t                 

            serialization.format      \t 

Hive使用一个Inputformat对象将输入流分割成记录；使用一个Outputformat对象将记录格式化为输出流，使用序列化/反序列化器SerDe做记录的解析（记录和列的转换）。

它们的默认值分别是：

Inputformat：org.apache.hadoop.mapred.TextInputFormat

Outputformat：org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat

SerDe：org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe

 

4、  具体实例

a、  创建内部表

create table mytable (id int, name string)

row format delimited fields terminated by ',' stored as textfile;   

（创建一个表 以 ',' 分隔开来，储存格式是textfile）

b、  创建外部表

create external table mytable2 (id int, name string)

row format delimited fields terminated by ','

location '/user/hive/warehouse/mytable2';

（创建一个表 以 ',' 分隔开来，位置放在.....）

c、  创建分区表

create table mytable3(id int, name string) partitioned by(sex string)

row format delimited fields terminated by ',' stored as textfile;

插入数据

插入男分区数据：load data local inpath '/root/hivedata/mingxing.txt' overwrite into table mytable3 partition(sex='boy');

插入女分区数据：load data local inpath '/root/hivedata/mingxing.txt' overwrite into table mytable3 partition(sex='girl');

查询表分区：

show partitions mytable3

d，创建分桶表

create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)

clustered by(Sno) sorted by(Sno DESC) into 4 buckets

row format delimited fields terminated by ',';

（创建一个表，根据Sno分桶，倒序Sno分组在第四桶。行格式分隔的字段以“，”结尾）

e、  使用like关键字拷贝表

// 不管老表是内部表还是外部表，新表new_table都是内部表

create table new_table like mytable;

// 不管老表是内部表还是外部表，新表new_table都是外部表

create external table if not exists new_table like mytable;

3.2.2、修改表

1、  重命名表

语法结构：ALTER TABLE table_name RENAME TO new_table_name

示例：

2、  增加/删除/改变/替换列

语法结构：

ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])

ALTER TABLE name CHANGE c_name new_name new_type [FIRST|AFTER c_name]

ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

（注意：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。）

 

ADD示例：

 

 

 

 

3、  增加/删除分区

增加分区语法结构：

ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...

 

partition_spec

: PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

 

删除分区语法结构：

ALTER TABLE table_name DROP partition_spec, partition_spec,...

 

添加分区示例：

ALTER TABLE student_p ADD partition(part='a') partition(part='b');

 

ALTER TABLE student_p ADD if not exists

partition(part='bb') location '/myhive_bb' partition(part='cc') location '/myhive_cc';

 

修改分区路径示例：

ALTER TABLE student_p partition (part='bb') SET location '/myhive_bbbbb';

 

删除分区示例：

ALTER TABLE student_p DROP if exists partition(part='aa');

 

最后补充：

1、  防止分区被删除：alter table student_p partition (part='aa') enable no_drop;

2、  防止分区被查询：alter table student_p partition (part='aa') enable offline;

enable和disable是反向操作

 

3.2.3、删除表

语法结构：

DROP TABLE [IF EXISTS] table_name;

命令：drop table if exists mytable;

3.3、其他辅助命令

show databases;

show databases like 'my*';

查看数据库列表

show tables;

show tables in db_name;

查看数据表

show create table table_name;

查看数据表的建表语句

show functions;

查看hive函数列表

show partitions table_name;

show partitions table_name partition(city='beijing')

查看hive表的分区

desc table_name;

desc extended table_name;

desc formatted table_name;

查看表的详细信息（元数据信息）

desc database db_name;

desc database extended db_name;

查看数据库的详细属性信息

truncate table table_name;

清空数据表

 

4、DML操作

4.1、Load装载数据  

语法结构：

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

说明：

1、  Load 操作只是单纯的复制（local）/移动（）操作，将数据文件移动到 Hive 表对应的位置。

2、  local关键字

如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。

如果没有指定 LOCAL 关键字，则根据inpath中的uri 查找文件

注意：uri是指hdfs上的路径，分简单模式和完整模式两种，例如：

简单模式：/user/hive/project/data1

完整模式：hdfs://namenode_host:9000/user/hive/project/data1

3、  filepath：

相对路径，例如：project/data1

绝对路径，例如：/user/hive/project/data1

包含模式的完整 URI，列如：hdfs:/localhost:9000/user/hive/project/data1

注意：inpath子句中的文件路径下，不能再有文件夹。

4、  overwrite关键字（覆盖并重写）

如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代.

具体实例：

1、  加载本地相对路径数据

load data local impath '....' into table student partition(...);

2、  加载绝对路径数据

load data local inpath '/root/.../...' into table student partition(...);

3、  加载包含模式数据

load data local impath 'hdfs:/localhost:9000/user/hive/....' ; 

4、  Overwrite关键字使用

load data local inpath  '...'  overwriter into table student partition(...); 

4.2、Insert插入数据

PS:

说一下需要注意的事项：
INSERT OVERWRITE LOCAL DIRECTORY ‘路径’ select * from datas;
该语句是将datas中的数据导出到本地的路径中。
INSERT OVERWRITE LOCAL DIRECTORY ‘路径’ ROW FORMAT DELIMITED FIELDS TERMINATED by ‘,’ select * from datas;
该语句是将datas中的数据以逗号“,”为分隔符导出到本地路径中。
INSERT OVERWRITE DIRECTORY ‘路径’ select * from datas;
该语句是将datas中的数据导入到hdfs文件系统中，在这个里面注意，某些博客中会加入ROW FORMAT DELIMITED FIELDS TERMINATED by ','语句表明会以逗号为分隔符写入hdfs中，但实际上，写入hdfs中加入该语句并不能对数据进行分割，反而会报错，请不要被某些博客误导，本人就看到了很多博客理所当然的使用这个语句，造成错误。

语法结构：

1. 插入一条数据

INSERT INTO TABLE table_name VALUES(...,...);

2.用查询语句将结果导入新表

INSERT OVERWRITE [INTO] TABLE table_name [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement

 

示例：

from mingxing

insert into table mingxing2 select id,name,sex,age

insert into table mingxing select id,name,sex ,age,department ;

从mingxing表中，按不同的字段进行查询得的结果分别插入不同的hive表

 

from studentss  

insert into table student_ptn partition(city='sa') select id,name,sex,age,department where department='MA'

insert into table student_ptn partition(city='sb') select id,name,sex,age,department;

4、分区插入

分区插入有两种，一种是静态分区，另一种是动态分区。如果混合使用静态分区和动态分区，则静态分区必须出现在动态分区之前。现分别介绍这两种分区插入。

 

静态分区：

A)、创建静态分区表

B)、从查询结果中导入数据

C)、查看插入结果

 

动态分区：

静态分区需要创建非常多的分区，那么用户就需要写非常多的SQL！Hive提供了一个动态分区功能，其可以基于查询参数推断出需要创建的分区名称。

A)、创建分区表，和创建静态分区表是一样的

B)、参数设置

hive> set hive.exec.dynamic.partition=true;

hive> set hive.exec.dynamic.partition.mode=nonstrict;

注意：动态分区默认情况下是没有开启的。开启后，默认是以”严格“模式执行的，在这种模式下要求至少有一列分区字段是静态的。这有助于阻止因设计错误导致查询产生大量的分区。但是此处我们不需要静态分区字段，估将其设为nonstrict。

对应还有一些参数可设置：

set hive.exec.max.dynamic.partitions.pernode=100;    //每个节点生成动态分区最大个数

set hive.exec.max.dynamic.partitions=1000;          //生成动态分区最大个数，如果自动分区数大于这个参数，将会报错

set hive.exec.max.created.files＝150000;   //一个任务最多可以创建的文件数目

set dfs.datanode.max.xcievers=4096;      //限定一次最多打开的文件数

set hive.error.on.empty.partition=false;    //表示当有空分区产生时，是否抛出异常

         小技能补充：如果以上参数被更改过，想还原，请使用reset命令执行一次即可    

C)、动态数据插入

insert into table test2 partition (age) select name,address,school,age from students;

insert into table student_ptn2 partition(city='sa',zipcode) select id, name, sex, age, department, department as zipcode from studentss;

注意：查询语句select查询出来的age字段必须放在最后，和分区字段对应，不然结果会出错

 

D)、查看插入结果

select * from student_ptn2 where city=’sa’ and zipcode=’MA’;

 

5、CTAS（create table … as select …）

在实际情况中，表的输出结果可能太多，不适于显示在控制台上，这时候，将Hive的查询输出结果直接存在一个新的表中是非常方便的，我们称这种情况为CTAS

 

展示：CREATE TABLE mytest AS SELECT name, age FROM test;

 

注意：

1. CTAS操作是原子的，因此如果select查询由于某种原因而失败，新表是不会创建的！

2. 原子操作：所谓原子操作是指不会被线程调度机制打断的操作，当某次操作一旦开始，就一直运行到结束，中间不会有任何中断。

CAS原子操作_马哈鱼SQLFlow的博客-CSDN博客_cas原子操作

4.4、Select查询数据

Hive中的SELECT基础语法和标准SQL语法基本一致，支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等；

1、select * from db.table1

2、select count(distinct uid) from db.table1

3、支持select、union all、join（left、right、full join）、like、where、having、各种聚合函数、支持json解析

4、UDF（User Defined Function）/ UDAF/UDTF

5、不支持update和delete

6、hive虽然支持in/exists（老版本是不支持的），但是hive推荐使用semi join 的方式来代替实现，而且效率更高。

7、支持case … when …

 

语法结构：

SELECT [ALL | DISTINCT] select_ condition, select_ condition, ...

FROM table_name a

[JOIN table_other b ON a.id = b.id]

[WHERE where_condition]

[GROUP BY col_list [HAVING condition]]

[CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list | ORDER BY col_list] ]

[LIMIT number]

 

说明：

1、  select_ condition查询字段

2、  table_name 表名

3、  order by(字段) 全局排序，因此只有一个reducer，只有一个reduce task的结果，比如文件名是000000_0，会导致当输入规模较大时，需要较长的计算时间。

4、  sort by(字段) 局部排序，不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

那万一，我要对我的所有处理结果进行一个综合排序，而且数据量又非常大，那么怎么解决？我们不适用order by进行全数据排序，我们适用sort by对数据进行局部排序，完了之后，再对所有的局部排序结果做一个归并排序

5、  distribute by(字段) 根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

6、  cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。

 

因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

如果我们要分桶的字段和要排序的字段不一样，那么我们就不能使用clustered by

分桶表的作用：最大的作用是用来提高join操作的效率；

（思考这个问题：select a.id,a.name,b.addr from a join b on a.id = b.id;如果a表和b表已经是分桶表，而且分桶的字段是id字段做这个join操作时，还需要全表做笛卡尔积吗？）

 

具体实例：

1、  获取年龄大的三个学生

select id, age,name from student where stat_date= '20140101' order by age desc limit 3;

 

2、  查询学生年龄按降序排序

Set mapred.reduce.tasks=4;

Select id, age, name from student sort by age desc;

 

Select id, age, name from student order by age desc;

 

Select id, age, name from student distribute by age;

 

这是分桶和排序的组合操作，对id进行分桶，对age，id进行降序排序

insert overwrite directory '/root/outputdata6' select * from mingxing2 distribute by id sort by age desc, id desc;

这是分桶操作，根据id分桶，但是不进行排序

insert overwrtiter directory '/root/outputdata4'  selecet * form mingxing2 distribute by id sort by age;

这是分桶操作，按照id分桶，并且按照id排序

insert overwriter directory'/root/outputdata3' select * form mingxing2 cluster by id; 

分桶查询：

指定开启分桶：

set hive.enforce.bucketing =true;

指定reducetask数量，也就是指定桶的数量

set mapreduce.job.reudce=4;

insert overwriter directory '/root/outputdata3' select * from mingxing2 cluster by id  

 

3. 按学生名称汇总学生年龄

select name,sum(age) from student group by name;

 

CLUSTER、 DISTRIBUTE、SORT、ORDER BY

1.order by全局排序，因此只有一个reducer，结果输出在一个文件中，当输入规模大时，需要较长的计算时间（尽量最好是少用，这个用在数据少的情况是比较好的，如果数据量大的话一个mapreduce是不可能跑完特别多的数据的！！！！会出现报错信息！！）。

2.Sort By：对于大规模的数据集 order by 的效率非常低。在很多情况下，并不需要全局排序，此时可以使用 sort by。Sort by 在每个 Reducer 内部进行排序，即使每个 reduce 内部是有序的，但是对于全局结果集 来说也还是乱序的。

          sort by 按什么规则分区：

  1. 使用 sort by 时，通常会跟 distribute by 字段一起连用，通过 distribute by 来指定分区规则。

  2. 当不指定分区规则时(只使用sort by )，则使用默认的内部算法进行分区

3.distribute by根据指定字段将数据分组，算法是hash散列。sort by是在分组之后，每个组内局部排序。

通常是先用 Distribute By 指定分区规则，然后再使用 Sort By 对分区内数据排序。

 

 

 

4.cluster by：当 distribute by 和 sorts by 字段相同时，可以使用 cluster by 方式。cluster by 除了具有 distribute by 的功能外还兼具 sort by 的功能。但是排序只能是升序排序，不能指定排序规则为 ASC 或者 DESC。Cluster by 不常用的。

 

 

如果distribute和sort的字段是同一个时，此时，cluster by = distribute by + sort by

 

 

 Hive中order by，sort by，distribute by，cluster by的区别_lzm1340458776的博客-CSDN博客_sortby和orderby的区别

Sort By、Distribute By 使用说明书_扛麻袋的少年的博客-CSDN博客（看这个强推）

 

总结：

一、解释三个执行参数

In order to change the average load for a reducer (in bytes):

set hive.exec.reducers.bytes.per.reducer=<number>

In order to limit the maximum number of reducers:

set hive.exec.reducers.max=<number>

In order to set a constant number of reducers:

  set mapreduce.job.reduces=<number>

 

 

1、直接使用不带设置值得时候是可以查看到这个参数的默认值：

set hive.exec.reducers.bytes.per.reducer

hive.exec.reducers.bytes.per.reducer：一个hive，就相当于一次hive查询中，每一个reduce任务它处理的平均数据量

如果要改变值，我们使用这种方式：

set hive.exec.reducers.bytes.per.reducer=51200000

 

2、查看设置的最大reducetask数量

set hive.exec.reducers.max

hive.exec.reducers.max：一次hive查询中，最多使用的reduce task的数量

我们可以这样使用去改变这个值：

set hive.exec.reducers.max = 20

 

3、查看设置的一个reducetask常量数量

set mapreduce.job.reduces

mapreduce.job.reduces：我们设置的reducetask数量

 

         二、HQL是否被转换成MR的问题

前面说过，HQL语句会被转换成MapReduce程序执行，但是上面的例子可以看出部分HQL语句并不会转换成MapReduce，那么什么情况下可以避免转换呢？

1、select * from student;      // 简单读取表中文件数据时不会

2、where过滤条件中只是分区字段时不会转换成MapReduce

3、set hive.exec.mode.local.auto=true;      // hive会尝试使用本地模式执行

否则，其他情况都会被转换成MapReduce程序执行

 

4.5、Hive Join查询

 join table:

     table reference JOIN table_factor [join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition

| table_reference LEFT SEMI JOIN table_reference join_condition

Hive 支持等值连接（equality join）、外连接（outer join）和（left/right join）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。

另外，Hive 支持多于 2 个表的连接。

PS:

1、  只支持等值链接，支持and，不支持or

例如：

SELECT a.* FROM a JOIN b ON (a.id = b.id)

SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)

是正确的；

然而：SELECT a.* FROM a JOIN b ON (a.id>b.id)是错误的。

 

2、  可以join多于2个表

例如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

 

如果join中多个表的 join key 是同一个，则 join 会被转化为单个 map/reduce 任务，例如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

被转化为单个 map/reduce 任务，因为 join 中只使用了 b.key1 作为 join key。

 

例如：SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1)  JOIN c ON (c.key = b.key2)

而这一 join 被转化为 2 个 map/reduce 任务。因为 b.key1 用于第一次 join 条件，而 b.key2 用于第二次 join。

 

3、  Join时，每次 map/reduce 任务的逻辑

reducer 会缓存 join 序列中除了最后一个表的所有表的记录，再通过最后一个表将结果序列化到文件系统。这一实现有助于在 reduce 端减少内存的使用量。实践中，应该把最大的那个表写在最后（否则会因为缓存浪费大量内存）。例如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)

所有表都使用同一个 join key（使用 1 次 map/reduce 任务计算）。Reduce 端会缓存 a 表和 b 表的记录，然后每次取得一个 c 表的记录就计算一次 join 结果，类似的还有：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

这里用了 2 次 map/reduce 任务：

第一次缓存 a 表，用 b 表序列化；

第二次缓存第一次 map/reduce 任务的结果，然后用 c 表序列化。

4、  HiveJoin分三种：inner join, outer join, semi join

其中：outer join包括left join，right join 和 full outer join,主要用来处理 join 中空记录的情况

1、  创建两张表：

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

create table tableb (id int, age int) row format delimited fields terminated by ',';

2、  准备数据：

先准备两份数据，例如

 

3、  分别导入数据a.txt到tablea表，b.txt到tableb表

4、  数据准备完毕

load data local inpath '/home/hadoop/a.txt' into table tablea;

load data local inpath '/home/hadoop/b.txt' into table tableb;

5、  Join演示

a)、inner join（内连接）（把符合两边连接条件的数据查询出来）

select * from tablea a inner join tableb b on a.id=b.id;

 

b)、left join（左连接，等同于left outer join）

1、以左表数据为匹配标准，左大右小

2、匹配不上的就是null

3、返回的数据条数与左表相同

HQL语句：select * from tablea a left join tableb b  on a.id =b.id

c)、right join（右连接，等同于right outer join）

1、以右表数据为匹配标准，左小右大

2、匹配不上的就是null

3、返回的数据条数与右表相同

HQL语句：select * from tablea a  right join tableb b  on a.id=b.id;

 

e)、left semi join（左半连接）（因为hive不支持in/exists操作（1.2.1版本的hive支持in的操作），所以用该操作实现，并且是in/exists的高效实现）

HSQ: select * from tableb a left semi join tableb b on a.id=b.id;

f)、full outer join（完全外链接）

select * from tablea a full outer join tableb b on a.id=b.id;

5、Hive数据类型

5.1、原子数据类型

1、Hive支持日期类型(老版本不支持)，在Hive里日期一般都是用字符串来表示的，而常用的日期格式转化操作则是通过自定义函数进行操作，当然也可以直接指定为日期类型

2、Hive是用Java开发的，Hive里的基本数据类型和java的基本数据类型也是一一对应的，除了String类型。

3、有符号的整数类型：TINYINT、SMALLINT、INT和BIGINT分别等价于Java的Byte、Short、Int和Long原子类型，它们分别为1字节、2字节、4字节和8字节有符号整数。

4、Hive的浮点数据类型FLOAT和DOUBLE,对应于Java的基本类型Float和Double类型。

5、Hive的BOOLEAN类型相当于Java的基本数据类型Boolean。

6、Hive的String类型相当于数据库的Varchar类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储2GB的字符数。

 

5.2、复杂数据类型

复杂数据类型包括数组（ARRAY）、映射（MAP）和结构体（STRUCT），具体如下所示：

 

说明：

ARRAY：ARRAY类型是由一系列相同数据类型的元素组成，这些元素可以通过下标来访问。比如有一个ARRAY类型的变量fruits，它是由['apple','orange','mango']组成，那么我们可以通过fruits[1]来访问元素orange，因为ARRAY类型的下标是从0开始的　　

MAP：MAP包含key->value键值对，可以通过key来访问元素。比如”userlist”是一个map类型，其中username是key，password是value；那么我们可以通过userlist['username']来得到这个用户对应的password

STRUCT(结构)：STRUCT可以包含不同数据类型的元素。这些元素可以通过”点语法”的方式来得到所需要的元素，比如user是一个STRUCT类型，那么可以通过user.address得到这个用户的地址。

 struct

示例：

CREATE TABLE student(

         name STRING,

         favors ARRAY<STRING>,

         scores MAP<STRING, FLOAT>,

    address STRUCT<province:STRING, city:STRING, detail:STRING, zip:INT>

)

   ROW FORMAT DELIMITED

   FIELDS TERMINATED BY '\t'

   COLLECTION ITEMS TERMINATED BY ';'

MAP KEYS TERMINATED BY ':' ;

说明：

1、字段name是基本类型，favors是数组类型，可以保存很多爱好，scores是映射类型，可以保存多个课程的成绩，address是结构类型，可以存储住址信息

2、ROW FORMAT DELIMITED是指明后面的关键词是列和元素分隔符的

3、FIELDS TERMINATED BY 是字段分隔符

4、COLLECTION ITEMS TERMINATED BY是元素分隔符（Array中的各元素、Struct中的各元素、Map中的key-value对之间）

5、MAP KEYS TERMINATED BY是Map中key与value的分隔符

6、LINES TERMINATED BY是行之间的分隔符

7、STORED AS TEXTFILE指数据文件上传之后保存的格式

总结：在关系型数据库中，我们至少需要三张表来定义，包括学生基本表、爱好表、成绩表；但在Hive中通过一张表就可以搞定了。也就是说，复合数据类型把多表关系通过一张表就可以实现了。

 

5.3、实操示例演示：

5.3.1、Array

--建表语句
create table person(
name string,
work_locations string 
)
row format delimited fireds terminated by '\t';

create table person1(
name string,
work_locations srting
)
row format delimited fields terminated by '\t'
collection items terminated by ',';

--数据person.txt：
huangbo    beijing,shanghai,tianjin,hangzhou
xuzheng    changchu,chengdu,wuhan
wangbaoqiang     dalian,shenyang,jilin

--导入数据
load data local inpath '/绝对路径/person.txt' into table person;

--查询语句
select * from person;
select name from person;
select work_locations from person;
select work_locations[0] from person;

5.3.2、Map

-- 建表语句
create table score(
name string,
scores map<string,int>   -- key->value键值对，可以通过key来访问元素
)
row format delimited fields terminated by '\t'
collection items terminated by ','

-- 数据 score.txt
huangbo    yuwen:80,shuxue:89,yingyu:95
xuzheng    yuwen:70,shuxue:65,yingyu:81
wangbaoqiang     yuwen:75,shuxue:100,yingyu:75

-- 导入数据
load data local inpath '/绝对路径/score.txt' into table score;

--查询语句：
select * from score;
select name from score;
select scores from score;
select s.scores['yuwen'] from score s;

5.3.3、Struct

-- 建表语句：
create table structtable(
id int,
course struct<name:string,score:int>
)
collection items terminated by ',';

-- 数据 structtable.txt
1 english,80
2 math,89
3 chinese,95

-- 导入数据
load data lcoal inpath '/绝对路径/structtable.txt' into table structtable;

-- 查询语句：
Select * from structtable;
Select id from structtable;
Select course from structtable;
Select t.course.name from structtable t;
Select t.course.score from structtable t;

 

6、Hive函数

6.1、Hive内置函数

1、内容较多，见《Hive官方文档》

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

 

2、测试内置函数的快捷方式：

1、创建一个dual表create table dual(id string);

2、load一个文件（一行，一个空格）到dual表

3、select substr('huangbo',2,3) from dual;

 

3、查看内置函数：

show functions;

显示函数的详细信息：

desc function abs;

显示函数的扩展信息：

desc function extended concat;

 

4、内置函数列表

一、关系运算：

         1. 等值比较: =

         2. 等值比较:<=>

         3. 不等值比较: <>和!=

         4. 小于比较: <

         5. 小于等于比较: <=

         6. 大于比较: >

         7. 大于等于比较: >=

         8. 区间比较

         9. 空值判断: IS NULL

         10. 非空判断: IS NOT NULL

         10.  LIKE比较: LIKE

         10.  JAVA的LIKE操作: RLIKE

         12.  REGEXP操作: REGEXP

 

二、数学运算：

         1. 加法操作: +

         2. 减法操作: –

         3. 乘法操作: *

         4. 除法操作: /

         5. 取余操作: %

         6. 位与操作: &

         7. 位或操作: |

         8. 位异或操作: ^

         9．位取反操作: ~

 

三、逻辑运算： 

         1. 逻辑与操作: AND 、&&

         2. 逻辑或操作: OR 、||

         3. 逻辑非操作: NOT、!

 

四、复合类型构造函数   

         1.  map结构

         2.  struct结构

         3.  named_struct结构

         4.  array结构

         5.  create_union

 

五、复合类型操作符

         1. 获取array中的元素

         2. 获取map中的元素

         3. 获取struct中的元素

 

六、数值计算函数 

         1. 取整函数: round

         2. 指定精度取整函数: round

         3. 向下取整函数: floor

         4. 向上取整函数: ceil

         5. 向上取整函数: ceiling

         6. 取随机数函数: rand

         7. 自然指数函数: exp

         8. 以10为底对数函数: log10

         9. 以2为底对数函数: log2

         10. 对数函数: log

         10. 幂运算函数: pow

         12. 幂运算函数: power

         13. 开平方函数: sqrt

         14. 二进制函数: bin

         15. 十六进制函数: hex

         16. 反转十六进制函数: unhex

         17. 进制转换函数: conv

         18. 绝对值函数: abs

         19. 正取余函数: pmod

         20. 正弦函数: sin

         21. 反正弦函数: asin

         22. 余弦函数: cos

         23. 反余弦函数: acos

         24.  positive函数: positive

         25.  negative函数: negative

 

七、集合操作函数   

         1.  map类型大小：size

         2.  array类型大小：size

         3. 判断元素数组是否包含元素：array_contains

         4. 获取map中所有value集合

         5. 获取map中所有key集合

         6. 数组排序

 

八、类型转换函数

         1. 二进制转换：binary

         2. 基础类型之间强制转换：cast

 

九、日期函数  

         1.  UNIX时间戳转日期函数: from_unixtime

         2. 获取当前UNIX时间戳函数: unix_timestamp

         3. 日期转UNIX时间戳函数: unix_timestamp

         4. 指定格式日期转UNIX时间戳函数: unix_timestamp

         5. 日期时间转日期函数: to_date

         6. 日期转年函数: year

         7. 日期转月函数: month

         8. 日期转天函数: day

         9. 日期转小时函数: hour

         10. 日期转分钟函数: minute

         10. 日期转秒函数: second

         12. 日期转周函数: weekofyear

         13. 日期比较函数: datediff

         14. 日期增加函数: date_add

         15. 日期减少函数: date_sub

 

十、条件函数  

         1.  If函数: if

         2. 非空查找函数: COALESCE

         3. 条件判断函数：CASE

         4. 条件判断函数：CASE

 

十一、字符串函数 

         1. 字符ascii码函数：ascii

         2. base64字符串

         3. 字符串连接函数：concat

         4. 带分隔符字符串连接函数：concat_ws

         5. 数组转换成字符串的函数：concat_ws

         6. 小数位格式化成字符串函数：format_number

         7. 字符串截取函数：substr,substring

         8. 字符串截取函数：substr,substring

         9. 字符串查找函数：instr

         10. 字符串长度函数：length

         10. 字符串查找函数：locate

         12. 字符串格式化函数：printf

         13. 字符串转换成map函数：str_to_map

         14.  base64解码函数：unbase64(string str)

         15. 字符串转大写函数：upper,ucase

         16. 字符串转小写函数：lower,lcase

         17. 去空格函数：trim

         18. 左边去空格函数：ltrim

         19. 右边去空格函数：rtrim

         20. 正则表达式替换函数：regexp_replace

         21. 正则表达式解析函数：regexp_extract

         22.  URL解析函数：parse_url

         23.  json解析函数：get_json_object

         24. 空格字符串函数：space

         25. 重复字符串函数：repeat

         26. 左补足函数：lpad

         27. 右补足函数：rpad

         28. 分割字符串函数: split

         29. 集合查找函数: find_in_set

         30. 分词函数：sentences

         31. 分词后统计一起出现频次最高的TOP-K

         32. 分词后统计与指定单词一起出现频次最高的TOP-K

 

十二、混合函数 

         1. 调用Java函数：java_method

         2. 调用Java函数：reflect

         3. 字符串的hash值：hash

 

十三、XPath解析XML函数  

         1. xpath

         2. xpath_string

         3. xpath_boolean

         4. xpath_short, xpath_int, xpath_long

         5. xpath_float, xpath_double, xpath_number

 

十四、汇总统计函数（UDAF）

         1. 个数统计函数: count

         2. 总和统计函数: sum

         3. 平均值统计函数: avg

         4. 最小值统计函数: min

         5. 最大值统计函数: max

         6. 非空集合总体变量函数: var_pop

         7. 非空集合样本变量函数: var_samp

         8. 总体标准偏离函数: stddev_pop

         9. 样本标准偏离函数: stddev_samp

         10．中位数函数: percentile

         10. 中位数函数: percentile

         12. 近似中位数函数: percentile_approx

         13. 近似中位数函数: percentile_approx

         14. 直方图: histogram_numeric

         15. 集合去重数：collect_set

         16. 集合不去重函数：collect_list

 

十五、表格生成函数Table-Generating Functions (UDTF)

         1. 数组拆分成多行：explode

         2. Map拆分成多行：explode

6.2、Hive自定义函数UDF

当Hive提供的内置函数无法满足业务处理需要时，此时就可以考虑使用用户自定义函数

 

UDF（user-defined function）作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）

UDAF（用户定义聚集函数User- Defined Aggregation Funcation）：接收多个输入数据行，并产生一个输出数据行。（count，max）

UDTF（User-Defined Table Functions）：接收一行输入，输出多行（explode）

6.2.1、一个简单的UDF示例

先开发一个简单的java类，继承org.apache.hadoop.hive.ql.exec.UDF，重载evaluate方法

Package com.ghgj.hive.udf

 

import java.util.HashMap;

import org.apache.hadoop.hive.ql.exec.UDF;

 

public class ToLowerCase extends UDF {

         // 必须是public，并且evaluate方法可以重载

         public String evaluate(String field) {

                  String result = field.toLowerCase();

                  return result;

         }

}

 

2、打成jar包上传到服务器

3、将jar包添加到hive的classpath

hive>add JAR /home/hadoop/hivejar/udf.jar;

        

         查看加入的jar的命令：

         hive> list jar;

 

4、创建临时函数与开发好的class关联起来

hive>create temporary function tolowercase as ' com.ghgj.hive.udf. ToLowerCase ';

 

5、至此，便可以在hql在使用自定义的函数

select tolowercase(name),age from student;

6.2.2、Json数据解析UDF开发

现有原始json数据（rating.json）如下，

{"movie":"1093","rate":"5","timeStamp":"978300760","uid":"1"}

{"movie":"661","rate":"3","timeStamp":"978302109","uid":"1"}

{"movie":"914","rate":"3","timeStamp":"978301968","uid":"1"}

{"movie":"3408","rate":"4","timeStamp":"978300275","uid":"1"}

{"movie":"2355","rate":"5","timeStamp":"978824291","uid":"1"}

{"movie":"1097","rate":"3","timeStamp":"978302268","uid":"1"}

{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}

{"movie":"2804","rate":"5","timeStamp":"978300719","uid":"1"}

{"movie":"594","rate":"4","timeStamp":"978302268","uid":"1"}

现在需要将数据导入到hive仓库中，并且最终要得到这么一个结果：

movie

rate

timeStamp

uid

1093

5

978300760

1

该怎么做、？？？（提示：可用内置get_json_object或者自定义函数完成）

6.2.3、Transform实现

Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能。适合实现Hive中没有的功能又不想写UDF的情况

 

具体以一个实例讲解。

Json数据：

{"movie":"1093","rate":"5","timeStamp":"978300760","uid":"1"}

需求：把timestamp的值转换成日期编号

 

1、先加载rating.json文件到hive的一个原始表 rat_json

create table rat_json(line string) row format delimited;

load data local inpath '/home/hadoop/rating.json' into table rat_json;

 

2、创建rate这张表用来存储解析json出来的字段：

create table rate(movie int, rate int, unixtime int, userid int) row format delimited fields terminated by '\t';

 

解析json，得到结果之后存入rate表：

insert into table rate select

get_json_object(line,'$.movie') as moive,

get_json_object(line,'$.rate') as rate,

get_json_object(line,'$.timeStamp') as unixtime,

get_json_object(line,'$.uid') as userid

from rat_json;

 

3、使用transform+python的方式去转换unixtime为weekday

先编辑一个python脚本文件

########python######代码

## vi weekday_mapper.py

 

#!/bin/python

import sys

import datetime

for line in sys.stdin:

  line = line.strip()

  movie,rate,unixtime,userid = line.split('\t')

  weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()

  print '\t'.join([movie, rate, str(weekday),userid])

 

保存文件

然后，将文件加入hive的classpath：

hive>add file /home/hadoop/weekday_mapper.py;

hive> insert into table lastjsontable select transform(movie,rate,unixtime,userid)

using 'python weekday_mapper.py' as(movie,rate,weekday,userid) from rate;

 

create table lastjsontable(movie int, rate int, weekday int, userid int) row format delimited fields terminated by '\t';

 

最后查询看数据是否正确：

select distinct(weekday) from lastjsontable;

7、视图

物化视图在此不做过多介绍（用的太少）

和关系型数据库一样，Hive也提供了视图的功能，不过请注意，Hive的视图和关系型数据库的数据还是有很大的区别：

1、只有逻辑视图，没有物化视图；

2、视图只能查询，不能Load/Insert/Update/Delete数据；

3、视图在创建时候，只是保存了一份元数据，当查询视图的时候，才开始执行视图对应的那些子查询

 

创建视图

create view view_name as select * from carss;

create view carss_view as select * from carss limit 500;

 

查看视图

show tables;   // 可以查看表，也可以查看视图

desc view_name    // 查看某个具体视图的信息

desc carss_view

 

删除视图

drop view view_name

drop view if exists carss_view

 

使用视图

create view sogou_view as select * from sogou_table where rank > 3 ;

select count(distinct uid) from sogou_view;

8、Hive执行过程实例分析

8.1、Hive执行过程概述

1.  Hive将HQL转换成一组操作符（Operator），比如GroupByOperator, JoinOperator等

2. 操作符Operator是Hive的最小处理单元

3. 每个操作符代表一个HDFS操作或者MapReduce作业

4. Hive通过ExecMapper和ExecReducer执行MapReduce程序，执行模式有本地模式和分布式两种模式

 

Hive操作符列表：

 

Hive 编译器的工作职责：

1.   Parser：将SQL语句转换成抽象语法树（AST Tree）

2.  Semantic Analyzer：将抽象语法树转换成查询块

3.  Logic Plan Generator：将查询块转换成逻辑查询计划、

4.  Logic Optimizer：重写逻辑查询计划

5.  Physical Plan Gernerator：将逻辑计划转化成物理计划（MapReduce Jobs）

6. Physical Optimizer：选择最佳的Join策略

8.2、Join

对于join操作：

SELECT pv.pageid, u.age FROM page_view pv JOIN user u ON pv.userid = u.userid;

 

执行的最后结果条数：page_view表中的userid数目 * user 表中的 userid数目

 

实现过程：

Map：1、以 JOIN ON 条件中的列作为 Key，如果有多个列，则 Key 是这些列的组合

2、以 JOIN 之后所关心的列作为 Value，当有多个列时，Value 是这些列的组合。在 Value 中还会包含表的 Tag 信息，用于标明此 Value 对应于哪个表。

3、按照 Key 进行排序。

Shuffle：

1、根据 Key 的值进行 Hash，并将 Key/Value 对按照 Hash 值推至不同对 Reduce 中。

Reduce：

1、  Reducer 根据 Key 值进行 Join 操作，并且通过 Tag 来识别不同的表中的数据。

 

具体实现过程：

、

8.3、Group By

对于group by:

SELECT pageid, age, count(1) FROM pv_users GROUP BY pageid, age; 

 

 

 

8.4、Distinct

对于distinct：

SELECT age, count(distinct pageid) FROM pv_users GROUP BY age;

按照age分组，然后统计每个分组里面的不重复的pageid有多少个。

 

实现过程：

 

 

详细过程解释：该SQL语句会按照age和pageid预先分组，进行distinct操作。然后会再按照age进行分组，再进行一次distinct操作

posted @ 2022-10-24 20:56  去揽一池星河  阅读(131)  评论(0)  编辑  收藏  举报