本章介绍如何更改表的属性,例如更改其表名,更改列名,添加列以及删除或替换列.
它用于更改Hive中的表.
该语句采用以下任何语法基于我们希望在表中修改的属性.
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 ...])
以下查询将表从员工重命名为 emp .
hive> ALTER TABLE employee RENAME TO emp;
重命名表的JDBC程序如下所示.
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(); } }
将程序保存在名为HiveAlterRenameTo.java的文件中.使用以下命令编译并执行该程序.
$ javac HiveAlterRenameTo.java $ java HiveAlterRenameTo
Table renamed successfully.
下表包含员工表的字段,并显示要更改的字段(粗体).
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 |
以下查询重命名使用上述数据的列名和列数据类型:
hive> ALTER TABLE employee CHANGE name ename String; hive> ALTER TABLE employee CHANGE salary salary Double;
以下是更改列的JDBC程序.
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(); } }
将程序保存在名为HiveAlterChangeColumn.java的文件中.使用以下命令编译并执行该程序.
$ javac HiveAlterChangeColumn.java $ java HiveAlterChangeColumn
Change column successful.
以下查询将一个名为dept的列添加到employee表中.
hive> ALTER TABLE employee ADD COLUMNS ( dept STRING COMMENT 'Department name');
下面给出了向表中添加列的JDBC程序.
import java.sql.SQLException; import java.sql.Connection; import java.sql.ResultSet; 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(); } }
将程序保存在名为HiveAlterAddColumn.java的文件中.使用以下命令编译并执行该程序.
$ javac HiveAlterAddColumn.java $ java HiveAlterAddColumn
Add column successful.
以下查询删除员工表中的所有列,用 \\ temp 和 name 列替换它:
hive> ALTER TABLE employee REPLACE COLUMNS ( eid INT empid Int, ename STRING name String);
以下是用<替换 eid 列的JDBC程序b> empid 和 ename 列名称.
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(); } }
将程序保存在名为HiveAlterReplaceColumn.java的文件中.使用以下命令编译并执行该程序.
$ javac HiveAlterReplaceColumn.java $ java HiveAlterReplaceColumn
Replace column successful.