Update Query on Query layer in ArcGIS Pro

Question

While updating query on query layer in ArcMap is nightmare process, it seems with ArcGIS Pro this could possibly be better using Python CIM Access.

I have tried following code and it works in the way I am able to get / replace SQL query on layer:

aprx = arcpy.mp.ArcGISProject('current')
am = aprx.activeMap
lyr = am.listLayers()[0]  # Picks first layer in active map
lyr_cim = lyr.getDefinition('V2') # Reads CIM definition
query = lyr_cim.featureTable.dataConnection.sqlQuery # Reads SQL Query
query += ' WHERE GEOM.STX > 0' # Example of query update
lyr_cim.featureTable.dataConnection.sqlQuery = query # Set query in definition
lyr.setDefinition(lyr_cim) # Update definition on layer

The problem is at that moment all features from layer disappear and I get red exclamation symbol next to layer name with tool tip 'Click to repair layer's data source.'

There is nothing wrong with the query or data source, ArcGIS Pro just thinks for some reason there is. If you click through user interface of layer query properties, the updated query is there, and is correct. You just must pretend edit (e.g. delete space and add space) to be able to manually click on 'Validate' button, and 'Next', and 'Finish' button in the next window.

How do I tell ArcGIS Pro programmatically that the layer is alright?

This is a workaround in ArcGIS Pro to be able to programmatically filter features for export to ArcGIS Online. Filtering by extent on layer does not work and sends all data to AOL, filtering by definition query fails too with error - seems publishing can't deal with definition query containing underlying DB methods like STEnvelope, STCentroid etc. Ultimately, what I want is to find some way to be able to clip layers to current map extent on the fly for publishing to ArcGIS Online

Answer 1

The issue is that reading the CIM returns an array of None values in the queryFields property of the data connection. When setting the definition back on the CIM with the array of None values, the data connection on the layer breaks.

A solution is to remove the queryFields attribute from the CIM and then setting the definition.

layerCIM = layer.getDefinition('V2')
dataConnection = layerCIM.featureTable.dataConnection
# retrieving the CIM will result in a queryFields array with None values, which breaks the data connection in ArcGIS Pro.
# We need to set it as an empty array/delete the attribute when saving the CIM definition, otherwise we will have to repair the data source in Pro.
delattr(dataConnection, 'queryFields')
layer.setDefinition(layerCIM)

Also see this community post:https://community.esri.com/t5/arcgis-pro-sdk-questions/why-does-cimsqlquerydataconnection-fromjson-choke/m-p/1026292