namespace-persistentvolumeclaim-usage report test discrepancy

testing_query.yaml

apiVersion: metering.openshift.io/v1
kind: ReportQuery
metadata:
  name: "namespace-persistentvolumeclaim-usage"
  labels:
    operator-metering: "true"
spec:
  columns:
  - name: period_start
    type: timestamp
    unit: date
  - name: period_end
    type: timestamp
  - name: namespace
    type: varchar
    unit: kubernetes_namespace
  - name: persistentvolumeclaim_usage_bytes
    type: double
    unit: bytes
  inputs:
  - name: ReportingStart
  - name: ReportingEnd
  - name: PersistentvolumeclaimUsageWithPhaseRawDataSourceName
    type: ReportDataSource
    default: persistentvolumeclaim-usage-with-phase-raw
  query: |
    SELECT
      timestamp '{| default .Report.ReportingStart .Report.Inputs.ReportingStart| prestoTimestamp |}' AS period_start,
      timestamp '{| default .Report.ReportingEnd .Report.Inputs.ReportingEnd | prestoTimestamp |}' AS period_end,
      namespace,
      avg(persistentvolumeclaim_usage_bytes)
    FROM {| dataSourceTableName .Report.Inputs.PersistentvolumeclaimUsageWithPhaseRawDataSourceName |}
    WHERE "timestamp" >= timestamp '{| default .Report.ReportingStart .Report.Inputs.ReportingStart | prestoTimestamp |}'
    AND "timestamp" < timestamp '{| default .Report.ReportingEnd .Report.Inputs.ReportingEnd | prestoTimestamp |}'
    AND dt >= '{| default .Report.ReportingStart .Report.Inputs.ReportingStart | prometheusMetricPartitionFormat |}'
    AND dt <= '{| default .Report.ReportingEnd .Report.Inputs.ReportingEnd | prometheusMetricPartitionFormat |}'
    GROUP BY namespace

Above is from:

https://github.com/kube-reporting/metering-operator/blob/25844903767642ef798542f25679bbb312353e4c/charts/openshift-metering/templates/openshift-reporting/report-queries/persistentvolumeclaim-usage.yaml#L181

From which I derived this SQL query:

presto:metering> SELECT
              ->       timestamp '2019-03-18 16:00:00 UTC' AS period_start,
              ->       timestamp '2019-03-18 16:05:00 UTC' AS period_end,
              ->       namespace,
              ->       persistentvolumeclaim,
              ->       avg(persistentvolumeclaim_usage_bytes)
              ->     FROM datasource_testing_validhdfs_reportstaticinputdata_persistentvolumeclaim_usage_with_phase_raw
              ->     WHERE "timestamp" >= timestamp '2019-03-18 16:00:00 UTC'
              ->     AND "timestamp" < timestamp '2019-03-18 16:05:00 UTC'
              ->     AND dt >= '2019-03-18'
              ->     AND dt <= '2019-03-18'
              ->     GROUP BY namespace;

And got these results:

        period_start         |         period_end          |                    namespace                     |        _col3
-----------------------------+-----------------------------+--------------------------------------------------+----------------------
 2019-03-18 16:00:00.000 UTC | 2019-03-18 16:05:00.000 UTC | openshift-monitoring                             |       2.2568730624E9
 2019-03-18 16:00:00.000 UTC | 2019-03-18 16:05:00.000 UTC | metering-tschuy                                  | 2.3910045013333333E8
 2019-03-18 16:00:00.000 UTC | 2019-03-18 16:05:00.000 UTC | telemeter-tschuy                                 |        2.667610112E8
 2019-03-18 16:00:00.000 UTC | 2019-03-18 16:05:00.000 UTC | metering-chancez                                 |         4.14670848E7
 2019-03-18 16:00:00.000 UTC | 2019-03-18 16:05:00.000 UTC | metering-ci2-openshift-continuous-upgrade-master |  8.737163946666666E8

For reference the test compares to this report:

[
    {
        "namespace": "metering-chancez",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 41467084.8
    },
    {
        "namespace": "metering-ci2-openshift-continuous-upgrade-master",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 932322157.7
    },
    {
        "namespace": "metering-tschuy",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 239100450.1
    },
    {
        "namespace": "openshift-monitoring",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 2563769996
    },
    {
        "namespace": "telemeter-tschuy",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 283902537.1
    }
]

https://raw.githubusercontent.com/kube-reporting/metering-operator/master/test/e2e/testdata/reports/namespace-persistentvolumeclaim-usage.json

The datasource table for reference:

presto:metering> select * from datasource_testing_validhdfs_reportstaticinputdata_persistentvolumeclaim_usage_with_phase_raw limit 1;
    namespace     |        timestamp        |       persistentvolumeclaim        | persistentvolumeclaim_usage_bytes |     dt
------------------+-------------------------+------------------------------------+-----------------------------------+------------
 metering-chancez | 2019-03-18 16:00:00.000 | hdfs-namenode-data-hdfs-namenode-0 |                       3.1485952E7 | 2019-

Re-organized the testdata/report/namespace-persistentvolumeclaim-usage.json data:

[
    {
        "namespace": "telemeter-tschuy",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 4001415168
    },
    {
        "namespace": "metering-ci2-openshift-continuous-upgrade-master",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 13105745920
    },
    {
        "namespace": "metering-chancez",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 622006272
    },
    {
        "namespace": "metering-tschuy",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 3586506752
    },
    {
        "namespace": "openshift-monitoring",
        "period_end": "2019-03-18T16:05:00Z",
        "period_start": "2019-03-18T16:00:00Z",
        "persistentvolumeclaim_usage_bytes": 56421826560
    }
]

The local test results:

presto:metering> select * from report_testing_validhdfs_reportstaticinputdata_namespace_persistentvolumeclaim_usage;
      period_start       |       period_end        |                    namespace                     | persistentvolumeclaim_usage_bytes 
-------------------------+-------------------------+--------------------------------------------------+-----------------------------------
 2019-03-18 16:00:00.000 | 2019-03-18 16:05:00.000 | telemeter-tschuy                                 |                     4.001415168E9 
 2019-03-18 16:00:00.000 | 2019-03-18 16:05:00.000 | metering-ci2-openshift-continuous-upgrade-master |                    1.310574592E10 
 2019-03-18 16:00:00.000 | 2019-03-18 16:05:00.000 | metering-chancez                                 |                      6.22006272E8 
 2019-03-18 16:00:00.000 | 2019-03-18 16:05:00.000 | metering-tschuy                                  |                     3.586506752E9 
 2019-03-18 16:00:00.000 | 2019-03-18 16:05:00.000 | openshift-monitoring                             |                    5.642182656E10 
(5 rows)

Query 20200528_181219_00237_cgggb, FINISHED, 1 node
Splits: 17 total, 17 done (100.00%)
0:00 [5 rows, 779B] [21 rows/s, 3.32KB/s]

presto:metering>