SQL Endpoint APIs

Preview

This feature is in Private Preview. To try it, reach out to your Databricks contact.

Important

To access Databricks REST APIs, you must authenticate.

Requirements

  • To create SQL endpoints you must have cluster create permission in Databricks Workspace.
  • To manage a SQL endpoint you must have Can Manage permission in Databricks SQL Analytics for the endpoint.

SQL Endpoints API

In this section:

Create

Endpoint HTTP Method
2.0/sql/endpoints/ POST

Create a SQL endpoint.

Field Name Type Description
name STRING Name of the SQL endpoint. Must be unique. This field is required.
cluster_size String The size of the clusters allocated to the endpoint: "2X-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "2X-Large", "3X-Large", "4X-Large". For the mapping from cluster to instance size, see Cluster size. This field is required.
min_num_clusters INT32 Minimum number of clusters available when a SQL endpoint is running. The default is 1.
max_num_clusters INT32 Maximum number of clusters available when a SQL endpoint is running. This field is required. If multi-cluster load balancing is not enabled, this is limited to 1.
auto_stop_mins INT32 Time in minutes until an idle SQL endpoint terminates all clusters and stops. This field is optional. The default is 0, which means auto stop is disabled.
instance_profile_arn STRING Instance profile used to access storage from the SQL endpoint. This field is optional.
tags A set of EndpointTagPair An object containing a set of tags for endpoint resources. Databricks tags all endpoint resources with these tags. This field is optional.
spot_instance_policy EndpointSpotInstancePolicy The spot policy to use for allocating instances to clusters. This field is optional.
enable_photon BOOLEAN Whether to enable Photon. This field is optional.

Example request

{
  "name": "My SQL Endpoint",
  "cluster_size": "Medium",
  "min_num_clusters": 1,
  "max_num_clusters": 10,
  "instance_profile_arn": "arn:aws:iam::336924118301:role/Test",
  "spot_instance_policy":"COST_OPTIMIZED",
  "enable_photon": "true"
}

Example response

{
  "id": "0123456789abcdef"
}

Delete

Endpoint HTTP Method
2.0/sql/endpoints/{id} DELETE

Delete a SQL endpoint.

Edit

Endpoint HTTP Method
2.0/sql/endpoints/{id}/edit POST

Modify a SQL endpoint. All fields are optional. Missing fields default to the current values.

Field Name Type Description
id STRING ID of the SQL endpoint.
name STRING Name of the SQL endpoint.
cluster_size String The size of the clusters allocated to the endpoint: "2X-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "2X-Large", "3X-Large", "4X-Large". For the mapping from cluster to instance size, see Cluster size.
min_num_clusters INT32 Minimum number of clusters available when a SQL endpoint is running.
max_num_clusters INT32 Maximum number of clusters available when a SQL endpoint is running. This field is required. If multi-cluster load balancing is not enabled, limited to 1.
auto_stop_mins INT32 Time in minutes until an idle SQL endpoint terminates all clusters and stops.
instance_profile_arn STRING Instance profile used to access storage from the SQL endpoint.
tags A set of EndpointTagPair An object containing a set of tags for endpoint resources. Databricks tags all endpoint resources with these tags.
spot_instance_policy EndpointSpotInstancePolicy The spot policy to use for allocating instances to clusters.
enable_photon BOOLEAN Whether to enable Photon.

Example request

{
  "name": "My Edited SQL endpoint",
  "cluster_size": "Large",
  "auto_stop_mins": 60
}

Get

Endpoint HTTP Method
2.0/sql/endpoints/{id} GET

Retrieve the info for a SQL endpoint.

Field Name Type Description
id STRING SQL endpoint ID.
name STRING Name of the SQL endpoint.
cluster_size String The size of the clusters allocated to the endpoint: "2X-Small", "X-Small", "Small", "Medium", "Large", "X-Large", "2X-Large", "3X-Large", "4X-Large". For the mapping from cluster to instance size, see Cluster size.
auto_stop_mins INT32 Time until an idle SQL endpoint terminates all clusters and stops.
instance_profile_arn STRING Instance profile used to access storage from the SQL endpoint.
spot_instance_policy EndpointSpotInstancePolicy The spot policy to use for allocating instances to clusters.
num_clusters INT32 Number of clusters allocated to the endpoint.
min_num_clusters INT32 Minimum number of clusters available when a SQL endpoint is running.
max_num_clusters INT32 Maximum number of clusters available when a SQL endpoint is running.
num_active_sessions INT32 Number of active JDBC and ODBC sessions running on the SQL endpoint.
state EndpointState State of the SQL endpoint.
creator_name STRING Email address of the user that created the endpoint.
creator_id STRING Databricks ID of the user that created the endpoint.
jdbc_url STRING The URL used to submit SQL commands to the SQL endpoint using JDBC.
odbc_params EndpointSpotInstancePolicy The host, path, protocol, and port information required to submit SQL commands to the SQL endpoint using ODBC.
tags A set of EndpointTagPair An object containing a set of tags for endpoint resources. Databricks tags all endpoint resources with these tags.
health EndpointHealth The health of the endpoint.
enable_photon Boolean Whether Photon is enabled.

Example response

{
  "id": "123456790abcdef",
  "name": "My SQL endpoint",
  "cluster_size": "Medium",
  "min_num_clusters": 1,
  "max_num_clusters": 10,
  "auto_stop_mins": 30,
  "instance_profile_arn": "arn:aws:iam::336924118301:role/Test",
  "num_clusters": 5,
  "num_active_sessions": 30,
  "state": "RUNNING",
  "creator_name": "user@example.com",
  "jdbc_url":"jdbc:spark://<databricks-instance>:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/protocolv1/o/0123456790abcdef;",
  "odbc_params": {
    "host": "<databricks-instance>",
    "path": "/sql/protocolv1/o/0/123456790abcdef",
    "protocol": "https",
    "port": 443
  }
}

List

Endpoint HTTP Method
2.0/sql/endpoints/ GET

List all SQL endpoints in the workspace.

Example response

{
  "endpoints": [
    { "id": "123456790abcdef", "name": "My SQL endpoint", "cluster_size": "Medium", },
    { "id": "098765321fedcba", "name": "Another SQL endpoint", "cluster_size": "Large", }
  ]
}

Start

Endpoint HTTP Method
2.0/sql/endpoints/{id}/start POST

Start a SQL endpoint.

Stop

Endpoint HTTP Method
2.0/sql/endpoints/{id}/stop POST

Stop a SQL endpoint.

Configure SQL Endpoints API

You can configure the security policy, instance profile, and data access properties for all SQL endpoints.

In this section:

Get

Endpoint HTTP Method
/2.0/sql/config/endpoints GET

Get the configuration for all SQL endpoints.

Field Name Type Description
security_policy EndpointSecurityPolicy The policy for controlling access to datasets.
data_access_config EndpointConfPair An object containing a set of optional key-value pairs containing properties for an external Hive metastore.
instance_profile_arn STRING Instance profile used to access storage from SQL endpoints.

Example response

{
  "security_policy": "DATA_ACCESS_CONTROL",
  "data_access_config": {},
  "instance_profile_arn": "arn:aws:iam::336924118301:role/Test"
}

Edit

Edit the configuration for all SQL endpoints.

Important

  • All fields are required.
  • Invoking this method restarts all running SQL endpoints.
Endpoint HTTP Method
/2.0/sql/config/endpoints PUT
Field Name Type Description
security_policy EndpointSecurityPolicy The policy for controlling access to datasets.
data_access_config EndpointConfPair An object containing a set of optional key-value pairs containing properties for an external Hive metastore.
instance_profile_arn STRING Instance profile used to access storage from SQL endpoints.

Example request

{
  "data_access_config": {},
  "instance_profile_arn": "arn:aws:iam::336924118301:role/Test"
}

Data structures

EndpointConfPair

Field Name Type Description
key STRING Configuration key name.
value STRING Configuration key value.

EndpointHealth

Field Name Type Description
status EndpointStatus Endpoint status.
message STRING A descriptive message about the health status. Includes information about errors contributing to current health status.

EndpointSecurityPolicy

Option Description
DATA_ACCESS_CONTROL Use data access control to control access to datasets.
PASSTHROUGH Use credential passthrough to control access to datasets.

EndpointState

State of a SQL endpoint. The allowable state transitions are:

  • STARTING -> STARTING, RUNNING, STOPPING, DELETING
  • RUNNING -> STOPPING, DELETING
  • STOPPING -> STOPPED, STARTING
  • STOPPED -> STARTING, DELETING
  • DELETING -> DELETED
State Description
STARTING The endpoint is in the process of starting.
RUNNING The starting process is done and the endpoint is ready to use.
STOPPING The endpoint is in the process of being stopped.
STOPPED The endpoint is stopped. Start by calling start or by submitting a JDBC or ODBC request.
DELETING The endpoint is in the process of being destroyed.
DELETED The endpoint has been deleted and cannot be recovered.

EndpointStatus

State Description
HEALTHY Endpoint is functioning normally and there are no known issues.
DEGRADED Endpoint might be functional, but there are some known issues. Performance might be affected.
FAILED Endpoint is severely affected and will not be able to serve queries.

EndpointTagPair

Field Name Type Description
key STRING Tag key name.
value STRING Tag key value.

ODBCParams

Field Name Type Description
host STRING ODBC server hostname.
path STRING ODBC server path.
protocol STRING ODBC server protocol.
port INT32 ODBC server port

EndpointSpotInstancePolicy

Option Description
COST_OPTIMIZED Use an on-demand instance for the cluster driver and spot instances for cluster executors. The maximum spot price is 100% of the on-demand price. This is the default policy.
RELIABILITY_OPTIMIZED Use on-demand instances for all cluster nodes.