Add Vertica as a Supported Database

[ColeAndrews]

Vertica is not a supported database by esri. Best scenario is for esri to add Vertica as a supported database. Secondary option is to allow OLE DB connections in Pro. Per esri support, there is no way to connect to Vertica in ArcGIS Pro. ArcMap can only connect via an OLE DB option, which is also not available in Pro.

There is a similar idea in "ideas", except that is for Access and personal geodatabases. This is specific to Vertica.

[MarcoBoeringa]

Per esri support, there is no way to connect to Vertica in ArcGIS Pro

This is not entirely true.

While it is true ESRI does not list Vertica under its "Supported database management systems" (Supported database management systems—Help | ArcGIS Desktop ), and you will thus not be able to create an "ordinary" ArcGIS Database Connection (*.sde) and access Vertica tables directly from the catalog window, nothing prevents you from connecting to Vertica using ODBC and Python / ArcPy.

I have successfully used the "pyodbc" Python package (Home · mkleehammer/pyodbc Wiki · GitHub) to connect to PostgreSQL 11.1 and send DDL statements to the database from both ArcMap and ArcGIS Pro through ODBC. The same thing would work with Vertica, as Vertica is listed for pyodbc as well.

Although I personally have only used pyodbc to send DDL statements like CREATE (MATERIALIZED) VIEW to the database, you should be able to access record sets as well in Python / ArcPy using SELECT statements and pyodbc. You could then use ArcPy to write out the resulting data received through ODBC to a supported database platform, or just simply an ESRI File Geodatabase.

Of course, this is not as convenient as an ArcGIS Database Connection, yet it will allow you to do a lot with the Vertica stored data with a bit of programming.

By the way, installing pyodbc in Pro is dead simple using the included Python Package Manager. It is listed as one of the installable packages. For ArcMap, it is a little bit more complicated, as you need to use the Windows Command Line and "pip" to install it.

[ColeAndrews]

Marco Boeringa‌ I appreciate the insight. I will look in to it, however I'm not much for programming workarounds. I wonder what the level of effort is for esri to be add databases as a supported type for connections? Databases types/companies continue to evolve with technology, so it would make sense that their supported databases continues to evolve and increase.

You see this from companies like Tableau and Alteryx. Tableau at first only support shapefiles, then another release they supported zipped file geodatabases as well, and now in an upcoming 2019 release they support direct connect to file geodatabases without zipping. Alteryx supported shapefiles, then personal geodatabases, and now file geodatabases. Not exactly the same as enterprise databases like Vertica, but similar concept of evolution.

[MarcoBoeringa]

To be fair to ESRI, compared to many other GIS's or CAD applications, ESRI already supports a considerable amount of databases, even for part of their most advanced "geodatabase" type functionality with all it goodies of specialized datasets with advanced properties (Network Datasets, Topology etc.)

Anyway, Vertica seems a big data / cloud computation spin off of PostgreSQL if I read this right on the pyodbc pages, so support may be coming one day.

[ColeAndrews]

FYI- Geodata team at esri recognized the missing ODBC functionality in Pro, which they created an enhancement request for. The enhancement is for the OLE DB tool to be added to Pro so that there is parity between ArcMap and Pro, but supporting Vertica as a native database is still the ideal solution so that spatial geometries can be read in to Arc rather than just flat table data.

ENH-000121416

[ColeAndrews]

No traction on esri directly supporting Vertica yet, but OLE DB connections are how we have been handling this in ArcMap. OLE DB is marked as implemented for ArcGIS Pro in 2.6: