Query layers are SQL queries stored in maps in ArcGIS AllSource projects. Query layers allow you to access spatial and nonspatial tables and views in databases and cloud data warehouses. For example, you may not have permissions in the database to create a view, but you want to limit what data from a feature class shows up in your map. You can create a query layer in the map that selects only certain fields or values from the feature class.
Tip:
Because query layers access databases directly using SQL, the data does not have to be part of an enterprise geodatabase, but you can define query layers for enterprise geodatabase tables and feature classes if you require it.
You define an SQL query to create a query layer. The query runs against the tables and views in the database or cloud data warehouse you specify, and the result set is added to the map as a layer if your query includes a spatial column or a stand-alone table if it does not.
The SQL query that defines a query layer is a static SQL statement that is run inside the database or data warehouse every time the layer is displayed or used in the map. This allows the latest information to be visible without making a copy or snapshot of the data. This is especially useful when working with dynamic information that is frequently changing. However, there are many situations where parts of SQL statements are not known in advance. For example, to display aggregated rainfall in a region, you may not know beforehand whether to aggregate the rainfall station values by day, week, or month. Parameters in query layers can help make components of SQL statements dynamic.
Keep the following in mind when working with query layers:
- Query layers are supported for specific database management systems and cloud data warehouses.
- When you drag a table from a database connection in your project onto a map, ArcGIS AllSource automatically creates a query layer that selects all fields and rows from the table. You can change what is available in the map by modifying the query layer definition from the layer's properties. However, do not drag layers onto the map if there are millions of features in the table.
- Alternatively, you can define the query layer before data is added to the map. Use this method to define a query layer for data in an enterprise geodatabase or to create a query layer from any supported source if the table contains a large numbers of features.
- Query layers behave in the same way as other feature layers or stand-alone tables in a map; you can use them to display data, use them as input to a geoprocessing tool, access them programmatically using developer APIs, or publish them.
- Query layers are read only when accessed in a map in ArcGIS AllSource.
- To edit relational database tables that are accessed as a query layer, share it as a web feature layer. Then add the web feature layer from your portal to a map and edit. The query layer cannot contain virtual columns, where clauses, or joins. Query layers that access data in a cloud data warehouse are not editable even through a web feature layer.
- If the query layer is defined on relational database data, you can export the data from a query layer to another database or enterprise geodatabase.