Page tree
Skip to end of metadata
Go to start of metadata

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

ParameterDetails
ADD / DROP COLUMNAdds or drops a column.
RENAMELets 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';

See Also 

ALTER TABLE

CREATE EXTERNAL TABLE

CREATE EXTERNAL DATA SOURCE

  • No labels