ArcGIS 10.6.1 Relational Data Store

[TimothyCasey]

We've recently upgraded to 10.6.1 and found out that ESRI is moving users to use the Relational Data Store.  However, we've also come to realize that you can't "see" (i.e. attach to Data Store and write SQL query) what's in the Data Store. As a HIPAA covered entity, I don't believe we can in good faith use this technology with these limitations.  In some ways, we're wondering if ESRI is moving backwards (i.e. back towards proprietary database technologies).  I understand that the Data Store backend is PostgreSQL; however, I'd like ESRI to add functionality so we could browse the Data Store in ArcCatalog and/or allow us to attach to the PostgreSQL Data Store and query using SQL.

Until this happens, we won't be using the Relational Data Store.

[MarcoBoeringa]

Hi,

I am slightly wondering what issue you have connecting to the Relational Data Store being a PostgreSQL database?

I must admit I have no experience with Data Store itself, but looking through some of the Help pages of Data Store, I am pretty sure you should be able to connect to the underlying database using at least your ArcGIS Server administrator's credentials and using something like a plain Window's PostgreSQL ODBC connection DSN.

Do note though, that ESRI actually intends the Data Store to be a kind of "encapsulated" and internally managed by e.g. ArcGIS (Server) tools, not so much directly managed and accessed (which may represent a risk in trashing the Data Store just like you can when accessing an ESRI Enterprise Geodatabase through tools not designed by ESRI), see the link to the other GeoNet thread entirely below and what Derek Law wrote there.

However, as to this topic, I have successfully used ArcPy and the "pyodbc" Python package (you can install it from the ArcGIS Pro package manager as it is listed there. For reference of pyodbc, see this: https://github.com/mkleehammer/pyodbc) to connect to a plain spatial PostgreSQL database, so not an ESR Enterprise Geodatabase enabled database. I have used this connection to create e.g. spatial database views using standard SQL DDL statements like "CREATE (MATERIALIZED) VIEW" written in ordinary Python / ArcPy sripting etc. without issues.

I can subsequently browse such spatial database views from Pro by creating a standard ArcGIS "Database Connection" in the Catalog window and add the layers as ArcGIS Query Layers (http://pro.arcgis.com/en/pro-app/help/data/query-layers/what-is-a-query-layer-.htm).

You just need to be careful defining the views: some PostgreSQL field / column types are not supported in ArcGIS, a notable example being the key/value storage PostgreSQL field type "hstore". If your database tables contain such field, you must exclude it in the SQL DDL statement for creating the view, so don't blindly specify something like "CREATE VIEW X AS SELECT * FROM YOUR_TABLE", because it may not be accesible due to invalid field types. Specify the exact field names to use in the DDL statement.

To do this kind of thing though, you will need DBA type knowledge of managing PostgreSQL. If you don't have that, get some course to educate yourself in this respect.

This post by Derek Law also explains something about ArcGIS Data Store that might be of use:

https://community.esri.com/thread/191678-arcgis-data-store-and-rdbms

[TimothyCasey]

Thanks so much for the response.  I've spent two days with ESRI Tech Support and not one suggested any of this - I'm guessing they're not allowed to.  If I can get any of your suggestions to work, that will be a success for us.  We do not need to change anything in the Data Store - just read what is in there.

[MarcoBoeringa]

@Timothy Casey,

Do note that this is of course all "at your own risk"...

And it will be vital to have basic knowledge of configuring access to a PostgreSQL database, the type of knowledge a good DBA should have. There is some configuration to be done to get access to a PostgreSQL database from another computer than where the PostgreSQL server runs, and there are PostgreSQL configurations files you may need to modify, e.g. to specify allowed IP addresses of computers that should be able to access the database, and possibly things like port numbers the database should listen at.

I've managed to figure out a lot of this stuff myself based on reading documentation, help and PostgreSQL discussion threads on the internet, but I strongly recommend some "starter" DBA type course for PostgreSQL if you don't have a solid IT background and some form of DBA experience (not necessarily PostgreSQL).

[TimothyCasey]

Of course.  I have DBAs to handle such things.  I'm guessing as people gain more experience with the +/- of the Data Store, solutions will present themselves.

[pheede-esri]

Hi Timothy,

I'd like to point you to the whitepaper that we recently published that speaks to the various data storage options within ArcGIS Enterprise including ArcGIS-managed options like ArcGIS Data Store vs. user-managed options like a traditional geodatabase based on an RDBMS that you manage directly: Data in ArcGIS: User Managed and ArcGIS Managed

The main comment I want to address is your statement that "ESRI is moving users to use the Relational Data Store". This is certainly not the case. There is no requirement that you must move data into the relational data store nor is there any intention to push you towards that option if it isn't appropriate for you. 

Sincerely,
Philip

[TimothyCasey]

Would you agree that if ESRI is only adding advanced features to the ArcGIS Data Store functionality, for all intents and purposes, "ESRI is moving users to use the Relational Data Store"?

[pheede-esri]

Certainly not. All of the advanced data modeling capabilities, like topologies, attribute rules, and more are exposed primarily via traditional geodatabases. You may have seen some of those capabilities start showing up for hosted layers as well, but that but no means should be interpreted as a push to move users from one to the other. It's about giving choice between managing the underlying data yourself (user-managed via your own geodatabase) or by the system (ArcGIS-managed).

I'm curious what advanced features you have seen show up only for hosted layers that aren't also available via your own geodatabase?

[TimothyCasey]

Philip - I appreciate your feedback and maybe it's just the way I'm thinking about it.  However, to answer you specifically, I found this in this thread (https://community.esri.com/thread/191678-arcgis-data-store-and-rdbms#comment-672633 )

Release of the Deprecated Features Plan for ArcGIS 10.5.1 | Support Services Blog 

• ArcGIS 10.5.1 is the last release to support anything other than the Data Store product as a data store for a Hosting Server.

So, from where I sit (the CIO seat - so I'm no expert), this kind of language suggests that there will be functionality available thru the Data Store technology that won't be available in legacy configurations.

As I think about it, this part of my original question is moot.  Until there's a supported way for me to see what's inside the Data Store, I can't use it anyway (from a HIPAA compliance perspective) since it's a bit of a black box.  And this won't happen until enough HIPAA-covered entities come to the same realization.

So thanks for your help. We're going to give some of your non-supported suggestions a try and make a determination if they'll ever be officially supported.

[RandyCasey]

I have found in the two years of using Data Store, that you have to use a very methodical approach to when you use it. Simply put, it does not work for every type of feature service. The most important thing to remember is that Data Store is NOT a replacement for Enterprise Geodatabases hosted by either SQL or Oracle servers. It is a supplemental tool that can be used in parallel with more traditional data hosting services (FGDB or EGDB). I wrote this analysis: Summary Analysis or Storing Feature Classes - for our division, which shows the pros and cons of using Data Store in comparison to EGDB - so that our staff could make informed decisions on when to use either Data Store or EGDB.

I personally have found Hosted Feature Services to be incredibly fast and agile, which is a benefit if you produce field applications like we do. But that comes at a cost of data integrity, which must be taken into account. Like any tool, I only use it when it's appropriate.