Streamlit real time
DB CAFE
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 streamlit real time chart[편집]
1.1 Build a real-time dashboard in Python with Tinybird and Dash[편집]
1.1.1 환경 설정 (Python >3.8)[편집]
mkdir python-dashboard
cd python-dashboard
python -m venv .e
. .e/Scripts/activate.bat
-- . .e/bin/activate
pip install dash
pip install pandas # Dash utilizes pandas
pip install dash_bootstrap_components # so we can use a Dash Theme
pip install verdin # Tinybird SDK for Python
pip install tinybird-cli
echo -e ".e*\n.tinyb" >> .gitignore
- Go to tinybird.co/signup and get a free Tinybird account.
1.1.2 TinyBird 인증[편집]
mkdir data-project
cd data-project
tb auth --token <the token you copied>
1.2 샘플[편집]
import streamlit as st
import time
import numpy as np
import pandas as pd
st.set_page_config(
page_title='Sirkülasyon Testi',
layout="wide",
initial_sidebar_state="collapsed",
)
st.sidebar.markdown('''
### Sirkülasyon Testi
Yapılacak değişiklikler programı resetler.
''')
marka = st.sidebar.text_input("Pompa Markası:","Brand")
model = st.sidebar.text_input("Pompa Modeli:","Model")
mod = st.sidebar.text_input("Çalıştırma Modu:","dP5.0")
zamandamgasi = time.strftime("]_%Y%b%d_%H.%M.%S")
isim = marka+"_"+model+"_["+mod+zamandamgasi
i = 0
t = 0
st.sidebar.write("Otomatik Vana Modunda Deney")
Dint = st.sidebar.number_input("Başlangıç Noktası:",value=15.0)
Dfin = st.sidebar.number_input("Bitiş Noktası:",value=70.0)
ds = st.sidebar.number_input("Adım Aralığı",value=0.25)
dt = st.sidebar.number_input("Adımlar Arası Periyot",value=8)
if st.sidebar.button("Otomatik Modda Kayıt Başlat"):
kaydet = 1
else:
kaydet = 0,
st.sidebar.write("Manuel Vana Ayarı")
D = st.sidebar.number_input("Vana Açıklığı [0:Kapalı -90:Açık]",value=0.0)
if st.sidebar.button("Ayarla"):
yaz(64,[int(D*4)])
sozluk =[
['Date' ,"Tarih" ,"YMD" ,"" ],
['Time' ,"Zaman" ,"HMS" ,"" ],
['Temperature' ,"Sıcaklık" ,"T" ,"°C" ],
['Flow_Rate' ,"Debi" ,"Q" ,"m³/saat" ],
['Valve_Aperture' ,"Vana Açıklığı" ,"D" ,"°" ],
['Static_Pressure' ,"Durağan Basınç" ,"P" ,"Bar" ],
['Suction_Pressure' ,"Emme Basıncı" ,"P1" ,"Bar" ],
['Discharge_Pressure' ,"Basma Basıncı" ,"P2" ,"Bar" ],
['Voltage' ,"Gerilim" ,"V" ,"V" ],
['Current' ,"Akım" ,"I" ,"A" ],
['Measured_Frequency' ,"Ölçülen Frekans" ,"f" ,"Hz" ],
['Active_Power' ,"Aktif Güç" ,"PAP" ,"W" ],
['Reactive_Power' ,"Reaktif Güç" ,"QRP" ,"VAr" ],
['Apperent_Power' ,"Görünür Güç" ,"SAP" ,"VA" ],
['Phase_Voltage_Angle' ,"Faz Gerilim Açısı" ,"PVA" ,"°" ],
['Phase_Current_Angle' ,"Faz Akım Açısı" ,"PCA" ,"°" ],
['Cos_Phi' ,"Cos_Phi" ,"PHI" ,"" ],
['Power_Factor' ,"Güç Faktörü" ,"PF" ,"" ],
['Differential_Pressure' ,"Fark Basıncı" ,"DP" ,"Bar" ],
['Head' ,"Basma Yüksekliği" ,"H" ,"m" ],
['Hydraulic_Power' ,"Hidrolik Güç" ,"PHYD" ,"W" ],
['Efficiency' ,"Verimlilik" ,"ETA" ,"%" ]
]
varis = []
for i in sozluk: varis.append(i[0]) #sozluk ilk sütun (Türkçe için 0 yerine 1. sütun seçilebilir.)
df = pd.DataFrame(columns=varis)
buffy = pd.DataFrame(columns=varis)
col = st.columns(4)
###################################### G R A F İ K L E R #############################################
col[0].subheader("Debi & Basma Yüksekliği [m³/h,m] \n Flow Rate & Head")
QHt = col[0].line_chart()
col[0].subheader("Hidrolik Güç & Verimlilik [W,%] \n Hydraulic Power & eta")
PHYDETAt = col[0].line_chart()
col[1].subheader("Emme & Basma Basınçları [Bar] \n Suction & Discharge Pressure")
P1P2t = col[1].line_chart()
col[1].subheader("Elektrik Güçleri [W, VA, VAr] \n Active, Reactive & Apperent Power" )
PAPQRPSAP = col[1].line_chart()
col[2].subheader("cos(φ) & Güç Faktörü \n cos(φ) & Power Factor")
PHIPFt = col[2].line_chart()
col[2].subheader("Faz Gerilim & Akım Açıları [°] \n Phase Voltage & Current Angles")
PVAPCAt = col[2].line_chart()
###################################### M E T R İ K L E R ##############################################
col[3].write(f"Deney Kimliği: \n {isim}")
YMDt = col[3].empty()
HMSt = col[3].empty()
Tt = col[3].empty()
Dt = col[3].empty()
Pt = col[3].empty()
Vt = col[3].empty()
ft = col[3].empty()
##################################### D E M O ##########################################################
df = pd.read_excel("demo_data.xlsx",index_col=0)
######################################### D Ö N G Ü ####################################################
for i in range(len(df)):
buff=df.iloc[i]
with Tt:
st.metric("Hat Sıcaklığı",f"{buff[2]} °C")
with YMDt:
st.metric("Tarih",buff[0])
with HMSt:
st.metric("Saat",buff[1])
with Dt:
st.metric("Vana Açıklığı",f"{buff[4]} °")
with Pt:
st.metric("Durağan Basınç",f"{buff[5]} Bar")
with Vt:
st.metric("Gerilim",f"{buff[8]} V")
with ft:
st.metric("Ölçülen Frekans",f"{buff[10]} Hz")
QHt.add_rows([[float(buff[3]),float(buff[19])]])
PHYDETAt.add_rows([[float(buff[20]),float(buff[21])]])
P1P2t.add_rows([[float(buff[6]),float(buff[7])]])
PHIPFt.add_rows([[float(buff[16]),float(buff[17])]])
PAPQRPSAP.add_rows([[float(buff[11]),float(buff[12]),float(buff[13])]])
PVAPCAt.add_rows([[float(buff[14]),float(buff[15])]])
time.sleep(0.1)
# if kaydet == 1:
# if t%dt==0 and Dint<Dfin:
# Dint=Dint+ds
# yaz(64,[int(Dint*4)])
# elif Dint>=Dfin:
# df.to_excel(f"{isim}.xlsx")
# kaydet=0
1.3 oracle ash real-time dash-board[편집]
- matplotlib 이용
pip install streamlit cx_Oracle pandas matplotlib
import cx_Oracle
import pandas as pd
import streamlit as st
import time
# Oracle 데이터베이스 연결 설정
def connect_to_oracle(user, password, dsn):
try:
connection = cx_Oracle.connect(user, password, dsn)
return connection
except cx_Oracle.DatabaseError as e:
st.error(f"Database connection error: {e}")
return None
# ASH 데이터를 가져오는 함수
def fetch_ash_data(connection):
query = """
SELECT sample_time,
session_id,
session_state,
event,
wait_class,
blocking_session,
current_obj#,
current_file#,
current_block#
FROM v$active_session_history
WHERE sample_time > sysdate - INTERVAL '5' MINUTE
"""
df = pd.read_sql(query, connection)
return df
import matplotlib.pyplot as plt
# Streamlit 페이지 구성
def main():
st.title("Oracle ASH Real-time Monitoring")
# Oracle 연결 정보 입력받기
st.sidebar.header("Database Connection")
user = st.sidebar.text_input("Username")
password = st.sidebar.text_input("Password", type="password")
host = st.sidebar.text_input("Host")
port = st.sidebar.text_input("Port", value="1521")
service_name = st.sidebar.text_input("Service Name")
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
if st.sidebar.button("Connect"):
connection = connect_to_oracle(user, password, dsn)
if connection:
st.success("Connected to Oracle Database!")
# 실시간 모니터링 시작
placeholder = st.empty()
while True:
# ASH 데이터 가져오기
df = fetch_ash_data(connection)
# ASH 데이터 프레임 출력
with placeholder.container():
st.subheader("Active Session History Data (Last 5 Minutes)")
st.dataframe(df)
# 세션 상태 분포 시각화
st.subheader("Session State Distribution")
session_state_counts = df['SESSION_STATE'].value_counts()
st.bar_chart(session_state_counts)
# 이벤트별 분포 시각화
st.subheader("Wait Events Distribution")
event_counts = df['EVENT'].value_counts().head(10)
st.bar_chart(event_counts)
# 주기적으로 데이터 갱신 (5초마다)
time.sleep(5)
if __name__ == "__main__":
main()
- echart 이용
pip install streamlit cx_Oracle pandas pyecharts streamlit-echarts
- 오라클 연결은 위와 동일
import cx_Oracle
import pandas as pd
import streamlit as st
import time
from pyecharts.charts import Bar
from pyecharts import options as opts
from streamlit_echarts import st_pyecharts
from pyecharts.charts import Page, Line
from pyecharts.faker import Faker
import random
# Oracle 데이터베이스 연결 설정
def connect_to_oracle(user, password, dsn):
try:
connection = cx_Oracle.connect(user, password, dsn)
return connection
except cx_Oracle.DatabaseError as e:
st.error(f"Database connection error: {e}")
return None
# CPU 사용률 데이터를 가져오는 함수
def fetch_cpu_usage(connection):
query = """
SELECT metric_name, value
FROM v$sysmetric
WHERE metric_name = 'Host CPU Utilization (%)'
ORDER BY begin_time DESC
FETCH FIRST 1 ROWS ONLY
"""
df = pd.read_sql(query, connection)
if not df.empty:
return df['VALUE'].values[0]
return None
# 실시간 CPU 사용률 차트 생성
def create_cpu_chart(cpu_data):
line = Line()
line.add_xaxis([i for i in range(len(cpu_data))])
line.add_yaxis("CPU Usage (%)", cpu_data)
line.set_global_opts(
title_opts=opts.TitleOpts(title="Real-time CPU Usage"),
yaxis_opts=opts.AxisOpts(max_=100, min_=0)
)
return line
# ASH 데이터를 가져오는 함수
def fetch_ash_data(connection):
query = """
SELECT sample_time,
session_id,
session_state,
event,
wait_class,
blocking_session,
current_obj#,
current_file#,
current_block#
FROM v$active_session_history
WHERE sample_time > sysdate - INTERVAL '5' MINUTE
"""
df = pd.read_sql(query, connection)
return df
# 세션 상태 분포 차트 생성
def create_session_state_chart(session_state_counts):
bar = Bar()
bar.add_xaxis(session_state_counts.index.tolist())
bar.add_yaxis("Session State", session_state_counts.values.tolist())
bar.set_global_opts(title_opts=opts.TitleOpts(title="Session State Distribution"))
return bar
# 이벤트 분포 차트 생성
def create_event_chart(event_counts):
bar = Bar()
bar.add_xaxis(event_counts.index.tolist())
bar.add_yaxis("Wait Events", event_counts.values.tolist())
bar.set_global_opts(title_opts=opts.TitleOpts(title="Wait Events Distribution"))
return bar
# Function to create a stacked area chart
def create_stacked_area_chart():
# Sample data
x_data = Faker.choose()
y_data1 = Faker.values()
y_data2 = Faker.values()
y_data3 = Faker.values()
# Create a line chart with stacked area style
chart = Line()
chart.add_xaxis(x_data)
chart.add_yaxis("Series 1", y_data1, stack="stack1")
chart.add_yaxis("Series 2", y_data2, stack="stack1")
chart.add_yaxis("Series 3", y_data3, stack="stack1")
chart.set_global_opts(title_opts=opts.TitleOpts(title="Stacked Area Chart"))
return chart
# Streamlit 페이지 구성
def main():
st.title("Oracle ASH Real-time Monitoring with ECharts")
# Oracle 연결 정보 입력받기
st.sidebar.header("Database Connection")
user = st.sidebar.text_input("Username", value="dbcafe")
password = st.sidebar.text_input("Password", type="password", value="")
host = st.sidebar.text_input("Host", value="localhost")
port = st.sidebar.text_input("Port", value="1521")
service_name = st.sidebar.text_input("Service Name",value="orcl")
dsn = cx_Oracle.makedsn(host, port, service_name=service_name)
if st.sidebar.button("Connect"):
connection = connect_to_oracle(user, password, dsn)
if connection:
st.success("Connected to Oracle Database!")
# 실시간 모니터링 시작
placeholder = st.empty()
cpu_data = []
while True:
# CPU 사용률 데이터 가져오기
cpu_usage = fetch_cpu_usage(connection)
if cpu_usage is not None:
# 최대 20개의 데이터를 저장하고 그려줌 (실시간 업데이트용)
if len(cpu_data) >= 20:
cpu_data.pop(0)
cpu_data.append(cpu_usage)
# CPU 사용률 차트 생성
cpu_chart = create_cpu_chart(cpu_data)
# 차트 업데이트
with placeholder.container():
st.subheader("CPU Usage % ")
st_pyecharts(cpu_chart,key=f'unique_chart_key_{random.randint(1, 10000)}')
# ASH 데이터 가져오기
df = fetch_ash_data(connection)
# ASH 데이터 프레임 출력
with placeholder.container():
st.subheader("Active Session History Data (Last 5 Minutes)")
st.dataframe(df)
# 세션 상태 분포 시각화
st.title('Stacked Area Chart')
# Generate the chart
stacked_area_chart = create_stacked_area_chart()
# Convert the chart to HTML and display it
# st.components.v1.html(chart.render_embed(), height=500)
st_pyecharts(stacked_area_chart,key=f'unique_chart_key_{random.randint(1, 10000)}')
# 세션 상태 분포 시각화
st.subheader("Session State Distribution")
session_state_counts = df['SESSION_STATE'].value_counts()
session_state_chart = create_session_state_chart(session_state_counts)
st_pyecharts(session_state_chart,key=f'unique_chart_key_{random.randint(1, 10000)}')
# 이벤트별 분포 시각화
st.subheader("Wait Events Distribution")
event_counts = df['EVENT'].value_counts().head(10)
event_chart = create_event_chart(event_counts)
st_pyecharts(event_chart,key=f'unique_chart_key_{random.randint(1, 10000)}')
# 주기적으로 데이터 갱신 (5초마다)
time.sleep(5)
if __name__ == "__main__":
main()