Deepgreen(Greenplum)数据表集中Analyze脚本

阅读原文请点击：http://click.aliyun.com/m/23008/
摘要： 数据加载完成以后，通常需要更新分析计划，因为单独做一个表的Analyze比较麻烦，整库做Analyze会比较耗时，我们可以通过下面这个脚本，指定Analyze部分表。 具体实践时，只需修改下面对应的SQL语句、数据库psql命令地址、IP端口及数据库名即可： #!/bin/bash # filename: analyze_table.

数据加载完成以后，通常需要更新分析计划，因为单独做一个表的Analyze比较麻烦，整库做Analyze会比较耗时，我们可以通过下面这个脚本，指定Analyze部分表。
具体实践时，只需修改下面对应的SQL语句、数据库psql命令地址、IP端口及数据库名即可：

#!/bin/bash
# filename: analyze_table.sh
# start time
start_time=$(date)
echo "-------- Start time is $start_time --------"
start_seconds=$(date +%s)
# get no partition tables
cmd_get_nopartitions="select schemaname||'.'||tablename as tablename from pg_tables where schemaname like 'rwnas_%' and tablename not like '%_1_prt_%' and schemaname||'.'||tablename not in (select schemaname||'.'||tablename from pg_partitions group by schemaname,tablename)"
exec_get_nopartitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_nopartitions}")
echo "${exec_get_nopartitions}" > nopartitions_tables.txt
echo "-------- No Partitions Tables List Below --------"
nopartitions_file=$(cat nopartitions_tables.txt)
echo "$nopartitions_file"
# analyze nopartition tables
rm -rf analyze_nopartitions_tables.txt
function for_np_file(){
for i in $nopartitions_file
do
echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $i")"
echo "$i" >> analyze_nopartitions_tables.txt
done
}
for_np_file
# get partition tables
cmd_get_partitions="select schemaname||'.'||tablename as tablename from pg_partitions where schemaname like 'rwnas_%' group by schemaname, tablename"
exec_get_partitions=$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -t -c "${cmd_get_partitions}")
echo "${exec_get_partitions}" > partitions_tables.txt
echo "-------- Partitions Tables List Below --------"
partitions_file=$(cat partitions_tables.txt)
echo "$partitions_file"
# analyze partition tables
rm -rf analyze_partitions_tables.txt
function for_p_file(){
for q in $partitions_file
do
echo "$(/app/greenplum-db-4.1.1.1/bin/psql -h 127.0.0.1 -p 5432 -d rwnas -U rwnas -c "analyze $q")"
echo "$q" >> analyze_partitions_tables.txt
done
}
for_p_file
# end time
end_time=$(date)
echo "-------- End time is $end_time --------"
end_seconds=$(date +%s)
diff=$((end_seconds - start_seconds))
echo "Total $diff seconds."
阅读原文请点击：http://click.aliyun.com/m/23008/