shell编写mysql抽取数据脚本

最新推荐文章于 2021-10-21 15:53:28 发布

转载最新推荐文章于 2021-10-21 15:53:28 发布 · 563 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/dayibagou/p/8065669.html

文章标签：

#数据库 #shell

本文介绍了一个具体的ETL(Extract, Transform, Load)过程，使用Shell脚本自动化从源数据库抽取数据，进行处理并加载到目标数据库。该流程包括数据截断、插入新数据、查询结果导出及导入等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#!/bin/bash

DT=`date +%Y%m%d` #当前日期
YESTERDAY=`date -d "yesterday" +%Y-%m-%d` #昨天，用于处理数据的日期

if [ ! -n "$1" ] ;then
echo "ETL Date:$YESTERDAY";
else
YESTERDAY=${1}
echo "ETL Date:$YESTERDAY";
fi

#AGENT_IDS="'NDM1MW1RME9HTmlaRGN5TWpjNU5mZTU4','MTI1Y2FEVXlOVEZtWlRVNFpqUTVOZjJiYQ==','MTdiYmN5T0RNM056VXlOMkV5TlRJOWNjNw==','N2RkZVlXVTJNemxoWTJJd05UaGtmYWJm','MTY4OGFPVGMzTkdRNU1ESXpZMkU0OGQxYg==','OTE4OG1Zak5rTm1JNU5URmlOekYxN2Y3Mw==','YTExN09UaG1aREl6TjJabE1EQTNmYTlh','DdlYzRhNWM5MTBhN','NlYTk5NjBjMDRjYz','MzJiMzFkZGEwODA0','mU2YWY0N2E1NWYxZ','c3NWJlZDVlYTZmNT','DI0MjQ3MWNkODk0Y','gxYzRhOTcyNzhlYz','FhMjBiNjM2YzI3Yj','MDc0NTM1NjljMTVk','dkYjU3MDgwODI4Mz','OTc2ZTc4YzBhNWVj','E2OWY5ZThmMGY3NT','xYjg3NzY2MWU5OWV','IwOTgxMjFlZDY5OG','WE3ZWIzZWU1YTMwY','WFlZjU0Njk2NTAwZ','2MGE1MDNmNTBkZmY','4ZDM2OWQ1ZGEwOTY','DVlODAyYzA0ZTc0Z','GNlNjMwMjIyZDA1Z','1YTRiODZlZGY2NzB','U4M2ZjYzQ4YzJlMT','OWFlZGYzNGNmODI3','BmYzUyMDBiNTU3Yz','1ODY4MzVjNmJhNDM','MGI5NTcxNzUzNDQ3','DMxYTk3ZjYxNjk2Z','Q4ZTI4ZDAyMzA3M2','YzEwMDBlYWI1N2U0','DlmNTgyNGQzMzVjZ','2ZiOGQ1MGQ3ZmRjN'"

QUY_HOST=*****.com
QUY_DB=****
QUY_USER=****
QUY_PWD=*****

mysql -h$QUY_HOST $QUY_DB -u$QUY_USER -p$QUY_PWD << EOF
truncate TABLE ros_query_new_everyday;
insert into ros_query_new_everyday(id,query_date,device_id,agent_id,agent_code,agent_name) select id,query_date,device_id,agent_id,agent_code,agent_name from ros_query_new a where a.query_time between '$YESTERDAY 00:00:00' and '$YESTERDAY 23:59：59' and result_status = 0;
EOF

# 抽取文本query
mysql -h$QUY_HOST $QUY_DB -u$QUY_USER -p$QUY_PWD --default-character-set=utf8 --skip-column-names -B -e "select agent_id,agent_code,agent_name,device_id,query_date,count(*),'TEXT' query_type from ros_query_new_everyday where query_date ='$YESTERDAY' and agent_code in(select app_id from stat_agent_code) group by agent_code,device_id;" > etl_text_query_$YESTERDAY.txt
echo 'querys succeeded.'

ECP_HOST=****
ECP_USER=***
ECP_PWD=*****
ECP_DATABASE=*****

mysql -h$ECP_HOST $ECP_DATABASE -u$ECP_USER -p$ECP_PWD << EOF
DELETE FROM tp_storybox_querys WHERE query_date='$YESTERDAY';
LOAD DATA LOCAL INFILE 'etl_text_query_$YESTERDAY.txt' INTO TABLE tp_storybox_querys(agent_id,agent_code,agent_name,client_id,query_date,query_count,query_type);
DELETE FROM tp_storybox_querys WHERE query_date='$YESTERDAY' and client_id NOT IN(SELECT device_id FROM tp_storybox_active_log);
EOF

echo 'ETL succeeded.'

转载于:https://www.cnblogs.com/dayibagou/p/8065669.html