App-BigQuery-Importer-MySQL

 view release on metacpan or  search on metacpan

lib/App/BigQuery/Importer/MySQL.pm  view on Meta::CPAN

use File::Temp qw(tempfile);
use File::Basename;
use DBI;
use Carp qw(croak);

our $VERSION = "0.024";

sub new {
    my ($class, $args) = @_;

    my @required_list = qw/ src dst mysqlhost mysqluser mysqlpassword project_id progs /;
    my $obj_data = +{};
    for my $required (@required_list) {
        if( ! defined $args->{$required} ) { croak "$required is required"};
        $obj_data->{$required} = $args->{$required};
    }
    $obj_data->{dryrun}          = $args->{dryrun},
    $obj_data->{allow_text_type} = $args->{allow_text_type},

    bless $obj_data, $class;
}

sub run {
    my $self = shift;

    my $db_host                  = $self->{'mysqlhost'};
    my ($src_schema, $src_table) = split /\./, $self->{'src'};
    my ($dst_schema, $dst_table) = split /\./, $self->{'dst'};

    # check the table does not have BLOB or TEXT
    my $dbh = DBI->connect("dbi:mysql:${src_schema}:${db_host}", $self->{'mysqluser'}, $self->{'mysqlpassword'});

    $self->_check_columns(
        +{
            dbh             => $dbh,
            src_schema      => $src_schema,
            src_table       => $src_table,
            allow_text_type => $self->{'allow_text_type'},
        }
    );

lib/App/BigQuery/Importer/MySQL.pm  view on Meta::CPAN

    my $bucket_name = $src_table . '_' . time;
    unless ($self->{'dryrun'}) {
        my $mb_command = "$self->{'progs'}->{'gsutil'} mb -p $self->{'project_id'} gs://$bucket_name";
        my $result_create_bucket = system($mb_command);
        if ($result_create_bucket != 0) {
            die "${mb_command} : failed";
        }
    }

    # dump table data
    my $dump_command = "$self->{'progs'}->{'mysql'} -u$self->{'mysqluser'} -p'$self->{'mysqlpassword'}' -h$self->{'mysqlhost'} ${src_schema} -Bse'SELECT * FROM ${src_table}'";
    my $dump_result = `$dump_command`;
    if ($? != 0) {
        die "${dump_command} : failed";
    }
    $dump_result =~ s/\"//g;
    $dump_result =~ s/NULL//g;
    my($src_dump_fh, $src_dump_filename) = tempfile(UNLINK => 1);
    unless ($self->{'dryrun'}) {
        print {$src_dump_fh} $dump_result;
    }

script/mysqlbq  view on Meta::CPAN

my @progs = qw(mysql gsutil bq);
for my $prog (@progs) {
   my $path = `which $prog 2> /dev/null` or pod2usage(2);
   chomp $path if $path;
   $pathes->{$prog} = $path;
}

# check config
defined RC->{'default.project_id'} or pod2usage(2);
defined RC->{'client.user'} or pod2usage(2);
defined RC->{'client.password'} or pod2usage(2);

# get options
my %opt;
GetOptions(
    \%opt,
    qw(db_host=s src=s dst=s allow_text_type dryrun)
) or pod2usage(2);

my @required_options = qw(db_host src dst);
pod2usage(2) if grep {!exists $opt{$_}} @required_options;

my $app = App::BigQuery::Importer::MySQL->new({
    dryrun          => $opt{dryrun},
    src             => $opt{src},
    dst             => $opt{dst},
    allow_text_type => $opt{allow_text_type},
    mysqlhost       => $opt{db_host},
    mysqluser       => RC->{'client.user'},
    mysqlpassword   => RC->{'client.password'},
    project_id      => RC->{'default.project_id'},
    progs           => $pathes,
});
$app->run;

__END__

=head1 NAME

mysqlbq - cli tool for App::BigQuery::Importer::MySQL

script/mysqlbq  view on Meta::CPAN

        --src             MySQL Schema and Table name(ex: schema_name.table_name)
        --dst             BigQuery Dataset and Table name(ex: dataset_name.table_name)
        --allow_text_type Import MySQL TEXT type columns to BigQuery as STRING type
        --dryrun          dry run mode. not run the side-effects operation.(ex: gsutil mk/rm, bq load/rm)
        -h(--help)  show this help
      Requirement Programs: mysql cli and gcloud package
      Requirement Files: this script needs ~/.my.cnf and ~/.bigqueryrc files
        ~/.my.cnf:
          [client]
          user = user
          password = pass
        ~/.bigqueryrc:
          project_id = pj_id
          credential_file = /path/to/credential.json

t/01_new.t  view on Meta::CPAN

use Test::Fatal;

use App::BigQuery::Importer::MySQL;

our $origin_args = {
    src             => 'src.table',
    dst             => 'dst.table',
    allow_text_type => 0,
    mysqlhost       => 'localhost',
    mysqluser       => 'user',
    mysqlpassword   => 'pass',
    project_id      => 'pjid',
    progs           => {
        mysql  => '/path/to/mysql',
        gsutil => '/path/to/gsutil',
        bq     => '/path/to/bq',
    }
};

subtest 'all_args' => sub {
    my $args = { %$origin_args };

t/01_new.t  view on Meta::CPAN

    $args->{mysqluser} = undef;
    like(
        exception {
            my $mysqlbq = App::BigQuery::Importer::MySQL->new($args);
        },
        qr/mysqluser is required/,
        "no mysqluser in args died as expected",
    );
};

subtest 'no mysqlpassword' => sub {
    my $args = { %$origin_args };
    $args->{mysqlpassword} = undef;
    like(
        exception {
            my $mysqlbq = App::BigQuery::Importer::MySQL->new($args);
        },
        qr/mysqlpassword is required/,
        "no mysqlpassword in args died as expected",
    );
};

subtest 'no project_id' => sub {
    my $args = { %$origin_args };
    $args->{project_id} = undef;
    like(
        exception {
            my $mysqlbq = App::BigQuery::Importer::MySQL->new($args);
        },