Created 02-18-2016 11:51 AM
Hi:
I want to delete one column to Hive table, my table is like that:
CREATE TABLE journey_v4( CODTF string, CODNRBEENF string, FECHAOPRCNF timestamp, FRECUENCIA int) PARTITIONED BY (year string,month string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' stored as Avro TBLPROPERTIES ("immutable"="false","avro.compress"="zlib","immutable"="false");
and then i added new column:
ALTER TABLE journey_v4 ADD COLUMNS (EXTRA string);
then i want to delete de column EXTRA to go back to the original table, but it doesnt have effect
ALTER TABLE journey_v4 REPLACE COLUMNS (CODTF string, CODNRBEENF string, FECHAOPRCNF timestamp,FRECUENCIA in);
Anny suggestions????
Thanks
Created 02-18-2016 11:57 AM
@Roberto Sancho can't be dropped , you can create a new table from select query without the extra column using CTAS or 'Create table as select' http://www.dummies.com/how-to/content/how-to-use-hives-create-table-as-select-ctas.html
Altering and Dropping Tables
Table names can be changed and columns can be added or replaced:
hive> ALTER TABLE events RENAME TO 3koobecaf; hive> ALTER TABLE pokes ADD COLUMNS (new_col INT); hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); hive> ALTER TABLE invites REPLACE COLUMNS (foo INT, bar STRING, baz INT COMMENT 'baz replaces new_col2');
Note that REPLACE COLUMNS replaces all existing columns and only changes the table's schema, not the data. The table must use a native SerDe. REPLACE COLUMNS can also be used to drop columns from the table's schema:
hive> ALTER TABLE invites REPLACE COLUMNS (foo INT COMMENT 'only keep the first column');
Created 02-18-2016 11:57 AM
@Roberto Sancho can't be dropped , you can create a new table from select query without the extra column using CTAS or 'Create table as select' http://www.dummies.com/how-to/content/how-to-use-hives-create-table-as-select-ctas.html
Altering and Dropping Tables
Table names can be changed and columns can be added or replaced:
hive> ALTER TABLE events RENAME TO 3koobecaf; hive> ALTER TABLE pokes ADD COLUMNS (new_col INT); hive> ALTER TABLE invites ADD COLUMNS (new_col2 INT COMMENT 'a comment'); hive> ALTER TABLE invites REPLACE COLUMNS (foo INT, bar STRING, baz INT COMMENT 'baz replaces new_col2');
Note that REPLACE COLUMNS replaces all existing columns and only changes the table's schema, not the data. The table must use a native SerDe. REPLACE COLUMNS can also be used to drop columns from the table's schema:
hive> ALTER TABLE invites REPLACE COLUMNS (foo INT COMMENT 'only keep the first column');
Created 02-18-2016 01:13 PM
Yes i know, for that reason after drop and create I dont know why the table is empty, because the files are there.
thanks
Created 02-18-2016 01:16 PM
must be a spelling mistake or something? I noticed your table is named journey_v4_externa with no l. @Roberto Sancho
Created 02-18-2016 12:27 PM
Hi:
i create external table:
CREATE EXTERNAL TABLE journey_v4_externa( CODTF string, CODNRBEENF string, FECHAOPRCNF timestamp, FRECUENCIA int) PARTITIONED BY (year string,month string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' stored as Avro LOCATION '/user/dangulo/tables_pig' TBLPROPERTIES ("immutable"="false","avro.compress"="zlib");
then insert the date, then y drop the table, but the files still in HDFS and the i RE-create the table but the table is empty, what can i do to insert the data again???
thanks.
Created 02-18-2016 01:06 PM
@Roberto Sancho the Hive DDL just changes schema, it does not affect data on disk.
Created 02-18-2016 02:36 PM
I mean the files are here:
/user/dangulo/tables_pig/year=2016/month=01
And the table that i create was like this:
CREATE EXTERNAL TABLE journey_v4_externa( CODTF string, CODNRBEENF string, FECHAOPRCNF timestamp, FRECUENCIA int) PARTITIONED BY (year string,month string) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' stored as Avro LOCATION '/user/dangulo/tables_pig' TBLPROPERTIES ("immutable"="false","avro.compress"="zlib");
so, i dont know why there is not data into the table.
Created 02-23-2016 02:34 AM
@Roberto Sancho what was your solution?
Created 02-22-2018 11:14 AM
Run below command, data will be visible in table
msck repair table <table_name>;