Latest Contributions by BruceHarold

This post is a stepping stone between the ETL Patterns community space and Esri's Well-Architected Framework site, where theory becomes practical advice on how to implement the ArcGIS system.  Specifically, this document relates to the integration pillar Data Pipelines and ETLs topic. If you browse the topic you'll see many ETL patterns described, but as the architecture site isn't designed as a software repository, the patterns link back to this community for samples you can explore and implement. To build the content, the document attached to this post, and shown below, was constructed.  It is a tabular summary of ETL modalities across ArcGIS.  You will likely have your own experience to draw upon and disagree with the software suggestions, if so please comment in this post!  We hope to keep this "framework" under construction with the involvement of everyone. Inbound ETL Patterns ... View more

Often, ETL is not one-off, recurrence is needed to incorporate data change over time.  Until the ArcGIS Pro 3.4 and ArcGIS Enterprise 11.4 releases, the supported automation patterns for this included ArcGIS Data Pipelines and scheduled Notebooks or tools using the REST API task scheduler, but a new no-code option, web tool scheduling, is delivered in ArcGIS Pro 3.4 and ArcGIS Enterprise 11.4! You can use any type of geoprocessing tool to create your scheduled web tool - core system tool, ModelBuilder tool, Python script tool or Spatial ETL tool.  For my blog subject matter I'm using an ArcGIS Data Interoperability Spatial ETL tool, because it can consume my source data, an RSS feed, specifically a Common Alerting Protocol (CAP) feed, as published by many agencies worldwide, including FEMA in the USA.  My CAP data is weather alerts in New Zealand, refreshed twice daily.  The feed will have no entries if the weather is good 😉.  CAP is XML-based, easily handled by ArcGIS Data Interoperability.  I want to mirror the CAP feed status to a hosted feature layer in ArcGIS Online. Below is a sample alert status map in ArcGIS Pro, for October 9th 2024.  The blog download has a couple of CAP alert XML documents, if you want to see some raw data. CAP Weather Alerts As the labeling suggests, the yellow feature is a high wind watch, the blue lines (zoomed in, orange polygons) are snow warnings for roads through some mountain passes.  If we zoom in to the northernmost feature we can inspect it.  It is Lewis Pass, which has two geothermal spring resorts along the route, so if you do get delayed by snow you can wait it out in comfort! Snow alert through Lewis Pass A few days later, there is a heavy rain watch: Fiordland rain watch For the area, rain only comes in one type - heavy - so it's no surprise the prediction of an alert upgrade from watch to warning (orange) became true at the next update in 12 hours, plus some new alerts arrived: West Coast rain And the next day - yet more weather! Yet more weather! Regular updates like this are a classic case for a scheduled web tool, in fact that's how the data was refreshed for me overnight.  What does that ETL look like? My data flow maintains a hosted feature layer in ArcGIS Online from the current CAP status.  My ETL tool is quite simple, here it is (also in the blog download, requires ArcGIS Data Interoperability for Pro 3.4, and ArcGIS Enterprise 11.4 if shared as a web tool). CAP alert ETL tool First a token is generated (using an EsriOnlineTokengetter, for a local portal you would use an EsriPortalTokenGetter), then the upper stream reads the RSS feed and writes an upsert transaction to the target feature layer - any new alerts become new features and any data changes to existing features are applied.  Upsert support requires the data have a uniquely indexed non-null field in the layer, as discussed in an earlier blog.  The lower stream tests for expired alerts and deletes them.  Note that the ETL tool has no parameters, because the input RSS feed and output feature layer do not change so can be set as not published at authoring time.  I'm showing a new, recommended ETL pattern here for maintenance of hosted feature layers, namely generating a portal token within the ETL tool rather than sharing web connections to the hosting server, which is an awkward step we can avoid.  The target feature service is read and written using the Esri ArcGIS Server Feature Service format and a supplied token, with the option to verify SSL certificates unchecked.  If your security requirements require it, you'll need to supply a trusted certificate. Having run my ETL tool locally in ArcGIS Pro I can share the history result as a web tool and schedule it.  There is some mental arithmetic to do when scheduling.  The CAP feed is updated by 9AM and 9PM "local time" which at writing is NZDT, or UTC+13.  My Pro machine is currently on PDT, which is UTC-7, so to start my schedule at the next available 12-hourly slot, 9AM NZDT, I calculate this is 8PM UTC or 1PM PDT. Web tool scheduling In real life, meteorological offices allow for more frequent bulletins than my schedule above, but you get the idea.  At the link for the scheduled webtool you can pause, delete or edit the schedule. The blog download has the tool source fmw file, some sample CAP XML files (which I used when authoring the ETL tool to get the XML parsing right), and a lyrx file that shows how I used data-driven rendering of hex codes in the data.  Not shown - creating an initial file geodatabase feature class used to instantiate the target feature service, but you can easily do this from the supplied workspace.  Don't forget the alert identifier constraints needed for upsert support! So there you have it, configuring a scheduled web tool that will run indefinitely and maintain a hosted feature layer! Now there is nothing stopping you from automating your ETL!   ... View more

Tens of thousands of hosted feature layers are made daily in ArcGIS Online. Below is one I made - street address points in the city of  Los Angeles, sourced from the city's open data site. I didn't make the layer with ArcGIS Data Pipelines, you can tell by the tuned widths of the text fields in the Fields view. Los Angeles address points and schema But I can automatically maintain the layer (it has daily changes) with a simple pipeline.  Go ahead and click the link, the pipeline is shared to everyone - here's what you'll see: The Pipeline Crazy simple, isn't it!  The secret sauce is schema mapping.  Knowing the output schema is defined by the existing layer, and editing the Input schema appropriately to match the field types, the Map Fields tool lets me connect the incoming field names to the output field names with no further data preparation required. Here is how Map Fields looks in my case (sorry, not enough real estate to show the whole dialog): Field Map Having connected everything (ObjectID is not mapped) and set the output write method to Replace, I can set up a schedule for the pipeline to run after hours and I'm done! So, all you owners of hosted feature layers, if ArcGIS Data Pipelines can reach your source data, you have a lightweight option to maintain your data, regardless of how you created it. ... View more

If you're synchronizing an external data source into a hosted feature layer in ArcGIS Online or ArcGIS Enterprise, then writing each periodic changeset is something you want to be as efficient as possible.  Once your datasets get into the hundreds of thousands or millions of features, calculating the optimal insert, update and delete transactions becomes very attractive, minimizing downtime and transaction failure risk. The problem is, deriving edit transactions requires reading the target hosted layer, and this is an expensive operation.  This blog shows an approach that avoids querying your target feature layer in favor of using a file geodatabase export of the data, automatically downloaded locally, then read performantly to find the changeset, without impact in extra item storage. Here is the approach in action: BulkUpsert2Avoiding feature layer queryIn an earlier post I showed an example of deriving upsert and delete transactions by reading a target feature layer, which is a serialized approach. My target layer has over 1M features - reading it takes several minutes.  The above does better, against the same service. The post download has a toolbox with the Pro 3.5 ArcGIS Data Interoperability ETL tool in it - see BulkUpsert2.  The tool reads a CSV file at a URL (also avoiding serialization, which is the default for the server), but also accesses portal content to trigger a file geodatabase export, wait in a loop until the export is finished, then downloads and reads the target feature layer content, not the service, and finally deleting the export item (the downloaded file geodatabase is also automatically deleted).  This is core functionality.  There are two mint green custom transformers in the tool.  An EsriOnlineTokenGetter retrieves a token needed for a later HTTP call (if you are working with an Enterprise portal you would use an EsriPortalTokenGetter). Pro Tip!  The blog download now contains the tool BulkUpsert2WebConnection which avoids retrieving a token by using an OAuth2-based web connection.   The web connection is supported by the Esri ArcGIS Connector package, which supplies the Esri ArcGIS Feature Service format used in the workspace.  The web connection is then used by an HTTPCaller transformer, eliminating the need for a token .  If your organization enforces multi-factor authentication then retrieving a token is blocked and the custom transformers will not work.  It is now recommended practice to install the package, create a new web connection and use it for feature service read/write and ArcGIS REST API http calls. The other custom transformer is a looping transformer you can inspect in its tab labeled FeatureServiceExportLooper.  It checks the export job status every 2 seconds until the job is complete.  Note that how long an export job takes is dependent on how big your service is and also how busy the host portal is - I have seen ArcGIS Online queue jobs as well as run them immediately.  Here is a run taking a little over a minute (just for the export) at a busy time in ArcGIS Online: FeatureServiceExportLooperFeatureServiceExportLooperThe net result however is a significant net gain.  Here is a screen capture from the earlier blog and the serialized approach - note the session duration. Reading the target feature layer takes longerReading the target feature layer takes longerSo that's how to avoid reading hosted feature layers with a serialized approach.  Now you have an option to time consuming changeset construction! ... View more