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

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

第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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。