healiseu/infinidat_orm_demo.md

## infinidat_orm_demo.md

      
    Raw
  

              infinidat_orm_demo.md
            
          
    Demo of Infinidat ORM Python Client for Clickhouse

Original source at  https://github.com/Infinidat/infi.clickhouse_orm

Modified and extended by Athanassios I. Hatzis

(c) 28th of September 2018

Jupyter Notebook: https://gist.github.com/healiseu/c1c51fc823f4a2cd6cb765b8a1157def

import psutil, time, datetime

from infi.clickhouse_orm.database import Database
from infi.clickhouse_orm.models import Model
from infi.clickhouse_orm.fields import *
from infi.clickhouse_orm.engines import Memory
Create Data Structures

# Create Model
class CPUStats(Model):

    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()

    engine = Memory()
CPUStats.fields()
OrderedDict([('timestamp',
              <infi.clickhouse_orm.fields.DateTimeField at 0x7f1cede05b38>),
             ('cpu_id',
              <infi.clickhouse_orm.fields.UInt16Field at 0x7f1cede05be0>),
             ('cpu_percent',
              <infi.clickhouse_orm.fields.Float32Field at 0x7f1cede05c18>)])

(CPUStats.table_name(), CPUStats.engine)
('cpustats', <infi.clickhouse_orm.engines.Memory at 0x7f1cede05c50>)

# Create Database or open an existing one
db = Database('DemoDB')
# Create table in database
db.create_table(CPUStats)
Single Measurement Test

# Get the first measurement
psutil.cpu_percent(percpu=True) # first sample should be discarded
# CPU Utilization for each CPU core
ts = datetime.datetime.now()
cpu_sample = psutil.cpu_percent(percpu=True)

# There are 4 cpu core measurements in each cpu_sample taken from psutil.cpu_percent
# Each core measurement has three values timestamp, core id and cpu utilization
measurements = [(ts, coreid, util) for coreid, util in enumerate(cpu_sample)]

# For each core in CPU cores
for core in measurements:
    # Print timestamp, core id and cpu utilization
    (ts, coreid, util) = (core[0], core[1], core[2])
    print(f"Time:{ts.strftime('%Y-%m-%d %H:%M:%S')}, Core:{coreid}, Utilization: {util}%")
Read/Write Measurements

# Take 10 cpu measurements
samples = 10

# Take a cpu sample from psutil.cpu_percent every 500msec
for n in range(samples):    
    ts = datetime.datetime.now()
    # read the CPU sample
    cpu_sample = psutil.cpu_percent(percpu=True)    
    # read the measurements for each core
    measurements = [CPUStats(timestamp=ts, cpu_id=coreid, cpu_percent=util) for coreid, util in enumerate(cpu_sample)]
    # print measurements for each core
    [print(core.timestamp.strftime('%Y-%m-%d %H:%M:%S'), core.cpu_id, core.cpu_percent) for core in measurements]
    # write measurements to ClickHouse Database
    db.insert(measurements)    
    # Pause for 500msec and repeat
    time.sleep(0.500)
Queries

# We wrote 10 samples in the database and each sample has 4 rows of data, one for each CPU core
# There are 4x10 = 40 rows in our dataset, each row is an instance of CPUStats
CPUStats.objects_in(db).count()
40

# Each CPU core has 10 measurements, i.e. CPUStats objects
# Count instances of the 1st core (cpu_id=1) 
core1_total_samples = CPUStats.objects_in(db).filter(cpu_id=1).count()
core1_total_samples
10

# Filtering Query in ORM form
# Sort all the measurements of CPU Core 1 in ascending order
core1samples = CPUStats.objects_in(db).filter(cpu_id=1).order_by('cpu_percent')
# Same query in SQL form
print(core1samples.as_sql())
SELECT `timestamp`, `cpu_id`, `cpu_percent`
FROM `cpustats`
WHERE cpu_id = 1
ORDER BY cpu_percent

# Print CPU Core 1 Utilization
for val in core1samples: print(f'CPU Core No1 Utilization: {val.cpu_percent}%')
CPU Core No1 Utilization: 3.8%
CPU Core No1 Utilization: 3.9%
CPU Core No1 Utilization: 3.9%
CPU Core No1 Utilization: 4.0%
CPU Core No1 Utilization: 4.0%
CPU Core No1 Utilization: 4.9%
CPU Core No1 Utilization: 6.0%
CPU Core No1 Utilization: 7.8%
CPU Core No1 Utilization: 25.5%
CPU Core No1 Utilization: 31.4%

# Filtering Query with range in ORM form
# Get all the measurements where CPU Core 1 was over 5% busy in descending order
core1over20 = CPUStats.objects_in(db).filter(cpu_id=1, cpu_percent__gt=5).order_by('cpu_percent DESC')
# Same query in SQL form
print(core1over20.as_sql())
SELECT `timestamp`, `cpu_id`, `cpu_percent`
FROM `cpustats`
WHERE cpu_id = 1 AND cpu_percent > 5.0
ORDER BY cpu_percent DESC

# Print core1over20 result set
for val in core1over20: print(f'CPU Core No1 Utilization: {val.cpu_percent}%')
CPU Core No1 Utilization: 31.4%
CPU Core No1 Utilization: 25.5%
CPU Core No1 Utilization: 7.8%
CPU Core No1 Utilization: 6.0%

# Find what percentage of time CPU Core 1 was over 5% busy
result = core1over20.count() * 100.0 / core1_total_samples
print(f'CPU Core 1 was busy {result}% of the time')
CPU Core 1 was busy 40.0% of the time

# Aggregation Query in ORM form 
# CPU Core 1 Average Utilization
core1avg = CPUStats.objects_in(db).filter(cpu_id=1).aggregate('cpu_id', average='avg(cpu_percent)')
# Same query in SQL form
print(core1avg.as_sql())
SELECT cpu_id, avg(cpu_percent) AS average
FROM `cpustats`
WHERE cpu_id = 1
GROUP BY `cpu_id`

# Print Average
for val in core1avg: print(f'CPU Core No1 Average Utilization: {round(val.average,2)}%')
CPU Core No1 Average Utilization: 9.52%

# Aggregation Query in ORM form
# Average CPU utilization per core in descending order
avgpercore = CPUStats.objects_in(db).aggregate('cpu_id', average='avg(cpu_percent)').order_by('average DESC')
# Same query in SQL form 
# 'where 1' indicates the absence of any filtering conditions)
print(avgpercore.as_sql())
SELECT cpu_id, avg(cpu_percent) AS average
FROM `cpustats`
WHERE 1
GROUP BY `cpu_id`
ORDER BY average DESC

# Print Average for each CPU core
for avg in avgpercore:
    print(f'CPU Core No{avg.cpu_id}: {avg.average:.2f}%')
CPU Core No0: 10.88%
CPU Core No2: 10.48%
CPU Core No1: 9.52%
CPU Core No3: 7.61%