Hive collection data type example

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table empsubquery
. . . . . . . . . . . . . . . . . . . . . . .> ( empid int,
. . . . . . . . . . . . . . . . . . . . . . .>   firstname varchar(30),
. . . . . . . . . . . . . . . . . . . . . . .>   lastname varchar(30),
. . . . . . . . . . . . . . . . . . . . . . .>   tenure int,
. . . . . . . . . . . . . . . . . . . . . . .>   address struct<street:string,city:string>,
. . . . . . . . . . . . . . . . . . . . . . .>   subordinates array<string>);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into empsubquery
. . . . . . . . . . . . . . . . . . . . . . .> select 1,'Donghua','Luo',2,named_struct("street","Serangoon Road","city","Singapore"),array("Larry","Tom") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 2,'Larry','Elison',5,named_struct("street","Victor Street","city","New York"),array("Tom") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 3,'Tom','Kyte',4,named_struct("street","Victor Street","city","New York"),array("Tiger","Leon") union all
. . . . . . . . . . . . . . . . . . . . . . .> select 4,'Tiger','Hood',3,named_struct("street","Eliz Road","city","London"),array("Jack");

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat vertical
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from empsubquery;
empsubquery.empid         1
empsubquery.firstname     Donghua
empsubquery.lastname      Luo
empsubquery.tenure        2
empsubquery.address       {"street":"Serangoon Road","city":"Singapore"}
empsubquery.subordinates  ["Larry","Tom"]

empsubquery.empid         2
empsubquery.firstname     Larry
empsubquery.lastname      Elison
empsubquery.tenure        5
empsubquery.address       {"street":"Victor Street","city":"New York"}
empsubquery.subordinates  ["Tom"]

empsubquery.empid         3
empsubquery.firstname     Tom
empsubquery.lastname      Kyte
empsubquery.tenure        4
empsubquery.address       {"street":"Victor Street","city":"New York"}
empsubquery.subordinates  ["Tiger","Leon"]

empsubquery.empid         4
empsubquery.firstname     Tiger
empsubquery.lastname      Hood
empsubquery.tenure        3
empsubquery.address       {"street":"Eliz Road","city":"London"}
empsubquery.subordinates  ["Jack"]

4 rows selected (0.271 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> !outputformat table
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select firstname,address.street,address.city from empsubquery;
+------------+-----------------+------------+--+
| firstname  |     street      |    city    |
+------------+-----------------+------------+--+
| Donghua    | Serangoon Road  | Singapore  |
| Larry      | Victor Street   | New York   |
| Tom        | Victor Street   | New York   |
| Tiger      | Eliz Road       | London     |
+------------+-----------------+------------+--+
4 rows selected (16.004 seconds)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select firstname,subordinates[0] subordinates_0,subordinates[1] subordinates_1 from empsubquery;
+------------+-----------------+-----------------+--+
| firstname  | subordinates_0  | subordinates_1  |
+------------+-----------------+-----------------+--+
| Donghua    | Larry           | Tom             |
| Larry      | Tom             | NULL            |
| Tom        | Tiger           | Leon            |
| Tiger      | Jack            | NULL            |
+------------+-----------------+-----------------+--+
4 rows selected (15.304 seconds)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def>

Hive built-in function explode example

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table explodeexample (empid smallint, score array<double>);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> desc explodeexample;
+-----------+----------------+----------+--+
| col_name  |   data_type    | comment  |
+-----------+----------------+----------+--+
| empid     | smallint       |          |
| score     | array<double>  |          |
+-----------+----------------+----------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into explodeexample values (1,array(80,70.4,99.0,100));
Error: Error while compiling statement: FAILED: SemanticException [Error 10293]: Unable to create temp file for insert values Expression of type TOK_FUNCTION not supported in insert/values (state=42000,code=10293)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into explodeexample select 1,array(80,70.4,99.0,100);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into explodeexample select 2,array(70,59.5,80,85,95.6,60);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from explodeexample;
+-----------------------+----------------------------------+--+
| explodeexample.empid  |       explodeexample.score       |
+-----------------------+----------------------------------+--+
| 1                     | [80.0,70.4,99.0,100.0]           |
| 2                     | [70.0,59.5,80.0,85.0,95.6,60.0]  |
+-----------------------+----------------------------------+--+

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select empid,size(score) score_count from explodeexample;;
+--------+--------------+--+
| empid  | score_count  |
+--------+--------------+--+
| 1      | 4            |
| 2      | 6            |
+--------+--------------+--+

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select explode(score) score from explodeexample where empid=1;
+--------+--+
| score  |
+--------+--+
| 80.0   |
| 70.4   |
| 99.0   |
| 100.0  |
+--------+--+

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select empid,explode(score) score from explodeexample where empid=1;
Error: Error while compiling statement: FAILED: SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions (state=42000,code=10081)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select empid,exp.score from explodeexample
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def>   lateral view explode(score) exp as score;
+--------+------------+--+
| empid  | exp.score  |
+--------+------------+--+
| 1      | 80.0       |
| 1      | 70.4       |
| 1      | 99.0       |
| 1      | 100.0      |
| 2      | 70.0       |
| 2      | 59.5       |
| 2      | 80.0       |
| 2      | 85.0       |
| 2      | 95.6       |
| 2      | 60.0       |
+--------+------------+—+

Alternative way to insert values into complex data type using UNION ALL

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert into explodeexample
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select 3,array(82,73.2,96.0)union all
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select 4,array(56,85.3,82.0,99.9)union all
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select 5,array(65,93.0)union all
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select 6,array(54,55.4,68.0,86);

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from explodeexample;
+-----------------------+----------------------------------+--+
| explodeexample.empid  |       explodeexample.score       |
+-----------------------+----------------------------------+--+
| 1                     | [80.0,70.4,99.0,100.0]           |
| 2                     | [70.0,59.5,80.0,85.0,95.6,60.0]  |
| 3                     | [82.0,73.2,96.0]                 |
| 4                     | [56.0,85.3,82.0,99.9]            |
| 5                     | [65.0,93.0]                      |
| 6                     | [54.0,55.4,68.0,86.0]            |
+-----------------------+----------------------------------+--+

Other tips to work with Hive functions:

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> desc function case;
+----------------------------------------------------+--+
|                      tab_name                      |
+----------------------------------------------------+--+
| CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c; when a = d, return e; else return f |
+----------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> desc function extended case;
+----------------------------------------------------+--+
|                      tab_name                      |
+----------------------------------------------------+--+
| CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END - When a = b, returns c; when a = d, return e; else return f |
| Example:                                           |
|  SELECT                                            |
|  CASE deptno                                       |
|    WHEN 1 THEN Engineering                         |
|    WHEN 2 THEN Finance                             |
|    ELSE admin                                      |
|  END,                                              |
|  CASE zone                                         |
|    WHEN 7 THEN Americas                            |
|    ELSE Asia-Pac                                   |
|  END                                               |
|  FROM emp_details                                  |
+----------------------------------------------------+—+

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show functions;
+-------------------------+--+
|        tab_name         |
+-------------------------+--+
| !                       |
| !=                      |
| %                       |
| &                       |
| *                       |
| +                       |
| -                       |
| /                       |
| <                       |
| <=                      |
| <=>                     |
| <>                      |
| =                       |
| ==                      |
| >                       |
| >=                      |
| ^                       |
| abs                     |
| acos                    |
| add_months              |
| and                     |
| array                   |
| array_contains          |
| ascii                   |
| asin                    |
| assert_true             |
| atan                    |
| avg                     |
| base64                  |
| between                 |
| bin                     |
| case                    |
| cbrt                    |
| ceil                    |
| ceiling                 |
| coalesce                |
| collect_list            |
| collect_set             |
| compute_stats           |
| concat                  |
| concat_ws               |
| context_ngrams          |
| conv                    |
| corr                    |
| cos                     |
| count                   |
| covar_pop               |
| covar_samp              |
| crc32                   |
| create_union            |
| cume_dist               |
| current_database        |
| current_date            |
| current_timestamp       |
| current_user            |
| date_add                |
| date_format             |
| date_sub                |
| datediff                |
| day                     |
| dayofmonth              |
| dayofweek               |
| decode                  |
| degrees                 |
| dense_rank              |
| div                     |
| e                       |
| elt                     |
| encode                  |
| ewah_bitmap             |
| ewah_bitmap_and         |
| ewah_bitmap_empty       |
| ewah_bitmap_or          |
| exp                     |
| explode                 |
| field                   |
| find_in_set             |
| first_value             |
| floor                   |
| format_number           |
| from_unixtime           |
| from_utc_timestamp      |
| get_json_object         |
| greatest                |
| hash                    |
| hex                     |
| histogram_numeric       |
| hour                    |
| if                      |
| in                      |
| in_file                 |
| index                   |
| initcap                 |
| inline                  |
| instr                   |
| isnotnull               |
| isnull                  |
| java_method             |
| json_tuple              |
| lag                     |
+-------------------------+--+
|        tab_name         |
+-------------------------+--+
| last_day                |
| last_value              |
| lcase                   |
| lead                    |
| least                   |
| length                  |
| levenshtein             |
| like                    |
| ln                      |
| locate                  |
| log                     |
| log10                   |
| log2                    |
| logged_in_user          |
| lower                   |
| lpad                    |
| ltrim                   |
| map                     |
| map_keys                |
| map_values              |
| matchpath               |
| max                     |
| md5                     |
| min                     |
| minute                  |
| month                   |
| months_between          |
| named_struct            |
| negative                |
| next_day                |
| ngrams                  |
| noop                    |
| noopstreaming           |
| noopwithmap             |
| noopwithmapstreaming    |
| not                     |
| ntile                   |
| nvl                     |
| or                      |
| parse_url               |
| parse_url_tuple         |
| percent_rank            |
| percentile              |
| percentile_approx       |
| pi                      |
| pmod                    |
| posexplode              |
| positive                |
| pow                     |
| power                   |
| printf                  |
| radians                 |
| rand                    |
| rank                    |
| reflect                 |
| reflect2                |
| regexp                  |
| regexp_extract          |
| regexp_replace          |
| repeat                  |
| reverse                 |
| rlike                   |
| round                   |
| row_number              |
| rpad                    |
| rtrim                   |
| second                  |
| sentences               |
| sha2                    |
| sign                    |
| sin                     |
| size                    |
| sort_array              |
| soundex                 |
| space                   |
| split                   |
| sqrt                    |
| stack                   |
| std                     |
| stddev                  |
| stddev_pop              |
| stddev_samp             |
| str_to_map              |
| struct                  |
| substr                  |
| substring               |
| sum                     |
| tan                     |
| to_date                 |
| to_unix_timestamp       |
| to_utc_timestamp        |
| translate               |
| trim                    |
| trunc                   |
| ucase                   |
| unbase64                |
| unhex                   |
| unix_timestamp          |
| upper                   |
| uuid                    |
+-------------------------+--+
|        tab_name         |
+-------------------------+--+
| var_pop                 |
| var_samp                |
| variance                |
| version                 |
| weekofyear              |
| when                    |
| windowingtablefunction  |
| xpath                   |
| xpath_boolean           |
| xpath_double            |
| xpath_float             |
| xpath_int               |
| xpath_long              |
| xpath_number            |
| xpath_short             |
| xpath_string            |
| year                    |
| |                       |
| ~                       |
+-------------------------+--+

Hive alter table DDL to rename table and add/replace columns

Rename Tables

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show tables;
+----------------------+--+
|       tab_name       |
+----------------------+--+
| datatypedemo         |
| employees            |
| employees2           |
| employees3           |
| hivedatatypedemo     |
| iotdatademo          |
| monthly_taxi_fleet   |
| monthly_taxi_fleet2  |
| monthly_taxi_fleet3  |
| monthly_taxi_fleet4  |
| sales_data           |
| sales_data_dup       |
+----------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet4 monthly_taxi_fleet5;
Error: Error while compiling statement: FAILED: ParseException line 1:32 cannot recognize input near 'monthly_taxi_fleet5' '<EOF>' '<EOF>' in alter table statement (state=42000,code=40000)

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet4 rename to monthly_taxi_fleet5;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show tables;
+----------------------+--+
|       tab_name       |
+----------------------+--+
| datatypedemo         |
| employees            |
| employees2           |
| employees3           |
| hivedatatypedemo     |
| iotdatademo          |
| monthly_taxi_fleet   |
| monthly_taxi_fleet2  |
| monthly_taxi_fleet3  |
| monthly_taxi_fleet5  |
| sales_data           |
| sales_data_dup       |
+----------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7) COMMENT '',                                                |
|   `company` varchar(50) COMMENT '',                                          |
|   `fleet` int COMMENT '')                                                    |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                             |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'               |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'              |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='true',                                            |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514072478',                                         |
|   'numFiles'='2',                                                            |
|   'numRows'='20',                                                            |
|   'rawDataSize'='0',                                                         |
|   'totalSize'='1230',                                                        |
|   'transient_lastDdlTime'='1514072478')                                      |
+------------------------------------------------------------------------------+—+

Add columns

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 date);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7) COMMENT '',                                                |
|   `company` varchar(50) COMMENT '',                                          |
|   `fleet` int COMMENT '',                                                    |
|   `c1` date COMMENT '')                                                      |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.serde2.avro.AvroSerDe'                             |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'               |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'              |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514072785',                                         |
|   'numFiles'='2',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='1230',                                                        |
|   'transient_lastDdlTime'='1514072785')                                      |
+------------------------------------------------------------------------------+—+

Drop Columns

REPLACE COLUMNS removes all existing columns and adds the new set of columns. This can be done only for tables with a native SerDe (DynamicSerDe, MetadataTypedColumnsetSerDe, LazySimpleSerDe and ColumnarSerDe). Refer to Hive SerDe for more information. REPLACE COLUMNS can also be used to drop columns. For example, "ALTER TABLE test_change REPLACE COLUMNS (a int, b int);" will remove column 'c' from test_change's schema.

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 replace columns (month char(7), company varchar(50),fleet smallint);
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Replace columns is not supported for table default.monthly_taxi_fleet5. SerDe may be incompatible. (state=42000,code=1)

Use Parquet to demostrate column drop

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> drop table monthly_taxi_fleet5;
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> create table monthly_taxi_fleet5 (
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> month char(7), company varchar(50),fleet smallint)
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> stored as parquet;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> insert overwrite table monthly_taxi_fleet5
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet3 limit 10;

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint)                                                          |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='true',                                            |
|   'numFiles'='1',                                                            |
|   'numRows'='10',                                                            |
|   'rawDataSize'='30',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073272')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;
+----------------------------+------------------------------+----------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  |
+----------------------------+------------------------------+----------------------------+--+
| 2005-02                    | CityCab                      | 4968                       |
| 2005-02                    | Comfort                      | 10046                      |
| 2005-01                    | Premier                      | 370                        |
| 2005-01                    | TransCab                     | 560                        |
| 2005-01                    | Smart                        | 320                        |
| 2005-01                    | Individual Yellow- Top       | 696                        |
| 2005-01                    | YTC                          | 1223                       |
| 2005-01                    | SMRT                         | 2441                       |
| 2005-01                    | CityCab                      | 4965                       |
| 2005-01                    | Comfort                      | 9952                       |
+----------------------------+------------------------------+----------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 date);
Error: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Parquet does not support date. See HIVE-6384 (state=08S01,code=1)

https://issues.apache.org/jira/browse/HIVE-6384

Date added into Parquet in Hive 1.2.0 https://issues.apache.org/jira/browse/HIVE-8119

0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 add columns (c1 timestamp);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;
+----------------------------+------------------------------+----------------------------+-------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  | monthly_taxi_fleet5.c1  |
+----------------------------+------------------------------+----------------------------+-------------------------+--+
| 2005-02                    | CityCab                      | 4968                       | NULL                    |
| 2005-02                    | Comfort                      | 10046                      | NULL                    |
| 2005-01                    | Premier                      | 370                        | NULL                    |
| 2005-01                    | TransCab                     | 560                        | NULL                    |
| 2005-01                    | Smart                        | 320                        | NULL                    |
| 2005-01                    | Individual Yellow- Top       | 696                        | NULL                    |
| 2005-01                    | YTC                          | 1223                       | NULL                    |
| 2005-01                    | SMRT                         | 2441                       | NULL                    |
| 2005-01                    | CityCab                      | 4965                       | NULL                    |
| 2005-01                    | Comfort                      | 9952                       | NULL                    |
+----------------------------+------------------------------+----------------------------+-------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint,                                                          |
|   `c1` timestamp)                                                            |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514073343',                                         |
|   'numFiles'='1',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073343')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> alter table monthly_taxi_fleet5 replace columns (month char(7), company varchar(50),fleet smallint);
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> show create table monthly_taxi_fleet5;
+------------------------------------------------------------------------------+--+
|                                createtab_stmt                                |
+------------------------------------------------------------------------------+--+
| CREATE TABLE `monthly_taxi_fleet5`(                                          |
|   `month` char(7),                                                           |
|   `company` varchar(50),                                                     |
|   `fleet` smallint)                                                          |
| ROW FORMAT SERDE                                                             |
|   'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'              |
| STORED AS INPUTFORMAT                                                        |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'            |
| OUTPUTFORMAT                                                                 |
|   'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'           |
| LOCATION                                                                     |
|   'hdfs://cdh-vm.dbaglobe.com:8020/user/hive/warehouse/monthly_taxi_fleet5'  |
| TBLPROPERTIES (                                                              |
|   'COLUMN_STATS_ACCURATE'='false',                                           |
|   'last_modified_by'='donghua',                                              |
|   'last_modified_time'='1514073374',                                         |
|   'numFiles'='1',                                                            |
|   'numRows'='-1',                                                            |
|   'rawDataSize'='-1',                                                        |
|   'totalSize'='628',                                                         |
|   'transient_lastDdlTime'='1514073374')                                      |
+------------------------------------------------------------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def> select * from monthly_taxi_fleet5;
+----------------------------+------------------------------+----------------------------+--+
| monthly_taxi_fleet5.month  | monthly_taxi_fleet5.company  | monthly_taxi_fleet5.fleet  |
+----------------------------+------------------------------+----------------------------+--+
| 2005-02                    | CityCab                      | 4968                       |
| 2005-02                    | Comfort                      | 10046                      |
| 2005-01                    | Premier                      | 370                        |
| 2005-01                    | TransCab                     | 560                        |
| 2005-01                    | Smart                        | 320                        |
| 2005-01                    | Individual Yellow- Top       | 696                        |
| 2005-01                    | YTC                          | 1223                       |
| 2005-01                    | SMRT                         | 2441                       |
| 2005-01                    | CityCab                      | 4965                       |
| 2005-01                    | Comfort                      | 9952                       |
+----------------------------+------------------------------+----------------------------+--+
0: jdbc:hive2://cdh-vm.dbaglobe.com:10000/def>

Reference URLs: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-AlterTable/Partition/Column