SQL相关

1. 大表查找

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='tb_tablename'
order by data_length desc, index_length desc;

2. 行转列-生成日期区间列表

SELECT
    DATE_ADD("2022-06-23", idx) AS date_range
FROM
    (
      SELECT 1
    )
x lateral VIEW posexplode(split(space(DATEDIFF("2022-06-30", "2022-06-23")), " ")) tt AS idx,
			v

Shell相关

1. 日志量统计，用于日志精简

# 按行数统计，格式：行数 代码位置
cat info.log.2020-05-06| grep -E 'INFO|ERROR|WARN'|  awk '{print $3}'| sort| uniq -c| sort -k1nr| head -100

# 按字符数统计，格式：字符数 代码位置
cat info.log.2020-05-06| grep -E 'INFO|ERROR|WARN'|  awk '{print  $3 "=" length($0);}'| sort| awk -F '=' 'BEGIN {len=0;}  {map[$1]=map[$1]+$2;} END {for(key in map){print map[key], key} }' | sort -k1nr| head -100