ALTER TABLE
The ALTER TABLE
command modifies the definition of a table.
Syntax
alteroption
depends on the operation you want to perform on the table. For all supported clauses, see the sections below.
Clauses
ADD COLUMN
Parameter or clause | Description |
---|---|
ADD [ COLUMN ] | This clause adds a new column to the table. COLUMN is optional. |
column_name | Specify the name of the column you want to add. |
data_type | The data type of the new column. |
DEFAULT | The DEFAULT clause allows you to assign a default value to a column. This default value is used when a new row is inserted, and no explicit value is provided for that column. |
default_expr | default_expr is any constant value or variable-free expression that does not reference other columns in the current table or involve subqueries. The data type of default_expr must match the data type of the column.If default_expr is impure, such as using a function like now(), all historical data will be filled with the result of the expression evaluated at the time the statement was executed. For future insertions, the default expression will be evaluated at the time of each respective insertion. |
NOTE
- If your table is defined with a schema registry, its columns can not be altered.
- Columns added by this command cannot be used by any existing materialized views or indexes. You must create new materialized views or indexes to reference it.
DROP COLUMN
Parameter or clause | Description |
---|---|
DROP [ COLUMN ] | This clause drops an existing column from a table. COLUMN is optional. |
IF EXISTS | Do not return an error if the specified column does not exist. A notice is issued instead. |
column_name | Specify the column you want to remove. |
NOTE
- If your table is defined with a schema registry, you can only change the table schema by
ALTER TABLE t REFRESH SCHEMA
. One exception is you can drop the generated columns even if the schema is defined with a schema registry. Note that dropping these generated columns will trigger a schema refresh. - You cannot drop columns referenced by materialized views or indexes.
- To drop a column referenced by a generated column, you must first drop the generated column.
OWNER TO
Parameter or clause | Description |
---|---|
OWNER TO | This clause changes the owner of the table to the specified user. It will cascadingly change all related internal objects as well, and the associated indexes will be changed too. |
new_user | Specify the user you want to assign to the table. |
After setting, you can observe the parallelism status within the internal rw_table_fragments
table.
SET SCHEMA
Parameter or clause | Description |
---|---|
SET SCHEMA | This clause moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well. |
schema_name | Specify the schema to which the table will be moved. |
SET PARALLELISM
Parameter or clause | Description |
---|---|
SET PARALLELISM | This clause controls the degree of parallelism for the targeted streaming job. |
parallelism_number | This parameter can be ADAPTIVE or a fixed number, like 1, 2, 3, etc. Altering the parameter to ADAPTIVE will expand the streaming job’s degree of parallelism to encompass all available units, whereas setting it to a fixed number will lock the job’s parallelism at that specific figure. Setting it to 0 is equivalent to ADAPTIVE. After setting the parallelism, the parallelism status of a table can be observed within the internal rw_table_fragments table or the rw_fragmentstable. |
Here is a more detailed example for you to practise this clause:
First, let’s set the parallelism to 3
by the SET command.
Then let’s create a table to view the parallelism we set. As mentioned, the parallelism status of a table can be observed within the rw_fragments table.
Now we can use SET PARALLELISM
to change the parallelism and view the change:
RENAME TO
Parameter or clause | Description |
---|---|
RENAME TO | This clause changes the name of the table. |
new_name | The new name of the table. |
REFRESH SCHEMA
This command alters the schema registry of a table created with connectors.
NOTE
If a downstream fragment references a column that is either missing or has undergone a type change in the updated schema, the command will be declined.
SET SOURCE_RATE_LIMIT
Use this statement to modify the rate limit of tables that have a source. For the specific value of SOURCE_RATE_LIMIT
, refer to How to view runtime parameters.
SET BACKFILL_RATE_LIMIT
[]
Use this statement to modify the backfill rate limit of a CDC table being created from a CDC source. For the specific value of BACKFILL_RATE_LIMIT
, refer to How to view runtime parameters.
Was this page helpful?