Replace column to hive

[#1]

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

[#2 · Accepted solution]

@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');

[#3]

Yes i know, for that reason after drop and create I dont know why the table is empty, because the files are there.

thanks

[#4]

must be a spelling mistake or something? I noticed your table is named journey_v4_externa with no l. @Roberto Sancho

[#5]

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.

[#6]

@Roberto Sancho the Hive DDL just changes schema, it does not affect data on disk.

[#7]

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.

[#8]

@Roberto Sancho what was your solution?

[#9]

Run below command, data will be visible in table

msck repair table <table_name>;