New at 10.5 - Registering a database view with the geodatabase

11140
11
12-14-2016 06:53 AM
Labels (1)
MelissaJarman
Esri Contributor
14 11 11.1K

This blog will give you information about the ability to register database views with the geodatabase at 10.5.

 

ArcGIS allows access to database views and tables that are not registered with the geodatabase via a query layer. An unregistered view within a geodatabase can be a view that is created using DBMS client tools or via the Create Database View tool.

 

Many users that work with enterprise geodatabases and database views have requested the ability to register views with the geodatabase. This will store information about the database view within the geodatabase system tables to allow the user experience with views to be similar for viewing and querying geodatabase tables and feature classes.

 

At the 10.5 release the Register with Geodatabase tool has been enhanced with additional parameters and now allows the registration of views with the geodatabase. You will also notice the right click Manage > Register with Geodatabase context menu for unregistered objects will now open the Register with Geodatabase tool.

The process of creating a registered view:
 1. Create a view using DBMS client tools or Create database view geoprocessing tool
  2. Register with Geodatabase using Register with geodatabase geoprocessing tool
 
The registration process requires a view to exist that is created via the database client tools, or by using the Create Database View tool. After the view exists the view can be registered using the Register with Geodatabase tool. The right click context menu from existing database views will also open the Register with Geodatabase tool with some of the initial parameters populated. Depending on the type of columns and if the view contains existing records parameters are pre-populated when opening the tool from the right click context menu. Not all tables or views are good candidates to register with the geodatabase, so this two-step process allows users the flexibility to interact with unregistered database views via a query layer and optionally register with the geodatabase.

 

Registered views:

After the database view is registered with the geodatabase it will appear the same as a geodatabase table or feature class, depending on if the view definition contains a spatial column. Views with a spatial column will be displayed with a feature class icon corresponding to the geometry type chosen during registration.

Context menu for unregistered database views within the geodatabase connection. 

 Enhanced Register with Geodatabase tool for 10.5.

Benefits:

  • Performance improvements for viewing and querying due to table metadata (rowid, extent, etc) being stored within the system tables 
  • No prompt to specify a unique identifier when adding database views to ArcMap/Pro clients
  • Ability to create and store geodatabase metadata on the registered view

 

 

User experience:

  • Registered views are read only geodatabase objects. This means that operations such as altering the schema or underlying data will be prevented. A error message 'Not supported on a view' will be received to alert you when the operation is not supported against a registered view.
    • Registered views can be renamed, but the schema itself is read only in ArcGIS
    • To prevent participation in geodatabase functionality where workflows will involve editing registered views cannot be added to feature datasets and registered views cannot participate in geodatabase relationship classes. Additionally other geodatabase functionality that implies editing (such as editor tracking, attachments, etc) are blocked.
    • Registered views can be published via map services or query only feature services.
  • Schema modifications to the underlying tables the view is based on should be handled by refreshing the view definition using the RDBMS client tools. Next, making a connection via the ArcGIS client will update these changes. 

  

Requirements:  

  • The OBJECT ID FIELD parameter is required for database view registration. This requires a unique integer column that can represent the ROW ID for the registered view.
  • For tables with a spatial column:
    • A single spatial column
    • Spatial column must contain a single entity type
    • If table is empty with a spatial column - it is still required to input these parameters for Shape Field, Geometry Type, and Coordinate System during registration.

 

11 Comments
JacobBoyle
Occasional Contributor III

Thank goodness, performance of views has been... less than great... since we lost command line functionality.

deleted-user-6RdsSRSrFgqr
New Contributor

How do you unregister a view from the geodatabase?

Asrujit_SenGupta
MVP Regular Contributor

Ted Why not just delete that View using ArcCatalog and recreate it with the same name and SQL?

deleted-user-6RdsSRSrFgqr
New Contributor

If you delete the view using the DBMS client tools after registering it with the geodatabase, it will still exist in sde.GDB_ITEMS. There should be an Esri tool to remove the view from all gdb repository tables.

Asrujit_SenGupta
MVP Regular Contributor

Using ArcCatalog I meant.

Use the Delete—Help | ArcGIS Desktop  tool to delete and Create Database View—Help | ArcGIS Desktop to recreate OR recreate from DBMS end.

deleted-user-6RdsSRSrFgqr
New Contributor

Yes, that does work. Thanks. I was having problems with a data type of one of the fields in the view - Pro was not responding to changes in the data type. I assume it was cached in the Definition field in gdb_items. So deleting it entirely from the gdb and recreating it has worked.

SaraWander
Occasional Contributor

Is there a way to register a view without changing the schema (additional fields get added when registering a view)? I have a view in SQL Server that already has a geometry field. This view is completely truncated and then updated from a linked server in Oracle every evening. Once this view is registered, the schema change breaks this nightly update job. I need to have the view registered so I can serve it out as a feature service on ArcGIS for Server and users can download the data. Any help would be so appreciated! 

Asrujit_SenGupta
MVP Regular Contributor

Sara Considering this is a Blog, please post the scenario you are facing as a new Question, so that more people can offer their suggestions.

https://community.esri.com/community/help-and-feedback/blog/2016/08/15/community-news-and-tips-how-t... 

https://community.esri.com/docs/DOC-1087 

AlexanderGray
Occasional Contributor III

Can you register views with tables that have different owners?

JTessier
Occasional Contributor II

Does this workflow remain true for 10.8.1 and ArcPro 2.6.2 and above?

About the Author
Product Engineer - Geodatabase Team
Labels