Sybase IQ: Analytický svět o 90° lépe
Honza Válek Anywhere s.r.o.
[email protected]
Agenda 1 2 3 4 5 6 7 8
BI refresh Symptomy skomírající BI infrastruktury Sybase IQ Vysoký výkon + Nízké TCO Potřeby business uživatele Lifecycle management Implementace DWH IQ pomocí PowerDesigner Sybase IQ Akcelerátor (IQ + PowerDesigner)
BI refresh
BI architektura Zdrojové Systémy
Extract, Transform, Analytický Server a Load
Reporting a Analýza
Sybase Replikace Sybase ETL Informatica Data Quality Pentaho IBM
EDW
Další Zdroje
Sybase® PowerDesigner® Datové Modelování / Metadata Management / Business Process Modeling
Škálovatelnost Systému
Rostoucí nároky na analytiku Analytika/BI Požadavky + Sybase IQ Možnosti OLTP Možnosti OLTP Požadavky
BI Škálovatelnost
Rozevírající se nůžky
Uživatelé Dotazy Data
Transakční škálovatelnost Uživatelé Transakce Data
1970s IBM Info Center
1980s DSS
1990s Datawarehouse
2000s BI/Analytics
IMS to DB2/DL1
Sequent Pyramid Britton Lee
RedBrick Teradata
Sybase IQ Netezza
Strategické BI (pomalé, drahé) …… Taktické BI (rychlé, hospodárné)
OLTP RDBMS Možnosti
8 SIGNÁLŮ KRIZE BI Pro uživatele 1 Rostoucí odezva na dotazy 2 Neúplná či neaktuální data 3 Dlouho se čeká na nové reporty Pro IT 4 Rostoucí nároky na administraci 5 Doba vymezená pro load dat přestává stačit 6 Nezvladatelný backup Pro sponzora 7 Bobtnající náklady 8 Ztráta agilnosti
KTERÝM SMĚREM JÍT? • Průběžně přikupovat hardware, software a personál – Přidávat CPU a servery – Stavět stále větší clustery – Přidávat další a další disky
• Přijmout neodzkoušenou architekturu – Hybridní řešení
• Zvolit alternativu – Masivně paralelní engine – Hardware engine – Sloupcově orientovaný engine
Sybase IQ
Efektivní analytická práce s velkým objemem dat Typické problémy OLTP při analytické práci • Dlouhotrvající komplexní dotazy • Velké nároky na diskový prostor a HW • Potřeba ručního ladění výkonu
Vertikální uložení dat v Sybase IQ
Page 1 C1 C2
Page 1 of C1
Page 1 of C2
Page 1 of C3
C3
. . . C50
C1
C2
C3
C2
C3
. . . C50
C1
C2
C3
C1 C2 ... C1 C2
C3
. . . C50
C3
. . . C50
C1 ... C1
C2 ... C2
C3 ... C3
C1
Page 2 of C2
Page 2 of C3
C3
. . . C50
Page 2 of C1
C2
C3
. . . C50
C1
C2
C3
C1 C2 ... C1 C2
C3
. . . C50
C1
C2
C3
C1 ... C1
C2 ... C2
C3 ... C3
Page 2 C1 C2 C1
C3
. . . C50
Vertikální uložení dat Efektivnější zpracování analytických dotazů •Načítání pouze těch dat, která jsou skutečně potřeba •Extrémní úspora I/O operací – bezkonkurenční odezva na uživatelské dotazy •SELECT JM, AVG(PLAN) … GROUP BY JM
ID
Tradiční OLTP JM DPT PLAN
1 2 3 4 5 6 7 8
aaa bbb aaa ddd aaa bbb ddd aaa
zz yy xx vv uu tt ss rr
70,5 80,6 90,7 60,4 50,3 40,2 30,1 20,0
Sybase IQ ID
JM
1 2 3 4 5 6 7 8
aaa bbb aaa ddd aaa bbb ddd aaa
DPT
PLAN
zz yy xx vv uu tt ss rr
70,5 80,6 90,7 60,4 50,3 40,2 30,1 20,0
Efektivní využití diskového prostoru Ukládání dat pomocí bitových (FP) indexů • Data = indexy • Bez požadavků na dodatečný diskový prostor
Zjednodušená struktura FP indexu
Lookup Pages Color
Token
(*)
Red
1
102
Blue
2
412
Green
3
662
• Mapovací tabulka • Na základě kardinality je vytvářen x bytový FP index Raw Data • 255 => 1 byte • 65536 => 2 byte Data Values • 16 mil => 3 byte Red • V ostatních případech „flat“
• Tabulka dat
Důsledek
Data Pages 1 2
Blue
3
Green
1
Red
3
• Data v databázi zaberou méně prostoru než v „surovém“ stavu
1 3 2
Efektivní využití diskového prostoru Uložení 1 TB surových dat ÚDAJE Z TPC BENCHMARKU 6.5 TB 5.1 TB 4.7 TB
Data v databázi IQ zaberou méně prostoru než v „surovém“ stavu!!!
4 TB
0. 0.4 47 TB
•DB2 UDB •IBM AIX
•Teradata •NCR
•Informix •HP
•Oracle •Sun
•Sybase IQ •Sun
Redukce a zrychlení I/O operací High Group (HG)
Redukce a zrychlení I/O operací Low Fast (LF)
Agregace a vyhledávání High Non Group (HNG)
Umístění objektů a partitioning CREATE TABLE richard ( col1 INT IQ UNIQUE (65500) col2 VARCHAR(20), col3 CLOB PARTITION (p1 IN dsp11, p2 IN dsp12, p3 IN dsp13), col4 DATE, col5 BIGINT, col6 VARCHAR(500) PARTITION (p1 IN dsp21 p2 IN dsp22), PRIMARY KEY (col5) IN dsp2 ) IN dsp1 PARTITION BY RANGE (col4) (p1 VALUES <= (‘2006/03/31’) IN dsp31, p2 VALUES <= (‘2006/06/30’) IN dsp32, p3 VALUES <= (‘2006/09/30’) IN dsp33 );
dsp1
Lookup Store for all columns
dsp2
Primary Key (HG) for col5
dsp3
DATE index for col4_date
dsp11
FP Index for col3 (where col4 <= 2006/03/31)
dsp12
FP Index for col3 (where col4 <= 2006/06/30)
dsp13
FP Index for col3 (where col4 <= 2006/09/30)
dsp21
FP Index for col6 (where col4 <= 2006/03/31)
dsp22
FP Index for col6 (where col4 <= 2006/06/30)
dsp31
FP Index for col1 col2 col4 col5 (where col4 <= 2006/03/31)
dsp32
FP Index for col1 col2 col4 col5 (where col4 <= 2006/06/30)
dsp33
FP Index for col1 col2 col4 col5 and col6 (where col4 <= 2006/09/30)
CREATE DATE INDEX col4_date on richard (col4) IN dsp3;
IQ
LOAD dat •Sybase IQ = analytická databáze •Rychlý SELECT => Pomalý INSERT ? •Řešení: Bulk LOAD ! (i replikace) •Load dat je 3x paralelní: – Tabulky – Partitions – Sloupce
•Ukládá se zlomek objemu zdrojových dat „Sybase IQ je od počátku koncipováno jako výhradně analytická databáze, která bude ve společnosti vždy existovat spolu s primárními databázovými transakčními systémy, z nichž bude v pravidelných intervalech plněna.“
Sybase IQ Zvenku a Zevnitř Zvenku:
ANSI relační databáze Tabulky se sloupci a řádky
Popodra Uložené procedury, funkce, views Konkurentní R/W ANSI 99 SQL , T-SQL, XML, OLAP funkce Konexe aplikací ODBC, JDBC, Open Client, Oledb Podpora většiny nejrozšířenějších BI nástrojů Nezávislé schéma Star, Relational, Flat (Rcubes) plněno ETL, replikací OS Platforma Unix (Solaris, HP-UX, AIX), Windows XP, Linux Storage technologie FC SCSI, ATA
A další….
Zevnitř:
Architektura pro reporting a analýzu Flexibilní a škálovatelné
Rychlé odezvy na dotazy Bohatá indexace pro připravené i ad-hoc dotazy Paralelní běh Rychlý load Bulk load Škálovatelnost uložení dat Sloupcové uložení umožňuje efektivní kompresi Horizontální i certikální řezání tabulek Tokenizace podporuje deduplikaci uložených dat Škálovatelnost z hlediska uživatelů Multiplex nasazení umožňuje masivní konkurentní čtení a zápis Podpora nestrukturovaných formátů Text, Obrázky, Video, Dokumenty, … A další….
SYBASE IQ A BI EKOSYSTÉM Zdrojové Systémy
Extract, Transform, Analytický Server a Load
Reporting a Analýza
Sybase Replikace Sybase ETL Informatica Data Quality Pentaho IBM
EDW
Další Zdroje
Sybase® PowerDesigner® Datové Modelování / Metadata Management / Business Process Modeling
Vysoký výkon i nízké TCO
Sybase IQ a TCO •Náklady na pořízení •Licence - CPU •Úložný prostor •Paměť •HW •Náklady na správu databáze •ANSI SQL databáze •Typicky 5-10% utilizace stávajících DBA •Náklady na rozvoj a správu BI řešení •Viz dále
Sybase IQ a DW tuning •DB statistiky ? •OLAP kostky (MOLAP) ? •Agregační tabulky ? •Materializovaná view ? •Další a další Data marty ? •X verzí jedné tabulky ? •SQL hinty ? •Indexy… ?
Přínosy pro business uživatele
Analytický výkon •Prokázaná bezkonkurenční rychlost odezvy na dotazy bez nekoncepčních berliček •Analýzy, které si dříve společnost nemohla dovolit, nedokázala představit
Sybase IQASE vs. Query SybaseTimes ASE IQ versus 1,500,000
Q
1,000,000 500,000 0
ASE 1
2
3
4
IQ 5
6
1 2 3 4 5 6
SYBASE ASE
150,439 1,470,418 497,715 723,550 864,791 741,383
SYBASE IQ
110,806 301,525 7,057 9,693 181,882 122,193
ROZDÍL
136% 488% 7052% 7465% 475% 607%
Všechny dotazy join tabulek 850M, 165M a 60M řádek
Konečně JEDNA verze pravdy •Datový sklad Sybase IQ umožňuje uložení veškerých dat pouze jednou, v libovolné míře detailu, bez nutnosti sumací a agregací a to včetně hluboké historie!!! OLAP
OLAP
DM DM
EDWH
DM
DWH
Testování, Dokumentace
Zkrácení intervalu Požadavek - Řešení Požadavek na nový report
Nastavení loadu dat do DWH
Definice reportu
Verzování tabulek
Tvorba pomocných tabulek
Tvorba fyzických OLAP kostek
Materializace view
Ladění SQL dotazu
Hotový report
Testování BI •Funkční testování •Funkcionalita jednotlivých komponent •Integrace komponent
•Obsahové testování •Jádro testování BI •Všechny vrstvy obsahují taková data, která mají •Nezbytné zapojení business uživatelů
Svoboda pro power-usery (BI playground) • • • • • • •
Menší závislost na IT Databázi nevadí ani „nepoučené“ dotazy Bezkonkurenční analytická odezva Nezávislost na DB schématu View nezpomalují výkon řešení Rychlý load dat Komprese dat
Business analytik nesmí být limitován technologií !
Information Lifecycle Management
Information Lifecycle Mgmt: Roll-on/off Sep Aug Jul Jun
1: Roll-On: Load Monthly Table Partition
3: Mark Partition Read-Only
Jun
May
Apr
2: Back-Up SAN Replication
Mar
4: Roll-Off: 5: Re-use Drop Partition DBSpace
Feb
Jan
Dec
Partition Table by Date Backup and Mark Read-Only Drop Old Data Partitions
Information Lifecycle Management? Proaktivní management informace v průběhu různých fází jejího životního cyklu a stárnutí. Důvody pro implementaci ILM: - Větší produktivita DBA - Snížení režijních nákladů na uložení dat a Zabezpečení citlivých dat - Zlepšení výkonu BI aplikací a zrychlení zálohování Data warehousing Prostředí Současní Sybase IQ zákazníci Současní Sybase IQ zákazníci s > 2 TB uložených dat
Sybase IQ vize 2011
Information Lifecycle Management – IQ + PD • Workspace Data Analytics v2.5 IDE Modeling Plug-in (PowerDesigner v15.1) pomáhá modelovat pravidla pro databáze Sybase IQ ve vztahu k VLDM option • • • • •
Definovat vrstvy uložení dat Definovat pravidla partitioningu a uchovávání dat v jednotlivých vrstvách Simulovat celkové náklady uložení a úspory při různých scénářích Generovat skripty pro data partitioning a přesun dat s nápovědou kdy skripty spouštět Administrovat skripty přes Sybase Central
•
High Performance
Near Line
Historical
Migrace informací (dat) v čase 33
Screenshot: Workspace Data Analytics IDE Modeling Plug-in (PowerDesigner v15.1) životní cyklus dat a nákladová analýza
Implementace Sybase IQ DWH pomocí PowerDesigner
Reverse Engineering OLTP Databáze PowerDesigner zajistí automatický reverse engineer zdrojové databáze (uživatelé, tabulky, view, indexy, integritní omezení, statistiky…) přes ODBC nebo JDBC
Reverse Engineering OLTP Databáze
Generování Sybase IQ fyzického datového modelu (PDM) Jednoduše lze vygenerovat nový PDM pro IQ na základě existujícího PDM OLTP databáze: – V PDM OLTP databáze vybereme Tools>Generate Database – Vybereme Sybase IQ jako typ RDBMS – V Detail tab, vybereme “Generate mappings”
Mezi těmito dvěma modely PoweDesigner automaticky vytvoří mapování na úrovni tabulek a jednotlivých sloupců. Na základě něho umí generovat skripty pro přenos dat mezi OLTP a Sybase IQ
Generování Sybase IQ DWH Vygenerujeme Sybase IQ datový sklad pomocí Database>Generate Database •Přes ODBC/JDBC přímo do databáze •Do sql skriptu
Přesun dat z OLTP databáze do Sybase IQ Tři základní metody pro přesun dat do Sybase IQ: – Spouštění skriptů pro export dat z OLTP databáze do flat souborů a skriptů pro load dat do Sybase IQ – Spouštění insert … location skriptů pro Sybase IQ – Použít ETL produkt PowerDesigner 15 automatizuje první dvě metody PowerDesigner používá pro generování těchto skriptů data mapping
Generování exportních skriptů a loadovacích skriptů pro Sybase IQ Exportní skripty exportují data z OLTP databáze do flat souborů Uživatel nakopíruje tyto soubory na stroj se Sybase IQ Loadovací skripty loadují data z flat souborů do Sybase IQ Source Database
IQ
PowerDesigner Export Script
Uživatel
Load Script
Copy the files
Data Movement IQ V PDM pro IQ, vyberte Model>Extended Model Definitions Přidejte “Data Movement IQ” extended model definition
Použití Mapping Editoru k ověření mapování mezi OLTP a IQ Vyberte Tools>Mapping Editor Definujte zdrojovou tabulku/sloupec pro cílovou tabulku/sloupec
Příklad exportních skriptů pro ASE
Příklad exportních skriptů pro Oracle
Příklad exportních skriptů pro DB2
Příklad loadovacích skriptů
Tvorba nového Sybase IQ DWH První cestou je tvorba Sybase IQ DWH z CDM – Identifikace dat pro budoucí datový sklad – Návrh konceptuálního datového modelu (CDM) – Vygenerování fyzického datového modelu (PDM) pro Sybase IQ z CDM – Definice mappingů z PDM zdrojové databáze do PDM Sybase IQ – Specifikace transformací – Definice indexů a join indexů – Generování Sybase IQ DWH – Generování skriptů pro export a load – přenos dat Druhou cestou je tvorba Sybase IQ DWH přímo z OLTP databáze – Revers engineering OLTP databáze PDM – Využití Mapping Editoru pro tvorbu IQ tabulek a sloupců ze zdrojových tabulek a sloupců – Definice indexů a join indexů – Generování Sybase IQ DWH – Generování skriptů pro export a load – přenos dat
IQ Akcelerátor Powered by PowerDesigner
Plně automatizovaný Sybase IQ Akcelerátor
Otázky, prosím…
