Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/perl
- use v5.12;
- use strict;
- use warnings;
- use DBI;
- use File::Slurp;
- use Text::CSV_XS;
- use Tie::File;
- use Data::Dumper;
- # Start logging
- use Log::Log4perl qw(:easy);
- binmode( STDOUT, ":utf8" );
- use Log::Log4perl::CommandLine
- qw(:all :logconfig logging.cfg);
- my $time_start = time;
- INFO qq{Begin Program: $time_start};
- DEBUG("Debug flag turned on");
- TRACE("Trace flag turned on");
- my $commit_frequency = 500000;
- my $memory_limit = q{0};
- #my $data_file = q{ShortVoters.txt};
- my $data_file = q{AllVoters_01122016.txt};
- # grant all on tmp_voters.* to testuser identified by 'testpassword';
- my $database = q{tmp_voters};
- my $hostname = q{localhost};
- my $port = q{3306};
- my $user = q{testuser};
- my $password = q{testpassword};
- ### Get Column Names
- my @column_names = read_file("record.cfg", chomp => 1) or LOGDIE;
- ### Setup Database
- my $dsn = qq{DBI:mysql:database=$database;host=$hostname;port=$port};
- my $dbh = DBI->connect($dsn, $user, $password);
- $dbh->{AutoCommit} = 0; # enable transactions, if possible
- $dbh->{RaiseError} = 1;
- my $create_stmt = q{create or replace table voters (} . join(q{ TEXT, }, @column_names) . q{ TEXT);};
- INFO $create_stmt;
- $dbh->do($create_stmt) or LOGDIE;
- my $insert_stmt = q{insert into voters values (} . "?" . ", ?" x ( (scalar @column_names) - 1) . q{)};
- INFO $insert_stmt;
- my $sth_insert = $dbh->prepare($insert_stmt);
- ### Setup Data File
- use Fcntl 'O_RDONLY';
- tie my @data_file, 'Tie::File', $data_file , autochomp => 0 , mode => O_RDONLY , memory => $memory_limit or LOGDIE;
- ### Setup CSV Parser
- my $csv = Text::CSV_XS->new ( { binary => 1, auto_diag => 1 } ) # should set binary attribute.
- or LOGDIE "Cannot use CSV: ".Text::CSV_XS->error_diag ();
- my $nbr_rows = 0;
- INFO q{Entering foreach loop at } . (time - $time_start) . q{ second(s)};
- foreach my $line (@data_file) {
- TRACE $line;
- $csv->parse( $line ) or LOGDIE( qq{CSV Error: } . Text::CSV_XS->error_diag () );
- if ( not $nbr_rows ) {
- INFO q{First Record Parsed at } . (time - $time_start) . q{ seconds};
- }
- $sth_insert->execute($csv->fields) or LOGDIE;
- $nbr_rows++;
- if ( $nbr_rows % $commit_frequency == 0 ) {
- $dbh->commit or LOGDIE;
- dump_elapsed_time();
- }
- }
- $dbh->commit or LOGDIE;
- dump_elapsed_time();
- $csv->eof or $csv->error_diag();
- exit;
- #############################################################################
- #
- #
- #
- #############################################################################
- sub dump_elapsed_time {
- my $time_elapsed = time - $time_start;
- INFO sprintf(q{% 5.1fM rows processed in %3d minutes | %5d rows/second | %.1e seconds/row},
- $nbr_rows/1000000, $time_elapsed/60, $nbr_rows/$time_elapsed , $time_elapsed/$nbr_rows);
- }
- __END__
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement