hive中表的创建以及数据的加载

本文介绍何如在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]

 

例如创建一个包含int类型的eid,String类型的name，String类型的salary，String类型的destination，并以tab制表符按列分割,\n按行分割的员工表：

hive> 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;

 

Java编程方式创建hive表：

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();
}
}

 

加载数据：

一般来说,在SQL创建表后,我们可以使用insert语句插入数据。但在hive中,我们可以使用Load Data加载数据到hive表中。

插入数据到hive表中时,最好使用 LOAD DATA加载大量数据。

有两种方法来加载数据:一个是来自本地文件系统,其次是Hadoop文件系统。

 

LOAD DATA加载数据语法：

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 manager
1202 Manisha 45000 Proof reader
1203 Masthanvali 40000 Technical writer
1204 Kiran 40000 Hr Admin
1205 Kranthi 30000 Op Admin

 

执行载入数据操作：

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

 

 

 以java编程的方式加载数据：

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();
}
}