You can translate the question and the replies:

Create empty file if query does not return result

Is there some way to distinguish when we have an input empty file in the source or the file is missing? (sftp protocol with "flat file" data source) From our POV, the behaviour should be different: We are expecting to receive a delta file with "yesterday" changes, and if no changes were made (the file is empty), we will generate an empty file. But if the input file is missing, we do not want to generate the file, it is an upstream failure and we do not want to send a wrong snapshot to our downstreams.
user
07-01-2020 05:04:07 -0500

5 Answers

Hi, In order to throw an error when the file is not found, you need to modify the data source in the Data route configuration tab so that the entire path to the file is located in the “File path” field. This includes the file name and extension; leave the “File name pattern” field blank. If you need to specify different file names you can add an interpolation variable to the file path. Before execution on the base view, Virtual DataPort will check the location of the file and will throw an error if the file cannot be found. Denodo also provides a [Distributed File System Custom Wrapper](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20FileSystem%20CustomWrapper%20-%20User%20Manual) which supports FTP and is useful for getting a list of files on the system. More information about [Delimited File Data Sources](https://community.denodo.com/docs/html/browse/latest/vdp/administration/creating_data_sources_and_base_views/delimited_file_sources/delimited_file_sources) and [Interpolation Variables](https://community.denodo.com/docs/html/browse/latest/vdp/vql/advanced_characteristics/execution_context_of_a_query_and_interpolation_strings/execution_context_of_a_query_and_interpolation_strings) can be found in the attached links. Hope this helps!
Denodo Team
07-01-2020 11:11:39 -0500
This workaround was necessary because the lack of some features Denodo should have... Scheduler - Dependencies amongst jobs could have some "output status variable condition" available to be used by the "input job", not only succeed / fail status. If we only have 2 status, we should able to control the output status (fail / succeed) and not only leave the work to the query functioning / not functioning. **Question: **is there some way to make the query fail during the execution in a case statement? That way we could avoid doing custom code. p.e.? : select case when campo=1 then "ok" case when campo=2 then <<query fail>> from table
user
08-01-2020 02:33:14 -0500
Hi, In regards to distinguishing empty versus missing input files, I think that this similar [Q&A question](https://community.denodo.com/answers/question/details?questionId=9060g000000TQu7AAG&title=Fail+query+if+filenames+do+not+exist+in+path) may have the answer that you are looking for. A different result for each case can be created using the [Distributed File System Custom Wrapper](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20FileSystem%20CustomWrapper%20-%20User%20Manual) as described in the Q&A question. I was not able to find a way to make a query fail in a case statement. I would suggest creating a custom Stored Procedure that throws an exception in this situation. More information about [Developing Stored Procedures](https://community.denodo.com/docs/html/browse/latest/vdp/developer/developing_extensions/developing_stored_procedures/developing_stored_procedures) can be found in the attached link. I hope this helps!
Denodo Team
15-01-2020 08:56:07 -0500
The answer is not solving my issue. The issue is not in the VDP. The issue is in the scheduler. We want to run the job or not depending of some condition. This is not feasible in Denodo, isn't it? We will need to create a stored procedure which will make the job fail depending of a due condition, right? The desired behaviour would be: keep waiting because we did not found the file. This is a common scenario in any ETL processing.
user
16-01-2020 04:35:26 -0500
Hi, I’ve tried an approach using the Denodo Scheduler’s built-in functionality to retry the job if no tuples are extracted. I’m sharing with you the steps I tried: 1. Create an exporter in the [exporter section](https://community.denodo.com/docs/html/browse/latest/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/postprocessing_section_exporters). Make sure that the “Create file even if no tuples are exported” option is not selected. 2. In the [retries section](https://community.denodo.com/docs/html/browse/latest/scheduler/administration/creating_and_scheduling_jobs/configuring_new_jobs/retry_section) select “Enable retries”. Under “What to retry”, select the “All queries if exported documents are less than:” option and type 1 so that the scheduler will retry if no documents are exported. Using this method, I was able to create a job that repeated when no file was present and stopped when a file was added to the target folder. I created a base view using the [FileSystem Custom Wrapper](https://community.denodo.com/docs/html/document/denodoconnects/7.0/Denodo%20FileSystem%20CustomWrapper%20-%20User%20Manual) to check for files; this can be joined to the Delimited File base view to include the data. The job will query the derived view and retry when no files are present as there are no output tuples in the exporter. The job will not retry with a blank file as the file names are output in the exporter (even though there is no data output). I hope this helps!
Denodo Team
21-01-2020 08:10:07 -0500
You must sign in to add an answer. If you do not have an account, you can register here