Query data

This article shows how to query data in Unity Catalog.

Requirements

The compute resource that you use to run the notebook, Databricks SQL editor, or Catalog Explorer workflow to create the query must be compliant with Unity Catalog security requirements.
To query data in a table or view, the user must have the USE CATALOG permission on the parent catalog, the USE SCHEMA permission on the parent schema, and the SELECT permission on the table or view.

Note

To read from a view on a cluster that uses single-user access mode, the user must have SELECT on all referenced tables and views.

Three-level namespace notation

In Unity Catalog, a table or view is contained within a parent catalog and schema. You can refer to a table or view using two different styles of notation. You can use USE CATALOG and USE statements to specify the catalog and schema:

USE CATALOG <catalog-name>;
USE SCHEMA <schema-name>;
SELECT * from <table-name>;

spark.sql("USE CATALOG <catalog-name>")
spark.sql("USE SCHEMA <schema-name>")

display(spark.table("<table-name>"))

library(SparkR)

sql("USE CATALOG <catalog-name>")
sql("USE SCHEMA <schema-name>")

display(tableToDF("<table-name>"))

spark.sql("USE CATALOG <catalog-name>")
spark.sql("USE SCHEMA <schema-name>")

display(spark.table("<table-name>"))

As an alternative, you can use three-level namespace notation:

SELECT * from <catalog-name>.<schema-name>.<table-name>;

display(spark.table("<catalog-name>.<schema-name>.<table-name>"))

library(SparkR)

display(tableToDF("<catalog-name>.<schema-name>.<table-name>"))

display(spark.table("<catalog-name>.<schema-name>.<table-name>"))

Using three-level namespace simplifies querying data in multiple catalogs and schemas.

You can also use three-level namespace notation for data in the Hive metastore by setting <catalog-name> to hive_metastore.

Explore tables and views in Databricks SQL

You can quickly explore tables and views without the need to run a cluster by using Catalog Explorer.

To open Catalog Explorer, click Catalog in the sidebar.
In Catalog Explorer, select the catalog and schema to view its tables and views.

For objects in the Hive Metastore, you must be running a SQL warehouse to use Catalog Explorer.

Select from tables and views

To select from a table or view using a notebook:

In the sidebar, click New > Notebook.
Attach the notebook to a SQL warehouse or cluster that uses an access mode that supports Unity Catalog.
In the notebook, create a query that references Unity Catalog tables and views. You can use three-level namespace notation to select data in multiple catalogs and schemas, including the workspace-local Hive metastore.

Note

To read from a view from a cluster with single-user security mode, the user must have SELECT on all referenced tables and views.

To select from a table or view using the SQL Editor:

In the sidebar, click SQL Editor.
Select a SQL warehouse.
Compose a query. To insert a table or view into the query, select a catalog and schema, then click the name of the table or view to insert.
Click Run.

Select from files

To explore data stored in an external location before you create tables from that data, you can use Catalog Explorer or the following commands.

Permissions required: You must have the READ FILES permission on the external location associated with the cloud storage path to return a list of data files in that location.

List the files in a cloud storage path:
```
LIST 's3://<path-to-files>';
```

Query the data in the files in a given path:

SELECT * FROM <format>.`[s3://<path-to-files>`;

List the files in a cloud storage path:

display(spark.sql("LIST 's3://<path-to-files>'"))

Query the data in the files in a given path:

display(spark.read.load("s3://<path-to-files>"))

List the files in a cloud storage path:

library(SparkR)

display(sql("LIST 's3://<path-to-files>'"))

Query the data in the files in a given path:

library(SparkR)

display(loadDF("s3://<path-to-files>"))

List the files in a cloud storage path:

display(spark.sql("LIST 's3://<path-to-files>'"))

Query the data in the files in a given path:

display(spark.read.load("s3://<path-to-files>"))

Next steps

Manage privileges in Unity Catalog