Section: Performance Co-Pilot (1)
Updated: Performance Co-PilotIndex
sheet2pcp - Import spreadsheet data and create a PCP archive
] [-Z timezone
] infile mapfile outfile
is intended to read a data spreadsheet (infile
)translate this into a PerformanceCo-Pilot (PCP
) archive with the basename outfile
The input spreadsheet can be in any of the common formats, providedthe appropriate Perl modules have been installed (see the CAVEATSsection below). The spreadsheet must be ``normalized''so that each row contains data for the same time interval, and oneof the columns contains the date and time for the data in eachrow.
The resultant PCP archive may be used with all the PCP client toolsto graph subsets of the data using pmchart(1),perform data reduction and reporting, filter withthe PCP inference engine pmie(1), etc.
The mapfile controls the import process and defines the datamapping from the spreadsheet columns onto the PCP data model. The fileis written in XML and conforms to the syntax defined in theMAPPING CONFIGURATION section below.
A series of physical files will be created with the prefix outfile.These are outfile.0 (the performance data),outfile.meta (the metadata that describes the performance data) andoutfile.index (a temporal index to improve efficiency of replayoperations for the archive). If any of these files exists already,then sheet2pcp will not overwrite them and will exit with an errormessage.
The -h option is an alternate to the hostname attribute of the <sheet> element in mapfiledescribed below. If both are specified, the value from mapfile isused.
The -Z option is an alternate to the timezone attribute of the <sheet> element in mapfiledescribed below. If both are specified, the value from mapfile isused.
sheet2pcp is a Perl script that uses the PCP::LogImport Perl wrapperaround the PCP libpcp_importlibrary, and as such could be used as an example to develop newtools to import other types of performance data and create PCP archives.
contains specifications in standard XML
The whole specification is wrapped in a <sheet> ... </sheet>element.The sheet tag supports the following optional attributes:
- Specifies the number ofheading rows to skip at the start of the spreadsheet before processing data.Example: heading=``1''.
- Set the source hostname in the PCP archive (thedefault is to use the hostname of the local host).Example: hostname=``some.where.com''.
- Set the source timezone in the PCP archive (thedefault is to use UTC). The timezone must have theformat +HHMM (for hours and minutes East of UTC) or -HHMM (for hoursand minutes West of UTC). Note in particular that neither the zoneinfo(aka Olson) format, e.g. Europe/Paris, nor the Posix TZ format, e.g.EST+5 is allowed.Example: timezone=``+1100''.
- The format of the date imported from the spreadsheet may be specifiedas a concatenation ofvalues that specify theorder of the year (Y), month (M) and day (D) fields in a date.The supported variants are DMY (the default),MDY and YMD.Example: datefmt=``YMD''.
A <sheet> element containsone or more metric specifications ofthe form <metric>metricname</metric>. The metrictag supports the following optional attributes:
- The Performance Metrics Identifier (PMID), specified as 3 numbersseparated by a periods (.) toset the domain, cluster and item fields of the PMID, see PMNS(4)for more details of PMIDs. If omitted, the PMID will be automaticallyassigned by pmiAddMetric(3).The value PM_ID_NULL may be used to explicitly nominatethe default behaviour.Examples: pmid=``60.0.2'', pmid=``PM_ID_NULL''.
- Each metric may have one or more values. If a metric alwayshas one value, it is singular and the Instance Domain should be set toPM_INDOM_NULL.Otherwise indom should be specified as 2 numbers separated by a period (.)to set the domain and ordinal fields of the Instance Domain, seethe __pmInDom_int typedef in <pcp/impl.h>.Examples: indom=``PM_INDOM_NULL'', indom=``60.3'', indom=``PMI_DOMAIN.4''.
More thanone metric can share the same Instance Domain when the metrics have definedvalues over similar sets of instances, e.g. all the metrics for each networkinterface. It is standard practice for the domain field to be thesame for the pmid and the indom; if the pmid attribute is missing,then the domain field for the indom should be the reserved domainPMI_DOMAIN.
If the indom attribute is omitted then the default Instance Domain forthe metric is PM_INDOM_NULL.
- The scale and dimension of the metric values along the axes of space, timeand count (events, messages, packets, etc.) is specified with a 6-tuple.These values are passed to the pmiUnits(3) function to generate apmUnits structure. Refer to pmLookupDesc(3) for a full descriptionof all the fields of this structure.The default is to assign no scale or dimension to the metric, i.e. units=``0,0,0,0,0,0''.Examples: units=``0,1,0,0,PM_TIME_MSEC,0'' (milliseconds),units=``1,-1,0,PM_SPACE_MBYTE,PM_TIME_SEC,0'' (Mbytes/sec),units=``0,1,-1,0,PM_TIME_USEC,PM_COUNT_ONE'' (microseconds/event).
- Defines the data type for the metric.Refer to pmLookupDesc(3) for a full descriptionof the possible type values; the default is PM_TYPE_FLOAT.Examples: type=``PM_TYPE_32'', type=``PM_TYPE_U64'', type=``PM_TYPE_STRING''.
- Defines the semantics of the metric.Refer to pmLookupDesc(3) for a full descriptionof the possible values; the default is PM_SEM_INSTANT.Examples: sem=``PM_SEM_COUNTER'', type=``PM_SEM_DISCRETE''.
The remaining specifications define the data columns in order usingexactly one <datetime></datetime> element,one or more <data>metricspec</data> elementsandone or more <skip></skip> elements.
The <datetime> element defines the column in which a date and time willbe found to form the timestamp in the PCP archive for all the data ineach row of the PCP archive.
For the <data> element,a metricspecconsists of a metric name (as defined in an earlier <metric>element), optionally followed by an instance name that is enclosed by square brackets,e.g. <data>hinv.ncpu</data>, <data>kernel.all.load[1 minute]</data>.
The skip tag defines the column that should be skipped when preparingdata for the PCP archive.
The order of the <datetime>, <data> and<skip> elements matches the order of columns in thespreadsheet. If the number of elements is not the same as the numberof columns a warning is issued, and the extra elements or columnsgenerate no metric values in the output archive.
<?xml version="1.0" encoding="UTF-8"?> <sheet heading="1"> <!-- simple example --> <metric pmid="60.0.2" indom="60.0" units="0,1,0,0,PM_TIME_MSEC,0" type="PM_TYPE_U64" sem="PM_SEM_COUNTER"> kernel.percpu.cpu.sys</metric> <datetime></datetime> <skip></skip> <data>kernel.percpu.cpu.sys[cpu0]</data> <data>kernel.percpu.cpu.sys[cpu1]</data> </sheet>
could be used for a spreadsheet in which the first few rows are ...
Date;"Status";"SysTime - 0";"SysTime - 1"; 26/01/2001 14:05:22;"Some Busy";0.750;0.133 26/01/2001 14:05:37;"OK";0.150;0.273 26/01/2001 14:05:52;"All Busy";0.733;0.653
Only the first sheet from infile
will be processed.
Additional Perl modules must be installed for the various spreadsheet formats,although these are checked for ar run-time so only the modules required forthe specific types of spreadsheets you wish to process need be installed:
- Spreadsheets in the Comma Separated Values (CSV) format require Text::CSV_XS(3pm).
- *.sxc or *.ods
- OpenOffice documents require Spreadsheet::ReadSXC(3pm), which in turnrequires Archive::Zip(3pm).
- Classical Microsoft Office documents require Spreadsheet::ParseExcel(3pm),which in turn requires OLE::Storage_Lite(3pm).
- Microsoft OpenXML documents require Spreadsheet::XLSX(3pm). sheet2pcpdoes not appear to work with OpenXML documents saved from OpenOffice.
- MAPPING CONFIGURATION
- SEE ALSO
This document was created byman2html,using the manual pages.