ALTER EXTERNAL TABLE
Modify the names, columns and constraints of an existing external table.
Syntax
ALTER EXTERNAL TABLE [schema_name.]table_name
{
[NULL DEFINED AS ('<string>' [,'<string>'...])]
[DATETIME FORMAT <ISO 8601 date format string>]
[TIMEZONE=<timezone>]
[DROP COLUMN column_name]
[ADD COLUMN column_name data_type]
[RENAME TO new_table_name]
[LOCATION <location_spec>]
[CHANGE (column_name <column_spec> [,column_name <column_spec>...n])]
[<file_format_spec>]
[RENAME COLUMN old_name to new_name]
[<rejects_handling_spec>]
[<rejects_thresholds_spec>]
}
<column_spec> ::=
{
column name
column_type
[<values_format_spec>]
}
<values_format_spec> ::=
{
[NULL DEFINED AS ('<string>' [,'<string>'...])]
[DATETIME FORMAT <ISO 8601 date format string>]
[TIMEZONE=<timezone>]
}
<file_format_spec> ::= -- Relevant only for 'files' type of external data source
{
[ROW FORMAT DELIMITED
[FIELDS TERMINATED BY '<char>']
[QUOTED BY '<char>' | NONE]
[COMMENTED BY '<char>' | NONE]
[LINES TERMINATED BY '<string>']
]
[OPTIONS SKIP n]
}
<location_spec> ::=
{
<table_name_from_the_external_data_source> |
<list_of_files>
}
<list_of_files> ::=
'<path>' [,'<path>'...]
<path> ::=
[HDFS://]folder[/file_name]
<rejects_handling_spec> ::=
REJECT_POLICY=
{
FILL_NULL |
SKIP_ROW |
FAIL
}
<rejects_thresholds_spec> ::=
{
[REJECT_LIMIT_COUNT=<a integer value specifying max number of allowed rows with reject>] |
[
REJECT_SAMPLE=<minimum number of rows to process before making reject decision>
REJECT_LIMIT_RATIO=<a decimal between 0 and 1>
]
}Description
Parameter | Details |
|---|---|
ADD / DROP COLUMN | Adds or drops a column. |
RENAME | Lets you change the table name. |
All the rest of the parameters' description can be found under CREATE EXTERNAL TABLE description.
Examples
ALTER EXTERNAL TABLE schema31.ext_dim ADD COLUMN col_1 INTEGER,col_2 STRING;
ALTER EXTERNAL TABLE schema31.ext_dim DROP COLUMN col_1, col_2;
ALTER EXTERNAL TABLE ext_dim RENAME TO new_ext_dim;
ALTER EXTERNAL TABLE def_schema.location
DROP COLUMN col_11,col_12,col_13,col_14,col_15,col_16,col_17,col_18,col_19,col_20,col_21,col_22,col_23,col_24,col_25,col_26,col_27,col_28,col_29,col_30,col_31,col_32
LOCATION '/home/jethro/catalog_page'
CHANGE (col_1
col_1 INTEGER,col_2
col_2 STRING,col_3
col_3 INTEGER,col_4
col_4 INTEGER,col_5
col_5 STRING,col_6
col_6 INTEGER,col_7
col_7 INTEGER,col_8
col_8 STRING,col_9
col_9 STRING,col_10
col_10 STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '|'
QUOTED BY '"'
LINES TERMINATED BY '\n';