T-SQL Deep Dive: Except & Intersect 17.04.2014

Markus Schwamberger
Markus Schwamberger, Senior eXpert

Dies ist vierte Teil einer Serie in der ich nützliche T-SQL Befehle vorstellen möchte, welche wenig bekannt sind oder nur selten eingesetzt werden.

Eine Übersicht über alle Artikel ist hier zu finden.

Als Entwickler steht man häufig vor der Aufgabe mehrere Tabellen oder Abfragen miteinander zu vergleich um zu ermitteln welche Daten identisch sind und welche nicht. Im SQL-Server werden hierzu meist so genannte "Set Operatoren" verwendet. Am bekanntesten ist sicherlich der UNION Operator. Weniger bekannt sind EXCEPT und INTERSECT die ich in dem heutigen Artikel vorstellen möchte.

Beide Operatoren wurden bereits mit SQL Server 2005 eingeführt. Für die Verwendung dieser Operatoren gelten die gleichen Bedingungen wie für den UNION Operator:

  1. Die Anzahl und die Reihefolge der Felder in beiden Abfragen muss identisch sein.
  2. Die Datentypen der Felder müssen miteinander kompatible sein.

EXCEPT gibt alle Werte aus der ersten Abfrage zurück, welche nicht in der zweiten enthalten sind.

INTERSECT gibt alle Werte aus zwei Abfragen zurück, die sowohl in der ersten als auch in der zweiten Abfrage enthalten sind.

Eine Grafik sagt hier mehr als tausend Worte:

Except & Intersect

Für die Beispiele erstellen wir zwei Tabellenvariablen mit Zahlen. Die erste Tabelle enthält alle ungeraden natürlichen Zahlen von eins bis neun, die zweite alle Primzahlen in diesem Bereich.

DECLARE @UngeradeZahlen AS TABLE (
Zahl int
)
DECLARE @Primzahlen AS TABLE (
Zahl int
)

INSERT INTO @UngeradeZahlen VALUES (1),(3),(5),(7),(9)

INSERT INTO @Primzahlen VALUES (2),(3),(5),(7)

UNION liefert eine sortierte und um Duplicate bereinigte Liste aller ungeraden und aller Primzahlen:

SELECT * FROM @UngeradeZahlen
UNION
SELECT * FROM @Primzahlen



Zahl
-----------
1
2
3
5
7
9

Mit EXECEPT können wir alle ungeraden Zahlen anzeigen lassen, welche keine Primzahl sind:

SELECT * FROM @UngeradeZahlen
EXCEPT
SELECT * FROM @Primzahlen



Zahl
-----------
1
9

Wenn wir die Reihenfolge umkehren bekommen wir alle geraden Primzahlen:

SELECT * FROM @Primzahlen
EXCEPT
SELECT * FROM @UngeradeZahlen

Zahl
-----------
2

INTERSECT liefert eine Liste aller ungeraden Primzahlen, dabei ist die Reihenfolge der Abfragen unerheblich:

SELECT * FROM @Primzahlen
INTERSECT
SELECT * FROM @UngeradeZahlen

Bzw.

SELECT * FROM @UngeradeZahlen
INTERSECT
SELECT * FROM @Primzahlen

Zahl
-----------
3
5
7


Fazit

EXCEPT und INTERSECT sind ungemein hilfreiche Befehle zur Datenanalyse. Mit ihnen ist es möglich mit einer gut lesbaren Abfrage die Unterschiede oder Gemeinsamkeiten zwischen zwei Sets zu ermitteln.

Share |

Office Day im April 15.04.2014

Svenja Henß
Svenja Henß, Senior Assistant

Letzte Woche Freitag war es wieder mal soweit: Der monatliche Office Day stand auf dem Programm.

Wie immer startete der Tag mit einem Update zu den Bereichen Marketing und Vertrieb. Danach stellen uns Anette und Philip das neue Intranet vor.

Auch dieses Mal diskutieren die eXperts in interessanten Breakout Sessions:

- Breakout Session: Stakeholder
- Breakout Session: Clean Code
- Breakout Session: Prozessmodellierung

Außerdem freuten sich die eXperts über ihre Osterüberraschung und ließen sich die Bärentreff-Pizza schmecken. Smiley

IMG_6364IMG_6365IMG_6374IMG_6379IMG_6380IMG_6388IMG_6395IMG_6400IMG_6401IMG_6403IMG_6404IMG_6408IMG_6412IMG_6417

Share |

Big Data Hands On Part 1: Getting Started 09.04.2014

Philip Loewenstein
Philip Loewenstein, BI eXpert

Dieser Beitrag zeigt Möglichkeiten zum Einstieg in die Big Data Thematik auf. Es werden technische Herangehensweisen gezeigt, um einen ersten Eindruck von den Anwendungssystemen zu gewinnen. Grundlage ist eine Hortonworks Hadoop HDP 2.1 Installation. Diese stellt ein Ökosystem zur Verwaltung von strukturierten und unstrukturierten Daten bereit.

Auf grundlegende technische Begriffe der Hadoop Architektur wie MapReduce, HDFS, Namenode, Datanodes wird in diesem Hands On Beitrag nicht eingegangen.

Intro Installation

Getreu dem Motto „Think big… start small" sollte man sich auf diesem Wege Big Data nähern. Ziel ist ein virtuelles Hortonworks Lab in einer Hyper-V Umgebung.

Zunächst möchte ich allerdings Alternativen zu einer selbstkonfigurierten Hyper-V Umgebung aufzeigen. Für ein hands on  in Big Data Umfeld bietet sich der HDInsight Server an. Dieser steht on-premise als Developer Preview (diese unterstützt z. Zt. nur eine Single-Node Installation) zum Download bereit, als Cloudlösung ist HDInsight auf Windows Azure verfügbar.

Neben dem On-Premise- und Cloudserver steht auch ein HDInsight Emulator zur Verfügung.

Der Cloudbasierte Azure HDInsight Service erfordert zum jetzigen Zeitpunkt tiefgehende PowerShell Kenntnisse. Auch das zur Verfügung stehende Kontingent auf Azure ist bei mehreren Nodes recht schnell erschöpft.

Für einen technischen Deepdive empfehle ich den Einsatz einer Hortonworks Hadoop Installation in einer Hyper-V Umgebung. Hier kann der versierte User auch hinter die Kulissen blicken. Wer den Installationsaufwand scheut, kann über die HortonWorks Website eine Sandbox VM herunterladen.

  • Sandbox VM als Download
    läuft out of the box und liefert mit Hue eine angenehme Web GUI

Für eine vollständige Installation in einer Hyper-V Umgebung kann die Hadoop Data Platform
(HDP 2.1) separat hier heruntergeladen werden. Hortonworks stellt HDP 2.1 für Linux und Windows bereit.

  • Manuelle Installation auf Linux Server via Ambari (Hadoop Installations Wizard)
    • RHEL/CentOS/SLES (64-bit)
  • Vollständig manuelle RPM oder TarBall Installation unter Linux ohne Ambari
    • Ubuntu/RHEL/CentOS/SLES (64-bit)
  • Hadoop Windows Installation
    • Wizard Unterstützung

In diesem Beitrag wird eine Windows Installation verwendet. Nach erfolgreicher Installation unter Windows Server 2012 R2 bietet sich im %HADOOP_ROOT% Folder folgendes Bild:

image

Neben den Teilmodulen des Hadoop Ecosystems finden sich hier diverse Power Shell / Batch Shell Skripte zum Starten und Stoppen der Windows Services. Bei den einzelnen Teilkomponenten handelt es sich um Java Programme.

Auf dem Desktop werden einige Links zu Websites für die Verwaltung der HDP Features angelegt:

image

Über den Link Hadoop YARN Status gelangt man z.B. zur Website des Ressource Locators
(YARN = Yet Another Ressource Locator). Dieser übernimmt nach Einrichten eines Jobs die Ressourcenallokation über die Nodes. Im Folgenden ein Beispiel des Job Trackers für einen Pig Job.

image

Fazit

Wer eine Einstiegsmöglichkeit in die Big Data Welt sucht und einen Eindruck von den verwendeten Technologien und Anwendungen sucht, der ist mit der von HortonWorks bereitgestellten DataPlattform  gut ausgestattet. Der Installtionsaufwand ist überschaubar und erste Ergebnisse lassen sich mit geringem Zeitaufwand generieren.

Wer zuvor in der Welt der SQL Server Management Tools beheimatet war, muss sich allerdings an eine grundlegend andersartige Umgebung gewöhnen - an Konsolenbefehlen führt in der Hadoop Welt kein Weg vorbei. Dank Verwendung von Skripten wie etwa Python in MapReduce Jobs via Pig lassen sich auch ETL Prozesse in Hadoop realisieren.

Im folgenden Beitrag werde ich anhand eines Beispiel-Hives den Umgang mit strukturierten Daten vorstellen.

Informationen zu MapReduce
http://research.google.com/archive/mapreduce.html

Hardware Referenz Architektur:
http://hortonworks.com/wp-content/uploads/2013/10/HP_Reference_Architecture_Jun13.pdf

Share |

Der Experte… 08.04.2014

Alexander Jung
Alexander Jung, Chief eXpert

"Our company has a new strategic initiative to increase market penetration, maximize brand loyalty, and enhance in tangible assets. [..] I understand your company can help us in this matter. […]

Anderson here is our expert in all matters related to …”

 

 

Großartig!

Andererseits…

“So what exacly is stopping us from doing this?

Geometry.

Just ignore it.”

… vielleicht auch – manchmal – ein wenig too close for comfort… ;-)

Share |

Integrationstests vs. Stored Procedures – Teil 2 03.04.2014

Torben Graefe
Torben Graefe, Senior eXpert

Im ersten Teil dieser Artikelserie wurden die Widersprüche zwischen zwei im Allgemeinen als vorteilhaft geltenden Ansätzen in der Softwareentwicklung deutlich gemacht: Der ausschließlichen Verwendung von Stored Procedures für Datenbankzugriffe sowie dem Anspruch, möglichst den gesamten Code einer Business-Anwendung automatisiert zu testen. Im zweiten Teil der Serie geht es um die für die Überwindung dieser Widersprüche notwendigen Voraussetzungen.

Es geht nicht ohne Regeln

In fast allen Projekten im Enterprise-Bereich wird die Datenzugriffsschicht nicht von einem einzelnen Entwickler umgesetzt, sondern von mehreren Entwicklern. Bei langlaufenden Projekten ist es oft sogar so, dass manche der Beteiligten sich nicht einmal kennen lernen, weil sich ihre Projekteinsätze zeitlich nicht überschneiden.

Vor der Automatisierung sollte deshalb immer die strikte Einhaltung einiger grundlegender Regeln kommen. Im Folgenden soll anhand eines ausführlichen Beispiels gezeigt werden, wie zu diesem Zweck verfahren werden kann. Viele Wege führen zum Ziel, aber es ist wichtig, dass man sich für einen entscheidet.

Denkbar wären folgende Namenskonventionen:

  • .Stored Procedures sollten nach dem gleichen Prinzip benannt werden:

    - SP_<Tabellenname>_Insert

    - SP_<Tabellenname>_Update

    - SP_<Tabellenname>_Delete

    - SP_<Tabellenname>_SelectById usw.

  • Die Namen der technischen Schlüssel sollten demselben Prinzip folgen.

    - Denkbar wäre: <TabellenName>Id

  • Gleiches gilt für die Namen der Parameter für die Stored Procedures, z. B.:

    - @CountryCode für die Spalte CountryCode usw.

Die Einhaltung einfacher Regeln dieser Art mag im ersten Moment selbstverständlich erscheinen. In der Praxis sieht es erfahrungsgemäß oft anders aus: Im schlimmsten Fall folgt jeder Entwickler nur seinen eigenen Präferenzen. Durch diese Auswüchse wird die Umsetzung automatisierter Integrationstests unnötiger erschwert. So wäre es zwar prinzipiell möglich, die Parameter einer Stored Procedure aus der Datenbank auszulesen. Diesen Umweg kann man sich durch Einhaltung der o. g. Regeln sparen. Ein weiterer positiver Nebeneffekt einheitlicher Namenskonventionen besteht darin, dass man beim Wiederverwenden vorhandenen Codes genau weiß, an welchen Stellen Änderungen von Nöten sind, weil es immer die gleichen Stellen sind. In diesem Fall ist Copy-and-Paste weitgehend unproblematisch, weil der entstehende Code Gegenstand der automatisierten Tests sein wird.

Entitäten mit Metadaten-Attributen anreichern

Mit der systematischen Benennung der Datenbankelemente ist der erste Schritt zur Automatisierung getan. Ein zweiter, denkbarer Schritt wäre, die Entitäten der Anwendung mit Metadaten für die Informationen anzureichern, die sich nicht ohne Weiteres aus den Namenskonventionen herleiten. Ein gängiger Weg in C# ist, hierfür eigene Attribute zu definieren. Der genaue Inhalt der Attribute richtet sich nach den Anforderungen der jeweiligen Anwendung.

Im Folgenden wird von einem relativ einfachen Beispiel ausgegangen, das nach Belieben erweitert werden kann:

   1: public class TableMappingAttribute : Attribute
   2: {
   3:     public string Name { get; set; }
   4: }
   1: public class ColumnMappingAttribute : Attribute
   2: {
   3:     public string Name { get; set; }
   4:     public bool IsReadonly { get; set; }
   5: }

Mit dem TableMapping-Attribut wird eine Verbindung zwischen der Entität in C# und der passenden Datenbanktabelle hergestellt. Analog dazu werden mit dem ColumnMapping-Attribut die relevanten Properties der Entität mit Spalten verbunden. In beiden Fällen könnte beim Weglassen des Namens davon ausgegangen werden, dass dieser mit dem Klassen- bzw. Property-Namen identisch ist. Mit IsReadonly können Properties gekennzeichnet werden, die nur für Select-Operationen relevant sind, also z. B. mittels eines JOINs aus einer anderen Tabelle erzeugt wurden.

Eine mit den vorgestellten Attributen angereicherte Entität könnte so aussehen:

   1: [TableMapping(Name = "Product")]
   2: public class Product
   3: {
   4:     [ColumnMapping]
   5:     public int ProductId { get; set; }
   6:     
   7:     [ColumnMapping]
   8:     public string Name { get; set; }
   9:     
  10:     [ColumnMapping(Name="InventoryNo")]
  11:     public int InventoryNumber { get; set; }
  12:  
  13:     [ColumnMapping(IsReadonly = true)]
  14:     public decimal PurchasePrice { get; set; }
  15: }

Eine mögliche Alternative zu dem auf Attributen basierenden Ansatz wäre eine Erweiterung der Namenskonventionen. So könnte per Konvention festgelegt sein, dass die Tabellen- und Spaltennamen identisch mit den Klassen- bzw. Property-Namen auf .NET-Seite sind. Für Properties, die nur gelesen, aber nicht geschrieben werden sollen, könnte man ebenfalls eine Namenskonvention einführen (z. B. mit einem Präfix oder Suffix im Spaltennamen).

Für die Veranschaulichung des Prinzips werden an dieser Stelle die vorgestellten Attribute verwendet werden. Durch mittels der Attribute hinterlegten Metadaten hat man alle Informationen für den Aufruf der Stored Procedures für CRUD-Operationen zusammen, nämlich:

  • Die Namen der aufzurufenden Stored Procedures (per Namenskonvention)

  • Den technischen Primärschlüssel (per Namenskonvention)

  • Die Namen aller relevanten Tabellenspalten (per Attribut)

  • Somit auch die Namen aller Parameter (per Attribut und Namenskonvention)

  • Die Möglichkeit, nur für den Lesezuggriff relevante Spalten mit „Readonly“ zu kennzeichnen (per Attribut)

Einbindung der Metadaten-Attribute

Die vorgestellten Attribute können nun in entsprechenden Methoden ausgelesen und berücksichtigt werden:

   1: public string GetTableName(Type type)
   2: {
   3:     var tableMapping = type.GetCustomAttributes(typeof(TableMappingAttribute), true).FirstOrDefault() as TableMappingAttribute;
   4:  
   5:     if (tableMapping == null)
   6:     {
   7:         // Kein passendes Attribut gefunden
   8:         return null;
   9:     }
  10:  
  11:     if (string.IsNullOrEmpty(tableMapping.Name))
  12:     {
  13:         // Default-Tabellennamen setzen (z. B. "Products" wenn der Klassenname "Product" ist)
  14:         return type.Name + "s";
  15:     }
  16:     
  17:     // Schema unberücksichtigt lassen 
  18:     return tableMapping.Name.Split('.').Last();
  19: }
  20:  
  21: public string GetPrimaryKeyName(string tableName)
  22: {
  23:     // Per Konvention festgelegt
  24:     return tableName + "Id";
  25: }

Der Tabellenname wird benötigt, um daraus den Namen der Stored Procedures daraus abzuleiten. Falls die Tabelle und die Stored Procedure z. B. unterschiedliche Schemata haben sollen, kann man das TableMapping-Attribut um ein passendes Property erweitern, das dann in der Methode zu berücksichtigen wäre.

   1: public Dictionary<string, string> GetColumnMappings(Type type, bool includeReadOnlyProperties)
   2: {
   3:     // Nur die relevanten Properties berücksichtigen
   4:     var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
   5:                          .Where(x => x.GetCustomAttributes(typeof(ColumnMappingAttribute), true).Any());
   6:  
   7:     var mappingDict = new Dictionary<string, string>();
   8:  
   9:     foreach (var currentProperty in properties)
  10:     {
  11:         var columnMapping = (ColumnMappingAttribute)currentProperty.GetCustomAttributes(typeof(ColumnMappingAttribute), true).First();
  12:  
  13:         if (string.IsNullOrEmpty(columnMapping.Name))
  14:         {
  15:             // Default-Spaltennamen setzen (entspricht Property-Name)
  16:             mappingDict.Add(currentProperty.Name, currentProperty.Name);
  17:             continue;
  18:         }
  19:  
  20:         // Spaltennamen aus Property übernehmen
  21:         mappingDict.Add(currentProperty.Name, columnMapping.Name);
  22:     }
  23:  
  24:     return mappingDict;
  25: }
  26: public Dictionary<PropertyInfo, string> GetColumnMappings(Type type, bool includeReadonlyProperties)
  27: {
  28:     // Nur die relevanten Properties berücksichtigen
  29:     var properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public)
  30:                          .Where(x => x.GetCustomAttributes(typeof(ColumnMappingAttribute), true).Any());
  31:  
  32:     var mappingDict = new Dictionary<PropertyInfo, string>();
  33:  
  34:     foreach (var currentProperty in properties)
  35:     {
  36:         var columnMapping = (ColumnMappingAttribute)currentProperty.GetCustomAttributes(typeof(ColumnMappingAttribute), true).First();
  37:  
  38:         if (!includeReadonlyProperties && columnMapping.IsReadonly)
  39:         {
  40:             // Readonly-Property überspringen
  41:             continue;
  42:         }
  43:  
  44:         if (string.IsNullOrEmpty(columnMapping.Name))
  45:         {
  46:             // Default-Spaltennamen setzen (entspricht Property-Name)
  47:             mappingDict.Add(currentProperty, currentProperty.Name);
  48:             continue;
  49:         }
  50:  
  51:         // Spaltennamen aus Property übernehmen
  52:         mappingDict.Add(currentProperty, columnMapping.Name);
  53:     }
  54:  
  55:     return mappingDict;
  56: } 

Das Column-Mapping-Dictionary wird später dazu verwendet, um die Inhalte der Entität von der .NET-Welt in die SQL-Server-Welt zu übertragen. Während man vorher womöglich für jede Stored Procedure eine korrespondierende Methode in .NET geschrieben hätte, kommt man nun im Idealfall mit einer Methode pro CRUD-Operation für alle ähnlich aufgebauten Entitäten aus. Dieses Prinzip soll beispielhaft anhand einer bewusst einfach gehaltenen Update-Methode gezeigt werden:

   1: public void Update<T>(T item)
   2: {
   3:     var itemType = item.GetType();
   4:     
   5:     var storedProcName = "SP_" + GetTableName(itemType) + "_Update";
   6:     var columnMappings = GetColumnMappings(itemType, false);
   7:  
   8:     using (SqlConnection connection = new SqlConnection("ConnectionString"))
   9:     {
  10:         using (SqlCommand cmd = new SqlCommand(storedProcName, connection))
  11:         {
  12:             cmd.CommandType = CommandType.StoredProcedure;
  13:  
  14:             foreach (var currentColumnMapping in columnMappings)
  15:             {
  16:                 var property = currentColumnMapping.Key;
  17:                 var columnName = currentColumnMapping.Value;
  18:                 var value = property.GetValue(item, null);
  19:                 
  20:                 if (!SqlDbTypeDict.ContainsKey(property.PropertyType))
  21:                 {
  22:                     throw new InvalidOperationException("Unerwarteter Typ: " + property.PropertyType);
  23:                 }
  24:  
  25:                 cmd.Parameters.Add("@" + columnName, SqlDbTypeDict[property.PropertyType]).Value = value;
  26:             }
  27:  
  28:             connection.Open();
  29:             cmd.ExecuteNonQuery();
  30:         }
  31:     }
  32: }

Das für die Übersetzung von Type zu SqlDbType benötigte Dicitionary sieht so aus:

   1: public static Dictionary<Type, SqlDbType> SqlDbTypeDict = new Dictionary<Type, SqlDbType>
   2: {
   3:     { typeof(string), SqlDbType.VarChar },
   4:     { typeof(DateTime), SqlDbType.DateTime },
   5:     { typeof(int), SqlDbType.Int },
   6:     { typeof(decimal), SqlDbType.Money },
   7:     { typeof(bool), SqlDbType.Bit }
   8:     // usw.
   9: };

Weitere CRUD-Operationen nach dem gleichen Prinzip

Die Methoden für die anderen CRUD-Operationen können vergleichbar einfach umgesetzt werden. Bei der Methode für die SELECT-Operation würde das Mapping in umgekehrter Richtung erfolgen und die mit „Readonly“ gekennzeichneten Spalten miteinbeziehen. Die Methode für die INSERT-Operation würde ggf. die Identität der erzeugten Tabellenzeile zurückgegen. Besonders einfach ist die Methode für die DELETE-Operation, da lediglich der Primary Key benötigt wird, der sich per Namenskonvention aus dem Tabellenname herleiten lässt.

Natürlich werden in der Praxis nicht alle Entitäten so trivial aufgebaut sein wie das Beispiel Product. Bei wesentlich komplexeren Entitäten muss abgewogen werden, ob es sich lohnt, das System in geeigneter Weise zu erweitern (z. B. durch Hinzufügen und Berücksichtigen weiterer Attribute). In manchen Fällen kann eine Sonderlösung aber nach wie vor der bessere Weg sein, vor allem wenn eine Wiederverwendung der Attribute für eine zweite Entität nahezu ausgeschlossen ist.

Im nächsten Teil dieser Artikelserie soll der Bogen zum Thema Integrationstests geschlagen werden. Es gilt zu zeigen, welche Auswirkungen das vorgestellte Prinzip, den Zugriff auf die Stored Procedures mittels Konventionen und Metadaten-Attributen zu vereinheitlichen, auf diesen Bereich hat.

Share |