Dataiku DSS 7 AMA is live! Learn more

Filter using an sql expression

Level 2
Filter using an sql expression

Hello, 

i'm kinda new to dataiku, 

I'm using a filter with an SQL expression.

I want to make a condition that says "date_variable"> today()

But this doesn't work. 

date_variable is like this 9999-12-30T23:00:00.000Z

Can you please tell me what's wrong ? thank you!

9 Replies
Dataiker
Dataiker

Hi,

if your filter is in SQL expression mode, then all you need to type is the expression that applies to your database. Note that using a SQL filter is only possible on input datasets that are SQL datasets themselves, and that the range of admissible values for timestamps doesn't always extend to 9999-12-30T23:00:00.000Z (for example not on MySQL)

What kind of recipe are you using, on what kind of dataset, and what is the error you have or the incorrect behavior you're seeing?

Regards,

    Frederic

Level 2
Author

Hi @fchataigner2 , 

thank you for your reply.

I'm using a filter recipe( keep only rows that satisty an SQL expression) on a hive table.  the error is in the message below. 

kind regards,

 

0 Kudos
Dataiker
Dataiker

Hi,

since you're using Hive, https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions says you should be doing `date_variable ` > CURRENT_DATE

Regards,

    Frederic

Level 2
Author

Thanks Frederic ! 

0 Kudos
Dataiker
Dataiker

Hi @salima ,

Which error did you get ?

Today's date syntax depends on your SQL database. In mysql, it is CURDATE(), in PostGreSQL CURRENT_DATE.

Also, have you checked the execution engine is SQL (click on the 3 wheels, bottom left, below the run button) ?

 

Damien Jacquemart, Lead Data Scientist @Dataiku
Level 2
Author

Hello @DamienJ , thanks for your quick reply

actually my table isn't an SQL table. 

My recipe can only be run on 3 recipe engines( dss, hive or spark) sql is not available because the database isn't sql table. 

here is the error i got. 

Is there another way to do this even if the database isn't SQL table ? 

thank you

0 Kudos
Level 1

Make sure your sql query is compatible with the engine you are using. For example if you are using "dateadd" function and you are running your recipe on spark engine then you might get an error as the correct sql function for spark is "date_add".

0 Kudos
Level 2
Author

@akgaab  thank you

Community Manager
Community Manager

This sparked quite the conversation. Great job everyone!