Drive Your ETL With Map Interaction And Web Tools
There are many ETL jobs you might want to trigger from your map and the patten we'll explore in this blog will help you get there.
Here is one use case, running an ETL web tool that accepts area features from a map as an input. The web tool happens to build a mobile geodatabase which is returned to my Pro session or Experience Builder analysis widget as a local file, but you might ETL data from anywhere to anywhere, in any format. The data doesn't have to be in your map, or even in your ArcGIS Pro project.
The blog download has my sample toolbox, so let's walk through the steps. There is a model tool and an ETL tool in the blog download's toolbox, the model wraps the ETL tool. I used ArcGIS Pro 3.6 and of course ArcGIS Data Interoperability for Pro 3.6.
Things start with a FeatureSet input parameter, in my case with polygon geometry intended to be the area(s) of interest containing any number of US cities. At run time this lets you pick from a map layer, browse for data or create your own features from scratch. The FeatureSet is then written as EsriJSON data to the path %scratchFolder%\esri.json. If you're not familiar with ModelBuilder inline variable substitution then read about it here. When the tool runs locally this path will be in the project scratch folder, and when it's a web tool, in the job scratch folder.
Now for a little time travel. Imagine you're building the above model and all you have is the FeatureSet input and the Features to JSON tool in the model. Save and run it and you'll have a file esri.json in your scratch folder. Now build your ETL tool (embedded, in the same toolbox as the model) that does what you want given the EsriJSON input. Here is mine.
Like the annotation says, an EsriJSON reader (NB: with single path input property) brings in my map input, the features get aggregated (I want one feature, multiparts are OK) then projected however I need in my downstream processing. In my case the resulting feature is used as an initiator and spatial filter in a FeatureReader, which extracts some data from Living Atlas and writes it to a mobile geodatabase with dynamic schema. There is a trick though in the pathing of the data.
Here I am editing user parameters in the ETL tool and the top one is a scripted parameter SCRATCHFOLDER that gets what ArcPy is using, either locally in Pro or in a job folder in a web tool. Scripted parameters are evaluated before any data processing happens.
The next parameter is the source EsriJSON file path, which will be supplied by the model but to make sure I set the default path to use the SCRATCHFOLDER scripted parameter. Note that you have to save the parameter state after adding SCRATCHFOLDER and reopen the dialog to use the value in other parameters.
Now the trick, the output mobile geodatabase is written with overwrite permission to the scratch folder.
Now back to the model. The ETL tool is added and the EsriJSON file connected as its input. The ETL tool output is written to %scratchFolder%\Extract.geodatabase.
The last step is to add a Calculate Value model tool that copies the output path to a new File type parameter. This is necessary because the output has to be Derived for a web tool to return data. Make sure the ETL tool output isn't set to intermediate in ModelBuilder or you'll get no output! Check out the Calculate Value expression, it is necessary to protect the path from interpretation as having escape characters by making sure its a raw string.
After some local test runs I had a result I could share as a web tool and then run. When sharing the web tool I set the message level to Info so I can watch the action. I used ArcGIS Enterprise 12.0, which is compatible with ArcGIS Pro 3.6. Make sure Data Interoperability is installed on your server and any packages and credentials installed on the server by the arcgis service owner user. It is good practice to open fmeworkbench.exe as the arcgis service owner user (in the Data Interoperability install) on the server and test readers and writers you will be using.
In the message stream above you can see the output written to a job scratch folder and it is also downloaded locally to my Pro session:
I don't have any file associations for .geodatabase files but I can open the link in Data Inspector:
Now I have an ETL web tool I can share with my colleagues! Speaking of colleagues I am indebted to @SashaLockamy for her help putting this together.
For clarity, the steps to building my web tool were:
- Make a model with a FeatureSet input parameter that writes EsriJSON to %scratchFolder%\esri.json
- Save and run the model to create the esri.json file
- Make an embedded ETL tool in the model's toolbox that reads EsriJSON and does your ETL
- If returning data it must be a file data type, and...
- Script the first parameter to return arcpy.env.scratchFolder
- Write the desired file output data to that folder
- Test the ETL tool using your esri.json file
- Add the ETL tool to the model
- Connect the EsriJSON output as the ETL tool input
- Add a Calculate Value tool to cast the ETL tool output to a File, Derived value
- Make sure the ETL tool output is not intermediate
- Save and run the model from the toolbox, not the model diagram
- Share the result as a web tool (or geoprocessing service if using a standalone server)
Please do comment in the post with your experiences.
