Webinar@Lunchtime - SAS · 2018. 10. 26. · Copyright © SAS Institute Inc. All rights reserved....

transcript

Webinar@LunchtimeSAS und Datenbanken – ein gutes Paar

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

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

Einführung: SAS und Datenbanken• Sie möchten mit Daten, die in einer Datenbank gehalten

werden, effizient arbeiten, z. B. Oracle oder DB2.

Verarbeitungsoptionen

• Verarbeitung in SAS

• Daten werden über das Netzwerk in SAS geschoben

• Code wird in SAS verarbeitet

• Ergebnis ist in SAS

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

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

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

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

• 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

• 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?

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?

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

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:

Übersetzung beim impliziten Pass-Through (IP)

• Diese Abfrage kann übersetzt und im DBMS ausgeführt werden:

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;

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;

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

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

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!

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;

Logausgabe mit und ohne NOSTSUFFIX

Ohne Option NOSTSUFFIX

Mit Option NOSTSUFFIX : Besser lesbar!

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;

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

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

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;

EXECUTE- AnweisungSELECT- Anweisung

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;

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

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.

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;

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

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

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

Fragen?

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!

Vielen Dank für Ihre Teilnahme!

Nächstes Webinar@Lunchtime:

SAS Visual Analytics – What‘s New in 8.3

06. Dezember 2018

12:30 – 13:00

sas.com

Folien zum Download unter www.sas.de/lunchtime

Webinar@Lunchtime - SAS · 2018. 10. 26. · Copyright © SAS Institute Inc. All rights reserved....

Documents