Zugriff auf relationale Datenbanken – gewusst wie!

WEBINAR@LUNCHTIME
THEMA: ZUGRIFF AUF RELATIONALE DATENBANKEN – GEWUSST WIE!
BRUNO MÜLLER
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME HERZLICH WILLKOMMEN BEI WEBINAR@LUNCHTIME
Moderation
Training
Anne K. Bogner-Hamleh
Bruno Müller
SAS Institute GmbH
Education Consultant
SAS Institute AG
Education Manager
Xing-Profil:
Xing-Profil:
http://www.xing.com/profile/AnneKatrin_BognerHamleh?key=0.0
https://www.xing.com/profile/Bruno_Mueller5
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
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON AGENDA
SAS/ACCESS TO …
Einführung
Impliziter Pass-Through
Expliziter Pass-Through
Best Practice
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON WAS BEKOMMEN SIE MIT SAS/ACCESS TO …
SAS/ACCESS TO …
■ LIBNAME Engine
Sorglospaket, Sie müssen sich um fast nichts kümmern.
■ Proc SQL Pass Through
Direktes ausführen von DBMS spezifischem SQL.
■ Bulk Load Möglichkeit
Verwenden von DBMS Utilities für das laden von Daten.
■ In-Database Verarbeitung
Einige SAS Prozeduren und andere Funktionalitäten können einen Teil der
Verarbeitung an das DBMS auslagern. Es werden nicht alle DBMS gleich unterstützt.
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON WAS MACHT SAS/ACCESS TO … FÜR SIE
SAS/ACCESS TO …
■ Automatische Daten Typen Konvertierung SAS ↔ DBMS
Beispiel: SAS Datum in DBMS Datum
■ Wenn möglich, Datenselektion etc., direkt im DBMS
Beispiel: WHERE, BY Anweisungen in entsprechendes SQL übersetzten
■ Übersetzen von DBMS Namen in SAS Namen (falls nötig)
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON
WAS BRAUCHT ES
SAS/ACCESS TO …
SAS Server
SAS Prozess
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
DBMS Server
DBMS Client
VERWENDEN VON
LIBNAME ANWEISUNG
SAS/ACCESS TO …
LIBNAME libref
DBMS-Enginename
Verbindnungs Informationen
Authentifizierungs Informationen
<SCHEMA=>
<Weitere Optionen,
allgemein oder DBMS spezifisch>
;
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
LIBNAME mydbms
ORACLE
PATH=XE
USER="educ" PASSWORD="educ"
;
VERWENDEN VON BEISPIEL ANWENDUNG
SAS/ACCESS TO …
Verarbeitung in DBMS (Empfehlenswert!)
■ SQL Code wird an das DBMS übergeben
■ DBMS macht die ganze Verarbeitung
■ Ergebnis wird in SAS Datei gespeichert
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON
WELCHE TABELLEN GIBT ES?
SAS/ACCESS TO …
Die Liste der Tabellen kann mit SAS Proc's etc angezeigt werden. Beispiel dictionary.tables:
proc sql;
create table myDBMS_tables as
select
*
from
dictionary.tables
where
libname = "MYDB"
order by
memname
;
quit;
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
Enterprise Guide:
Bibliothek muss zugewiesen sein, das
heisst sie hat ein gelbes Icon
VERWENDEN VON INFORMATIONEN ÜBER DIE VERABEITUNG
SAS/ACCESS TO …
options
sastrace=',,,d'
sastraceloc=saslog
nostsuffix
;
Mit Hilfe dieser SAS System Optionen können wir sehen welche SQL
Anweisung an das DBMS übergeben wird.
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME
DEMO
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON PASS-THROUGH DEFINITION
SAS/ACCESS TO …
Pass-Through-Verarbeitung
■ effizientester Weg der Datenabfrage auf die DBMS-Daten
■ “Pass-Through”  Durchreichen des Codes an die Datenbank zur Verarbeitung
Es gibt zwei verschiedene Arten von Pass-Through:
■ Impliziter Pass-Through, alles automatisch
■ Expliziter Pass-Through, selber im code definiert
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON IMPLIZITER PASS-THROUGH
SAS/ACCESS TO …
Der SAS Code wird soweit als möglich in DBMS SQL Dialekt übersetzt.
Impliziter Pass-Through
■ ist eine Optimierungstechnik in PROC SQL, gewissen Prozeduren und dem DATA
Step
■ Übersetzt die Abfrageinstruktionen von SAS in DBMS-spezifische Syntax, soweit
möglich
■ Wird bei erfolgreicher Übersetzung die Abfrage direkt in DBMS durchführen.
■ Es kann auch nur ein Teil an das DBMS übergeben werden
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEISPIEL PROC SQL (ABFRAGE FENSTER)
SAS/ACCESS TO …
Folgende Aufgaben können über einen impliziten Pass-Through gelöst werden:
In Proc SQL
■ Einbinden einer Verdichtungsfunktion (SUM, COUNT, …)
■ Filtern von Ursprungs- oder verdichteten Spalten mit Standardoperatoren
■ Sortieren der Zeilen
■ Verknüpfen mit anderen DBMS-Tabellen
■ Erstellen von berechneten Spalten mit DBMS-kompatiblen Funktionen
■ Verwenden der Option Nur eindeutige Zeilen auswählen (DISTINCT)
Im DATA Step
■ Filtern von Spalten mit Standardoperatoren (WHERE)
■ Sortieren der Zeilen (BY)
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEISPIEL PROC SQL (ABFRAGE FENSTER)
SAS/ACCESS TO …
In diesen Situationen wird KEIN impliziter Pass-Through verwendet:
■ Verknüpfen von DBMS-Tabellen mit anderen Tabellen ausserhalb der DBMS (zum
Teil möglich, MULTI_DATASRC_OPT= LIBNAME Option)
■ Verwenden von Funktionen die nicht in DBMS-Syntax übersetzt werden können
■ Gruppieren und Verdichten von Daten mit gleichzeitiger Verwendung der Detaildaten
(weniger Spalten in GROUP BY als in SELECT)
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME
DEMO
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON EXPLIZITER PASS-THROUGH
SAS/ACCESS TO …
Gründe für die Verwendung des expliziten Pass-Through:
■ Komplette Kontrolle über die SQL Anweisung
■ Einbinden von DBMS-spezifischen Funktionen oder Optionen.
■ Verwendung von Subselects
■ Auch andere Anweisungen wie SELECT möglich, z.Bsp. CREATE, GRANT usw..
■ Vermeiden des Datentransfers zu SAS vor der Verarbeitung
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON EXPLIZITER PASS-THROUGH
SAS/ACCESS TO …
Selbst geschriebene Programme bieten folgende Vorteile:
■ komplette Kontrolle über den zu DBMS gesendeten Code
■ DBMS-spezifische Funktionen und Optionen können verwendet werden
Beim expliziten Pass-Through wird der SQL Code direct an das DBMS
weitergegeben. Die DBMS Fehlermeldungen werden im SAS Log angezeigt
Die Makro Variablen SQLXRC und SQLXMSG enthalten Returncode und
Fehlermeldung.
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON
EXPLIZITER PASS-THROUGH, ENTERPRISE GUIDE
SAS/ACCESS TO …
Enterprise Guide:
Im Fenster Abfrageoptionen kann
ein expliziter Pass-Through
gefordert werden. Dann schickt
SAS den Code ohne Überprüfung
direkt an DBMS.
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON EXPLIZITER PASS-THROUGH, SELECT
SAS/ACCESS TO …
proc sql;
Folgende Anweisungen werden verwendet:
■ CONNECT TO DBMS ( );
■ SELECT * FROM CONNECTION TO DBMS (
DBMS select
);
■ DISCONNECT FROM DBMS;
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
connect to DBMS (
path=xe
);
create table dbms_pt as
select * from connection to DBMS (
select
customer_country
, customer_name
, customer_age
, avg(customer_age)
over(partition by customer_country )
as avg_age
from kunden
where
customer_id between 4100 and 4200
order by
customer_country
, customer_age desc
);
%put NOTE: &=sqlxrc;
%put NOTE: &=sqlxmsg;
quit;
VERWENDEN VON EXPLIZITER PASS-THROUGH, NICHT SELECT
SAS/ACCESS TO …
proc sql;
connect to DBMS (
path=xe
);
Folgende Anweisungen werden verwendet:
■ CONNECT TO DBMS ( );
■ EXECUTE (
DBMS statement
) BY DBMS;
■ DISCONNECT FROM DBMS;
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
execute (
create view kunden_de as
select
*
from
kunden
where
customer_country = 'DE'
) by DBMS;
%put NOTE: &=sqlxrc;
%put NOTE: &=sqlxmsg;
quit;
WEBINAR@LUNCHTIME
DEMO
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEST PRACTICE
SAS/ACCESS TO …
Was geht zur Datenbank
■ Option SASTRACE setzen
■ Proc SQL anstatt bestimmte SAS Prozedur verwenden
Enterprise Guide: Extras  Optionen
■ Daten  Performance  Zeilenanzahl für Anzeige begrenzen  UNBEDINGT
SETZEN!
■ Daten beim Hinzufügen zum Projekt automatisch öffnen  nur mit Bedacht und
Einschränkung der Zeilen
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEST PRACTICE
SAS/ACCESS TO …
Expliziter Pass-Through
■ Textkonstanten immer in einfache Hochkommata 'xyz'
■ Angabe von Datumskonstanten beim expliziten Pass-Through, DATE '2015-11-03'
■ Umgang mit Textkonstanten in Makrovariablen
%TSLIT(&mVar), %TSLIT wird einfache Hochkomma hinzufügen
■ Bezeichner (Spaltennamen) mit Spezialzeichen immer in doppelte
Anführungszeichen "XYZ“
■ Keine SAS Formate beim Pass-Through verwenden!
■ Keine reservierten Wörter als Spaltennamen, Tabellennamen verwenden (es gibt
sehr viele)
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEST PRACTICE
SAS/ACCESS TO …
Die LIBNAME Anweisung hat sehr viele Optionen:
■ BL_... beinflussen Bulk Load Utility
■ PRESERVE_TAB_NAMES, PRESERVE_COL_NAMES
behalten von Spezialzeichen und Gross/Kleinschreibung in Namen, wollen sie das?
■ DBMAX_TEXT, Maximale Länge von Char Variablen (default 1024)
■ CONNECTION, wieviele DBMS Verbindungen soll SAS erstellen.
■ DBCONINIT, setzen von DBMS spezifischen Einstellungen, z.Bsp. Sortierreihenfolge
■ DBMSTEMP, Global Temporary Table Support
■…
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON BEST PRACTICE
SAS/ACCESS TO …
Folgende Prozeduren können einen Teil der Verarbeitung direct im DBMS
ausführen.
■ FREQ
■ RANK
■ REPORT
■ SORT
■ SUMMARY/MEANS
■ TABULATE
In der Regel sollte jedoch die Aggregation von DBMS Daten mit Proc SQL
gemacht werden (bessere Kontrolle).
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
VERWENDEN VON MAL ANSCHAUEN
SAS/ACCESS TO …
Proc FEDSQL und Proc DS2 unterstützen Standard ANSI Datentypen wie
VARCHAR, BIGINT, DECIMAL usw.
Das heisst sie können innerhalb dieser Prozeduren diese Datentypen
verwenden und auch in eine DBMS Tabelle schreiben.
Diese Datentypen können nicht in eine SAS Datei geschrieben werden.
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME
DEMO
Optimize Data Access within SAS Enterprise Guide
https://www.youtube.com/watch?v=OSTa1EUpKT8
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
FRAGEN?
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME VIELEN DANK FÜR IHRE TEILNAHME
Interesse an weiterem Austausch?
Diskutieren Sie mit uns in der XING-Gruppe
Business Analytics mit SAS
Sprechen Sie uns direkt an:
[email protected]
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
WEBINAR@LUNCHTIME WEITERE INFORMATIONEN UND KURSE ZU DIESEM THEMA…
Kompaktkurs: Base SAS® Programmierung für erfahrene Programmierer
04.07. - 08.07.16 Heidelberg
01.08. - 05.08.16 Frankfurt
05.09. - 09.09.16 Heidelberg
Zugriff auf Datenbanken und Tabellen mit der SAS/ACCESS® Software
Auf Anfrage
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
NÄCHSTES WEBINAR@LUNCHTIME:
07. JULI 2016
SAS ENTERPRISE GUIDE AUSFÜHREN
MIT VIEL WENN UND ABER
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
FOLIEN ZUM DOWNLOAD UNTER
WWW.SAS.DE/LUNCHTIME
WIE HAT IHNEN UNSER WEBINAR GEFALLEN?
C op yr i g h t © 2 0 1 2 , S A S I n s t i t u t e I n c . A l l r i g h t s r es er v e d .
www.SAS.com