webinar@lunchtime - sas · 2018. 10. 26. · copyright © sas institute inc. all rights reserved....
TRANSCRIPT
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Webinar@LunchtimeSAS und Datenbanken – ein gutes Paar
Copyright © SAS Inst itute Inc. A l l r ights reserved.
ModerationAnne K. Bogner-Hamleh
SAS Institute GmbH
Sr Curriculum Consultant
Xing-Profil:http://www.xing.com/profile/AnneKatrin_BognerHamleh?key=0.0
Herzlich Willkommen bei Webinar@Lunchtime
Hinweise zum Ablauf des Webinars:
• Teilnehmer sind automatisch “stumm” geschaltet
• Sie können Nachrichten an den Moderator senden und Fragen stellen
• Die Veranstaltung wird aufgezeichnet. Die Unterlagen und die Aufzeichnung werden auf www.sas.de/lunchtime bereit gestellt.
TrainingEva-Maria Kegelmann
SAS Institute AG
Sr Technical Training Consultant
Copyright © SAS Inst itute Inc. A l l r ights reserved.
SAS und Datenbanken – ein gutes Team
Einführung in SAS und Datenbankverarbeitung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice und wer‘s noch genauer wissen will
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Einführung in SAS und Datenbankverarbeitung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice und wer‘s noch genauer wissen will
SAS und Datenbanken – ein gutes Team
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Einführung: SAS und Datenbanken• Sie möchten mit Daten, die in einer Datenbank gehalten
werden, effizient arbeiten, z. B. Oracle oder DB2.
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Verarbeitungsoptionen
• Verarbeitung in SAS
• Daten werden über das Netzwerk in SAS geschoben
• Code wird in SAS verarbeitet
• Ergebnis ist in SAS
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Verarbeitungsoptionen
• Verarbeitung in der Datenbank (empfehlenswert!)
• Code wird über das Netzwerk in die Datenbank geschoben
• Code wird in der Datenbank verarbeitet
• Ergebnis wird ggf. in einerSAS Datei gespeichert
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Daten in DB oder SASVergleich
Daten in der Datenbank
• Alle Daten liegen schon vor
• Schnelle Datenverarbeitungin der DB
Daten in SAS
• Breite Analysemöglichkeiten
• Weiterhin bewährte Arbeitsweise
• Daten müssen ggf. erst erstelltwerden
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Welche Tabellen gibt es?• Liste der Datenbank-Tabellen kann angezeigt werden:
DICTIONARY.TABLES
oder auch
SASHELP.VTABLE
proc sql;create table myDBMS_tables asselect *from dictionary.tableswhere libname = "MYDB"order by memname ;
quit;Case-sensitiv
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Einführung in SAS und Datenbankverarbeitung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice und wer‘s noch genauer wissen will
SAS und Datenbanken – ein gutes Team
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Optimierung von Abfragen
• Pass-Through-Verarbeitung
• Effizientester Weg der Datenabfrage auf Datenbank-Daten
• “Pass-Through” Durchreichen des Codes an die Datenbank zur dortigen Verarbeitung
• Zwei Arten von Pass-Through:
• Impliziter Pass-Through
• Expliziter Pass-Through
Effiziente Verarbeitung mit Datenbanken
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Impliziter Pass-Through
• Optimierungstechnik in PROC SQL für Abfragen auf eine Datenbank
• Versucht die Abfrage-Anweisungen von SAS in Datenbank-spezifische Syntax zu übersetzen
• Bei erfolgreicher Übersetzung wird die Abfrage direktin der Datenbank ausgeführt
• Es kann auch nur ein Teil an das DBMS übergeben werden
Begriffserklärung
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Impliziter Pass-Through
• Folgende Aufgaben können mit implizitem Pass-Through umgesetzt werden:
• Verdichtungsfunktionen (SUM, COUNT, …)
• Filtern von Ursprungs- oder verdichteten Daten mitStandardoperatoren
• Sortieren der Zeilen
• Verknüpfen mit anderen DBMS-Tabellen (Join)
• Berechnen von Spalten mit DBMS-kompatiblen Funktionen
• Verwenden der Option “Nur eindeutige Zeilen auswählen” (DISTINCT)
Was geht beim Impliziten Pass-Through?
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Kein Impliziter Pass-Through, wenn…
• In diesen Situationen kann KEIN impliziter Pass-Through verwendet werden:
• Verknüpfen von DBMS-Tabellen mit Tabellen ausserhalbder DBMS
• Verwenden von Funktionen, die nicht in DBMS-Syntax übersetzt werden können
• Gruppieren und Verdichten von Daten mit gleichzeitigerVerwendung von Detaildaten(Weniger Spalten in GROUP BY als in SELECT)
Wann wird kein Impliziter Pass-Through verwendet?
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Schritte beim Impliziten Pass-Through1. Im SAS Enterprise Guide wird eine Abfrage auf eine
Tabelle in einer DBMS-Bibliothek erstellt.
2. Generierter SQL-Code wird vom EG an SAS geschickt,dort übersetzt und an die Datenbank weitergereicht.
3. DBMS verarbeitet mit dem übersetztenSQL-Programm die Daten.
4. Abfrageergebnis wird vom DBMS zurückgegebenund ist ggf. als SAS Datei verfügbar.
Impliziter Pass-Through:
SAS ist vor der Datenverarbeitung involviert
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Libname-AnweisungVerbindungsmöglichkeit zur Datenbank
LIBNAME librefDBMS-EnginenameVerbindungs-InformationenAuthentifizierungs-Informationen
<SCHEMA=><Weitere Optionen,allgemein oder DBMS spezifisch>
;
LIBNAME mydbmsORACLEPATH=XEUSER="educ"PASSWORD="educ“
;
Beispiel:
Syntax:
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Übersetzung beim impliziten Pass-Through (IP)
• Diese Abfrage kann übersetzt und im DBMS ausgeführt werden:
17
proc sql;create table work.kunden_alter_desc asselectt1.customer_country, t1.customer_gender, count(*) as nKunden, avg(t1.customer_age) as avg_of_customer_age, min(t1.customer_age) as min_of_customer_age, max(t1.customer_age) as max_of_customer_age
from my.kunden t1where month(t1.customer_birthdate) <= 6group by t1.customer_country, t1.customer_gender;
quit;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Optionen: SQL_IP_TRACE und DBIDIRECTEXEC• Impliziter Pass-Through (IP) ist bei fast alle SAS/ACCESS
Schnittstellen standardmäßig deaktiviert(Ausnahme: Amazon Redshift).
• IP dennoch mit SAS/ACCESS nutzen durch:
1. Option DBIDIRECTEXEC setzen
2. IP Ausführungshinweise (Fehlerprotokollierung) im Log ausgeben lassen mit Option:sql_ip_trace=(note source)
Impliziten Pass-Through
einschalten
note = Hinweis source = Quelltext
options sql_ip_trace=note msglevel=idbidirectexec;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Log-Ausgaben Optionen: SQL_IP_TRACE und DBIDIRECTEXEC
• Sind die o.g. Optionen gesetzt, dann könnte folgende Meldung im Log erscheinen:
• SQL_IP_TRACE: The CREATE statement was passed to the DBMS.
• Ohne Option DBIDIRECTEXEC wird folgendeMeldung erzeugt:
• SQL_IP_TRACE: None of the SQL was directly passed to the DBMS.
Setzen der Optionen oder nicht:
So sehen die Logmeldungen aus
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Einführung in SAS und Datenbankverarbeitung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice und wer‘s noch genauer wissen will
SAS und Datenbanken – ein gutes Team
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Expliziter Pass-Through• Beim expliziten Pass-Through werden (im Gegensatz
zum Impliziten Pass-Through):
• Code und Verbindungsinformationen ohne Überprüfungdurch SAS an das DBMS durchgereicht.
Begriffserklärung
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Expliziter Pass-Through• Gründe für die Verwendung des expliziten Pass-Through:
• Komplette Kontrolle über die SQL Anweisung
• Einbinden von DBMS-spezifischen Funktionen/ Optionenmöglich und erforderlich.
• Ergebnisdateien sollen auch in der Datenbank liegen
• Große Dateien in der Datenbank liefern kleine Ergebnisdatei
• Vermeiden des Transfers großer Datenmengen zu SAS vor der Verarbeitung
• Auch andere Anweisungen als SELECT möglich, z.B. CREATE usw.
Expliziter Pass-Through:
SAS ist vor der Datenverarbeitung nicht involviert!
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Informationen über die DB-Verarbeitung
• Mit folgenden Systemoptionen wird im Log dargestellt, was und wo verarbeitet wurde:
•
•
• Abfrage erstellen Ausführen Ein Blick in das LOG bringt Klarheit!
Gibt an, dass alle SQL Anweisungen, die zur DB gesendet wurden, im Log erscheinen;SASTRACE Output ist DB spezifisch.
Wohin sollen Meldungen geschrieben werden? Hier ins SASLOG.
Log Ausgabe ist einfacher zu verstehen, wenn diese Option gesetzt ist. Sonst ist das Log sehr kryptisch und wenig hilfreich.
Options sastrace=‘,,,db’
sastraceloc=saslog
nostsuffix;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Logausgabe mit und ohne NOSTSUFFIX
Ohne Option NOSTSUFFIX
Mit Option NOSTSUFFIX : Besser lesbar!
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Zwei hilfreiche Makrovariablen zur Verarbeitung auf der Datenbank:
• SQLXRC : DBMS spezifischer Returncode, der durch die
Pass-Through Facility zurückgeliefert wird
• SQLXMSG: Fehlermeldung zum DBMS spezifischen Returncode
War die Verarbeitung auf der Datenbank erfolgreich?
%put &=sqlxrc &=sqlxmsg;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Beim expliziten Pass-Through immer die
Datenbank Funktionen verwenden!
Expliziter Pass-ThroughIn der Datenbank nicht bekannte Funktionen liefern eineFehlermeldung:
Wichtig:
SAS Funktion
DB-Funktion, hier: Oracle
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Beispiele für DB-Funktion (hier: Oracle)• Namensgleichheiten bzw. Unterschiede zwischen SAS
Funktionen und Funktionen auf der DB sind in der SAS Hilfe zur jeweiligen DB dokumentiert.
• Einige Beispiel-Funktionen,die bei SAS und Oracle unterschiedlich heißen:
Vorsicht bei Datenbank-Funktionen mit anderem Funktionsnamen als bei SAS:
SAS Hilfe für die jeweilige DB beachten!
SAS ORACLE
DATETIME SYSDATE
LOWCASE LCASE
STRIP TRIM
TRIM TRMIN
UPCASE UPPER
Mean AVG
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Beispiele - Proc SQL mit explizitem Pass-Through
proc sql;
connect to DBMS (path=xe );create table dbms_pt as
select * from connection to DBMS(select customer_countr
, customer_name, customer_age, avg(customer_age)
over(partition bycustomer_country )as avg_age
from kundenwhere customer_id between 4100
and 4200order by customer_country,
customer_age);
%put NOTE: &=sqlxrc &=sqlxmsg;quit;
proc sql;
connect to DBMS (path=xe);execute (create view kunden_de asselect*
from kundenwherecustomer_country = 'DE'
) by DBMS;
%put NOTE: &=sqlxrc;%put NOTE: &=sqlxmsg;
quit;
EXECUTE- AnweisungSELECT- Anweisung
Copyright © SAS Inst itute Inc. A l l r ights reserved.
EXECUTE-Anweisung• EXECUTE sendet eine DB-spezifische SQL-Anweisung
an die DB via SAS/ACCESS-Schnittstelle
• Nur bei explizitem Pass-Through
• Syntax:
• Returncode und Meldungen im Log, die von der DB erzeugt wurden, können mit den Makrovariablen SQLXRC und SQLXMSG abgefragt werden.
Lizenzen: SAS/Access to<datenbanken>
Beispiele:SAS/ACCESS to Oracle,
SAS/ACCESS to DB2
SAS/ACCESS to….
Begriffserklärung
EXECUTE (DBMS-SQL-Anweisung) BY DBMS-Name | Alias;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Einführung in SAS und Datenbankverarbeitung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice und wer‘s noch genauer wissen will
SAS und Datenbanken – ein gutes Team
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Best Practice • Textkonstanten immer in einfache Hochkomma: 'xyz'
• Datumskonstanten beim expliziten Pass-Through immer in der Form: DATE '2015-11-03'
• Textkonstanten in Makrovariablen angeben als:%let macvar=‘Konstanter Text’; oder %TSLIT(&mVar)Das Autocall-Makro %TSLIT fügt einfache Hochkomma hinzu.
• Spaltennamen mit Sonderzeichen immer in doppelteAnführungszeichen: "XYZ“
• Keine SAS Formate beim Pass-Through verwenden!
• Keine reservierten Wörter als Spaltenname/ Tabellennameverwenden. (Es gibt sehr viele!)
Das sollten Sie beim Pass-Through beachten
*Hinweis: Autocall Makros sind in jeder SAS Umgebung vorhanden
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Best Practice: IN-Database-Verarbeitung bei SAS Prozeduren
• Folgende Prozeduren können einen Teil der Verarbeitung im DBMS ausführen:
• FREQ
• RANK
• REPORT
• SORT
• SUMMARY/MEANS
• TABULATE
In der SAS Hilfe erfahren Sie mehr über Prozeduren, die In-Database-Verarbeitung ermöglichen.
Empfehlung: Die Aggregation von
DBMS-Daten mit Proc SQL durchführen,
da bessere Kontrolle.
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Libname-Option: BULKLOAD
• Lädt Datenzeilen blockweise in eine Datenbank.
• BULKLOAD=YES ist der schnellste Weg, Daten in eineDB-Datei einzufügen
• Syntax: BULKLOAD=YES | NO
• YES: Ruft die DB-spezifische Bulkload-Facility auf,um Zeilen blockweise in eine Tabelleeinzufügen oder anzuhängen
• NO: Nutzt die dynamische SAS/ACCESS Engine, um Zeilen einzeln in eine DB-Tabelleeinzufügen oder anzuhängen.
Datenblöcke laden statt Einzelzeilen einfügen Performance Gewinn!
Syntaxbeispiel:
Libname test oracleUser=hugo Password=Bulkload=yes;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Libname-Option: READBUFF=• Anzahl Zeilen von DB-Daten (Integerwert), die in
einen Buffer (Zwischenspeicher) pro Lesevorgangeingelesen werden, d.h. Anzahl Datenzeilen, die im Arbeitsspeichergehalten werden für den Input nach SAS.
• Standardgröße unterschiedlich je DB (SAS Hilfe!)
• Nachteil: Mehr Arbeitsspeicher erforderlich
• Vorteil: Performance Gewinn, da weniger I/Onötig und Zugriff auf Arbeitsspeicherschnell ist
Begriffserklärung
Syntaxbeispiel:
*I/0=Input/Output
Libname testoracle user=hugopassword=…Path=….. Readbuff=1000
;
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Interessante Links und Dokumentationen zum Thema
An Insider’s Guide to Fine-Tuning Your CREATE TABLE Statements Using SAS® Options https://support.sas.com/resources/papers/proceedings17/SAS0409-2017.pdf
oder
Erster Einstieg in das Thema Pass-Through: https://www.lexjansen.com/nesug/nesug11/ps/ps04.pdf
https://support.sas.com/resources/papers/proceedings11/105-2011.pdf
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Fragen?
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Weitere Informationen und Kurse zu diesem Thema…
• Zugriff auf Datenbanken und Tabellen mit der SAS/ACCESS® Softwareals LiveWebClass oder als Firmentraining –kontaktieren Sie uns!
• Praxistage für Base SAS ProgrammiererAuszüge daraus oder das Komplettprogramm als Firmentraining –kontaktieren Sie uns!
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Vielen Dank für Ihre Teilnahme!
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Nächstes Webinar@Lunchtime:
SAS Visual Analytics – What‘s New in 8.3
06. Dezember 2018
12:30 – 13:00
sas.com
Copyright © SAS Inst itute Inc. A l l r ights reserved.
Folien zum Download unter www.sas.de/lunchtime