java设置hive执行引擎_hive sql常用整理-hive引擎设置

ALTER TABLE table_name ADD PARTITION (partCol = 'value1') location 'loc1'; //示例

ALTER TABLE table_name ADD IF NOT EXISTS PARTITION (dt='20130101') LOCATION '/user/hadoop/warehouse/table_name/dt=20130101'; //一次添加一个分区

例：

ALTER TABLE $abc$ ADD IF NOT EXISTS PARTITION(p_provincecode=$abc.p_provincecode$,p_year=$abc.p_year$,p_month=$abc.p_month$) LOCATION '/hadoop路径/p_provincecode=$abc.p_provincecode$/p_year=$abc.p_year$/p_month=$abc.p_month$/'

ALTER TABLE page_view ADD PARTITION (dt='2008-08-08', country='us') location '/path/to/us/part080808' PARTITION (dt='2008-08-09', country='us') location '/path/to/us/part080809';  //一次添加多个分区

删除分区

ALTER TABLE login DROP IF EXISTS PARTITION (dt='2008-08-08');

ALTER TABLE page_view DROP IF EXISTS PARTITION (dt='2008-08-08', country='us');

修改分区

ALTER TABLE table_name PARTITION (dt='2008-08-08') SET LOCATION "new location";

ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

添加列(字段)

ALTER TABLE table_name ADD COLUMNS (col_name STRING);  //在所有存在的列后面，但是在分区列之前添加一列

修改列

CREATE TABLE test_change (a int, b int, c int);

/* will change column a's name to a1*/

ALTER TABLE test_change CHANGE a a1 INT;

修改列的顺序

ALTER TABLE table_name CHANGE col_old_name col_new_name column_type AFTER column_name;

/* will change column a's name to a1, a's data type to string, and put it after column b. The new table's structure is: b int, a1 string, c int*/

ALTER TABLE test_change CHANGE a a1 STRING AFTER b;

/* will change column b's name to b1, and put it as the first column. The new table's structure is: b1 int, a int, c int*/

ALTER TABLE test_change CHANGE b b1 INT FIRST;

hive删除字段

ALTER TABLE wireless_union_data_export_part1 REPLACE COLUMNS();

修改表属性

alter table table_name set TBLPROPERTIES ('EXTERNAL'='TRUE');  //内部表转外部表

alter table table_name set TBLPROPERTIES ('EXTERNAL'='FALSE');  //外部表转内部表

表的重命名

ALTER TABLE table_name RENAME TO new_table_name

gbase修改字段只能先删掉字段，然后再添加

gbase删除字段

alter table table_name drop 字段名

gbase添加字段

alter table table_name add COLUMN col_name STRING;

gbase清除表数据不删除表

delete 表名

常用函数

1.isnull(string a)--如果a是null，返回flase，如果啊不为空，返回true

2.ROUND(double a)四舍五入bigint类型

CEILING       --舍入到最大整数   3.33舍入到4    2.98舍入到3    -3.61舍入到-3

FLOOR()       --舍入到最小整数   3.33舍入到3    2.98舍入到2    -3.61舍入到-4

3.map(key1, value1, key2, value2, …)通过指定的键/值对，创建一个map