Mwalker has submitted this change and it was merged. Change subject: Move silverpop_export into new directory, and add supporting files. update.sh is the script to run for usual updates. ......................................................................
Move silverpop_export into new directory, and add supporting files. update.sh is the script to run for usual updates. Change-Id: I65a81058fecafe33c538fbc5ea9b6a0983174170 --- A silverpop_export/export_all.sql A silverpop_export/export_unsubscribes.sql A silverpop_export/update.sh R silverpop_export/update_table.sql 4 files changed, 43 insertions(+), 28 deletions(-) Approvals: Mwalker: Looks good to me, approved jenkins-bot: Verified diff --git a/silverpop_export/export_all.sql b/silverpop_export/export_all.sql new file mode 100755 index 0000000..adc2b14 --- /dev/null +++ b/silverpop_export/export_all.sql @@ -0,0 +1,17 @@ +SELECT contact_id ContactID, email, first_name firstname, last_name lastname, + last_ctid ContributionID, country, SUBSTRING(preferred_language, 1, 2) IsoLang, + IF(has_recurred_donation, 'TRUE', 'FALSE') has_recurred_donation, + highest_usd_amount, lifetime_usd_total, + DATE_FORMAT(latest_donation, '%m/%d/%Y') latest_donation_date, + latest_usd_amount, latest_currency, latest_native_amount, + tzoffset timezone, donation_count, + IF(is_2006_donor, 'TRUE', 'FALSE') is_2006_donor, + IF(is_2007_donor, 'TRUE', 'FALSE') is_2007_donor, + IF(is_2008_donor, 'TRUE', 'FALSE') is_2008_donor, + IF(is_2009_donor, 'TRUE', 'FALSE') is_2009_donor, + IF(is_2010_donor, 'TRUE', 'FALSE') is_2010_donor, + IF(is_2011_donor, 'TRUE', 'FALSE') is_2011_donor, + IF(is_2012_donor, 'TRUE', 'FALSE') is_2012_donor, + IF(is_2013_donor, 'TRUE', 'FALSE') is_2013_donor, + unsub_hash +FROM silverpop_export; \ No newline at end of file diff --git a/silverpop_export/export_unsubscribes.sql b/silverpop_export/export_unsubscribes.sql new file mode 100755 index 0000000..c666f38 --- /dev/null +++ b/silverpop_export/export_unsubscribes.sql @@ -0,0 +1 @@ +SELECT email FROM silverpop_export WHERE opted_out=1; \ No newline at end of file diff --git a/silverpop_export/update.sh b/silverpop_export/update.sh new file mode 100755 index 0000000..012a22c --- /dev/null +++ b/silverpop_export/update.sh @@ -0,0 +1,23 @@ +#!/usr/bin/env sh +echo "Start" >> log.log +date >> log.log + +echo "Regenerating db table" +mysql mwalker < update_table.sql > /dev/null + +echo "Exporting whole table" +mysql mwalker < export_all.sql > DatabaseUpdate.tsv + +echo "Exporting unsubscribes" +mysql mwalker < export_unsubscribes.sql > Unsubscribes.tsv + +echo "Archiving files" +DATE=$(date +"%Y%m%d_%H%M") +cp DatabaseUpdate.tsv old/full_$DATE.tsv +cp Unsubscribes.tsv old/unsub_$DATE.tsv + +# These are long running, so maybe send email when it's done +# echo "Now go put files on the server" | mail -s "Silverpop export job finished" [email protected] + +echo "End" >> log.log +date >> log.log \ No newline at end of file diff --git a/silverpop_export.sql b/silverpop_export/update_table.sql similarity index 87% rename from silverpop_export.sql rename to silverpop_export/update_table.sql index 60a6226..bdcba9c 100755 --- a/silverpop_export.sql +++ b/silverpop_export/update_table.sql @@ -1,3 +1,5 @@ +-- Updates the silverpop_export table + SET autocommit = 1; DROP TABLE IF EXISTS silverpop_export_dedupe_email; @@ -282,31 +284,3 @@ UPDATE silverpop_export ex SET unsub_hash = SHA1(CONCAT(last_ctid, email, XXX)) WHERE ex.opted_out = 0; - --- Export some random rows --- Run something like this from the command line like so... --- mysql -h db1008.eqiad.wmnet mwalker < silverpop_export.sql | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > out.csv -SELECT contact_id ContactID, email, first_name firstname, last_name lastname, - last_ctid ContributionID, country, SUBSTRING(preferred_language, 1, 2) IsoLang, - IF(has_recurred_donation, 'TRUE', 'FALSE') has_recurred_donation, - highest_usd_amount, lifetime_usd_total, - DATE_FORMAT(latest_donation, '%m/%d/%Y') latest_donation_date, - latest_usd_amount, latest_currency, latest_native_amount, - tzoffset timezone, donation_count, - IF(is_2006_donor, 'TRUE', 'FALSE') is_2006_donor, - IF(is_2007_donor, 'TRUE', 'FALSE') is_2007_donor, - IF(is_2008_donor, 'TRUE', 'FALSE') is_2008_donor, - IF(is_2009_donor, 'TRUE', 'FALSE') is_2009_donor, - IF(is_2010_donor, 'TRUE', 'FALSE') is_2010_donor, - IF(is_2011_donor, 'TRUE', 'FALSE') is_2011_donor, - IF(is_2012_donor, 'TRUE', 'FALSE') is_2012_donor, - IF(is_2013_donor, 'TRUE', 'FALSE') is_2013_donor, - unsub_hash -FROM silverpop_export AS r1 JOIN - (SELECT (RAND() * (SELECT MAX(id) FROM silverpop_export)) AS id) AS r2 - WHERE r1.id >= r2.id - ORDER BY r1.id ASC - LIMIT 1000; - --- Also export the unsubscribes -SELECT email FROM silverpop_export WHERE opted_out=1 LIMIT 1000; -- To view, visit https://gerrit.wikimedia.org/r/108528 To unsubscribe, visit https://gerrit.wikimedia.org/r/settings Gerrit-MessageType: merged Gerrit-Change-Id: I65a81058fecafe33c538fbc5ea9b6a0983174170 Gerrit-PatchSet: 1 Gerrit-Project: wikimedia/fundraising/tools Gerrit-Branch: master Gerrit-Owner: Pcoombe <[email protected]> Gerrit-Reviewer: Mwalker <[email protected]> Gerrit-Reviewer: jenkins-bot <[email protected]> _______________________________________________ MediaWiki-commits mailing list [email protected] https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
