A library for extracting tabular/spatial data from many different sources, transforming it to meet ArcGIS Online hosted feature service requirements, and loading it into existing feature services.
palletjack is a library of modules for
updating data in ArcGIS Online (AGOL) based on information from external data. It handles the
repetitive parts of the extract, transform, and load stages of the ETL process, allowing the user to
focus on the custom transform steps unique to every project.
As a library, palletjack is not meant to be used by itself. Rather, its classes and methods should be used by other apps written for specific use cases. These other apps are referred to as the "client" apps, and internally we often refer to them as "skids".
pandas dataframes are the main unifying data structure between the different steps. The client loads data from an external source into a dataframe and then modifies the dataframe according to their business needs. Once the dataframe is ready to go, it is used to update the hosted feature service in AGOL.
The individual modules within palletjack each handle their own step of the ETL process. Each module contains classes for accomplishing its task organized by source, operation, or destination. There may be multiple, similar methods in a class depending on exactly how you want to perform a given step—you probably won't use all the available classes and methods in every application. The publicly-exposed methods usually call several private methods to keep functions small and testable.
handle the extract stage, pulling data in from external sources. Tabular data (csvs, Google Sheets)
are loaded into dataframes, while non-tabular data for attachments are just downloaded to the
specified locations. Spatial data are loaded into spatially-enable dataframes. You'll instantiate
the desired class with the basic connection info and then call the appropriate method on the
resulting object to extract the data.
There are a handful of classes in
methods for cleaning and preparing your dataframes for upload to AGOL. You may also need to modify
your data to fit your specific business needs: calculating fields, renaming fields, performing
quality checks, etc. Some classes only have static methods can be called directly without needing to
instantiate the class.
Once your dataframe is looking pretty, the
palletjack.load module will help you
update a hosted feature service with your new data. The
represents a single feature layer and its supporting information, allowing you to call the
individual methods for the necessary parts of the update process (add, remove, update, or completely
truncate and load).
While many parts of the classes' functionality are hidden in private methods, commonly-used code is
exposed publicly in the
palletjack.utils module. You will
probably not need any of the methods provided, but they may be useful for other projects. This is
palletjack's junk drawer.
Under the hood, palletjack uses the
arcgis.features.FeatureLayer.append() method to
upload data. To eliminate the dependency on
arcpy (and thus ArcGIS Pro/Enterprise), it
uses GeoPandas and pyogrio to save data to a geodatabase, uploads the geodatabase to AGOL, calls
.append() using the geodatabase as the source, and then deletes the geodatabase item
from AGOL. palletjack tests for all the known gotchas and raises an error if the data needs extra
work before uploading. In addition, AGOL imposes its own set of constraints.
The column names in your dataframes should match the field names in AGOL one-to-one, with the
exception of AGOL's auto-generated fields (shape length/area, editor tracking, etc). You can use
to handle the majority of field name formatting, but you'll want to double check the results.
The upload process is very particular about data types and missing data.
contains a mapping of dataframe dtypes to Esri field types. They generally follow what you would
expect. However, because pandas (currently) handles missing data by with
default, you may have integer data assigned a float dtype. In addition, some sources render missing
data as an empty string, creating an object dtype. Finally, datetimes must be in UTC and stored in
has methods to help convert your data to these dtypes. In addition, it's a good practice to use
pandas' nullable dtypes via
(see also the section on nullable ints).
OBJECTID and Join Keys
If you want to update existing data without truncating and loading, you will need a join key between
the incoming new data and the existing AGOL data. Do not use OBJECTID for this field; it may change
at any time. Instead, use your own custom field that you have complete control over. You will
perform the join manually in the transform step with pandas by loading the live AGOL data into a
dataframe, joining the new data into the live data, and then passing the resulting dataframe to
This method uses the live data's OBJECTID to apply the edits to the proper rows.
The client is responsible for handling errors and warnings that arise during the process. palletjack will raise its own errors when something occurs that would keep the process from continuing or when one of its internal data checks fails. It will also captured and chain errors from the underlying libraries to include additional, context-specific messages for the user. It will raise warnings when something happens that the client should be aware of but will not keep the process from completing.
palletjack takes full advantage of python's built-in
library to perform its feedback and reporting. It employs a hierarchical structure, creating module-level
loggers for each module and then each class in a module creates their own child logger. This allows
rapid identification of where log events are occurring.
Accessing palletjack logs
The client can get a reference to the palletjack logger and add their handlers, formatters, etc to it alongside its own logger:
myapp_logger = logging.getLogger('my_app') myapp_logger.setLevel(logging.INFO) palletjack_logger = logging.getLogger('palletjack') palletjack_logger.setLevel(logging.INFO) #: set up handlers and formatters #: ... myapp_logger.addHandler(log_handler) palletjack_logger.addHandler(log_handler)
logging.DEBUGincludes verbose debug info that should allow you to manually (possibly programmatically) undo or redo any operation.
logging.INFOincludes standard runtime progress reports and result information.
logging.WARNINGincludes negative results from checks or other situations that the user should be aware of.
Updating from v3 to v4
palletjack v4's biggest breaking change requires you to now instantiate a
FeatureServiceUpdater class yourself before calling the appropriate methods. v3 used
class methods to handle instantiation for you, but we've broken this out into the more traditional
pattern to store the additional information that's common to all the steps.
v4 also completely does away with JSON for uploads and storage. Because of this, you no longer need to worry about projecting to WGS84 or (relatively sane) dataset sizes. In addition, truncate and load now uses a simple boolean flag to save the existing data, and saves it as a file gdb instead of a JSON file.
Updating from v2 to v3
palletjack v3 has several changes from the previous version that users will need to consider when updating existing clients. Version 3 is designed to align with each step in the ETL process and to better follow the single-responsibility principle.
The largest change is that the namespace has been refactored to match the ETL steps.
loaders.py has been changed to
been changed to
load.py. This eliminates the confusion created by "loaders" being used
in the ETL "extract" stage.
As a corollary to this, clients now import each module rather than palletjack exposing the classes
directly. The recommended import is
from palletjack import palletjack.extract, palletjack.transform, palletjack.load, palletjack.utils
(omitting unused modules as necessary).
Version 3 also introduces the use of class methods to take care of object instantiation for the
client. These are used the most in
where the client just calls the relevant methods.
One Step at a Time
As part of the refactor, each method generally only tries to do one thing: extract one piece of data, clean one common data error, or perform one load operation.
Previous versions focused on being able to call a single method to do everything. This quickly got unwieldly and led to a lot of complexity trying to match the complexity of real-world data.
Version 3 load methods expect the client to have already cleaned the data and made it ready for
uploading. Many of the cleaning steps that were coupled to the load methods in version 2 have been
refactored to the
.append instead of .edit_features
Under the hood, version 3 has completely replaced
FeatureLayer.append() based on the recommendation in the ArcGIS API for Python docs.
This has a couple ramifications for the client. First, in order to avoid the arcpy dependency, all
data are converted to geojson for upload. This requires the client to project the dataframes to
WGS84/wkid 4326 prior to updating the feature service. Secondly, the client must separate out add,
update, and delete operations into individual method calls.
Expand source code
"""A library for extracting tabular/spatial data from many different sources, transforming it to meet ArcGIS Online hosted feature service requirements, and loading it into existing feature services. .. include:: ../../docs/README.md """ import locale from . import extract, load, transform, utils from .errors import IntFieldAsFloatError, TimezoneAwareDatetimeError #: If the locale is not set explicitly, set it to the system default for text to number conversions if not locale.getlocale(locale.LC_NUMERIC): locale.setlocale(locale.LC_NUMERIC, locale.getlocale())
Errors specific to palletjack
Extract tabular/spatial data from various sources into a pandas dataframe …
Modify existing ArcGIS Online content (mostly hosted feature services). Contains classes for updating hosted feature service data, modifying the …
Transform pandas dataframes in preparation for loading to AGOL.
Utility classes and methods that are used internally throughout palletjack. Many are exposed publicly in case they are useful elsewhere in a client's …
A single source of truth for the version in a programmatically-accessible variable. This must only include a single line: version = '0.0.0'