该脚本展示了如何使用bash命令行删除一系列ClickHouse中的视图，并在删除前进行数据检查，确保数据已被完全清除。同时，它还涉及到了删除MySQL中的关联表数据。整个过程包含了错误处理和循环等待，以确保数据操作的完整性和安全性。
删除视图表
function deleteViewTable() {
# 声明要删除的表
tableList=“keepa_view.v4_country_weekend keepa_view.v4_country_class keepa_view.v4_country_class30 keepa_view.v4_analysis_of_attribute_name
keepa_view.v4_analysis_of_attribute_name_detail keepa_view.v4_analysis_of_the_release_time keepa_view.v4_analysis_of_the_release_time_detail
keepa_view.v4_analysis_of_the_price_step keepa_view.v4_analysis_of_the_price_step_detail keepa_view.v4_analysis_of_the_rating_step
keepa_view.v4_analysis_of_the_rating_step_detail keepa_view.v4_analysis_of_the_review_step keepa_view.v4_analysis_of_the_review_step_detail
keepa_view.v4_analysis_of_the_score keepa_view.v4_analysis_of_the_score_detail keepa_view.v4_analysis_of_the_top_brand keepa_view.v4_analysis_of_the_top_asin”
for table in
t
a
b
l
e
L
i
s
t
d
o
e
c
h
o
"
删除视图表
[
tableList do echo "删除视图表[
tableListdoecho"删除视图表[{$table}]开始"
clickhouse-client -h ${clickhouseHost} -u
c
l
i
c
k
h
o
u
s
e
U
s
e
r
−
−
p
a
s
s
w
o
r
d
"
{clickhouseUser} --password "
clickhouseUser−−password"{clickhousePwd}" --query=“ALTER TABLE
t
a
b
l
e
D
E
L
E
T
E
W
H
E
R
E
s
e
a
r
c
h
i
d
=
′
{table} DELETE WHERE search_id = '
tableDELETEWHEREsearchid=′searchId’ and domain_id = '
d
o
m
a
i
n
I
d
′
"
;
w
h
i
l
e
(
(
domainId'"; while ((
domainId′";while(((clickhouse-client -h ${clickhouseHost} -u
c
l
i
c
k
h
o
u
s
e
U
s
e
r
−
−
p
a
s
s
w
o
r
d
"
{clickhouseUser} --password "
clickhouseUser−−password"{clickhousePwd}” --query=“select count(1) total from
t
a
b
l
e
W
H
E
R
E
s
e
a
r
c
h
i
d
=
′
{table} WHERE search_id = '
tableWHEREsearchid=′searchId’ and domain_id = '
d
o
m
a
i
n
I
d
′
"
)
>
0
)
)
d
o
e
c
h
o
"
数据未删除完成，
s
l
e
e
p
3
秒
t
a
b
l
e
=
"
domainId'") > 0)) do echo "数据未删除完成，sleep 3 秒 table="
domainId′")>0))doecho"数据未删除完成，sleep3秒table="{table}
sleep 3
done
echo “删除视图表[KaTeX parse error: Expected '}', got 'EOF' at end of input: {table}]结束”
done
echo “删除差评关联表[keepa_bi.bad_review_rel]”
clickhouse-client -h ${clickhouseHost} -u
c
l
i
c
k
h
o
u
s
e
U
s
e
r
−
−
p
a
s
s
w
o
r
d
"
{clickhouseUser} --password "
clickhouseUser−−password"{clickhousePwd}” --query=“ALTER TABLE keepa_bi.bad_review_rel DELETE WHERE search_id = $searchId”;
echo “删除mysql差评关联表[keepa.keepa_bad_review_rel]”
mysql -u KaTeX parse error: Expected group as argument to '\'' at end of input: …ysqlUser} -p \'{mysqlPwd}’ -P${mysqlPort} -e “delete from keepa.keepa_bad_review_rel where search_id = $searchId;”;
}
#!/bin/bash
set -o errexit
echo search_id:$1;
echo domain_id:$2;
tableList=(“test.calc_asins” “test.asin_classification_attribute”)
for table in test.calc_asins test.asin_classification_attribute
do
echo “开始删除数据table=”${table}
clickhouse-client -u default --password ‘RsrsJGiX#wK6k3uY’ --query=“ALTER TABLE ${table} DELETE WHERE search_id = ‘$1’ and domain_id = ‘$2’”;
while (($(clickhouse-client -u default --password 'RsrsJGiX#wK6k3uY' --query="select count(1) total from ${table} WHERE search_id = '$1' and domain_id = '$2'") > 0))
do
echo "数据未删除完成，sleep 2 秒 table="${table}
sleep 2
done
echo "删除数据完成table="${table}
done
echo “run successful”
#!/bin/bash
star=date +'%Y-%m-%d %H:%M:%S'
echo $(date “+%Y-%m-%d %H:%M:%S”);
echo search_id:$1;
echo domain_id:$2;
声明要删除的表
tableList=“keepa_view.v4_country_weekend keepa_view.v4_country_class keepa_view.v4_country_class30 keepa_view.v4_analysis_of_attribute_name
keepa_view.v4_analysis_of_attribute_name_detail keepa_view.v4_analysis_of_the_release_time keepa_view.v4_analysis_of_the_release_time_detail
keepa_view.v4_analysis_of_the_price_step keepa_view.v4_analysis_of_the_price_step_detail keepa_view.v4_analysis_of_the_rating_step
keepa_view.v4_analysis_of_the_rating_step_detail keepa_view.v4_analysis_of_the_review_step keepa_view.v4_analysis_of_the_review_step_detail
keepa_view.v4_analysis_of_the_score keepa_view.v4_analysis_of_the_score_detail keepa_view.v4_analysis_of_the_top_brand keepa_view.v4_analysis_of_the_top_asin”
for table in
t
a
b
l
e
L
i
s
t
d
o
e
c
h
o
"
删除视图表
[
tableList do echo "删除视图表[
tableListdoecho"删除视图表[{table}]开始"
clickhouse-client --query="ALTER TABLE ${table} DELETE WHERE search_id = $1 and domain_id =
2
"
;
w
h
i
l
e
(
(
2"; while ((
2";while(((clickhouse-client --query="select count(1) total from ${table} WHERE search_id = $1 and domain_id =
2
"
)
>
0
)
)
d
o
e
c
h
o
"
数据未删除完成，
s
l
e
e
p
3
秒
t
a
b
l
e
=
"
2") > 0)) do echo "数据未删除完成，sleep 3 秒 table="
2")>0))doecho"数据未删除完成，sleep3秒table="{table}
sleep 3
done
echo “删除视图表[${table}]结束”
done
echo “删除差评关联表[keepa_bi.bad_review_rel]”
clickhouse-client --query=“ALTER TABLE keepa_bi.bad_review_rel DELETE WHERE search_id = $1”;
echo “删除mysql差评关联表[keepa.keepa_bad_review_rel]”
mysql -u keepa -p’342AAff1!@##asd’ -e “delete from keepa.keepa_bad_review_rel where search_id = $1;”;
f_flag=mysql -h172.18.6.233 -udcin_dmp -pdmp20230218 -e "select count(1) cnt from scheduler.bdpstudio_flag where etl_date=$1 and task_id='dm_acct_wt_user_boci_tag1_df'"
flag=echo $f_flag|cut -d' ' -f2
echo
f
l
a
g
w
h
i
l
e
(
(
flag while ((
flagwhile((flag < 1))
do
echo $flag
sleep 600
f_flag=mysql -h172.18.6.233 -udcin_dmp -pdmp20230218 -e "select count(1) cnt from scheduler.bdpstudio_flag where etl_date=$1 and task_id='dws_ast_assets_df'"
flag=echo $f_flag|cut -d' ' -f2
done
echo “开始执行”
863
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
