hive数据类型
hive基本数据类型
基本类型 | 大小(字节) | 描述 |
---|---|---|
TINYINT | 1 | 有符号整数 |
SMALLINT | 2 | 有符号整数 |
INT | 4 | 有符号整数 |
BIGINT | 8 | 有符号整数 |
STRING | 最大2GB | 字符串,类似SQL的VARCHAR类型 |
FLOAT | 4 | 单精度浮点数 |
DOUBLE | 8 | 双精度浮点数 |
BOOLEAN | TRUE/FALSE |
hive复杂类型
基本类型 | 大小(字节) | 描述 |
---|---|---|
Array | 不限 | 数组,顺序存储一列相同类型的数据 |
Struct | 不限 | 结构体,存储一列不同类型的数据 |
Map | 不限 | 无序键值对,由一列的键值对组成 |
常用命令
# 查询数据库
show databases;
# 查询表
show tables;
#使用数据库
use database_name ;
# 查看表结构
desc table_name;
创建表
创建表的格式
格式:
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]
# 字段解释:
[external] # 申明是否为外部表
[if not exists] # 如果表存在,则不创建了
[(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] # 在clustered by 里的字段上建立 num_buckets个桶,记录是由 sorted by里的字段排序的。
[ROW FORMAT row_format] # 指定分隔符,可以是以下几个:
: DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[STORED AS file_format] # 指定load文件的类型,分为:
textfile 是 纯文本文件
sequence 是 压缩文件
[LOCATION hdfs_path] # 向表里加载数据,hdfs_path是一个hdfs上的目录,不能是文件,hive会依据默认配置的hdfs路径,自动将整个目录下的文件都加载到表中。
创建表的实例
创建一个简单的内部表
create table if not exists T1(
id int,
name string,
salary double comment 'comment是字段注释'
)comment 'comment是表注释'
row format delimited
fields terminated by ','
stored as textfile;
创建外部表
create external table if not exists T1(
id int,
name string,
salary double comment 'comment是字段注释'
)comment 'comment是表注释'
row format
delimited fields terminated by ','
stored as textfile;
使用复杂类型创建表
create external table if not exists T2(
id int,
course array<string>,
score map<string,int>
)
row format
delimited fields terminated by ','
collection items terminated by '|'
map keys terminated by ':'
stored as textfile;
# 数据文件内容
1001,语文|数学|英语,语文|56,语文:102|数学:2033|英语:30
1002,语文|数学|英语,语文|156,语文:120|数学:2033|英语:30
1003,语文|数学|英语,语文|1156,语文:210|数学:3320|英语:30
1004,语文|数学|英语,语文|1156,语文:2210|数学:203|英语:30
1005,语文|数学|英语,语文|5116,语文:22210|数学:230|英语:30
# 导入数据文件
load data local inpath '/home/datanode/hiveTest/test01' overwrite into table t2;
创建一个带分区的内部表
create table if not exists T3(
id int,
name string
)
partitioned by (classid int)
row format
delimited fields terminated by ','
stored as textfile;
创建一个带桶的内部表
create table T4(
id int ,
name string,
sex string,
age int
)
partitioned by (city string)
clustered by(age) sorted by(name) into 5 buckets
row format
delimited fields terminated by ','
stored as textfile;
加载数据
加载数据的格式
load 方式加载数据
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
#字段解释
[LOCAL] # 加载的是否是本地文件
[OVERWRITE] # 是否覆盖表里原来的数据
[PARTITION (partcol1=val1, partcol2=val2 ...)] # 如果表存在分区,指定加载到那个分区
insert 方式加载数据
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement
#字段解释
[PARTITION (partcol1=val1, partcol2=val2 ...)] # 如果表存在分区,指定加载到那个分区
加载数据的实例
加载本地文件
LOAD DATA LOCAL INPATH '/home/admin/test/test.txt' OVERWRITE INTO TABLE test_1
加载hdfs上的文件
LOAD DATA INPATH '/user/admin/test/test.txt' OVERWRITE INTO TABLE test_1
用insert加载数据
insert overwrite table t5 select * from t2;
insert into table t5 select * from t2;
查询语句与常用命令
查询格式
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]
# 字段解释
[ALL | DISTINCT] # 查询的字段
[WHERE where_condition] # where子句
# 类似我们传统SQL的where 条件
# 目前支持 AND,OR ,0.9版本支持between
# IN, NOT IN
# 不支持EXIST ,NOT EXIST
[GROUP BY col_list [HAVING condition]] # 字段分组和having子句
[SORT BY| ORDER BY col_list] # 排序
# ORDER BY与SORT BY的不同
# ORDER BY 全局排序,只有一个Reduce任务
# SORT BY 只在本机做排序
[LIMIT number] # 限制查询记录数
查询实例
# 查询t2表的数据
select * from t2;
# 查询10条t2表的数据
select * from t2 limit 10;
连接语句
hive 多表连接使用 join…on 语句
hive 只支持等值连接,即 on子句中只能使用等号连接
如果连接语句中有where子句,会先执行join子句,再执行where子句。
内连接
select * from t1 join t2 on t1.id = t2.id;
左外连接
select * from t1 left join t2 on t1.id=t2.id;
右外连接
select * from t1 right join t2 on t1.id=t2.id;
全外连接
select * from t1 outer join t2 on t1.id=t2.id;
- 1
左半连接
select * from t1 left semi join t2 on t1.id=t2.id;
# 等价于
selct * from t1 where t1.id in (select t2.id from t2);
in / not in / like / not like
hive 0.9.0版本开始支持 in , not in , like . not like
用法与SQL的 in , not in , like . not like的作用相同,就不在多说了。
修改表结构 : alter
# 修改表名
ALTER TABLE t1 RENAME TO 3koobecaf;
# 增加一个字段
ALTER TABLE t1 ADD COLUMNS (new_col INT);
# 增加一个字段,带注释
ALTER TABLE t1 ADD COLUMNS (new_col2 INT COMMENT 'a comment');
# 将表的字段全部替换为新的字段
ALTER TABLE t1 REPLACE COLUMNS (foo INT, bar STRING, baz INT);
函数
数字函数
函数 | 说明 |
---|---|
round(double a, int d) | 小数部分d位之后数字四舍五入,例如round(21.263,2),返回21.26 |
floor(double a) | 对给定数据进行向下舍入最接近的整数。例如floor(21.2),返回21 |
ceil(double a) | 将参数向上舍入为最接近的整数。例如ceil(21.2),返回23 |
rand() | 返回大于或等于0且小于1的平均分布随机数 |
rand(int seed) | 返回大于或等于0且小于seed的平均分布随机数 |
pow(double a, double p) power(double a, double p) | 返回某数的乘幂 |
sqrt(double a) | 返回数值的平方根 |
abs(double a) | 取绝对值 |
pmod(int a, int b) pmod(double a, double b) | 返回a除b的余数的绝对值 |
positive(int a) positive(double a) | 返回A的值,例如positive(2),返回2 |
negative(int a) negative(double a) | 返回A的相反数,例如negative(2),返回-2 |
收集函数
函数 | 说明 |
---|---|
size(Map) | 返回的map类型的元素的数量 |
size(Array) | 返回数组类型的元素数量 |
日期函数
函数 | 说明 |
---|---|
from_unixtime(bigint unixtime[, string format]) | UNIX_TIMESTAMP参数表示返回一个值’YYYY- MM – DD HH:MM:SS’或YYYYMMDDHHMMSS.uuuuuu格式,这取决于是否是在一个字符串或数字语境中使用的功能。该值表示在当前的时区 |
unix_timestamp(string date) | 指定日期参数调用UNIX_TIMESTAMP(),它返回参数值’1970- 01 – 0100:00:00′到指定日期的秒数。 |
to_date(string timestamp) | 返回时间中的年月日: to_date(“1970-01-01 00:00:00″) = “1970-01-01″ |
to_dates(string date) | 给定一个日期date,返回一个天数(0年以来的天数) |
datediff(string enddate, string startdate) | 两个时间参数的日期之差 |
date_add(string startdate, int days) | 给定时间,在此基础上加上指定的时间段 |
date_sub(string startdate, int days) | 给定时间,在此基础上减去指定的时间段 |
year(string date) | 返回指定时间的年份,范围在1000到9999,或为”零”日期的0 |
month(string date) | 返回指定时间的月份,范围为1至12月,或0一个月的一部分,如’0000-00-00′或’2008-00-00′的日期 |
day(string date) | 返回指定时间的日期 |
dayofmonth(date) | 返回指定时间的日期 |
hour(string date) | 返回指定时间的小时,范围为0到23 |
minute(string date) | 返回指定时间的分钟,范围为0到59 |
second(string date) | 返回指定时间的秒,范围为0到59 |
weekofyear(string date) | 返回指定日期所在一年中的星期号,范围为0到53 |
字符串函数
函数 | 说明 |
---|---|
length(string A) | 返回字符串的长度 |
reverse(string A) | 返回倒序字符串 |
concat(string A, string B…) | 连接多个字符串,合并为一个字符串,可以接受任意数量的输入字符串 |
concat_ws(string SEP, string A, string B…) | 链接多个字符串,字符串之间以指定的分隔符分开 |
substring(string A, int start, int len) | 从文本字符串中指定的位置指定长度的字符 |
upper(string A) ucase(string A) | 将文本字符串转换成字母全部大写形式 |
lower(string A) lcase(string A) | 将文本字符串转换成字母全部小写形式 |
trim(string A) | 删除字符串两端的空格,字符之间的空格保留 |
regexp_replace(string A, string B, string C) | 字符串A中的B字符被C字符替代 |
regexp_extract(string subject, string pattern, int index) | 通过下标返回正则表达式指定的部分。regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’ |
space(int n) | 返回指定数量的空格 |
repeat(string str, int n) | 重复N次字符串 |
rpad(string str, int len, string pad) | 返回指定长度的字符串,给定字符串长度小于指定长度时,由指定字符从右侧填补。 |
聚合函数
函数 | 说明 |
---|---|
count(*) | 返回记录条数 |
sum(*) | 求和 |
avg(*) | 求平均 |
min(*) | 最小 |
max(*) | 最大 |
ar_pop(col) | 返回指定列的方差 |
var_samp(col) | 返回指定列的样本方差 |
stddev_pop(col) | 返回指定列的偏差 |
stddev_samp(col) | 返回指定列的样本偏差 |
covar_pop(col1, col2) | 两列数值协方差 |
covar_samp(col1, col2) | 两列数值样本协方差 |
corr(col1, col2) | 返回两列数值的相关系数 |
单次执行语句
# 不进入命令行,直接执行一次命令
hive -e 'select * from test05';
#将查询到的内容放到a.txt中
hive -S -e 'select * from test05' >> a.txt
应用文章:
HIVE函数参考手册
http://www.cnblogs.com/end/archive/2012/06/18/2553682.html
HIVE函数
http://www.cnblogs.com/end/archive/2012/06/18/2553682.html