记录oracle转sparksql的问题

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