本章介绍如何创建表以及如何将数据插入表中.在HIVE中创建表的约定与使用SQL创建表非常相似.
创建表语句
创建表是一个用于在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表中的字段及其数据类型:
Sr.No | Field Name | Data Type |
---|---|---|
1 | Eid | int |
2 | Name | String |
3 | Salary | Float |
4 | Designation | string |
以下数据是注释,行格式化字段,如字段终止符,行终止符和存储文件类型.
COMMENT ‘Employee details’FIELDS TERMINATED BY ‘\t’LINES TERMINATED BY ‘\n’STORED IN TEXT FILE
以下查询使用上述数据创建名为 employee 的表.
hive> CREATE TABLE IF NOT EXISTS employee ( eid int, name String,salary String, destination String)COMMENT ‘Employee details’ROW FORMAT DELIMITEDFIELDS TERMINATED BY ‘\t’LINES TERMINATED BY ‘\n’STORED AS TEXTFILE;
如果添加选项IF NOT EXISTS,Hive会在表已存在的情况下忽略该语句.
On成功创建表后,您将看到以下响应:
OKTime taken: 5.905 secondshive>
JDBC程序
给出了创建表的JDBC程序示例.
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(); }}
将程序保存在名为HiveCreateDb.java的文件中.以下命令用于编译和执行该程序.
$ javac HiveCreateDb.java$ java HiveCreateDb
输出
Table employee created.
加载数据语句
通常,在SQL中创建表后,我们可以使用Insert语句插入数据.但是在Hive中,我们可以使用LOAD DATA语句插入数据.
在将数据插入Hive时,最好使用LOAD DATA存储批量记录.有两种加载数据的方法:一种是来自本地文件系统,另一种是来自Hadoop文件系统.
语法
加载数据的语法如下:
LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOCAL是指定本地路径的标识符.它是可选的.
OVERWRITE是可选的,用于覆盖表格中的数据.
PARTITION是可选的.
示例
我们将在表格中插入以下数据.它是/home/user 目录中名为 sample.txt 的文本文件.
1201 Gopal 45000 Technical manager1202 Manisha 45000 Proof reader1203 Masthanvali 40000 Technical writer1204 Kiran 40000 Hr Admin1205 Kranthi 30000 Op Admin
以下查询将给定文本加载到表中.
hive> LOAD DATA LOCAL INPATH '/home/user/sample.txt'OVERWRITE INTO TABLE employee;
成功下载后,您会看到以下回复:
OKTime taken: 15.905 secondshive>
JDBC程序
如下所示是将给定数据加载到表中的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:10000/userdb", "", ""); // create statement Statement stmt = con.createStatement(); // execute statement stmt.executeQuery("LOAD DATA LOCAL INPATH '/home/user/sample.txt'" + "OVERWRITE INTO TABLE employee;"); System.out.println("Load Data into employee successful"); con.close(); }}
将程序保存在名为HiveLoadData.java的文件中.使用以下命令编译并执行该程序.
$ javac HiveLoadData.java$ java HiveLoadData
输出:
Load Data into employee successful