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, theUSE SCHEMA
permission on the parent schema, and theSELECT
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>"))