NAME

MySQL::Slurp - Use PIPEs to write directly to a MySQL table

CAVEAT

  MySQL::Slurp only works on systems that support FIFOs and
  does not support Windows ... yet.

VERSION

0.28

SYNOPSIS

    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.

DESCRIPTION

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.

METHODS

new

Creates a new MySQL::Slurp object

database (required)

name of the MySQL database containing the target table.

table (required)

Name of MySQL table to write to.

tmp

The (name of the) temporary directory in which the FIFO/pipe is created. This is created by File::Temp

buffer

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.

method

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.

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. Applies to mysqlimport method only

verbose

Whether to display verbose output

force

Continue even if errors are encountered

open

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.

print

Writes arguments directly to the MySQL database. Buffering is off by default, see the buffer attribute.

slurp

Read from <STDIN> and write to the database table.

close

Closes and removes the pipe and temporary table. Calls MySQL::Slurp::Writer::close and _rmfifo.

INTERNAL SLOTS

writer

The slow holding the MySQL::Slurp::Writer used for buffering the writing and thread-safe.

dbh

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.

fifo

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.

INTERNAL METHODS

Do not use these methods directly.

_mkfifo

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.

_rmfifo

Removes the FIFO. Used in cleaning up after the upload.

_import

This is the heart of MySQL::Slurp, reading from the fifo and writing from the table.

_install_writer

_write

Print to MySQL::Slurp::Writer object located in $_[0]-writer>.

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>

http://www.opendatagroup.com

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.

cpanm MySQL::Slurp

perl -MCPAN -e shell
install MySQL::Slurp