第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.