hive sql详解

最新推荐文章于 2025-06-14 18:29:00 发布

qq_806913882

最新推荐文章于 2025-06-14 18:29:00 发布

阅读量8.4k

点赞数 1

CC 4.0 BY-SA版权

分类专栏：大数据 hbase 文章标签： hbase sql 大数据

本文链接：https://blog.youkuaiyun.com/qq_806913882/article/details/53576356

大数据同时被 2 个专栏收录

11 篇文章

订阅专栏

hbase

3 篇文章

订阅专栏

本文详细介绍了Hive的数据类型、常用命令，包括创建表、加载数据、查询语句、连接操作，以及函数使用等核心概念。特别强调了Hive的连接语句，如内连接、外连接，并提及了Hive支持的条件筛选操作如in、not in、like、not like。此外，还涵盖了修改表结构和各种函数的应用，为Hive初学者提供了全面的参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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;

左半连接

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://wenku.baidu.com/link?url=f07hgey5586Kw7HjN1y6gOW4m0ofmIoi7FI3RjXN1TxRglCnDApVVWgDS-gbqrMiLcgTsqm5Q1OCgCyVJswKCIrcGxrWL6ew4gcfQyXjPu3

HIVE函数
http://www.cnblogs.com/end/archive/2012/06/18/2553682.html