Presto hive table to delta table conversion
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-15-2021 09:19 AM
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 )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-15-2021 10:51 AM
Hi @Arumugam Perumal ,
Please provide more details. What is the error you are getting? can you share the error stack trace?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-15-2021 10:07 PM
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: [];
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-17-2021 09:59 AM
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-17-2021 09:20 PM
could you please help me with the alternative function for date_array
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-10-2021 03:20 PM
Does any other date() function help you to solve this issue?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tuesday
Hi @SQL @jose_gonzalez , Have you tried code conversion tool fromTravinto technologies ? They have hive to delta table conversion