Overwrite ArcGIS Online Feature Service using Truncate and Append
60390
191
04-24-2020 05:57 AM
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.
@RobertEisler I have modified the script as per below to include input and output parameters (Lines 10-12 & 144) which have replaced the variables that you edit at the beginning of the script.
(NOTE: I have also implemented the use of keyring to parse the login details for ArcGIS Online which means I don't need to store our password in the script: Lines 28-30)
import arcpy, os, time, uuid
from zipfile import ZipFile
from arcgis.gis import GIS
import arcgis.features
# Overwrite Output
arcpy.env.overwriteOutput = True
# Parameters
infc = arcpy.GetParameter(0)
outfsID = arcpy.GetParameter(1)
sublayerID = arcpy.GetParameter(2)
# Variables
fc = str(infc) # Path to Feature Class
fsItemId = outfsID # 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 = sublayerID # 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
# Start Timer
startTime = time.time()
# Create GIS object
print("Connecting to AGOL")
### Access the stored password with keyring and sign into the GIS # https://community.esri.com/t5/arcgis-online-blog/connect-to-the-gis-in-python-scripts-without/ba-p/889867
import keyring
pw = keyring.get_password("ArcGISOnline", "Username")
gis = GIS("https://fpcwa.maps.arcgis.com", "Username", pw)
print("Connected to the GIS")
# Create UUID variable for GDB
gdbId = str(uuid.uuid1())
# Function to Zip FGD
def zipDir(dirPath, zipPath):
'''Zip File Geodatabase'''
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()
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'}
fgd_item = gis.content.add(item_properties=fgd_properties, data=gdb + ".zip")
# Get featureService/hostedTable layer
serviceLayer = gis.content.get(fsItemId)
if featureService == True:
fLyr = serviceLayer.layers[layerIndex]
elif hostedTable == True:
fLyr = serviceLayer.tables[layerIndex]
# Truncate Feature Service
# If views exist, or disableSync = False use delete_features. OBJECTIDs will not reset
flc = arcgis.features.FeatureLayerCollection(serviceLayer.url, gis)
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 - 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()
# Append features from featureService class/hostedTable
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))
CompleteVariable = "Complete"
arcpy.SetParameter(3, CompleteVariable)
By doing this, I can then save the script as a Python Script Tool in a Toolbox which you can load into a Model and parse data in as required (feature class, feature service ID, sub layer ID) and get a "Complete" variable output (Line 144) so you know when it's done (can use as a precondition for further steps). This means you can also use the same script over and over as per my demo below.
by
JakeSkinner
@EthanSmith2 are you working with an ArcGIS Enterprise hosted feature service, or AGOL hosted feature service?
And, what version of the arcgis api for python are you using?
by
JakeSkinner
@ModernElectric it appears the ArcGIS API for Python will not work with attachments. I believe the reason is that is maintains Global IDs. However, you can use ArcGIS Pro's Truncate and Append tools to perform the same steps. In the Append tool's environment variables, Maintain Attachments is automatically checked:
Note: this tool will fail if you check the option Maintain Attachments and Preserve Global IDs. It has to be one or the other.
