Skip to main content

New and Open ideas are awaiting review or have been reviewed by our Product Management team, and are open for commenting and voting.

Filter by idea status

Filter by product area

1111 Ideas

vlroyrenn
Enthusiast
vlroyrennEnthusiast

FeatureWriter: Option to write to temporary file without giving an explicit pathOpen

There are a number of operations that are made much easier and/or more efficient by simply having data be passed around a file on disk instead of as FME features. When dealing with large amounts of features going into a PythonCaller, for instance, it can be preferable to dump them all into a Parquet or Arrow file and decode that file with Pandas or some other dataframe library, rather than extracting attribute data feature by feature in Python. Some other use-cases, like Emailer, also often make use of temporary files.The issue is that having FeatureWriter recieve both a path from TempPathnameCreator and the features it needs to actually write doesn’t work, that path needs to be joined on each feature in any of various clumsy methods. Given most uses of TempPathnameCreator are intended to feed into FeatureWriter, I would suggest that “Temporary local file” be a valid dataset option for FeatureWriter, which would simply generate a temporary path in FME_TEMP, write all features to it and allow the file name to be retrieved as the _dataset attribute under the Summary tag, as is already the case. It would otherwise work in exactly the same way as the combination of TempPathnameCreator and FeatureWriter, but simpler to setup.Any scenario where it is more practical to pass features between transformers as single files rather than large collections of features might benefit from this. 

vlroyrenn
Enthusiast
vlroyrennEnthusiast

Allow database writers to bulk-write to temporary/private tables before inserting/copying to final locationOpen

I would like to see FeatureWriter or another transformer updated to allow data being written to a temporary table and an SQL statement (or several) to be run afterwards to manipulate that data, most likely to move it to its permanent location and/or create partitions for the range to be inserted.Related to a community question I opened last year, and to a similar idea I submitted for temporary files.One method I commonly use (at least in Postgres) to efficiently write large amounts of data to large tables is to create a temporary table that’s identical to my target table (minus indexes and constrainsts), use some bulk loading method like COPY to fill that table with the data I’m inserting, and then running whatever statement I need to insert/upsert the final table with that data. This allows the DBMS to use whatever method works best for filling that data and does the actual data insertion as a single transaction. For FME specifically, this allows rows to be upserted without opting out of bulk-mode insertion.As you can see from my community question, this turns out to be very complicated with FME because transactions/sessions aren’t scoped across transformer nodes in a way that makes it possible to use temporary tables, so you need to:Create a schema for fake temporary tables Figure out some way to make a table name that is unique per run, which works reliably for both FME Form and FME Flow and fits within the table name length limits CREATE UNLOGGED TABLE “fme_fake_temp”.temp_table_name LIKE source_table_name EXCLUDING CONSTRAINTS EXCLUDING INDEXES In a subsequent SQLExecutor, run your insert statement and drop the tableDropping the table is often unreliable, insertion errors will often cause the temporary table to remain there and slowly inflate the fake temporary schema, requiring preriodical cleanup. Creating a unique table name is also inconveinient; $(FME_UUID) (which is what TempPathnameCreator uses) would work reasonnably well, since it’s an underscore-separated UUID prefixed with the transformer name, but it doesn’t work with FeatureWriter and requires transformer names to be valid table identifiers and shorter than 25 characters long to fit under Postgres’ 63 character limit. Besides that, FME offers no identifier that is unique per run and available on both Form and Flow.This is a lot of gymnastics for what I believe is a fairly common data insertion pattern, and FME would benefit from supporting it “natively”, so to speak, possibly using actual temporary tables. Other DBMS support similar private/temporary tables, like Oracle’s Private Temporary Tables, MySQL’s temporary tables, MS-SQL’s #TemporaryTables, or the (somewhat more convoluted) SQLite approach of mounting a separate database (possibly one that only exists in :memory:) as a schema, and creating temporary tables there. For FeatureWriter proper, however, these mechanisms wouldn’t work, so having FME track temporary “real” tables and drop them at the end of the flow run would likely be the least disruptive option. Using the actual mechanisms for session-local tables would probably require something more akin to InlineQuerier.