2.18 roadmap and goals

[bamaer]

We're entering the code freeze for the 2.17 release, so we'll need to start thinking about our focus for the 2.18 release.

One of the areas we can work on is improving the consistency and in Apache Hop.

• Incoming/outgoing transforms: just like in [Bug:] "Merge join" transform: input transforms are not updated #4877, there are other transforms that have incoming or outgoing transforms that are not (always) updated correctly
• Transform dialog harmonization: the Database Lookup transform dialog has been updated for 2.17, but there are quite a lot of other lookup (and other) transforms that have the same or similar UI problems. We could walk through all of the transform and action dialogs that pack too many UI elements and try to simplify them like we did for the Database Lookup dialog.
• File inputs: a lot of our text based input transforms have a similar dialog to pick up files. Similar does not mean identical, and these little differences can cause confusing. Ideally, we'd move the file inputs to one or a couple of widgets and/or annotations that we can then use from the various transforms.
• Metadata editors: some have an icon in the top right corner, some don't. Some have the metadata item on a separate line at the top of the editor, some don't, etc. We could walk through all of the editors and make sure their layout is consistent.
• Code consistency: there are plenty of areas where we repeat the same or similar code blocks. Sometimes that's acceptable, but often the code would be more maintainable by moving to a single implementation in e.g. helper classes and methods that we can call from the current implementations.

The UI topics here could be a good opportunity to start working on (and enforcing) UI guidelines, so developers will know what is expected when they build new UI elements.

My 2 cents, let me know what you think our focus should be for 2.18.

[nadment]

There are three areas where I would like to see improvements:

Execution monitoring and logging.
A view in Hop GUI with the execution history of a single pipeline or workflow (as in Kettle) to see dates, statuses, and quickly compare the duration of recent executions. Today, it is not very practical, from an “execution information” perspective, to find the last five executions, for example.

• For now, I store the information in a DB with Pipeline/Workflow Log, but I have to go look in the DB.
• My scheduler also helps me.
• OpenTelemetry is probably also an interesting solution.

Improve Git integration.
Add a perspective for commits to make it more convenient to use.

The database explorer dialog box.
We should give it some love, because we use it every day and it's not very sexy.

I already have work on the last two points that I've never been able to finish due to lack of time.

[fpapon]

Big +1 to use OpenTelemetry as it's become the new standard in the industry.
I can help with the integration.

[zoomingrocket]

Some more suggestions based on our current use

1. Standardize SFTP support in FTP Delete to support Public Key Authentication.
2. Move file shape for SFTP / FTP
3. Insert/Update Transform - Support Bulk Pattern by offloading the insert vs update to the database instead of external lookup row by row, slowing down the entire transform. Current workaround is to spool a local lookup cache, but that becomes a burden when we deal with 1B+ rows
4. Produce base metrics for each pipeline run via hoprun as an output to keep track of total rows processed.

[zoomingrocket]

2. Move file shape for SFTP/FTP -> This translates into "RENAME" command for SFTP. Primarily, a lot of banking SFTP endpoints do not allow the RM command. So after processing/downloading the file, they expect the processors to move the file to a different directory on SFTP. With Hop, we can only delete while getting a file from SFTP and manually reupload (put) to a different directory with the current shapes. This approach works where the vendor does not restrict the RM SFTP command. However, if RM is restricted and only "RENAME" (which moves and/or renames files, similar to the shell "mv") is allowed, it would be beneficial to have a shape within Hop. To workaround this, we are doing an external Shell script SFTP and firing off RENAME within Hop.

3. We are doing that today by pre-determining which are insert vs update by grabbing the key column data and spooling them off at the start of the pipeline. This is ok for small to medium-sized tables, but once we hit larger tables having 1B+ rows, the spooling of keys itself takes a lot of time than the actual rows to be processed. Hence, the suggestion was more towards implementing MERGE (Oracle), ON CONFLICT (Postgres), ON DUPLICATE (MySQL), etc. Almost all modern databases support insert v/s update within a statement, so we save significant network round-trips and let the database manage upsert for us.

4. I was wondering if we can print out/redirect pipeline metrics in the stdout of a hoprun.sh execution. I see pipeline log allows redirect to a database, but in our case, we are using hop-run with Airflow, and then pushing all the std output into an OpenTelemetry-based collector for distributed tracing & overall observability. So we are missing pipeline metrics, which is a vital bit of info for stakeholder KPIs, Metrics for day-to-day operations, reports, etc.

[hansva]

3. And there lies the main problem, there is no real standard for this (except MERGE, which is part of the SQL standard). We could add the framework to allow this and implement it for 1 maybe 2 databases that we use in our testing framework. But going over our 40+ database types would take a considerable amount of time.

4. The pipeline and workflow log would be the correct way to do this; you could even shape your metrics to a structured format and print them to the logs.

[dave-csc]

@zoomingrocket You can actually address point 2 with the existing Process files transform or the Copy files / Move files actions. Just specify as source/destination paths strings like sftp://user:password@path/file.ext.

You'll probably don't want to set your password in clear text, but you can mitigate this by using a variable resolver (see this discussion for details, beware that versions below 2.16 have a flaw in decrypting the passwords 😅)

[tlue]

Point 3: I guess Hans is right. Recent Postgres an MSSQL I.e. offer an optional "matched by target" clause in their MERGE implementations (i.e. in order to delete those not coming in source any more), which Oracle does not (yet). Not sure about MySql and MariaDB and DB2 (-variants i.e. DB2/400 I would doubt) as well as Terradata.

[zoomingrocket]

@dave-csc - Thanks for the tip, I will give it a try to see if this works out when SFTP login is via public key
@hansva - Yes, it will be helpful if we support MERGE.

[dmainou]

I added this ticket to add a DB widget to the ui, which I think would be relatively trivial to add as a new feature.
Combined with Bart's Terminal widget would mean less need to leave the tool to complete tasks.
#6464

[dave-csc]

This is not UI related, but I would propose a revision of the main configuration file, hop-config.json, based on what can be stored in a Git repository (such as projects and environments definitions, and their relations), and what it's not recommended to do so (such as account info and credentials)

[bamaer]

There are a couple of open tickets on projects and environments. We could consider creating a separate discussion to list them and to provide a general approach on how we'll deal with projects and environments now and in the future.

[zoomingrocket]

@bamaer - Another hop-config.json feedback, not sure if feasible. Any way to make the lifecycleEnvironments.configurationFiles array to simply point to a directory under which all *.json will be loaded at Hop startup or introduce a new element that does this? We realized that everytime Developers add new, we have to keep track of adding these on the server apart from local machine development.

[bamaer]

this sounds similar (not identical) to #4193, isn't it?

[zoomingrocket]

@bamaer - #4193 - This we are currently solving by doing a set variables pointing to a local props file specific to the Workflow and then setting variables with value as ${prop_######}

[sramazzina]

I can merge my enhanced version of the parquet plugins (that now are available as separated plugins) in the main branch. I already applied all the PRs applied to the standard parquet plugin (logical types plus all other PRs) and moreover they are already in use in production (so basically they works..).

There is only a minor point of backward incompatibility in the input plugin but I think we can easily find a solution for that.

[ossDataEngineer]

Hello Hop Community,

I would like to suggest few new features/enhancements

1. "Delete" transform should support batch option for improving database table delete performance. Addressed in discussions 'Delete' missing batch option - so deletes are slow #6189
2. Salesforce Connections OAuth metadata should support customizing API version
3. Json Input transform should be robust to be able to flatten complex nested jsons into tabular structures for following scenarios
   a. uneven schema (missing json keys, or fields with empty data like address:”” or null values like address: null)
   b. multiple array-based elements in same json

Thanks!

[dave-csc]

Some considerations about point 3:

1. in Hop 2.17 the option "Default path leaf to null" is present. It means that if you specify a non-existing path in the JSON, the transform will output NULL for the corresponding field (I still need to test this option, anyway 😅)
2. processing multiple JSON arrays can be a bit problematic in terms of having a "tabular" structure like Hop streams. You can easily address a single array by producing a "denormalized" stream (i.e. a stream with a row for each array element, and the values outside the array repeated in all the rows). But what would you do if multiple arrays are at the same level in the JSON structure?

[zoomingrocket]

Enhance Serialize & Deserialize from File to accept filename as a field instead of just variables so that it can be combined with other shapes like Get File Names

[ossDataEngineer]

Simple Mapping Pipelines logs are not affected by log-level set at parent workflow/pipeline. Simple mapping pipelines always create logs regardless of log-level. Fixing this will help reduce hop log size

[hansva]

These are specific tasks and not high-level goals, for these types, I suggest creating a ticket if it does not exist yet.

[ossDataEngineer]

Today transforms like database, rest api fail when there is intermittent network connectivity loss. Could the transforms support internal retry mechanism after certain interval to try again?

[dave-csc]

An auto-retry mechanism would still need some limitations: if the database/rest service host was unreachable because it's actually "dead", would you mind being stuck in an infinite loop, waiting for a response that will never arrive?

[bamaer]

@leehaut added retry to the REST client in 2.17.0. I agree this could be very useful, but I also agree with @dave-csc that we'd need to plan and design this very carefully, with a max number of retries, delay between retries, options to retry on workflow, action, pipeline, transform levels without conflicts between these levels etc. This very quickly becomes another can of worms...

[liosc]

In case of automatic retries there should be some configuration to choose how many attempts and how wait before the next attempt. Both parameters should be low enough to prevent waiting too much and great enough to let the automatic attempts be significant (based on the context).

[mattcasters]

IMO either retry is supported by the JDBC driver or underlying database, or we shouldn't implement it.
It's better to design our data structures properly to allow for re-running a pipeline or workflow.