topic Presto hive table to delta table conversion in Data Engineering

Presto hive table to delta table conversion

SQL — Mon, 15 Nov 2021 17:19:33 GMT

Hi Everyone,

I am using the below sql query to generate the days in order in hive & it is working fine. The table got migrated to delta and my query is failing. It would be appreciated if someone helps me to figure out the issue.

SQL Query :

with

explode_dates as (

with list_of_dates as (

SELECT CAST(date_column AS DATE) AS DAY

FROM (

VALUES (SEQUENCE(cast('2021-01-01' AS date), date_add('day',-1,current_date), INTERVAL '1' DAY) )

) AS t1(date_array)

CROSS JOIN UNNEST(date_array) AS t2(date_column)

)

select a.day as KPI_DATE, month(b.day) as dates_month, b.day as dates from list_of_dates a

join list_of_dates b on b.day between date_trunc('quarter', date_add('day',0,a.day)) and a.day )

Re: Presto hive table to delta table conversion

jose_gonzalez — Mon, 15 Nov 2021 18:51:16 GMT

Hi @Arumugam Perumal ,

Please provide more details. What is the error you are getting? can you share the error stack trace?

Re: Presto hive table to delta table conversion

SQL — Tue, 16 Nov 2021 06:07:04 GMT

Hi @Jose Gonzalez ,

I am getting below error message .I have changed the date_add function in order to compatible with Delta (date_add('day',-1,current_date), INTERVAL '1' DAY) ) --> date_add(current_date,-1))

Error Message:

ERROR processing query/statement. Error Code: 0, SQL state: org.apache.hive.service.cli.HiveSQLException: Error running query: org.apache.spark.sql.AnalysisException: cannot resolve '`date_array`' given input columns: [];

Re: Presto hive table to delta table conversion

jose_gonzalez — Wed, 17 Nov 2021 17:59:55 GMT

Hi @Arumugam Perumal ,

I was checking the list of build in functions and it seems like we do not have "date_array". You can check the docs

Re: Presto hive table to delta table conversion

SQL — Thu, 18 Nov 2021 05:20:08 GMT

could you please help me with the alternative function for date_array

Re: Presto hive table to delta table conversion

jose_gonzalez — Fri, 10 Dec 2021 23:20:32 GMT

Does any other date() function help you to solve this issue?

Re: Presto hive table to delta table conversion

thelogicplus — Wed, 15 Jan 2025 07:35:54 GMT

Hi @SQL @jose_gonzalez , Have you tried code conversion tool fromTravinto technologies ? They have hive to delta table conversion