Hive常用命令，

Hive常用命令，

/*Hive查看昨天日期，日期形如 20150712*/
select from_unixtime(unix_timestamp()-86400,'yyyyMMdd') from dw_stage.dual;
/*以下两种 UNIX_TIMESTAMP 用法等价*/ select UNIX_TIMESTAMP('2009-03-20','yyyy-MM-dd') from dw_stage.dual;
select UNIX_TIMESTAMP('2009-03-20 00:00:00') from dw_stage.dual;

/*指定日期所属周的第一天的日期*/
select date_sub('2015-09-02', pmod(datediff('2015-09-02', '2012-01-01'), 7)) from dw_stage.dual;
/*删除表*/
drop table if exists dw_db.table_name_peter;

/*建表语句*/
create table dw_db.gcw_test
(
city_id int, vppv_valid int
)
partitioned by (cal_dt string)
row format delimited fields terminated by '\t';

/*设置动态分区*/
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.max.dynamic.partitions = 100000;
SET hive.exec.max.dynamic.partitions.pernode = 100000;
/*将查询结果插入到指定表的分区内*/
insert overwrite table dw_db.gcw_test partition (cal_dt)
select city_id, vppv_valid, cal_dt
from dw_stage.gcw_test;
/*更改表名*/
use dw_db;
alter table old_table_name rename to new_table_name;

/*在所有存在的列后面，但是在分区列之前添加一列*/
use dw_db;
alter table da_broker_city_company_daily add columns 
(
revenue_accounting_combo double,
pm_broker_window_ad int
);
/*删除分区*/
use dw_db;
alter table table_name drop if exists partition (cal_dt = '2015-05-31');

/*Hive清空表数据*/
hive> dfs -rmr /user/hive/warehouse/dw_stage.db/peter000;
hive> dfs -rmr /user/hive/warehouse/dw_db.db/peter000;
/*将本地文件导入Hive表中*/ hive> load data local inpath '/data/dwadmin/tmp/input.txt' into table dw_stage.gcw_tmp000;
/*修改数据表，以使用LZO压缩数据*/ use dw_db;
ALTER TABLE table_name
SET FILEFORMAT
INPUTFORMAT "com.hadoop.mapred.DeprecatedLzoTextInputFormat"
OUTPUTFORMAT "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat";
/*打印更多的报错日志*/ hive -hiveconf hive.root.logger=DEBUG,console
/*修改字段名称或者类型*/ alter table dw_wuba_valid_order_daily change team_id team_id string;
/*添加jdbc2hive jar*/ add jar hdfs://nameservice1/user/hadoop/udf/jdbc2hive.jar; 
/*限制 mysql_db 的连接数*/ set jdbc2hive.runtime.map.max = 3; set mapred.map.tasks.speculative.execution=false;
set mapreduce.map.speculative=false;
/*显示分区表的锁信息*/ show locks dw_mobile_chat_msg_basic partition(cal_dt='2015-10-20');