New to Dataiku DSS? Try out our NEW Quick Start Programs today and get onboarded on the product in just one hour! Let's go

Filter using an sql expression

salima
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
DamienJ
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
salima
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
akgaab
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
CoreyS
Community Manager
Community Manager

This sparked quite the conversation. Great job everyone!

Looking for more resources to help you use DSS effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
salima
Level 2
Author

@akgaab  thank you

fchataigner2
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

salima
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
fchataigner2
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

salima
Level 2
Author

Thanks Frederic ! 

0 Kudos
A banner prompting to get Dataiku DSS