操作

乱七八糟

添加jar包，前者是添加hdfs 后者是本地文件
add jar hdfs:/jar-file/udf-hashids.jar;
add jar /root/hive/lib/elasticsearch-hadoop-5.5.1.jar;
### 重启hive
/etc/init.d/hive-server2 status 或者 service hive-server2 status
/etc/init.d/hive-metastore status
ps -aux|grep hive
kill
nohup hive --service metastore >> ~/metastore.log 2>&1 &
nohup  hive --service hiveserver2 >> ~/hiveserver2.log 2>&1 &

表

row format delimited fields terminated by ',' -- 列分隔符
collection items terminated by '_' --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ':'  -- MAP 中的 key 与 value 的分隔符
lines terminated by '\n'; -- 行分隔符

create table dm_users_orders_d_stars_mainland_fact
(
  uid bigint,
  users int,
  goods_kind int,
  count int,
  beans decimal(12,2)
)
partitioned by (day int)
clustered by(uid) into 10 buckets
row format delimited fields terminated by ','
stored as orc
location '/hive/dm_users_orders_d_stars_mainland_fact';


修改表分隔符
alter table store set SERDEPROPERTIES('field.delim'='\t');

表改名
alter table marvin_test3 rename to marvin_test3_tmp;

内部表、外部表互相转换
alter table student2 set tblproperties('EXTERNAL'='TRUE');—转成外部
alter table student2 set tblproperties('EXTERNAL'='FALSE');--内部

分区

添加分区
ALTER TABLE marvin_test ADD PARTITION (day = 2) LOCATION "/user/hive/warehouse/blued_log.db/marvin2";
添加多个分区
alter table dept_partition add partition(day='20200405') partition(day='20200406');
删除分区
ALTER TABLE tmp_uid DROP IF EXISTS PARTITION (day=20190324);

字段、列

添加列
alter TABLE ods_feed add columns(extras  string,location  string);

修改字段
alter table 表名 change column 列名 新的列名 新列名类型 comment '列注释’;

命令行显示不全

spark-sql --queue root.bpa.ad_push.online -e "
select *
from tmp.dws_inapp_req_funnel_asa_day
where data_date = 20210509
and app_key = '74482a45ca0a8b1f23089844'
" | sed 's/\t/,/g' > /home/mazb/test.txt

导入导出

import table student2 partition(month='201709') from'/user/hive/warehouse/export/student';
export table default.student to '/user/hive/warehouse/export/student';