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.

To configure individual SQL endpoints, use the SQL Endpoints API. To configure all SQL endpoints, use the Global SQL Endpoints API.

SQL Endpoints API

Use this API to create, edit, list, and get SQL endpoints.

In this section:

Create
Delete
Edit
Get
List
Start
Stop

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	EndpointTags	Key-value pairs that describe the endpoint. 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 queries are executed on a native vectorized engine that speeds up query execution. 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",
  "tags": {
    "custom_tags": [
      {
        "key": "mykey",
        "value": "myvalue"
      }
    ]
  },
  "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	EndpointTags	Key-value pairs that describe the endpoint.
spot_instance_policy	EndpointSpotInstancePolicy	The spot policy to use for allocating instances to clusters.
enable_photon	`BOOLEAN`	Whether queries are executed on a native vectorized engine that speeds up query execution.

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	ODBCParams	The host, path, protocol, and port information required to submit SQL commands to the SQL endpoint using ODBC.
tags	EndpointTags	Key-value pairs that describe the endpoint.
health	EndpointHealth	The health of the endpoint.
enable_photon	`BOOLEAN`	Whether queries are executed on a native vectorized engine that speeds up query execution.

Example response

{
  "id": "7f2629a529869126",
  "name": "MyEndpoint",
  "size": "SMALL",
  "min_num_clusters": 1,
  "max_num_clusters": 1,
  "auto_stop_mins": 0,
  "auto_resume": true,
  "num_clusters": 0,
  "num_active_sessions": 0,
  "state": "STOPPED",
  "creator_name": "user@example.com",
  "jdbc_url": "jdbc:spark://hostname.staging.cloud.databricks.com:443/default;transportMode=http;ssl=1;AuthMech=3;httpPath=/sql/1.0/endpoints/7f2629a529869126;",
  "odbc_params": {
    "hostname": "hostname.cloud.databricks.com",
    "path": "/sql/1.0/endpoints/7f2629a529869126",
    "protocol": "https",
    "port": 443
  },
  "tags": {
    "custom_tags": [
      {
        "key": "mykey",
        "value": "myvalue"
      }
    ]
  },
  "spot_instance_policy": "COST_OPTIMIZED",
  "enable_photon": true,
  "cluster_size": "Small"
}

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.

Global SQL Endpoints API

Use this API to configure the security policy, instance profile, and data access properties for all SQL endpoints.

In this section:

Get
Edit

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	Array of EndpointConfPair	An array of 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": [
    {
      "key": "spark.sql.hive.metastore.jars",
      "value": "/databricks/hive_metastore_jars/*"
    }
  ],
  "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	Array of EndpointConfPair	An array of 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": [
    {
      "key": "spark.sql.hive.metastore.jars",
      "value": "/databricks/hive_metastore_jars/*"
    }
  ],
  "instance_profile_arn": "arn:aws:iam::336924118301:role/Test"
}

Data structures

In this section:

EndpointConfPair
EndpointHealth
EndpointSecurityPolicy
EndpointSpotInstancePolicy
EndpointState
EndpointStatus
EndpointTags
EndpointTagPair
ODBCParams

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.

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.

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.

EndpointTags

Field Name	Type	Description
custom_tags	Array of EndpointTagPair	An object containing an array of key-value pairs.

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