Hello all -
I've been trying to get a bash script set-up that creates a DB, does a
join, writes the join to file, and then cleans itself up afterwards.
Everything within the script runs fine when entered at an actual
prompt, but when I run the script the SELECT query runs indefinitely.
I'm not sure what the cause is—the script is being run on a box
connecting to its own local postgres installation, so it can't be a
connection issue. Any thoughts?
The bash script is included below; postgres version is 9.0.4. Any help
very much appreciated :)
#!/bin/bash
: ${1?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}
: ${2?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}
: ${3?"ERROR: Incorrect number of arguments (files have not been
properly specified). Proper format is 'mta-join old_mta_file
new_mta_file desired_output_filename.'"}
OLD_MTA_FILE=$1
NEW_MTA_FILE=$2
OUTPUT_FILE=$3
# Figure out how many days we need columns for
export NEW_MTA_COLWIDTH=`head -1 $NEW_MTA_FILE | grep ',' -o | wc -l`
NEW_MTA_COLWIDTH=($NEW_MTA_COLWIDTH-7)/4
# Assemble the extra SQL for the above
ADDITIONAL_CREATION_FIELDS=""
ADDITIONAL_SELECTION_FIELDS=""
for (( c=0; c<=$NEW_MTA_COLWIDTH; c++ ))
do
ADDITIONAL_CREATION_FIELDS=$ADDITIONAL_CREATION_FIELDS"DAY_"$c"
varchar(255), event_count_"$c" numeric(20,10), conversions_"$c"
numeric(20,10), revenue_"$c" numeric(20,10), "
ADDITIONAL_SELECTION_FIELDS=$ADDITIONAL_SELECTION_FIELDS"new_mta.DAY_"$c",
new_mta.event_count_"$c", new_mta.conversions_"$c",
new_mta.revenue_"$c", "
done
# Let's get rid of that extra comma at the end.
ADDITIONAL_CREATION_FIELDS=${ADDITIONAL_CREATION_FIELDS:0:${#ADDITIONAL_CREATION_FIELDS}-2}
ADDITIONAL_SELECTION_FIELDS=${ADDITIONAL_SELECTION_FIELDS:0:${#ADDITIONAL_SELECTION_FIELDS}-2}
echo -n "Creating database mta-join..."
createdb mta-join
echo -e "Done!\n"
echo "Creating table new_mta..."
# PSQL commands start here
psql mta-join<<EOF
CREATE TABLE new_mta (
report_date date,
campaign_id integer,
tracking_campaign_id integer,
placement_id integer,
creative_id integer,
package_id integer,
audience_id integer,
$ADDITIONAL_CREATION_FIELDS);
EOF
echo -e "Done!\n"
echo -n "Inserting new mta file into new_mta table..."
psql mta-join<<EOF
\copy new_mta from '$NEW_MTA_FILE' with delimiter ','
\q
EOF
echo -e "Done!\n"
echo "Creating table old_mta..."
# PSQL commands start here
psql mta-join<<EOF
CREATE TABLE old_mta (
report_date_day date,
report_date_week date,
report_date_month date,
campaign_name varchar(255),
package_name varchar(255),
audience_name varchar(255),
inventory_provider_name varchar(255),
placement_name varchar(255),
creative_size varchar(255),
creative_name varchar(255),
impressions bigint,
data_cost numeric(20,10),
media_cost numeric(20,10),
gross_cost numeric(20,10),
clicks integer,
lta_click_actions integer,
lta_view_actions integer,
lta_click_revenue integer,
lta_view_revenue integer,
mta_actions numeric(20,10),
mta_revenue integer,
mta_action_count integer,
mta_seconds integer,
campaign_id integer,
placement_id bigint,
creative_id bigint,
package_id bigint,
audience_id integer);
\q
EOF
echo -e "Done!\n"
# Upload old MTA file into table old_mta
echo -n "Inserting old mta file into old_mta table..."
psql mta-join<<EOF
\COPY old_mta from '$OLD_MTA_FILE' with delimiter ',' CSV header;
EOF
echo -e "Done!\n"
# Create a bunch of indexes
echo -n "Creating table indexes for faster querying..."
psql mta-join<<EOF
CREATE INDEX campaign_id_index_old ON old_mta (campaign_id);
CREATE INDEX campaign_id_index_new ON new_mta (campaign_id);
CREATE INDEX placement_id_index_old ON old_mta (placement_id);
CREATE INDEX placement_id_index_new ON new_mta (placement_id);
CREATE INDEX creative_id_index_old ON old_mta (creative_id);
CREATE INDEX creative_id_index_new ON new_mta (creative_id);
CREATE INDEX package_id_index_old ON old_mta (package_id);
CREATE INDEX package_id_index_new ON new_mta (package_id);
CREATE INDEX audience_id_index_old ON old_mta (audience_id);
CREATE INDEX audience_id_index_new ON old_mta (audience_id);
\q
EOF
echo -e "Done!\n"
echo "Writing join to file..."
psql -d mta-join -t -A -F ',' -c 'copy (SELECT * FROM new_mta, old_mta
WHERE report_date = report_date_day AND new_mta.campaign_id =
old_mta.campaign_id AND new_mta.placement_id = old_mta.placement_id
AND new_mta.creative_id = old_mta.creative_id AND new_mta.package_id =
old_mta.package_id AND new_mta.audience_id = old_mta.audience_id) to
stdout;' > $OUTPUT_FILE
echo -e "Done!\n"
# Clearing the newly created tables
echo "Cleaning up postgres installation..."
psql mta-join<<EOF
DROP TABLE new_mta;
DROP TABLE old_mta;
EOF
# Drop the mta-join database
dropdb mta-join
echo -e "Done!\r"
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
