Skip to content

Instantly share code, notes, and snippets.

@bentito

bentito/testing_query.yaml

Last active May 28, 2020 18:16
Show Gist options
  • Save bentito/f81d1aa47b6311724331e1a312a63e55 to your computer and use it in GitHub Desktop.
Save bentito/f81d1aa47b6311724331e1a312a63e55 to your computer and use it in GitHub Desktop.
Download ZIP
namespace-persistentvolumeclaim-usage report test discrepancy
Raw
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
@bentito
Copy link
Author

bentito commented May 28, 2020 •
edited
Loading

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;

@bentito
Copy link
Author

bentito commented May 28, 2020 •
edited
Loading

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

@bentito
Copy link
Author

bentito commented May 28, 2020 •
edited
Loading

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

@bentito
Copy link
Author

bentito commented May 28, 2020

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-

@timflannagan
Copy link

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>

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment