Re: [pcp] PCP question ... leading to pmimport discussion

[Ken McDonell <kenj@xxxxxxxxxxxxxxxx>]

OK, removing the Perl attachment and burying that in the body of the
mail and changing the addressees to see if I can get this past
somebody's mail filter ... sigh.

On Fri, 2010-07-16 at 07:41 +1000, Ken McDonell wrote:
> I'm having real trouble sending this mail ... not sure where it is
> getting swallowed.
> 
> This time I've detached the spreadsheet ... you can retrieve that from
> oss.sgi.com:~kenj/utility_daily_avgs.csv
> 
> I'm still keen for feedback on the Perl code ...
> 
> On Thu, 2010-07-15 at 17:26 +1000, Ken McDonell wrote:
> > Here's a status update ... seems to work!
> > 
> > Attached is a csv spreadsheet from Petr that I chopped columns a bit to
> > reduce the data set size and added a couple of bogus test columns at the
> > end.
> > 
> > Also attached is a Perl program that uses PCP::LogImport to create a PCP
> > archive from the spreadsheet!
> > 
> > The 70 lines in sub init($) could be replaced by the XML-drived config
> > mechanism we've been speaking of, which would halve the Perl program
> > size and make it generic for _any_ spreadsheet (once the XML spec was
> > done).
> > 
> > Comments and feedback most welcome, but please be gentle as this is my
> > first Perl program.
> 

#!/usr/bin/perl
use strict;
use warnings;

# use Spreadsheet::Read;
# my $ref = ReadData ("utility_daily_avgs.csv", sep => ";"); 
# print "maxrow=$ref->[0]{A1}\n";

use Text::CSV_XS;
use Date::Parse;
use Date::Format;
use PCP::LogImport;

my @handle = ();        # pmi* handles, one per data column
my $debug = 0;
my $nrow = 0;           # spreadsheet rows processed
my $stamp;              # tv_sec to use in pmiWrite()
my $timedate;           # date munged so Date::Parse is not confused

# args to be handled more elegantly ... 8^)>
#
my $archive = "petr";
my $mysheet = "utility_daily_avgs.csv";

sub dodate($)
{
    # convert datetime format DD/MM/YYYY HH:MM:SS ... from OpenOffice
    # Spreadsheet CSV into the format DD-Mmm-YYYY HH:MM:SS that
    # Date::Parse seems to be able to parse correctly
    #
    ($_) = @_;
    if (/\/0*1\//) { $_ =~ s/\/0*1\//-Jan-/; }
    elsif (/\/0*2\//) { $_ =~ s/\/0*2\//-Feb-/; }
    elsif (/\/0*3\//) { $_ =~ s/\/0*3\//-Mar-/; }
    elsif (/\/0*4\//) { $_ =~ s/\/0*4\//-Apr-/; }
    elsif (/\/0*5\//) { $_ =~ s/\/0*5\//-May-/; }
    elsif (/\/0*6\//) { $_ =~ s/\/0*6\//-Jun-/; }
    elsif (/\/0*7\//) { $_ =~ s/\/0*7\//-Jul-/; }
    elsif (/\/0*8\//) { $_ =~ s/\/0*8\//-Aug-/; }
    elsif (/\/0*9\//) { $_ =~ s/\/0*9\//-Sep-/; }
    elsif (/\/10\//) { $_ =~ s/\/10\//-Oct-/; }
    elsif (/\/11\//) { $_ =~ s/\/11\//-Nov-/; }
    elsif (/\/12\//) { $_ =~ s/\/12\//-Dec-/; }
    else { die "dodate: bad month in datetime: \"$_\"\n"; }
    return $_;
}

# data set-specific initialization ... arg is number of columns in
# first row of spreadsheet ... this should be timestamp followed
# by the number of metric-instances we're expecting
#
# first row of Petr's modified spreadsheet
# 
"DATE";"T_OP_L";"T_OP_P";"T_MP1_L";"T_MP1_P";"T_MP2_L";"T_MP2_P";"T_NV";"CTR";"DELTA"
#
sub init($)
{
    my ($ncol) = @_;

    $_ = pmiStart($archive, 0);
    die "pmiStart($archive): " . pmiErrStr($_) . "\n" if ($_ < 0);

    $_ = pmiAddMetric("t_op", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1), 
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"t_op\", ...): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddMetric("t_mp1", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1), 
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"t_mp1\", ...): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddMetric("t_mp2", PM_ID_NULL, PM_TYPE_FLOAT, pmInDom_build(245,1), 
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"t_mp2\", ...): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddMetric("t_nv", PM_ID_NULL, PM_TYPE_FLOAT, PM_INDOM_NULL, 
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"t_nv\", ...): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddMetric("fake.ctr", PM_ID_NULL, PM_TYPE_32, PM_INDOM_NULL, 
PM_SEM_COUNTER, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"fake.ctr\", ...): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddMetric("fake.delta", PM_ID_NULL, PM_TYPE_32, PM_INDOM_NULL, 
PM_SEM_INSTANT, pmiUnits(0,0,0,0,0,0));
    die "pmiAddMetric(\"fake.delta\", ...): " . pmiErrStr($_) . "\n" if ($_ < 
0);

    $_ = pmiAddInstance(pmInDom_build(245,1), "low", 1);
    die "pmiAddInstance(..., \"low\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    $_ = pmiAddInstance(pmInDom_build(245,1), "peak", 2);
    die "pmiAddInstance(..., \"peak\"): " . pmiErrStr($_) . "\n" if ($_ < 0);

    push(@handle, -1);                  # col 1 is timestamp, not data

    $_ = pmiGetHandle("t_op", "low");
    die "pmiGetHandle(\"t_op\", \"low\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_op", "peak");
    die "pmiGetHandle(\"t_op\", \"peak\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_mp1", "low");
    die "pmiGetHandle(\"t_mp1\", \"low\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_mp1", "peak");
    die "pmiGetHandle(\"t_mp1\", \"peak\"): " . pmiErrStr($_) . "\n" if ($_ < 
0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_mp2", "low");
    die "pmiGetHandle(\"t_mp2\", \"low\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_mp2", "peak");
    die "pmiGetHandle(\"t_mp2\", \"peak\"): " . pmiErrStr($_) . "\n" if ($_ < 
0);
    push(@handle, $_);
    $_ = pmiGetHandle("t_nv", "");
    die "pmiGetHandle(\"t_nv\", \"\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("fake.ctr", "");
    die "pmiGetHandle(\"fake.ctr\", \"\"): " . pmiErrStr($_) . "\n" if ($_ < 0);
    push(@handle, $_);
    $_ = pmiGetHandle("fake.delta", "");
    die "pmiGetHandle(\"fake.delta\", \"\"): " . pmiErrStr($_) . "\n" if ($_ < 
0);
    push(@handle, $_);

    if ($debug) {
        for (my $v = 0; $v < @handle; $v++) {
            print "handle[$v] = $handle[$v]\n";
        }
    }

    die "init: $#handle metric-instance definitions does not equal " . 
($ncol-1) . " data columns\n" if $#handle != $ncol-1;
}

my $csv = Text::CSV_XS->new ({ sep_char => ";", binary => 1 }) or
    die "Cannot use CSV: ".Text::CSV->error_diag();
open my $fh, "<:encoding(utf8)", $mysheet or die "$mysheet: $!";

# process spreadsheet one row at a time ...
#
while (my $row = $csv->getline ($fh)) {
    $nrow++;
    if ($nrow == 1) {
        # set up PCP::LogImport and skip first row of column headings
        init(scalar(@$row));
        next;
    }
    $timedate = dodate($row->[0]);
    $stamp = str2time($timedate);
    $debug and print "in: $row->[0] reformat: $timedate stamp: $stamp ctime: " 
. ctime($stamp) . "\n";
    for (my $v = 1; $v < @handle; $v++) {
        $_ = pmiPutValueHandle($handle[$v], $row->[$v]);
        die "pmiPutValueHandle: row: $nrow col: $v : " . pmiErrStr($_) . "\n" 
if ($_ < 0);
    }
    $_ = pmiWrite($stamp, 0);
    die "pmiWrite: row: $nrow : " . pmiErrStr($_) . "\n" if ($_ < 0);
}
$csv->eof or $csv->error_diag();
close $fh;

$_ = pmiEnd();
die "pmiEnd: " . pmiErrStr($_) . "\n" if ($_ < 0);

print "Imported $nrow data rows, archive $archive created.\n";