SQL系列（三）SQL使用的旁枝末节
SQL系列（三）SQL使用的旁枝末节
首先，来揭晓上期的答案。方法不唯一，符合结果即可～
with temp as
(
select 2 as st，5 as en
union all
select 11 as st，9 as en
)
select
st
,en
,collect_list(numbers_end) as result -- 得到等差数组
from
(
select
st
,en
,if(st<en,int(numbers+rn),int(numbers-rn)) as numbers_end -- 构造等差数值
from
(
select
st
,en
,numbers
,row_number() over(partition by st order by st) -1 as rn -- 构造排序字段辅助计算
from
(-- 构造起始差长度+1的重复序列
select
st
,en
,regexp_extract(repeat(concat(st,','),int(abs(en-st)+1)),'(.*),',1) as number_repeat_list
from
temp
)a
lateral view explode(split(number_repeat_list,',')) t as numbers -- 将重复序列展开成多行
)a
)a
group by
st
,en
虽然日常中使用最多的是查询，但在数字基建中，增删改也是必不可少的。
Hive的DDL
创建表
-- 创建表模版
use temp;
CREATE TABLE hh_teachr_price
(
teacherid string comment "教师ID"
,task_type string comment "任务类型"
,price string comment "基础单价"
)
PARTITIONED BY (`dt` string) -- 含分区
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim'='\t', -- 字段分隔符
'serialization.format'='\t')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
;
增删改"插"
-- 新增字段 -- 含分区的表需加上关键字CASCADE，联级改变历史分区文件结构
alter table temp.hh_teachr_price add columns(type string comment '题型', course string comment '科目') CASCADE;
-- 删除字段
alter table temp.hh_teachr_price replace columns (teacherid string, task_type string); -- 删除price
-- 修改列名
alter table temp.hh_teachr_price CHANGE COLUMN task_type task_type_new string comment '任务特征';
-- 修改注释
alter table temp.hh_teachr_price CHANGE COLUMN task_type task_type string comment '任务特征2';
-- 修改分隔符类型
alter table temp.hh_teachr_price set serdeproperties('field.delim'=',', 'serialization.format'=',');
-- 插入数据
insert overwrite table temp.hh_teachr_price partition (dt='${date}') -- 覆盖写入
select ...
insert into table temp.hh_teachr_price partition (dt='${date}') -- 追加写入
select ...
MySQL的DDL
创建表
-- 创建表模版
use temp;
CREATE TABLE `question_nums`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键'
,`dt` varchar(30) NOT NULL DEFAULT 'total_count' COMMENT '日期'
,`phase` varchar(30) NOT NULL DEFAULT 'total_count' COMMENT '学段'
,`nums` int(30) NOT NULL DEFAULT '0' COMMENT '数量'
,`dbctime` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间'
,`dbutime` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间'
,PRIMARY KEY (`id`)
,UNIQUE KEY `u_key` (`dt`,`phase`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT '题目数量'
增删改
-- 合并为一条语句
alter table question_nums
add column `language` varchar(30) not null default 'total_count' comment '语言' -- 增加维度
,add column `new_nums` int(30) not null default '0' comment '新增数量' -- 增加度量
,drop column `new_nums` -- 删除字段
,modify column `nums` decimal(10,1) not null default '0' COMMENT '数量' -- 只修改字段类型
,change `nums` `nums_new` int(30) not null default '0' COMMENT '数量' -- 修改字段名称和类型
,drop index `u_key` -- 删除索引
,add unique key `u_key` (`dt`,`phase`,`language`); -- 增加索引
Hive其他操作
服务器查看hdfs文件
# hdfs dfs 与 hadoop fs 用法相通
hdfs dfs -ls 列出路径下的所有文件和文件夹（统计分区数量）
hdfs dfs -cat 查看文件中的内容
hdfs dfs -text 查看文件中的内容
hdfs dfs -text /app/20190909/* |grep channel_id=14764618 正则查找
hdfs dfs -text /app/20190909/* | head[tail] -n 5 展示前[后]5行
hdfs dfs -du -s -h /app/20190909/ 整体文件大小
hdfs dfs -du -s -h /app/20190909/* 各分区文件大小
hdfs dfs -cat /app/20190909/* | wc -l 查看文件行数
Hive参数设置
-- 参数设置
set hive.new.job.grouping.set.cardinality =256; -- 维度组合数2的n次方
set mapreduce.reduce.memory.mb = 8192; -- reduce大小
set mapreduce.map.memory.mb = 8192; -- map大小
set hive.exec.parallel=true; -- 并发
set hive.strict.checks.large.query = false; -- 非严格限制
set hive.mapred.mode = nonstrict; -- 非严格限制
set hive.cli.print.header=true; -- 显示列名
set hive.resultset.use.unique.column.names=false; -- 不显示库表前缀
这里重点说一下严格模式和非严格模式：Hive在严格模式下查询时，需指定分区（查询分区表时），排序后需增加limit限制，不支持笛卡尔积查询。防止误操作等造成的资源浪费。
在严格模式下需指定分区，避免全盘扫面带来的浪费
分区简单理解就是文件夹，例如按照日期建立多个文件夹，每个日期的数据存在相应的文件夹下。此时的分区字段就是日期。例如：
select * from temp1 where dt = current_date(dt, -1) -- 指定分区为昨日
在严格模式下需限制排序数量，避免全局排序造成的浪费
如果排序完后要获取所有数据，可以将
limit设置很大，例如：select * from temp1 where dt = current_date(dt, -1) -- 指定分区为昨日 order by age limit 100000000 -- 增加limit限制
在严格模式下不支持笛卡尔积，主要防止分析师的不当操作，造成资源大量浪费
可以增加关联字段代替笛卡尔积查询方式，如果还会报错，可以设置参数开启非严格模式，例如：
-- 开启非严格模式 -- 理论上不开启下述代码也能正常运行 set hive.strict.checks.large.query = false; set hive.mapred.mode = nonstrict; select col1 ,col2 from ( select 1 as connect ,col1 from tamp1 )t1 left join ( select 1 as connect ,col2 from tamp1 )t2 on t1.connect=t2.connect -- 1关联1形成笛卡尔积
客户端交互命令
# -e 执行sql语句
hive -e "select * from temp.hh_teachr_price limit 100;"
# -f 执行sql脚本
hive -f /app/mydata/hh_teachr_price.sql
# 服务器cli上输出文件
hive -e"set hive.cli.print.header=true; -- 显示列名
set hive.resultset.use.unique.column.names=false; -- 不显示库表前缀
select * from temp.hh_teachr_price limit 100;" | sed 's/[\t]/,/g' >hh_teachr_price.csv
# hive集群输出文件 无法带标题
set hive.cli.print.header=true; -- 显示列名
set hive.resultset.use.unique.column.names=false; -- 不显示库表前缀
insert overwrite local directory '/home/data/hh_teachr_price'
row format delimited fields terminated by ','
select * from temp.hh_teachr_price limit 100;
日常爬坑
- 长整型字段与字符串字段关联，两个默认会转为double型，有时会因为超出长度结尾数字变为0造成匹配错误（一对多）。可以将长整型字段转化为字符串再关联。
-- 结尾溢出造成错误匹配上了
select
a.id
,b.id
from
(
select
bigint(190000000002778025) as id
)a
left join
(
select '190000000002778023' as id
)b on a.id=b.id
- 非数值型字符串与数字/数值型字符串比较的区别
select
-- 非数值型字符串与数字比较，会造成结果为null
'abc' != 2 as a -- null
,'' != 2 as c -- null
-- 将数字转为数值型字符串即可
,'abc' != '2' as b -- true
,'' != '2' as d -- true
- 字段含有null值，会导致explode丢失数据
-- 因为null造成A类型的数据丢失
select
ids
,id
,dtype
from
(
select
null as ids
,'A' as dtype
union all
select
'3,4,6' as ids
,'B' as dtype
)a
lateral view explode(split(ids,',')) t as id
总结
至此，SQL系列已经全部结束，但是需求是无穷的。道阻且长，且行且珍惜～
共勉～
#数据分析教程##数据分析师工作#数据分析实用手册 文章被收录于专栏
微信知乎搜索HsuHeinrich，第一时间更新～ 最真实的数据分析教程，SQL、Python、Tableau、Excel、数据基建、BI报表、埋点、专题分析、数据挖掘、A/B试验。日常分析师涉及的方方面面，这里都有～
