对于数据量的统计，从表是否分区分为分区表和非分区表两者有着不同的统计方式

非分区表

1. 利用传统方法count

2. 利用元数据计算：

select
sum(tb.param_value) AS TOTAL
from sys.tbls t
left join sys.dbs d
on t.db_id = d.db_id
left join sys.table_params tb
on t.tbl_id = tb.tbl_id
where
tb.param_key='numRows' 
and
d.name='dw'
and (t.tbl_name ='ods_pre_t_glj_nb_road_s')

但是有时候会出现特殊情况，有非分区表的numrows为0

此时需要执行：

ANALYZE TABLE dw.ods_pre_t_lwzx_zdsj COMPUTE STATISTICS;

重新再次执行SQL语句：numRows已经有数据了

分区表

select
    sum(e.PARAM_VALUE) as numRows
    from sys.TBLS t
    left join sys.DBS d
    on t.DB_ID = d.DB_ID
    left join sys.PARTITIONS a 
    on t.TBL_ID=a.TBL_ID
    left join sys.PARTITION_PARAMS e 
    on a.part_id=e.part_id
    where t.TBL_NAME='ods_pre_tbl_ex_waste'
    AND e.PARAM_KEY='numRows'

同样也会出现统计数量为0或NULL或者数据量缺少的情况，此时同样需要执行

ANALYZE TABLE dw.ods_pre_t_lwzx_zdsj COMPUTE STATISTICS;

ANALYZE TABLE

ANALYZE TABLE是什么？为什么每次元数据信息统计时总会出现个别统计不准确的情况？

ANALYZE TABLE 是 Hive 中用于收集表或分区统计信息的命令。它的作用是通过扫描数据文件来计算表或分区的关键统计信息，例如行数、数据大小、列值分布等。这些统计信息存储在 Hive 的元数据中，用于优化查询计划。

统计不准确的原因有很多，分区未被正确扫描、数据未完全加载或变动后未重新统计、数据文件格式的限制等等。

优化后的SQL：

	SELECT 
		a.db_name,a.tbl_id,a.tbl_name,IF(tb.param_value IS NULL,b.numRows,tb.param_value) as numRows
	FROM
	(
	-- 选出自己需要统计的表名
	select
		d.name as db_name,
		tbl_id,
		tbl_name
	from
		sys.TBLS t
	left join sys.DBS d
	where
		d.name = 'dw'
		and tbl_name like 'ods%'
	) a
	-- 关联表参数统计表去获取行数
	LEFT JOIN (SELECT tbl_id,param_value from sys.table_params where param_key='numRows') tb
	ON a.tbl_id=tb.tbl_id
	LEFT JOIN 
	(
	-- 关联分区统计表去获取行数
		    select 
		    a.tbl_id,SUM(PARAM_VALUE) as numRows
		    from  sys.TBLS t
		    left join sys.DBS d
		    on t.DB_ID = d.DB_ID
		    LEFT join
		    	sys.PARTITIONS a
		    on a.tbl_id=t.tbl_id
		    left join 
			    sys.PARTITION_PARAMS b
		    on a.part_id=b.part_id
		    where d.name='dw'
			and b.param_key='numRows' 
			and t.tbl_name like 'ods%'
		    GROUP BY a.tbl_id
	) b
	ON a.tbl_id=b.tbl_id

没有办法去批量的analyze表，可以写个shell脚本，执行以上优化后的SQL，将查询结果为null的表执行analyze以及所有分区表analyze后，再执行优化后的SQL。

#!/bin/bash

# 定义数据库名称和连接信息
DATABASE_NAME="dw"
OUTPUT_FILE="/tmp/tables_to_analyze.txt"
BEELINE_URL="jdbc:hive2://xxx.xxx.xxx.xxx:10000/default"
USERNAME="hive用户名称"
PASSWORD="hive用户密码"

#beeline -u "jdbc:hive2://xxx.xxx.xxx.xxx:10000/default;" -n hive -p hive -e 
# 1. 查询需要 ANALYZE 的表
beeline -u "${BEELINE_URL}" -n "${USERNAME}" -p "${PASSWORD}" --silent=true --outputformat=csv2 -e "
SET hive.cli.print.header=false;
SELECT CONCAT(db_name, '.', tbl_name) AS full_table_name
FROM (
    SELECT 
        a.db_name, a.tbl_name, 
        COALESCE(tb.param_value, b.numRows) AS numRows
    FROM (
        SELECT d.name AS db_name, t.tbl_name, t.tbl_id
        FROM sys.TBLS t
        LEFT JOIN sys.DBS d ON t.db_id = d.db_id
        WHERE d.name = '${DATABASE_NAME}' AND t.tbl_name LIKE 'ods%'
    ) a
    LEFT JOIN (SELECT tbl_id, param_value FROM sys.table_params WHERE param_key='numRows') tb 
        ON a.tbl_id = tb.tbl_id
    LEFT JOIN (
        SELECT 
            a.tbl_id, SUM(b.param_value) AS numRows
        FROM sys.PARTITIONS a
        LEFT JOIN sys.PARTITION_PARAMS b ON a.part_id = b.part_id
        WHERE b.param_key = 'numRows'
        GROUP BY a.tbl_id
    ) b ON a.tbl_id = b.tbl_id
) c
WHERE numRows IS NULL;
" > "${OUTPUT_FILE}"

# 2. 执行 ANALYZE TABLE 操作
if [[ -s "${OUTPUT_FILE}" ]]; then
    echo "Starting ANALYZE TABLE for the following tables:"
    cat "${OUTPUT_FILE}"

    while IFS= read -r table_name; do
        echo "Analyzing table: ${table_name}"
        
        # 执行 ANALYZE TABLE
        beeline -u "${BEELINE_URL}" -n "${USERNAME}" -p "${PASSWORD}" --silent=true -e "ANALYZE TABLE ${table_name} COMPUTE STATISTICS;" 
        
        if [[ $? -ne 0 ]]; then
            echo "Error analyzing table: ${table_name}"
        else
            echo "Successfully analyzed table: ${table_name}"
        fi
    done < "${OUTPUT_FILE}"
else
    echo "No tables require ANALYZE."
fi

# 清理临时文件
rm -f "${OUTPUT_FILE}"

#执行数据量统计插入
beeline -u "${BEELINE_URL}" -n "${USERNAME}" -p "${PASSWORD}" -e "
set tez.queue.name = other;
INSERT INTO dw.dwd_daily_data_warehouse_total(tbl_name,rq,total)
SELECT 
tbl_name,date_sub(current_date,1),numRows
FROM
(
	SELECT 
		a.db_name,a.tbl_id,a.tbl_name,IF(tb.param_value IS NULL,b.numRows,tb.param_value) as numRows
	FROM
	(
	-- 选出自己需要统计的表名
	select
		d.name as db_name,
		tbl_id,
		tbl_name
	from
		sys.TBLS t
	left join sys.DBS d
	where
		d.name = 'dw'
		and tbl_name like 'ods%'
	) a
	-- 关联表参数统计表去获取行数
	LEFT JOIN (SELECT tbl_id,param_value from sys.table_params where param_key='numRows') tb
	ON a.tbl_id=tb.tbl_id
	LEFT JOIN 
	(
	-- 关联分区统计表去获取行数
		    select 
		    a.tbl_id,SUM(PARAM_VALUE) as numRows
		    from  sys.TBLS t
		    left join sys.DBS d
		    on t.DB_ID = d.DB_ID
		    LEFT join
		    	sys.PARTITIONS a
		    on a.tbl_id=t.tbl_id
		    left join 
			    sys.PARTITION_PARAMS b
		    on a.part_id=b.part_id
		    where d.name='dw'
			and b.param_key='numRows' 
			and t.tbl_name like 'ods%'
		    GROUP BY a.tbl_id
	) b
	ON a.tbl_id=b.tbl_id
) t;"