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!
Answers
-
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) ?
-
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
-
Hello @DamienJ
, thanks for your quick replyactually 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
-
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,
-
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
-
Thanks Frederic !
-
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".
-
CoreyS Dataiker Alumni, Dataiku DSS Core Designer, Dataiku DSS Core Concepts, Registered Posts: 1,150 ✭✭✭✭✭✭✭✭✭This sparked quite the conversation. Great job everyone!
-
@akgaab
thank you
