Latest Contributions by BruceHarold

This post shows some advanced ETL techniques but additionally shows how you can hand off finalizing your data to a geodatabase view (actually hundreds of them in this sample), letting the database do the heavy lifting, and in a File GDB at that.  That's right - File Geodatabase views are a new feature at ArcGIS Pro 2.6, due out mid 2020, this is your preview!  No longer are you confined to the 'where' clause in leveraging SQL when working with FileGDB!   I'm going big with the data behind the post - the USDA National Agricultural Statistics Service (NASS) crops database.  I was thinking of calling the post something like 'You, Big Data and Asparagus' but that would lose a lot of people right at the title, even if they do like big data or asparagus.  NASS is a big program, and I don't pretend to know all it offers, but for my demonstration purposes I'll use crop statistics per county.  If you surf the NASS site you'll find ways to access data including selecting areas of interest using a map interface or as compressed text for data focusing on specific topics.  I want it all, in bulk.  NASS supports my need at this FTP site.  The file names change daily, but look for the file beginning 'qs.crops'.  At time of writing it contains over 19 million statistics for over 180 crop types, with records dating from the early 20th century.  So, while the record count might not impress you, I'm going to call NASS 'Big Data' as it has a daily update velocity.   We're going to automate putting this data into File Geodatabase so it can be mapped and analyzed, and doing so at any frequency including the data's native daily lifecycle.   Importing the data to File GDB is done by the ETL tool RefreshCrops (in the post download).  Here is how it looks after a successful run (click to enlarge), we'll walk through the underlying processing next.  (I'm anticipating the reader is able to open the ETL tool, has some Workbench app experience, and will follow along; this requires Data Interoperability extension, or FME, both at release 2020 or later.)       The first issue with the data is that the file of interest changes name daily, so while you could read it with an FTPCaller transformer it would be an error-prone user experience to enter the correct URL for each run, so the process is automated with a Python scripted parameter.  If you have never heard of scripted parameters before, they are a way to make user parameters work dynamically.  We're breaking the no-code paradigm here, but we have a good reason.  Here is how the parameter looks in the property editor:     The code opens the FTP site, reads the available file names, then downloads the 'qs.crops' file to a file named DailyCrops.txt.gz.  The file is written into the same folder as the ETL tool, namely the project home folder.  On my laptop it takes a minute or two, and this occurs at the beginning of the tool run.   Now the GZipped payload has to be unpacked.  It is delimited text data so can be opened with a FeatureReader using CSV format.  CSV data rarely travels with a schema.ini file revealing its field types, but the schema is discoverable here, just click the Usage option.  The file is however monolithic, containing all statistics for all crops and multiple aggregation areas (county, state, national) in just the one file, plus three aggregation periods (annual, monthly and point-in-time).  For my purposes I'm interested only in statistics for the County level.  So how do we separate the data by crop and county-level statistic?  The technique is called fanout, in this case two types of fanout are used, dataset fanout that directs records into separate File GDBs for each statistic, and featuretype fanout that directs records into separate tables within each dataset.   The three output parameters (folders for annual, monthly and point in time statistics) have dataset fanout taken from the value of the STATISTICS_UNIT field, which is calculated at run time by concatenating STATISTICCAT_DESC and UNIT_DESC fields.     Each output dataset has writers for crop statistics where featuretype fanout is taken from the value of the COMMODITY_DESC field.  Here is the property dialog for ANNUAL statistics:     Note also the table handling property is set to Drop and Create, this is so re-runs of the tool remake each crop statistic table and don't keep adding to previously created data.   This combination of fanout settings dynamically creates 43 File GDBs for annual statistics, each with as many tables as there are crops reporting the statistic.  There are smaller numbers of monthly and point-in-time workspaces output.  Here is how the ANNUAL folder looks after the initial run (this takes a little less than 3 hours on my laptop):     You can inspect the processing in the transformers that are outside of bookmarks and see that the basic idea is to ensure the VALUE field contains a valid numeric value and that the fanout attributes and state & county naming fields are well formed.  The LOAD_TIME field is also made into a correct datetime value.  Otherwise what went in is what comes out into each crop statistic table.  Here is the schema:     You will notice a feature class COUNTYBOUNDARIES is also copied into each output workspace so that the forthcoming view creation step can use objects within a single File GDB.  A fine point; I use the API-based FILEGDB writer to output COUNTYBOUNDARIES as it has the ability to create an index on county and state name fields; the indexes will be used by the join processor when views are calculated.  These indexes would be created automatically by the underlying Create Database View geoprocessing tool but I like to roll up these background tasks into my ETL.   Another output for each dataset is a table MAXLOADTIMES that shows the time of the latest load time for each crop statistic.  More on this later.   At this point we are ready for view creation.  The input CSV data has no geometry, the whole point of the views is to join county boundary geometry to each crop statistic table.  The script tool MakeCropViews walks each output dataset and creates views from all the crop statistic tables, using the Create Database View tool.  For the ANNUAL folder this makes 895 views in a little over 15 minutes.  Now that is a lot of data you don't have to generate manually!   Here is what you'll see in the message stream as MakeCropViews runs:     File GDB views follow the SQL 92 standard, they are evaluated at run time and do not make a copy of any data.  You can also replace the data referenced by a view without affecting the view, which is a key point, you can schedule the ETL to replace the underlying data while leaving the views in place.   Expanding the 'AREA GROWN ACRES' dataset we can see the views, ready for use in a map:     Lets have a look at a view.  I'm going with peanuts.  I have never made a study of peanuts, but at least I know what they are.  Working with this data I learned of crops like Escarole Endive, which I may have eaten but never knew.  I like red-skinned Valencia peanuts, I think they make the best peanut butter, and to believe the packaging, excellent ones come from Texas.  I also buy a lot of peanuts in the shell (roasted, unknown variety, unsalted) to feed the wildlife in our yard, and that packaging assures me Virginia peanuts are great too.  Peanuts here we go.   I added PEANUTS_VIEW from the workspace YIELD LB PER ACRE.gdb to my map and symbolized by VALUE with graduated color using 10 classes with a color ramp from green to red (red is high productivity).  Displaying all features I can see the engine room of peanut productivity is the arc of land across Mississippi, Alabama, Georgia, the Carolinas and up to Virginia, and more west of the Mississippi in Oklahoma and Texas.     Time enabling on YEAR gives us the real story behind peanuts though.  In the download, view the 30 second movie PeanutsTheMovie.mp4.  This animates in single-year frames for all years 1934 to 2018.   Here is 1934, what we might now call historically low yield and only in the east of 'peanut country':     By 1965 productivity and range had increased:     By 1975 productivity and expansion had greatly increased:     ..and moving right along to the current time, peanut yield per acre has reached yields ten times historic values:     A common issue with big data is you want to find what has changed - what is new.  This is where the script tool ReportLoadTimes comes in.  This reads each MAXLOADTIMES table and emits a message about the most recent crop(s) statistic in each workspace.  For my data at time of writing I can see this:     So for example in my workspace YIELD LB PER ACRE where my peanuts view lives, the latest statistics are for these crops updated a few days before finishing this blog:   Latest YIELD LB PER ACRE : LENTILS statistic was loaded at 4/16/2020 3:00:22 PM Latest YIELD LB PER ACRE : PEAS statistic was loaded at 4/16/2020 3:00:22 PM Latest YIELD LB PER ACRE : CHICKPEAS statistic was loaded at 4/16/2020 3:00:22 PM Latest YIELD LB PER ACRE : BEANS statistic was loaded at 4/16/2020 3:00:22 PM All crops sharing the latest load date are reported.   I hope this gives you confidence to tackle your own big data problems with ETL and File GDB views.  I read that for NASS data there are many commercial decisions made based on crop statistics and you will have your own business drivers.  Caveat:  NASS has some peculiarities that prevent all records displaying, for example some VALUE values are non-numeric and are discarded by this processing, and there are some county aggregations that cannot be mapped to county boundaries, so don't go building your peanut butter factory on my analysis.   Have fun! ... View more

Q:           What is ArcGIS Data Interoperability? A:            It’s a product of course, an extension for desktop and enterprise, but as a technology its Esri’s no-code integration solution.  ‘Integration’ meaning connecting ArcGIS to data – for read or write – across a huge variety of formats, feeds and repositories, then manipulating the data as required for your business needs.  If you can access it then Data Interoperability can probably work with it how you want.   Q:           That’s a pretty bold claim - if you can ‘connect’ you can probably ‘use’ – what kind of data sources are we really talking about? A:            Historically it was all about file-based formats like CAD and some proprietary formats used by early GIS software, plus anything text-based – files on disk.  Database connectivity was added – network transport.  Then an era of XML arrived, and raster and point cloud.  FTP and HTTP connectivity were added to reach the internet, then JSON so ‘data in motion’ became tractable, and lately connectors to cloud stores.  As products have evolved from desktop through network to web the technology has adapted to ‘just work’.  This all snowballs of course, nothing goes away, and new sources of all types are added as they emerge.   Q:           Is Data Interoperability the same as FME? A:            It is except when it isn’t.  You’re obviously aware it is FME technology, Safe Software build Data Interoperability for us, but the two desktop products differ slightly in what functionality is included in a licensed item, and the server products are different.  We collaborate with Safe very closely to make sure functionality Esri users are going to need gets into FME technology and hence Data Interoperability.  Many users have both products, you can share workspace files between them, and your skills in each are applicable in the other.   Q:           If I connect to data can I just use it like a geodatabase item or feature service or other ArcGIS native source in my mapping and geoprocessing? A:            Yes, after writing it to where an Esri app can see it, but accessing raw data is only part of the story.  It is rare that data is in exactly the schema you want for your work, and filtering and geometry remediation is commonly needed.  This is where the hundreds of data transformation tools come in.  Once you connect to data you diagram a stream-based workflow that implements the processing you need, and lastly write your result where you need it.   Q:           You said: ‘diagram a stream-based workflow’.  Is this with ModelBuilder? A:            Data Interoperability provides an app – Workbench – which delivers a visual programming environment like ModelBuilder.  It is very easy to drive, you work on a canvas, add, configure and connect things called readers, transformers and writers, do partial or complete runs like ModelBuilder, and sessions can be persisted as geoprocessing tools, again like ModelBuilder.  I want to stress that working in Workbench is like ModelBuilder or creating Python script tools in that you work in the ArcGIS geoprocessing environment.  Like ModelBuilder but unlike Python script tools, you aren’t coding, you are diagramming.   Q:           I’m handy with Python, does that help? A:            It might, but Python isn’t necessary.  Data Interoperability is no-code technology, but code friendly too.  Sometimes it saves some diagramming work to use a Python snippet for a function.  This is another similarity with ModelBuilder, which has a model tool ‘Calculate Value’ that lets you apply a Python snippet.  In both cases it can be a timesaver.   Q:           Data Interoperability is ETL, but I see cloud vendors promoting ELT, do you have comments? A:            Extract Transform & Load (ETL) is where the transformation of data is done before sending it to a system of record, Extract Load & Transform (ELT) is where data is first sent (or already exists) in a system of record and is manipulated within that, for example by using SQL or using a view and/or a SQL mimic language in a JSON store technology.  While Data Interoperability has first class manipulation capability, it also has query connectivity within cloud platforms, letting you work on the data where it is and not hauling it up and down.  Don’t fight data gravity, do what works for you, Data Interoperability lets you do this from within ArcGIS as either ETL or ELT.   Q:           What is the best learning pathway for Data Interoperability?  Is this how you got started? A:            There are online courses at esri.com/training, and a wealth of information at safe.com for FME Desktop, which is almost completely applicable to Data Interoperability, but the most valuable learning pathway is to learn by doing – i.e. making ETL tools - and engaging with the ETL community on GeoNet and at knowledge.safe.com.  I picked up Data Interoperability by being involved in migrations of CAD systems to ArcGIS.   Q:           I have heard of Data Interop for Server.  Can you explain ETL functionality in the server context? A:            The pattern for Data Interoperability for Server is within geoprocessing service publication, it is just a specific case of that.  There are some details you need to think about, like how to handle ETL tools that output a workspace – such as a File Geodatabase – which can’t be done in core geoprocessing (you can zip the GDB and return a file parameter), and services should be asynchronous, but otherwise just treat an ETL tool like, say, a Python script tool.   Q:           I have heard people use Data Interoperability for supporting standards-based formats and protocols, like GML or WFS or GeoPackage.  Can you elaborate a bit on that? A:            Popular formats and protocols often eventually find their way into core ArcGIS, even if originally support for them was through Data Interoperability.  However, it is usually the case that a much richer translation experience remains available in Data Interoperability.  This is true even for de-facto standards like CSV, Excel and KML.   Q:           Can you share any interesting scenarios where Data Interoperability has played an important role? A:            The most impactful outcomes – being subjective here, but the pattern is recurring and the number of users very large – is when a system of record can be harvested for authoritative data, then the data ameliorated and used to maintain a hosted feature service in ArcGIS Enterprise or Online, which of course delivers a performant data source to Esri apps.  The source system might be FTP, HTTP, WFS, Protocol Buffer, a REST API endpoint – anything.  The advantage of this pattern is you can automate data provision to Esri users with no downtime and without disrupting the system of record.   Q:           I have heard of FME server.  Do you support that? A:            I mentioned that the ArcGIS and FME server products are different – meaning separate – but they have functional overlap.  Data Interoperability for ArcGIS Enterprise delivers web ETL in the geoprocessing service framework, a good pattern for doing things like scaling format translation work, or processing file-based data sources heavily used in your organization, for example you may process a lot of Excel files.  FME Server can be used in the same way but wouldn’t be a good investment compared to licensing Data Interoperability for an existing ArcGIS Enterprise installation.  However, FME Server has a rich trigger-action automation capability that can be used for complex integrations amongst multiple systems.  If your work is about integrations that do not emit results to a client but perform synchronizations, then FME Server may be indicated.  If installed alongside ArcGIS Enterprise, your integrations can include ArcGIS software like ArcPy or the ArcGIS Python API.   Q:           What is in the roadmap for Data Interoperability? A:            There is always a long list of format and transformation functionality in the pipeline, but a couple of things stand out which are close to release so hopefully will be out by the time this Q&A goes out the door.  The first is the maturing of tools to interact with big data.  An example I intend to blog about is retrieving large compressed CSV data (that changes daily) from the web, then using GeoAnalytics Desktop to do some parallelized Spark processing in Pro, then sending the results as a Parquet file to a cloud platform where it can be queried by anyone; all as an ETL toolset.  Automating this so it happens daily on a schedule will drive the point home.  A second development we expect to be a crowd-pleaser is the ability to manage ArcGIS Online items within an ETL workflow, that means the upload, overwrite, download or deletion of the many Online item types you can create with Data Interoperability.  This will close the loop on sharing ETL processing on Esri’s public cloud.   Q:           Can you talk about more futures – not just Data Interoperability, but generally in the areas of ETL, app integration and such? A:            Nothing is going away, so file-based and network-based data sources will continue to exist, but for some time now and increasingly, data is moved around in web formats like JSON, and we are seeing trends in services with protocol buffer payloads, and formats optimized for moving big data around, like Parquet.  Interacting with REST APIs is almost old hat now and made simple with Data Interoperability.  You can take it for granted that formats or protocols that have traction in industry will be supported by Data Interoperability.  Focus on your work and tell us what you need, the future will be built for you.   Q:           Where can I get more information about Data Interoperability in particular, and ETL related topics in general? A:            Your Esri representative will be pleased to advise you on Data Interoperability licensing.  ETL is a big topic and a well-documented industry discipline, but to really bootstrap your project consider a professional services engagement with your local Esri representative or partner.  For background material, search on GeoNet within the Open Platform, Standards and Interoperability space. ... View more