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
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'}
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.
Thanks Jake, when i run it i get the following error
So I Frankensteined it a bit and it ran successfully
import arcpy, os, time
from zipfile import ZipFile
from arcgis.gis import GIS
arcpy.env.overwriteOutput = True
startTime = time.clock()
# Variables
username = "abc" # AGOL Username
password = ***** # AGOL Password
fc = r"L:\Data\Engineering.sde\Engineering.DBO.Roads_Skids\Engineering.DBO.Skid" # Path to Feature Class
fsItemId = "" # Feature Service Item ID to update
# Create GIS object
gis = GIS("https://www.arcgis.com", username, password)
# Function to Zip FGD
def zipDir(dirPath, zipPath):
zipf = ZipFile(zipPath , mode='w')
gdb = os.path.basename(dirPath)
for root, _ , files in os.walk(dirPath):
for file in files:
filePath = os.path.join(root, file)
zipf.write(filePath , os.path.join(gdb, file))
zipf.close()
print("Creating temporary File Geodatabase")
arcpy.CreateFileGDB_management(arcpy.env.scratchFolder, "TempGDB")
# Export feature classes to temporary File Geodatabase
fcName = os.path.basename(fc)
fcName = fcName.split('.')[-1]
print("Exporting {0} to temp FGD".format(fcName))
arcpy.conversion.FeatureClassToFeatureClass(fc, os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb"), fcName)
# Zip temp FGD
print("Zipping temp FGD")
zipDir(os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb"), os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb.zip"))
# Check if FGD exists, if True, delete item
searchResults = gis.content.search('title:tempFGD AND owner:{0}'.format(username), item_type='File Geodatabase')
if len(searchResults) > 0:
item = searchResults[0]
item.delete()
# Upload zipped File Geodatabase
print("Uploading File Geodatabase")
fgd_properties={'title':'tempFGD', 'tags':'temp file geodatabase', 'type':'File Geodatabase'}
fgd_item = gis.content.add(item_properties=fgd_properties, data=os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb.zip"))
# Truncate Feature Service
print("Truncating Feature Service")
premiseLayer = gis.content.get(fsItemId)
fLyr = premiseLayer.layers[0]
#count = fLyr.query(return_count_only=true)
max_objid = fLyr.query(out_statistics=[{"statisticType":"MAX","onStatisticField":"OID","outStatisticFieldName":"MAX_OBJ"}], return_geometry=False)
maxoid = max_objid.features[0].attributes['MAX_OBJ']
#delete in steps of 20000 or more, in case the dataset is large
i = 0
step = 20000
#replace maxoid with count if attempting to delete features based on feature count
while i <= maxoid:
i += step
fLyr.delete_features(where=f"OID<= {i}")
print(i)
#if using for loop
#for i in range(1,20000,maxoid):
print("Feature Layer was truncated")
# Append features from feature class
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(os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb"))
os.remove(os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb.zip"))
endTime = time.clock()
elapsedTime = round((endTime - startTime) / 60, 2)
print("Script finished in {0} minutes".format(elapsedTime))
The only issue i found was that it would not work if the feature layer was empty which would be to do with the query. I would love to use truncate() but understand this is a logged bug in that it is turned off by default in the metadata on AGOL.
Cheers
Jake Skinner just wondering if you have encountered runtime errors using delete features, the script runs through and deletes all features some times then other times it stops almost like its timed out
Cheers
Rory
Admittedly I'm rather new with python but whenever I run this script using a layer coming directly off my desktop I get this error:
@JakeSkinner, this worked with a layer coming off of a GDB on my desktop, however when trying to do that same a SQL server geodatabase I keep receiving this error:
I am in fact the admin, so I'm unsure why this is happening.
Any help would be great, or a redirect to a more appropriate thread.
Tried moving my service over and getting an error, figured something was configured wrong but ran it on my old machine and it also wasn't working any more.
this is the error
Creating temporary File Geodatabase
Exporting Parcel_Data to temp FGD
Zipping temp FGD
Uploading File Geodatabase
Traceback (most recent call last):
File "C:\ReggieAGOL\TruncateAppend_Parcel_Data.py", line 50, in <module>
fgd_item = gis.content.add(item_properties=fgd_properties, data=os.path.join(arcpy.env.scratchFolder, "TempGDB.gdb.zip"))
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\__init__.py", line 3973, in add
owner_name, folder)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_portalpy.py", line 326, in add_item
resp = self.con.post(path, postdata, files)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py", line 710, in post
force_bytes=kwargs.pop('force_bytes', False))
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py", line 505, in _handle_response
self._handle_json_error(data['error'], errorcode)
File "C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\site-packages\arcgis\gis\_impl\_con\_connection.py", line 523, in _handle_json_error
raise Exception(errormessage)
Exception: Item already exists. Set the overwrite flag to true if you want to overwrite.
(Error Code: 409)
>>>
@akjones3 what version of the ArcGIS API for Python are you running? You can check by running the following:
import arcgis
arcgis.__version__
@KevinMacLeodCAI you would have 2 options:
1. Overwrite the hosted feature service
2. Use the script above. All you have to do is update the 4 variables:
