ALTER TABLE

Update a table’s definition or schema.

{{< codeheader "Syntax" expand >}}

-- Add Columns
ALTER TABLE <table_name>
  ADD COLUMNS ( <column_name1> <data_type>, <column_name2> <data_type>, ... )

-- Drop Columns
ALTER TABLE <table_name>
  DROP COLUMN <column_name>

-- Modify Columns
ALTER TABLE <table_name>
  { ALTER | MODIFY } COLUMN <old_column_name> <new_column_name> <data_type>

-- Refresh Metadata
ALTER TABLE <table_name>
  REFRESH METADATA
    [ FOR PARTITIONS ( <partition_name> = '<value>') ]
    [ { AVOID | AUTO } PROMOTION ]
    [ { FORCE | LAZY } UPDATE ]
    [ { MAINTAIN | DELETE } WHEN MISSING ]

-- Forget Metadata
ALTER TABLE <table_name>
  FORGET METADATA

-- Create Aggregate Reflections
ALTER TABLE <table_name>
  CREATE AGGREGATE REFLECTION <reflection_name> USING DIMENSIONS ( <column_name1>, <column_name2>, ... ) MEASURES ( <column_name1> ( <aggregation_type>, <column_name2> <aggregation_type> , ... ) )
    [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
    [ LOCALSORT BY ( <column_name1>, <column_name2>, ... ) ]
    [ ARROW CACHE ]

-- Create External Reflections
ALTER TABLE <table_name>
  CREATE EXTERNAL REFLECTION <reflection_name> USING <table_name>

-- Create Raw Reflections
ALTER TABLE <table_name>
  CREATE RAW REFLECTION <reflection_name> USING DISPLAY ( <column_name1>, <column_name2>, ...)
    [ PARTITION BY ( <column_name1>, <column_name2>, ... ) ]
    [ LOCALSORT BY ( <column_name1>, <column_name2>, ... ) ]
    [ ARROW CACHE ]

-- Route Reflections
ALTER TABLE <table_name>
  ROUTE REFLECTIONS TO { DEFAULT ENGINE | ENGINE { <engine_name> | <engine_uuid> } }

-- Alter Apache Iceberg Tables
ALTER TABLE <table_name>
  {
  { ALTER | MODIFY | CHANGE } COLUMN ( <source_col_name> <new_col_name> <data_type> )
  | ADD { COLUMN | COLUMNS } (<column_name1> <data_type> [, <column_name2> <data_type> ... ] ) [ BEFORE <column_name> ]
  | DROP COLUMN <column_name>
  | { ADD | DROP } PARTITION FIELD { <column_name> | <partition_transform> }
  | REFRESH METADATA
  }

-- Set a Column-Masking Policy
ALTER TABLE [ IF EXISTS ] <table_name>
  MODIFY COLUMN <column_name>
  SET MASKING POLICY <function_name> ( <column_name> [, ... ] )

-- Unset a Column-Masking Policy
ALTER TABLE [ IF EXISTS ] <table_name>
  MODIFY COLUMN <column_name>
  UNSET MASKING POLICY <function_name>

-- Set or Unset a Row-Access Policy
ALTER TABLE [ IF EXISTS ] <table_name>
  MODIFY COLUMN <column_name>
  { ADD | DROP } ROW ACCESS POLICY <function_name> ( <column_name> [, ... ] )

Parameters

{{< sql-section file="data/sql/tables.json" data="alteringATable" >}}

{{< sql-section file="data/sql/tables.json" data="alteringATableOptional" >}}

{{< sql-section file="data/sql/tables.json" data="refreshOptions" >}}

{{< sql-section file="data/sql/reflections.json" data="creatingAggReflectionsParameters" >}}

{{< sql-section file="data/sql/reflections.json" data="creatingRawReflectionsParameters" >}}

{{< sql-section file="data/sql/engine-routing-sql.json" data="routingReflectionRefreshJobs" >}}

{{< sql-section file="data/sql/apache-iceberg-tables.json" data="everythingAlter" >}}

{{< sql-section file="data/sql/apache-iceberg-tables.json" data="renamingColumns" >}}

{{< sql-section file="data/sql/row-column-policies.json" data="maskingExistingParameters" >}}

Examples

Add a column

ALTER TABLE services
  ADD COLUMNS (county varchar)

Modify a column

ALTER TABLE services
  MODIFY COLUMN tip_amount tip_amount DECIMAL

Rename a column

ALTER TABLE services
  MODIFY COLUMN tip_amount gratuity_amount DECIMAL

Refresh all the metadata for a table

ALTER TABLE services
  REFRESH METADATA

Refresh all the metadata for a table using optional clauses

ALTER TABLE services
  REFRESH METADATA
    AUTO PROMOTION
    LAZY UPDATE
    MAINTAIN WHEN MISSING

Refresh the metadata for a single partition

ALTER TABLE Samples."samples.dremio.com"."zips.json"  
  REFRESH METADATA FOR PARTITIONS (state = 'TX')

Refresh the metadata for a single partition using optional clauses

ALTER TABLE Samples."samples.dremio.com"."zips.json"  
  REFRESH METADATA FOR PARTITIONS (state = 'TX')
    AUTO PROMOTION
    LAZY UPDATE
    MAINTAIN WHEN MISSING

Forget the metadata for a table

ALTER TABLE Samples."samples.dremio.com"."zips.json"
  FORGET METADATA

Create a raw reflection that sorts customers by last name and partitions them by country

ALTER TABLE "@user1"."customers"
  CREATE RAW REFLECTION customers_by_country USING DISPLAY (id,lastName,firstName,address,country)
    PARTITION BY (country)
    LOCALSORT BY (lastName)

Create an aggregate reflection that counts the cities per state and sorts by state

ALTER TABLE Samples."samples.dremio.com"."zips.json"
  CREATE AGGREGATE REFLECTION per_state
  USING
  DIMENSIONS (state)
  MEASURES (city (COUNT))
  LOCALSORT BY (state)

Routing Reflections

ALTER TABLE "Table 1" ROUTE REFLECTIONS TO ENGINE "Engine 1"
ALTER TABLE "View 1" ROUTE REFLECTIONS TO ENGINE "Engine 1"
ALTER TABLE "View 3" ROUTE REFLECTIONS TO ENGINE "Engine 1"

Adding a column for an Apache Iceberg Table

ALTER TABLE myTable ADD COLUMN (address VARCHAR)

Changing the data type of a column to BIGINT for an Apache Iceberg Table

ALTER TABLE myTable ALTER COLUMN id id BIGINT

Set a column-masking policy that takes multiple columns

ALTER TABLE e.employees
    MODIFY COLUMN ssn_col
    SET MASKING POLICY protect_ssn (ssn_col, region)

Unset a column-masking policy

ALTER TABLE e.employees
    MODIFY COLUMN ssn_col
    UNSET MASKING POLICY protect_ssn

Add a row-access policy to a table

ALTER TABLE e.employees
  ADD ROW ACCESS POLICY state_policy ( state_col )

Remove a row-access policy from a table

ALTER TABLE employees
  MODIFY COLUMN ssn_col
  DROP ROW ACCESS POLICY protect_ssn (ssn_col, region)