Skip to content

Latest commit

 

History

History
99 lines (71 loc) · 3.43 KB

sql.md

File metadata and controls

99 lines (71 loc) · 3.43 KB
Raw

SQL Queries

This section contains details for what type of data is retrieved by each of the sql queries included in scripts/sql.

1. ai_unit_hourly.sql

Calculates the number of ai_units used by each [resource] hourly.

Given that:

  • ram_gi: RAM in Gigabytes
  • cpu: CPU units (converted from millicpus to CPU units in the precursor step)
  • gpu: Count of GPUs

Number of ai units is calculated as follows $$ai_units = \max\left(\max\left(\frac{ram_gi}{64}, \frac{cpu}{8}\right) - gpu, 0\right) + (gpu \times 4)$$

2. aiem.sql

Calculates the resource utilization used by each engine hourly

Given that:

  • stream: One of value starting / pausing / deleting
  • user_name: Keycloak preferred username
  • enginename: Name of the engine
  • type: Type of the engine
  • version: Version
  • cpu: CPU units (converted from millicpus to CPU units in the precursor step)
  • gpu: Count of GPUs
  • memory: Memory usage in GB
  • storage: Storage usage in GB

Number of ai units is calculated as follows $$ai_units = \max\left(\max\left(\frac{ram_gi}{64}, \frac{cpu}{8}\right) - gpu, 0\right) + (gpu \times 4)$$

3. app_events.sql

Returns telemetry data for app events

Given that:

  • stream: stream of the app event
  • username: Keycloak preferred username
  • label: payload data
  • ts: timestamp

4. hourly_consumption_details.sql

Calculates hourly hardware resource utilization by resources

Given that:

  • hour: calculated hour
  • resource: resource name
  • ram_gi: RAM in Gigabytes
  • ram_gi_ai_unit_hours: $$ram_gi_ai_unit_hours=\frac{ram_gi}{64}$$
  • cpu: CPU units (converted from millicpus to CPU units in the precursor step)
  • cpu_ai_unit_hours: $$cpu_ai_unit_hours=\frac{cpu}{8}$$
  • gpu: Count of GPUs
  • gpu_ai_unit_hours: $$cpu_ai_unit_hours={gpu}\times{8}$$
  • ai_units $$ai_units = \max\left(\max\left(\frac{ram_gi}{64}, \frac{cpu}{8}\right) - gpu, 0\right) + (gpu \times 4)$$

5. hourly_max_ai_units_cat.sql

Calculates the maximum ai_units with other resource utilization hourly

  • time_interval: calculated hour
  • resource: resource name
  • ram_gi: RAM in Gigabytes
  • ram_gi_ai_unit_hours: $$ram_gi_ai_unit_hours=\frac{ram_gi}{64}$$
  • cpu: CPU units (converted from millicpus to CPU units in the precursor step)
  • cpu_ai_unit_hours: $$cpu_ai_unit_hours=\frac{cpu}{8}$$
  • gpu: Count of GPUs
  • gpu_ai_unit_hours: $$cpu_ai_unit_hours={gpu}\times{8}$$
  • ai_units $$ai_units = \max\left(\max\left(\frac{ram_gi}{64}, \frac{cpu}{8}\right) - gpu, 0\right) + (gpu \times 4)$$

6. hourly_max_ai_units.sql

Calculates the maximum ai_units utilized hourly

Given that:

  • time_interval: Calculated hour
  • ai_units: $$ai_units = \max\left(\left(\max\left(\left(\frac{ram_gi} {ram_gi_per_ai_unit_hours}\right), \left(\frac{cpu}{cpu_per_ai_unit_hours}\right)\right) - gpu\right), 0\right) + \left(gpu \times gpu_per_ai_unit_hours\right)$$

7. instance_events.sql

Returns summary of events of the instances

Given that:

  • stream: stream of the app event
  • username: Keycloak preferred username
  • label: payload data
  • ts: timestamp

8. user_login_data.sql

Retrieves data related to user login events from the app store.

  • username: Keycloak preferred username
  • ts: timestamp