版权声明:本文为博主原创文章,转载请注明出处 https://blog.csdn.net/yidu_fanchen/article/details/77683479
Alter Table命令用来修改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 ... ])
1、重命名表命令:
ALTER TABLE employee RENAME TO emp;
可以看到,employee表已经被改成了emp表。
2、使用python 操作hive,修改表名,代码如下:
from pyhive import hive
from TCLIService.ttypes import TOperationState
hiveConn = hive.connect(host='192.168.83.135' ,port=11111 ,username='hadoop' )
cursor = hiveConn.cursor()
sql = ''' USE userdbbypy '''
cursor.execute(sql, async=True )
status = cursor.poll().operationState
print "status:" ,status
sql = ''' ALTER TABLE employee RENAME TO emp '''
cursor.execute(sql, async=True )
status = cursor.poll().operationState
print "status:" ,status
cursor.close()
hiveConn.close()
执行程序,然后去beeline里面进行查看验证,如图:
3、更改表的字段和类型:
ALTER TABLE emp CHANGE name ename String;
ALTER TABLE emp CHANGE salary salary DOUBLE ;
执行语句,查询结果,如图所示:
4、使用python 操作hive,修改emp表的字段名和字段类型:
from pyhive import hive
from TCLIService.ttypes import TOperationState
hiveConn = hive.connect(host='192.168.83.135' ,port=11111 ,username='hadoop' ,database='userdbbypy' )
cursor = hiveConn.cursor()
sql = ''' ALTER TABLE emp CHANGE name ename String '''
cursor.execute(sql, async=True )
status = cursor.poll().operationState
print "status:" ,status
sql = ''' ALTER TABLE emp CHANGE salary salary DOUBLE '''
cursor.execute(sql, async=True )
status = cursor.poll().operationState
print "status:" ,status
cursor.close()
hiveConn.close()
执行代码,查看运行结果:
5、添加一列:
ALTER TABLE emp ADD COLUMNS(dept String COMMENT 'Department name' );
执行命令,结果如下:
6、使用python操作hive,为emp表添加一列dept,代码如下:
from pyhive import hive
from TCLIService.ttypes import TOperationState
hiveConn = hive.connect(host='192.168.83.135' ,port=11111 ,username='hadoop' ,database='userdbbypy' )
cursor = hiveConn.cursor()
sql = ''' ALTER TABLE emp ADD COLUMNS(dept String COMMENT 'Department name') '''
cursor.execute(sql, async=True )
status = cursor.poll().operationState
print "status:" ,status
cursor.close()
hiveConn.close()
执行代码,查看结果如图所示:
7、删除表
DROP TABLE [IF EXISTS] table_name;
本条命令是删除表的,不在做详细讲解。