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

ops/2024/10/25 12:21:28/

目录

前言

oracle%E4%B8%AD%E7%9A%84%EF%BC%88%2B%EF%BC%89%E5%86%99%E6%B3%95-toc" style="margin-left:0px;">1、oracle中的（+）写法

1.1、区分左右连接

hive%E4%B8%AD%E7%9A%84%E5%86%99%E6%B3%95-toc" style="margin-left:40px;">1.2、hive中的写法

a、最常用

b、副表带条件

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

2、select中含有子查询

oracle%E7%9A%84decode%E5%87%BD%E6%95%B0-toc" style="margin-left:0px;">3、oracle的decode函数

oracle%E7%9A%84%E6%97%B6%E9%97%B4%E8%BD%AC%E5%8C%96-toc" style="margin-left:0px;">4、oracle的时间转化

oracle%E7%9A%84trunc%E5%87%BD%E6%95%B0-toc" style="margin-left:0px;">5、oracle的trunc函数

oracle%20instr%E5%87%BD%E6%95%B0%C2%A0-toc" style="margin-left:0px;">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上，两个之间还是有些差异的，特写此文，共大家参考学习。有问题欢迎指正。

oracle%E4%B8%AD%E7%9A%84%EF%BC%88%2B%EF%BC%89%E5%86%99%E6%B3%95">1、oracle中的（+）写法

1.1、区分左右连接

左连接：

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

右连接：

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

hive%E4%B8%AD%E7%9A%84%E5%86%99%E6%B3%95">1.2、hive中的写法

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

建表和插入数据

sql">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、最常用
sql">--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、副表带条件
sql">--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、只显示过滤条件的数据
sql">--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中含有子查询

sql">--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

oracle%E7%9A%84decode%E5%87%BD%E6%95%B0">3、oracle的decode函数

sql">--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值的

oracle%E7%9A%84%E6%97%B6%E9%97%B4%E8%BD%AC%E5%8C%96">4、oracle的时间转化

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

sql">--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;

oracle%E7%9A%84trunc%E5%87%BD%E6%95%B0">5、oracle的trunc函数

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

sql">--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);

oracle%20instr%E5%87%BD%E6%95%B0%C2%A0">6、oracle instr函数 

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

sql">--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(财付通专用)

sql">--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、临时表名

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

8、with插入用法

sql">--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、计算语法 或者||用法不同

sql">--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、日期格式不标准的转换

sql">--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

sql">--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、日期格式化

sql">--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、类型兼容问题

sql">--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 (左右填充函数)

sql">--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、时间相减

sql">--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语句

sql">------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语句：

sql">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.ppmy.cn/ops/128328.html

相关文章

【ChatGPT】编写结构化 Prompt 的技巧

【ChatGPT】编写结构化 Prompt 的技巧

编写结构化 Prompt 的技巧 在与 ChatGPT 互动时，结构化 Prompt 是提升模型输出质量的有效手段。通过使用清晰的格式、分步指导以及明确的任务要求，您可以获得更符合预期的输出。本文将介绍如何通过编写结构化 Prompt 来优化 ChatGPT 的生成结果。 一、…
阅读更多...
Crawler4j在多线程网页抓取中的应用

Crawler4j在多线程网页抓取中的应用

网页爬虫作为获取网络数据的重要工具，其效率和性能直接影响到数据获取的速度和质量。Crawler4j作为一个强大的Java库，专门用于网页爬取，提供了丰富的功能来帮助开发者高效地抓取网页内容。本文将探讨如何利用Crawler4j进行多线程网页抓取&…
阅读更多...
Ubuntu20.04 更新Nvidia驱动 + 安装CUDA12.1 + cudnn8.9.7

Ubuntu20.04 更新Nvidia驱动 + 安装CUDA12.1 + cudnn8.9.7

一、概述 最近客户给了几台GPU服务器，长期放置落灰那种，然后想利用起来，所以上去看看了配置，系统是Ubuntu20.04，相关的驱动版本稍嫌老一些，所以需要更新Nvidia驱动，同时在安装CUDA和CUDNN&#…
阅读更多...
Leetcode 柱状图中最大的矩形

Leetcode 柱状图中最大的矩形

h 是右边界&#xff0c;连续多个高度递增的柱子&#xff0c;如果遇到下一个 h < 栈顶元素(是最大的元素&#xff0c;单调递增栈)&#xff0c;那么会不断出栈来更新计算最大面积。 并非是一次性计算出最大面积的&#xff0c;很重要的一点是while (!stack.isEmpty()这一部分的…
阅读更多...
教育平台的创新设计：Spring Boot实现

教育平台的创新设计：Spring Boot实现

3系统分析 3.1可行性分析 通过对本信息化在线教学平台实行的目的初步调查和分析&#xff0c;提出可行性方案并对其一一进行论证。我们在这里主要从技术可行性、经济可行性、操作可行性等方面进行分析。 3.1.1技术可行性 本信息化在线教学平台采用Spring Boot框架&#xff0c;JA…
阅读更多...
C/C++逆向：字符数组与字符串对比

C/C++逆向：字符数组与字符串对比

在逆向工程中&#xff0c;字符数组和字符串的处理是一个重要的方面。字符数组通常是由相同类型的数据元素组成的集合&#xff0c;存储在连续的内存空间中&#xff0c;而字符串则是字符数组的一种特殊形式&#xff0c;用于表示文本信息。字符数组和字符串的区别主要体现在它们的…
阅读更多...
VScode分文件编写C++报错 | 如何进行VScode分文件编写C++ | 不懂也能轻松解决版

VScode分文件编写C++报错 | 如何进行VScode分文件编写C++ | 不懂也能轻松解决版

分文件编写遇到的问题 分文件编写例子如下所示&#xff1a; 但是直接使用 Run Code 或者 调试C/C文件 会报错如下&#xff1a; 正在执行任务: C/C: g.exe 生成活动文件 正在启动生成… cmd /c chcp 65001>nul && D:\Librarys\mingw64\bin\g.exe -fdiagnostics-col…
阅读更多...
云原生后端（Cloud-Native Backend）

云原生后端（Cloud-Native Backend）

云原生后端&#xff08;Cloud-Native Backend&#xff09;是指在云计算环境中&#xff0c;利用云原生技术&#xff08;如容器、微服务、服务网格等&#xff09;构建和部署后端应用程序的一种方法。这种方法的兴起得益于云计算和微服务架构的快速发展&#xff0c;以及企业对高效…
阅读更多...
最新文章

Copyright @ 2022~2023