NAME
MySQL::Slurp - Use PIPEs to import a file into MySQL table.
CAVEAT
MySQL::Slurp only works on systems that support FIFOs and
does not support Windows ... yet.VERSION
0.23
SYNOPSIS
use MySQL::Slurp;
# NEW OBJECTS
my $slurper= MySQL::Slurp->new(
database => 'test' ,
table => 'table_1' ,
buffer => 10000 ,
args => [] ,
);
$slurper->open;
# OR,
my $slurper->new( database => 'test', table => 'table_1' )->open;
# IMPORT METHODS
$slurper->slurp(); # slurp from <STDIN>
# RECOMMENDED METHOD TO WRITE TO A TABLE
# implements buffer and locks
$slurper->write( @records );
# WRITE DIRECTLY TO TABLE WITHOUT BUFFER AND LOCKS
$slurper->print( "Fred\tFlinstone\n" );
print { $slurper->{writer} } "Fred\tFlinstone\n";
$slurper->close;
# In coordinated environents
my $slurper1 = MySQL::Slurp::Writer->new( ... );
my $slurper2 = MySQL::Slurp::Writer->new( ... );
$slurper1->write( @a ); # In thread 1.
$slurper2->write( @b ); # In thread 2.DESCRIPTION
MySQL::Slurp slurps data directly into a MySQL table. This is the fastest way to import data into MySQL.
By itself mysqlimport does not allow reading from STDIN. IN fact, mysqlimport only reads from files that have the same name as the target table. This is very often inconvenient.
This module provides a library and tool that wraps mysqlimport and the mkfifo to allow piping data directly into MySQL tables. It allows such things as:
cat file | perl myscript.pl This is very handy for large ETL jobs.
Unike using DBI for trapping errors, catching errors with mysqlimport can be troublesome with inconsitent data. It is recommended that you check you data before writing to the MySQL::Slurp handle or use a suitable DBI method.
METHODS
new
Creates a new MySQL::Slurp object
- database
-
name of database (required)
- table
-
Name of table to import (required)
- tmp
-
Name of temporary directory (optional)
- buffer ( default: 1 )
-
Maximum number of records that are stored in the buffer before locking the fifo and flushing to the table. By default, there is no buffering, buffer = 1.
- method ( default: mysqlimport )
-
Method to use for importing. Supports c<mysqlimport>, c<mysql> and c<dbi> for mysqlimport, mysql and dbi loading methods, respectively.
- args
-
Options to pass to mysqlimport. args is an array ref and should appear exactly as it does in the command line invocation of mysqlimport.
open
Opens a connection to the MySQL table through a temporary FIFO. Returns a GlobRef that can be directly written to.
write
Writes arguments to the MySQL database. Buffering is on by default, see the buffer attribute.
close
Closes and removes the pipe and temporary table.
slurp
Write <STDIN> to the database table.
THREAD SAFE
MySQL::Slurp is believed to be thread safe if using the 'write' method. Directly accessing the IO::File pipe is not considered Thread safe.
TODO
- use MooseX::Attribute::Defaults::GNU for object attributes
- remove reliance on installation of mysqlimport, by XS wrapping the C libraries.
- create a version to run on windows with named pipes(?)
- create method for INSERT DELAYED
SEE ALSO
MySQL::Slurp relies on the Moose metaobject package.
mysqlimport at http://mysql.com, currently http://dev.mysql.com/doc/refman/5.1/en/mysqlimport.html
AUTHOR
Christopher Brown, <ctbrown@cpan.org<gt>
COPYRIGHT AND LICENSE
Copyright (C) 2008 by Open Data
This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.8 or, at your option, any later version of Perl 5 you may have available.