oracle和hive之间关于sql的语法差异及转换

目录

前言

1、oracle中的（+）写法

1.1、区分左右连接

1.2、hive中的写法

a、最常用

b、副表带条件

c、只显示过滤条件的数据

2、select中含有子查询

3、oracle的decode函数

4、oracle的时间转化

5、oracle的trunc函数

6、oracle instr函数 

7、截取

7、临时表名

8、with插入用法

9、计算语法 或者||用法不同

10、日期格式不标准的转换

11、WM_CONCAT

12、日期格式化

13、类型兼容问题

14、Oracle中pivot/和unpivot函数 

15、RPAD LPAD (左右填充函数)

16、时间相减

17、INSERT语句

18、HIVE特性

前言

最近工作需要从Oracle数据库SQL、存储过程迁移到hive上，两个之间还是有些差异的，特写此文，共大家参考学习。有问题欢迎指正。

1、oracle中的（+）写法

1.1、区分左右连接

左连接：

select a.*,b.*from table1 a, table2 b
where a.id = b.id(+)

右连接：

select a.*,b.*from table1 a, table2 b
where a.id(+) = b.id

1.2、hive中的写法

以左连接进行讲解，右连接同理

建表和插入数据

CREATE TABLE Test_Departments
(
depID NUMBER(38,0),
depName VARCHAR2(20),
delFlag NUMBER(1,0)
);--生成员工表
CREATE TABLE Test_Employees
(
empID NUMBER(38,0),
empName VARCHAR2(20),
depID NUMBER(38,0),
delFlag NUMBER(1,0)
);--插入测试数据
INSERT INTO Test_Departments VALUES(1,'FI',0);
INSERT INTO Test_Departments VALUES(2,'MA',0);
INSERT INTO Test_Departments VALUES(3,'HR',1);
INSERT INTO Test_Departments VALUES(4,'IT',0);
INSERT INTO Test_Employees VALUES(1,'wbq',1,0);
INSERT INTO Test_Employees VALUES(2,'czh',2,0);
INSERT INTO Test_Employees VALUES(3,'chh',1,0);
INSERT INTO Test_Employees VALUES(4,'wal',2,0);
INSERT INTO Test_Employees VALUES(5,'ddd',3,0);
a、最常用
--Oracle中的写法
SELECT A.*, B.*FROM Test_Departments A, Test_Employees B
WHERE A.depID = B.depID(+);--Hive中的写法
SELECT A.*, B.*FROM Test_Departments ALEFT JOIN Test_Employees B ON A.depID = B.depID;
b、副表带条件
--Oracle中的写法：
SELECT A.*, B.*
FROM Test_Departments A, Test_Employees B
WHERE A.depID = B.depID(+)
AND (B.depID(+) = 3);--Hive中的写法：
SELECT A.*, B.*
FROM Test_Departments A
LEFT JOIN Test_Employees B ON A.depID = B.depID
AND B.depID = 3;

这里涉及到一个 left join 后面的过滤条件是on和where 的问题

select A.*,B.* from A left join B on A.id=B.id and B.id=3

返回结果：显示A表所有数据，B.id=3的数据，B.id不等于3的默认为空

select A.*,B.* from A,B where A.id=B.id(+) and B.id=3

返回结果：仅仅显示B.id=3的一条数据

c、只显示过滤条件的数据
--Oracle
SELECT A.*, B.*
FROM Test_Departments A, Test_Employees B
WHERE A.depID = B.depID(+)
AND (B.depID = 3);--Hive
SELECT A.*, B.*
FROM Test_Departments A
LEFT JOIN Test_Employees B ON A.depID = B.depID
WHERE B.depID = 3;

这种方式也可以选择使用join实现

以上这段参考：

oracle (+)学习-CSDN博客

2、select中含有子查询

--Oracle：
select a.id, (select b.id from b where b.name=a.id) from a --hive 是不支持select 里面子查询 修改如下：
select a.id ,b.id from a left join b on a.id=b.name

3、oracle的decode函数

--Oracle：decode('key',if1,then1 ,if2,then2...thenN)--Hive：--1、一般来改为：case when key = if1 then then1  when key = if2 then then2 ...else thenN end--2、如果decode比较简单 可以直接改为 :if('key'=if1,then1,then2)--复杂的改为:case when 注意hive有个decode函数是编码函数，不是用来处理null值的

4、oracle的时间转化

某字符串yyyyMM获取上个月时间

--oracle：
select to_char(add_months(to_date('202202','yyyymm'),-1),'yyyymm') 
from dual;SELECT 'yyyyMM', to_char (SYSDATE,'yyyyMM') FROM dual UNION ALL 
SELECT 'yyyy-MM', to_char (SYSDATE,'yyyy-MM') FROM dual UNION ALL 
SELECT 'yyyy-MM-dd', to_char (SYSDATE,'yyyy-MM-dd') FROM dual UNION ALL 
SELECT 'yyyy/MM/dd', to_char (SYSDATE,'yyyy/MM/dd') FROM dual;TO_CHAR(TO_DATE(REPLACE(ADJ.VAR1, '-',''), 'YYYYMMDD'), 'yyyy')--hive：
select DATE_FORMAT(current_timestamp,'yyyy-MM-dd'); --转换为字符串格式select to_date(current_timestamp); --日期格式select 'yyyyMM', DATE_FORMAT(current_timestamp,'yyyyMM') union all
select 'yyyy-MM', DATE_FORMAT(current_timestamp,'yyyy-MM') union all
select 'yyyy-MM-dd', DATE_FORMAT(current_timestamp,'yyyy-MM-dd') union all
select 'yyyy/MM/dd', DATE_FORMAT(current_timestamp, 'yyyy/MM/dd');with tmp as (
select '2023-12-23' VAR1
)
select from_unixtime(unix_timestamp(replace(ADJ.VAR1, '-',''),'yyyyMMdd'),'yyyy')from tmp adj;

5、oracle的trunc函数

oracle的trunc函数改为hive的函数_hive对应oracle的trunc函数-CSDN博客

--hive
select TRUNC(current_date,'YYYY') year,
TRUNC(current_date,'MM') month,
last_day(current_date);--季初：
select 'floor_quarter',date_format(floor_quarter(timestamp('2024-09-23')),'yyyy-MM-dd HH:mm:ss');--当前时间属于哪个季度：
select quarter(current_date);

6、oracle instr函数 

Oracle中的instr()函数 详解及应用_oracle instr-CSDN博客

--oracle
SELECT instr('1234567890123456789','3') FROM dual  -- 3
SELECT instr('1234567890123456789','3',1) FROM dual  -- 3 ,从第1位开始查找第一个3
SELECT instr('1234567890123456789','3',1,2) FROM dual --13 从第1位开始查找第二个3
SELECT instr('1234567890123456789','3',4) FROM dual   -- 13  从第4位开始查找第一个3
SELECT instr('1234567890123456789','3',4,1) FROM dual  --13 从第4位开始查找第一个3
SELECT instr('1234567890123456789','3',4,2) FROM dual  --0  从第4位开始查找第二个3--select instr('被查找的字符串','我们需要查找的字符',从第几位开始 首位是0,查找第几个出现的)--hive--instr(str, substr)  Returns the index of the first occurance of substr in str
SELECT instr('1234567890123456789','3'); -- 3--locate函数
select locate('3','12345123',4) --8
select locate('3','12345123',1) --3
-- 这个locate函数也是找到字符串的下标 locate('要找的字符','被找的字符串',' 从下标多少开始找')。

7、截取

字段格式是： xx1.xxxxx2.xx3.xxx4.xx5.xx6   我们需要xx3格式的数据

缺省.受限制现金-人民币-风险准备金专户.工行高新支行321413RMB(财付通专用).缺省.缺省.缺省.缺省.缺省   ->  工行高新支行321413RMB(财付通专用)

--Oracle：
SELECT 
SEGMENT_NAME_MERGE, 
SUBSTR(T.SEGMENT_NAME_MERGE,INSTR(T.SEGMENT_NAME_MERGE, '.', 1, 2) + 1,(INSTR(T.SEGMENT_NAME_MERGE, '.', 1, 3) - INSTR(T.SEGMENT_NAME_MERGE, '.', 1, 2)) - 1) 
FROM ODSERPDATA.ODS_CE_GL_ACCOUNT_Q T--hive
with tmp as(
select '缺省.受限制现金-人民币-风险准备金专户.工行高新支行321413RMB(财付通专用).缺省.缺省.缺省.缺省.缺省' text
)
select SUBSTRING_INDEX(substring_index(a.text,'.',3),'.',-1)from tmp a;select  replace(substring_index(a,'.',3),substring_index(a,'.',2)||'.',''),
substr(a,length(substring_index(a,'.',2))+2,length(substring_index(a,'.',3))-length(substring_index(a,'.',2))-1),regexp_extract(a,'.*?\\..*?\\.(.*?)\\.+',1)
from (select '缺省.受限制现金-人民币-风险准备金专户.工行高新支行321413RMB(财付通专用).缺省.缺省.缺省.缺省.缺省' a )t
--上面是三种办法。 1.是替换 2.是截取 3是正则

7、临时表名

--Oracle：SELECT * FROM (SELECT 1,2 FROM dual );--hive：必须临时表名select * from (select 1, 2 )t;  --正确select * from (select 1, 2 );  --错误

8、with插入用法

--Oracle：
INSERT INTO  TEST.CC_STUDENT_02
WITH tmp AS (SELECT * FROM TEST.CC_STUDENT_02 cs )
SELECT * FROM tmp;--Hive
WITH tmp AS (SELECT * FROM TEST.CC_STUDENT_02 cs )
INSERT INTO  TEST.CC_STUDENT_02
SELECT * FROM tmp;

9、计算语法 或者||用法不同

--Oracle：
SELECT substr('202212', 1, 4) - 1 || 'aa' FROM dual   -- 2021aaSELECT 1||NULL||2 FROM dual  -- 12--Hive
SELECT substr('202212', 1, 4) - 1 || 'aa' -- 2021.0aa--解决办法：SELECT cast(substr('202212', 1, 4) - 1 as int)|| 'aa' -- 2021aaSELECT cast(substr('202212', 1, 4) as int) - 1|| 'aa' -- 2021aa--因为int-int=int 。 string-int 和int-string=double

10、日期格式不标准的转换

--Oracle
SELECT to_date('2017-3-31', 'yyyy/mm/dd') FROM dual UNION ALL 
SELECT to_date('2018/11/6', 'yyyy/mm/dd') FROM dual UNION ALL 
SELECT to_date('2017/6/20', 'yyyy/mm/dd') FROM dual UNION ALL 
SELECT to_date('2017-06-20', 'yyyy/mm/dd') FROM dual UNION ALL 
SELECT to_date('20170620', 'yyyy/mm/dd') FROM dual --Hive
with tmp as
(SELECT '2017-3-31' var UNION ALL 
SELECT '2018/11/6' UNION ALL 
SELECT '20170620'
)
select case when length(a.var) = 8 then date(from_unixtime(unix_timestamp(a.var,'yyyyMMdd'),'yyyy-MM-dd'))else date(replace(a.var,'/','-')) endfrom tmp a;

11、WM_CONCAT

--Oracle：
SELECT t.id ,to_char(WM_CONCAT(name)),WM_CONCAT(name)
FROM (
SELECT 1 AS id ,1 as name  FROM dual UNION ALL 
SELECT 1 AS id ,2 as name  FROM dual UNION ALL 
SELECT 1 AS id ,1 as name  FROM dual UNION ALL 
SELECT 2 AS id ,4 as name  FROM dual UNION ALL 
SELECT 2 AS id ,5 as name  FROM dual 
)t 
GROUP BY t.id;--Hive
with tmp as (
SELECT 1 AS id ,'1' as name UNION ALL 
SELECT 1 AS id ,'2' as name UNION ALL 
SELECT 1 AS id ,'1' as name UNION ALL 
SELECT 2 AS id ,'4' as name UNION ALL 
SELECT 2 AS id ,'5' as name
)
select id,concat_ws(',',collect_list(name))  --未去重,concat_ws(',',collect_set(name))  --去重from tmp a
group by id;

12、日期格式化

--Oracle：
SELECT to_date('2023-01-02 15:55:03', 'yyyy-mm-dd hh24:mi:ss')
,TO_DATE('2023-01-02 15:55:03', 'YYYY-MM-DD HH24:MI:SS')FROM dual--Hive
select DATE_FORMAT('2023-01-02 15:55:03','yyyy-MM-dd HH:mm:ss')

13、类型兼容问题

--Oracle：
with t as (select '1' a union allselect '11'a union allselect '2' a union allselect '3' a
)
select t.a from t  order by a;--Hive：
with t as (select '1' a union allselect '11'a union allselect '2' a union allselect '3' a
)
select t.a from t  order by cast(a as int );

14、Oracle中pivot/和unpivot函数 

oracle 学习之 unpivot/pivot函数及hive实现该功能_cclovezbf的博客-CSDN博客

15、RPAD LPAD (左右填充函数)

--Oracle：
SELECT RPAD ('1234',10),LENGTH(RPAD ('1234',10)) FROM dual -- 1234      10  --注意 1234后面又6个空格SELECT RPAD ('1234',10,'a'),LENGTH(RPAD ('1234',10,'a')) FROM dual --1234aaaaaa 10--hive：
SELECT RPAD ('1234',10,' '),LENGTH(RPAD ('1234',10,' ')),RPAD ('1234',10,'a'),lPAD ('1234',10,'a')

16、时间相减

--Oracle
SELECT SYSDATE -1 FROM dual  --获取昨天SELECT to_date('2023-05-03','YYYY-MM-DD') -to_date('2023-05-02','YYYY-MM-DD') FROM dual  --1  两天时间差SELECT SYSDATE -to_date('2023-05-02','YYYY-MM-DD') FROM dual--9.64637731481481481481481481481481481481 时间差 计算了时分秒---hive
select `current_timestamp`(), `current_timestamp`() -to_date('2023-05-10')-- 2023-05-11 15:33:00.779000000,1 15:33:00.779000000 但是不推荐 因为看起来既直观又不直观--计算时间差一般采用的是datediff函数select datediff(current_timestamp(),to_date('2023-05-10'))  -- 1 计--算前几天后几天采用date_add date_subselect current_date,date_add(current_date,1),date_sub(current_date(),1)---相差的秒数
select (unix_timestamp('2024-10-23 14:12:34') - unix_timestamp('2024-10-23 12:12:34'))*1.00000---相差的月数
SELECT months_between('2022-12-01', '2022-01-01') AS months_difference---相差小时
select (unix_timestamp('2024-10-23 14:13:34') - unix_timestamp('2024-10-23 12:12:34'))*1.00000 / 3600---相差天数
select (unix_timestamp('2024-10-24 14:13:34') - unix_timestamp('2024-10-23 12:12:34'))*1.00000 / 86400

以上参考文章：oracle和hive之间关于sql的语法差异及转换_hivesql 与oracle 语句区别-CSDN博客

17、INSERT语句

------ORACLE：EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_A';
INSERT /*+APPEND*/ INTO A NOLOGGING (
COL_1,
COL_2,
COL_3,
);------Hive:
INSERT OVERWRITE TABLE TABLE_A;

18、HIVE特性

在迁移的时候，会遇到一些HIVE的特性导致无法顺利迁移，如下ORACLE语句：

SELECT *
FROM A
GROUP BY A.COL_1
HAVING COUNT(DISTINCT A.COL_2) = 2;

在HIVE中运行会遇到如下报错：

FAILED: SemanticException [Error 10002]: Line 19:22 Invalid column reference 'COL_2'

去掉DISTINCT后可以正常执行。

属于HIVE的特性导致的问题，HAVING子句中无法使用DISTINCT关键字，在迁移中需要进行取舍。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/455543.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

你还在使用存储过程吗？

你还在使用存储过程吗？

上周，reddit 网 r/dotnet 区的网友 technolang 发帖：「你还在使用存储过程吗？」 我很好奇为什么 2024 年了我们还在使用存储过程。难道网络应用中没有一个业务层来处理所有事情吗？依赖 DBA 并在数据库层创建依赖关系似乎没有必要。…
阅读更多...
【WRF数据处理】基于GIS4WRF插件将geotiff数据转为tiff（geogrid，WPS所需数据）

【WRF数据处理】基于GIS4WRF插件将geotiff数据转为tiff（geogrid，WPS所需数据）

【WRF数据处理】基于GIS4WRF插件将geotiff数据转为tiff（geogrid，WPS所需数据） 数据准备：以叶面积指数LAI为例QGis实操：基于GIS4WRF插件将geotiff数据转为tiff警告：GIS4WRF: Input layer had an unexpected …
阅读更多...
ONLYOFFICE 文档8.2版本已发布：PDF 协作编辑、改进界面、性能优化等更新

ONLYOFFICE 文档8.2版本已发布：PDF 协作编辑、改进界面、性能优化等更新

ONLYOFFICE 在线编辑器最新版本已经发布，其中包含30多个新功能和500多个错误修复。阅读本文了解所有更新。 关于 ONLYOFFICE 文档 ONLYOFFICE 是一个开源项目，专注于高级和安全的文档处理。坐拥全球超过 1500 万用户，ONLYOFFICE 是在线办公领…
阅读更多...
2024年 Spring Boot 系列学习宝典！！！！！

2024年 Spring Boot 系列学习宝典！！！！！

欢迎来到Spring Boot的世界！本系列文章旨在为开发者提供从入门到精通的全面指导，无论你是Spring Boot新手还是有经验的开发者，都能在这里找到有价值的内容。让我们一起踏上这段旅程，探索如何使用Spring Boot构建高效、可扩展的应用…
阅读更多...
Redis底层和缓存雪崩，击穿，穿透

Redis底层和缓存雪崩，击穿，穿透

一、Redis的数据结构 1.动态字符串 我们知道Redis中保存的Key是字符串，value往往hi字符串或者字符串的集合。可见字符串是Redis中最常用的一种数据结构。不过，Redis 没有直接使用c语言的字符串，因为c语言字符串存在许多问题： …
阅读更多...
蚁剑连接本地木马文件报错

蚁剑连接本地木马文件报错

项目场景： 本地搭建php和蚁剑环境，连接本地木马文件ma.php 问题描述 使用蚁剑连接localhost时报错 错误{ "address":"127.0.0.1" "code":"ECONNREFUSED", "errno":"ECONNREFUSED", &qu…
阅读更多...
【Kubernetes实战】Kubernetes集群搭建(虚拟机环境，一主两从)

【Kubernetes实战】Kubernetes集群搭建(虚拟机环境，一主两从)

目录 一、 以Node1节点为例创建虚拟机二、 环境初始化三、集群所需组件安装1. docker（18.06.3）2. 安装Kubernetes组件 四、安装Kubernetes集群1. 准备集群镜像2. 集群初始化3. 安装网络插件 五、环境测试(服务部署) 集群规模：一主二从(一个ma…
阅读更多...
云计算实验1——基于VirtualBox的Ubuntu安装和配置

云计算实验1——基于VirtualBox的Ubuntu安装和配置

实验步骤 1、VirtualBox的安装 本实验使用VirtualBox-7.0.10 进行演示。对于安装包，大家可以前往 VirtualBox官网下载页面(https :/ / www. virtualbox.org/wiki/Downloads)下载其7.0版本安装包进行安装，或者直接使用QQ群的安装包VirtualBox-7.0.10-15…
阅读更多...
数字英文验证码识别 API 对接说明

数字英文验证码识别 API 对接说明

本文将介绍一种 数字英文验证码识别 API 对接说明，它是基于深度学习技术，可用于识别变长英文数字验证码。输入验证码图像的内容，输出验证码结果。 接下来介绍下 数字英文验证码识别 API 的对接说明。 申请流程 要使用 API，需要…
阅读更多...
腾讯地图SDK 手势失效或冲突的解决办法

腾讯地图SDK 手势失效或冲突的解决办法

前言 由于高德地图sdk开始涨价割韭菜了，因此，我司在降本增效的大背景下，需要把高德地图换成腾讯地图。 在更换sdk过程中，踩了一些关于地图手势事件的坑，这里记录下，希望能给遇到同样问题的大佬们一个思路。…
阅读更多...
21、基于Firefly-rk3399字符设备驱动寄存器控制LED

21、基于Firefly-rk3399字符设备驱动寄存器控制LED

文章目录 一、电路分析引脚配置功能(R/W register) 二、RK3399数据手册分析：1、GPIO(General-purpose input/output)介绍：2、CRU(Clock & Reset Unit)介绍查找GPIO相关内容： 3、PMU(Power Management Uni)4、GRF（General Regi…
阅读更多...
git 报错 SSL certificate problem: certificate has expired

git 报错 SSL certificate problem: certificate has expired

git小乌龟 报错 SSL certificate problem: certificate has expired 场景复现： 原因： 这个错误表明你在使用Git时尝试通过HTTPS进行通信，但是SSL证书已经过期。这通常发生在使用自签名证书或证书有效期已到期的情况下。 解决方法: 1.如果是…
阅读更多...
WTN6 E 系列语音芯片 单线时序及示例代码

WTN6 E 系列语音芯片 单线时序及示例代码

1. 概述: WTN6 系列为多功能，低功耗，高性能的 CMOS 语音芯片。现有 WTN6020E、WTN6040E、 WTN6080E、WTN6170E 四种芯片（语音长度分别为 20s、40s、80s、170s），已投入市场。 音频采样率目前最高可达 32kHz&#xff0…
阅读更多...
streamlit 实现 flink SQL运行界面

streamlit 实现 flink SQL运行界面

实现效果 streamlit flink-playground.py 文件如下： import streamlit as st import io import contextlib import sys import os import uuid import subprocess from jinja2 import Templatest.set_page_config(layout"wide")# 设置页面标题 st.title…
阅读更多...
SL3160 dcdc150V降压5.1V/1A 车载GPS定位器供电芯片

SL3160 dcdc150V降压5.1V/1A 车载GPS定位器供电芯片

一、主要特性 宽输入电压范围：SL3160支持10~150V的宽输入电压范围，使其能够适应各种电源电压波动，确保稳定输出。 高效降压转换：该芯片采用先进的电源管理技术，转换效率高达90%以上，降低了散热压力和整体…
阅读更多...
点云标注工具开发记录（五）之点云文件加载、视角转换

点云标注工具开发记录（五）之点云文件加载、视角转换

在Open3D中，通过read方法，我们可以读取不同格式的点云数据，那么，在不使用Open3D的相关接口时，我们就需要自己重写文件读入、加载、渲染展示方法，效果如下： 点云文件读入 首先，我们要…
阅读更多...
vue开发的一个小插件vue.js devtools

vue开发的一个小插件vue.js devtools

可打开谷歌商城的情况下，不可打开的可以到极简插件里面去下载 极简插件官网_Chrome插件下载_Chrome浏览器应用商店 搜索vue即可
阅读更多...
Flutter仿京东商城APP实战 用户中心基础布局

Flutter仿京东商城APP实战 用户中心基础布局

用户中心界面 pages/tabs/user/user.dart import package:flutter/material.dart; import package:jdshop/utils/zdp_screen.dart; import package:provider/provider.dart;import ../../../store/counter_store.dart;class UserPage extends StatefulWidget {const UserPage…
阅读更多...
Maven入门到实践：从安装到项目构建与IDEA集成

Maven入门到实践：从安装到项目构建与IDEA集成

目录 1. Maven的概念 1.1 什么是Maven 1.2 什么是依赖管理 1.3 什么是项目构建 1.4 Maven的应用场景 1.5 为什么使用Maven 1.6 Maven模型 2.初识Maven 2.1 Maven安装 2.1.1 安装准备 2.1.2 Maven安装目录分析 2.1.3 Maven的环境变量 2.2 Maven的第一个项目 2.2.1…
阅读更多...
古埃及象形文字在线字典

古埃及象形文字在线字典

我在个人网站“小孔的埃及学站点”上推出了在线的象形文字字典，总共收罗了将近700条的象形文字（词）。在线字典的使用方法很简单，在网站各大版块首页的右上方会有如下图所示的查询入口。 点击文本框，输入中文或英文关键…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023