topic Re: Converting SQL Code to SQL Databricks in Data Engineering

Converting SQL Code to SQL Databricks

LightUp — Mon, 09 May 2022 13:59:21 GMT

I am new to Databricks. Please excuse my ignorance.

My requirement is to convert the SQL query below into Databricks SQL. The query comes from EventLog table and the output of the query goes into EventSummary

These queries can be found here

CREATE TABLE EventLog 
 
(
 
 EventID INT
 
, EventName String
 
, EventStartDateTime timestamp
 
, EventEndDateTime  timestamp
 
)
 
 
 
 
 
INSERT INTO EventLog(EventID, EventName, EventStartDateTime, EventEndDateTime)
 
VALUES(100, 'Planting', '20210620 10:34:09 AM','20211018 10:54:49 PM')
 
 ,(200, 'Foundation', '20200420 10:34:09 AM','20211018 10:54:49 PM')
 
 ,(300, 'Seeding', '20210410 10:27:19 AM','')
 
 ,(400, 'Spreading', '20220310 10:24:09 PM','');
 
 
 
 CREATE TABLE EventSummary
 
(  
 
 EventID INT
 
, EventName VARCHAR(50) String
 
, [Year] INT
 
, [MonthName] String
 
, [Hours] DECIMAL
 
)

I have a code like so which I want to convert to Databricks SQL:

;WITH CTE AS (
 
  SELECT EventID,EventName,EventStartDateTime,IIF(EventEndDateTime = '',GETUTCDATE(),EventEndDateTime) EventEndDateTime
 
  FROM EventLog
 
  UNION ALL
 
  SELECT EventID,EventName, DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) , EventEndDateTime
 
  FROM CTE 
 
  WHERE DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0) <= EventEndDateTime
 
)
 
INSERT INTO EventSummary(EventID,EventName,Year,MonthName,Hours)
 
SELECT EventID,EventName,YEAR(EventStartDateTime),DATENAME(MONTH,EventStartDateTime),DATEDIFF(second, EventStartDateTime, n_EventStartDateTime) / 3600.0
 
FROM (
 
 SELECT EventID,EventName,EventStartDateTime,LEAD(EventStartDateTime,1,EventEndDateTime) OVER(PARTITION BY EventID,EventName ORDER BY EventStartDateTime) n_EventStartDateTime
 
 FROM CTE
 
) t1
 
option (maxrecursion 0)

This query works very well against SQL Server but I am having issues converting this to SQL Databricks

I want to get the same result but in Databricks, I get errors that CTE cannot be found because I am calling CTE inside the first CTE

I greatly appreciate your help on this.

Re: Converting SQL Code to SQL Databricks

Anonymous — Mon, 09 May 2022 15:27:40 GMT

Spark SQL does have Common Table Expressions: https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-cte.html

I'm not an expert at SQL or CTEs, but my guess is that it's the recursion option.

Re: Converting SQL Code to SQL Databricks

LightUp — Mon, 09 May 2022 15:43:07 GMT

Thank you @Joseph Kambourakis

The part that is not clear to me from the how to rework the part circled in the image above.

Even this part of the code does not work in databricks:

DATEADD(month, DATEDIFF(month, 0, DATEADD(month , 1 , EventStartDateTime)), 0)

Tried converting too but not sure which function(s) can replace those to get the same answers

Looking forward to more ideas

Re: Converting SQL Code to SQL Databricks

thelogicplus — Sat, 14 Dec 2024 18:13:12 GMT

you may explore the tool and services from Travinto Technologies . They have very good tools. We had explored their tool for our code coversion from Informatica, Datastage and abi initio to DATABRICKS , pyspark. Also we used for SQL queries, stored procedure, trigger, cursor etc.