Code Conversion Chronicles: A Journey from Oracle to BigQuery.

Each and every migration to the cloud has its own flavour and always unfolds a story to tell. It’s important to share these stories with the wider community and learn from them. Today, I am going to share a story on code conversion that may seem simple but surely has something to learn. I was working with a customer on migrating away from Oracle to Google Cloud BigQuery and using the SQL Translation engine to convert the dialect.
Code conversion involves creating migration patterns to ensure compatibility between source and target features.

let’s walkthrough and build the initial subset of state on conversion in Oracle.

select to_date(to_char(sysdate,'DD-Mon-YYYY HH24:MI'),'DD-Mon-YYYY HH24:MI') + 59/86400 requiredDate
from dual ;

let’s break down the component in the Oracle sample sql.

1. Data addition with + operator in Oracle.

2. usage of traditional to_date and to_char function with formatting as till minutes.

Overall its seems simple and as next step we will use public preview features, BigQuery SQL Translation to convert the dialect as shown below.

Oracle to BigQuery using online SQL Translation Engine

--Converted code - Version 1
SELECT
    date_add(parse_date('%d-Mon-%Y %H:%M', format_datetime('%d-Mon-%Y %H:%M', datetime_trunc(current_datetime(), SECOND))), interval CAST(CAST(59 / 86400 as BIGNUMERIC) as INT64) DAY) AS REQUIREDDATE
;

The SQL Translation engine on Google Cloud is an exciting tool that allows you to seamlessly convert your code from one dialect to another, making it easy to work with different databases. Check out my blog on how to “Accelerate DDL migration from Oracle using SQL Translation Engine.”

let’s try to run the version 1 of converted code and validate functionality as per source.

SELECT
   date_add(parse_date('%d-Mon-%Y %H:%M', format_datetime('%d-Mon-%Y %H:%M', datetime_trunc(current_datetime(), SECOND))), interval CAST(CAST(59 / 86400 as BIGNUMERIC) as INT64) DAY) AS REQUIREDDATE
;

Output :- Invalid format: %H is not allowed for the DATE type.

It fails and first learning has to be
Not all converted code has to be functionally correct!

To gain a better understanding of code functionality, it is important to examine how the SQL works in its source. Let’s run the SQL in Oracle and gain insights.

Oracle — Execution of SQL

In Oracle, though the data type can be “Date” but it holds time component like hours, minutes and seconds. However, when the code is converted, it is parsed as a Date and does not include the time component, resulting in an exception in BigQuery.

Let’s fix it manually with datetime related functions in BigQuery.

--Converted code - Version 2
SELECT
    datetime_add(parse_datetime('%d-Mon-%Y %H:%M', format_datetime('%d-Mon-%Y %H:%M', datetime_trunc(current_datetime(), SECOND))), interval CAST(CAST(59 / 86400 as BIGNUMERIC) as INT64) DAY) AS REQUIREDDATE
;

Second learning has to be,
Understanding of Source to uncovers how it use to function.

Now comes the most thrilling part, validating if the converted code functions correctly in BigQuery, just like it does in Oracle. Let’s dive deeper into the functionality in Oracle and see how it measures up.

Oracle SQL — Source Behaviour

In Source, we are trying to get time period of 59th Seconds.We are able to achieve it with 59/86400 addition in the date expression.

59/86400 implies 59 seconds from whole day, 86400 is 1 day.

As we are using + operator for date manipulation it takes fractional input as per Day in Oracle.

SQL> select sysdate + (59/86400) from dual;

SYSDATE+(59/86400)      
_______________________ 
19-Jan-2023 14:27:29 

As we have build strong understanding of how it function in source let’s unfold how it is and should be in target i.e. BigQuery.

BigQuery Comparision.

In BigQuery, Required Date is not giving result as 59th Seconds Time period. Instead it is making seconds component as Zero.

let’s recheck the converted code and try to find the culprit. In Converted code fractional output for 59/86400 is converted to Integer.

SELECT CAST(CAST(59 / 86400 AS BIGNUMERIC) AS INT64)
Output :- 0

Does that mean, BigQuery does not support fractional interval as part of DAY interval. what’s better then testing it out.

BigQuery — Fractional interval for Date Add.

Interesting, as compared to Oracle, BigQuery does not support fractional input for DAY interval. However, as we have built our understanding of the source code in Oracle, we know it is trying to add 59 seconds to the datetime values. So, instead of using DAY interval in the converted code, use SECOND interval.

Third learning has to be,
Always and Always perform functional verification on converted code.

Final converted code.

--Converted code - Version 3
SELECT current_datetime(),
    datetime_add(parse_datetime('%d-Mon-%Y %H:%M', format_datetime('%d-Mon-%Y %H:%M', datetime_trunc(current_datetime(), SECOND))), 
interval 59 SECOND) AS REQUIREDDATE
;

BigQuery Converted Code.

Conclusion :-

I hope you all enjoyed the story of the conversion from Oracle to BigQuery and the lessons it taught us. To reiterate, some key takeaways when working on any migration that involves conversion are:

Not all converted code has to be functionally correct!

Understanding of Source to uncovers how it use to function.

Always and Always perform functional verification on converted code.