Overwrite ArcGIS Online Feature Service using Truncate and Append
You may have a need to overwrite an ArcGIS Online hosted feature service due to feature and/or attribute updates. However, this could cause some data loss such as pop-ups, symbology changes, etc in the hosted feature service. For example, you will receive a warning about this when you try to overwrite a feature service in ArcGIS Pro:
One way around this is to use the ArcGIS API for Python. If you are the data owner, or Administrator, you can truncate the feature service, and then append data. This is essentially an overwrite of the feature service. The below script will do this by specifying a local feature class and the item id of the feature service you wish to update. The script will then execute the following steps:
- export the feature class to a temporary File Geodatabase
- zip the File Geodatabase
- upload the zipped File Geodatabase to AGOL
- truncate the feature service
- append the zipped File Geodatabase to the feature service
- delete the uploaded zipped File Geodatabase in AGOL
- delete the local zipped File Geodatabase
- delete the temporary File Geodatabase
Here is an explanation of the script variables:
- username = ArcGIS Online username
- password = ArcGIS Online username password
- fc = path to feature class used to update feature service
- fsItemId = the item id of the ArcGIS Online feature service
- featureService = True if updating a Feature Service, False if updating a Hosted Table
- hostedTable = True is updating a Hosted Table, False if updating a Feature Service
- layerIndex = feature service layer index
- disableSync = True to disable sync, and then re-enable sync after append, False to not disable sync. Set to True if sync is not enabled
- updateSchema = True will remove/add fields from feature service keeping schema in-sync, False will not remove/add fields
- upsert = True will not truncate the feature service, requires a field with unique values
- uniqueField = Field that contains unique values
Note: For this script to work, the field names in the feature class must match the field names in the hosted feature service. The hosted feature service can have additional fields, though.
Video
Script
import arcpy, os, time, uuid, arcgis
from zipfile import ZipFile
from arcgis.gis import GIS
import arcgis.features
# Variables
username = "jskinner_rats" # AGOL Username
password = "********" # AGOL Password
fc = r"c:\DB Connections\GIS@PLANNING.sde\GIS.Parcels" # Path to Feature Class
fsItemId = "a0ad52a76ded483b82c3943321f76f5a" # Feature Service Item ID to update
featureService = True # True if updating a Feature Service, False if updating a Hosted Table
hostedTable = False # True is updating a Hosted Table, False if updating a Feature Service
layerIndex = 0 # Layer Index
disableSync = True # True to disable sync, and then re-enable sync after append, False to not disable sync. Set to True if sync is not enabled
updateSchema = True # True will remove/add fields from feature service keeping schema in-sync, False will not remove/add fields
upsert = True # True will not truncate the feature service, requires a field with unique values
uniqueField = 'PIN' # Field that contains unique values
# Environment Variables
arcpy.env.overwriteOutput = True
arcpy.env.preserveGlobalIds = True
def zipDir(dirPath, zipPath):
'''
Zips File Geodatabase
Args:
dirPath: (string) path to File Geodatabase
zipPath: (string) path to where File Geodatabase zip file will be created
Returns:
'''
zipf = ZipFile(zipPath , mode='w')
gdb = os.path.basename(dirPath)
for root, _ , files in os.walk(dirPath):
for file in files:
if 'lock' not in file:
filePath = os.path.join(root, file)
zipf.write(filePath , os.path.join(gdb, file))
zipf.close()
def updateFeatureServiceSchema():
'''
Updates the hosted feature service schema
Returns:
'''
# Get required fields to skip
requiredFields = [field.name for field in arcpy.ListFields(fc) if field.required]
# Get feature service fields
print("Get feature service fields")
featureServiceFields = {}
for field in fLyr.manager.properties.fields:
if field.type != 'esriFieldTypeOID' and 'Shape_' not in field.name:
featureServiceFields[field.name] = field.type
# Get feature class/table fields
print("Get feature class/table fields")
featureClassFields = {}
arcpy.env.workspace = gdb
if hostedTable == True:
for field in arcpy.ListFields(fc):
if field.name not in requiredFields:
featureClassFields[field.name] = field.type
else:
for field in arcpy.ListFields(fc):
if field.name not in requiredFields:
featureClassFields[field.name] = field.type
minusSchemaDiff = set(featureServiceFields) - set(featureClassFields)
addSchemaDiff = set(featureClassFields) - set(featureServiceFields)
# Delete removed fields
if len(minusSchemaDiff) > 0:
print("Deleting removed fields")
for key in minusSchemaDiff:
print(f"\tDeleting field {key}")
remove_field = {
"name": key,
"type": featureServiceFields[key]
}
update_dict = {"fields": [remove_field]}
fLyr.manager.delete_from_definition(update_dict)
# Create additional fields
fieldTypeDict = {}
fieldTypeDict['Date'] = 'esriFieldTypeDate'
fieldTypeDict['Double'] = 'esriFieldTypeDouble'
fieldTypeDict['Integer'] = 'esriFieldTypeInteger'
fieldTypeDict['String'] = 'esriFieldTypeString'
if len(addSchemaDiff) > 0:
print("Adding additional fields")
for key in addSchemaDiff:
print(f"\tAdding field {key}")
if fieldTypeDict[featureClassFields[key]] == 'esriFieldTypeString':
new_field = {
"name": key,
"type": fieldTypeDict[featureClassFields[key]],
"length": [field.length for field in arcpy.ListFields(fc, key)][0]
}
else:
new_field = {
"name": key,
"type": fieldTypeDict[featureClassFields[key]]
}
update_dict = {"fields": [new_field]}
fLyr.manager.add_to_definition(update_dict)
def divide_chunks(l, n):
'''
Args:
l: (list) list of unique IDs for features that have been deleted
n: (integer) number to iterate by
Returns:
'''
# looping till length l
for i in range(0, len(l), n):
yield l[i:i + n]
if __name__ == "__main__":
# Start Timer
startTime = time.time()
# Create GIS object
print("Connecting to AGOL")
gis = GIS("https://www.arcgis.com", username, password)
# Create UUID variable for GDB
gdbId = str(uuid.uuid1())
print("Creating temporary File Geodatabase")
gdb = arcpy.CreateFileGDB_management(arcpy.env.scratchFolder, gdbId)[0]
# Export featureService classes to temporary File Geodatabase
fcName = os.path.basename(fc)
fcName = fcName.split('.')[-1]
print(f"Exporting {fcName} to temp FGD")
if featureService == True:
arcpy.conversion.FeatureClassToFeatureClass(fc, gdb, fcName)
elif hostedTable == True:
arcpy.conversion.TableToTable(fc, gdb, fcName)
# Zip temp FGD
print("Zipping temp FGD")
zipDir(gdb, gdb + ".zip")
# Upload zipped File Geodatabase
print("Uploading File Geodatabase")
fgd_properties={'title':gdbId, 'tags':'temp file geodatabase', 'type':'File Geodatabase'}
if arcgis.__version__ < '2.4.0':
fgd_item = gis.content.add(item_properties=fgd_properties, data=gdb + ".zip")
elif arcgis.__version__ >= '2.4.0':
root_folder = gis.content.folders.get()
fgd_item = root_folder.add(item_properties=fgd_properties, file=gdb + ".zip").result()
# Get featureService/hostedTable layer
serviceLayer = gis.content.get(fsItemId)
if featureService == True:
fLyr = serviceLayer.layers[layerIndex]
elif hostedTable == True:
fLyr = serviceLayer.tables[layerIndex]
# Append features from featureService class/hostedTable
if upsert == True:
# Check if unique field has index
indexedFields = []
for index in fLyr.manager.properties['indexes']:
indexedFields.append(index['fields'])
if uniqueField not in indexedFields:
print(f"{uniqueField} does not have unique index; creating")
fLyr.manager.add_to_definition({
"indexes": [
{
"fields": f"{uniqueField}",
"isUnique": True,
"description": "Unique field for upsert"
}
]
})
# Schema Sync
if updateSchema == True:
updateFeatureServiceSchema()
# Append features
print("Appending features")
fLyr.append(item_id=fgd_item.id, upload_format="filegdb", upsert=True, upsert_matching_field=uniqueField, field_mappings=[])
# Delete features that have been removed from source
# Get list of unique field for feature class and feature service
entGDBList = [row[0] for row in arcpy.da.SearchCursor(fc, [uniqueField])]
fsList = [row[0] for row in arcpy.da.SearchCursor(fLyr.url, [uniqueField])]
s = set(entGDBList)
differences = [x for x in fsList if x not in s]
# Delete features in AGOL service that no longer exist
if len(differences) > 0:
print('Deleting differences')
if len(differences) == 1:
if type(differences[0]) == str:
features = fLyr.query(where=f"{uniqueField} = '{differences[0]}'")
else:
features = fLyr.query(where=f"{uniqueField} = {differences[0]}")
fLyr.edit_features(deletes=features)
else:
chunkList = list(divide_chunks(differences, 1000))
for list in chunkList:
chunkTuple = tuple(list)
features = fLyr.query(where=f'{uniqueField} IN {chunkTuple}')
fLyr.edit_features(deletes=features)
else:
# Schema Sync
if updateSchema == True:
updateFeatureServiceSchema()
# Truncate Feature Service
# If views exist, or disableSync = False use delete_features. OBJECTIDs will not reset
flc = arcgis.features.FeatureLayerCollection(serviceLayer.url, gis)
hasViews = False
try:
if flc.properties.hasViews == True:
print("Feature Service has view(s)")
hasViews = True
except:
hasViews = False
if hasViews == True or disableSync == False:
# Get Min OBJECTID
minOID = fLyr.query(out_statistics=[
{"statisticType": "MIN", "onStatisticField": "OBJECTID", "outStatisticFieldName": "MINOID"}])
minOBJECTID = minOID.features[0].attributes['MINOID']
# Get Max OBJECTID
maxOID = fLyr.query(out_statistics=[
{"statisticType": "MAX", "onStatisticField": "OBJECTID", "outStatisticFieldName": "MAXOID"}])
maxOBJECTID = maxOID.features[0].attributes['MAXOID']
# If more than 2,000 features, delete in 2000 increments
print("Deleting features")
if maxOBJECTID != None and minOBJECTID != None:
if (maxOBJECTID - minOBJECTID) > 2000:
x = minOBJECTID
y = x + 1999
while x < maxOBJECTID:
query = f"OBJECTID >= {x} AND OBJECTID <= {y}"
fLyr.delete_features(where=query)
x += 2000
y += 2000
# Else if less than 2,000 features, delete all
else:
print("Deleting features")
fLyr.delete_features(where="1=1")
# If no views and disableSync is True: disable Sync, truncate, and then re-enable Sync. OBJECTIDs will reset
elif hasViews == False and disableSync == True:
if flc.properties.syncEnabled == True:
print("Disabling Sync")
properties = flc.properties.capabilities
updateDict = {"capabilities": "Query", "syncEnabled": False}
flc.manager.update_definition(updateDict)
print("Truncating Feature Service")
fLyr.manager.truncate()
print("Enabling Sync")
updateDict = {"capabilities": properties, "syncEnabled": True}
flc.manager.update_definition(updateDict)
else:
print("Truncating Feature Service")
fLyr.manager.truncate()
print("Appending features")
fLyr.append(item_id=fgd_item.id, upload_format="filegdb", upsert=False, field_mappings=[])
# Delete Uploaded File Geodatabase
print("Deleting uploaded File Geodatabase")
fgd_item.delete()
# Delete temporary File Geodatabase and zip file
print("Deleting temporary FGD and zip file")
arcpy.Delete_management(gdb)
os.remove(gdb + ".zip")
endTime = time.time()
elapsedTime = round((endTime - startTime) / 60, 2)
print("Script finished in {0} minutes".format(elapsedTime))
Updates
3/3/2023: Added the ability to add/remove fields from feature service keeping schemas in-sync. For example, if a field(s) is added/removed from the feature class, it will also add/remove the field(s) from the feature service
10/17/2024: Added upsert functionality. Deleted features/rows from the source feature class/table will also be deleted from feature service. This is helpful if you do not want your feature service truncated at all. In the event of a failed append, the feature service will still contain data. A prerequisite for this is for the data to have a unique id field.
I am encountering an error when attempting to use this script and I am unsure of the reason behind it. Thank you.
@StuartMoore yes, there is an Environment Variable set to True in this script to preserver GlobalIDs:
Hey @JakeSkinner ,
I first discovered this post back in the summer and it's been working great. I ran into a warning, for the first time, this morning. It's a DeprecatedWarning related to add.
Warning (from warnings module):
File "C:\Users\BIHARIDA\AppData\Local\ESRI\conda\envs\arcgispro-py3-clone\Lib\idlelib\run.py", line 579
exec(code, self.locals)
DeprecatedWarning: add is deprecated as of 2.3.0 and has been removed in 3.0.0. Use `Folder.add()` instead.
It suggests using Folder.add. but I am unable to find any documentation on Folder.add() anywhere. I assume the "add" that it is referring to is part of this line of code:
fgd_item = gis.content.add(item_properties=fgd_properties, data=gdb + ".zip")
I should add that I just upgraded my ArcGIS Pro to 3.4 this week. Not sure if that caused the warning.
Any help or direction that you could provide would be greatly appreciated.
Thanks Jake
The script should disable and re-enable Sync in this portion of the script:
Sounds like this is not happening on your end?
Here is one of the original scripts I have been using, for some reason when the script finishes, the sync enabled is unchecked and I have to recheck it via AGOL each time.
# Variables
prjPath = r"C:\Users\cwafstet\Documents\MODERN GIS WORKING FILES\ELECTRIC\ArcGIS PRO PROJECTS\AGOL UPDATE - DATASET - ELECTRIC.aprx" # Path to Pro Project
map = 'MODERN ELECTRIC AGOL UPDATES' # Name of map in Pro Project
serviceDefID = 'a57db3b4e0db4cddafc7dacaf33cd317' # Item ID of Service Definition
featureServiceID = '0694901da5ce44bbba3d0cec88e1bb6c' # Item ID of Feature Service
portal = "https://www.arcgis.com" # AGOL
user = "xyzx" # AGOL username
password = "xyzx" # AGOL password
preserveEditorTracking = True # True/False to preserve editor tracking from feature class
unregisterReplicas = False # True/False to unregister existing replicas
# Set Environment Variables
arcpy.env.overwriteOutput = 1
# Disable warnings
requests.packages.urllib3.disable_warnings()
# Start Timer
startTime = time.time()
print(f"Connecting to AGOL")
gis = GIS(portal, user, password)
arcpy.SignInToPortal(portal, user, password)
# Local paths to create temporary content
sddraft = os.path.join(arcpy.env.scratchFolder, "WebUpdate.sddraft")
sd = os.path.join(arcpy.env.scratchFolder, "WebUpdate.sd")
sdItem = gis.content.get(serviceDefID)
# Create a new SDDraft and stage to SD
print("Creating SD file")
arcpy.env.overwriteOutput = True
prj = arcpy.mp.ArcGISProject(prjPath)
mp = prj.listMaps(map)[0]
serviceDefName = sdItem.title
arcpy.mp.CreateWebLayerSDDraft(mp, sddraft, serviceDefName, 'MY_HOSTED_SERVICES',
'FEATURE_ACCESS', '', True, True)
arcpy.StageService_server(sddraft, sd)
# Reference existing feature service to get properties
fsItem = gis.content.get(featureServiceID)
flyrCollection = FeatureLayerCollection.fromitem(fsItem)
properties = fsItem.get_data()
# Get thumbnail and metadata
thumbnail_file = fsItem.download_thumbnail(arcpy.env.scratchFolder)
metadata_file = fsItem.download_metadata(arcpy.env.scratchFolder)
# Unregister existing replicas
if unregisterReplicas:
if flyrCollection.properties.syncEnabled:
print("Unregister existing replicas")
for replica in flyrCollection.replicas.get_list():
replicaID = replica['replicaID']
flyrCollection.replicas.unregister(replicaID)
# Overwrite feature service
sdItem.update(data=sd)
print("Overwriting existing feature service")
if preserveEditorTracking:
pub_params = {"editorTrackingInfo" : {"preserveEditUsersAndTimestamps":'true'}}
fs = sdItem.publish(overwrite=True, publish_parameters=pub_params)
else:
fs = sdItem.publish(overwrite=True)
# Update service with previous properties
print("Updating service properties")
params = {'f': 'pjson', 'id': featureServiceID, 'text': json.dumps(properties), 'token': gis._con.token}
agolOrg = str(gis).split('@ ')[1].split(' ')[0]
postURL = f'{agolOrg}/sharing/rest/content/users/{fsItem.owner}/items/{featureServiceID}/update'
r = requests.post(postURL, data=params, verify=False)
response = json.loads(r.content)
# Update thumbnail and metadata
print("Updating thumbnail and metadata")
fs.update(thumbnail=thumbnail_file, metadata=metadata_file)
endTime = time.time()
elapsedTime = round((endTime - startTime) / 60, 2)
print(f"Script completed in {elapsedTime} minutes")
The script that you updated, will not run saying it cannot run with a sync enabled hosted feature layer. I am getting this message when running the updated script with a Hosted Feature Layer that has sync enabled:
fc = r"C:\Users\cwafstet\Documents\MODERN GIS WORKING FILES\ELECTRIC\GEODATABASES\MEWCo ELECTRIC SYSTEM.gdb\AGOL_ELECTRIC_DATASET\UG_PRIMARY_CONDUCTOR_FEEDER" # Path to Feature Class
fsItemId = "dbf687af06b649d3afafbc8469e8d448" # Feature Service Item ID to update
featureService = True # True if updating a Feature Service, False if updating a Hosted Table
hostedTable = False # True is updating a Hosted Table, False if updating a Feature Service
layerIndex = 0 # Layer Index
disableSync = True # True to disable sync, and then re-enable sync after append, False to not disable sync. Set to True if sync is not enabled
updateSchema = False # True will remove/add fields from feature service keeping schema in-sync, False will not remove/add fields
upsert = True # True will not truncate the feature service, requires a field with unique values
uniqueField = 'GlobalID' # Field that contains unique values
# Environment Variables
arcpy.env.overwriteOutput = True
arcpy.env.preserveGlobalIds = True
Appreciate any feedback on either of the (2) scripts.
@ModernElectric try the latest script here. I updated the code to re-enable sync on the feature service if it was previously enabled:
@BrianShepard @DJB that help document is helpful and looks like the new way forward is to use .add from the Folders method, however, I could not get this to work:
I even tried the sample code here and received the same error.
@BrianShepard good find. I think there are still some issues. The help states this should return arcgis.gis.Item, but during my testing it's returning concurrent.futures._base.Future. Which will cause the append operation to fail:
