Lakehouse: ko'l va ombor birlashgani

diagramma(
    {
        "Manbalar": (0, 1.8, 1.9, 1.0, "manba"),
        "KO'L\n(arzon, tartibsiz)": (2.7, 3.2, 2.4, 1.1, "saqlash"),
        "OMBOR\n(qimmat, tartibli)": (2.7, 0.4, 2.4, 1.1, "saqlash"),
        "ETL\n(yuk mashinasi)": (6.0, 1.8, 2.1, 1.0, "qayta"),
        "ML / Data Science": (9.2, 3.2, 2.4, 1.0, "xizmat"),
        "Hisobot / BI": (9.2, 0.4, 2.4, 1.0, "xizmat"),
    },
    [
        ("Manbalar", "KO'L\n(arzon, tartibsiz)"),
        ("KO'L\n(arzon, tartibsiz)", "ETL\n(yuk mashinasi)"),
        ("ETL\n(yuk mashinasi)", "OMBOR\n(qimmat, tartibli)"),
        ("KO'L\n(arzon, tartibsiz)", "ML / Data Science"),
        ("OMBOR\n(qimmat, tartibli)", "Hisobot / BI"),
    ],
    nom="kol_ombor.png", kenglik=13,
    izoh="Eski tuzilish: ikki nusxa, ular orasida doimiy ko'chirish.",
)

import pandas as pd
show(pd.DataFrame([
    {"": "Narxi", "Ko'l": "Arzon", "Ombor": "Qimmat", "Lakehouse": "Arzon"},
    {"": "Sxema", "Ko'l": "Yo'q", "Ombor": "Bor", "Lakehouse": "Bor"},
    {"": "Tranzaksiya", "Ko'l": "Yo'q", "Ombor": "Bor", "Lakehouse": "Bor"},
    {"": "Tarix", "Ko'l": "Yo'q", "Ombor": "Cheklangan", "Lakehouse": "Time travel"},
    {"": "Har xil ma'lumot", "Ko'l": "Ha", "Ombor": "Yo'q", "Lakehouse": "Ha"},
    {"": "Nusxalar soni", "Ko'l": "1", "Ombor": "1", "Lakehouse": "1 (jami)"},
]))

python3 -c "
import sys, shutil; sys.path.insert(0, 'src')
import pandas as pd, pyarrow as pa
from quvur.muz import katalog

USTUN = ['elon_id','tuman','narx_mln','xonalar','tavsif_uzunligi','broker']
d = pd.read_csv('../datasets/ijara.csv').drop_duplicates('elon_id')

shutil.rmtree('ombor', ignore_errors=True)
c = katalog()
c.create_namespace_if_not_exists('rostuy')
ar = pa.Table.from_pandas(d[USTUN], preserve_index=False)
t = c.create_table('rostuy.elon', schema=ar.schema)
t.append(ar)
print(f'lakehouse jadvali: {len(t.scan().to_arrow()):,} qator')
print(f'fayllar          : {len(list(t.scan().plan_files()))}')
"

python3 -c "
import sys, time; sys.path.insert(0, 'src')
from quvur.muz import katalog

c = katalog()
t = c.load_table('rostuy.elon')

# 1) pyiceberg - to'g'ridan-to'g'ri
t0 = time.perf_counter()
ark = t.scan().to_arrow()
a = (time.perf_counter()-t0)*1000
print(f'1. pyiceberg  : {len(ark):>6,} qator  {a:>7.1f} ms')

# 2) pandas - tahlilchi uslubi
t0 = time.perf_counter()
df = t.scan().to_pandas()
ort = df.groupby('tuman')['narx_mln'].mean().sort_values(ascending=False)
b = (time.perf_counter()-t0)*1000
print(f'2. pandas     : {len(df):>6,} qator  {b:>7.1f} ms')

# 3) DuckDB - SQL uslubi (Iceberg qaytargan arrow ustida)
import duckdb
con = duckdb.connect()
t0 = time.perf_counter()
r = con.execute('SELECT tuman, avg(narx_mln) m FROM ark GROUP BY tuman ORDER BY m DESC').fetchall()
d_ms = (time.perf_counter()-t0)*1000
print(f'3. DuckDB SQL : {len(r):>6,} guruh  {d_ms:>7.1f} ms')
print()
print('Eng qimmat tuman:')
print(f'  pandas : {ort.index[0]:14s} {ort.iloc[0]:.3f}')
print(f'  DuckDB : {r[0][0]:14s} {r[0][1]:.3f}')
print()
print(f'natijalar bir xilmi: {abs(ort.iloc[0] - r[0][1]) < 1e-9}')
con.close()
"

import pandas as pd
show(pd.DataFrame([
    {"Qatlam": "Bronza", "Nima": "Xom manba, o'zgartirilmagan",
     "Kim ishlatadi": "Muhandis", "Nega kerak": "Qayta hisoblash imkoni"},
    {"Qatlam": "Kumush", "Nima": "Tozalangan, sxema qo'llangan",
     "Kim ishlatadi": "Ma'lumot olimi", "Nega kerak": "Ishonchli asos"},
    {"Qatlam": "Oltin", "Nima": "Yig'ilgan, biznes ta'rifi",
     "Kim ishlatadi": "Tahlilchi, direktor", "Nega kerak": "Tez hisobot"},
]))

python3 -c "
import sys, pathlib; sys.path.insert(0, 'src')
import pandas as pd, pyarrow as pa
from quvur.muz import katalog

c = katalog()
c.create_namespace_if_not_exists('bronza')
c.create_namespace_if_not_exists('kumush')
c.create_namespace_if_not_exists('oltin')

xom = pd.read_csv('../datasets/ijara.csv')

# BRONZA: qanday kelgan bo'lsa shunday
b = pa.Table.from_pandas(xom, preserve_index=False)
try: c.drop_table('bronza.elon')
except Exception: pass
tb = c.create_table('bronza.elon', schema=b.schema); tb.append(b)

# KUMUSH: takrorni tashlaymiz, qoidaga mos bo'lmaganini olib tashlaymiz
k = xom.drop_duplicates('elon_id')
k = k[(k['narx_mln'] > 0) & (k['maydon'] >= 10) & (k['xonalar'].between(1, 10))]
kt = pa.Table.from_pandas(k, preserve_index=False)
try: c.drop_table('kumush.elon')
except Exception: pass
tk = c.create_table('kumush.elon', schema=kt.schema); tk.append(kt)

# OLTIN: biznes savoliga tayyor javob
o = (k.groupby('tuman')
       .agg(elonlar=('elon_id','count'),
            ort_narx=('narx_mln','mean'),
            broker_ulushi=('broker','mean'))
       .reset_index())
ot = pa.Table.from_pandas(o, preserve_index=False)
try: c.drop_table('oltin.tuman_xulosa')
except Exception: pass
to = c.create_table('oltin.tuman_xulosa', schema=ot.schema); to.append(ot)

def hajm(t):
    return sum(pathlib.Path(f.file.file_path.replace('file://','')).stat().st_size
               for f in t.scan().plan_files())

print(f'{\"qatlam\":10s} {\"qatorlar\":>10s} {\"hajm KB\":>10s}')
print('-' * 34)
for nom, t in (('bronza', tb), ('kumush', tk), ('oltin', to)):
    n = len(t.scan().to_arrow())
    print(f'{nom:10s} {n:>10,} {hajm(t)/1024:>10.1f}')
"

python3 -c "
import sys, time; sys.path.insert(0, 'src')
from quvur.muz import katalog
import pandas as pd

c = katalog()
kumush = c.load_table('kumush.elon')
oltin = c.load_table('oltin.tuman_xulosa')

t0 = time.perf_counter()
d = kumush.scan().to_pandas()
r1 = d.groupby('tuman')['narx_mln'].mean().sort_values(ascending=False)
a = (time.perf_counter()-t0)*1000

t0 = time.perf_counter()
r2 = oltin.scan().to_pandas().sort_values('ort_narx', ascending=False)
b = (time.perf_counter()-t0)*1000

print(f'kumushdan hisoblash : {a:7.1f} ms')
print(f'oltindan o\\'qish     : {b:7.1f} ms')
print(f'oltin {a/b:.1f}x tez')
print()
print(f'javob bir xilmi: {abs(r1.iloc[0] - r2[\"ort_narx\"].iloc[0]) < 1e-9}')
print(f'  eng qimmat tuman: {r2[\"tuman\"].iloc[0]} ({r2[\"ort_narx\"].iloc[0]:.3f} mln)')
"

python3 -c "
import sys; sys.path.insert(0, 'src')
from quvur.muz import katalog
c = katalog()
for t in ('bronza.elon', 'kumush.elon', 'oltin.tuman_xulosa'):
    try: c.drop_table(t); print(f'{t} o\\'chirildi')
    except Exception: pass
print('rostuy.elon saqlanadi')
"