RetroSearch Browse

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-ddl-alter-table below:

ALTER TABLE | Databricks Documentation

ALTER TABLE

Applies to: Databricks SQL Databricks Runtime

Alters the schema or properties of a table.

For type changes or renaming columns in Delta Lake see rewrite the data.

To change the comment on a table or a column, you can also use COMMENT ON.

To alter a STREAMING TABLE, use ALTER STREAMING TABLE.

If the table is cached, the command clears cached data of the table and all its dependents that refer to it. The cache will be lazily filled when the table or the dependents are accessed the next time.

note

When you add a column to an existing Delta table, you cannot define a DEFAULT value. All columns added to Delta tables are treated as NULL for existing rows. After adding a column, you can optionally define a default value for the column, but this is only applied for new rows inserted into the table. Use the following syntax:

SQL

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_expression

On foreign tables, you can perform only ALTER TABLE SET OWNER and ALTER TABLE RENAME TO.

Required permissionsâ

If you use Unity Catalog you must have MODIFY permission to:

ALTER COLUMN
ADD COLUMN
DROP COLUMN
SET TBLPROPERTIES
UNSET TBLPROPERTIES
modify PREDICTIVE OPTIMIZATION

If you use Unity Catalog you must have MANAGE permission or ownership to:

SET OWNER TO

All other operations require ownership of the table.

Syntaxâ

ALTER TABLE table_name
    { RENAME TO clause |
      ADD COLUMN clause |
      ALTER COLUMN clause |
      DROP COLUMN clause |
      RENAME COLUMN clause |
      DEFAULT COLLATION clause |
      ADD CONSTRAINT clause |
      DROP CONSTRAINT clause |
      DROP FEATURE clause |
      ADD PARTITION clause |
      DROP PARTITION clause |
      PARTITION SET LOCATION clause |
      RENAME PARTITION clause |
      RECOVER PARTITIONS clause |
      SET { ROW FILTER clause } |
      DROP ROW FILTER |
      SET TBLPROPERTIES clause |
      UNSET TBLPROPERTIES clause |
      SET SERDE clause |
      SET LOCATION clause |
      SET OWNER TO clause |
      SET SERDE clause |
      SET TAGS clause |
      UNSET TAGS clause |
      CLUSTER BY clause |
      PREDICTIVE OPTIMIZATION clause}

Parametersâ

table_name

Identifies the table being altered. The name must not include a temporal specification or options specification. If the table cannot be found Databricks raises a TABLE_OR_VIEW_NOT_FOUND error.
RENAME TO to_table_name

Renames the table.

note

If you use AWS Glue Data Catalog as the metastore, RENAME is not supported.
- to_table_name
  
  Identifies the new table name. The name must not include a temporal specification or options specification.
  
  For Unity Catalog tables, the to_table_name must be within the same catalog as table_name. For other tables, the to_table_name must be within the same schema as table_name.
  
  If to_table_name is unqualified it is implicitly qualified with the current schema.
ADD COLUMN

Adds one or more columns to the table.

ALTER COLUMN

Changes a property or the location of a column.
DROP COLUMN

Drop one or more columns or fields in a Delta Lake table.
RENAME COLUMN

Renames a column or field in a Delta Lake table.

ADD CONSTRAINT

Adds a check constraint, informational foreign key constraint, or informational primary key constraint to the table.

Foreign keys and primary keys are supported only for tables in Unity Catalog, not the hive_metastore catalog.
DEFAULT COLLATION collation_name

Applies to: Databricks SQL Databricks Runtime 16.3 and above

Changes the default collation of the table for new STRING columns. Existing columns are not affected by this clause. To change the collation of an existing column, use ALTER TABLE ... ALTER COLUMN ... COLLATE collation_name.

DROP CONSTRAINT

Drops a primary key, foreign key, or check constraint from the table.
DROP FEATURE feature_name [ TRUNCATE HISTORY ]

Applies to: Databricks Runtime 14.3 LTS and above

Legacy support for DROP FEATURE is available starting in Databricks Runtime 14.3 LTS. For documentation of the legacy functionality, see Drop Delta table features (legacy).

Applies to: Databricks SQL Databricks Runtime 16.3 and above
Databricks recommends using Databricks Runtime 16.3 and above for all DROP FEATURE commands, which replaces the legacy behavior.

Removes a feature from a Delta Lake table.

Removing a feature may result in the addition of the checkpointProtection writer feature in the table protocol. For more information, see Drop Delta table features and Table features for protocol compatibility.
- feature_name
  
  The name of a feature in form of a STRING literal or identifier, that must be understood by Databricks and be supported on the table.
  
  If the feature is not present in the table Databricks raises DELTA_FEATURE_DROP_FEATURE_NOT_PRESENT.
- TRUNCATE HISTORY
  
  Removal of features by truncating history. This requires a two stage process:

The removal of features by truncating history requires a two-step process:

The first invocation clears traces of the feature and informs you of partial success.
Then, wait until the retention period ends before re-executing the statement to complete the removal.

If you initiate the second invocation too early, Databricks raises DELTA_FEATURE_DROP_WAIT_FOR_RETENTION_PERIOD or DELTA_FEATURE_DROP_HISTORICAL_VERSIONS_EXIST.

Truncating the table history limits your ability to perform DESCRIBE HISTORY and execute time travel queries.
ADD PARTITION

Adds one or more partitions to the table.
DROP PARTITION

Drops one or more partitions from the table.
PARTITION â¦ SET LOCATION

Sets the location of a partition.
RENAME PARTITION

Replaces the keys of a partition.
RECOVER PARTITIONS

Instructs Databricks to scan the table's location and add any files to the table which have been added directly to the filesystem.
SET ROW FILTER clause

Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above Unity Catalog only

Adds a row filter function to the table. All subsequent queries to the table receive a subset of the rows where the function evaluates to boolean TRUE. This can be useful for fine-grained access control purposes where the function can inspect the identity or group memberships of the invoking user to determine whether to filter certain rows.
DROP ROW FILTER

Applies to: Unity Catalog only

Drops the row filter from the table, if any. Future queries will return all rows from the table without any automatic filtering.
SET TBLPROPERTIES

Sets or resets one or more user defined properties.
UNSET TBLPROPERTIES

Removes one or more user defined properties.
SET LOCATION

Moves the location of a table.
- LOCATION path
  
  path must be a STRING literal. Specifies the new location for the table.
  
  Files in the original location will not be moved to the new location.
[ SET ] OWNER TO principal

Transfers ownership of the table to principal.

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above

SET is allowed as an optional keyword.
SET TAGS ( { tag_name = tag_value } [, ...] )

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above

Apply tags to the table. You need to have APPLY TAG permission to add tags to the table.
- tag_name
  
  A literal STRING. The tag_name must be unique within the table or column.
- tag_value
  
  A literal STRING.
UNSET TAGS ( tag_name [, ...] )

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above

Remove tags from the table. You need to have APPLY TAG permission to remove tags from the table.
- tag_name
  
  A literal STRING. The tag_name must be unique within the table or column.
CLUSTER BY clause

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above

Adds, changes, or drops the clustering strategy for a Delta Lake table.
{ ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION

Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above Unity Catalog only

Alters the managed Delta Lake table to the desired predictive optimization setting.

By default, when tables are created, the behavior is to INHERIT from the schema.

When predictive optimization is explicitly enabled or inherited as enabled OPTIMIZE and VACUUM will be automatically invoked on the table as deemed appropriate by Databricks. For more details see: Predictive optimization for Unity Catalog managed tables.

Examplesâ

For Delta Lake add constraints and alter column examples, see

SQL


> DESCRIBE student;
                col_name data_type comment
 
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 
              
                     age       int    NULL

> ALTER TABLE Student RENAME TO StudentInfo;


> DESCRIBE StudentInfo;
                col_name data_type comment
 
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 
              
                     age       int    NULL


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=10
    age=11
    age=12

> ALTER TABLE default.StudentInfo PARTITION (age='10') RENAME TO PARTITION (age='15');


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15


> DESCRIBE StudentInfo;
                col_name data_type comment
 
                    name    string    NULL
                  rollno       int    NULL
                     age       int    NULL
 
              
                     age       int    NULL

> ALTER TABLE StudentInfo ADD columns (LastName string, DOB timestamp);


> DESCRIBE StudentInfo;
                col_name data_type comment
 
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL
 
              
                     age       int    NULL


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15

> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18);


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15
    age=18


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15
    age=18

> ALTER TABLE StudentInfo DROP IF EXISTS PARTITION (age=18);


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15

> ALTER TABLE StudentInfo ADD IF NOT EXISTS PARTITION (age=18) PARTITION (age=20);


> SHOW PARTITIONS StudentInfo;
 partition
 
    age=11
    age=12
    age=15
    age=18
    age=20


> DESCRIBE StudentInfo;
                col_name data_type comment
+
                    name    string    NULL
                  rollno       int    NULL
                LastName    string    NULL
                     DOB timestamp    NULL
                     age       int    NULL
 
              
                     age       int    NULL

ALTER TABLE StudentInfo ALTER COLUMN name COMMENT "new comment";


> DESCRIBE StudentInfo;
                col_name data_type     comment
 
                    name    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL
 
              
                     age       int        NULL


> ALTER TABLE StudentInfo RENAME COLUMN name TO FirstName;


> DESCRIBE StudentInfo;
                col_name data_type     comment
 
               FirstName    string new comment
                  rollno       int        NULL
                LastName    string        NULL
                     DOB timestamp        NULL
                     age       int        NULL
 
              
                     age       int        NULL


> ALTER TABLE dbx.tab1 PARTITION (a='1', b='2') SET LOCATION '/path/to/part/ways';


> ALTER TABLE test_tab SET SERDE 'org.apache.hadoop.hive.serde2.columnar.LazyBinaryColumnarSerDe';

> ALTER TABLE dbx.tab1 SET SERDE 'org.apache.hadoop' WITH SERDEPROPERTIES ('k' = 'v', 'kay' = 'vee');


> ALTER TABLE dbx.tab1 SET TBLPROPERTIES ('winner' = 'loser');


> ALTER TABLE dbx.tab1 UNSET TBLPROPERTIES ('winner');


> ALTER TABLE my_table DROP FEATURE deletionVectors;


> ALTER TABLE my_table DROP FEATURE deletionVectors TRUNCATE HISTORY;


> ALTER TABLE test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');


> ALTER TABLE test UNSET TAGS ('tag1', 'tag2', 'tag3');


> ALTER TABLE main.schema1.test ALTER COLUMN col1 SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');


> ALTER TABLE main.schema1.test ALTER COLUMN col1 UNSET TAGS ('tag1', 'tag2', 'tag3');


> ALTER TABLE my_table ENABLE PREDICTIVE OPTIMIZATION;



> CREATE TABLE my_table (num INT, str STRING, bool BOOLEAN) TBLPROPERTIES('delta.feature.allowColumnDefaults' = 'supported')
> DESCRIBE TABLE my_table;
  col_name    data_type     comment
  
       num          int        null
       str       string        null
       bool      boolean       null


> ALTER TABLE table ALTER COLUMN
   num COMMENT 'number column',
   str COMMENT 'string column';

> DESCRIBE TABLE my_table;
  col_name    data_type      comment
  
       num          int   number column
       str       string   string column
      bool      boolean            null


> ALTER TABLE table ALTER COLUMN
   bool COMMENT 'boolean column',
   num AFTER bool,
   str AFTER num,
   bool SET DEFAULT true;

> DESCRIBE TABLE my_table;
  col_name    data_type      comment
  
      bool      boolean   boolean column
       num          int    number column
       str       string    string column