DOAG 2014_SQL als ETL Tool_Christian König CGI

SQL als ETL Tool
DOAG Konferenz Nürnberg 2014
Christian König, CGI Business Intelligence Expert
18. November 2014
© CGI Group Inc.
Referent: Christian König
Business Intelligence Expert,
CGI (Germany) GmbH & Co. KG
Schwerpunkte
Konzeption und Implementierung von
komplexen Datenbewirtschaftungen im
Bereich BI&DWH
Oracle Warehouse Builder, Oracle Data
Integrator, Informatica, Ab Initio & SQL
Ab Initio Certified Technician
Informatica DI PC Developer Certified
Specialist
Branchenfokus Telekommunikation,
Banking & Finance, Retail
2
Warum SQL als ETL Tool?
Lizenzkosten
„Tool“ schon vorhanden
SQL
auch in Verbindung mit
anderen Tools nutzbar
Entwicklungszeit
3
Inhalte
1
Metaprogramming
2
WITH Clause
3
Model Clause
4
Anforderungen „ETL Tool“
5
Fazit
4
Metaprogramming (1)
Beispiel Staging:
INSERT INTO s_ORDERS(
SELECT * FROM ORDERS
WHERE INSERT_DATE > (Select max(load_date) from utl_src_loads
where table_name = 'ORDERS')
);
Mit Execute Immediate:
begin
FOR t_name in (Select table_name from utl_src_tables)
LOOP
EXECUTE IMMEDIATE 'INSERT INTO s_' || t_name.table_name ||
'(SELECT * FROM ' || t_name.table_name || ' WHERE INSERT_DATE >
(Select max(load_date) from
utl_src_loads where table_name = ''' ||
t_name.table_name || ''') )';
commit;
END LOOP;
end;
5
Metaprogramming (2)
declare
str_sql varchar(2000);
l_cnt number(8);
-- Cursor gibt Tabellenname, Attributliste, und Ladedatum zurück
cursor cur_get_tables is
select
utc.table_name
, listagg(column_name, ',') within group(order by column_name) as col_list
, max(trunc(nvl(ld_dt, to_date('01.01.1900', 'dd.mm.yyyy')))) as ld_dt
from
user_tab_columns utc
left outer join (select max(load_date) ld_dt, table_name
from
utl_src_loads group by table_name)
src_loads on (utc.table_name = src_loads.table_name)
where utc.table_name in (select table_name from utl_src_tables)
group by utc.table_name ;
6
Metaprogramming (3)
begin
for t in cur_get_tables
loop
begin
log_start_sa(t.table_name);
str_sql :=
'insert into
s_' || t.table_name || ' (' || t.col_list || ')
( select ' || t.col_list || '
from ' || t.table_name || '
where insert_date >= :load_date) ';
execute immediate str_sql using t.ld_dt;
log_end_sa(tablename, SQL%ROWCOUNT);
commit;
exception when others then
log_err_sa(tablename, sqlerrm);
end;
end loop;
end;
7
Metaprogramming (4)
• Komplexere Ladeprozesse
•
•
•
•
• Deltaerkennung
• Partitionsweises laden
• External tables
Data Quality
Surrogate Keys
Testdatenanonymisierung
Andere generische Prozesse
8
WITH Clause (Subquery factoring) (1)
Select * from
(select * from
(select * from sales
)
) b
inner join (select * from customers) c
on (c.id = b.cust_id);
with
a as ( select * from sales
),
b as ( select * from a
),
c as ( select * from customers
)
Select * from
b inner join c on (c.id = b.cust_id);
9
WITH Clause (2)
• Vorteile: Lesbarkeit
• Nicht zu viele Ebenen
• Ergebnis kann mehrmals im Query verwendet werden
• Query kann schrittweise aufgebaut werden
with
cust as (
Select * from (
select c.*
, rank() over(partition by cust_id
order by valid_from desc) rk
from customers c
) where rk = 1
)
Select *
from
sales b
inner join cust
on (c.id = b.cust_id);
10
SQL Model Clause (1)
SELECT country, product, year, sales from fkt_sales;
• Die Verkäufe von Tomaten in Jahr 2002 sollen mit 1.2 multipliziert werden
• Für das Jahr 2003 sollen die Verkäufe der letzten beiden Jahre addiert werden
case when product = 'Tomatoe' and year = 2002
then sales*1.2
else
case when product = 'Tomatoe' and year = 2003 then
lag(sales, 1) over(partition by product order by year) +
lag(sales, 2) over(partition by product order by year)
else
sales
end
end as sales
11
SQL Model Clause (2)
SELECT country, product, year, sales from fkt_sales;
• Die Verkäufe von Tomaten in Jahr 2003 sollen mit 1.2 multipliziert werden
• Für das Jahr 2003 sollen die Verkäufe der letzten beiden Jahre addiert werden
case when product = 'Tomatoe' and year = 2002
then sales*1.2
else
case when product = 'Tomatoe' and year = 2003 then
lag(sales, 1) over(partition by product order by year) +
lag(sales, 2) over(partition by product order by year)
else
sales
end
end as sales
Sales['Tomatoe', 2003] = Sales['Tomatoe', 2001] + Sales['Tomatoe',2002]
,Sales['Tomatoe', 2002] = Sales['Tomatoe', 2002]*1.2
12
SQL Model Clause (3)
• Definition Dimensionen und Kennzahlen:
PARTITION BY (country) DIMENSION BY (product, year)
MEASURES (sales sales)
• Ein komplettes Beispiel:
SELECT country, product, year, sales
FROM fkt_sales
WHERE country IN ('Germany', 'Japan')
MODEL
PARTITION BY (country) DIMENSION BY (product, year)
MEASURES (sales sales)
RULES
(
Sales['Tomatoe', 2003] = Sales['Tomatoe', 2001]+Sales['Tomatoe',2002]
,Sales['Tomatoe', 2002] = Sales['Tomatoe', 2002]*1.2
)
13
SQL Model Clause(4)
• Für alle Produkte, nehme den Vorjahreswert *2:
Sales[any, 2004] = Sales[CV(product), CV(year)-1]*2,
• Für Kartoffeln im Jahr 2001, nehme die max. Verkäufe zwischen
1997 und 2000:
Sales['Tomatoe',2001]
= MAX(sales)['Tomatoe', year BETWEEN 2000 AND 2003]
• Anwendungsfälle:
• Verkaufsprovisionierung, Boniberechung
• Unterschiedliche Ausnahmen und Sonderbehandlungen für
Vertriebsorganisationen
• Regeln sind abhängig von mehreren Dimensionen
14
SQL Model Clause (5)
-- alle Online-Verkäufe von prod3 in Deutschland bekommen 10% des
Verkaufspreises + 5 EUR für jedes Produkt, das mehr verkauft wurde als
im Vorjahr
provi['Prod3', any, 'Germany', 'Online', any] =
amount['Prod3', CV(year), 'Germany', 'Online', CV(sales_org)]*0.1
+ ( sales['Prod3', CV(year), 'Germany', 'Online', CV(sales_org)]
- sales['Prod3', CV(year)-1, 'Germany', 'Online', CV(sales_org)]
)*5
-- Für alle Verkäufe in Frankreich werden die durchschnittlichen
Verkaufszahlen der letzten 3 Jahre genommen
, provi[any, any, 'France', 'Online', any] =
avg(sales)[CV(product), year between cv(year)-2 and cv(year) ,
'France', 'Online', CV(sales_org)]*0.2
15
Anforderungen „ETL-Tool“
1
Word, Visio, PPT, Excel,
Wiki (manuell)
Runtime Metadata
2
Log-Tabelle
Insert SQL vor / nach ETL
Versioning
3
SVN, Git, …
Data Lineage / Impact Analyse
4
all_dependencies, grep
5
DBMS_Scheduler,
cron, batch files
Architekturkonzept, Coding Guidelines
Datenmodell, Best Practices, Dokumentation
Scheduling
16
Vor- und Nachteile
•
•
•
•
Lizenzkosten
Entwicklungszeit
Skills in Entwicklung und Betrieb vorhanden
Flexibilität und Adaptierbarkeit
• Quellsystemanbindung (aber: Connectoren kosten oft
•
•
•
Aufpreis)
Keine grafische Oberfläche, nur Code
Logging, Metadata, Data Lineage muss selbst entwickelt
werden
Viel Inhouse-Wissen: Outsourcing, Offshore etc. eher
schwierig
17
Fazit
• Homogene Systemlandschaft, Oracle + Text
•
•
•
•
•
•
•
•
•
Files
SQL und Oracle Skills vorhanden
Hohe Anforderungen an Adaptierbarkeit
Betrieb (Monitoring, Schedulung)
Entwickler Präferenz Code
Agiles Projektumfeld
1
SQL als ETL-Tool
Hetereogene Systemlandschaft (SAP, Host)
ETL-Tool-Skills vorhanden
Geringe Anfroderungen an Adaptierbarkeit
Entwickler Präferenz grafisches Tool
Komplexes Projektumfeld (offshore,
Outsourcing, …)
2
Separates ETL-Tool sinnvoll
18
Ich freue
mich auf Ihre
Kommentare &
Fragen!
Christian König
Consultant BI
Mobile: +49 170569 7832
E-Mail: [email protected]
19
Vielen Dank für Ihre
Aufmerksamkeit
Our commitment to you
We approach every engagement with one
objective in mind: to help clients succeed