i-Hive-1

1. Hive 初探

1.1 Hive 的数据存储

• Hive的数据存储基于Hadoop HDFS
• Hive没有专门的数据存储格式
• 存储结构主要包括：数据库、文件、表、视图、索引
• Hive默认可以直接加载文本文件（TextFile），还支持SequenceFile、RCFile
• 创建表时，指定Hive数据的列分隔符与行分隔符，Hive即可解析数据

1.2 Hive的系统架构

• 用户接口，包括 CLI，JDBC/ODBC，WebUI
• 元数据存储，通常是存储在关系数据库如 mysql, derby 中
• 解释器、编译器、优化器、执行器
• Hadoop：用 HDFS 进行存储，利用 MapReduce 进行计算

1.3 Hive的系统架构

• 用户接口主要有三个：CLI，JDBC/ODBC和 WebUI
  • CLI，即Shell命令行
  • JDBC/ODBC 是 Hive 的Java，与使用传统数据库JDBC的方式类似
  • WebGUI是通过浏览器访问 Hive
• Hive 将元数据存储在数据库中(metastore)，目前只支持 mysql、derby。Hive 中的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表等），表的数据所在目录等
• 解释器、编译器、优化器完成 HQL 查询语句从词法分析、语法分析、编译、优化以及查询计划（plan）的生成。生成的查询计划存储在 HDFS 中，并在随后由 MapReduce 调用执行
• Hive 的数据存储在 HDFS 中，大部分的查询由 MapReduce 完成（包含 的查询，比如 select from table 不会生成 MapRedcue 任务

1.4 Hive的metastore

• metastore是hive元数据的集中存放地。
• metastore默认使用内嵌的derby数据库作为存储引擎
• Derby引擎的缺点：一次只能打开一个会话
• 使用Mysql作为外置存储引擎，多用户同时访问

1.5 Hive 和 Hadoop 的调用关系

1、提交sql 交给驱动
2、驱动编译 解析相关的字段表信息
3、去metastore查询相关的信息 返回字段表信息
4、编译返回信息 发给驱动
5、驱动发送一个执行计划 交给执行引擎
6.1、DDLs 对数据库表的操作的, 直接和metastore交互,create table t1(name string);

6.2、完成job返回数据信息、找namenode查数据
6.3、namenode交互select count(1) from t1;
7、返回结果信息集

1.6 Hive 参数配置使用

命名空间	使用权限	描述
hivevar	可读写	$ hive -d name=zhangsan;
hiveconf	可读写	\$ hive –hiveconf hive.cli.print.current.db=true; $ hive –hiveconf hive.cli.print.header=true;
system	可读写	java定义的配置属性，如system:user.name
env	只读	shell环境变量，如env:USER

• hivevar

  # 使用场景: 起别名
  hive -d name=zhangsan  #传参
  > create table t2(name string,${name} string); #取参数
  > desc t2;
  ---
  name                	string
  zhangsan            	string

• hiveconf :

  #  显示当前数据库名称
  [ap@cs2]~% hive --hiveconf hive.cli.print.current.db=true;
  hive (default)> create database mydb;
  hive (default)> use mydb;
  hive (mydb)> 
  
  # 显示表头(字段名)
  hive --hiveconf hive.cli.print.header=true;
  select * from t2;
  t2.name	t2.zhangsan

1.7 Hive 的脚本执行

• Hive -e “xx ”
  • e 就是 edit, 在终端打印输出
• Hive -e “show tables” >> a.txt
  • 可以把执行结果重定向到文件中
• Hive -S -e “show tables” >> a.txt
  • -S : silence 安静的执行
• hive -f file
  • hive -f hql , hql 是文件, 执行文件
  • 执行完了之后, 就离开 hive 命令行
• hive -i /home/ap/hive-init.sql
  • 执行完了,还在控制台, 可以继续操作
• hive>source file
  • source + 文件名 : 直接执行当前目录文件
  • source /home/ap/xx.sql;

1.8 hive与依赖环境的交互

• 与linux交互命令 ！
  • !ls
  • !pwd
• 与hdfs交互命令
  • dfs -ls /
  • dfs -mkdir /hive
  • hive (default)> dfs -rm -r /user/hive/warehouse/t5;
• beeline 与 linux & hdfs 交互
  • !help 查看帮助

1.9 Hive 的 JDBC 模式

• JAVA API交互执行方式

• hive 远程服务 (端口号1000 0) 启动方式

  • hive --service hiveserver2
  • org.apache.hive.jdbc.HiveDriver

• 在java代码中调用hive的JDBC建立连接

• 用 beeline 连接

  • 方式1: 直接登录

    • 注意: 这里的cs2是指的数据库所在的服务器, 如果mysql 安装在 cs2上, 那么不管在哪台机器上登录beeline , 都这样输入就行了

    beeline -u jdbc:hive2://cs2:10000 -n ap

  • 方式2: 输入用户名密码登录

    !connect jdbc:hive2://cs2:10000

• beeline注意点:

  • 使用 beeline 连接时, 貌似无法与 Linux 目录交互
  • 当前目录在/home/ap/apps/apache-hive-2.3.2-bin/bin/下
  • 要传文件的话, 要使用全路径

1.10 SET命令使用

• Hive 控制台set 命令
  • set; set -v; 显示所有的环境变量
  • set hive.cli.print.current.db=true;
  • set hive.cli.print.header=true;
  • set hive.metastore.warehouse.dir=/hive;
• hive参数初始化配置set命令:
  • ~/.hiverc
    • 创建此文件, 在此文件中配置初始化命令
  • 补充：
    hive历史操作命令集
    ~/.hivehistory

2. Hive数据类型

2.1 基本数据类型

2.2 复合数据类型

创建学生表

CREATE TABLE student(
    id INT,
    name STRING,
    favors ARRAY\<STRING>,
    scores MAP<STRING, FLOAT>
);

默认分隔符	描述	语句
\n	分隔行	LINES TERMINATED BY ‘\t’
^A	分隔字段(列)，显示编码使用\001	FIELDS TERMINATED BY ‘\001’
^B	分隔复合类型中的元素，显示编码使用\002	COLLECTION ITEMS TERMINATED BY ‘\002’
^C	分隔map元素的key和value，显示编码使用\003	MAP KEYS TERMINATED BY ‘\003’

2.2.1. Struct 使用

Structs内部的数据可以通过DOT（.）来存取，例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a

# 数据
1001,zhangsan:24
1002,lisi:28
1003,wangwu:25

# 1.创建表
hive> create table student_test(id INT, info struct<name:STRING, age:INT>)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','COLLECTION ITEMS TERMINATED BY ':';

# 2.加载表
hive> load data local inpath "student_test" into table student_test;

# 3.顺便设置 显示表头,和当前数据库
hive> set hive.cli.print.header=true;
hive> set hive.cli.print.current.db=true;

# 4. 展示所有的
hive (default)> select * from student_test;
---
student_test.id	student_test.info
1001	{"name":"zhangsan","age":24}
1002	{"name":"lisi","age":28}
1003	{"name":"wangwu","age":25}
---

# Struct -结构体-使用 . 
hive (default)> select id,info.name,info.age from student_test;
id	name	age
1001	zhangsan	24
1002	lisi	28
1003	wangwu	25

2.2.2. Array 使用

Array中的数据为相同类型，例如，假如array A中元素['a','b','c’]，则A[1]的值为’b’

# 原始数据
zhangsan,24:25:27:37
lisi,28:39:23:43
wangwu,25:23:02:54

# 创建表
hive (default)> create table class_test(name string,student_id_list array<int>) row format delimited fields terminated by ',' collection items terminated by ':';

# 加载表
hive (default)> load data local inpath "class_test" into table class_test;

# 查看表
hive (default)> select * from class_test;
OK
class_test.name	class_test.student_id_list
zhangsan	[24,25,27,37]
lisi	[28,39,23,43]
wangwu	[25,23,2,54]

# 查看数据中某个元素
hive (default)> select name, student_id_list[0] from class_test where name='zhangsan';
OK
name	_c1
zhangsan	24

2.2.3. Map 使用

访问指定域可以通过[“指定域名称”]进行，例如，一个Map M包含了一个group->gid的kv对，gid的值可以通过M[‘group’]来获取

# 原始数据
1001	job:80,team:60,person:70
1002	job:60,team:80,person:80
1003	job:90,team:70,person:100

# 创建表
hive (default)> create table employee(id string,perf map<string,int>) row format delimited fields terminated by '\t' collection items terminated by ',' map keys terminated by ':';

# 导入
hive (default)> load data local inpath "employee_data" into table employee;

# 查看
hive (default)> select * from employee;
---
employee.id	employee.perf
1001	{"job":80,"team":60,"person":70}
1002	{"job":60,"team":80,"person":80}
1003	{"job":90,"team":70,"person":100}
Time taken: 0.228 seconds, Fetched: 3 row(s)

# 查看单个
hive (default)> select id,perf['job'],perf['team'],perf['person'] from employee;
OK
id	_c1	_c2	_c3
1001	80	60	70
1002	60	80	80
1003	90	70	100

# 显示别名
hive (default)> select id,perf['job'] as job,perf['team'] as team,perf['person'] as person from employee;
OK
id	job	team	person
1001	80	60	70
1002	60	80	80
1003	90	70	100

3. DDL , DML

3.1 DDL

3.1.1 数据库定义

• 默认数据库”default”

• 使用某个数据库 use <数据库名>

• 创建一个新库

  CREATE DATABASE  
  [IF NOT EXISTS] mydb  
  [LOCATION] '/.......'  
  [COMMENT] '....’;
  
  hive>SHOW DATABASES;
  hive>DESCRIBE DATABASE [extended] mydb;
  hive>DROP DATABASE [IF EXISTS] mydb [CASCADE];

• 创建

  • create database db1;

• 删除

  • drop database if exists db1;
  • 级联删除
    • drop database if exists db1 cascade;

3.1.2 表定义/修改

• 创建表

  • hive>CREATE TABLE IF NOT EXISTS t1(…)

    [COMMENT ‘….’]

    [LOCATION ‘…’]

    • hive (default)> create table t4(name string,age int) row format delimited fields terminated by "\t”;

  • hive> SHOW TABLES in mydb;

    • show tables in mydb ‘’class*“ : 查看以 mydb 库中, 以 class 开头的表

  • hive>CREATE TABLE t2 LIKE t1; 复制表

    • 只会复制表结构
    • hive (default)> create table t2 like t1;
    • hive (mydb)> create table t3 like default.employee;
      • 复制其它库的表

  • hive>DESCRIBE t2;

    • desc t2; # 效果一样的
    • desc extended t1; # 查看更详细的表信息
    • hive (default)> desc formatted t1; # 格式化查看表的详细信息

  • drop table xxx;

    • 删除表

  • 查看建表语句

    • show create table t_table;

• 修改表

  • 重命名表
    • ALTER TABLE table_name RENAME TO new_table_name
  • 增加/删除 分区
    • alter table student_p add partition(part='a') partition(part='b');
      • 两个 partition中没有’,’
    • alter table student drop partition(stat_data=‘ffff’), partition(part=‘a’),partiton(part=‘b’);
      • 两个 partition中有’,’
  • 增加/更新 列
    • ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
      • 注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。
      • alter table student add columns (name1 string);
    • ALTER TABLE table_name CHANGE c_name new_c_name new_c_type [FIRST | AFTER c_name]

3.1.3 列定义

• 修改列的名称、类型、位置、注释

  • ALTER TABLE t3 CHANGE COLUMN old_name new_name String COMMENT '...' AFTER column2;

  • # 修改列名
    hive (default)> alter table t1 change column name username string comment 'new name';
    # 查看表
    hive (default)> desc t1;
    ---
    col_name	data_type	comment
    username            	string              	new name
    age                 	int
    ---

• 增加列

  • hive> ALTER TABLE t3 ADD COLUMNS(gender int);

    # 查看表结构
    hive (default)> desc t3;
    ---
    col_name	data_type	comment
    name                	string
    ---
    # 添加列
    hive (default)> alter table t3 add columns(gender int);
    ---
    hive (default)> desc t3;
    ---
    col_name	data_type	comment
    name                	string
    gender              	int

• 删除列 replace

  • 非常不建议使用, 会造成数据错乱, 一般采取重新创建一张表的方式.

3.1.4 显示命令

show tables

show databases

show partitions

show functions

desc extended t_name;

desc formatted table_name;

3.2 DML

3.2.1 Load

语法结构

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

说明：

1. Load 操作只是单纯的复制/移动操作，将数据文件移动到 Hive 表对应的位置。
2. filepath：
   • 相对路径，例如：project/data1
   • 绝对路径，例如：/user/hive/project/data1
   • 包含模式的完整 URI，例如：
     • hdfs://namenode:9000/user/hive/project/data1

3. LOCAL关键字

   • 如果指定了 LOCAL， load 命令会去查找本地文件系统中的 filepath。
   • 如果没有指定 LOCAL 关键字，则根据inpath中的uri查找文件

4. OVERWRITE 关键字

   • 如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
   • 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。

3.2.2 Insert

语法结构

• 普通插入

  • INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement1 FROM from_statement

• Multiple inserts:

  FROM from_statement 
  [INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 ]
  [INSERT OVERWRITE TABLE tablename2 [PARTITION ...] select_statement2] ...
  
  # 多重插入举例
  from student
  insert into table student_p partition(part='a')
  select * where id<95011
  insert into table student_p partition(part='b')
  select * where id>95011;

• Dynamic partition inserts:

  • 不指定分区字段, 按照 from 表的分区字段插入
  • INSERT OVERWRITE TABLE tablename PARTITION (partcol1, partcol2 ...) select_statement FROM from_statement

导出表数据

语法结构

• INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...

  # 导出到本地
  insert overwrite local directory '/home/ap/test/stucent1'
  select * from student1;
  --------------------
  例子:
  '查询学生信息，按性别分区，在分区内按年龄有序'
  0: jdbc:hive2://cs2:10000> set mapred.reduce.tasks=2;
  No rows affected (0.015 seconds)
  0: jdbc:hive2://cs2:10000> insert overwrite local directory '/home/ap/ihiveout'
  . . . . . . . . . . . . .> select * from student distribute by Sex sort by Sage;
  --------------------
  
  # 导出到 HDFS (仅仅是少了一个 local)
  insert overwrite directory '/test/stucent1'
  select * from student1;

• multiple inserts:

  FROM from_statement
  INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
  [INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...

4. Hive的数据模型

4.1 管理表 - 又称为内部表, 受控表

基本操作

• 创建数据文件inner_table.dat
• 创建表
  • hive>create table inner_table (key string);
• 加载数据
  • 加载本地数据
    • hive>load data local inpath '/root/inner_table.dat' into table inner_table;
  • 加载HDFS 上数据
    • hive>load data inpath ‘xxx’ into table xxx;
  • 区别
    • 加载 hdfs 上的数据没有 local
    • 加载本地数据是 copy 一份, 加载 hdfs 上的数据是直接移动数据到加载的表目录下– mv
• 查看数据
  • select * from inner_table
  • select count(*) from inner_table
• 删除表 drop table inner_table
• 清空表 truncate table table_name;
• 注意:
  • 如果创建表的时候, 只指定了目录, 没有指定表名, 删除表的时候, 会把该目录下的所有表全部删掉
  • hive (mydb)> create table t2(id int)location '/home/t2';

内部表解释

• 管理表，也称作内部表,受控表
  • 所有的 Table 数据（不包括 External Table）都保存在warehouse这个目录中。
  • 删除表时，元数据与数据都会被删除
  • 创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除

内部表转为外部表, 外部表转为内部表

• hive (mydb)> create table t1(id int);
  
  # manage_table 转换为 外部表 external_table 
  ## 注意: 修改为外部表时, 后面2个都要大写
  hive (mydb)> alter table t1 set tblproperties('EXTERNAL'='TRUE');
  ## 修改为内部表
  hive (mydb)> alter table t1 set tblproperties('EXTERNAL'='FALSE');
  
  
  # 查看t1详细信息
  desc formatted t1;
  ---
  Location:           	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1
  Table Type:         	EXTERNAL_TABLE
  -- 
  
  # 删除t1
  hive (mydb)> drop table t1;
  # 此时再查看, 已经没了
  hive (mydb)> show tables;
  OK
  tab_name
  
  # 但是查看hdfs 路径会发现还在, 因为此表现在已经是外部表, 删除不会删除数据
  dfs -ls /user/hive/warehouse/mydb.db/t1
  
  # 如果此时再创建一个新表 t1, 表结构一样, 则数据会自动加载

4.2 ※ 外部表

4.2.1基本操作

• 创建数据文件external_table.dat
• 创建表
  • hive>create external table external_table1 (key string) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' location '/home/external’;
• 在HDFS创建目录/home/external
  • #hadoop fs -put /home/external_table.dat /home/external
  • 在工作中, 一般都这样使用, 把数据上传到 hdfs 中
• 加载数据
  • LOAD DATA '/home/external_table1.dat' INTO TABLE external_table1;
• 查看数据
  • select * from external_table
  • select count(*) from external_table
• 删除表
  • drop table external_table

4.2.2 外部表解释

• 包含External 的表叫外部表
  • 删除外部表只删除metastore的元数据，不删除hdfs中的表数据
  • 外部表 只有一个过程，加载数据和创建表同时完成，并不会移动到数据仓库目录中，只是与外部数据建立一个链接。当删除一个 外部表 时，仅删除该链接
  • 指向已经在 HDFS 中存在的数据，可以创建 Partition
  • 它和 内部表 在元数据的组织上是相同的，而实际数据的存储则有较大的差异

4.2.3 外部表语法

CREATE EXTERNAL TABLE page_view
( viewTime INT, 
  userid BIGINT,
  page_url STRING, 	
 referrer_url STRING, 							
  ip STRING COMMENT 'IP Address of the User',
  country STRING COMMENT 'country of origination‘
)
    COMMENT 'This is the staging page view table'
    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 	LINES 	TERMINATED BY '\n'
    STORED AS TEXTFILE
    LOCATION 'hdfs://centos:9000/user/data/staging/page_view';

4.2.4外部表注意点:

• 先创建外部表/内部表, 表名为t3, 再往t3传对应字段的数据, 就可以直接 select 数据了

• 删除外部表之后, 原本数据不会删除, 此时在相同的父路径创建与被删除表字段相同&名称相同的内部/外部表, 数据也会直接加载

• 再看一个操作

  # 在 mydb.db 库下创建一个外部表 t5, 指定路径为 '/external/t5'
  # 此时在 mydb.db 库的路径下是不存在 t5表的, 而是存在 '/external/t5' 中
  # 但是使用 'show tables' 是存在 t5 的
  hive (mydb)> create external table t5(id int) location '/external/t5';
  # 往此目录传数据, 注意: 此时传过去, intdata 数据存在 t5目录下
  [ap@cs2]~% hadoop fs -put intdata /external/t5
  [ap@cs2]~% hadoop fs -put intdata /external/t5/i2
  # 继续传数据, 查询的时候, 就是传的全部数据'相当于所有的数据都在 t5表中'
  [ap@cs2]~% hadoop fs -put intdata /external/t5
  # 注意: 如果传到 t5 目录下, 没有指定数据文件名的话, 会默认采用数据的名称文件.

4.3 ※ 分区表

4.3.1 基本概念和操作

• 分区可以理解为分类，通过分类把不同类型的数据放到不同的目录下。
• 分类的标准就是分区字段，可以一个，也可以多个。
• 分区表的意义在于优化查询。查询时尽量利用分区字段。如果不使用分区字段，就会全部扫描。

创建分区表, 指定分区字段

hive>CREATE TABLE t3(...) PARTITIONED BY (province string);

• 创建表的时候, 指定分区字段 keyprovince

为分区字段添加一个值

hive>ALTER TABLE t3 ADD [IF NOT EXISTS] PARTITION(...) LOCATION '...’;

• alter table t3 add if not exists partition(province='hubei') partition(province='shanghai');
• alter table t3 add if not exists partition(province='jiangsu');
• 可为此分区字段添加多个值, 为 province 添加 hubei, hunan….

查看表的分区字段&值

hive>SHOW PARTITIONS t3 [partition (province='beijing')];

删除分区

hive>ALTER TABLE t3 DROP PARTITION(province=‘beijing’.);

• 这里是删除北京的分区 (如果是内部表, 会连数据一起删除)

设置表不能被删除/查询 ——– 这里报语法错误, :TODO

• 防止分区被删除:alter table student_p partition (part='aa') enable no_drop;
• 防止分区被查询:alter table student_p partition (part='aa') enable offline;
• enable 和 disable 是反向操作

其它一些相关命令

SHOW TABLES; # 查看所有的表

SHOW TABLES ‘TMP‘; #支持模糊查询

SHOW PARTITIONS TMP_TABLE; #查看表有哪些分区

DESC TMP_TABLE; #查看表结构

4.3.2 创建分区表完整语法

CREATE TABLE tmp_table #表名
(
title   string, # 字段名称 字段类型
minimum_bid     double,
quantity        bigint,
have_invoice    bigint
)COMMENT '注释：XXX' #表注释
 PARTITIONED BY(pt STRING) #分区表字段（如果你文件非常之大的话，采用分区表可以快过滤出按分区字段划分的数据）
 ROW FORMAT DELIMITED 
   FIELDS TERMINATED BY '\001'   # 字段是用什么分割开的
STORED AS SEQUENCEFILE; #用哪种方式存储数据，SEQUENCEFILE是hadoop自带的文件压缩格式

4.3.3 分区表注意点(错误点)

• 1) 分区表在 load 数据的时候, 得指定分区, 否则会报错

  # 错误1: 分区表在 load 数据的时候, 得指定分区
  hive (mydb)> load data local inpath '~/ihivedata/intdata' into table t6;
  FAILED: SemanticException [Error 10062]: Need to specify partition columns because the destination table is partitioned
  
  # 错误2: 导入本地数据的时候, 'path'是从当前所在路径开始的
  hive (mydb)> load data local inpath '~/ihivedata/intdata' into table t6 partition(class='job1');
  FAILED: SemanticException Line 1:23 Invalid path ''<sub>/ihivedata/intdata'': No files matching path file:/home/ap/</sub>/ihivedata/intdata
  
  # 这里就正确了
  hive (mydb)> load data local inpath 'ihivedata/intdata' into table t6 partition(class='job1');
  Loading data to table mydb.t6 partition (class=job1)
  OK

  • 本质原因:

    • 分区表的分区, 就是在 hdfs 上, 原表的文件夹下面创建了一个子文件夹, 文件夹名就是分区名.

    

    • 从本地 load 数据: hive (mydb)> load data local inpath 'ihivedata/intdata' into table t6 partition(class='job1');
    • load 数据指定分区之后, 会直接 load 到数据文件夹里面

    

  • 2) 没有添加分区时, 直接往不存在的分区导入数据, 分区会自动创建

    # 直接往不存在的分区load数据, 分区会自动创建
    load data local inpath 'ihivedata/intdata' into table t6 partition(class='job110');

• 3) 手动在表中创建分区(文件夹), 并直接向此文件夹中导入数据

  # 直接创建目录
  hive (mydb)> dfs -mkdir /user/hive/warehouse/mydb.db/t6/class=job120;
  
  # 直接从 hadoop 端传数据
  hadoop fs -put ihivedata/intdata /user/hive/warehouse/mydb.db/t6/class=job120
  
  # 此时再 show partitions t6; 会发现并没有此分区
  ---
  partition
  class=job1
  class=job110
  class=job2
  class=job3
  class=job4
  
  # 此时就需要手动'激活'此分区, 加入了就有了
  hive (mydb)> alter table t6 add partition(class='job120');
  ---
  hive (mydb)> show partitions t6;
  ---
  partition
  class=job1
  class=job110
  class=job120
  class=job2
  class=job3
  class=job4
  
  # 查看分区信息
  hive (mydb)> select * from t6 where class='job110';
  OK
  t6.id	t6.class
  1	job110
  2	job110
  3	job110
  4	job110
  5	job110

4.3.4 复合分区

基本操作

• 创建数据文件partition_table.dat
• 创建表
  • create table t7(name string,age int)partitioned by(class string,city string)row format delimited fields terminated by '\t' stored as TEXTFILE;
• 在 Hive 下加载数据到分区
  • load data local inpath 'ihivedata/partidata' into table t7 partition(class='job1',city='beijing');
  • load data local inpath 'ihivedata/partidata' into table t7 partition(class='job1',city='shanghai');
  • load data local inpath 'ihivedata/partidata' into table t7 partition(class='job2',city='ss');
  • 注意: 多级分区其实就是多级目录
    • 越靠近左边, 目录层级越高;
    • 越靠近右边, 目录层级越低;
  • load 数据到多级分区, load层级必须和整个层级数量相同
    • 也就是说, 如果分区有2层, 传数据的时候, 也必须传2层分区, 并且层级顺序必须一致
• 从Linux 本地直接导数据到分区
  • 可以直接在 hadoop UI 页面, 查看路径, 然后直接传到此路径中
  • hadoop fs -put ihivedata/partidata /user/hive/warehouse/mydb.db/t7/class=job1/city=beijing/p2
• 查看数据
  • select * from partition_table
  • select count(*) from partition_table
• 删除表
  • drop table partition_table
• 工作中 用的最多的是 外部表 + 分区表

4.4 桶表 - 主要用于抽样查询

桶表的基本操作

• 创建桶表完整过程

#创建分桶表
drop table stu_buck;
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 ',';


#设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数
set hive.enforce.bucketing = true;
set mapreduce.job.reduces=4;

#开始往创建的分通表插入数据(插入数据需要是已分桶, 且排序的)
#可以使用distribute by(sno) sort by(sno asc)   或是排序和分桶的字段相同的时候使用Cluster by(字段)
#注意使用cluster by  就等同于分桶+排序(sort)

insert into table stu_buck
select Sno,Sname,Sex,Sage,Sdept from student distribute by(Sno) sort by(Sno asc);

insert overwrite table stu_buck
select * from student distribute by(Sno) sort by(Sno asc);

insert overwrite table stu_buck
select * from student cluster by(Sno);
-----
以上3者效果一样的

• 保存select查询结果的几种方式：

  • 将查询结果保存到一张新的hive表中

    create table t_tmp
    as
    select * from t_p;

2. 将查询结果保存到一张已经存在的hive表中

   insert into  table t_tmp
   select * from t_p;

3. 将查询结果保存到指定的文件目录（可以是本地，也可以是hdfs）

   insert overwrite local directory '/home/hadoop/test'
   select * from t_p;
   
   insert overwrite directory '/aaa/test'
   select * from t_p;

• 数据加载到桶表时，会对字段取hash值，然后与桶的数量取模。把数据放到对应的文件中。

  • 所以顺序是打乱的, 不是原始 t1的数据顺序

• 查看数据

  • 可以直接select * 查看全部

  • 也可以直接单独查看每个桶的数据

    hive (mydb)> dfs -cat /user/hive/warehouse/mydb.db/bucket_table/000000_0;
    4
    hive (mydb)> dfs -cat /user/hive/warehouse/mydb.db/bucket_table/000001_0;
    5
    1
    hive (mydb)> dfs -cat /user/hive/warehouse/mydb.db/bucket_table/000002_0;
    2
    hive (mydb)> dfs -cat /user/hive/warehouse/mydb.db/bucket_table/000003_0;
    3

• 修改桶的个数

  • alter table bucket_table clustered by (id) sorted by(id) into 10 buckets;
    • 但是这样修改之后, 生成的是原来的 copy, 并且里面的数据也很奇怪, 不知道是按照什么来执行的? :TODO

• 注意：

  • 物理上，每个桶就是表(或分区）目录里的一个文件
  • 一个作业产生的桶(输出文件)和reduce任务个数相同

• 桶表工作中容易遇到的错误

  • 向桶表中插入其它表查出的数据的时候, 必须指定字段名, 否则会报字段不匹配.

    FAILED: SemanticException [Error 10044]: Line 1:12 Cannot insert into target table because column number/types are different 'bucket_table': Table insclause-0 has 1 columns, but query has 2 columns.
    
    # 应该是这样
    insert into bucket_table select id from t6;

※ 桶表的抽样查询

• 桶表的抽样查询
  • select * from bucket_table tablesample(bucket 1 out of 4 on id);
• tablesample是抽样语句
  • 语法解析：TABLESAMPLE(BUCKET x OUT OF y)
  • y必须是table总bucket数的倍数或者因子。
  • hive根据y的大小，决定抽样的比例。
  • 例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。x表示从哪个bucket开始抽取。
  • 例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取（32/16=）2个bucket的数据，分别为第3个bucket和第（3+16=）19个bucket的数据。

5. Hive 视图的操作

• 使用视图可以降低查询的复杂度
• 视图的创建
  • create view v1 AS select t1.name from t1;
• 视图的删除
  • drop view if exists v1;

6. Hive 索引的操作

• 创建索引

  • create index t1_index on table t1(id) as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred rebuild in table t1_index_table;
  • t1_index: 索引名称
  • as: 指定索引器
  • t1_index_table: 要创建的索引表

• 显示索引

  • show formatted index on t1;

• 重建索引

  • alter index t1_index on t1 rebuild;

  • 重建完索引之后, 查看 t1_index_table 这张表, 就存了t1表文件具体的位置, 最后一列t1_index_table._offsets是 索引的偏移量, 类似于指针, 偏移量是索引的精髓

    hive (mydb)> select * from t1_index_table;
    OK
    t1_index_table.id	t1_index_table._bucketname	t1_index_table._offsets
    1	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1/intdata	[0]
    2	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1/intdata	[2]
    3	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1/intdata	[4]
    4	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1/intdata	[6]
    5	hdfs://cs1:9000/user/hive/warehouse/mydb.db/t1/intdata	[8]

• 分区字段本质上其实就是索引

7. 装载数据

7.1 普通装载数据:

• 从本地 put

• 从 hive cp

• 从文件中装载数据

  hive>LOAD DATA [LOCAL] INPATH '...' [OVERWRITE] INTO TABLE t2 [PARTITION (province='beijing')];

• 通过查询表装载数据

  # 方式1
  hive>INSERT OVERWRITE TABLE t2 PARTITION (province='beijing') SELECT * FROM xxx WHERE xxx;
  
  # 方式2
  hive>FROM t4 
   INSERT OVERWRITE TABLE t3 PARTITION (...) SELECT ...WHERE... 
   INSERT OVERWRITE TABLE t3 PARTITION (...) SELECT ...WHERE... 
   INSERT OVERWRITE TABLE t3 PARTITION (...) SELECT ...WHERE...;
   
   # 方式3
   直接插入数据, 也会转化为文件的形式, 存在表的目录下
  - insert into table_name  values(xxx); 
  
  # 方式4
   直接传文件
   - load data (local) inpath ‘ xxx’ into table t_1;

7.2动态装载数据

• 不开启动态装载时

  hive>INSERT OVERWRITE TABLE t3 PARTITION(province='bj', city='bj') 
  SELECT t.province, t.city FROM temp t WHERE t.province='bj';

• 开启动态分区支持

  hive>set hive.exec.dynamic.partition=true;
  hive>set hive.exec.dynamic.partition.mode=nostrict;
  hive>set hive.exec.max.dynamic.partitions.pernode=1000;

• 把 t6 表的所有的字段 (包括分区字段) 加载进 t9 对应的分区

  hive (mydb)> insert overwrite table t9 partition(class) select id,class from t6;

• 单语句建表并同时装载数据

  hive>CREATE TABLE t4 AS SELECT ....

8. 导出数据

• 在hdfs之间复制文件(夹)
  • hadoop fs -cp source destination
  • hive> dfs -cp source destination
  • 案例: hive> dfs -get /user/hive/warehouse/mydb.db/t9 /root/t9;
    • 从 hdfs 复制到本地
• 使用DIRECTORY
  • hive>INSERT OVERWRITE 【LOCAL】 DIRECTORY '...' SELECT ...FROM...WHERE ...;
  • 案例:通过查询导出到 t9, 走的 MapReduce
    • 导到到 hdfs: insert overwrite directory "/home/t9" select * from t9;
    • 导出到本地: insert overwrite local directory "/home/ap/t9" select * from t9;

9. 读模式&写模式

• RDBMS是写模式
• Hive是读模式

10. 完整建表语句语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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]
    [SKEWED BY (col_name, col_name, ...) 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 (...)]   ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]     
  [AS select_statement]  (Note: not supported when creating external tables.)

11. 文件格式

• TextFile
• SequenceFile
• RCFile
• ORC

11.1 默认存储格式就是 TextFile

• 存储空间消耗比较大，
• 并且压缩的text 无法分割和合并
• 查询的效率最低,可以直接存储，
• 加载数据的速度最高

11.2 使用SequenceFile存储

• 存储空间消耗大
• 压缩的文件可以分割和合并
• 查询效率高
• 需要通过text文件转化来加载

hive> 
create table test2(str STRING) STORED AS SEQUENCEFILE;  

set hive.exec.compress.output=true;

set mapred.output.compress=true;

set mapred.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

set io.seqfile.compression.type=BLOCK;

set io.compression.codecs=com.hadoop.compression.lzo.LzoCodec;
INSERT OVERWRITE TABLE test2 SELECT * FROM test1;

注意点: SequenceFile 类型的表, 不能直接导入数据文件, 只能通过从他表查询

• insert overwrite table t2 select * from t1;

  # 查看此 'SequenceFile' 表
  hive (db2)> dfs -cat /user/hive/warehouse/db2.db/t2/000000_0 ;
  SEQ"org.apache.hadoop.io.BytesWritableorg.apache.hadoop.io.Text*org.apache.hadoop.io.compress.DefaultCodec���/*<bb�m�?x�c453x�c464x�c475x�c486x�c497hive (db2)>

11.3 使用RCFile存储

RCFILE是一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。

• rcfile 存储空间最小
• 查询的效率最高
• 需要通过text文件转化来加载
• 加载的速度最低

hive> 
create table test3(str STRING)  STORED AS RCFILE;  

set hive.exec.compress.output=true;

set mapred.output.compress=true;

set mapred.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;

set io.compression.codecs=com.hadoop.compression.lzo.LzoCodec;

INSERT OVERWRITE TABLE test3 SELECT * FROM test1;

注意点: RCFile 也只能从其它表导入数据

hive (db2)> dfs -cat /user/hive/warehouse/db2.db/t3/000000_0;
RCF*org.apache.hadoop.io.compress.DefaultCodechive.io.rcfile.column.number1

11.4 使用ORC存储(最好的一种格式)

是一种针对 RCFile 优化的格式

主要特点: 压缩, 索引, 单文件输出

hive> 
create table t1_orc(id int, name string) row format delimited fields terminated by '\t' stored as orc 

tblproperties("orc.compress"="ZLIB");

ALTER TABLE ... [PARTITION partition_spec] SET FILEFORMAT ORC;

# 也可以改为其它的, 修改的语法就是这样
alter table t1 set fileformat textfile;

SET hive.default.fileformat=Orc;

insert overwrite table t1_orc select * from t1;

注意点:

• ORC 也只能从其它表导入数据
• 占用空间大, 一个 block 有256M, 之前2种都是128M

hive (db2)> dfs -cat /user/hive/warehouse/db2.db/t4/000000_0;
ORC
P+
P	�6�b�``���ь@�H�
                       1q01-
P
PK
# ("
       id0P:
P@�;��"
       (0��ORChive

12. 序列化 & 反序列化 (Hive SerDe)

12.1 SerDe

• What is a SerDe?
  • SerDe 是 “Serializer and Deserializer.”的缩写
  • Hive 使用 SerDe和FileFormat进行行内容的读写.
• Hive序列化流程
  • 从 HDFS 上读入文件 (反序列化)
    • HDFS文件 --> InputFileFormat --> <key, value> --> Deserializer --> 行对象
  • 写出到 HDFS (序列化)
    • 行对象 --> Serializer --> <key, value> --> OutputFileFormat --> HDFS文件
  • 注意: 数据全部存在在value中，key内容无意义

• Hive 使用如下FileFormat 类读写 HDFS files:

  • TextInputFormat/HiveIgnoreKeyTextOutputFormat: 读写普通HDFS文本文件.
  • SequenceFileInputFormat/SequenceFileOutputFormat: 读写SequenceFile格式的HDFS文件
  • ….

• Hive 使用如下SerDe 类(反)序列化数据:

  • MetadataTypedColumnsetSerDe: 读写csv、tsv文件和默认格式文件
  • ThriftSerDe: 读写Thrift 序列化后的对象.
  • DynamicSerDe: 读写Thrift序列化后的对象, 不过不需要解读schema中的ddl.

12.2 使用CSV Serde

CSV格式的文件也称为逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号。在本文中的CSV格式的数据就不是简单的逗号分割的），其文件以纯文本形式存储表格数据（数字和文本）。CSV文件由任意数目的记录组成，记录间以某种换行符分隔；每条记录由字段组成，字段间的分隔符是其它字符或字符串，最常见的是逗号或制表符。通常，所有记录都有完全相同的字段序列。
默认的分隔符是

DEFAULT_ESCAPE_CHARACTER \
DEFAULT_QUOTE_CHARACTER “ —如果没有，则不需要指定
DEFAULT_SEPARATOR ,

CREATE TABLE csv_table(a string, b string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("separatorChar"="\t", "quoteChar"="'", "escapeChar"="\\")  STORED AS TEXTFILE;

# separatorChar：分隔符
# quoteChar：引号符
# escapeChar：转义符


>>  :TODO 创建表没成功, 用到时再说

13. Lateral View 语法

lateral view用于和split, explode等UDTF一起使用，它能够将一行数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。lateral view首先为原始表的每行调用UDTF，UTDF会把一行拆分成一或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。

lateral: 侧面, 横切面

Lateral View: 切面表

• 创建表

  • create table t8(name string,nums array\<int>)row format delimited fields terminated by “\t” COLLECTION ITEMS TERMINATED BY ‘:’;

• 数据切割

  • SELECT name,new_num FROM t8 LATERAL VIEW explode(nums) num AS new_num;
  • select name,id from class_test lateral view explode(student_id_list) list as id;
  • 注意: as 前面的 list 貌似是可以随表起名的

• 效果演示

  hive (default)> select * from class_test;
  OK
  class_test.name	class_test.student_id_list
  zhangsan	[24,25,27,37]
  lisi	[28,39,23,43]
  wangwu	[25,23,2,54]
  ---
  hive (default)> select name,id from class_test lateral view explode(student_id_list) list as id;
  OK
  name	id
  zhangsan	24
  zhangsan	25
  zhangsan	27
  zhangsan	37
  lisi	28
  lisi	39
  lisi	23
  lisi	43
  wangwu	25
  wangwu	23
  wangwu	2
  wangwu	54

14. Hive的高级函数

14.1 简介

• 简单查询
  • select … from…where…
• 使用各种函数
  • hive>show functions;
    • 展示所有函数
  • hive>describe function xxx;
    • 详细描述函数用法
• LIMIT语句
• 列别名
• 嵌套select语句

14.2 高级函数分类

• 标准函数

  • reverse()
  • upper()

• 聚合函数

  • avg()

  • sum()

    select avg(id) from t3;
    select sum(id) from t3;
    
    # 最简答的嵌套
    hive (mydb)> select t.id from (select * from t1 where id > 3)t;
    
    # 最简单的 group by
    
    # if else 的效果
    select id, 
    
    case 
    when id <= 2 
    	then 'low'
    when id>=3 and id <4 
    	then 'middle'
    when id>=4 and id <5 
    	then 'high'
    else 
    	'very high'
    end 
    
    as 
    id_highly 
    from t1;
    
    ----执行结果----
    id	id_highly
    1	low
    2	low
    3	middle
    4	high
    5	very high
    
    --------------------
    
    select sid,
    case course
    when 'yuwen' then score
    else '0'
    end
    (别名) yuwen
    
    case course
    when 'shuxue' then score
    else '0'
    end
    (别名) shuxue
    
    
    
    # cast 转换函数, 大概是这么用
    hive (mydb)> select id from t1 where cast(id AS FLOAT) <3.0;
    OK
    id
    1
    2

    - ![image-20180619150824031](http://p6i5vzkfk.bkt.clouddn.com/study/2018-06-19-070824.png)
    

首先当前不存在的列补0, 然后按照学号分组求和

​

• 然后按照 sid 分组求和/求最大值, 就可以了
• 同一列不同的放在不同的列上, 常用的方法

---

面试题4

array_contains()

desc function array_contains()

• 

• 自定义函数

  • UDF

---

15. Hive 性能调优

15.1 什么时候可以避免执行MapReduce？

• select * or select field1,field2

• limite 10

• where语句中只有分区字段

• 使用本地set hive.exec.mode.local.auto=true;

  select * from t3;
  select name,age from t3;
  select name,age from t3 limit 2;
  select name,age from t3 where age=25;
  # 当 where 是本地字段(列中的字段), 是不走 MR的

• group by语句：

  • 通常和聚合函数一起使用，按照一个或者多个列对结果进行分组，然后对每组执行聚合操作
• having语句：
  • 限制结果的输出
• hive将查询转化为MapReduce执行，hive的优化可以转化为mapreduce的优化！

15.2 hive是如何将查询转化为MapReduce的？-EXPLAIN的使用

• hive对sql的查询计划信息解析

• EXPLAIN SELECT COUNT(1) FROM T1;

• EXPLAIN EXTENDED

  • 显示详细扩展查询计划信息

  • EXPLAIN EXTENDED SELECT COUNT(1) FROM T1;

  • 为啥我的 explain extended 只有固定的几行?

    • 因为这个 count 没有调动 MR, 用 sum 就会启用 MR, 会出现长长的 log

    ---不会启用 MR
    hive (mydb)> explain EXTENDED select count(1) from t9;
    ---
    Explain
    STAGE DEPENDENCIES:
      Stage-0 is a root stage
      ...
    -----------------------------
    ---这里会启用 MR
    hive (mydb)> explain EXTENDED select sum(id) from t9;
    ---
    Explain
    STAGE DEPENDENCIES:
      Stage-1 is a root stage
      Stage-0 depends on stages: Stage-1
    
    STAGE PLANS:
      Stage: Stage-1
      ....

15.3 性能调优

15.3.1 本地mr

• 本地模式设置方式：

  • set mapred.job.tracker=local;
    • mapper 的本地模式, 只有在开发中才会使用
  • set hive.exec.mode.local.auto=true;
    • Hive 的执行模式
    • 可以用在生产中, 因为是自动模式, 可根据参数变化
    • 设置这里才会转成 local hadoop
    • 按照这里设定的规则hive.exec.mode.local.auto.input.files.max
  • 测试 select 1 from wlan limit 5;

• 下面两个参数是local mr中常用的控制参数:

  • hive.exec.mode.local.auto.inputbytes.max默认134217728

    • 设置local mr的最大输入数据量,当输入数据量小于这个值的时候会采用local mr的方式

  • hive.exec.mode.local.auto.input.files.max默认是4

    • 设置local mr的最大输入文件个数,当输入文件个数小于这个值的时候会采用local mr的方式

    • 大于此数时, 就不会转化为 local hadoop

      Cannot run job locally: Number of Input Files (= 6) is larger than hive.exec.mode.local.auto.input.files.max(= 4)

    • 可以这样修改local mr的最大输入文件个数值, 主要在调试阶段使用

      hive (mydb)> set hive.exec.mode.local.auto.input.files.max=8;
      
      # 这样设置了之后, 只要文件数<=8, 就会在本地运行
      Job running in-process (local Hadoop)

15.3.2 开启并行计算

• 开启并行计算,增加集群的利用率
  • set hive.exec.parallel=true;

15.3.3 设置严格模式

• 设置严格模式
  • set hive.mapred.mode=strict;
• 设置非严格模式
  • set hive.mapred.mode=nonstrict;
• strict可以禁止三种类型的查询：
  • 强制分区表的where条件过滤
  • Order by语句必须使用limit
    • hive (mydb)> select id from t9 where class='job110' order by id limit 3;
  • 限制笛卡尔积查询

15.3.4 调整mapper和reducer的数量

• 调整mapper和reducer的数量
  • 太多map导致启动产生过多开销
  • marpred.min.split.size
  • 按照输入数据量大小确定reducer数目,
    • `set mapred.reduce.tasks= 默认3
    • dfs -count /分区目录/*
    • hive.exec.reducers.max设置阻止资源过度消耗
• JVM重用
  • 小文件多或task多的业务场景
  • set mapred.job.reuse.jvm.num.task=10
  • 会一直占用task槽

15.3.5 排序方面的优化

• order by 语句：是全局排序, 用的比较多

  # 加个 desc 就是倒序排序
  hive (mydb)> select id from bucket_table order by id desc limit 10;
  Automatically selecting local only mode for query

  sort by 语句： 是单reduce排序

  • 一般与 distribute by结合使用

• distribute by语句：类似于分桶，根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列

  • 与 sort by 结合用的比较多, 在每个分区内有序

    # 注意此处 distribute 的作用
    hive (mydb)> select id from bucket_table distribute by id sort by id desc limit 10;

• cluster by语句：

  • select * from t9 cluster by id;
  • 可以确保类似的数据的分发到同一个reduce task中，并且保证数据有序, 防止所有的数据分发到同一个reduce上，导致整体的job时间延长

• cluster by语句的等价语句：

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

15.3.6 Map-side聚合

可以直接在 .hiverc中配置

• set hive.map.aggr=true;
• 这个设置可以将顶层的聚合操作放在Map阶段执行，从而减轻清洗阶段数据传输和Reduce阶段的执行时间，提升总体性能。:TODO 不太懂
• 缺点：该设置会消耗更多的内存。
• 执行select count(1) from wlan;

15.3.6 Join 优化

• 优先过滤后再进行 Join 操作，最大限度的减少参与 join 的数据量
• 小表 join 大表，最好启动 mapjoin
  • 在使用写有 Join 操作的查询语句时有一条原则:应该将条目少的表/子查询放在 Join 操作 符的左边。
• Join on 的条件相同的话，最好放入同一个 job，并且 join 表的排列顺序从小到大

在编写 Join 查询语句时，如果确定是由于 join 出现的数据倾斜，那么请做如下设置:

set hive.skewjoin.key=100000; // 这个是 join 的键对应的记录条数超过这个值则会进行

分拆，值根据具体数据量设置

set hive.optimize.skewjoin=true; // 如果是 join 过程出现倾斜应该设置为 true

16. 表连接 (只支持等值连接)

16.1 简介

• INNER JOIN

  • 两张表中都有，且两表符合连接条件
  • select t1.name,t1.age,t9.age from t9 join t1 on t1.name=t9.name;

• LEFT OUTER JOIN

  • 左表中符合where条件出现，右表可以为空
  • 从左表返回所有的行(字段), 右表没有匹配where 条件的话返回null

• RIGHT OUTER JOIN

  • 右表中符合where条件出现，左表可以为空

• FULL OUTER JOIN

  • 返回所有表符合where条件的所有记录，没有NULL替代

• LEFT SEMI-JOIN

  • 左表中符合右表on条件出现，右表不出现

  • Hive 当前没有实现 IN/EXISTS 子查询，可以用 LEFT SEMI JOIN 重写子查询语句。LEFT SEMI JOIN 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

    SELECT a.key, a.value FROM a
    WHERE a.key in (SELECT b.key FROM B);
    
    # 可以被重写为：
    SELECT a.key, a.val FROM a LEFT SEMI JOIN b on (a.key = b.key)

• 笛卡尔积

  • 是m x n的结果

• map-side JOIN

  • 只有一张小表，在mapper的时候将小表完全放在内存中
  • select /+ mapjoin(t9) /t1.name,t1.age from t9 JOIN t1on t1.name=t9.name;

16.2 代码测试

关于hive中的各种join

准备数据
1,a
2,b
3,c
4,d
7,y
8,u

2,bb
3,cc
7,yy
9,pp



建表：
create table a(id int,name string)
row format delimited fields terminated by ',';

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

导入数据：
load data local inpath '/home/hadoop/a.txt' into table a;
load data local inpath '/home/hadoop/b.txt' into table b;


实验：
** inner join
select * from a inner join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 7     | y       | 7     | yy      |
+-------+---------+-------+---------+--+





**left join
select * from a left join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
+-------+---------+-------+---------+--+





**right join
select * from a right join b on a.id=b.id;





**
select * from a full outer join b on a.id=b.id;
+-------+---------+-------+---------+--+
| a.id  | a.name  | b.id  | b.name  |
+-------+---------+-------+---------+--+
| 1     | a       | NULL  | NULL    |
| 2     | b       | 2     | bb      |
| 3     | c       | 3     | cc      |
| 4     | d       | NULL  | NULL    |
| 7     | y       | 7     | yy      |
| 8     | u       | NULL  | NULL    |
| NULL  | NULL    | 9     | pp      |
+-------+---------+-------+---------+--+


**
select * from a left semi join b on a.id = b.id;
+-------+---------+--+
| a.id  | a.name  |
+-------+---------+--+
| 2     | b       |
| 3     | c       |
| 7     | y       |
+-------+---------+--+

17. Hive自定义函数 & Transform

17.1 自定义函数类别

• UDF
  • 作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）
• UDAF
  • (用户定义聚集函数)：接收多个输入数据行，并产生一个输出数据行。（count，max）

17.2 UDF 开发实例

17.2.1 简单入门

• 先开发一个java类，继承UDF，并重载evaluate方法

  package com.rox.hive.udf;
  import org.apache.hadoop.hive.ql.exec.UDF;
  public class ToLowerCase extends UDF {
  	
  	// 必须是 public
  	public String evaluate(String field) {
  		String res = field.toLowerCase();
  		return res;
  	}
  }

• 打成jar包上传到服务器

• 将jar包添加到hive的classpath

  • hive>add JAR /home/ap/udf.jar;

• 创建临时函数与开发好的java class关联

  • Hive>create temporary function tolowercase as 'com.rox.hive.udf.ToLowerCase';

• 即可在hql中使用自定义的函数strip

  • select tolowercase(name) from t_1..

17.2.2 稍稍复杂

# 需求: 通过一些手机号判断手机区域
1364535532,10
1374535532,42
1384535532,34
1364535532,45
1384535532,22
136-beijing
137-shanghai
138-guangzhou
-----

## 1.编写 UDF
	public static HashMap<String, String> provinceMap = new HashMap<String, String>();
	static {
		provinceMap.put("136", "beijing");
		provinceMap.put("136", "shanghai");
		provinceMap.put("136", "guangzhou");
	}

	public String evaluate(String phoneNum) {
		return provinceMap.get(phoneNum.substring(0, 3)) == null ? "huoxing" : provinceMap.get(phoneNum.substring(0, 3));
	}
	
## 2.打包上传, 添加到 classpath, 创建临时函数

## 3.创建表,加载数据
create table flow_t(pnum string,flow int)
row format delimited fields terminated by ',';

load data local inpath '/home/ap/ihivedata/flow.tmp' into table flow_t;

## 4.使用
0: jdbc:hive2://cs2:10000> select pnum,tolow(pnum),flow from flow_t;
+-------------+------------+-------+
|    pnum     |    _c1     | flow  |
+-------------+------------+-------+
| 1364535532  | guangzhou  | 10    |
| 1374535532  | huoxing    | 42    |
| 1384535532  | huoxing    | 34    |
| 1364535532  | guangzhou  | 45    |
| 1384535532  | huoxing    | 22    |
+-------------+------------+-------+

17.2.3 有些复杂

# 解析 json 数据表 rating.json
## 1. 写 udf

// com.rox.json.MovieRateBean 
package com.rox.json;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class MovieRateBean {
	private String movie;
	private String rate;
	private String timeStamp;
	private String uid;
	@Override
	public String toString() {
		return movie + "\t" + rate + "\t"
				+ timeStamp + "\t" + uid;
	}
}

// com.rox.json.JsonParser 
package com.rox.json;
import java.io.IOException;
import org.apache.hadoop.hive.ql.exec.UDF;
import com.fasterxml.jackson.databind.ObjectMapper;
public class JsonParser extends UDF {
	public String evaluate(String jsonLine) {
		ObjectMapper objectMapper = new ObjectMapper();
		try {
			MovieRateBean bean = objectMapper.readValue(jsonLine, MovieRateBean.class);
			return bean.toString();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return "";
	}
}
====================================================
## 2.打包上传, 添加到 classpath, 创建临时函数, 检查是否成功
show functions;

## 3.创建表,加载数据
create table t_json(line string) row format delimited;

load data local inpath '/home/ap/ihivedata/flow.log' into table t_json;

## 4.检查数据
select * from t_json limit 10;

## 5.调用函数
0: jdbc:hive2://cs2:10000> select jsonparser(line)parsedline  from t_json limit 10;
+---------------------+
|     parsedline      |
+---------------------+
| 1193	5	978300760	1  |
| 661	3	978302109	1   |
| 914	3	978301968	1   |
| 3408	4	978300275	1  |
| 2355	5	978824291	1  |
| 1197	3	978302268	1  |
| 1287	5	978302039	1  |
| 2804	5	978300719	1  |
| 594	4	978302268	1   |
| 919	4	978301368	1   |
+---------------------+
// 但是这样只是把每一行解析出来了,
    
    
## 6.删除原来的表    
drop table if exists t_rating;

## 7.重新创建一个表
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';

## 8.根据查出来的每一行, 按照 '\t'分割, 然后再插入到表中
create table  t_rating as
select 
split(jsonparser(line),'\t')[0]as movieid, split(jsonparser(line),'\t')[1] as rate, split(jsonparser(line),'\t')[2] as timestring, split(jsonparser(line),'\t')[3] as uid   
from t_json limit 10;

// 但是执行结果会报错,不知道为啥,难道是 java 代码的问题? :TODO


-------
## 9.内置json函数
select get_json_object(line,'$.movie') as moive,get_json_object(line,'$.rate') as rate  from rat_json limit 10;

17.3 Transform实现

# 1、先加载rating.json文件到hive的一个原始表 t_json
create table rat_json(line string) row format delimited;
load data local inpath '/home/ap/rating.json' into table t_json;

2、需要解析json数据成四个字段，插入一张新的表 t_rating
drop table if exists t_rating;
# 创建表
create table t_rating(movieid string,rate int,timestring string,uid string)
row format delimited fields terminated by '\t';

# 插入, 也可以直接创建  create table xx as + select...
insert overwrite table t_rating
select get_json_object(line,'$.movie') as moiveid, get_json_object(line,'$.rate') as rate, get_json_object(line,'$.timeStamp') as timestring,
get_json_object(line,'$.uid') as uid 
from t_json;

3. 写一个 python 脚本
vi weekday_mapper.py

#!/bin/python
import sys
import datetime

for line in sys.stdin:
  line = line.strip()
  movieid, rating, timestring,userid = line.split('\t')
  weekday = datetime.datetime.fromtimestamp(float(timestring)).isoweekday()
  print '\t'.join([movieid, rating, str(weekday),userid])
  
4. 保存文件, 然后将文件加入 hive 的 classpath
hive>add FILE /home/hadoop/weekday_mapper.py;

5. 此时可以直接创建新表
hive>create TABLE u_data_new as
SELECT
  TRANSFORM (movieid, rate, timestring,uid)
  USING 'python weekday_mapper.py'
  AS (movieid, rate, weekday,uid)
FROM t_rating;

6. 查询结果
## distinct看看有多少个不重复的数字
select distinct(weekday) from u_data_new limit 10;

18. 案例

18.1 广告推送-用户画像的介绍

一个广告推送平台的项目结构示意图

18.2 累计报表实现套路(面试)

题: 求每个月的累计访问次数

此处的访问次数可以换成工资,等等..

有如下访客访问次数统计表 t_access_times

访客	月份	访问次数
A	2015-01	5
A	2015-01	15
B	2015-01	5
A	2015-01	8
B	2015-01	25
A	2015-01	5
A	2015-02	4
A	2015-02	6
B	2015-02	10
B	2015-02	5
……	……	……

需要输出报表：t_access_times_accumulate

访客	月份	月访问总计	累计访问总计
A	2015-01	33	33
A	2015-02	10	43
…….	…….	…….	…….
B	2015-01	30	30
B	2015-02	15	45
…….	…….	…….	…….

解题代码

# 准备数据
vi t_access_times

A,2015-01,5
A,2015-01,15
B,2015-01,5
A,2015-01,8
B,2015-01,254
A,2015-01,5
A,2015-02,4
A,2015-02,6
B,2015-02,10
B,2015-02,5
----

# 建表
create table t_access_times(username string,month string,salary int)
row format delimited fields terminated by ',';

# 加载数据
load data local inpath '/home/ap/t_access_times' into table t_access_times;



1、第一步，先求个用户的月总金额
select username,month,sum(salary) as salary from t_access_times group by username,month;

+-----------+----------+---------+--+
| username  |  month   | salary  |
+-----------+----------+---------+--+
| A         | 2015-01  | 33      |
| A         | 2015-02  | 10      |
| B         | 2015-01  | 30      |
| B         | 2015-02  | 15      |
+-----------+----------+---------+--+

2、第二步，将月总金额表 自己连接 自己连接

create table aa as
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
A.username=B.username

+-------------+----------+-----------+-------------+----------+-----------+--+
| a.username  | a.month  | a.salary  | b.username  | b.month  | b.salary  |
+-------------+----------+-----------+-------------+----------+-----------+--+
| A           | 2015-01  | 33        | A           | 2015-01  | 33        |
| A           | 2015-01  | 33        | A           | 2015-02  | 10        |
| A           | 2015-02  | 10        | A           | 2015-01  | 33        |
| A           | 2015-02  | 10        | A           | 2015-02  | 10        |
| B           | 2015-01  | 30        | B           | 2015-01  | 30        |
| B           | 2015-01  | 30        | B           | 2015-02  | 15        |
| B           | 2015-02  | 15        | B           | 2015-01  | 30        |
| B           | 2015-02  | 15        | B           | 2015-02  | 15        |
+-------------+----------+-----------+-------------+----------+-----------+--+

3、第三步，从上一步的结果中
进行分组查询，分组的字段是a.username a.month
求月累计值：  将b.month <= a.month的所有b.salary求和即可
select A.username,A.month,max(A.salary) as salary,sum(B.salary) as accumulate
from 
(select username,month,sum(salary) as salary from t_access_times group by username,month) A 
inner join 
(select username,month,sum(salary) as salary from t_access_times group by username,month) B
on
A.username=B.username
where B.month <= A.month
group by A.username,A.month
order by A.username,A.month;


+-------------+----------+---------+-------------+
| a.username  | a.month  | salary  | accumulate  |
+-------------+----------+---------+-------------+
| A           | 2015-01  | 33      | 33          |
| A           | 2015-02  | 10      | 43          |
| B           | 2015-01  | 259     | 259         |
| B           | 2015-02  | 15      | 274         |
+-------------+----------+---------+-------------+

18.3 待做项目

19.注意点

1. 使用聚合函数, 后面一定要分组(group by xxx), group by 会自动去重
   • 如果 sql 语句中有 group by, 那么 select 后面必须有 group by 的字段, 或聚合函数
2. 使用order by 排序某个字段时, 必须在 select中出现此字段, 否则会找不到 :TODO
   • 待验证