SHEET2PCP(1)

📦
version 7.2.2 · 4 versions
, in arch rolling, debian trixie, ubuntu questing, ubuntu resolute, ubuntu stonking

💖 SHEET2PCP(1) 💖

okay bestie, welcome to the General Commands Manual entry for sheet2pcp — yes it's a real one, yes it eats spreadsheets for breakfast, and yes we're gonna get through the whole thing together. grab a boba. 🧋✨

✨ NAME ✨

sheet2pcp — the lil glow-up machine that imports spreadsheet data and creates a PCP archive 🦋

🌸 SYNOPSIS 🌸

sheet2pcp [-h host] [-V version] [-Z timezone] infile mapfile outfile

💅 DESCRIPTION 💅

so like. sheet2pcp is literally built to read a data spreadsheet (infile) and translate it into a Performance Co-Pilot (PCP) archive whose basename is outfile. that's the whole vibe. 🎀

the input spreadsheet can be in any of the common formats — provided the right Perl modules are installed (go peek at the CAVEATS section below, iykyk). and the spreadsheet has to be “normalized”, okay? that means every row holds data for the same time interval, and one of the columns has the date and time for the data in each row. no chaotic rows allowed, we're keeping it consistent bestie. ⭐

the resulting PCP archive can be used with all the PCP client tools — graph subsets of the data with pmchart(1), do data reduction and reporting, filter with the PCP inference engine pmie(1), etc. etc. it's giving versatile. 🔥

the mapfile is the main character here — it controls the import process and defines the data mapping from the spreadsheet columns onto the PCP data model. it's written in XML and follows the syntax laid out in the MAPPING CONFIGURATION section below. 💜

a series of physical files gets created with the prefix outfile:

  • outfile.0 — the performance data
  • outfile.meta — the metadata that describes the performance data
  • outfile.index — a temporal index to improve efficiency of replay operations for the archive
⚠️ if any of those files already exists, sheet2pcp will not overwrite them and will exit with an error message. it's respectful like that. 😌

the -h option is an alternate to the hostname attribute of the <sheet> element in mapfile (described below). if both are specified, the value from mapfile wins. periodt. 👑

the -V option specifies the version for the output PCP archive. by default the archive version $PCP_ARCHIVE_VERSION is used (set to 3 in current PCP releases), and the only values currently supported for version are 2 or 3. don't try anything else, bestie. 🙈

the -Z option is an alternate to the timezone attribute of the <sheet> element in mapfile (described below). if both are specified, the value from mapfile wins again. mapfile stays undefeated. 💪

sheet2pcp is a Perl script that uses the PCP::LogImport Perl wrapper around the PCP libpcp_import library, and as such it could be used as an example to develop new tools to import other types of performance data and create PCP archives. so it's not just a tool, it's inspiration. main character energy fr fr. 🌈

🎀 MAPPING CONFIGURATION 🎀

the mapfile contains specifications in standard XML format. ✨

the whole specification is wrapped in a <sheet> ... </sheet> element. the sheet tag supports the following optional attributes:

  • heading — specifies the number of heading rows to skip at the start of the spreadsheet before processing data. example: heading="1". bye bye header row 👋
  • hostname — set the source hostname in the PCP archive (the default is to use the hostname of the local host). example: hostname="some.where.com".
  • timezone — set the source timezone in the PCP archive (the default is to use UTC). the timezone must have the format +HHMM (for hours and minutes East of UTC) or -HHMM (for hours and 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". don't try to be cute with the format, it WILL not work 💀
  • datefmt — the format of the date imported from the spreadsheet may be specified as a concatenation of values that specify the order 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 contains one or more metric specifications of the form <metric>metricname</metric>. the metric tag supports the following optional attributes:

  • pmid — the Performance Metrics Identifier (PMID), specified as 3 numbers separated by a periods (.) to set the domain, cluster and item fields of the PMID, see PMNS(5) for more details of PMIDs. if omitted, the PMID will be automatically assigned by pmiAddMetric(3). the value PM_ID_NULL may be used to explicitly nominate the default behaviour. examples: pmid="60.0.2", pmid="PM_ID_NULL".
  • indom — each metric may have one or more values. if a metric always has one value, it is singular and the Instance Domain should be set to PM_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. examples: indom="PM_INDOM_NULL", indom="60.3", indom="PMI_DOMAIN.4".
    • more than one metric can share the same Instance Domain when the metrics have defined values over similar sets of instances, e.g. all the metrics for each network interface. 🖥️
    • it is standard practice for the domain field to be the same for the pmid and the indom; if the pmid attribute is missing, then the domain field for the indom should be the reserved domain PMI_DOMAIN.
    • if the indom attribute is omitted then the default Instance Domain for the metric is PM_INDOM_NULL.
  • units — the scale and dimension of the metric values along the axes of space, time and count (events, messages, packets, etc.) is specified with a 6-tuple. these values are passed to the pmiUnits(3) function to generate a pmUnits structure. refer to pmLookupDesc(3) for a full description of 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)
  • type — defines the data type for the metric. refer to pmLookupDesc(3) for a full description of the possible type values; the default is PM_TYPE_FLOAT. examples: type="PM_TYPE_32", type="PM_TYPE_U64", type="PM_TYPE_STRING".
  • sem — defines the semantics of the metric. refer to pmLookupDesc(3) for a full description of 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 using exactly one <datetime></datetime> element, one or more <data>metricspec</data> elements and one or more <skip></skip> elements. ⭐

the <datetime> element defines the column in which a date and time will be found to form the timestamp in the PCP archive for all the data in each row of the PCP archive. 🕐

for the <data> element, a metricspec consists 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 preparing data for the PCP archive. not every column deserves to make it, and that's okay 🚫

the order of the <datetime>, <data> and <skip> elements matches the order of columns in the spreadsheet. if the number of elements is not the same as the number of columns, a warning is issued, and the extra elements or columns generate no metric values in the output archive. so like. count your columns bestie 😭

🌟 EXAMPLE 🌟

the mapfile...

<?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

like. look at that mapping go. iconic. 👑

⚠️ CAVEATS ⚠️

okay so the important stuff, no skipping bestie:

only the first sheet from infile will be processed. just the first one. the rest are getting ignored, sorry to them. 💀

additional Perl modules must be installed for the various spreadsheet formats, although these are checked for at run-time so only the modules required for the specific types of spreadsheets you wish to process need to be installed:

🌈 PCP ENVIRONMENT 🌈

environment variables with the prefix PCP_ are used to parameterize the file and directory names used by PCP. on each installation, the file /etc/pcp.conf contains the local values for these variables. the $PCP_CONF variable may be used to specify an alternative configuration file, as described in pcp.conf(5). ✨

for environment variables affecting PCP tools, see pmGetOptions(3).

🫶 SEE ALSO 🫶

the whole friend group, in order: pmchart(1), pmie(1), pmlogger(1), sed(1), pmiAddMetric(3), pmLookupDesc(3), pmiUnits(3), Archive::Zip(3pm), Date::Format(3pm), Date::Parse(3pm), PCP::LogImport(3pm), OLE::Storage_Lite(3pm), Spreadsheet::ParseExcel(3pm), Spreadsheet::ReadSXC(3pm), Spreadsheet::XLSX(3pm), Text::CSV_XS(3pm), XML::TokeParser(3pm) and LOGIMPORT(3).

that's the whole page bestie 💖 brought to you by Performance Co-Pilot / PCP, page SHEET2PCP(1). go forth, import those spreadsheets, and slay. 🦋✨🎀

📦 where this page comes from