Hive sql 常用命令1_hive 查看字符的下标-优快云博客

本文详细介绍了 Hive 数据库的创建、查询、修改和删除等基本操作，包括 DDL 和 DML 语句，以及窗口函数的使用。内容涵盖创建数据库、查看表结构、插入数据、查询语法、窗口函数如 rank()、dense_rank()、row_number() 等，并举例说明了日期函数和各种统计函数的应用。此外，还提到了 Hive 与 MySQL 在日期处理上的差异。

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

原文地址：https://zhuanlan.zhihu.com/p/176989230

基本语法

创建数据库：

create database if not exists database_name

查看数据库：

show databases;

查看数据库存在的描述以及路径：

describe database database_name;

修改数据库路径：

create database database_name location 'path';

删除空数据库：

drop database if exists database_name;

先删表再删库：

drop database if exists database_name cascade;

使用xx数据库：

use database_name

查看该数据库中的所有表：

show tables;

创建表：

create table if not exists table_name
(
uname string comment 'uname',
uage int comment 'age'
) comment 'table'

查看表结构以及表路径：

describe tab_name

删除表：

delete table if exists table_name

插入新字段：

alter table table_name add columns()

修改表名：

alter table table_name rename to new_table_name;

导入数据：

-- hive 不支持行级的数据插入，删除，更新，只支持将文件写入。
load data local inpath 'path' into table table_name

DML（数据操纵语言，增删改查

查询SQL基本格式（该排序为人思维方式：从“来自哪个表”开始）：

from 表名
on + 字段
left/reght join + 表名
where + 条件
group by + 字段
having + 条件
select + 字段
distinct + 字段
union + 结果集
order by 字段
limit 数量

窗口函数

窗口函数：保留聚合前的数据，同时可以看到聚合后的数据。窗口函数都是最后一步执行（窗口函数使用的数据来自于前面SQL处理后的数据），而且仅位于order by（该函数对于row_number(),lead(),lag()函数是必须的，如果数据无序，这类函数的结果就没有任何意义，而因此有了order by子句，count(),min()等函数，就没有任何意义）之前。

基本语法：

1. 聚合函数 over()
2. 聚合函数 over(partition by ...)
3. 聚合函数 over(partition by ... order by ...)
4. 聚合函数 over(partition by ... order by ...) rows between A and B,A/B

示例：

输出字段：name,orderdate,cost,sum_window_0（该参数名为窗口函数名）
select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) 

查看购买明细，以及根据月进行购买金额的汇总，并根据金额升序排序
select name.orderdate,money,sum(money) over(partition by month(orderdate) order by orderdate) 
from table_name

排序型-窗口函数

生成数据项在分组中的排名，排名相等会在名词中不会留下空位。
1. dense_rank over(partition by ... order by ..)

生成数据项在分组中的排名，排名相等会在名词中留下空位。
留空位：比如从1开始，两个相同的值，从2开始，留空位就是会把3给留出来，在后面的值从下标4开始。
2. rank over(partition by ... order by ..)：

从1开始，按照顺序，生成分组内记录的序列；当排序的值相同时，按照表中记录的顺序进行排列
3. row_number over(partition by ... order by ..)

前后型-窗口函数参数：字段，查看的条数，默认值

前N行
1. lag(colname,num,default) over(partition by ... order by ..)   

后N行
2. lead(colname,num,defalut) over(partition by ... order by ..)

分组排序后-窗口函数：

分组排序后第一行
1. first_value(colname) over(partition by ... order by ..) 

分组排序后最后一行
2. last_value(colname) over(partition by ... order by ..)

切片型-窗口函数：参数3表示将数据切分成三份

1. ntile(3) over() 对全局数据切片
2. ntile(3) over(partition by ..) 对分组之后的数据切片
3. ntile(3) over(order by ...) 对全局数据按照升序后切成三份
4. ntile(3) over(partition by .... order by ...) 对数据分组并按照升序后切成三份

百分比型-窗口函数

小于等于当前值的行数/分组内总行数

1. cume_dist over(order by ...)
2. cume_dist over(partition by ... order by)

分组内当前行的排名-1/分组内总行数-1

1. PERCENT_RANK over(order by ...)
2. PERCENT_RANK over(partition by ... order by ...)

日期函数

to_date:
from_unixtime
unix_timestamp
year/month/day/hour/minute/second
weekofyear
datediff
date_sub
date_add
from_unixtime+unix_timestamp

1、关系函数

等值比较=
不等值比较<>
空值比较 IS NULL
非空比较IS NOT NULL
like比较：B中字符”_”表示任意单个字符，而字符”%”表示任意数量的字符。
JAVA的LIKE/REGEXP操作: RLIKE/REGEXP

2、日期函数

UNIX时间戳转日期函数: from_unixtime
获取当前UNIX时间戳函数: unix_timestamp
日期转UNIX时间戳函数: unix_timestamp
日期时间转日期函数: to_date
日期转年函数: year
日期转周函数: weekofyear
日期比较函数: datediff
日期增加函数: date_add，减少函数date_sub

日期函数在mysql和hive中的区别

Hive: SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d') 结果为 %2009%0%6
Hive： SELECT FROM_UNIXTIME( 1249488000, 'yyyy-MM-dd') 结果为 2009-08-06
MySQL：SELECT FROM_UNIXTIME( 1249488000, '%Y%m%d') 结果为 20090806
MySQL：SELECT FROM_UNIXTIME( 1249488000, '%Y-%m-%d') 结果为 2009-08-06

3、条件函数