/alter/table

URL: http://<db.host>:<db.port>/alter/table

Apply various modifications to a table or view. The available modifications include the following:

Manage a table's columns--a column can be added, removed, or have its type and properties modified, including whether it is dictionary encoded or not.

External tables cannot be modified except for their refresh method.

Create or delete a column, chunk skip, or geospatial index. This can speed up certain operations when using expressions containing equality or relational operators on indexed columns. This only applies to tables.

Create or delete a foreign key on a particular column.

Manage a range-partitioned or a manual list-partitioned table's partitions.

Set (or reset) the tier strategy of a table or view.

Refresh and manage the refresh mode of a materialized view or an external table.

Set the time-to-live (TTL). This can be applied to tables or views.

Set the global access mode (i.e. locking) for a table. This setting trumps any role-based access controls that may be in place; e.g., a user with write access to a table marked read-only will not be able to insert records into it. The mode can be set to read-only, write-only, read/write, and no access.

Input Parameter Description

NameTypeDescription
table_namestringTable on which the operation will be performed, in [schema_name.]table_name format, using standard name resolution rules. Must be an existing table or view.
actionstring

Modification operation to be applied

Supported ValuesDescription
allow_homogeneous_tablesNo longer supported; action will be ignored.
create_indexCreates a column (attribute) index, chunk skip index, or geospatial index (depending on the specified index_type), on the column name specified in input parameter value. If this column already has the specified index, an error will be returned.
delete_indexDeletes a column (attribute) index, chunk skip index, or geospatial index (depending on the specified index_type), on the column name specified in input parameter value. If this column does not have the specified index, an error will be returned.
move_to_collection[DEPRECATED--please use move_to_schema and use /create/schema to create the schema if non-existent] Moves a table or view into a schema named input parameter value. If the schema provided is non-existent, it will be automatically created.
move_to_schemaMoves a table or view into a schema named input parameter value. If the schema provided is nonexistent, an error will be thrown. If input parameter value is empty, then the table or view will be placed in the user's default schema.
protectedNo longer used. Previously set whether the given input parameter table_name should be protected or not. The input parameter value would have been either 'true' or 'false'.
rename_tableRenames a table or view within its current schema to input parameter value. Has the same naming restrictions as tables.
ttlSets the time-to-live in minutes of the table or view specified in input parameter table_name.
add_columnAdds the column specified in input parameter value to the table specified in input parameter table_name. Use column_type and column_properties in input parameter options to set the column's type and properties, respectively.
change_columnChanges type and properties of the column specified in input parameter value. Use column_type and column_properties in input parameter options to set the column's type and properties, respectively. Note that primary key and/or shard key columns cannot be changed. All unchanging column properties must be listed for the change to take place, e.g., to add dictionary encoding to an existing 'char4' column, both 'char4' and 'dict' must be specified in the input parameter options map.
set_column_compressionNo longer supported; action will be ignored.
delete_columnDeletes the column specified in input parameter value from the table specified in input parameter table_name.
create_foreign_keyCreates a foreign key specified in input parameter value using the format '(source_column_name [, ...]) references target_table_name(primary_key_column_name [, ...]) [as foreign_key_name]'.
delete_foreign_keyDeletes a foreign key. The input parameter value should be the foreign_key_name specified when creating the key or the complete string used to define it.
add_partitionAdds the partition specified in input parameter value, to either a range-partitioned or manual list-partitioned table.
remove_partitionRemoves the partition specified in input parameter value (and relocates all of its data to the default partition) from either a range-partitioned or manual list-partitioned table.
delete_partitionDeletes the partition specified in input parameter value (and all of its data) from either a range-partitioned or manual list-partitioned table.
set_global_access_modeSets the global access mode (i.e. locking) for the table specified in input parameter table_name. Specify the access mode in input parameter value. Valid modes are 'no_access', 'read_only', 'write_only' and 'read_write'.
refreshFor a materialized view, replays all the table creation commands required to create the view. For an external table, reloads all data in the table from its associated source files or data source.
set_refresh_methodFor a materialized view, sets the method by which the view is refreshed to the method specified in input parameter value - one of 'manual', 'periodic', or 'on_change'. For an external table, sets the method by which the table is refreshed to the method specified in input parameter value - either 'manual' or 'on_start'.
set_refresh_start_timeSets the time to start periodic refreshes of this materialized view to the datetime string specified in input parameter value with format 'YYYY-MM-DD HH:MM:SS'. Subsequent refreshes occur at the specified time + N * the refresh period.
set_refresh_stop_timeSets the time to stop periodic refreshes of this materialized view to the datetime string specified in input parameter value with format 'YYYY-MM-DD HH:MM:SS'.
set_refresh_periodSets the time interval in seconds at which to refresh this materialized view to the value specified in input parameter value. Also, sets the refresh method to periodic if not already set.
set_refresh_spanSets the future time-offset(in seconds) for the view refresh to stop.
set_refresh_execute_asSets the user name to refresh this materialized view to the value specified in input parameter value.
remove_text_search_attributesRemoves text search attribute from all columns.
remove_shard_keysRemoves the shard key property from all columns, so that the table will be considered randomly sharded. The data is not moved. The input parameter value is ignored.
set_strategy_definitionSets the tier strategy for the table and its columns to the one specified in input parameter value, replacing the existing tier strategy in its entirety.
cancel_datasource_subscriptionPermanently unsubscribe a data source that is loading continuously as a stream. The data source can be Kafka / S3 / Azure.
pause_datasource_subscriptionTemporarily unsubscribe a data source that is loading continuously as a stream. The data source can be Kafka / S3 / Azure.
resume_datasource_subscriptionResubscribe to a paused data source subscription. The data source can be Kafka / S3 / Azure.
change_ownerChange the owner resource group of the table.
valuestringThe value of the modification, depending on input parameter action. For example, if input parameter action is add_column, this would be the column name; while the column's definition would be covered by the column_type, column_properties, column_default_value, and add_column_expression in input parameter options. If input parameter action is ttl, it would be the number of minutes for the new TTL. If input parameter action is refresh, this field would be blank.
optionsmap of string to strings

Optional parameters. The default value is an empty map ( {} ).

Supported Parameters (keys)Parameter Description
action 
column_name 
table_name 
column_default_valueWhen adding a column, set a default value for existing records. For nullable columns, the default value will be null, regardless of data type.
column_propertiesWhen adding or changing a column, set the column properties (strings, separated by a comma: data, store_only, text_search, char8, int8 etc).
column_typeWhen adding or changing a column, set the column type (strings, separated by a comma: int, double, string, null etc).
compression_type

No longer supported; option will be ignored. The default value is snappy. The supported values are:

  • none
  • snappy
  • lz4
  • lz4hc
copy_values_from_column[DEPRECATED--please use add_column_expression instead.]
rename_columnWhen changing a column, specify new column name.
validate_change_column

When changing a column, validate the change before applying it (or not). The default value is true.

Supported ValuesDescription
trueValidate all values. A value too large (or too long) for the new type will prevent any change.
falseWhen a value is too large or long, it will be truncated.
update_last_access_time

Indicates whether the time-to-live (TTL) expiration countdown timer should be reset to the table's TTL. The default value is true.

Supported ValuesDescription
trueReset the expiration countdown timer to the table's configured TTL.
falseDon't reset the timer; expiration countdown will continue from where it is, as if the table had not been accessed.
add_column_expressionWhen adding a column, an optional expression to use for the new column's values. Any valid expression may be used, including one containing references to existing columns in the same table.
strategy_definitionOptional parameter for specifying the tier strategy for the table and its columns when input parameter action is set_strategy_definition, replacing the existing tier strategy in its entirety.
index_type

Type of index to create, when input parameter action is create_index, or to delete, when input parameter action is delete_index. The default value is column.

Supported ValuesDescription
columnCreate or delete a column (attribute) index.
chunk_skipCreate or delete a chunk skip index.
geospatialCreate or delete a geospatial index

Output Parameter Description

The GPUdb server embeds the endpoint response inside a standard response structure which contains status information and the actual response to the query. Here is a description of the various fields of the wrapper:

NameTypeDescription
statusString'OK' or 'ERROR'
messageStringEmpty if success or an error message
data_typeString'alter_table_response' or 'none' in case of an error
dataStringEmpty string
data_strJSON or String

This embedded JSON represents the result of the /alter/table endpoint:

NameTypeDescription
table_namestringTable on which the operation was performed.
actionstringModification operation that was performed.
valuestringThe value of the modification that was performed.
type_idstringreturn the type_id (when changing a table, a new type may be created)
type_definitionstringreturn the type_definition (when changing a table, a new type may be created)
propertiesmap of string to arrays of stringsreturn the type properties (when changing a table, a new type may be created)
labelstringreturn the type label (when changing a table, a new type may be created)
infomap of string to stringsAdditional information.

Empty string in case of an error.