Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Replace column to hive

Solved Go to solution

Replace column to hive

Super Collaborator

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

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Replace column to hive

Mentor

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

Re: Replace column to hive

Mentor

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

Re: Replace column to hive

Super Collaborator

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

thanks

Re: Replace column to hive

Mentor

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

Re: Replace column to hive

Super Collaborator

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.

Re: Replace column to hive

Mentor

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

Re: Replace column to hive

Super Collaborator

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.

Re: Replace column to hive

Mentor

@Roberto Sancho what was your solution?

Re: Replace column to hive

New Contributor

Run below command, data will be visible in table

msck repair table <table_name>;