[TOC]
# Hive操作
## 1 DDL操作
### 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]
~~~
> 说明：
1. CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
2. EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive 创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
3. LIKE 允许用户复制现有的表结构，但是不复制数据。
4. 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, ...)]
~~~
> 用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive通过 SerDe 确定表的具体的列的数据。
5、 STORED AS
> SEQUENCEFILE|TEXTFILE|RCFILE
> 如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
6、CLUSTERED BY
> 对于每一个表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中。
> 把表（或者分区）组织成桶（Bucket）有两个理由：
1. 获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接 （Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。
2. 使取样（sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
> 具体实例
1、 创建内部表mytable。
2、 创建外部表pageview。
3、 创建分区表invites。
> create table student_p(Sno int,Sname string,Sex string,Sage int,Sdept string)
> partitioned by(part string)
> row format delimited fields
> terminated by ','stored as textfile;
4、 创建带桶的表student。
5、 桶表抽样查询
~~~
Select * from student tablesample(bucket 1 out of 2 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的数据。
### 1.2 修改表
> 增加/删除分区
1) 语法结构
~~~
ALTER TABLE table_name ADD [IF NOT EXISTS] partition_spec [ LOCATION 'location1' ] partition_spec [ LOCATION 'location2' ] ...
partition_spec:
: PARTITION (partition_col = partition_col_value, partition_col = partiton_col_value, ...)
ALTER TABLE table_name DROP partition_spec, partition_spec,...
~~~
> 具体实例
~~~
alter table student_p add partition(part='a') partition(part='b');
~~~
2) 重命名表
> 语法结构
~~~
ALTER TABLE table_name RENAME TO new_table_name
~~~
> 具体实例
3) 增加/更新列
语法结构
~~~
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)
~~~
> 注：ADD是代表新增一字段，字段位置在所有列后面(partition列前)，REPLACE则是表示替换表中所有字段。
~~~
ALTER TABLE table_name
CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
~~~
> 具体实例
### 1.3 显示命令
~~~
show tables
show databases
show partitions
show functions
desc extended t_name;
desc formatted table_name;
~~~
## 2 DML操作
### 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 关键字
1) 如果使用了 OVERWRITE 关键字，则目标表（或者分区）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。
2) 如果目标表（分区）已经有一个文件，并且文件名和 filepath 中的文件名冲突，那么现有的文件会被新文件所替代。
> 具体实例
1、 加载相对路径数据。
2、 加载绝对路径数据。
3、 加载包含模式数据。
4、 OVERWRITE关键字使用。
### 2.2 Insert
#### 将查询结果插入Hive表
> 语法结构
> 利用查询语句，将查询结果插入新的表
~~~
INSERT OVERWRITE [INTO] TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
~~~
> 插入一条数据
~~~
INSERT INTO TABLE VALUES(XX,YY,ZZ);
~~~
> Multi Inserts多重插入:
~~~
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ...]
Select statement2] ...
~~~
> Dynamic partition inserts动态分区插入:
~~~
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement
~~~
> 具体实例
1、基本模式插入。
2、多插入模式。
3、自动分区模式。
> 使用了非严格模式
~~~
set hive.exec.dynamic.partition.mode=nonstrict
~~~
#### 导出表数据
> 语法结构
~~~
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... FROM ...
multiple inserts:
FROM from_statement
INSERT OVERWRITE [LOCAL] DIRECTORY directory1 select_statement1
[INSERT OVERWRITE [LOCAL] DIRECTORY directory2 select_statement2] ...
~~~
> 具体实例
1、导出文件到本地。
> 说明：
> 数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。用more命令查看时不容易看出分割符，可以使用: sed -e 's/\x01/|/g' filename来查看。
2、导出数据到HDFS。
### 2.3 SELECT
#### 基本的Select操作
> 语法结构
~~~
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list [HAVING condition]]
[CLUSTER BY col_list
| [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list]
]
[LIMIT number]
~~~
> 注：
1. order by 会对输入做全局排序，因此只有一个reducer，会导致当输入规模较大时，需要较长的计算时间。
2. sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。
3. distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。
4. Cluster by(字段) 除了具有Distribute by的功能外，还会对该字段进行排序。
> 因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by
> 分桶表的作用：最大的作用是用来提高join操作的效率；
> （思考这个问题：
> select a.id,a.name,b.addr from a join b on a.id = b.id;
> 如果a表和b表已经是分桶表，而且分桶的字段是id字段
> 做这个join操作时，还需要全表做笛卡尔积吗？）
> 注意：在hive中提供了一种“严格模式”的设置来阻止用户执行可能会带来未知不好影响的查询。
> 设置属性hive.mapred.mode 为strict能够阻止以下三种类型的查询：
1) 除非在where语段中包含了分区过滤，否则不能查询分区了的表。这是因为分区表通常保存的数据量都比较大，没有限定分区查询会扫描所有分区，耗费很多资源。
~~~
不允许：select *from logs;
允许：select * from logs where day=20151212;
~~~
2) 包含order by，但没有limit子句的查询。因为order by 会将所有的结果发送给单个reducer来执行排序，这样的排序很耗时。
3) 笛卡尔乘积；简单理解就是JOIN没带ON，而是带where的
> 具体实例
1、获取年龄大的3个学生。
2、查询学生信息按年龄，降序排序。
3、按学生名称汇总学生年龄。
## 3 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 joins）、外连接（outer joins）和（left/right joins）。Hive 不支持非等值的连接，因为非等值连接非常难转化到 map/reduce 任务。
> 另外，Hive 支持多于 2 个表的连接。
> 写 join 查询时，需要注意几个关键点：
1. 只支持等值join
> 例如：
~~~
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. LEFT，RIGHT 和 FULL OUTER 关键字用于处理 join 中空记录的情况
> 例如：
~~~
SELECT a.val, b.val FROM
a LEFT OUTER JOIN b ON (a.key=b.key)
~~~
> 对应所有 a 表中的记录都有一条记录输出。输出的结果应该是 a.val, b.val，当 a.key=b.key 时，而当
> b.key 中找不到等值的 a.key 记录时也会输出:
> a.val, NULL
> 所以 a 表中的所有记录都被保留了；
> “a RIGHT OUTER JOIN b”会保留所有 b 表的记录。
Join 发生在 WHERE 子句之前。如果你想限制 join 的输出，应该在 WHERE 子句中写过滤条件——或是在 join 子句中写。这里面一个容易混淆的问题是表分区的情况：
~~~
SELECT a.val, b.val FROM a
LEFT OUTER JOIN b ON (a.key=b.key)
WHERE a.ds='2009-07-07' AND b.ds='2009-07-07'
~~~
> 会 join a 表到 b 表（OUTER JOIN），列出 a.val 和 b.val 的记录。WHERE 从句中可以使用其他列作为过滤条件。但是，如前所述，如果 b 表中找不到对应 a 表的记录，b 表的所有列都会列出 NULL，包括 ds 列。也就是说，join 会过滤 b 表中不能找到匹配 a 表 join key 的所有记录。这样的话，LEFT OUTER 就使得查询结果与 WHERE 子句无关了。解决的办法是在 OUTER JOIN 时使用以下语法：
~~~
SELECT a.val, b.val FROM a LEFT OUTER JOIN b
ON (a.key=b.key AND
b.ds='2009-07-07' AND
a.ds='2009-07-07')
~~~
> 这一查询的结果是预先在 join 阶段过滤过的，所以不会存在上述问题。这一逻辑也可以应用于 RIGHT 和 FULL 类型的 join 中。
> Join 是不能交换位置的。无论是 LEFT 还是 RIGHT join，都是左连接的。
~~~
SELECT a.val1, a.val2, b.val, c.val
FROM a
JOIN b ON (a.key = b.key)
LEFT OUTER JOIN c ON (a.key = c.key)
~~~
> 先 join a 表到 b 表，丢弃掉所有 join key 中不匹配的记录，然后用这一中间结果和 c 表做 join。这一表述有一个不太明显的问题，就是当一个 key 在 a 表和 c 表都存在，但是 b 表中不存在的时候：整个记录在第一次 join，即 a JOIN b 的时候都被丢掉了（包括a.val1，a.val2和a.key），然后我们再和 c 表 join 的时候，如果 c.key 与 a.key 或 b.key 相等，就会得到这样的结果：NULL, NULL, NULL, c.val
> 具体实例
1、 获取已经分配班级的学生姓名。
2、 获取尚未分配班级的学生姓名。
3、 LEFT SEMI JOIN是IN/EXISTS的高效实现。
## 4 Hive Shell参数
### 4.1 Hive命令行
> 语法结构
~~~
hive [-hiveconf x=y]* [<-i filename>]* [<-f filename>|<-e query-string>] [-S]
~~~
> 说明：
1. -i 从文件初始化HQL。
2. -e从命令行执行指定的HQL
3. -f 执行HQL脚本
4. -v 输出执行的HQL语句到控制台
5. -p <port> connect to Hive Server on port number
6. -hiveconf x=y Use this to set hive/hadoop configuration variables.
> 具体实例
1、运行一个查询。
2、运行一个文件。
3、运行参数文件。
### 4.2 Hive参数配置方式
> Hive参数大全：
> https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties
> 开发Hive应用时，不可避免地需要设定Hive的参数。设定Hive的参数可以调优HQL代码的执行效率，或帮助定位问题。然而实践中经常遇到的一个问题是，为什么设定的参数没有起作用？这通常是错误的设定方式导致的。
> 对于一般参数，有以下三种设定方式：
1. 配置文件
2. 命令行参数
3. 参数声明
> 配置文件：Hive的配置文件包括
1. 用户自定义配置文件：$HIVE_CONF_DIR/hive-site.xml
2. 默认配置文件：$HIVE_CONF_DIR/hive-default.xml
> 用户自定义配置会覆盖默认配置。
> 另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。
> 配置文件的设定对本机启动的所有Hive进程都有效。
> 命令行参数：启动Hive（客户端或Server方式）时，可以在命令行添加-hiveconf param=value来设定参数，例如：
~~~
bin/hive -hiveconf hive.root.logger=INFO,console
~~~
> 这一设定对本次启动的Session（对于Server方式启动，则是所有请求的Sessions）有效。
> 参数声明：可以在HQL中使用SET关键字设定参数，例如：
~~~
set mapred.reduce.tasks=100;
~~~
> 这一设定的作用域也是session级的。
~~~
每个reducer task任务处理的数据量。默认为1G
set hive.exec.reducers.bytes.per.reducer=<number>
设置reducers task的最大个数
set hive.exec.reducers.max=<number>
设置reduce task的任务数
set mapred.reduce.tasks=<number>
~~~
> 总结：
> 上述三种设定方式的优先级依次递增。即参数声明覆盖命令行参数，命令行参数覆盖配置文件设定。注意某些系统级的参数，例如log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在Session建立以前已经完成了。
## 5 Hive函数
### 5.1 内置运算符
> 内容较多，见《Hive官方文档》
### 5.2 内置函数
> 内容较多，见《Hive官方文档》
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
> 测试各种内置函数的快捷方法：
1、创建一个dual表
~~~
create table dual(id string);
~~~
2、load一个文件（一行，一个空格）到dual表
3、select substr('angelababy',2,3) from dual;
### 5.3 Hive自定义函数和Transform
> 当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。
#### 5.3.1 自定义函数类别
> UDF 作用于单个数据行，产生一个数据行作为输出。（数学函数，字符串函数）
> UDAF（用户定义聚集函数）：接收多个输入数据行，并产生一个输出数据行。（count，max）
#### 5.3.2 UDF开发实例
1）简单UDF示例
1. 先开发一个java类，继承UDF，并重载evaluate方法
~~~
package cn.itcast.bigdata.udf
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
public final class Lower extends UDF{
public Text evaluate(final Text s){
if(s==null){return null;}
return new Text(s.toString().toLowerCase());
}
}
~~~
2) 打成jar包上传到服务器
3) 将jar包添加到hive的classpath
~~~
hive>add JAR /home/hadoop/udf.jar;
~~~
4) 创建临时函数与开发好的java class关联
~~~
Hive>create temporary function tolowercase as 'cn.itcast.bigdata.udf.ToProvince';
~~~
5) 即可在hql中使用自定义的函数tolowercase ip
~~~
Select tolowercase(name),age from t_test;
~~~
2) Json数据解析UDF开发
> 作业：
> 有原始json数据如下：
~~~
{"movie":"1193","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":"1197","rate":"3","timeStamp":"978302268","uid":"1"}
{"movie":"1287","rate":"5","timeStamp":"978302039","uid":"1"}
~~~
> 需要将数据导入到hive数据仓库中
> 我不管你中间用几个表，最终我要得到一个结果表：
| movie | rate | timestamp | uid |
| --- | --- | --- | --- |
| 1197 | 3 | 978302268 | 1 |
> 注：全在hive中完成，可以用自定义函数
~~~
package cn.itcast.bigdata.hive.udf;
//{"movie":"1721","rate":"3","timeStamp":"965440048","uid":"5114"}
public class MovieRateBean {
private String movie;
private String rate;
private String timeStamp;
private String uid;
public String getMovie() {
return movie;
}
public void setMovie(String movie) {
this.movie = movie;
}
public String getRate() {
return rate;
}
public void setRate(String rate) {
this.rate = rate;
}
public String getTimeStamp() {
return timeStamp;
}
public void setTimeStamp(String timeStamp) {
this.timeStamp = timeStamp;
}
public String getUid() {
return uid;
}
public void setUid(String uid) {
this.uid = uid;
}
@Override
public String toString() {
return movie + "\t" + rate + "\t" + timeStamp + "\t" + uid;
}
}
package cn.itcast.bigdata.hive.udf;
import java.io.IOException;
import org.apache.hadoop.hive.ql.exec.UDF;
import parquet.org.codehaus.jackson.JsonParseException;
import parquet.org.codehaus.jackson.map.JsonMappingException;
import parquet.org.codehaus.jackson.map.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 (Exception e) {
}
return "";
}
}
~~~
#### 5.3.3 Transform实现
> Hive的 TRANSFORM 关键字提供了在SQL中调用自写脚本的功能
> 适合实现Hive中没有的功能又不想写UDF的情况
> 使用示例1：下面这句sql就是借用了weekday_mapper.py对数据进行了处理.
~~~
CREATE TABLE u_data_new (
movieid INT,
rating INT,
weekday INT,
userid INT)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t';
add FILE weekday_mapper.py;
INSERT OVERWRITE TABLE u_data_new
SELECT
TRANSFORM (movieid , rate, timestring,uid)
USING 'python weekday_mapper.py'
AS (movieid, rating, weekday,userid)
FROM t_rating;
~~~
> 其中weekday_mapper.py内容如下
~~~
#!/bin/python
import sys
import datetime
for line in sys.stdin:
line = line.strip()
movieid, rating, unixtime,userid = line.split('\t')
weekday = datetime.datetime.fromtimestamp(float(unixtime)).isoweekday()
print '\t'.join([movieid, rating, str(weekday),userid])
~~~
### 5.4 HIVE特殊分隔符处理
> Hive对文件中的分隔符默认情况下只支持单字节分隔符，,默认单字符是\001。当然你也可以在创建表格时指定数据的分割符号。但是如果数据文件中的分隔符是多字符的，如下图：
~~~
01||zhangsan
02||lisi
03||wangwu
~~~
补充：hive读取数据的机制
1) 首先用inputformat的一个具体的实现类读取文件数据，返回一条条的记录（可以是行，或者是你逻辑中的“行”）
2) 然后利用SerDe<默认：
> org.apache.hadoop.hive.serde2.LazySimpleSerDe>的一个具体的实现类，对上面返回的一条条记录进行字段切割
#### 5.4.1使用RegexSerDe通过正则表达式来抽取字段
1) 建表
~~~
create table t_bi_reg(id string,name string)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties(
'input.regex'='(.*)\\|\\|(.*)',
'output.format.string'='%1$s%2$s'
)
stored as textfile;
~~~
2) 加载数据
~~~
01||zhangsan
02||lisi
03||wangwu
load data local inpath '/root/lianggang.txt' into table t_bi_reg;
~~~
3) 查询
~~~
hive> select * from t_bi_reg;
OK
01 zhangsan
02 lisi
03 wangwu
~~~
#### 5.4.2通过自定义inputformat解决特殊分隔符问题
> 其原理是在inputformat读取行的时候将数据中的“多字节分隔符”替换为hive默认的分隔符（ctrl+A 亦即 \001）或用于替代的单字符分隔符。以便hive在serde操作的时候按照默认的单字节分隔符进行字段抽取
~~~
package cn.itcast.bigdata.hive.inputformat;
import java.io.IOException;
import org.apache.hadoop.io.LongWritable;
import org.apache.hadoop.io.Text;
import org.apache.hadoop.mapred.FileSplit;
import org.apache.hadoop.mapred.InputSplit;
import org.apache.hadoop.mapred.JobConf;
import org.apache.hadoop.mapred.LineRecordReader;
import org.apache.hadoop.mapred.RecordReader;
import org.apache.hadoop.mapred.Reporter;
import org.apache.hadoop.mapred.TextInputFormat;
public class BiDelimiterInputFormat extends TextInputFormat {
@Override
public RecordReader<LongWritable, Text> getRecordReader(
InputSplit genericSplit, JobConf job, Reporter reporter)
throws IOException {
reporter.setStatus(genericSplit.toString());
MyDemoRecordReader reader = new MyDemoRecordReader(new LineRecordReader(job, (FileSplit) genericSplit));
// BiRecordReader reader = new BiRecordReader(job, (FileSplit)genericSplit);
return reader;
}
public static class MyDemoRecordReader implements RecordReader<LongWritable, Text> {
LineRecordReader reader;
Text text;
public MyDemoRecordReader(LineRecordReader reader) {
this.reader = reader;
text = reader.createValue();
}
@Override
public void close() throws IOException {
reader.close();
}
@Override
public LongWritable createKey() {
return reader.createKey();
}
@Override
public Text createValue() {
return new Text();
}
@Override
public long getPos() throws IOException {
return reader.getPos();
}
@Override
public float getProgress() throws IOException {
return reader.getProgress();
}
@Override
public boolean next(LongWritable key, Text value) throws IOException {
boolean next = reader.next(key, text);
if(next){
String replaceText = text.toString().replaceAll("\\|\\|", "\\|");
value.set(replaceText);
}
return next;
}
}
}
~~~
1) 打包成jar，放到$HIVE_HOME/lib下
2) 建表指明自定义的inputformat
~~~
create table t_lianggang(id string,name string)
row format delimited
fields terminated by '|'
stored as inputformat 'cn.itcast.bigdata.hive.inputformat.BiDelimiterInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
~~~
3) 加载数据
~~~
01||zhangsan
02||lisi
03||wangwu
load data local inpath '/root/lianggang.txt' into table t_lianggang;
~~~
4) 查询
~~~
hive> select * from t_lianggang;
OK
01 zhangsan
02 lisi
03 wangwu
~~~
## 6.hive严格模式
> hive提供了一个严格模式，可以防止用户执行那些可能产生意想不到的不好的效果的查询。即某些查询在严格模式下无法执行。通过设置hive.mapred.mode的值为strict，可以禁止3中类型的查询。
1) 查询一个分区表时
> 如果在一个分区表执行hive，除非where语句中包含分区字段过滤条件来显示数据范围，否则不允许执行。换句话说，就是用户不允许扫描所有的分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。 如果没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表：例如
~~~
hive> select * from t_patition;
FAILED: SemanticException [Error 10041]: No partition predicate found for Alias "t_patition" Table "t_patition"
~~~
> 注意查询的时候加上分区：
~~~
hive> select * from t_patition where country=’China’;
~~~
2) 带有order by的查询
> 对于使用了orderby的查询，要求必须有limit语句。因为orderby为了执行排序过程会讲所有的结果分发到同一个reducer中进行处理，强烈要求用户增加这个limit语句可以防止reducer额外执行很长一段时间：
~~~
hive> select * from student order by id;
FAILED: SemanticException 1:31 In strict mode, if ORDER BY is specified, LIMIT must also be specified. Error encountered near token 'id'
~~~
> 注意查询的时候加上limit：
~~~
select * from student order by id limit 10;
~~~
3) 限制笛卡尔积的查询
> 对关系型数据库非常了解的用户可能期望在执行join查询的时候不使用on语句而是使用where语句，这样关系数据库的执行优化器就可以高效的将where语句转换成那个on语句。不幸的是，hive不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况：
~~~
hive> SELECT * FROM fracture_act JOIN fracture_ads
> WHERE fracture_act.planner_id = fracture_ads.planner_id;
FAILED: Error in semantic analysis: In strict mode, cartesian product
is not allowed. If you really want to perform the operation,
+set hive.mapred.mode=nonstrict+
注意查询的时候使用join和on语句的查询：
hive> SELECT * FROM fracture_act JOIN fracture_ads
> ON (fracture_act.planner_id = fracture_ads.planner_id);
~~~
- hadoop
- linux基础
- Linux入门
- Linux进阶
- shell
- Zookeeper
- Zookeeper简介及部署
- Zookeeper使用及API
- Redis
- Redis简介安装部署
- Redis使用及API
- Java高级增强
- Java多线程增强
- Maven简介及搭建
- Hive
- Hive简介及安装
- Hive操作
- HIve常用函数
- Hive数据类型
- Flume
- Flume简介及安装
- flume 拦截器(interceptor)
- azkaban
- azKaban简介及安装
- Sqoop
- Sqoop简介及安装
- HDFS
- HDFS原理
- HDFS操作API
- MAPREDUCE原理
- MAPREDUCE图片资源
- MAPREDUCE加强
- HBASE
- HBASE简介及安装
- HBASE操作及API
- HBASE内部原理
- Storm
- Storm简介及安装
- Storm原理
- kafka
- kafka简介及安装
- kafka常用操作及API
- kafka原理
- kafka配置详解
- Scala
- Scala简介及安装
- Scala基础语法
- Scala实战