[HOP-3913] SQL file output does not write all rows

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 1.2.0
• Fix Version/s: Not applicable
• Component/s: Transforms
• Assignee: Hans Van Akelyen
• Reporter: Jason M
• Created: 21/Apr/22 13:28
• Updated: 22/Apr/22 13:28
• Resolved: 22/Apr/22 13:28

Description

The 'SQL file output' transform doesn't write all rows. It looks like the output is buffered and not flushed before the file is closed given that it stops mid-line. 

You can replicate it simply by generating 200 rows with a single field and having the SQL file output 'Start a new line for each statement'.

Here are the logs from a sample run where the step is reporting it wrote 200 rows. 

2022/04/21 09:43:56 - Hop - Pipeline opened.
2022/04/21 09:43:56 - Hop - Launching pipeline [test-sql-file-output]...
2022/04/21 09:43:56 - Hop - Started the pipeline execution.
2022/04/21 09:43:56 - test-sql-file-output - nr of transforms to run : 2  , nr of hops : 1
2022/04/21 09:43:56 - test-sql-file-output - Executing this pipeline using the Local Pipeline Engine with run configuration 'local'
2022/04/21 09:43:56 - test-sql-file-output - Not running a unit test...
2022/04/21 09:43:56 - test-sql-file-output - Execution started for pipeline [test-sql-file-output]
2022/04/21 09:43:56 - test-sql-file-output - I found 2 different transforms to launch.
2022/04/21 09:43:56 - test-sql-file-output - Allocating rowsets...
2022/04/21 09:43:56 - test-sql-file-output -  Allocating rowsets for transform 0 --> Generate rows
2022/04/21 09:43:56 - test-sql-file-output -   prevcopies = 1, nextcopies=1
2022/04/21 09:43:56 - test-sql-file-output - Pipeline allocated new rowset [Generate rows.0 - SQL file output.0]
2022/04/21 09:43:56 - test-sql-file-output -  Allocated 1 rowsets for transform 0 --> Generate rows
2022/04/21 09:43:56 - test-sql-file-output -  Allocating rowsets for transform 1 --> SQL file output
2022/04/21 09:43:56 - test-sql-file-output -  Allocated 1 rowsets for transform 1 --> SQL file output
2022/04/21 09:43:56 - test-sql-file-output - Allocating Transforms & TransformData...
2022/04/21 09:43:56 - test-sql-file-output -  Pipeline is about to allocate transform [Generate rows] of type [RowGenerator]
2022/04/21 09:43:56 - test-sql-file-output -   Transform has nrcopies=1
2022/04/21 09:43:56 - Generate rows.0 - Starting allocation of buffers & new threads...
2022/04/21 09:43:56 - Generate rows.0 - Transform info: nrinput=0 nroutput=1
2022/04/21 09:43:56 - Generate rows.0 - output rel. is 1:1
2022/04/21 09:43:56 - Generate rows.0 - Found output rowset [Generate rows.0 - SQL file output.0]
2022/04/21 09:43:56 - Generate rows.0 - Finished dispatching
2022/04/21 09:43:56 - test-sql-file-output -  Pipeline has allocated a new transform: [Generate rows].0
2022/04/21 09:43:56 - test-sql-file-output -  Pipeline is about to allocate transform [SQL file output] of type [SQLFileOutput]
2022/04/21 09:43:56 - test-sql-file-output -   Transform has nrcopies=1
2022/04/21 09:43:56 - SQL file output.0 - Starting allocation of buffers & new threads...
2022/04/21 09:43:56 - SQL file output.0 - Transform info: nrinput=1 nroutput=0
2022/04/21 09:43:56 - SQL file output.0 - Got previous transform from [SQL file output] #0 --> Generate rows
2022/04/21 09:43:56 - SQL file output.0 - input rel is 1:1
2022/04/21 09:43:56 - SQL file output.0 - Found input rowset [Generate rows.0 - SQL file output.0]
2022/04/21 09:43:56 - SQL file output.0 - Finished dispatching
2022/04/21 09:43:56 - test-sql-file-output -  Pipeline has allocated a new transform: [SQL file output].0
2022/04/21 09:43:56 - test-sql-file-output - Initialising 2 transforms...
2022/04/21 09:43:56 - dev - New database connection defined
2022/04/21 09:43:56 - SQL file output.0 - Connected to database [dev]
2022/04/21 09:43:56 - SQL file output.0 - Opening output stream in nocompress mode
2022/04/21 09:43:56 - SQL file output.0 - Opening output stream in default encoding
2022/04/21 09:43:56 - SQL file output.0 - Opening output stream in default encoding
2022/04/21 09:43:56 - SQL file output.0 - Opened new file with name [/home/jmarchioni/temp/test.sql]
2022/04/21 09:43:56 - test-sql-file-output - Transform [Generate rows.0] initialized flawlessly.
2022/04/21 09:43:56 - test-sql-file-output - Transform [SQL file output.0] initialized flawlessly.
2022/04/21 09:43:56 - test-sql-file-output - Data Set enabled? false
2022/04/21 09:43:56 - Generate rows.0 - Starting to run...
2022/04/21 09:43:56 - Generate rows.0 - Signaling 'output done' to 1 output rowsets.
2022/04/21 09:43:56 - Generate rows.0 - Finished processing (I=0, O=0, R=0, W=200, U=0, E=0)
2022/04/21 09:43:56 - test-sql-file-output - Pipeline has allocated 2 threads and 1 rowsets.
2022/04/21 09:43:56 - SQL file output.0 - Starting to run...
2022/04/21 09:43:56 - SQL file output.0 - Finished processing (I=0, O=200, R=200, W=200, U=0, E=0)
2022/04/21 09:43:56 - test-sql-file-output - Pipeline duration : 0.306 seconds [  0.306" ]
2022/04/21 09:43:56 - test-sql-file-output - Execution finished on a local pipeline engine with run configuration 'local'

Here is the resulting file with line numbers. 

 

Issue Links

• duplicates: HOP-3847 SQLFileOutput does not close file