1、Create Table 命令用于在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 字段名称 数据类型
1 Eid int
2 Name String
3 Salary Float
4 Designation String
创建命令如下:
CREATE TABLE IF NOT EXISTS userdb.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;
输入上述命令,如果直接执行,则会提示如下错误:
大概意思是hadoop:supergroup这个权限无权对userdb.db文件进行操作,所以需要在登录beeline时输入用户名:hadoop,重新运行命令即可。
创建后查询可以看到创建的employee表
2、使用python操作hive创建employee表,代码如下:
# coding:utf-8
from pyhive import hive
from TCLIService.ttypes import TOperationState
# 打开hive连接
hiveConn = hive.connect(host='192.168.83.135',port=11111,username='hadoop')
cursor = hiveConn.cursor()
# 执行sql语句
sql = ''' CREATE TABLE IF NOT EXISTS userdbbypy.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 '''
cursor.execute(sql, async=True)
# 得到执行语句的状态
status = cursor.poll().operationState
print "status:",status
# 关闭hive连接
cursor.close()
hiveConn.close()
运行代码,然后在beeline中查看结果,如图所示: