大数据学习笔记-------------------(23)

2017/03/14 17:25
阅读数 35

第23章数据表操作

23.1创建数据表

创建数据表和在表中插入数据。HIVE创建数据表的语法和SQL创建数据表非常类似。

23.1.1 创建数据表

HIVE创建数据表的语句如下:

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.] table_name 
[(col_name data_type [COMMENT col_comment], ...)] 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format]

Ø  例子

 用create table语法创建一个名为employee的table。在employee的table中的列名与数据类型:


如下数据是一个注释,行格式化字段,如字段终止符、行终止符和存储文件类型:

COMMENT 'Employee details'
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'
STORED IN TEXT FILE

创建一个包含如上表格数据的数据表,表名为employee:

CREATE TABLE IF NOT EXISTS employee ( eid int, name String, 
salary String, destination String) 
COMMENT 'Employee details'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE;

如果添加选项"if not exists",Hive会在表已经存在的情况下忽略语句。一旦创建表成功,会获取到如下响应:

     

23.1.2 JDBC程序

保存代码到文件HiveCreateTable.java中。用如下命令进行编译和执行代码:

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveCreateTable
{
	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("CREATE TABLE IF NOT EXISTS "
			+" employee ( eid int, name String, "
			+" salary String, destignation String)"
			+" COMMENT ‘Employee details’"
			+" ROW FORMAT DELIMITED"
			+" FIELDS TERMINATED BY ‘\t’"
			+" LINES TERMINATED BY ‘\n’"
			+" STORED AS TEXTFILE;");
		System.out.println(“ Table employee created.”);
		con.close();
	}
}

javac HiveCreateTable.java

java HiveCreateTable

输出:Table employeecreated.

23.2load数据(插入数据)

23.2.1 load data

         通常,表创建成功后,用Insert语句插入数据。但是在Hive中,用load data语句插入数据。当把数据插入到Hive时,最好使用load data来存储快记录。load data的方法有两种:一是从本地文件系统、二是从Hadoop文件系统。load data的语法如下:

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]

LOCAL:指定本地路径。可选项

OVERWRITE: 覆盖表中的数据。可选项

PARTITION:可选项

Ø  例子

把如下数据插入表中,文本文件的名为sample.txt,该文件在/usr/local路径下。

1201 Gopal 45000 Technical manager 
1202 Manisha 45000 Proof reader 
1203 Masthanvali 40000 Technical writer 
1204 Krian 40000 Hr Admin 
1205 Kranthi 30000 Op Admin

执行如下语句,把文本信息插入表中:

LOAD DATA LOCAL INPATH '/home/hadoop/sample.txt' OVERWRITE  INTO TABLE  employee;

命令执行成功,可以看到响应信息。


23.2.2 JDBC程序

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveLoadData
{
	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:9000/hive", "", "");
		// create statement
		Statement stmt = con.createStatement();
		// execute statement
		stmt.executeQuery("LOAD DATA LOCAL INPATH '/home/hadoop/sample.txt'"
		+"OVERWRITE INTO TABLE employee;");
		System.out.println("Load Data into employee successful");
		con.close();
	}
}

保存代码到文件 HiveLoadData.java中。用如下命令进行编译和执行代码:

javac HiveLoadData.java

ava HiveLoadData

 输出:LoadData into employee successful

23.3 修改数据表(Altertable)

怎么修改表的属性值,例如修改表明、修改列名、添加列、删除或替换列。

23.3.1 修改数据表语法

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 ...])

 重命名的语法,执行下面语法,把表名从employee改为emp:

ALTER TABLE employee RENAME TO emp;

23.3.2 重命名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:9000/hive", "", "");
	// 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

23.3.3 Change语法

下表加黑的字体,表明employee表要进行改变的部分:


执行下面的命令,来修改上表中列名和列的数据类型:

ALTER TABLE employee CHANGE name ename String;
ALTER TABLE employee CHANGE salary salary Double;
  

23.3.4 Change的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:9000/hive", "", "");
	// 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.

23.3.5 AddColumns语句

在employee表中添加列dept,执行的语句如下:

ALTER TABLE employee ADD COLUMNS (dept STRING COMMENT 'Department name');

23.3.6 AddColumns的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:9000/hive", "", "");
		// 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

23.3.7 Replace语句

执行下面的语句,将employee表中所有列都删除,并用emp和name替换掉相应的列:

ALTER TABLE employee REPLACE COLUMNS (     eid INT empid Int, ename STRING name String);

23.3.8 Replace的JDBC程序

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:9000/hive", "", "");
		// 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

23.4 删除表(Droptable)

 当从HiveMetastore删除一个表时,同时删除了表中列的数据和元数据。它可以是一个正常的表(存储在Metastore)或者一个外部表(存储在本地文件系统中);Hive以相同的方式对待,无论它们的类型如何。

23.4.1删除表(DropTable)

删除表的语法DROP TABLE [IF EXISTS] table_name;

创建一个名为emp带有列:eid、ename的表,然后删除表emp:



23.4.2删除表的JDBC程序

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;
public class HiveDropTable
{
	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:9000/hive", "", "");
	// create statement
	Statement stmt = con.createStatement();
	//create emp table
	stmt.executeQuery("CREATE TABLE emp"
			+" (eid INT,"
			+" ename String);");
	// execute statement
	stmt.executeQuery("DROP TABLE IF EXISTS emp;");
	System.out.println("Drop table successful.");
	con.close();
	}
}
保存代码到文件 HiveDropTable.java 中。用如下命令进行编译和执行代码:

javac HiveDropTable.java     

java HiveDropTable

 输出:Drop table successful

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部