topic Re: branch versioning vs. mass updates in Geodatabase Questions

branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 15:25:24 GMT

Hi folks,

we are switching from traditional versioning to branch versioning as we are also switching from direct db access to a servcie based architecture.

In the past we made bulk updates on tables directly via sql but disabled versioning prior these activities and re-enabling the traditional versioning after this update. This is against the "recommodation" from esri but it was working quite good.

As the new branch versioning is new to us we also want to follow the proposed workflow from Esri but as a consequence we will most likely see a grow of our tables by factor 2 with every bulk update. This will be "mad" after a few weeks in our use case....
Of course we could also stop the feature service, disable versioning do our "mass updates" via sql or feature servcie and enable versioning again. But this will b e not in line with the esri policy, will it?

Are there any other users facing a similar problem? How have you overcome this problem?

I have seen this comment from the 2022 UC

Can the same feature class be set to use both traditional and branch versioning?

A dataset can only have one registration type. It would either be traditional or branch versioned.This pattern should be consistent for all datasets found within a feature dataset.

Looking forward your replies.

Best, Riccardo

Re: branch versioning vs. mass updates

jcarlson — Thu, 02 Feb 2023 15:33:28 GMT

A Branch Versioned service is by definition published to a Portal, and so has access via a REST endpoint. Using editing operations through, for instance, the ArcGIS Python API, you can easily make bulk edits without stopping the service. You can also specify the gdbVersion parameter, in the event that you do not want to make bulk edits to the default version.

Re: branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 15:37:55 GMT

thanks for the insight @jcarlson . So there is no need to update any sidecar table / take care of any othe table? The table structure is easy to understand and to my understanding it is just important not to skrew up the additonal fields that are relevant for the versioning. python API edits directly against the db or also via the service? confused a bit.

Re: branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 15:43:33 GMT

but also edits to default will increase the number of rows by multiples,,,

Re: branch versioning vs. mass updates

jcarlson — Thu, 02 Feb 2023 15:44:32 GMT

Using the API is strictly editing against the service, so there's no need to worry about edit version uuids or whatnot, as long as you're specifying a valid version or editing the default version. The REST endpoint converts it all into valid SQL in the background anyway.

What kind of update are you making? Do you have some other datasource you're applying to the versioned service, or is it a sort of field calculation?

Now, you could still edit directly against the DB, but we tried that for a while and it ended up introducing errors, no matter how careful we were with the expression. Using "proper methods" has worked just fine.

Re: branch versioning vs. mass updates

jcarlson — Thu, 02 Feb 2023 16:25:05 GMT

An important consideration! If you're worried it will impact performance or storage size, I would suggest testing the scenario and getting metrics on it. We don't find this matters much to us, but our largest layer is still only about 100k features with very simple geometry, and our bulk edits only hit a small subset of the rows on any given occasion.

What is your bulk update for? Are the edited attributes / features crucial for those who are editing the service, or is it primarily for users who will only be viewing the service?

Re: branch versioning vs. mass updates

DavidSolari — Thu, 02 Feb 2023 17:19:53 GMT

The table structure for Branch Versioned data is much simpler than Traditional Versioning, it's basically a tweaked version of the format used by Archive-only tables. If you want to make direct edits via SQL, start by reconciling/posting/deleting all your child versions as appropriate, then add records in accordance with how ArcGIS clients do. Off the top of my head for SQL Server EGDBs:

Inserts add a new record with a new GDB_ARCHIVE_OID, OBJECTID and GlobalID. The GDB_FROM_DATE is the UTC date of the transaction (this is different IIRC from classic archive tables, which use the local time).
Updates add a new record with the same OID and GID but a new GDB_ARCHIVE_OID. Same rules for GDB_FROM_DATE.
Deletes are the tricky one. In my initial testing it looked like you just grab the most recent record, set the delete flag and fill out the appropriate delete fields. In a later testing session it looked like deletes added a whole new record that's just a copy of the final record, but with the delete info set and the GDB_FROM_DATE set to the delete time. Make sure you test thoroughly.
Almost goes without saying, but the state you set should always be 0. If you want to make edits against a specific version then good luck.

Basically just observe how all your clients adjust the table under a variety of circumstances and then mimic that in SQL. I understand why nobody from ESRI will confirm how the operations work from the DB side but that would be a good resource, pretty please?

Re: branch versioning vs. mass updates

MarceloMarques — Fri, 03 Feb 2023 00:36:07 GMT

Esri content to explore about Branch Versioning to learn more about some of the technical details.

To Branch or Not to Branch - An Introduction to Branch Versioning (esri.com)

Enterprise data management strategies—ArcGIS Pro | Documentation

Branch version scenarios—ArcGIS Pro | Documentation

SQL Views and Branch Versioning

Utility Network Journey: Branch Versioning and SQL

I hope this helps.

Re: branch versioning vs. mass updates

George_Thompson — Thu, 02 Feb 2023 17:45:23 GMT

Here is a blog talking about SQL & Branch Versioning: https://www.esri.com/arcgis-blog/products/utility-network/electric-gas/utility-network-sql/

I would recommend against directly editing the table.......

As for the size, there is an idea: https://community.esri.com/t5/data-management-ideas/trim-archive-history-tool-for-branch-versioned/idi-p/1055659 that would be to trim the table. Go over and add your use case or Kudos.

Hope this helps!

Re: branch versioning vs. mass updates

MarceloMarques — Thu, 02 Feb 2023 22:05:06 GMT

Caveat. Direct SQL access to Branch Versioned Featureclasses tables is not supported. You need to use the REST Endpoint Service instead to manipulate the data. I hope this clarifies.

This is a good read to catch some important points.

Utility Network Journey: Branch Versioning and SQL

https://www.esri.com/arcgis-blog/products/utility-network/electric-gas/utility-network-sql/

Re: branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 21:56:16 GMT

Hi Marcelo,
I don't use network utility. So I don't need to read this. I know, that direct sql access is not "supported" yet I can write directly into the db in this table.
And as I mentioned above: I would only write directly to the db, after deregister as versioned in the db connection/sde. after direct sql access I would simply register the table as versioned again.

Re: branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 21:59:32 GMT

David, I feel you!

As you described it, it would be the way to work with the db while the table is still registered as "branch versioned table".... If I swith off the versionining for the table, the process is even simpler. I will then make the bulk edits, and switch on the versioning again.

Re: branch versioning vs. mass updates

RiccardoKlinger — Thu, 02 Feb 2023 22:00:56 GMT

Why is branch-versioning so "utility-network" centric? I don't get it.

Yet, thanks for the links.

Re: branch versioning vs. mass updates

MarceloMarques — Fri, 03 Feb 2023 00:17:44 GMT

"branch versioning" was created because of the new ArcGIS Enterprise - Portal + ArcGIS Server service architecture in mind to better support editing workflows via the web and in particular, of course, "utilities" electric, water, gas, oil, etc. to modernize their workflows, as consequence the new "utility network" replaces the old "geometric network", and the new "utility network" only works with "branch versioning". I hope this clarifies your question. -Marcelo : )

Re: branch versioning vs. mass updates

MarceloMarques — Fri, 03 Feb 2023 00:27:20 GMT

I understand. yes that will work if you unregister as versioned first.

Re: branch versioning vs. mass updates

MarceloMarques — Fri, 03 Feb 2023 05:02:40 GMT

Hello @RiccardoKlinger

Please, bear with me. I can see 2 scenarios about "branch versioning vs. mass updates" that you have already described.

A. if the branch versioned featureclass is small with just some million rows and the actual featureclass table size in the Enterprise Geodatabase RDBMS Storage is around let's say 20GB or so.

then you can open Pro
connect to Portal
add the Feature Service to the map
and perform the mass attribute update using the Pro editing tools directly in the branch version sde.default
can even use model builder to automate the process for example.
hence there is no need in this scenario to unregister as branch versioned the featureclass(es)

B. on the other hand if the branch versioned featureclass is quite large with 500 milllions rows for example and the featureclass table size in the Enterprise Geodatabase RDBMS storage is around let's say 1 TB.

then open Pro
reconcile and post all branch versions child of sde.default
close Pro
take a full database backup
stop the ArcGIS Server Feature Service(s)
need to do this because of locks that might prevent from unregister as branch versioned
open pro and unregister as branch version the featureclass(es) then close Pro
perform the mass attribute update using SQL statement
sure thing the mass field update will be faster via SQL
here consider to ask your database administrator to place the database in nologging (oracle), simple recovery mode (sql server) before you do the mass update via SQL, this will speed up the mass update via SQL
after the update is done the database administrator can change the database back to logging (oracle), full recovery mode (sqlserver)
open pro and register as branch version the featureclass(es) again and close Pro
start the ArcGIS Server Feature Service(s)
test the feature service and make sure you can make edits in a new branch version child of sde.default
if everything is fine then take another full database backup

@RiccardoKlinger this is pretty much the same process that you have already described in your original question, but I just wanted to create a step by step to clarify for anyone reading this discussion.

I do not see any major problems with Scenario B and you have also mentioned that this is working fine for you, hence although this might not be officially sanctioned by Esri yet. I do not see any other way of doing the branch version mass update for very large featureclasses, Scenario B is the more appropriate way that avoids any data corruption.

Thanks,

Re: branch versioning vs. mass updates

ArizonaGIS — Tue, 23 May 2023 21:02:16 GMT

Hello. When I try to create a new child version using the ArcGIS Python API to write some updates, I receive this message below. I wanted to know if anyone has successfully created a temporary child version, applied edits, reconciled/posted the updates to the parent, and deleted the child version in the process.

Error creating version: Failed to execute. Parameters are not valid.
ERROR 000837: The workspace is not the correct workspace type.
WARNING 000379: The value may not be valid because of empty domain or does not exist in the domain
Failed to execute (CreateVersion).

from arcgis.gis import GIS from arcgis.features import FeatureLayer, Feature import arcpy import time # Record the start time start_time = time.time() # Connect to the GIS gis = GIS(url="YOUR PORTAL URL", username="YOUR USERNAME", password="YOUR PASSWORD") # Path to the SDE file (i.e., the database connection file) database = r"P:\GIS.sde" # Define version names parent_version = "sde.DEFAULT" child_version = "Child_Version" # Try to create the child version try: arcpy.CreateVersion_management(database, parent_version, child_version, "PUBLIC") print(f"Created version: {child_version}") except Exception as e: print(f"Error creating version: {e}") arcpy.AddError(e) sys.exit() # stop the script if version creation fails # Create a feature layer from the feature class feature_layer = arcpy.management.MakeFeatureLayer(feature_class, "FeatureLayer").getOutput(0) # Switch to the new version arcpy.ChangeVersion_management(feature_layer, "TRANSACTIONAL", f"{child_version}") # Get the feature service service_id = "SERVICE ID" service = gis.content.get(service_id) layer = service.layers[0] # Fetch the features to be updated features = layer.query() # You may need to refine this query # Create a dictionary to match the prefixes to the correct "St_PreTyp" value prefixes = { "Interstate": "Interstate" } # Loop through the features for feature in features: # Get the "St_Name" value st_name = feature.attributes["St_Name"] if st_name is not None: update_required = True # Check if St_Name contains only the Spanish prefix if st_name.strip() in ["Calle"]: update_required = False # No update required # If update is required, loop through the prefixes to find a match if update_required: for prefix, pretyp in prefixes.items(): if st_name.startswith(prefix): # If additional characters are present, parse and update the fields feature.attributes["St_PreTyp"] = pretyp feature.attributes["St_Name"] = st_name.replace(prefix, "").lstrip().replace("Highway", "").lstrip() break else: feature.attributes["St_PreTyp"] = None # Submit the edits layer.edit_features(updates=features) # After performing your operations, reconcile and post the changes from the child version to the parent version arcpy.ReconcileVersions_management(database, "ALL_VERSIONS", parent_version, child_version, "LOCK_ACQUIRED", "NO_ABORT", "BY_OBJECT", "FAVOR_TARGET_VERSION", "POST", "DELETE_VERSION") # Switch back to the parent version arcpy.ChangeVersion_management(database, "TRANSACTIONAL", parent_version) # Delete the child version arcpy.DeleteVersion_management(database, child_version) # Record the end time end_time = time.time() # Calculate the total time in minutes total_time = (end_time - start_time) / 60 print("Successfully updated parsing for feature class") print("Total time taken: {:.2f} minutes".format(total_time))

Re: branch versioning vs. mass updates

JOSEPHAUSBY — Wed, 01 Nov 2023 20:49:31 GMT

Hi there,

What was your final solution to bulk edit data using pure T-SQL. We also have 100's of ETL processes that push/pull data directly into SQL from Esri databases. We now are using Utility Network and branch versioning.