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.28
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.
mysqlimport
LOAD DATA INFILE
open
write
close
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:
slurp
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.
mysql
mysqlimport uses the mysqlimport appication. Since the mysqlimport uses multiple threads. This is faster than the DBI method. It reads settings from ~/.my.cnf.
~/.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
args
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.
MySQL::Slurp
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.
MySQL::Slurp::Writer::close
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.
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.
tmp
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.
[tmp]/mysqlslurp/[table].txt
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>.
$_[0]-
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>
http://www.opendatagroup.com
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.
To install MySQL::Slurp, copy and paste the appropriate command in to your terminal.
cpanm
cpanm MySQL::Slurp
CPAN shell
perl -MCPAN -e shell install MySQL::Slurp
For more information on module installation, please visit the detailed CPAN module installation guide.