Filter using an sql expression

Highlighted
salima
Level 1
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
Highlighted
DamienJ Dataiker
Dataiker
Re: Filter using an sql expression

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
Highlighted
fchataigner2 Dataiker
Dataiker
Re: Filter using an sql expression

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

Highlighted
salima
Level 1
Author
Re: Filter using an sql expression

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
Highlighted
salima
Level 1
Author
Re: Filter using an sql expression

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
Highlighted
fchataigner2 Dataiker
Dataiker
Re: Filter using an sql expression

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

Highlighted
salima
Level 1
Author
Re: Filter using an sql expression

Thanks Frederic ! 

0 Kudos
Highlighted
akgaab
Level 1
Re: Filter using an sql expression

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
Highlighted
Community Manager
Community Manager
Re: Filter using an sql expression

This sparked quite the conversation. Great job everyone!

Highlighted
salima
Level 1
Author
Re: Filter using an sql expression

@akgaab  thank you