登录
当前位置： 首页 > 工具软件 > psql2csv > 使用案例 >

shell脚本---------数据库中数据导出到csv文件中

傅毅然
2023-12-01

需求：导出数据库中数据，生成csv文件（定时任务去做）

入口：扫表操作

扫表的sql：（polljob.sql）

select oid,jobId,batchCode,COALESCE(batchStartTime,'2000-01-01'),COALESCE(batchEndTime, '2030-01-01'),jobStatus,COALESCE(offsetOid,'0')
from T_MONEY_JOB 
WHERE workerPid is null and jobStatus in ('toRun');

 根据扫描出的内容，做具体的操作（jobpoller.sh）：

#!/bin/bash

basedir=`dirname $0`
cd $basedir
source $basedir/db.conf.sh

cat polljob.sql | $mysqlcli -N |
while IFS=$'\t' read oid jobId batchCode batchStartTime batchEndTime jobStatus offsetOid productOid
do
	args=("$jobId" "$oid" "$batchCode" "$batchStartTime" "$batchEndTime" "$jobStatus" "$offsetOid" "$productOid")
	script=csvgen.sh

	sh $script "${args[@]}"
done

进入到csvgen.sh脚本：

#!/bin/sh

key=$1
condition=$2
filetype=$key
basedir=`dirname $0`

confirmDate=$(date +"%Y-%m-%d" -d '-1 day')
startTime="${confirmDate} 00:00:00"
endTime="${confirmDate} 23:59:59"

[ $basedir == '.' ] && basedir=$PWD

datadir=$basedir/$key/data
logsdir=$basedir/$key/logs

# remove old files within one same minute
oldDate=$(date -d "last month" "+%Y%m%d")

if [ -d $datadir/$oldDate ];then
   rm -rf $datadir/$oldDate
   echo "完成删除动作"
   if [ -d $datadir/$oldDate ];then
     echo "删除失败"
   else
     echo "删除成功"
  fi
else
   echo "目录不存在"
fi

[ -d $datadir ] || mkdir -p $datadir
[ -d $logsdir ] || mkdir -p $logsdir

today=`date +%Y%m%d`
fileDate=`date +%Y-%m-%d`
batch="`date +%Y%m%d%H%M00`"
datadir=$datadir/$today/$batch
[ -d $datadir ] || mkdir -p $datadir

#filepre=$datadir/customer_$key_

shift

#args: jobId,batchCode,batchStart,batchEnd,jobStatus,offsetOid,productOid
jobId=$1
batchCode=$2
batchStart=$3
batchEnd=$4
jobStatus=$5
offsetOid=$6
productOid=$7
[ "x$offsetOid" = "x" ] && offsetOid=$(date +%s)
source $basedir/db.conf.sh


myfiles=("customer_order_")
[ "$key" = "income" ] && myfiles=("customer_income_")
[ "$key" = "order" ] && myfiles=("customer_order_")
[ "$key" = "hold" ] && myfiles=("customer_hold_")

for x in ${myfiles[@]}
do

	#hold
	if [ "$key" = "hold" ];then
	#csv
	holdSQLfile="hold_$confirmDate.sql"
	holdCSVfile="${x}${batch}_0.csv"
	notifyContent={'"'fileType'"':'"'hold'"','"'fileName'"':'"'$holdCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
	sed -e "s/#startTime/$startTime/g" -e "s/#endTime/$endTime/g" $basedir/hold.temp.sql >$datadir/$holdSQLfile
	$mysqlcli -N -r < $datadir/$holdSQLfile >$datadir/$holdCSVfile
	cat $datadir/$holdSQLfile
	echo "======================================="
	
	#log
	holdlogname="${x}${batch}_0.log"
	holdlogfile=$logsdir/$holdlogname
	echo $(date) start >> $holdlogfile
	echo "confirmDate=${confirmDate}, startTime=${startTime}, endTime=${endTime}" >> $holdlogfile
	echo $(date) end >> $holdlogfile
	
	#income
	elif [ "$key" = "income" ];then
	#csv
	incomeSQLfile="income_$confirmDate.sql"
	incomeCSVfile="${x}${batch}_0.csv"
	notifyContent={'"'fileType'"':'"'income'"','"'fileName'"':'"'$incomeCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
	sed -e "s/#startTime/$startTime/g" -e "s/#endTime/$endTime/g" $basedir/income.temp.sql >$datadir/$incomeSQLfile
	$mysqlcli -N -r < $datadir/$incomeSQLfile >$datadir/$incomeCSVfile
	cat $datadir/$incomeSQLfile
	echo $fileDate+"fileDate"
	echo "======================================="
		
	#log
	incomelogname="${x}${batch}_0.log"
	incomelogfile=$logsdir/$incomelogname
	echo $(date) start >> $incomelogfile
	echo "confirmDate=${confirmDate}, startTime=${startTime}, endTime=${endTime}" >> $incomelogfile
	echo $(date) end >> $incomelogfile
	
	elif [ "$key" = "order" ];then
	#csv
	orderSQLfile="order_$confirmDate.sql"
	orderCSVfile="${x}${batch}_0.csv"
	notifyContent={'"'fileType'"':'"'order'"','"'fileName'"':'"'$orderCSVfile'"','"'fileDate'"':'"'$fileDate'"','"'filePath'"':'"'$datadir'/"'}
	sed -e "s/#offsetOid/$offsetOid/g" $basedir/order.temp.sql >$datadir/$orderSQLfile
	$mysqlcli -N -r < $datadir/$orderSQLfile >$datadir/$orderCSVfile
	cat $datadir/$orderSQLfile
	echo "======================================="  
	
	#log
	orderlogname="${x}${batch}_0.log"
	orderlogfile=$logsdir/$orderlogname
	echo $(date) start >> $orderlogfile
	echo "confirmDate=${confirmDate}, startTime=${startTime}, endTime=${endTime}" >> $orderlogfile
	echo $(date) end >> $orderlogfile
	
	else
		echo "no command execute"
	fi
	
	#lock table
	sql="update t_money_job set jobStatus='finished' where oid='$condition' "
	$mysqlcli -e "$sql" 
	
	#save notify
	notifyOiduuid=`cat /proc/sys/kernel/random/uuid`
	notifyoid=`echo $notifyOiduuid | sed 's/-//g'`
	notifyIduuid=`cat /proc/sys/kernel/random/uuid`
	notifyId=`echo $notifyIduuid | sed 's/-//g'`
	notifyDate=`date "+%Y-%m-%d %H:%M:%S"`
	sqlnotify="insert into t_money_platform_notify(oid,notifyId,notifyType,notifyContent,errorCode,notifyStatus,notifyTimes,seqId,updateTime,createTime) values('$notifyoid','$notifyId','documentExport','$notifyContent','0','toConfirm','0','0','$notifyDate','$notifyDate')"
	$mysqlcli -e "$sqlnotify" 
done

echo "finished!!"

 

根据不同的条件去执行不同的sql：

hold.temp.sql：

SELECT 'productOid, investorOid,totalVolume,holdVolume,toConfirmInvestVolume,toConfirmRedeemVolume,redeemableHoldVolume,lockRedeemHoldVolume,expGoldVolume,totalInvestVolume,accruableHoldVolume,value,holdTotalIncome, totalBaseIncome,totalRewardIncome,holdYesterdayIncome,yesterdayBaseIncome,yesterdayRewardIncome,incomeAmount,redeemableIncome,lockIncome,confirmDate,expectIncome,expectIncomeExt,accountType,maxHoldVolume,dayRedeemVolume,dayInvestVolume,dayRedeemCount, productAlias, holdStatus, productType'
UNION ALL
SELECT 
  CONCAT( t1.productOid, ', ',
  t1.investorOid, ', ',
  TRUNCATE(t1.totalVolume * 100, 0), ', ',
  TRUNCATE(t1.holdVolume * 100, 0), ', ',
  TRUNCATE(t1.toConfirmInvestVolume * 100, 0), ', ',
  TRUNCATE(t1.toConfirmRedeemVolume * 100, 0), ', ',
  TRUNCATE(t1.redeemableHoldVolume * 100, 0), ', ',
  TRUNCATE(t1.lockRedeemHoldVolume * 100, 0), ', ',
  TRUNCATE(t1.expGoldVolume * 100, 0), ', ',
  TRUNCATE(t1.totalInvestVolume * 100, 0), ', ',
  TRUNCATE(t1.accruableHoldVolume * 100, 0), ', ',
  TRUNCATE(t1.value * 100, 0), ', ',
  TRUNCATE(t1.holdTotalIncome * 100, 0), ', ',
  TRUNCATE(t1.totalBaseIncome * 100, 0), ', ',
  TRUNCATE(t1.totalRewardIncome * 100, 0), ', ',
  TRUNCATE(t1.holdYesterdayIncome * 100, 0), ', ',
  TRUNCATE(t1.yesterdayBaseIncome * 100, 0), ', ',
  TRUNCATE(t1.yesterdayRewardIncome * 100, 0), ', ',
  TRUNCATE(t1.incomeAmount * 100, 0), ', ',
  TRUNCATE(t1.redeemableIncome * 100, 0), ', ',
  TRUNCATE(t1.lockIncome * 100, 0), ', ',
  IFNULL(t1.confirmDate,'(null)'), ', ',
  TRUNCATE(t1.expectIncome * 100, 0), ', ',
  TRUNCATE(t1.expectIncomeExt * 100, 0), ', ',
  t1.accountType, ', ',
  TRUNCATE(t1.maxHoldVolume * 100, 0), ', ',
  TRUNCATE(t1.dayRedeemVolume * 100, 0), ', ',
  TRUNCATE(t1.dayInvestVolume * 100, 0), ', ',
  t1.dayRedeemCount, ', ',
  t1.productAlias, ', ',
  t1.holdStatus, ', ',
  t2.type)
FROM
  `t_money_publisher_hold` t1,`t_gam_product` t2  WHERE t1.productOid=t2.oid

income.temp.sql:

SELECT 'productOid, investorOid, incomeAmount, confirmDate, beforeVolume, afterVolume,productType '
UNION ALL
SELECT 
  CONCAT( t1.productOid, ', ',
  t1.investorOid, ', ',
  TRUNCATE(t1.incomeAmount * 100, 0),', ',
  t1.confirmDate,', ',
  TRUNCATE(t1.accureVolume * 100, 0),', ',
  TRUNCATE((t1.accureVolume + t1.incomeAmount) * 100, 0), ', ',
  t2.type)
FROM
  `t_money_publisher_investor_holdincome` t1,`t_gam_product` t2 WHERE t1.confirmDate >= '#startTime' AND t1.confirmDate <= '#endTime' AND t1.productOid=t2.oid

order.temp.sql:

SELECT 'investorOid,orderCode, productOid,orderType,orderTime,orderStatus,orderAmount,productType'
UNION ALL
SELECT CONCAT( t1.investorOid, ', ' ,
 t1.`orderCode`, ', ', 
 t1.`productOid`, ', ', 
 t1.`orderType`, ', ', 
 t1.`orderTime`, ', ', 
 t1.`orderStatus`, ', ', 
 TRUNCATE(t1.`orderAmount` * 100, 0), ', ',
 t2.type)
FROM `t_money_investor_tradeorder` t1,`t_gam_product` t2 WHERE t1.publisherOffsetOid ='#offsetOid' AND t1.productOid=t2.oid

 

数据库配置文件：（db.conf.sh）

mydb=gh_mimosa
#mydb=gh_jz_mimosa
basedir=`dirname $0`
[ $basedir == '.' ] && basedir=$PWD
mysqlcli="mysql --defaults-extra-file=$basedir/mysqlclient.conf -N -r $mydb"

cid=2
token=5EA77F0752E2EF36973EB64D8730CC6

(mysql)

#!/bin/sh

db=gh_mimosa
[ $1 ] && db=$1
mysql --defaults-extra-file=mysqlclient.conf $db

(mysqlclient.conf)

[client]
host=172.16.110.12
user=root
password=root
default-character-set=utf8

 

全局配置文件：（crontab）-----定义定时任务

SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/bin:/usr/local/redis/bin
MAILTO=root
HOME=/

# For details see man 4 crontabs

# Example of job definition:
# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name command to be executed
*/5  *  *  *  * root /bin/bash /customer/gencmd/jobpoller.sh

 

转载于:https://my.oschina.net/u/3110937/blog/995123

 类似资料：

相关阅读

在Shell脚本中读取JSON数据php将csv文件导入到mysql数据库的方法从App Android导出CSV文件上的数据将数据从XML文件导入到SQL数据库使用sqlalchemy将csv文件加载到数据库中

相关文章

OrientDB导入数据库MySQL导出表数据OrientDB列出数据库mysql源码解读——数据到文件之数据DB2 RazorSQL复制到本地数据库

相关问答

Pandas未将数据帧导出到csv用于数据库备份的Linux shell脚本如何将CSV数据使用到Jeter webdrive脚本中？Spring Boot通过RESTendpoint将大型数据库导出到csvAndorid Room数据库如何导出数据库

相关文档

Phinx 数据库迁移中文文档数据库教程KDB+ 高性能列式数据库 中文教程Shell 脚本学习Access 数据库教程
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.