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