[jira] [Comment Edited] (CARBONDATA-4047) Datediff datatype is not working with spark-2.4.5. even in spark-sql its showing as null. .Either the query might be wrong or the versions don't support datatypes.

[Akash R Nilugal (Jira)]

    [ https://issues.apache.org/jira/browse/CARBONDATA-4047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17258004#comment-17258004 ]

Akash R Nilugal edited comment on CARBONDATA-4047 at 1/4/21, 6:22 AM:
----------------------------------------------------------------------

The query you have given is wrong, stored as should come first.

correct query would be
1. CREATE TABLE IF NOT EXISTS vestedlogs3 STORED AS carbondata as (select * , intck ("Hours", 'StartTimestamp', 'CompleteTimestamp') as Hours FROM vestedlogs)

This will also fail, as spark doesnt have intck function, it fails for parquet also, you can check.

2. rewrite the query
CREATE TABLE IF NOT EXISTS vestedlogs3  STORED AS carbondata as (SELECT *,DATEDIFF(HOUR,StartTimestamp GETDATE(),CompleteTimestamp GETDATE()) AS "Hours" FROM vestedlogs)

after this again fails for parsing, please check your query again
and can refer https://stackoverflow.com/questions/52527571/datediff-in-spark-sql
spark doesnt support all, you can check for alternative


was (Author: akashrn5):
The query you have given is wrong, stored as should come first.

correct query would be
1. CREATE TABLE IF NOT EXISTS vestedlogs3 STORED AS carbondata as (select * , intck ("Hours", 'StartTimestamp', 'CompleteTimestamp') as Hours FROM vestedlogs)

This will also fail, as spark doesnt have intck function, it fails for parquet also, you can check.

2. rewrite the query
CREATE TABLE IF NOT EXISTS vestedlogs3  STORED AS carbondata as (SELECT *,DATEDIFF(HOUR,StartTimestamp GETDATE(),CompleteTimestamp GETDATE()) AS "Hours" FROM vestedlogs)

after this again fails for parsing, please check your query again
and can refer
[this post|https://stackoverflow.com/questions/52527571/datediff-in-spark-sql]
spark doesnt support all, you can check for alternative

> Datediff datatype is not working with spark-2.4.5. even in spark-sql its showing as null. .Either the query might be wrong or the versions don't support datatypes.
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: CARBONDATA-4047
>                 URL: https://issues.apache.org/jira/browse/CARBONDATA-4047
>             Project: CarbonData
>          Issue Type: Task
>          Components: spark-integration
>    Affects Versions: 2.0.0
>         Environment: Hadoop - 3.2.1
> Hive - 3.1.2
> Spark - 2.4.5
> carbon data-2.0
> mysql connector jar - mysql-connector-java-8.0.19.jar
>            Reporter: sravya
>            Priority: Major
>              Labels: CarbonData, hadoop, hive, spark2.4
>         Attachments: carbon error.PNG, sparksql.PNG
>
>
> 1.scala> carbon.sql("CREATE TABLE IF NOT EXISTS vestedlogs3 as (select * , intck ("Hours", 'StartTimestamp', 'CompleteTimestamp') as Hours FROM vestedlogs) STORED AS carbondata").show()
> <console>:1: error: ')' expected but string literal found.
> carbon.sql("CREATE TABLE IF NOT EXISTS vestedlogs3 as (select * , intck ("Hours", 'StartTimestamp', 'CompleteTimestamp') as Hours FROM vestedlogs) STORED AS carbondata").show()
>  
> 2.scala> carbon.sql("CREATE TABLE IF NOT EXISTS vestedlogs3 (SELECT *,DATEDIFF(HOUR,StartTimestamp GETDATE(),CompleteTimestamp GETDATE() AS "Hours" FROM vestedlogs) STORED AS carbondata").show()
> <console>:1: error: ')' expected but string literal found.
> carbon.sql("CREATE TABLE IF NOT EXISTS vestedlogs3 (SELECT *,DATEDIFF(HOUR,StartTimestamp GETDATE(),CompleteTimestamp GETDATE() AS "Hours" FROM vestedlogs) STORED AS carbondata").show()
>  
>  
>  
>  

--
This message was sent by Atlassian Jira
(v8.3.4#803005)