您的位置: 首页  >  文章  >  Hive笔记3-HQL操作

Hive笔记3-HQL操作

分类: 文章 • 2025-02-03 23:31:34

HQL操作

参考资料：

1、DDL操作

（1）显示命令

  • 显示数据库：show databases;
  • 创建数据库：CREATE DATABASE|SCHEMA [IF NOT EXISTS] <database name>
  • 删除数据库：DROP DATABASE StatementDROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
  • 进入数据库：use database_name;
  • 显示表：show tables;
  • 描述表结构：desc table_name;
  • 显示分区：show partitions;

（2）创建表

建表语法 
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 row_format

    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）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

  7. 字段类型

    • 基础数据类型：tinyint, smallint, int, bigint, float, decimal, boolean, varchar, char, string
    • 复合数据类型：struct, array, map
具体实例

  1. 创建内部表mytable

    hive> create table if not exists mytable(sid int,sname string)  
> row format delimited fields terminated by ',' stored as textfile;

    示例， 显示如下：

    Hive笔记3-HQL操作

  2. 创建外部表pageview

    hive> create external table if not exists pageview(  
> pageid int,  
> page_url string comment 'The page URL'  
> )  
> row format delimited fields terminated by ','  
> location 'hdfs://192.168.158.171:9000/user/hivewarehouse/';

  3. 创建分区表invites

    hive> 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

    hive> create table student(id int,age int,name string)  
> partitioned by(stat_data string)  
> clustered by(id) sorted by(age) into 2 buckets  
> row format delimited fields terminated by ',';

    注意：

    1. 分桶之前要执行命令hive.enforce.bucketiong=true;
    2. 要使用关键字clustered by 指定分区依据的列名，还要指定分为多少桶。
    3. 与分区不同的是，分区依据的不是真实数据表文件中的列，而是我们指定的伪列，但是分桶是依据数据表中真实的列而不是伪列。所以在指定分区依据的列的时候要指定列的类型，因为在数据表文件中不存在这个列，相当于新建一个列。而分桶依据的是表中已经存在的列，这个列的数据类型显然是已知的，所以不需要指定列的类型。

  5. 创建带复合数据类型的表

# array
create table person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
biansutao beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
select * from person;
# biansutao       ["beijing","shanghai","tianjin","hangzhou"]
# linan           ["changchu","chengdu","wuhan"]

# map
create table score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
# 数据
biansutao '数学':80,'语文':89,'英语':95
jobs '语文':60,'数学':80,'英语':99
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
select * from score;
# biansutao       {"数学":80,"语文":89,"英语":95}
# jobs            {"语文":60,"数学":80,"英语":99}

# struct
CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
1 english,80
2 math,89
3 chinese,95
# 入库
LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
# 查询
select * from test;
# 1       {"course":"english","score":80}
# 2       {"course":"math","score":89}
# 3       {"course":"chinese","score":95}

（3）修改表

内外部表转化
alter table table_name set TBLPROPROTIES ('EXTERNAL'='TRUE'); # 内部表转外部表
alter table table_name set TBLPROPROTIES ('EXTERNAL'='FALSE');# 外部表转内部表
增加/删除分区

语法结构

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');

重命名表

语法结构

ALTER TABLE table_name RENAME TO new_table_name

具体实例

hive> alter table student rename to student1;

Hive笔记3-HQL操作

增加/更新列

语法结构

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

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

具体实例

Hive笔记3-HQL操作

Hive笔记3-HQL操作

修改字段
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

2、DML操作

（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 中的文件名冲突，那么现有的文件会被新文件所替代。

具体实例

  1. 加载相对路径数据

hive> load data local inpath 'sc.txt' overwrite into table sc;

  1. 加载绝对路径数据

hive> load data local inpath '/home/hadoop/hivedata/students.txt' overwrite into table student;

  1. 加载包含模式数据

hive> load data inpath 'hdfs://mini1:9000/hivedata/course.txt' overwrite into table course;

(2)Insert

将查询结果插入Hive表

语法结构

INSERT OVERWRITE [INTO] 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] ...

Dynamic partition inserts:

INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement

具体实例

  1. 基本模式插入

    insert overwrite table student partition(stat_date='20140101') 
select id,age,name from student where stat_date='20131229';

  2. 多插入模式

    from student 
insert overwite table student partition(stat_date='20140102') 
select id,age,name from student where stat_date='20131229';
insert overwite table student partition(stat_date='20140103') 
select id,age,name from student where stat_date='20131229';

  3. 自动分区模式

    insert overwrite table student1 partition(stat_date) 
select id,age,name,stat_date from student where stat_date='20140101';
导出表数据

语法结构

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. 导出文件到本地

    hive> insert overwrite local directory '/home/hadoop/hivedata/outdata'  
> select * from student;

    说明：

    数据写入到文件系统时进行文本序列化，且每列用^A来区分，\n为换行符。用more命令查看时不容易看出分割符，

    可以使用: sed -e 's/\x01/|/g' filename来查看。

    如：sed -e 's/\x01/,/g' 000000_0

  2. 导出数据到HDFS

    hive> insert overwrite directory 'hdfs://mini1:9000/hivedata/outdatasc'  
> select * from sc;

（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根据distribute by指定的内容将数据分到同一个reducer。
  4. Cluster by 除了具有Distribute by的功能外，还会对该字段进行排序。因此，常常认为cluster by = distribute by + sort by

具体实例

  1. 获取年龄大的3个学生。
    hive> select sno,sname,sage from student order by sage desc limit 3;
  2. 查询学生信息按年龄，降序排序。
    hive> select sno,sname,sage from student sort by sage desc;

hive> select sno,sname,sage from student order by sage desc;  

hive> select sno,sname,sage from student distribute by sage;
  3. 按学生名称汇总学生年龄。
    hive> select sname,sum(sage) from student group by sname;

（4）综合示例

# 增加分区
insert overwrite table table_name partition (d='${pre_date}')

# 建表语句
# 进行分区，每个分区相当于是一个文件夹，如果是双分区，则第二个分区作为第一个分区的子文件夹
drop table if exists employees;  
create table  if not exists employees(  
       name string,  
       salary float,  
       subordinate array<string>,  
       deductions map<string,float>,  
       address struct<street:string,city:string,num:int>  
) partitioned by (date_time string, type string)
row format delimited
fields terminated by '\t'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/hive/...';

# hive桶
# 分区是粗粒度的，桶是细粒度的
# hive针对某一列进行分桶，对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中
create table bucketed_user(id int, name string)
clustered by (id) sorted by (name) into 4 buckets
row format delimited
fields terminated by '\t'
stored as textfile;
# 注意，使用桶表的时候我们要开启桶表
set hive.enforce.bucketing=true;
# 将employee表中的name和salary查询出来插入到表中
insert overwrite table bucketed_user select salary, name from employees

如果字段类型是string，则通过get_json_object提取数据；
如果字段类型是struct或map,则通过col['xx']方式提取数据；

3、Hive Join

语法结构

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 任务。

写 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)

是错误的。

tips:后续版本已经可以支持不等值

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。

而第一个例子的 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 表的记录。

5. 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 中。

6. 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. 获取已经分配班级的学生姓名
hive> select name,classname from student a join class b on (a.name=b.std_name);
  1. 获取尚未分配班级的学生姓名
hive> select name,classname from student a left join class b on (a.name=b.std_name) where b.std_name is null;
  1. LEFT SEMI JOIN是IN/EXISTS的高效实现
hive> select id,name from student a left semi join class b on (a.name=b.std_name);

4、视图和索引

创建视图的语法如下：

CREATE VIEW [IF NOT EXISTS] view_name [(column_name [COMMENT column_comment], ...) ]
[COMMENT table_comment]
AS SELECT ...

创建索引的语法如下：

CREATE INDEX index_name
ON TABLE base_table_name (col_name, ...)
AS 'index.handler.class.name'
[WITH DEFERRED REBUILD]
[IDXPROPERTIES (property_name=property_value, ...)]
[IN TABLE index_table_name]
[PARTITIONED BY (col_name, ...)]
[
   [ ROW FORMAT ...] STORED AS ...
   | STORED BY ...
]
[LOCATION hdfs_path]
[TBLPROPERTIES (...)]

5、常用函数

Hive笔记3-HQL操作

  • 显示当前会话有多少函数可用：SHOW FUNCTIONS;
  • 显示函数的描述信息：DESC FUNCTION concat;
  • 显示函数的扩展描述信息：DESC FUNCTION EXTENDED concat;

注意：

  • hive 中最高精度的数据类型是 double, 只精确到小数点后 16 位，在做除法运算的时候要特别注意；
  • 如果A乘以B的结果超过默认结果类型的数值范围，则需要通过cast将结果转换成范围更大的数值类型；
# if 函数，如果满足条件，则返回A， 否则返回B
if (boolean condition, T A, T B)
# case 条件判断函数, 当a为b时则返回c；当a为d时，返回e；否则返回f
case a when b then c when d then e else f end
# 返回列表中第一个非空元素,如果所有值都为空，则返回null
coalesce(v1, v2, v3, ...)
# 去除两边空格
trim()
# 大小写转换
lower(), upper()
# 返回第二个参数在待查找字符串中的位置（找不到返回0）
instr(string str, string search_str)
# 字符串连接
concat(string A, string B, string C, ...)
# 自定义分隔符sep的字符串连接
concat_ws(string sep, string A, string B, string C, ...)
# 返回字符串长度
length()
# 反转字符串
reverse()
# 字符串截取
substring(string A, int start, int len)
# 按照pat字符串分割str，返回分割后的字符串数组
split(string str, string pat)
# 将字符串类型的数据读取为json类型，并得到其中的元素key的值
# 第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用.读取对象或数组；
get_json_object(string s, '$.key')
# url解析
# parse_url('http://facebook.com/path/p1.php?query=1','HOST')返回'facebook.com' 
# parse_url('http://facebook.com/path/p1.php?query=1','PATH')返回'/path/p1.php' 
# parse_url('http://facebook.com/path/p1.php?query=1','QUERY')返回'query=1'，
parse_url()
# 将字符串A中的符合java正则表达式pat的部分替换为C;
regexp_replace(string A, string pat, string C)
# 将字符串subject按照pattern正则表达式的规则进行拆分，返回index制定的字符
# 0:显示与之匹配的整个字符串， 1：显示第一个括号里的， 2：显示第二个括号里的
regexp_extract(string subject, string pattern, int index)
# 类型转换
cast(expr as type)
# 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
str_to_map(string A, string item_pat, string dict_pat)
# 提取出map的key, 返回key的array
map_keys(map m)
# 日期函数
# 日期比较函数，返回相差天数，datediff('${cur_date},d)
datediff(date1, date2)
# 返回当前时间
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
# explode就是将hive一行中复杂的array或者map结构拆分成多行
explode(colname)
# lateral view 将一行数据adid_list拆分为多行adid后，使用lateral view使之成为一个虚表adTable，使得每行的数据adid与之前的pageid一一对应, 因此最后pageAds表结构已发生改变，增加了一列adid
select pageid, adid from pageAds
lateral view explode(adid_list) adTable as adid

6、自定义函数UDF和Transform

当Hive提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义函数（UDF：user-defined function）。

1. 自定义函数类别

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

2. UDF开发实例

前期准备，要把hive的lib包导入到工程中，其中UDF依赖的是hive-exec-1.2.1.jar。也就是说要把apache-hive-1.2.1-bin\lib中内容都引入到工程中。若用到hadoop中的一些api，请把hadoop的api也引入进去。

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

package cn.lyx.bigdata.udf

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

public final class ToLowerCase 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.lyx.bigdata.udf.ToLowerCase';

5、即可在hql中使用自定义的函数toLowerCase

select toLowerCase(name),age from t_test;

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, rating, unixtime,userid)
USING 'python weekday_mapper.py'
AS (movieid, rating, weekday,userid)
FROM u_data;

其中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])

使用示例2：下面的例子则是使用了shell的cat命令来处理数据

FROM invites a INSERT OVERWRITE TABLE events SELECT TRANSFORM(a.foo, a.bar) AS (oof, rab) USING '/bin/cat' WHERE a.ds > '2008-08-08';

相关推荐