ALTER EXTERNAL LOCATION

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.3 and above check marked yes Unity Catalog only

Alters properties of an external location or renames the location.

Syntax 

ALTER EXTERNAL LOCATION location_name
   { RENAME TO to_location_name |
     SET URL url [ FORCE ] |
     SET STORAGE CREDENTIAL credential_name |
     [ SET ] OWNER TO principal  |
     SET ACCESS POINT access_point }

Parameters

  • location_name

    Identifies the external location being altered.

  • RENAME TO to_location_name

    Renames the location. The name must be unique among all locations in the metastore.

  • SET URL url [ FORCE ]

    url must be a STRING literal with the location of the cloud storage described as an absolute URL.

    Unless you specify FORCE the statement will fail if the location is currently in use.

  • SET STORAGE CREDENTIAL credential_name

    Updates the named credential used to access this location. If the credential does not exist Databricks SQL raises an error.

  • [ SET ] OWNER TO principal

    Transfers ownership of the storage location to principal.

    Applies to: check marked yes Databricks SQL SQL warehouse version 2022.35 or higher check marked yes Databricks Runtime 11.2 and above

    SET is allowed as an optional keyword.

  • access_point

    check marked yes Databricks SQL check marked yes Databricks Runtime 12.1 and above

    access_point must be a STRING literal with the Amazon S3 access point described in Amazon Resource Name (ARN) format and used to access the location of the cloud storage.

Examples 

-- Rename a location
> ALTER EXTERNAL LOCATION descend_loc RENAME TO decent_loc;

-- Redirect the URL associated with the location
> ALTER EXTERNAL LOCATION best_loc SET `s3://us-east-1-prod/best_location` FORCE;

-- Change the credentials used to access the location
> ALTER EXTERNAL LOCATION best_loc SET STORAGE CREDENTIAL street_cred;

-- Change ownership of the external location
> ALTER EXTERNAL LOCATION best_loc OWNER TO `alf@melmak.et`

-- Change ARN of the external location
> ALTER EXTERNAL LOCATION best_loc
    SET ACCESS POINT 'arn:aws:s3:us-east-1:123456789:accesspoint/remoteaccesspoint';