2

Need advise on apache calcite. We have some SQL queries running on Teradata. Now we want to run these sql queries (as it is) on Hadoop/Spark perhaps using Apache calcite.

We tried these SQL queries (as it is) in Spark SQL (2.6.3) and also in Apache calcite - but few queries dont run. The problem is if we define a derived variable (AS) and try to use it in the same query at same level, it does not WORK in SparkSQL and calcite but WORKS in Teradata. Example query:

select EMPNO, sum(deptno) as sum_dept, 
   case when sum_dept > 10 then 1 else 0 end as tmp 
from emps 
group by EMPNO; => WORKS in Teradata

But the same does not work in SparkSQL and Calcite. Throws following error:

Error while executing SQL "select EMPNO, sum(deptno) as sum_dept, case when sum_dept > 10 then 1 else 0 end as tmp from emps group by EMPNO": From line 1, column 50 to line 1, column 57: Column 'SUM_DEPT' not found in any table (state=,code=0)

Does anybody (SparkSQL/Calcite expert) know if there is a way to make it work in sparkSQL or calcite?

dnoeth
  • 59,503
  • 4
  • 39
  • 56
sunillp
  • 983
  • 3
  • 13
  • 31

1 Answers1

3

In Standard SQL an alias can only be used in the ORDER BY clause, but Teradata allows it in any place. You must replace the alias with the original calculation:

select EMPNO, sum(deptno) as sum_dept, 
   case when sum(deptno) > 10 then 1 else 0 end as tmp 
from emps 
group by EMPNO;
dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • Thanks dnoeth. Yes, sure this is one way to do it. In our case the alias is a complex one often containing multiple case statements. So wanted to know if there is easy way to do it. I think you are saying this is not possible? – sunillp Jan 27 '18 at 04:27
  • The common way proposed by Standard SQL is nesting the query, i.e. assigning an alias in either a Derived Table or a Common Table Expression and then using it in the outer level. – dnoeth Jan 27 '18 at 11:00
  • Hi, I was looking for a tool to convert Teradata query into Hive/SQL query. I found one site "http://www.sqlines.com/online" that claims to do so. But for the above query it did not convert it correctly. I would love to hear if anybody has used any such tool with success. Desperately need this. – sunillp Jan 28 '18 at 15:55
  • @sunillp did you have any success in finding such tool? – Pushkin Sep 25 '19 at 05:51