### Hive, Presto, Spark区别 #### 数据类型区别 ##### 文本 | Hive | Spark | Presto | | ------ | ------ | ------- | | string | string | varchar | #### 语法函数区别 ##### 日期处理 ###### 文本日期转换 ```sql -- 文本转换日期 -- Hive/Spark to_date('2025-07-05') -> string date_format('2024-11-01 01:02:03','yyyy-MM-dd HH:mm:ss') -> string -- Presto to_date('2025-07-05', 'yyyy-mm-dd') → timestamp to_date('20250705', 'yyyymmdd') date_parse(string, format) → timestamp date_parse('20250705', '%Y%m%d') date_parse('2025-07-05', '%Y-%m-%d') date_parse('2025-07-05 22:12:24', '%Y-%m-%d %H:%i:%s') -- 日期转换文本 -- Presto date_format(timestamp, format) → varchar date_format(date_parse('20210814','%Y%m%d'),'%Y-%m-%d') → varchar ``` ###### Presto date_parse() 引用：[prestodb.io#mysql-date-functions](https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions) | Specifier | Description | | ------------ | ------------------------------------------------------------------------------------------------ | | %a | Abbreviated weekday name (Sun .. Sat) | | %b | Abbreviated month name (Jan .. Dec) | | %c | [Month, numeric (1 .. 12) | | **%d** | **[Day of the month, numeric (01 .. 31)** | | %e | [Day of the month, numeric (1 .. 31) | | **%H** | **Hour (00 .. 23)** | | %h | Hour (01 .. 12) | | **%i** | **Minutes, numeric (00 .. 59)** | | %j | Day of year (001 .. 366) | | %k | Hour (0 .. 23) | | %l | Hour (1 .. 12) | | %M | Month name (January .. December) | | **%m** | Month, numeric (01 .. 12) | | %p | AM or PM | | %r | Time, 12-hour (hh:mm:ss followed by AM or PM) | | **%S** | **Seconds (00 .. 59)** | | **%s** | **Seconds (00 .. 59)** | | %T | Time, 24-hour (hh:mm:ss) | | %U | Week (00 .. 53), where Sunday is the first day of the week | | %u | Week (00 .. 53), where Monday is the first day of the week | | %V | Week (01 .. 53), where Sunday is the first day of the week; used with %X | | %v | Week (01 .. 53), where Monday is the first day of the week; used with %x | | %W | Weekday name (Sunday .. Saturday) | | %w | [Day of the week (0 .. 6), where Sunday is the first day of the week | | %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with%V | | %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with%v | | **%Y** | **Year, numeric, four digits** | | %y | [Year, numeric (two digits) | | %% | A literal% character | | %x | x, for any x not listed above | ###### 日期计算(加减) ```sql -- Spark, Hive datediff(newer_date,older_date) -- Presto date_diff(unit, timestamp1, timestamp2) date_diff('day', older_date, newer_date) date_diff('month', older_date, newer_date) date_diff('hour', older_date, newer_date) ``` ##### map, json结构提取数值 ```sql -- Presto json_extract_scalar(column_name, '$.key') -- Spark, Hive get_json_object(column_name, '$.key') ``` ### 数据类型 | 分类 | 类型 | 描述 | 范围和举例 | | -------- | --------- | ---------------------------------------------- | ------------------------------------------------------------------------- | | 原始类型 | boolean | true/false | TRUE, FALSE | | 原始类型 | tinyint | 1字节的有符号整数 | [-128, 127] | | 原始类型 | smallint | 2个字节的有符号整数 | [-32768, 32767] | | 原始类型 | int | 4个字节的带符号整数 | [-2147483648, 2147483647] | | 原始类型 | bigint | 8字节带符号整数 | [9223372036854775808, 9223372036854775807] | | 原始类型 | float | 字节单精度浮点数1.0 | [-3.40E+38, +3.40E+38] | | 原始类型 | double | 8字节双精度浮点数 | [-1.79E+308, +1.79E+38] | | 原始类型 | decimal | 任意精度的带符号小数 | | | 原始类型 | string | 字符串，变长 | | | 原始类型 | varchar | 变长字符串 | | | 原始类型 | char | 固定长度字符串 | | | 原始类型 | binary | 字节数组 | | | 原始类型 | timestamp | 时间戳，毫秒值精度 | 1327882394<br />1327882394.123456789<br />YYYY-MM-DD hh:mm:ss.fffff | | 原始类型 | date | 日期 | '2018-04-07' | | 原始类型 | interval | 时间频率间隔 | | | 复杂类型 | array | 有序的的同类型的集合 | array(1,2) | | 复杂类型 | map | key-value,key必须为原始类型，value可以任意类型 | map(‘a’,1,’b’,2) | | 复杂类型 | struct | 字段集合,类型可以不同 | struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) | | 复杂类型 | union | 在有限取值范围内的一个值 | create_union(1,’a’,63) | ###### 存储格式 | 存储类型 | 描述 | | ------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | textfile | textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。 | | SequenceFile | 1、SequenceFile是Hadoop API提供的一种二进制文件支持，具有使用方便、可分割、可压缩的特点。<br />2、SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 | | RCFile | 一种行列存储相结合的存储方式。 | | ORCFile | 数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。<br />hive给出的新格式，属于RCFILE的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩快速列存取。 | | Parquet | Parquet也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。 | ### 数据格式 | 分隔符 | 描述 | | ----------- | -------------------------------------------------------------------------------------------- | | \n | 对于文本文件来说，每行是一条记录，所以\n 来分割记录 | | ^A (Ctrl+A) | 分割字段<br />也可以用\001 来表示 | | ^B (Ctrl+B) | 用于分割 Arrary 或者 Struct 中的元素，或者用于 map 中键值之间的分割<br />也可以用\002 分割。 | | ^C | 用于 map 中键和值自己分割，也可以用\003 表示。 | ### 内部表与外部表区别 | 区别\表类型 | 内部表(MANAGED_TABLE) | 外部表(EXTERNAL_TABLE) | | ------------------ | ------------------------------------ | ---------------------------------------------- | | 是否有EXTERNAL修饰 | 否 | 是 | | 数据管理 | 数据由 Hive 自身管理 | 数据由 HDFS 管理 | | 创建表时 | 将数据移动到数据仓库指向的路径 | 仅记录数据所在的路径，不对数据的位置做任何改变 | | 删除表时 | 直接删除元数据（metadata）及存储数据 | 仅会删除元数据，HDFS上的文件并不会被删除 | ### 基础操作语句 #### 库 ###### 查看所有库 ```sql show datebases; ``` ###### 切换 ```sql use database_name; ``` ###### 查看信息 ```sql desc datebase database_name; -- 查看数据库的键值对信息 desc datebase extended database_name; ``` ###### 创建 ```sql CREATE DATABASE if not exists database_name; -- 指定hdfs存储路径 CREATE DATABASE database_name location '/myhive2'; -- 指定键值对 CREATE DATABASE database_name with dbproperties ('owner'='Zakikun', 'date'='20221002'); ``` ###### 修改信息 ```sql ALTER DATABASE database_name set dbproperties ('owner'='Zakikun'); ``` 删除 ```sql -- 删除一个空数据库，如果数据库下面有数据表，那么就会报错 DROP DATABSE database_name; -- 强制删除数据库，包含数据库下面的表一起删除 DROP DATABSE database_name cascad ``` #### 表 ##### 创建 ```sql 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] ``` ```sql -- 创建一个指定名字的表：如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常 CREATE TABLE -- 创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION） -- 内部表: 数据移动到指定路径，删除内部表的时候会一并删除metadata和实际数据 -- 外部表：数据不移动，仅记录这个外部表数据所在的路径，删除该外部表的时候只删除metadata，不影响实际数据 EXTERNAL -- 表示注释，默认不能使用中文 COMMENT -- 表示使用分区，一个表可以拥有一个或者多个分区，每个分区单独存在一个目录下 PARTITIONED BY -- 允许用户复制现有的表结构，但是不复制数据 LIKE -- 可用来指定行分隔符，默认分隔符为 '^A' ，在八进制中为 '\001' ROW FORMAT DELIMITED -- 指定该表数据的存储格式，hive 中，表的默认存储格式为 TextFile STORED AS SEQUENCEFILE | TEXTFILE | RCFILE | orc | PARQUET -- 对于每一个表（table）进行分桶(MapReuce中的分区），桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中 CLUSTERED BY -- 指定表在 HDFS 上的存储路径 LOCATION -- 指定压缩算法 tblproperties ('orc.compress'='SNAPPY') ``` ##### 删除 ```sql -- 习惯性加上if exists判断是否存在, 会将hdfs一并删除 DROP TABLE if exists table_name ``` ##### 查看表结构 ```sql -- 格式化表格 desc formatted table_name; -- 简单表格 desc table_name; ``` ##### 查看分区信息 ```sql show partitions table_name; ``` ##### 复制表结构 ```sql CREATE TABLE if not exists new_table_name LIKE database.origin_table_name; ``` ##### 复制表（包括数据） ```sql -- 方法1 CREATE TABLE if not exists new_table_name as select * from database.origin_table_name; -- 方法2 CREATE TABLE if not exists new_table_name LIKE database.origin_table_name; INSERT INTO new_table_name SELECT * FROM database.origin_table_name; ``` ##### 表改名 ```sql ALTER TABLE old_table_name rename to new_table_name; ``` ##### 清空表数据 ```sql TRUNCATE TABLE table_name; ``` ##### 插入数据（追加） ```sql -- 将查询数据追加插入到表中 INSERT INTO TABLE table_name [partition(partcol1=val1,partclo2=val2)] select_statement; ``` ##### 插入数据（覆盖/重写） ```sql -- 将查询数据覆盖插入到表中 NISERT OVERWRITE table table_name [partition(partcol1=val1,partclo2=val2)] select_statement; ``` ##### 修改表注释 ```sql ALTER TABLE db.table_name SET TBLPROPERTIES('comment' = '新的表备注'); ``` #### 分区 ##### 增加分区 正常情况下需要分区不存在，如果为了防止分区存在报错可以加 `IF NOT EXISTS` 关键字 ```sql ALTER TABLE database.table_name ADD [IF NOT EXISTS] PARTITION (分区字段名 = 'XXX') LOCATION '$PATH' ``` ##### 删除分区 ```sql ALTER TABLE database.table_name DROP IF EXISTS PARTITION (分区字段名 = 'XXX'); ``` ##### 修改分区 ```sql ALTER TABLE class_info_partition PARTITION (分区字段名 = 'XXX') SET LOCATION '$PATH' ; ``` #### 字段 ###### 增加/创建 ```sql ALTER TABLE table_name ADD COLUMNS( 新增字段名 新增字段类型 comment '新增字段注释', 新增字段名 新增字段类型 comment '新增字段注释' ); ``` ###### 删除字段/反向保留 ```sql ALTER TABLE table_name REPLACE COLUMNS( 保留字段1 保留字段类型1 comment '保留字段类型1注释', 保留字段2 保留字段类型2 comment '保留字段类型2注释', 保留字段3 保留字段类型3 comment '保留字段类型3注释', 保留字段4 保留字段类型4 comment '保留字段类型4注释', 保留字段5 保留字段类型5 comment '保留字段类型5注释', 保留字段6 保留字段类型6 comment '保留字段类型6注释', ...... ); ``` ###### 修改名称/类型/位置/注释 ```sql -- 修改字段名 ALTER TABLE table_name RENAME COLUMN 现字段名 TO 新字段名 -- 修改字段名,类型,注释 ALTER TABLE table_name CHANGE column 现字段名 修改后字段名 修改后类型 comment '修改后注释'; -- 修改后字段位置 ALTER TABLE table_name CHANGE column 现字段1名 修改后字段1名 修改后类型 comment '修改后注释' after 改为在某字段后的某字段名; ``` #### 数据 ##### select ```sql SELECT [ ALL | DISTINCT ] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING where_condition] [ORDER BY col_list] [CLUSTER BY col_list] [DISTRIBUTE BY col_list] [SORT BY col_list] [LIMIT number] ``` ##### join full outer join 等价于 full join ##### with...as... ```sql WITH temp1 as ( select * from xxx ),temp2 as ( select * from xxx ) select * from temp1,temp2; ``` ```sql table_reference [INNER] 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 table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10) ``` ##### 运算符 | 操作符 | 支持的数据类型 | 描述 | | ----------------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE | | A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL | | A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL；<br />如果A不等于B，则返回TRUE，反之返回FALSE | | A<B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A小于B，则返回TRUE，反之返回FALSE | | A<=B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A小于等于B，则返回TRUE，反之返回FALSE | | A>B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A大于B，则返回TRUE，反之返回FALSE | | A>=B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A大于等于B，则返回TRUE，反之返回FALSE | | A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 | | A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE | | A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE | | IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 | | A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。<br />B的表达式说明如下：<br />‘x%’表示A必须以字母‘x’开头，<br />‘%x’表示A必须以字母’x’结尾，<br />而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。<br />如果使用NOT关键字则可达到相反的效果。 | | A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。<br />匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。<br />例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 | ##### 逻辑运算符 | 操作符 | 含义 | | ------ | ------ | | AND | 逻辑并 | | OR | 逻辑或 | | NOT | 逻辑否 | ##### 算术运算符 | 运算符 | 类型 | 描述 | | ------ | -------- | -------------------------------------------------------------------------------------------------------------------------------- | | A + B | 所有类型 | A与B相加的结果,结果的数值类型等于A的类型和B的类型的最小父类型;<br />int+int一般结果为int类型<br />int+double一般结果为double类型 | | A – B | 所有类型 | A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型 | | A * B | 所有类型 | A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型 | | A / B | 所有类型 | A除以B的结果。结果的数值类型为double | | A % B | 所有类型 | A除以B的余数 | | A & B | 所有类型 | A和B按位进行与操作的结果 | | A | B | 所有类型 | | A ^ B | 所有类型 | A和B按位进行异或操作的结果 | | ~A | 所有类型 | A按位进行取反操作的结果，结果的数值类型等于A的类型 | ##### 分组 ###### group by ```sql select s_id ,max(s_score) from score group by s_id; ``` ###### having ```sql select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85; ``` where和having区别 ```plaintext having与where不同点 1.where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据 2.where后面不能写分组函数，而having后面可以使用分组函数 3.having只用于group by分组统计语句 ``` ##### 排序 ###### order by 全局排序 ```sql select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg; ``` 对 NULL 值的处理 ```sql -- 将 null 值列在最前 select * from student order by student.s_birth nulls first; -- 将 null 值列在最后 select * from student order by student.s_birth nulls last; ``` ###### Sort By 每个MapReduce内部局部排序 ###### Distribute By 分区排序 ###### cluster by ##### 合并 ###### union 去重合并select ###### union all 不去重合并select #### 常用函数 ##### 数值 | 运算符 | 类型 | 功能说明 | | ---------------------------------------- | ------ | -------------------------------------------------------------------------- | | round(double a) | BIGINT | Hive四舍五入取整 | | round(double a, int d) | DOUBLE | Hive指定保留小数点四舍五入 | | floor(double a) | BIGINT | 对给定数据进行向下取最接近的整数 | | ceil(double a)<br />ceiling(double a) | BIGINT | 参数向上取最接近的整数 | | rand(), rand(int seed) | double | 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 | | abs(double a) | double | 取绝对值 | | positive(int a)<br />positive(double a) | double | 取绝对值 | | negative(int a)<br />negative(double a) | double | A的相反数 | ##### 时间 | 函数 | 描述 | 例子 | | ------------------------------------------- | ------------------------------------- | ----------------------------------- | | current_date() | 当前日期 | | | current_time() | | | | datediff(string enddate, string startdate) | 时间差 | datediff('2020-05-29','1997-05-16') | | date_add(string startdate, int days) | 日期加 | date_add('2020-05-29', 7) | | date_sub(string startdate, int days) | 日期减 | date_sub('2020-05-29', 7) | | to_date(string timestamp) | 返回时间中的年月日 | | | from_unixtime | int型转时间戳 | | | unix_timestamp() | 获得当前时区的UNIX时间戳 | | | unix_timestamp(string date, string pattern) | 转换指定pattern格式的日期到UNIX时间戳 | | | year(string date) | 返回指定时间的年份 | | | month(string date) | 返回指定时间的月份 | | | day(string date) | 返回指定时间的天 | | | hour(string date) | 返回指定时间的小时 | | | minute(string date) | 返回指定时间的分钟 | | | second(string date) | 返回指定时间的秒 | | | last_day(string date) | 指定日期字符所在月份的最后一天 | | ##### 条件判断 ```sql case when age < 20 then '20岁以下' when age < 30 then '20-30岁' when age < 40 then '30-40岁' else '40岁以上' end as age_type, ``` ##### 字符串 ```sql substr( stringA, INT start, INT len) ``` | 函数 | 米醋 | | ---------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | length(string A) | 返回字符串A的长度 | | reverse(string A) | reverse(string A) | | concat(string A, string B…) | 返回输入字符串连接后的结果，支持任意个输入字符串 | | concat_ws(string SEP, string A, string B…) | 返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 | | substr(string A, int start) | 返回字符串A从start位置到结尾的字符串 | | substring(string A, int start) | 返回字符串A从start位置到结尾的字符串 | | substr(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 | | substring(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 | | upper(string A) | 字符串转大写函数：upper,ucase | | ucase(string A) | 字符串转大写函数：upper,ucase | | lower(string A) | 字符串转小写函数：lower,lcase | | lcase(string A) | 字符串转小写函数：lower,lcase | | trim(string A) | 去除字符串两边的空格 | | ltrim(string A) | 去除字符串左边的空格 | | rtrim(string A) | 右边去空格函数：rtrim | | regexp_replace(string A, string B, string C) | 将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数 | | regexp_extract(string subject, string pattern, int index) | 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符 | | repeat(string str, int n) | 返回重复n次后的str字符串 | | split(string str, string pat) | 按照pat字符串分割str，会返回分割后的字符串数组 | | find_in_set(string str, string strList) | 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0 | | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 | ##### 数据类型转换 ```sql cast (value as type) -- eg. cast ('2019' as int) ``` Loading... ### Hive, Presto, Spark区别 #### 数据类型区别 ##### 文本 | Hive | Spark | Presto | | ------ | ------ | ------- | | string | string | varchar | #### 语法函数区别 ##### 日期处理 ###### 文本日期转换 ```sql -- 文本转换日期 -- Hive/Spark to_date('2025-07-05') -> string date_format('2024-11-01 01:02:03','yyyy-MM-dd HH:mm:ss') -> string -- Presto to_date('2025-07-05', 'yyyy-mm-dd') → timestamp to_date('20250705', 'yyyymmdd') date_parse(string, format) → timestamp date_parse('20250705', '%Y%m%d') date_parse('2025-07-05', '%Y-%m-%d') date_parse('2025-07-05 22:12:24', '%Y-%m-%d %H:%i:%s') -- 日期转换文本 -- Presto date_format(timestamp, format) → varchar date_format(date_parse('20210814','%Y%m%d'),'%Y-%m-%d') → varchar ``` ###### Presto date_parse() 引用：[prestodb.io#mysql-date-functions](https://prestodb.io/docs/current/functions/datetime.html#mysql-date-functions) | Specifier | Description | | ------------ | ------------------------------------------------------------------------------------------------ | | %a | Abbreviated weekday name (Sun .. Sat) | | %b | Abbreviated month name (Jan .. Dec) | | %c | [Month, numeric (1 .. 12) | | **%d** | **[Day of the month, numeric (01 .. 31)** | | %e | [Day of the month, numeric (1 .. 31) | | **%H** | **Hour (00 .. 23)** | | %h | Hour (01 .. 12) | | **%i** | **Minutes, numeric (00 .. 59)** | | %j | Day of year (001 .. 366) | | %k | Hour (0 .. 23) | | %l | Hour (1 .. 12) | | %M | Month name (January .. December) | | **%m** | Month, numeric (01 .. 12) | | %p | AM or PM | | %r | Time, 12-hour (hh:mm:ss followed by AM or PM) | | **%S** | **Seconds (00 .. 59)** | | **%s** | **Seconds (00 .. 59)** | | %T | Time, 24-hour (hh:mm:ss) | | %U | Week (00 .. 53), where Sunday is the first day of the week | | %u | Week (00 .. 53), where Monday is the first day of the week | | %V | Week (01 .. 53), where Sunday is the first day of the week; used with %X | | %v | Week (01 .. 53), where Monday is the first day of the week; used with %x | | %W | Weekday name (Sunday .. Saturday) | | %w | [Day of the week (0 .. 6), where Sunday is the first day of the week | | %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with%V | | %x | Year for the week, where Monday is the first day of the week, numeric, four digits; used with%v | | **%Y** | **Year, numeric, four digits** | | %y | [Year, numeric (two digits) | | %% | A literal% character | | %x | x, for any x not listed above | ###### 日期计算(加减) ```sql -- Spark, Hive datediff(newer_date,older_date) -- Presto date_diff(unit, timestamp1, timestamp2) date_diff('day', older_date, newer_date) date_diff('month', older_date, newer_date) date_diff('hour', older_date, newer_date) ``` ##### map, json结构提取数值 ```sql -- Presto json_extract_scalar(column_name, '$.key') -- Spark, Hive get_json_object(column_name, '$.key') ``` ### 数据类型 | 分类 | 类型 | 描述 | 范围和举例 | | -------- | --------- | ---------------------------------------------- | ------------------------------------------------------------------------- | | 原始类型 | boolean | true/false | TRUE, FALSE | | 原始类型 | tinyint | 1字节的有符号整数 | [-128, 127] | | 原始类型 | smallint | 2个字节的有符号整数 | [-32768, 32767] | | 原始类型 | int | 4个字节的带符号整数 | [-2147483648, 2147483647] | | 原始类型 | bigint | 8字节带符号整数 | [9223372036854775808, 9223372036854775807] | | 原始类型 | float | 字节单精度浮点数1.0 | [-3.40E+38, +3.40E+38] | | 原始类型 | double | 8字节双精度浮点数 | [-1.79E+308, +1.79E+38] | | 原始类型 | decimal | 任意精度的带符号小数 | | | 原始类型 | string | 字符串，变长 | | | 原始类型 | varchar | 变长字符串 | | | 原始类型 | char | 固定长度字符串 | | | 原始类型 | binary | 字节数组 | | | 原始类型 | timestamp | 时间戳，毫秒值精度 | 1327882394<br />1327882394.123456789<br />YYYY-MM-DD hh:mm:ss.fffff | | 原始类型 | date | 日期 | '2018-04-07' | | 原始类型 | interval | 时间频率间隔 | | | 复杂类型 | array | 有序的的同类型的集合 | array(1,2) | | 复杂类型 | map | key-value,key必须为原始类型，value可以任意类型 | map(‘a’,1,’b’,2) | | 复杂类型 | struct | 字段集合,类型可以不同 | struct(‘1’,1,1.0), named_stract(‘col1’,’1’,’col2’,1,’clo3’,1.0) | | 复杂类型 | union | 在有限取值范围内的一个值 | create_union(1,’a’,63) | ###### 存储格式 | 存储类型 | 描述 | | ------------ | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | textfile | textfile为默认格式，存储方式为行存储。数据不做压缩，磁盘开销大，数据解析开销大。 | | SequenceFile | 1、SequenceFile是Hadoop API提供的一种二进制文件支持，具有使用方便、可分割、可压缩的特点。<br />2、SequenceFile支持三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。 | | RCFile | 一种行列存储相结合的存储方式。 | | ORCFile | 数据按照行分块，每个块按照列存储，其中每个块都存储有一个索引。<br />hive给出的新格式，属于RCFILE的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩快速列存取。 | | Parquet | Parquet也是一种行式存储，同时具有很好的压缩性能；同时可以减少大量的表扫描和反序列化的时间。 | ### 数据格式 | 分隔符 | 描述 | | ----------- | -------------------------------------------------------------------------------------------- | | \n | 对于文本文件来说，每行是一条记录，所以\n 来分割记录 | | ^A (Ctrl+A) | 分割字段<br />也可以用\001 来表示 | | ^B (Ctrl+B) | 用于分割 Arrary 或者 Struct 中的元素，或者用于 map 中键值之间的分割<br />也可以用\002 分割。 | | ^C | 用于 map 中键和值自己分割，也可以用\003 表示。 | ### 内部表与外部表区别 | 区别\表类型 | 内部表(MANAGED_TABLE) | 外部表(EXTERNAL_TABLE) | | ------------------ | ------------------------------------ | ---------------------------------------------- | | 是否有EXTERNAL修饰 | 否 | 是 | | 数据管理 | 数据由 Hive 自身管理 | 数据由 HDFS 管理 | | 创建表时 | 将数据移动到数据仓库指向的路径 | 仅记录数据所在的路径，不对数据的位置做任何改变 | | 删除表时 | 直接删除元数据（metadata）及存储数据 | 仅会删除元数据，HDFS上的文件并不会被删除 | ### 基础操作语句 #### 库 ###### 查看所有库 ```sql show datebases; ``` ###### 切换 ```sql use database_name; ``` ###### 查看信息 ```sql desc datebase database_name; -- 查看数据库的键值对信息 desc datebase extended database_name; ``` ###### 创建 ```sql CREATE DATABASE if not exists database_name; -- 指定hdfs存储路径 CREATE DATABASE database_name location '/myhive2'; -- 指定键值对 CREATE DATABASE database_name with dbproperties ('owner'='Zakikun', 'date'='20221002'); ``` ###### 修改信息 ```sql ALTER DATABASE database_name set dbproperties ('owner'='Zakikun'); ``` 删除 ```sql -- 删除一个空数据库，如果数据库下面有数据表，那么就会报错 DROP DATABSE database_name; -- 强制删除数据库，包含数据库下面的表一起删除 DROP DATABSE database_name cascad ``` #### 表 ##### 创建 ```sql 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] ``` ```sql -- 创建一个指定名字的表：如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常 CREATE TABLE -- 创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION） -- 内部表: 数据移动到指定路径，删除内部表的时候会一并删除metadata和实际数据 -- 外部表：数据不移动，仅记录这个外部表数据所在的路径，删除该外部表的时候只删除metadata，不影响实际数据 EXTERNAL -- 表示注释，默认不能使用中文 COMMENT -- 表示使用分区，一个表可以拥有一个或者多个分区，每个分区单独存在一个目录下 PARTITIONED BY -- 允许用户复制现有的表结构，但是不复制数据 LIKE -- 可用来指定行分隔符，默认分隔符为 '^A' ，在八进制中为 '\001' ROW FORMAT DELIMITED -- 指定该表数据的存储格式，hive 中，表的默认存储格式为 TextFile STORED AS SEQUENCEFILE | TEXTFILE | RCFILE | orc | PARQUET -- 对于每一个表（table）进行分桶(MapReuce中的分区），桶是更为细粒度的数据范围划分。Hive也是 针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中 CLUSTERED BY -- 指定表在 HDFS 上的存储路径 LOCATION -- 指定压缩算法 tblproperties ('orc.compress'='SNAPPY') ``` ##### 删除 ```sql -- 习惯性加上if exists判断是否存在, 会将hdfs一并删除 DROP TABLE if exists table_name ``` ##### 查看表结构 ```sql -- 格式化表格 desc formatted table_name; -- 简单表格 desc table_name; ``` ##### 查看分区信息 ```sql show partitions table_name; ``` ##### 复制表结构 ```sql CREATE TABLE if not exists new_table_name LIKE database.origin_table_name; ``` ##### 复制表（包括数据） ```sql -- 方法1 CREATE TABLE if not exists new_table_name as select * from database.origin_table_name; -- 方法2 CREATE TABLE if not exists new_table_name LIKE database.origin_table_name; INSERT INTO new_table_name SELECT * FROM database.origin_table_name; ``` ##### 表改名 ```sql ALTER TABLE old_table_name rename to new_table_name; ``` ##### 清空表数据 ```sql TRUNCATE TABLE table_name; ``` ##### 插入数据（追加） ```sql -- 将查询数据追加插入到表中 INSERT INTO TABLE table_name [partition(partcol1=val1,partclo2=val2)] select_statement; ``` ##### 插入数据（覆盖/重写） ```sql -- 将查询数据覆盖插入到表中 NISERT OVERWRITE table table_name [partition(partcol1=val1,partclo2=val2)] select_statement; ``` ##### 修改表注释 ```sql ALTER TABLE db.table_name SET TBLPROPERTIES('comment' = '新的表备注'); ``` #### 分区 ##### 增加分区 正常情况下需要分区不存在，如果为了防止分区存在报错可以加 `IF NOT EXISTS` 关键字 ```sql ALTER TABLE database.table_name ADD [IF NOT EXISTS] PARTITION (分区字段名 = 'XXX') LOCATION '$PATH' ``` ##### 删除分区 ```sql ALTER TABLE database.table_name DROP IF EXISTS PARTITION (分区字段名 = 'XXX'); ``` ##### 修改分区 ```sql ALTER TABLE class_info_partition PARTITION (分区字段名 = 'XXX') SET LOCATION '$PATH' ; ``` #### 字段 ###### 增加/创建 ```sql ALTER TABLE table_name ADD COLUMNS( 新增字段名 新增字段类型 comment '新增字段注释', 新增字段名 新增字段类型 comment '新增字段注释' ); ``` ###### 删除字段/反向保留 ```sql ALTER TABLE table_name REPLACE COLUMNS( 保留字段1 保留字段类型1 comment '保留字段类型1注释', 保留字段2 保留字段类型2 comment '保留字段类型2注释', 保留字段3 保留字段类型3 comment '保留字段类型3注释', 保留字段4 保留字段类型4 comment '保留字段类型4注释', 保留字段5 保留字段类型5 comment '保留字段类型5注释', 保留字段6 保留字段类型6 comment '保留字段类型6注释', ...... ); ``` ###### 修改名称/类型/位置/注释 ```sql -- 修改字段名 ALTER TABLE table_name RENAME COLUMN 现字段名 TO 新字段名 -- 修改字段名,类型,注释 ALTER TABLE table_name CHANGE column 现字段名 修改后字段名 修改后类型 comment '修改后注释'; -- 修改后字段位置 ALTER TABLE table_name CHANGE column 现字段1名 修改后字段1名 修改后类型 comment '修改后注释' after 改为在某字段后的某字段名; ``` #### 数据 ##### select ```sql SELECT [ ALL | DISTINCT ] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [HAVING where_condition] [ORDER BY col_list] [CLUSTER BY col_list] [DISTRIBUTE BY col_list] [SORT BY col_list] [LIMIT number] ``` ##### join full outer join 等价于 full join ##### with...as... ```sql WITH temp1 as ( select * from xxx ),temp2 as ( select * from xxx ) select * from temp1,temp2; ``` ```sql table_reference [INNER] 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 table_reference CROSS JOIN table_reference [join_condition] (as of Hive 0.10) ``` ##### 运算符 | 操作符 | 支持的数据类型 | 描述 | | ----------------------- | -------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | A=B | 基本数据类型 | 如果A等于B则返回TRUE，反之返回FALSE | | A<=>B | 基本数据类型 | 如果A和B都为NULL，则返回TRUE，其他的和等号（=）操作符的结果一致，如果任一为NULL则结果为NULL | | A<>B, A!=B | 基本数据类型 | A或者B为NULL则返回NULL；<br />如果A不等于B，则返回TRUE，反之返回FALSE | | A<B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A小于B，则返回TRUE，反之返回FALSE | | A<=B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A小于等于B，则返回TRUE，反之返回FALSE | | A>B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A大于B，则返回TRUE，反之返回FALSE | | A>=B | 基本数据类型 | A或者B为NULL，则返回NULL；<br />如果A大于等于B，则返回TRUE，反之返回FALSE | | A [NOT] BETWEEN B AND C | 基本数据类型 | 如果A，B或者C任一为NULL，则结果为NULL。如果A的值大于等于B而且小于或等于C，则结果为TRUE，反之为FALSE。如果使用NOT关键字则可达到相反的效果。 | | A IS NULL | 所有数据类型 | 如果A等于NULL，则返回TRUE，反之返回FALSE | | A IS NOT NULL | 所有数据类型 | 如果A不等于NULL，则返回TRUE，反之返回FALSE | | IN(数值1, 数值2) | 所有数据类型 | 使用 IN运算显示列表中的值 | | A [NOT] LIKE B | STRING 类型 | B是一个SQL下的简单正则表达式，如果A与其匹配的话，则返回TRUE；反之返回FALSE。<br />B的表达式说明如下：<br />‘x%’表示A必须以字母‘x’开头，<br />‘%x’表示A必须以字母’x’结尾，<br />而‘%x%’表示A包含有字母’x’,可以位于开头，结尾或者字符串中间。<br />如果使用NOT关键字则可达到相反的效果。 | | A RLIKE B, A REGEXP B | STRING 类型 | B是一个正则表达式，如果A与其匹配，则返回TRUE；反之返回FALSE。<br />匹配使用的是JDK中的正则表达式接口实现的，因为正则也依据其中的规则。<br />例如，正则表达式必须和整个字符串A相匹配，而不是只需与其字符串匹配。 | ##### 逻辑运算符 | 操作符 | 含义 | | ------ | ------ | | AND | 逻辑并 | | OR | 逻辑或 | | NOT | 逻辑否 | ##### 算术运算符 | 运算符 | 类型 | 描述 | | ------ | -------- | -------------------------------------------------------------------------------------------------------------------------------- | | A + B | 所有类型 | A与B相加的结果,结果的数值类型等于A的类型和B的类型的最小父类型;<br />int+int一般结果为int类型<br />int+double一般结果为double类型 | | A – B | 所有类型 | A与B相减的结果。结果的数值类型等于A的类型和B的类型的最小父类型 | | A * B | 所有类型 | A与B相乘的结果。结果的数值类型等于A的类型和B的类型的最小父类型 | | A / B | 所有类型 | A除以B的结果。结果的数值类型为double | | A % B | 所有类型 | A除以B的余数 | | A & B | 所有类型 | A和B按位进行与操作的结果 | | A | B | 所有类型 | | A ^ B | 所有类型 | A和B按位进行异或操作的结果 | | ~A | 所有类型 | A按位进行取反操作的结果，结果的数值类型等于A的类型 | ##### 分组 ###### group by ```sql select s_id ,max(s_score) from score group by s_id; ``` ###### having ```sql select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85; ``` where和having区别 ```plaintext having与where不同点 1.where针对表中的列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据 2.where后面不能写分组函数，而having后面可以使用分组函数 3.having只用于group by分组统计语句 ``` ##### 排序 ###### order by 全局排序 ```sql select s_id ,avg(s_score) avg from score group by s_id order by s_id,avg; ``` 对 NULL 值的处理 ```sql -- 将 null 值列在最前 select * from student order by student.s_birth nulls first; -- 将 null 值列在最后 select * from student order by student.s_birth nulls last; ``` ###### Sort By 每个MapReduce内部局部排序 ###### Distribute By 分区排序 ###### cluster by ##### 合并 ###### union 去重合并select ###### union all 不去重合并select #### 常用函数 ##### 数值 | 运算符 | 类型 | 功能说明 | | ---------------------------------------- | ------ | -------------------------------------------------------------------------- | | round(double a) | BIGINT | Hive四舍五入取整 | | round(double a, int d) | DOUBLE | Hive指定保留小数点四舍五入 | | floor(double a) | BIGINT | 对给定数据进行向下取最接近的整数 | | ceil(double a)<br />ceiling(double a) | BIGINT | 参数向上取最接近的整数 | | rand(), rand(int seed) | double | 返回一个0到1范围内的随机数。如果指定种子seed，则会等到一个稳定的随机数序列 | | abs(double a) | double | 取绝对值 | | positive(int a)<br />positive(double a) | double | 取绝对值 | | negative(int a)<br />negative(double a) | double | A的相反数 | ##### 时间 | 函数 | 描述 | 例子 | | ------------------------------------------- | ------------------------------------- | ----------------------------------- | | current_date() | 当前日期 | | | current_time() | | | | datediff(string enddate, string startdate) | 时间差 | datediff('2020-05-29','1997-05-16') | | date_add(string startdate, int days) | 日期加 | date_add('2020-05-29', 7) | | date_sub(string startdate, int days) | 日期减 | date_sub('2020-05-29', 7) | | to_date(string timestamp) | 返回时间中的年月日 | | | from_unixtime | int型转时间戳 | | | unix_timestamp() | 获得当前时区的UNIX时间戳 | | | unix_timestamp(string date, string pattern) | 转换指定pattern格式的日期到UNIX时间戳 | | | year(string date) | 返回指定时间的年份 | | | month(string date) | 返回指定时间的月份 | | | day(string date) | 返回指定时间的天 | | | hour(string date) | 返回指定时间的小时 | | | minute(string date) | 返回指定时间的分钟 | | | second(string date) | 返回指定时间的秒 | | | last_day(string date) | 指定日期字符所在月份的最后一天 | | ##### 条件判断 ```sql case when age < 20 then '20岁以下' when age < 30 then '20-30岁' when age < 40 then '30-40岁' else '40岁以上' end as age_type, ``` ##### 字符串 ```sql substr( stringA, INT start, INT len) ``` | 函数 | 米醋 | | ---------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------- | | length(string A) | 返回字符串A的长度 | | reverse(string A) | reverse(string A) | | concat(string A, string B…) | 返回输入字符串连接后的结果，支持任意个输入字符串 | | concat_ws(string SEP, string A, string B…) | 返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符 | | substr(string A, int start) | 返回字符串A从start位置到结尾的字符串 | | substring(string A, int start) | 返回字符串A从start位置到结尾的字符串 | | substr(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 | | substring(string A, int start, int len) | 返回字符串A从start位置开始，长度为len的字符串 | | upper(string A) | 字符串转大写函数：upper,ucase | | ucase(string A) | 字符串转大写函数：upper,ucase | | lower(string A) | 字符串转小写函数：lower,lcase | | lcase(string A) | 字符串转小写函数：lower,lcase | | trim(string A) | 去除字符串两边的空格 | | ltrim(string A) | 去除字符串左边的空格 | | rtrim(string A) | 右边去空格函数：rtrim | | regexp_replace(string A, string B, string C) | 将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符,类似oracle中的regexp_replace函数 | | regexp_extract(string subject, string pattern, int index) | 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符 | | repeat(string str, int n) | 返回重复n次后的str字符串 | | split(string str, string pat) | 按照pat字符串分割str，会返回分割后的字符串数组 | | find_in_set(string str, string strList) | 返回str在strlist第一次出现的位置，strlist是用逗号分割的字符串。如果没有找该str字符，则返回0 | | locate(string substr, string str[, int pos]) | 查找字符串str中的pos位置后字符串substr第一次出现的位置 | ##### 数据类型转换 ```sql cast (value as type) -- eg. cast ('2019' as int) ``` 最后修改：2025 年 07 月 26 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 如果觉得我的文章对你有用，请随意赞赏
