Hive笔记3-HQL操作

最新推荐文章于 2024-04-29 23:08:04 发布
最新推荐文章于 2024-04-29 23:08:04 发布
阅读量771 收藏 3
点赞数
分类专栏： 大数据 文章标签： hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Fighting_No1/article/details/88690033
版权

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;

    示例， 显示如下：

  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;

增加/更新列

语法结构

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]

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、常用函数

  • 显示当前会话有多少函数可用：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';
Fighting_No1
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
【大数据开发】Hive——窗口函数、自定义函数
白色风车
10-26 712
一、窗口函数 如果要查询详细记录和聚合函数，必须要经过两次查询，比较麻烦，这个时候使用窗口函数，会方便很多 （1）窗口函数是针对每一行数据的 （2）如果over中没有指定参数，则默认窗口大小为全部结果集 注意：开窗函数不能直接写在where里面 --求01课程的平均成绩 select *, avg(sc.s_score) over() from mydb2.score sc where sc.c_id='01' --求每门课的平均成绩 select *, avg(sc.s_score) over(d
hql(hibernate查询语言)
yjsuge的专栏
07-27 1202
hibernate查询语言hql在hql中关键字不区分大小写，通常小写，类的名称和属性名称必须区分大小写主要有如下几种查询:(以Studet和Classes为例说明,1、简单属性查询【重要】 * 单一属性查询，返会属性结果集列表，元素类型和实体类中相应的类型一致//返回结果集属性
Hive3.1.3详细安装配置教程及安装时问题解决
最新发布
Jeramy_的博客
04-29 1551
可能会出现错误 Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;原因是hive/lib中的guava包版本与/hadoop/share/hadoop/common/lib中的guava包版本不匹配，将hadoop中的guava包替换到hive中，执行以下命令。重新执行命令升级元数据。
Hive HQL操作
qq_43240913的博客
11-29 738
实验思路 首先检查Hadoop相关进程，是否已经启动。若未启动，切换到/apps/hadoop/sbin目录下，启动Hadoop。 然后开启Mysql，用于存放Hive的元数据。（密码：zhangyu 在终端命令行界面，直接输入Hive命令，启动Hive命令行 在hive中创建一个表student 在hive中创建一个表course 创建本地目录/data/ans17，下载...
大数据hadoop系列：Hive HQL常用操作
qq_26766821的博客
09-13 496
1.hive HQL 的表操作： 1.1 创建数据库 hive> create database zhanzhy; OK Time taken: 0.073 seconds hive> show databases; OK default zhanzhy Time taken: 0.012 seconds, Fetched: 2 row(s) hive> -- 查看数据库详...
Hive-HQL操作
l_zerol的博客
02-09 1313
HQL 一、DDL 1.1 数据库操作 -- 创建数据库 create database if not exists mydb2 [comment 'this is mydb'] [location '/user/hive/mydb.db']; -- 查看数据库 -- 查看所有数据库 show database; -- 查看数据库信息 desc database mydb2; desc database extended mydb2; describe database extended m
Hive大数据仓库-笔记整理 （一）2020年最新版.pdf
10-11
3. **Hive与HDFS的关系**：Hive中的数据实际存储在Hadoop的HDFS（Hadoop Distributed File System）上，Hive不管理数据的存储和分布，只是提供了数据访问的接口。 4. **Hive数据类型**：Hive支持多种数据类型，包括...
hive学习笔记精华版
10-08
【Hive学习笔记精华版】 Hive是大数据处理领域中的一款重要工具，它基于Hadoop构建，主要用于数据仓库和数据分析。作为一个离线处理系统，Hive可以将结构化的数据文件映射成数据库表，允许用户使用类似SQL的查询...
hive-笔记.docx
05-15
在实际使用 Hive 时，用户可以通过 HQL 来创建表、加载数据、执行查询和数据聚合操作。Hive 提供了丰富的内置函数，同时也允许用户编写 UDF、UDAF（用户定义的聚合函数）和 UDTF（用户定义的转换函数），以处理各种...
尚硅谷大数据技术hive学习笔记
10-27
Hive 的核心是将 HQL 转化成 MapReduce 程序，通过 SQL 语言中常用的操作（select、where、group 等）用 MapReduce 写成很多模板，所有的 MapReduce 模板封装在 Hive 中。 Hive 的优点包括： 1. 操作接口采用类 ...
hive学习笔记
10-16
3. **Hive和Hadoop关系** Hive是建立在Hadoop之上的数据仓库工具，它将SQL查询转换为MapReduce任务运行在Hadoop上。Hadoop提供数据存储和计算能力，而Hive则提供了更友好的数据查询接口和数据管理功能。 4. **...
hive的条件函数case
网络
12-10 813
case用法示例select id,userid,case title when 'test_title_2' then userid+1 else userid+10 end from article;从article（id,userid,article,content）表中将标题为‘test_title_2’的记录其userid+1，其他的记录userid+10coalesce用法示例：sel
Hive3 介绍与安装使用
小毕超博客
11-06 1371
是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一种类似SQL的查询模型，称为Hive查询语言HQL，用于访问和分析存储在Hadoop文件中的大型数据集。Hive的核心是将HQL转换为MapReduce程序，然后将程序提交到Hadoop群集执行。这里需要注意下：在 Hive2 中，由于MapReduce的特征，就已经不建议继续使用MapReduce运算，推荐使用速度更快的spark或tez。
Hadoop Hive sql语法详解
weixin_30355437的博客
02-14 6912
Hive 是基于Hadoop 构建的一套数据仓库分析系统，它提供了丰富的SQL查询方式来分析存储在Hadoop 分布式文件系统中的数据，可以将结构 化的数据文件映射为一张数据库表，并提供完整的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行，通过自己的SQL 去查询分析需 要的内容，这套SQL 简称Hive SQL，使不熟悉mapreduce 的用户...
substring(x)和substring(x,y)的用法
zhulinxi的博客
03-24 1237
代码：public class textmu { /**  * @param args  */ public static void main(String[] args) {  // TODO Auto-generated method stub    String id="123454321";    String shortId,longId,Id;    shortId=id.substr...
Hive3入门至精通(基础、部署、理论、SQL、函数、运算以及性能优化)1-14章
wt334502157的博客
10-24 2873
Hive3入门至精通(基础、部署、理论、SQL、函数、运算以及性能优化)1-14章Hive3入门至精通(基础、部署、理论、SQL、函数、运算以及性能优化)15-28章数据仓库（英语：Data Warehouse，简称数仓、DW）,是一个用于存储、分析、报告的数据系统。 数据仓库的目的是构建面向分析的集成化数据环境，分析结果为企业提供决策支持（Decision Support）。数据仓库为了分析数据而来，分析结果给企业决策提供支撑。 企业中，信息数据总是用作两个目的： （1）操作型记录的保存（2）分析型决策的
Hive--查询
Mr_huang的博客
10-30 450
Hive–查询 前言： 官方文档：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select 查询语句语法： [WITH CommonTableExpression (, CommonTableExpression)*] (Note: Only available starting with Hive 0.13...
HIVE3 深度剖析 (上篇)
明哥的IT随笔
05-11 1562
1. 从 HIVE 架构的演进看 HIVE 的发展趋势 2. 盘点下 HIVE3.X 和 HIVE2.X 的那些重大差异点 3. HIVE3.X 的 ORC 事务表详解 4. HIVE3.X 的 LEGACY 传统模式详解 5. 周边生态如 SPARK/DATAX 如何对接HIVE 3x 6. 大数据应用对接 HIVE3.x 的几点建议
HQL05 将查询后的列重新命名
m0_47792921的博客
03-29 424
测试用例 create database nk_test; //创建数据库 use nk_test; //进入数据库 -- 创建测试表 CREATE TABLE user_profile( id int, device_id int, gender string, age int, university string, province string); -- 向测试表中插入数据 INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','
hive -f 执行 hql 去更新目标表
05-26
可以使用以下语法执行 HiveQL 去更新目标表： ``` hive -e "UPDATE target_table SET column1=value1, column2=value2 WHERE condition;...hive -f update.hql ``` 其中，`update.hql` 包含了完整的更新语句。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值