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, which is enabled in the Data Science & Engineering workspace.
  • To manage a SQL endpoint you must have Can Manage permission in Databricks SQL 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

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.
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. The default is true.

Example request

{
  "name": "My SQL Endpoint",
  "cluster_size": "Medium",
  "min_num_clusters": 1,
  "max_num_clusters": 10,
  "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.
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.
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

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

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