namespace-memory-usage-gigabyte-hours.sql

SELECT
  timestamp '2019-09-01 00:00:00.000' AS period_start,
  timestamp '2019-09-30 00:00:00.000' AS period_end,
  namespace,
  sum(pod_request_memory_byte_seconds / memory_units.resource_base_units / time_units.resource_base_units) as pod_request_memory,
  concat(memory_units.resource_unit, ' ', time_units.resource_unit, 's') AS pod_request_memory_units,
  sum(pod_request_memory_byte_seconds / price_sheet_memory_unit.resource_base_units / price_sheet_time_unit.resource_base_units * price_sheet.price) as cost,
  (price_sheet.price / (price_sheet_memory_unit.resource_base_units / memory_units.resource_base_units) / (price_sheet_time_unit.resource_base_units / time_units.resource_base_units)) AS price,
  upper(price_sheet.currency) as currency
FROM hive.metering.datasource_metering_chancez2_pod_memory_request_raw

-- specify how prices are defined in the price sheet
JOIN my_custom_price_sheet3 price_sheet
ON price_sheet.resource = 'memory'
AND price_sheet.resource_unit = 'gigabyte'
AND price_sheet.time_unit = 'hour'

-- desired memory units for conversions
JOIN unit_conversions memory_units
ON memory_units.resource = 'memory'
AND memory_units.resource_unit = 'gigabyte'

-- get the unit conversion from the price sheet unit to convert price book
-- memory units to desired units
JOIN unit_conversions price_sheet_memory_unit
ON price_sheet_memory_unit.resource = 'memory'
AND price_sheet_memory_unit.resource_unit = price_sheet.resource_unit

-- desired time units for conversions
JOIN unit_conversions time_units
ON time_units.resource = 'time'
AND time_units.resource_unit = 'hour'

-- get the unit conversion from the price sheet unit to convert price book time
-- units to desired units
JOIN unit_conversions price_sheet_time_unit
ON price_sheet_time_unit.resource = 'time'
AND price_sheet_time_unit.resource_unit = price_sheet.time_unit

WHERE "timestamp" >= timestamp '2019-09-01 00:00:00.000'
AND "timestamp" < timestamp '2019-09-30 00:00:00.000'
AND dt >= '2019-09-01'
AND dt <= '2019-09-30'
GROUP BY
    namespace,
    concat(memory_units.resource_unit, ' ', time_units.resource_unit, 's'),
    (price_sheet.price / (price_sheet_memory_unit.resource_base_units / memory_units.resource_base_units) / (price_sheet_time_unit.resource_base_units / time_units.resource_base_units)),
    price_sheet.currency
ORDER BY pod_request_memory DESC

price-sheet.sql

SELECT * FROM (
  VALUES
  ('memory', 'gigabyte', 'hour', 0.01275, 'USD', null, null),
  ('cpu', 'core', 'hour', 0.0255, 'USD', null, null)
) AS t (resource, resource_unit, time_unit, price, currency, price_start_date, price_end_Date)

unit-conversions.sql

SELECT * FROM (
  VALUES
  ('time', 'second', 1),
  ('time', 'minute', 60),
  ('time', 'hour', 60*60),
  ('time', 'day', 24*60*60),
  ('memory', 'byte', 1),
  ('memory', 'kilobyte', power(1024, 1)),
  ('memory', 'megabyte', power(1024, 2)),
  ('memory', 'gigabyte', power(1024, 3)),
  ('cpu', 'core', 1),
  ('cpu', 'millicore', 1000)
) AS t (resource, resource_unit, resource_base_units)