MySQL::Slurp - Use PIPEs to write directly to a MySQL table
MySQL::Slurp only works on systems that support FIFOs and does not support Windows ... yet.
0.27_02
use MySQL::Slurp;
# NEW OBJECTS
my $slurper= MySQL::Slurp->new(
database => 'test' ,
table => 'table_1' ,
buffer => 10000 ,
args => [] ,
);
$slurper->open;
# OR,
my $slurper = MySQL::Slurp->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.
MySQL::Slurp provides methods for writing directly to a MySQL table using a convenient interface. This module creates a writable FIFO and uses mysqlimport or LOAD DATA INFILE to load whatever is written to that FIFO. This is the fastest method for importing data into a MySQL table. This module makes it easy. The user needs only open, write, and close a MySQL::Slurp object.
This module also provides a slurp method for reaing directly from <STDIN> and writing to the table. This allows you to do tasks such as the following:
cat data.tsv | 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.
The module also implements buffering and locking using MySQL::Slurp::Writer. This allows for multi-process and multi- threading.
Creates a new MySQL::Slurp object
name of the MySQL database containing the target table.
Name of MySQL table to write to.
The (name of the) temporary directory in which the FIFO/pipe is created. This is created by File::Temp
default: 1 ( buffer one line, i.e. no buffering )
Maximum number of records that are stored in the buffer before locking the fifo and flushing to the MySQL table. By default, there is no buffering, Buffer = 1. This attribute is used by MySQL::Slurp::Writer
There is no checking for memory limits. The user is responsible for using a sensible value.
default: dbi
Method to use for importing. Supports c<mysqlimport>, c<mysql> and c<dbi> for mysqlimport, mysql and dbi loading methods, respectively.
c<dbi> is the default method. This method uses the DBI module and is the most portable.
mysql uses the mysql command line application.
mysqlimport uses the mysqlimport appication. Since the mysqlimport uses multiple threads. This is faster than the DBI method. It reads settings from ~/.my.cnf.
Options to pass to mysqlimport. args is an array ref and should appear exactly as it does in the command line invocation of mysqlimport. Applies to mysqlimport method only
Whether to display verbose output
Continue even if errors are encountered
Opens a connection to the MySQL table through a temporary FIFO. Returns a GlobRef that can be directly written to. This calls internal methods _mkfifo, <_import>, <_install_writer>, After the MySQL::Slurp object is open, one can print directly to the table.
Writes arguments directly to the MySQL database. Buffering is off by default, see the buffer attribute.
Read from <STDIN> and write to the database table.
Closes and removes the pipe and temporary table. Calls MySQL::Slurp::Writer::close and _rmfifo.
The slow holding the MySQL::Slurp::Writer used for buffering the writing and thread-safe.
The database handle to the target table. The handle is created using the defaults in your ~/.my.cnf file. Compression is used and query is streamed, mysql_use_result=1.
The fifo used for import. This is simply the path. It is set to the name of the tmp directory and the name of the table.
Do not use these methods directly.
Creates the FIFO at [tmp]/mysqlslurp/[table].txt. This will die if a pipe, file, directory exists with the same descriptor.
The fifo is created as with default mode 0777.
Removes the FIFO. Used in cleaning up after the upload.
This is the heart of MySQL::Slurp, reading from the fifo and writing from the table.
Print to MySQL::Slurp::Writer object located in $_[0]-writer>.
MySQL::Slurp is believed to be thread safe if using the 'write' method. Directly accessing the IO::File pipe is not considered Thread safe.
- 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
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
Christopher Brown, <ctbrown@cpan.org<gt>
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.
