How do you use SQL Script in recipe as application
I have a flow where I use SQL scripts to create datasets (see image).
I follow the instructions as described here: https://knowledge.dataiku.com/latest/courses/o16n/dataiku-applications/create-app-as-recipe.html
The issue is that when the flow is converted to a recipe as an application, while the log reports that the names of the input are being replaced (see ReplaceNames.png), it still crashes in the application because it looks for the 'original' dataset name from the recipe.
Therefore, I get the error:
Invalid object name "<Original_Tablename>"
Any idea on how to make this work? I also tried using SQL recipe, but I get the same error.
Answers
-
Hi
the app-as-recipe instantiation indeed won't touch the code of the SQL recipes, so it's under the responsibility of the app to alter the recipe or to setup variables in the project that can be used to point to the table (for example with Execute python code step at the beginning of the scenario of the app-as-recipe). The easiest solution is to start the flow by a Sync recipe, making a copy of the input dataset, and using it as input of the SQL recipe(s): the Sync recipe automatically uses the right table name, and the copied table has a "local" name that only depends on the project's key.
Note that you'll need to have the ${projectKey} in the SQL recipes' code, otherwise the execution will happen on the tables of the project where the app-as-recipe is defined, preventing concurrent usage: if the recipe code says `select ... from EVENTID_some_dataset_name ...` then even in the instantiated app-as-recipe, the table read will be EVENTID_some_dataset_name, not the table of the app-as-recipe instance.
