oracle转sparksql中遇到某些函数转换问题

1.listagg
行转列函数
LISTAGG(Item_Category_Name ‘,’) WITHIN
GROUP(ORDER BY Item_Category_Name）//oracle
经过多方查询：
使用
CONCAT_WS(",”，collect_set(Item_Category_Name)) //over (oder by Item_Category_Name);//sparksql

2.regexp_substr(b.orgcode,’[^.]+’,1,2) 部门组
3010100.50320.1665.112
此函数为正则取部门编号即50320.
用sparksql替换为
regexp_extract（b.orgcode,’(\\d+)\\.(\\d+)\\.(\\d+).\\(\\d+)’,2)
取第二个匹配括号的值

3.select * from (select * from abc )
此处需要注意，必须要给from后嵌套的selelct子句外侧加一个表别名。
即select * from (select * from abc ) b，不然sparksql会报错

4.with as等创建临时表的操作一般采用中间registerTemptable来进行，不用with as的语法。
5.对于Oracle存过中sql过长的情况一般采用拆分的方式来进行，以免spark解析器无法解析sql报错。

6.对于row_number（）over (partion by ````)这种函数，容易报错无法获取内存资源，需要在代码开头加一段参数配置如
下：val sc=new SparkContext(conf)
val sqlContext=new HiveContext(sc)
sqlContext.setConf(“spark.sql.tungsten.enabled”,“ture”)

7.sparksql 不支持oracle中update、delete关于多行数据的操作。一般来说用writeparquet进行落地，中间表用registerTemptable进行注册临时表来处理。这里说一下对于落地的表需要加载到已经建好的表中，属于overwrite的操作，所以一般对于增量层采用直接落地（有效期一般是一天左右），全量层数据一般用增量数据和以往全量进行union操作，实际上也是一个update的操作。中间表一定需要droptemptable

8.对于监控日志等需要做逐条插入的操作，sparksql可以使用
insert into table abc_cvt_injust select
变量1，变量2，变量3;
如以上语法可以满足逐条插入的需求。
一般用于多个sql进行日志监控，用一张表存放日志信息。

9.对于需要insert into 的表，去掉该语法，直接select ，然后将select的df 写成parquet，然后再利用load 加载入hive表中，使用overwrite的方式，其中如果出现落地以后，select中出现多个相同字段，请给该列加一个别名，别名参考具体插入的目标表该列别名，否则写入parquet会报错出现相同列的问题，

10.对于oracle11g中出现unpivot的语法，可以使用
原本：

select user_account, signup_date, src_col_name, friend_email
from email_signup unpivot((friend_email) for src_col_name in(user_email,
                                                               friend1_email,
                                                               friend2_email,
                                                               friend3_email));
select user_account,signup_date,'USER_EMAIL' as src_col_name,user_email as friend_email from email_signup
where user_email is not null
union
select user_account,signup_date,'FRIEND1_EMAIL' as src_col_name,user_email as friend_email from email_signup
where friend1_email is not null
union
select user_account,signup_date,'FRIEND2_EMAIL' as src_col_name,user_email as friend_email from email_signup
where friend2_email is not null
union
select user_account,signup_date,'FRIEND3_EMAIL' as src_col_name,user_email as friend_email from email_signup
where friend3_email is not null;
具体参考： 
http://blog.itpub.net/26506993/viewspace-2057034/
11.遇到rollup和grouping一起混用的，在sparksql中无法使用多个rollup和字段并列group by。 
group by a,b,rollup(c,d,e),rollup(f)
以上情况sparksql不支持必须改为单个rollup或cube。 
with tmp1 as select 、、、、、
group by rollup(a,b,c,d,e,f)
union(去重)
select ''''''''
group by rollup(a,b,f,c,d,e)
tmp2 as select * from tmp1 f where 
f.a is nou null and f.b is not null
使用两个rollup把所有需要的情况合并去重以后再筛掉不需要的数据。用临时表将结果筛掉不需要的数据。 
decode(groupping(t.cassfolder),1,'900010',t.cassfolder)
单个grouping需要改为 
case when t.cassfolder is null then '900010' else t.cassfolder end 
12.使用scala xml解析，如果报错出现for循环空指针问题，报错地方为for循环头部，其实为for循环内部某变量没有赋值，报错并不准确 
13.pivot 在转的过程中 
select 
         casefolderid, LASTADJUSTTIME, LASTLAWSUITTIME, LASTPAYBACKTIME
          from a t1 pivot(max(operatetime) for task_def_key in('task_adjust'
                                                                LASTADJUSTTIME,
                                                               'task_lawsuit'
                                                               LASTLAWSUITTIME,
                                                               'task_payBack'
                                                               LASTPAYBACKTIME)) ;
改为用如下转换 
select casefolderid,
					          max( case when task_def_key='task_adjust' then operatetime else null end) LASTADJUSTTIME,
							  max( case when task_def_key='task_lawsuit' then operatetime else null end) LASTLAWSUITTIME,
							  max( case when task_def_key='task_payBack' then operatetime else null end) LASTPAYBACKTIME
					   from a 
					   group by casefolderid 
其实多复杂都可以用其变形 
pivot (max(a)) for b in ('c' d)
max(case when b=c then a else null end )  d	
group by others;
14.提示找不到***.parquet文件
 1.select 列有重名列
 2.write parquet 前打印schema与目标表结构比对
 3.目标表的路径需要删除重新建立
 hadoop dfs -rm -r /hdfs/table
 hadoop dfs -mkdir /hdfs/table
                    oracle转sparksql中遇到某些函数转换问题1.listagg  行转列函数 LISTAGG(Item_Category_Name ‘,’) WITHIN GROUP(ORDER BY Item_Category_Name）//oracle 经过多方查询： 使用 CONCAT_WS(“,”，collect_set(Item_Category_Name))  over (od...
				简介：本文主要介绍Amazon Redshift如何迁移到MaxCompute，主要从语法对比和数据迁移两方面介绍，由于Amazon Redshift和MaxCompute存在语法差异，这篇文章讲解了一下语法差异
本文档详细介绍了Redshift和MaxCompute之间SQL语法的异同。这篇文档有助于加快sql任务迁移到MaxCompute。由于Redshift和MaxCompute之间语法存在很多差异，因此我们需要修改Redshift上编写的脚本,然后才能在MaxCompute中使用，因.
				背景及问题
本文基于spark 3.1.2
最近在排查spark sql问题的时候，出现了一系列的(CTE)with操作，导致该任务运行不出来，而把对应的(CTE)with 替换成了临时表以后，任务很快的就能运行出来
对应的最简化的sql如下：
with temp1 as (
  select 
    null as user_id
    ,a.sku_id
    ,int(sum(`可申请数量`)) `开放申请数量`
  from xxx.xxx `a`
  where pt between '20
				一．WITH AS的含义
WITH AS短语，也叫做子查询部分（subquery factoring），可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION
 ALL的不同部分，作为提供数据的部分。
特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行
				前言：最近在写一些比较复杂的SQL，是一些统计分析类的，动不动就三四百行，也是首次写那么长的SQL，有用到一些奇形怪状的SQL函数，在这里结合网上的例子做一些笔记，以后用到不记得用法可以翻出来看！
1.基础用法：LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)，就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来
				转自：http://www.cnblogs.com/feiyumo/p/8763186.html
1.concat对于字符串进行拼接
concat(str1, str2, …, strN) - Returns the concatenation of str1, str2, …, strN.
Examples:> SELECT concat(‘Spark’, ‘SQL’);　　SparkSQ...
                Failed to execute goal net.alchim31.maven:scala-maven-plugin:3.2.2:compile问题解决
                    Ljl2003825: 
                    大哥第三种能详细一点吗
                Failed to execute goal net.alchim31.maven:scala-maven-plugin:3.2.2:compile问题解决
                    ᝰ742: 
                    第三种方法运行出来没有jar包，但是类加载出来了
                Failed to execute goal net.alchim31.maven:scala-maven-plugin:3.2.2:compile问题解决
                    秦风古道: 
                    采用第三种方式成功打包，感谢
                Failed to execute goal net.alchim31.maven:scala-maven-plugin:3.2.2:compile问题解决
                    qq_46524188: 
                    那个命令加哪
                spark记录单个task卡住的，导致作业不结束的问题
                    deepthinkers
                    weixin_43605606: 
local模式相当于本地的一个进程。直接用kill -9  进程号就可以