hive相关

• 杀死后台执行的hive任务

yarn application -kill <Application ID>

• 列出后台相关的hive任务

yarn application -list | grep ***

• 查看日志

yarn logs -applicationId xxx

• 查看某张表的建表语句

show create table tablenamne

• hadoop上传文件

hadoop fs -put ./local_path ./hdfs_path

• hadoop删除文件

hadoop fs -rm -r /file.txt

• 解析list里面多个对象的sql

  SELECT
	  project_name,
	  city_code,
	  get_json_object(custom_table.info,'$.purpose') AS purpose
  FROM
  (SELECT 
	  project_name,
	  city_code,
	  regexp_replace(regexp_replace(purpose_info,'[\\[\\]]',''),',\\{','#\\{')  AS custom_info
  FROM ********** WHERE pt='20220109000000'
  ) AS prefer_detail LATERAL VIEW OUTER explode(split(prefer_detail.custom_info,'#')) custom_table AS info

　　

• textfile格式的建表语句（单分区）

create external table if not exists table_name (
  `stat_date` string COMMENT '统计时间',
  `scene` string COMMENT '场景',
  `group_type` string COMMENT '用户分组',
  `expo_uv` string COMMENT '曝光量',
  `click_uv` string COMMENT '点击量',
  `click_ratio` string COMMENT '点击率',
  `sj_uv` string COMMENT '商机量'
  )
partitioned by (pt string)
row format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile
location '/table_name_location'

• textfile格式的建表语句（多分区）

CREATE TABLE  table_name(

  `stat_date` string COMMENT '统计时间',
  `scene` string COMMENT '场景',
  `group_type` string COMMENT '用户分组',
  `expo_uv` string COMMENT '曝光量',
  `click_uv` string COMMENT '点击量',
  `click_ratio` string COMMENT '点击率',
  `sj_uv` string COMMENT '商机量'

) PARTITIONED BY ( `pt` string, `city_code` string) 
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n'

stored as textfile
location '/table_name_location'

 从文件建hive表

#!/bin/bash
set -eu
SQL="load data local inpath 'template_lead_info_da.txt' overwrite into table table_name;"
hive -e "${SQL}"

#从文件建hive分区表
"load data local inpath 'template_lead_info_da.txt' overwrite into table table_name partition (pt=20200225000000)"

#多分区表
sql="LOAD DATA LOCAL INPATH 'template_lead_info_da.txt' OVERWRITE INTO table_name PARTITION (pt=20200225000000, city_code=110000);"