apache hive - Hive Alter Table - hive tutorial - hadoop hive - hadoop hive - hiveql



apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

How to alter a table in Hive?

Alter Table Statement

  • SQL ALTER TABLE statement is used to rename a table
  • The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table
  • It is used to alter a table in Hive.

Syntax

  • The statement takes any of the following syntaxes based on what attributes we wish to modify in a table.
ALTER TABLE name RENAME TO new_name
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
ALTER TABLE name DROP [COLUMN] column_name
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Rename To… Statement

  • The following query renames the table from employee to emp

Sample Code:

hive> ALTER TABLE employee RENAME TO emp;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
 learn hive tutorial - hive alter table - hive example

apache hive - learn hive - hive tutorial - hive alter table - hive example

apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

JDBC Program

  • The JDBC program to rename a table is as follows.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet; 
import java.sql.Statement;
import java.sql.DriverManager
public class HiveAlterRenameTo {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver" 
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();   
      // execute statement
      stmt.executeQuery("ALTER TABLE employee RENAME TO emp;");
      System.out.println("Table Renamed Successfully");
      con.close();
   }
}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
  • The following commands given are used to compile and execute this program.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Sample Code:

$ javac HiveAlterRenameTo.java
$ java HiveAlterRenameTo
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Output:

Table renamed successfully.
  • The following table contains the fields of employee table and it shows the fields to be changed (in bold).
Field Name Convert from Data Type Change Field Name Convert to Data Type
eid int eid int
name String ename String
salary Float salary Double
designation String designation String
  • The following queries rename the column name and column data type using the above data:
hive> ALTER TABLE employee CHANGE name ename String;
hive> ALTER TABLE employee CHANGE salary salary Double;
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

JDBC Program

  • Given below is the JDBC program to change a column.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveAlterChangeColumn {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");    
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("ALTER TABLE employee CHANGE name ename String;");
      stmt.executeQuery("ALTER TABLE employee CHANGE salary salary Double;");  
      System.out.println("Change column successful.");
      con.close();
   }
}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
  • The following commands given are used to compile and execute this program.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Code

$ javac HiveAlterChangeColumn.java
$ java HiveAlterChangeColumn
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Output:

Change column successful.

Add Columns Statement

  • The following query adds a column named dept to the employee table.
hive> ALTER TABLE employee ADD COLUMNS ( 
dept STRING COMMENT 'Department name');
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

JDBC Program

  • The JDBC program to add a column to a table is given below.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Result;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveAlterAddColumn {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName)
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");
      // create statement
      Statement stmt = con.createStatement();
      // execute statement
      stmt.executeQuery("ALTER TABLE employee ADD COLUMNS " + " (dept STRING COMMENT 'Department name');");
      System.out.prinln("Add column successful.");
      con.close();
   }
}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
  • The following commands are done to compile and execute this program.

Sample Code:

$ javac HiveAlterAddColumn.java
$ java HiveAlterAddColumn
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Output:

Add column successful.
  • The following query deletes all the columns from the employee table and replaces it with emp and name columns:

Syntax:

hive> ALTER TABLE employee REPLACE COLUMNS ( 
eid INT empid Int, 
ename STRING name String);
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

JDBC Program

  • Given below is the JDBC program to replace eid column with empid and ename column with name.
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveAlterReplaceColumn {
   private static String driverName = "org.apache.hadoop.hive.jdbc.HiveDriver";
   public static void main(String[] args) throws SQLException {
      // Register driver and create driver instance
      Class.forName(driverName);   
      // get connection
      Connection con = DriverManager.getConnection("jdbc:hive://localhost:10000/userdb", "", "");   
      // create statement
      Statement stmt = con.createStatement(); 
      // execute statement
      stmt.executeQuery("ALTER TABLE employee REPLACE COLUMNS "
         +" (eid INT empid Int,"
         +" ename STRING name String);");
      System.out.println(" Replace column successful");
      con.close();
   }
}
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team
  • The following commands given are used to compile and execute this program.
apache hive related article tags - hive tutorial - hadoop hive - hadoop hive - hiveql - hive hadoop - learnhive - hive sql

Sample Code:

$ javac HiveAlterReplaceColumn.java
$ java HiveAlterReplaceColumn
Clicking "Copy Code" button will copy the code into the clipboard - memory. Please paste(Ctrl+V) it in your destination. The code will get pasted. Happy coding from Wikitechy hive tutorial team

Output:

Replace column successful.

Wikitechy Apache Hive tutorials provides you the base of all the following topics . Enjoy learning on big data , hadoop , data analytics , big data analytics , mapreduce , hadoop tutorial , what is hadoop , big data hadoop , apache hadoop , apache hive , hadoop wiki , hadoop jobs , hadoop training , hive tutorial , hadoop big data , hadoop architecture , hadoop certification , hadoop ecosystem , hadoop fs , apache pig , hadoop cluster , cloudera hadoop , hadoop download , hadoop mapreduce , hadoop workflow , hive data types , hadoop hive , pig hadoop , hadoop administration , hadoop installation , hive hadoop , learn hadoop , hadoop for dummies , hadoop commands , hive definition , hiveql , learnhive , hive sql , hive database , hive date functions , hive query , apache hive tutorial , hive apache , hive wiki , what is a hive , hive big data , programming hive , what is hive in hadoop , hive documentation , how does hive work

Related Searches to Hive Alter Table