Hive使用,UDF的创建

create database testdb;


自定义UDF
1.把程序打包放到目标机器上:hiveserver(hive使用端)
2.进入hive客户端,添加jar包
hive>add jar /usr/jar/hivetest.jar
3.创建临时函数
hive>create temporary function add_example as 'hive.udf.Add';
4.查询HQL语句:
select add_example(8,9) from score;
5.销毁临时函数
hive>drop temporary function add_example;


create table psn1(
id int,
name string,
likes ARRAY <string>,
address MAP <string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED by ','
COLLECTION ITEMS TERMINATED by '-'
MAP KEYS TERMINATED by ':';

vi /var/tmp/data/psn1
1,xiaoming,book-tv-code,beijing:chaoyang-shanghai:pudong
2,lilei,book-code,huoxing:xxx-shanghai:pudong
3,hanmeimie,book-lilei,beijing:xxx

hive>load data local inpath '/var/tmp/data/psn1' into table psn1;


创建外部表
create external table psn2(
id int,
name string,
likes ARRAY <string>,
address MAP <string,string>
)
ROW FORMAT DELIMITED
FIELDS TERMINATED by ','
COLLECTION ITEMS TERMINATED by '-'
MAP KEYS TERMINATED by ':'
LOCATION '/user/data';

外部表删除后,在数据库里没有表的数据,但在目录中该文件仍存在


CREATE TABLE IF NOT EXISTS employee ( eid int, name String,
salary String, destination String)
COMMENT 'Employee details'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

sample.txt
1201 Gopal 45000 Technical manager
1202 Manisha 45000 Proof reader
1203 Masthanvali 40000 Technical writer
1204 Kiran 40000 Hr Admin
1205 Kranthi 30000 Op Admin
hive>load data local inpath '/user/sample.txt' into table employee;

创建分区
create table psn3(
id int,
name string,
likes ARRAY <string>,
address MAP <string,string>
)
PARTITIONED BY(year int)
ROW FORMAT DELIMITED
FIELDS TERMINATED by ','
COLLECTION ITEMS TERMINATED by '-'
MAP KEYS TERMINATED by ':'
LOCATION '/user/psn3';


alter table psn3 add partiton (year int);
alter table psn3 drop partition (year int);

load data local inpath '/user/sample.txt' into table employee partition (dept='IT');

load data [local] 'filepath' into table tb_name;
如果filepath是hdfs的目录,相当于将hdfs的文件移动到hive表目录
如果是本地目录,是将文件上传到hdfs,再将这个hdfs文件拷贝到对应表文件目录下

create table result_table (cnt int);

常用语保存运算或统计结果
from psn1
insert into table result_table
select count(*);

转载于:https://www.cnblogs.com/pashanhu/p/10950262.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值