Hive项目实践常用操作汇总(不断更新)

Hive

1、分隔符前后字段截取

Tips:
mysql的3种字段截取；
1、`SUBSTR(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：`SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO

2、`SUBSTRING(s, start, length)从字符串 s 的 start 位置截取长度为 length 的子字符串从字符串 RUNOOB 中的第 2 个位置截取 3个 字符：
`SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO

3、`SUBSTRING_INDEX(s, delimiter, number)返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。 如果 number 是正数，返回第 number 个字符左边的字符串。 如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
SELECT SUBSTRING_INDEX('a*b','*',1) -- a 
SELECT SUBSTRING_INDEX('a*b','*',-1) -- b   
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1) -- c`

	
hive使用split截取；
# cell-key:12345_678
split(cellkey,'_')[0],split(cellkey,'_')[1] -- 12345,678

2、Hive建表以及表字段分隔符

Tips:
1.最后一个字段不需要加逗号；
2.external 是外部表的标志，不加关键字则是内部表，外部表删不掉数据。一般做测试表的话建议建内部表，删除方便；
3.分区字段 partition by(‘XXXX’ TYPE,‘OOOO’ TYPE…)不能出现在创建表时的字段中，换句话说，分区字段字段也是新的字段，在别的表中用的时候和普通字段使用方法是一样的；

建表语句示例：

CREATE external TABLE `table`(
  `city_id` int COMMENT '地市编码',
  `city_name` string COMMENT '地市名称',
  `dayNow` string COMMENT '查询日期'
)
PARTITIONED BY (
     `province_id` int,
     `daySelect` int)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'=',',
  'serialization.format'=',')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';
     
     
ROW FORMAT SERDE
  'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'field.delim'='|',
  'serialization.format'='|')
STORED AS INPUTFORMAT
  'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat';

3、HDFS数据导入Hive表以及表修复

a.将数据传到HDFS

#创建文件夹
hadoop fs -mkdir -p /user/hive/warehouse/stg/test/bus_date=20191101 

hadoop fs -put /opt/datas/test.txt(要上传的文件) /user/hive/warehouse/stg/test/bus_date=20191101(上传的目标文件夹)

tips:
 若是需要将文件夹数据全部导入，记得在创建的时候少一级目录
eg: hadoop fs -put /root/data/a/b/c/d    /DATA/a/b/c/ 

b.若存在证明数据上传成功，若不存在执行以下命令

msck repair table stg.test;

4、本地数据导入Hive表

load data local inpath '/home/data/mr_341800_14_20.txt' into table agg_mr_grid_cell_d_ah7 partition(p_provincecode=834,p_date='20200621');

load data local inpath '/home/data/mro_fcn_0605_new.txt' into table agg_mro_fcn_cell_grid_d;

5、Hive提交脚本步骤

若hive.sql执行中会有三个参数，需要后期的变动，下面给出执行命令

hive -f /root/sql/ruu1.sql --hivevar province_id=844 --hivevar daySelect=`date +%Y%m%d -d '-9 days'`  --hivevar province_name='广东'

hive -f /root/sql/ruu1.sql -d province_id=844 -d daySelect=`date +%Y%m%d -d '-9 days'`  -d province_name='广东'

更多讲解在链接：

https://www.cnblogs.com/wqbin/p/10361045.html

提交步骤：

1）脚本调用
test.sql

use default;
select 
'${hiveconf:month}' as month
from default.user1;
use default;
select 
'${hivevar:month}' as month
from default.user1;


2）shell脚本
start.sh

#!/bin/sh
#ecoding=utf-8

set -x
month=