ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/gclib/scripts/mybcpin
Revision: 24
Committed: Tue Jul 26 21:46:39 2011 UTC (8 years, 3 months ago) by gpertea
File size: 6469 byte(s)
Log Message:
Line File contents
1 #!/usr/bin/perl
2 use strict;
3 use FindBin;
4 use Getopt::Std;
5 use lib $FindBin::Bin;
6 use dbSession;
7
8 my $usage=q/Usage:
9 mybcpin -b <targetdb> [-T] [-I] [-c '<column_delim>'] [-r 'row_delim'] <files>..
10
11 Loads multiple bcp files into tables of database <targetdb>.
12 The first token of each name of the input <files> should correspond to
13 the exact target table name in the database <targetdb>.
14 (e.g. a filename called 'names.bcp.bz2' will be loaded in table 'names')
15
16 For file names with the extension .z, .gz, gzip, .bz2, .bzip2 or .bz, such
17 input files are decompressed first.
18
19 <targetdb> can be given in the format: <db>[@<server>][:<user>]
20 (complete login information is taken from your ~\/.db_pass
21 authentication file, use dbpass utility to maintain it)
22
23 Options (for each input file\/table):
24 -T : truncate the table before loading data
25 -I : disable the indices before, and
26 enable them after loading data
27 -c,-r : provide custom column\/row delimiters (defaults are tab and
28 newline, respectively)
29 /;
30 my $bcpcmd=`which mysqlimport`;chomp($bcpcmd);
31 die "Error: mysqlimport not found in path!\n" unless -e $bcpcmd;
32 die "$usage\n" unless @ARGV;
33
34 print STDERR "Command line: $0 ".join(' ',@ARGV)."\n";
35
36
37 getopts('TIb:c:r:')
38 || die "$usage\nError getting options!\n";
39 my $targetdb = $Getopt::Std::opt_b || die "$usage Target database not specified!\n";
40 my ($coldelim, $rowdelim)=($Getopt::Std::opt_c, $Getopt::Std::opt_r);
41 my $coldelimflag="--fields-terminated-by='$coldelim'" if $coldelim;
42 my $rowdelimflag="--lines-terminated-by='$coldelim'" if $rowdelim;
43 my ($truncate, $dropidx)=($Getopt::Std::opt_T, $Getopt::Std::opt_I);
44
45 $coldelim="\t" unless $coldelim;
46 $rowdelim="\n" unless $rowdelim;
47 my @files=@ARGV;
48 my @authdta;
49 my ($server, $user, $pass, $db, @authdta)=&db_perm($targetdb, undef, 'ASK');
50 print STDERR "Connecting to server $server (user=$user, db=$db)\n";
51 my $dbh=&db_login($server, $user, $pass, $db, @authdta);
52
53 #die "Error: dbo permissions needed for current user ($user)!\n"
54 # unless syb_dboalias($dbh);
55
56 #fetch the identity information for that annoying -E flag of bcp in
57 # my %hasident;
58 # print STDERR "..find $db tables with identity fields..\n";
59 # my $sth=&sql_exec($dbh, q/
60 # select so.name from sysobjects so,
61 # syscolumns sc where sc.status & 128 = 128
62 # and sc.id=so.id
63 # /);
64 # while (my $r=&sth_fetch($sth)) {
65 # $hasident{$$r[0]}=1;
66 # }
67
68 print STDERR "..start processing bcp files..\n";
69 #$server=uc($1) if ($server=~m/^(\w+)\./); #NEOSYBASE case before full client upgrade
70
71 my $text_size = 9900000;
72 foreach my $file (@files) {
73 my ($tab)=($file=~/^(\w+)/);
74 my $bcplog="${tab}_bcp.log";
75 if (-s $bcplog) {
76 print STDERR ">> ..skipping file $file (because log $bcplog was found)\n\n";
77 next;
78 }
79 my $start = time;
80 my $start_str = localtime($start);
81 warn ">>Processing file $tab ...\n";
82 my $unzcmd;
83 #try to use the
84 if ($file=~/\.g?zi?p?$/i) {
85 $unzcmd='gzip -cd '.$file;
86 }
87 elsif ($file=~/\.bzi?p?2?$/i) {
88 $unzcmd='bzip2 -cd '.$file;
89 }
90 my $bcpinput=$file;
91 my ($postcmd);
92 if ($unzcmd) {
93 $bcpinput=$tab.'.unz.bcp';
94 #system("rm -f $bcpinput");
95 #system("mknod $bcpinput p") && die "Error: mknod $bcpinput p failed!\n";
96 print STDERR " ..uncompressing $file to $bcpinput..\n";
97 open(TOBCP, '>'.$bcpinput) || die "Error creating file '$bcpinput'!\n";
98 open(UNZ, $unzcmd.'|') || die "Error: failed to open '$unzcmd' pipe\n";
99 #local $/="(**)\n"; #one row at a time, not a line!
100 local $/=$rowdelim;
101 while (<UNZ>) {
102 #row processing here:
103 #print TOBCP &processRow($_);
104 #my @t=split(/\!~~\!/s);
105 my @t=split(/$coldelim/s); #if there are metachars in there, bad luck..
106 chomp($t[-1]);
107 foreach my $v (@t) {
108 if ($v=~m/^([\-\d\.]+)e\-(\d+)$/i) {
109 my ($mant, $exp)=($1,$2);
110 $v=sprintf('%.4e',$v);
111 $v='0.0' if $exp>300;
112 }
113 # -- the transform below can be commented out
114 # if you really have values when more than 2 decimals matter!
115 elsif ($v=~m/^\d{2,3}\.\d{3,}$/) { #percent value here
116 $v=sprintf('%4.2f',$v); #round to two decimals
117 #nobody cares about the other decimals
118 }
119 }
120 print TOBCP join($coldelim,@t).$rowdelim;
121 }
122 #system("$unzcmd > $bcpinput") && die "Error uncompressing $file!\n";
123 close(UNZ);
124 close(TOBCP);
125 my $duration = time - $start;
126 print STDERR " Finished unpacking - it took $duration seconds.\n"
127 }
128 # else { - we should filter uncompressed files too!
129 #
130 # }
131
132
133 # my $identflag = exists($hasident{$tab}) ? ' -E ' : '';
134
135
136 if ($truncate) {
137 &sql_do($dbh, "truncate table $tab");
138 }
139 #my @tabidx;
140 if ($dropidx) {
141 #@tabidx=&syb_getIndexes($dbh, $tab, 1);
142 &sql_do($dbh, "alter table `$tab` disable keys");
143 }
144
145 # my $bcpjob="$bcpcmd $db..$tab in $bcpinput -m1 -c ".
146 # " $coldelimflag $rowdelimflag ".
147 # "-U$user -P $pass -S $server $identflag -b 10000 -T $text_size ".
148 # "-e $bcplog";
149
150 my $inopts="-h $server -u $user -L --password=$pass $coldelimflag $rowdelimflag";
151 my $bcpjob="$bcpcmd $inopts $db $bcpinput";
152 print STDERR "..started loading $bcpinput..\n";
153 my $joboutput=`( $bcpjob ) 2>&1`;
154 my $status=$?;
155 #die "ERROR status $status at bcp from $db..$tab\n" if $status;
156 my $alloutput=$joboutput;
157 if (-s $bcplog) {
158 $alloutput.="\n ------error log content ($bcplog): \n";
159 $alloutput.=`cat $bcplog`;
160 }
161 local *TOLOG;
162 open(TOLOG, '>>'.$bcplog);
163 print TOLOG "\n$joboutput\n";
164 close(TOLOG);
165 if ($dropidx) {
166 # &syb_putIndexes($dbh, @tabidx);
167 &sql_do($dbh, "alter table `$tab` enable keys");
168 }
169 $bcpjob=~s/--password=\S+/--password=<yourpass>/;
170 die "Error detected at bcp-in $file file: \n$alloutput\n$bcpjob\n"
171 if $status || ($joboutput=~/error|fail|fatal|truncat|permission/i);
172 system("rm -f $bcpinput") if $unzcmd;
173 my $tot = time - $start;
174 if ($dropidx) {
175 #@tabidx=&syb_getIndexes($dbh, $tab, 1);
176 &sql_do($dbh, "alter table `$tab` enable keys");
177 }
178
179 print STDERR "<< $tab done in $tot seconds (exit code $status)\n\n";
180
181 }
182 &db_logout($dbh);
183 #-----------------------

Properties

Name Value
svn:executable *