Amazon Redshift: Der endgültige Leitfaden

Kapitel 4. Strategien zur Datenumwandlung

Diese Arbeit wurde mithilfe von KI übersetzt. Wir freuen uns über dein Feedback und deine Kommentare: [e-mail]

Ein kürzlich von Forbes veröffentlichter Bericht beschreibt wie einige Börsenmakler und Handelsunternehmen in der Lage waren, schneller als ihre Konkurrenten auf Daten zuzugreifen und diese zu analysieren. Dadurch waren sie in der Lage, "Trades zum besten Preis auszuführen, Mikrosekunden vor der Masse. Der Gewinn war zwar nur geringfügig in Bezug auf die Zeit, aber gewaltig in Bezug auf den Wettbewerbsvorteil, der durch die Schnelligkeit der Erkenntnisse erzielt wurde."

Wenn du eine Analyselösung in Betracht ziehst, ist es wichtig, dass du schnell zu Erkenntnissen kommst. Je schneller ein Unternehmen auf eine Veränderung seiner Daten reagieren kann, desto wettbewerbsfähiger wird es sein. In vielen Fällen müssen die Daten umgewandelt werden, um die benötigten Erkenntnisse zu gewinnen. Wie in Kapitel 3, "Einrichten deiner Datenmodelleund Einlesen von Daten", kurz erläutert , kannst du einen ETL-Ansatz verwenden, bei dem die Quelldaten gelesen, die Transformationen in einer externen Anwendung verarbeitet und die Ergebnisse geladen werden, oder du kannst einen ELT-Ansatz verwenden, bei dem die Daten, die du gerade geladen hast, an Ort und Stelle transformiert werden, indem du die Rechenleistung von Amazon Redshift nutzt.

In diesem Kapitel beginnen wir mit dem "Vergleich von ELT- und ETL-Strategien", um dir bei der Entscheidung zu helfen, welche Datenladestrategie du beim Aufbau deines Data Warehouse verwenden solltest. Wir tauchen auch in einige der einzigartigen Funktionen von Redshift ein, die für Analytics-Anwendungsfälle entwickelt wurden und die "In-Database Transformation" ermöglichen, und erfahren, wie du die eingebauten "Zeitplanungs- und Orchestrierungsprogramme" zur Ausführung deiner Pipelines nutzen kannst. Dann werden wir uns ansehen, wie Amazon Redshift die ELT-Strategie noch weiter ausbaut, indem es dir ermöglicht, auf alle deine Daten zuzugreifen, auch wenn sie nicht in Redshift geladen wurden. Zum Schluss gehen wir darauf ein, wann eine "Externe Transformation" sinnvoll sein kann und wie du AWS Glue Studio für die Erstellung deiner ETL-Pipelines verwendest.

ELT- und ETL-Strategien im Vergleich

Unabhängig von der ELT- oder ETL-Strategie kann die gemeinsamen Ziele deiner Datenmanagement-Plattform unterstützen, zu denen in der Regel das Bereinigen, Transformieren und Aggregieren der Daten für das Laden in dein Berichtsdatenmodell gehören. Dies sind alles ressourcenintensive Vorgänge, und der Hauptunterschied zwischen den beiden Strategien besteht darin, wo die Verarbeitung stattfindet: im Rechenzentrum deines ETL-Servers oder im Rechenzentrum deiner Data-Warehouse-Plattform. Bei ETL-Prozessen werden Daten aus verschiedenen Quellen gelesen und mithilfe der Funktionen und Möglichkeiten der ETL-Engine umgewandelt. Im Gegensatz dazu werden bei ELT-Prozessen auch Daten aus verschiedenen Quellen extrahiert, aber zunächst in das Data Warehouse geladen. Der Transformationsschritt wird nach dem Laden der Daten unter Verwendung der bekannten SQL-Semantik durchgeführt. Bei der Wahl zwischen den beiden Verfahren gibt es einige Dinge zu beachten:

Leistung und Skalierbarkeit

ETL-Prozesse sind von den Ressourcen des ETL-Servers/der ETL-Server abhängig und erfordern von den Plattformbetreibern die korrekte Verwaltung und Größe der Umgebung. Rechenplattformen wie Spark können zur Parallelisierung der Datentransformationen verwendet werden und AWS Glue wird als serverlose Option für die Verwaltung von ETL-Pipelines angeboten. Die ELT-Verarbeitung wird mit den Rechenressourcen des Data Warehouse durchgeführt. Im Fall von Amazon Redshift wird die Leistung der MPP-Architektur zur Durchführung der Transformationen genutzt. In der Vergangenheit wurde die externe Transformation der Daten bevorzugt, da die Verarbeitung auf unabhängige Rechenressourcen ausgelagert wurde. Moderne Data-Warehouse-Plattformen, einschließlich Amazon Redshift, skalieren jedoch dynamisch und können gemischte Arbeitslasten unterstützen, was eine ELT-Strategie attraktiver macht. Da Data-Warehouse-Plattformen außerdem darauf ausgelegt sind, riesige Datenmengen mit nativen Datenbankfunktionen zu verarbeiten und umzuwandeln, sind ELT-Aufträge in der Regel leistungsfähiger. Schließlich sind ELT-Strategien frei von Netzwerkengpässen, die bei ETL erforderlich sind, um Daten zur Verarbeitung ein- und auszulagern.

Flexibilität

Während jeder Transformationscode in deiner Datenplattform einem Entwicklungszyklus folgen sollte, wird der Code bei einer ETL-Strategie normalerweise von einem Team mit speziellen Kenntnissen in einer externen Anwendung verwaltet. Im Gegensatz dazu stehen bei einer ELT-Strategie alle Rohdaten zur Abfrage und Umwandlung in der Datenmanagement-Plattform zur Verfügung. Die Analysten können den Code mit vertrauten SQL-Funktionen schreiben und dabei ihre bereits vorhandenen Kenntnisse nutzen. Die Befähigung der Analysten verkürzt den Entwicklungszyklus, da sie den Code prototypisch erstellen und die Geschäftslogik validieren können. Die Eigentümer der Datenplattform sind für die Optimierung und das Zeitplannungsprogramm des Codes verantwortlich.

Metadatenmanagement und Orchestrierung

Eine wichtige Überlegung für deine Datenstrategie ist die Verwaltung der Auftragsmetadaten und die Orchestrierung. Die Nutzung einer ELT-Strategie bedeutet, dass der Eigentümer der Datenplattform den Überblick über die Aufträge, ihre Abhängigkeiten und Ladepläne behalten muss. ETL-Tools verfügen in der Regel über Funktionen zur Erfassung und Organisation von Metadaten zu Quellen, Zielen und Auftragseigenschaften sowie zur Datenabfolge. Sie können auch Aufträge orchestrieren und Abhängigkeiten über mehrere Datenplattformen hinweg aufbauen.

Letztendlich hängt die Wahl zwischen ETL und ELT von den spezifischen Anforderungen des Analyseaufwands ab. Beide Strategien haben Stärken und Schwächen, und die Entscheidung, welche du verwendest, hängt von den Merkmalen der Datenquellen, den Transformationsanforderungen und den Leistungs- und Skalierbarkeitsanforderungen des Projekts ab. Um die Herausforderungen beider Strategien abzumildern, wählen viele Nutzer einen hybriden Ansatz. Du kannst die Vorteile der Metadatenverwaltung und der Orchestrierungsfunktionen von ETL-Tools sowie die Leistung und Skalierbarkeit der ELT-Verarbeitung nutzen, indem du Aufträge erstellst, die den ETL-Code in SQL-Anweisungen übersetzen. In "Externe Transformation" wird genauer erläutert, wie das möglich ist.

Transformation innerhalb der Datenbank

Angesichts der Vielfalt und Geschwindigkeit der Daten, die heute vorhanden sind, besteht die Herausforderung bei der Entwicklung einer Datenplattform darin, sie sowohl skalierbar als auch flexibel zu gestalten. Amazon Redshift entwickelt sich weiter und bietet mit seinen ELT-Funktionen (In-Database Transformation) Funktionen zur Verarbeitung aller Daten an einem Ort. Da Amazon Redshift eine ANSI-SQL-kompatible relationale Datenbank ist, unterstützt es SQL-Befehle und ist damit für die meisten Datenbankentwickler eine vertraute Entwicklungsumgebung. Amazon Redshift unterstützt auch fortschrittliche Funktionen, die in modernen Datenplattformen zu finden sind, wie Window Functions, HyperLogLog Functions und Recursive CTE (Common Table Expressions), um nur einige zu nennen. Zusätzlich zu den Funktionen, die du vielleicht schon kennst, unterstützt Amazon Redshift einzigartige Funktionen für die analytische Verarbeitung. Amazon Redshift unterstützt zum Beispiel die In-Place-Abfrage für "Semistrukturierte Daten" und bietet Analysten damit eine Möglichkeit, auf diese Daten auf performante Weise zuzugreifen, ohne darauf warten zu müssen, dass sie in Tabellen und Spalten geladen werden. Wenn du die Möglichkeiten von Amazon Redshift erweitern möchtest, kannst du außerdem "Benutzerdefinierte Funktionen" nutzen, die innerhalb der Datenbank ausgeführt werden oder externe Dienste aufrufen können. Mit "Stored Procedures" schließlich kannst du deine Transformationslogik verpacken. Sie können einen Ergebnissatz mit Eingabeparametern zurückgeben oder sogar Daten laden und verwaltete Operationen wie das Laden einer Fakten-, Dimensions- oder Aggregatstabelle durchführen.

Semistrukturierte Daten

Semistrukturierte Daten fallen unter die Kategorie von Daten, die sich nicht an ein starres Schema halten, das in relationalen Datenbanken erwartet wird. Semistrukturierte Formate sind in Webprotokollen, Sensordaten oder API-Nachrichten weit verbreitet und werden oft bevorzugt, weil diese Anwendungen oft Daten mit verschachtelten Beziehungen senden müssen und es effizienter ist, die Daten nur einmal zu senden, anstatt sie mehrfach zu übertragen. Semistrukturierte Daten enthalten komplexe Werte wie Arrays und verschachtelte Strukturen, die mit Serialisierungsformaten wie JSON verbunden sind. Es gibt zwar Tools von Drittanbietern, mit denen du deine Daten außerhalb der Datenbank umwandeln kannst, aber die Erstellung und Pflege dieses Codes erfordert technische Ressourcen und ist möglicherweise nicht so leistungsfähig. Unabhängig davon, ob du auf "externe Amazon S3-Daten" oder lokal geladene Daten zugreifst, nutzt Amazon Redshift die PartiQL-Syntax für die Analyse und Umwandlung semistrukturierter Daten. Ein spezieller Datentyp, SUPERwurde eingeführt, um diese Daten in ihrer nativen Form zu speichern. Beim Zugriff von Amazon S3 werden sie jedoch mit einem Datentyp von struct oder array katalogisiert.

Im folgenden Beispiel verweisen wir auf eine Datei, die in einer Amazon S3-Umgebung gelandet ist. Du kannst diese Datei katalogisieren und in Amazon Redshift zugänglich machen, indem du ein externes Schema erstellst und jede Datei, die in diesem Amazon S3-Präfix existiert, dieser Tabellendefinition zuordnest.

Die erste Abfrage(Beispiel 4-1) ermittelt den Gesamtumsatz pro Veranstaltung.

Beispiel 4-1. Externe Tabelle aus JSON-Daten erstellen

CREATE external SCHEMA IF NOT EXISTS nested_json
FROM data catalog DATABASE 'nested_json'
IAM_ROLE default
CREATE EXTERNAL DATABASE IF NOT EXISTS;

DROP TABLE IF EXISTS nested_json.nested_json;
CREATE EXTERNAL TABLE nested_json.nested_json (
    c_name varchar,
    c_address varchar,
    c_nationkey int,
    c_phone varchar,
    c_acctbal float,
    c_mktsegment varchar,
    c_comment varchar,
    orders struct<"order":array<struct<
      o_orderstatus:varchar,
      o_totalprice:float,
      o_orderdate:varchar,
      o_order_priority:varchar,
      o_clerk:varchar,
      o_ship_priority:int,
      o_comment:varchar
      >>> )
row format serde 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties ('paths'='c_name,c_address,c_nationkey,c_phone,
  c_acctbal,c_mktsegment,c_comment,Orders')
stored as inputformat 'org.apache.hadoop.mapred.TextInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location 's3://redshift-immersionday-labs/data/nested-json/';

Diese Datendatei befindet sich in der Region us-west-2, und dieses Beispiel funktioniert nur, wenn sich dein Amazon Redshift Data Warehouse ebenfalls in dieser Region befindet. Außerdem haben wir auf die IAM-Rolle default verwiesen. Achte darauf, dass du die Rolle so anpasst, dass sie Lesezugriff auf diesen Amazon S3-Speicherort sowie Zugriff auf die Verwaltung des AWS Glue Data Catalog hat.

Jetzt, wo die Tabelle verfügbar ist, kann sie abgefragt werden und du kannst auf die Top-Level-Attribute ohne besondere Bearbeitung zugreifen(Beispiel 4-2).

Beispiel 4-2. Top-Level-Attribute

SELECT cust.c_name,
  cust.c_nationkey,
  cust.c_address
FROM nested_json.nested_json cust
WHERE cust.c_nationkey = '-2015'
  AND cust.c_address like '%E12';

Mit der PartiQL-Syntax kannst du auf die verschachtelten struct Daten zugreifen. In Beispiel 4-3 heben wir die Verschachtelung der Daten im Feld orders auf und zeigen die mehreren Bestellungen an, die dem Kundendatensatz zugeordnet sind.

Beispiel 4-3. Nicht verschachtelte Attribute (extern)

SELECT cust.c_name,
   cust_order.o_orderstatus,
   cust_order.o_totalprice,
   cust_order.o_orderdate::date,
   cust_order.o_order_priority,
   cust_order.o_clerk,
   cust_order.o_ship_priority,
   cust_order.o_comment
FROM nested_json.nested_json cust,
     cust.orders.order cust_order
WHERE cust.c_nationkey = '-2015'
  AND cust.c_address like '%E12';

Zusätzlich zum Zugriff auf die Daten in S3, können diese semistrukturierten Daten mit dem Datentyp SUPER in deine Amazon Redshift-Tabelle geladen werden. In Beispiel 4-4 wird dieselbe Datei in eine physische Tabelle geladen. Ein bemerkenswerter Unterschied beim Laden in Amazon Redshift ist, dass keine Informationen über das Schema der orders Spalte benötigt werden, die dem SUPER Datentyp zugeordnet ist. Dies vereinfacht den Lade- und Metadatenverwaltungsprozess und bietet Flexibilität bei Änderungen der Metadaten.

Beispiel 4-4. Lokale Tabelle aus JSON-Daten erstellen

DROP TABLE IF EXISTS nested_json_local;
CREATE TABLE nested_json_local (
    c_name varchar,
    c_address varchar,
    c_nationkey int,
    c_phone varchar,
    c_acctbal float,
    c_mktsegment varchar,
    c_comment varchar,
    orders SUPER);

COPY nested_json_local
from 's3://redshift-immersionday-labs/data/nested-json/'
IAM_ROLE default REGION 'us-west-2'
JSON 'auto ignorecase';

Wir haben auf die IAM-Rolle default verwiesen. Achte darauf, dass du die Rolle so änderst, dass sie Lesezugriff auf diesen Amazon S3-Speicherort gewährt.

Jetzt, wo die Tabelle verfügbar ist, kann sie abgefragt werden. Mit der gleichen PartiQL-Syntax kannst du auf die Bestelldetails zugreifen(Beispiel 4-5).

Beispiel 4-5. Ungeschachtelte Attribute (lokal)

SET enable_case_sensitive_identifier TO true;
SELECT cust.c_name,
   cust_order.o_orderstatus,
   cust_order.o_totalprice,
   cust_order.o_orderdate::date,
   cust_order.o_order_priority,
   cust_order.o_clerk,
   cust_order.o_ship_priority,
   cust_order.o_comment
FROM nested_json_local cust,
     cust.orders."Order" cust_order
WHERE cust.c_nationkey = '-2015'
  AND cust.c_address like '%E12';

enable_case_sensitive_identifier ist ein wichtiger Parameter bei der Abfrage von SUPER Daten, wenn deine Eingabe Bezeichner mit gemischter Groß- und Kleinschreibung enthält. Weitere Informationen findest du in der Online-Dokumentation.

Weitere Details und Beispiele zur Abfrage von semistrukturierten Daten findest du in der Online-Dokumentation .

Benutzerdefinierte Funktionen

Wenn eine eingebaute Funktion für deine spezifischen Transformationsanforderungen nicht zur Verfügung steht, bietet Amazon Redshift einige Optionen zur Erweiterung der Funktionalität der Plattform. Amazon Redshift ermöglicht es dir, skalare benutzerdefinierte Funktionen (UDFs) in drei Varianten zu erstellen: SQL, Python und Lambda. Eine ausführliche Dokumentation zur Erstellung dieser Funktionstypen findest du in der Online-Dokumentation.

Eine skalare Funktion gibt genau einen Wert pro Aufruf zurück. In den meisten Fällen kannst du dir das so vorstellen, dass ein Wert pro Zeile zurückgegeben wird.

Eine SQL-UDF nutzt die bestehende SQL-Syntax. Sie kann verwendet werden, um sicherzustellen, dass eine einheitliche Logik angewendet wird, und um die Menge an Code zu vereinfachen, die jeder Benutzer einzeln schreiben müsste. In Beispiel 4-6 aus dem Amazon Redshift UDFs GitHub Repo siehst du eine SQL-Funktion, die zwei Eingabeparameter benötigt: Das erste Feld varchar sind die Daten, die maskiert werden sollen, und das zweite Feld ist die Klassifizierung der Daten. Das Ergebnis ist eine unterschiedliche Maskierungsstrategie, die auf der Klassifizierung der Daten basiert.

Beispiel 4-6. SQL UDF-Definition

CREATE OR REPLACE function f_mask_varchar (varchar, varchar)
  returns varchar
immutable
AS $$
  SELECT case $2
    WHEN 'ssn' then
      substring($1, 1, 7)||'xxxx'
    WHEN 'email' then
      substring(SPLIT_PART($1, '@', 1), 1, 3) + 'xxxx@' + SPLIT_PART($1, '@', 2)
    ELSE substring($1, 1, 3)||'xxxxx' end
$$ language sql;

Benutzer können eine SQL UDF innerhalb von einer SELECT Anweisung referenzieren. In diesem Szenario könntest du die Anweisung SELECT wie in Beispiel 4-7 dargestellt schreiben.

Beispiel 4-7. SQL UDF-Zugriff

DROP TABLE IF EXISTS customer_sqludf;
CREATE TABLE customer_sqludf (cust_name varchar, email varchar, ssn varchar);
INSERT INTO customer_sqludf VALUES('Jane Doe', '[e-mail]', '123-45-6789');

SELECT
 f_mask_varchar (cust_name, NULL) mask_name, cust_name,
 f_mask_varchar (email, 'email') mask_email, email,
 f_mask_varchar (ssn, 'ssn') mask_ssn, ssn
FROM customer_sqludf;

Die Anweisung SELECT in Beispiel 4-7 ergibt die folgende Ausgabe:

maske_name	Name	maske_email	E-Mail	maske_ssn	ssn
Janxxxxx	Unbekannte	[e-mail]	[e-mail]	123-45-xxxx	123-45-6789

Eine Python UDF ermöglicht es Nutzern, Python-Code zu verwenden, um ihre Daten zu transformieren. Zusätzlich zu den Python-Kernbibliotheken können Nutzer auch ihre eigenen Bibliotheken importieren, um die in Amazon Redshift verfügbaren Funktionen zu erweitern. In Beispiel 4-8 aus dem Amazon Redshift UDFs GitHub Repo siehst du eine Python-Funktion, die eine externe Bibliothek, ua_parser, nutzt, um einen User-Agent-String in ein JSON-Objekt zu parsen und die Betriebssystemfamilie des Clients zurückzugeben. Eine detaillierte Anleitung zur Installation dieses Beispiels findest du im GitHub Repo.

Beispiel 4-8. Python UDF-Definition

CREATE OR REPLACE FUNCTION f_ua_parser_family (ua VARCHAR)
RETURNS VARCHAR IMMUTABLE AS $$
  FROM ua_parser import user_agent_parser
  RETURN user_agent_parser.ParseUserAgent(ua)['family']
$$ LANGUAGE plpythonu;

Ähnlich wie bei den SQL-UDFs können die Benutzer innerhalb einer SELECT -Anweisung auf eine Python-UDF verweisen. In diesem Beispiel könntest du die in Beispiel 4-9 gezeigte SELECT Anweisung schreiben.

Beispiel 4-9. Python UDF Zugriff

SELECT f_ua_parser_family (agent) family, agent FROM weblog;

Die Anweisung SELECT in Beispiel 4-9 ergibt die folgende Ausgabe:

Familie	Agent
Chrom	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_4) AppleWebKit/537.36 (KHTML, wie Gecko) Chrome/41.0.2272.104 Safari/537.36

Schließlich ermöglicht die Lambda UDF den Nutzern die Interaktion und Integration mit externen Komponenten außerhalb von Amazon Redshift. Lambda UDFs können in jeder unterstützten Programmiersprache geschrieben werden, z. B. in Java, Go PowerShell, Node.js, C#, Python, Ruby oder einer benutzerdefinierten Laufzeitumgebung. Diese Funktionalität ermöglicht neue Anwendungsfälle für Amazon Redshift, darunter die Anreicherung von Daten aus externen Datenspeichern (z. B. Amazon DynamoDB, Amazon ElastiCache usw.), die Anreicherung von Daten aus externen APIs (z. B. Melissa Global Address Web API usw.), die Maskierung und Tokenisierung von Daten von externen Anbietern (z. B. Protegrity) und die Konvertierung von Legacy-UDFs, die in anderen Sprachen wie C,C++ und Java geschrieben wurden. In Beispiel 4-10 aus dem Amazon Redshift UDFs GitHub Repo siehst du eine Lambda-Funktion, die den AWS Key Management Service (KMS) nutzt und einen eingehenden String entgegennimmt, um den verschlüsselten Wert zurückzugeben. Der erste Codeblock richtet eine Lambda-Funktion f-kms-encrypt ein, die ein verschachteltes Array von Argumenten erwartet, das an die Funktion übergeben wird. In diesem Beispiel würde der Benutzer kmskeyid und columnValue als Eingabeparameter angeben; argument[0] und argument[1]. Die Funktion verwendet die boto3 Bibliothek, um den kms Dienst aufzurufen und die verschlüsselte response zurückzugeben. Eine ausführliche Anleitung zur Installation dieses Beispiels findest du im GitHub Repo.

Beispiel 4-10. Definition einer Lambda-Funktion

import json, boto3, os, base64
kms = boto3.client('kms')
def handler(event, context):
  ret = dict()
  res = []
  for argument in event['arguments']:
    try:
      kmskeyid = argument[0]
      columnValue = argument[1]
      if (columnValue == None):
          response = None
      else:
          ciphertext = kms.encrypt(KeyId=kmskeyid, Plaintext=columnValue)
          cipherblob = ciphertext["CiphertextBlob"]
          response = base64.b64encode(cipherblob).decode('utf-8')
      res.append(response)
    except Exception as e:
      print (str(e))
      res.append(None)
  ret['success'] = True
  ret['results'] = res
  return json.dumps(ret)

Der nächste Codeblock richtet die Amazon Redshift UDF ein, die auf die Lambda-Funktion verweist(Beispiel 4-11).

Beispiel 4-11. Lambda UDF Definition

CREATE OR REPLACE EXTERNAL FUNCTION f_kms_encrypt (key varchar, value varchar)
RETURNS varchar(max) STABLE
LAMBDA 'f-kms-encrypt'
IAM_ROLE default;

Wir haben auf die IAM-Rolle default verwiesen. Achte darauf, dass du die Rolle so anpasst, dass sie die Ausführung der zuvor erstellten Lambda-Funktion erlaubt.

Genau wie bei den SQL- und Python-UDFs können Benutzer eine Lambda-UDF innerhalb einer SELECT -Anweisung referenzieren. In diesem Szenario könntest du die in Beispiel 4-12 gezeigte SELECT Anweisung schreiben.

Beispiel 4-12. Lambda UDF Zugriff

SELECT f_kms_encrypt (email) email_encrypt, email FROM customer;

Die Anweisung SELECT in Beispiel 4-12 ergibt die folgende Ausgabe:

email_verschlüsseln	E-Mail
AQICAHiQbIJ478Gbu8DZyl0frUxOrbgDlP+CyfuWCuF0kHJyWg ...	[e-mail]

Weitere Einzelheiten zu Python UDFs findest du unter im Blogbeitrag "Introduction to Python UDFs in Amazon Redshift" und zu Lambda UDFs im Blogbeitrag "Accessing External Components Using Amazon Redshift Lambda UDFs".

Gespeicherte Prozeduren

Eine Amazon Redshift Stored Procedure ist ein vom Benutzer erstelltes Objekt, das eine Reihe von SQL-Abfragen und logischen Operationen ausführt. Die Prozedur wird in der Datenbank gespeichert und steht den Benutzern zur Verfügung, die über die entsprechenden Berechtigungen verfügen, sie auszuführen. Im Gegensatz zu einer skalaren UDF-Funktion, die nur eine einzige Datenzeile in einer Tabelle bearbeiten kann, kann eine Stored Procedure neben SELECT Abfragen auch Data Definition Language (DDL) und Data Manipulation Language (DML) enthalten. Außerdem muss eine Stored Procedure keinen Wert zurückgeben und kann Schleifen und bedingte Ausdrücke enthalten.

Gespeicherte Prozeduren werden häufig verwendet, um die Logik für die Datenumwandlung, die Datenvalidierung und geschäftsspezifische Operationen zu kapseln, als Alternative zu Shell-Skripten oder komplexen ETL- und Orchestrierungstools. Mit Stored Procedures können die logischen ETL/ELT-Schritte vollständig in eine Prozedur eingeschlossen werden. Du kannst die Prozedur so schreiben, dass sie Daten inkrementell überträgt oder dass sie entweder vollständig erfolgreich ist (alle Zeilen verarbeitet) oder vollständig fehlschlägt (keine Zeilen verarbeitet). Da die gesamte Verarbeitung im Data Warehouse stattfindet, fällt kein Overhead für die Übertragung der Daten über das Netzwerk an und du kannst die Vorteile der MPP-Architektur von Amazon Redshift nutzen, um große Datenmengen schnell zu verarbeiten.

Da die Stored Procedures in der Programmiersprache PL/pgSQL implementiert sind, musst du keine neue Programmiersprache erlernen, um sie zu nutzen. Es kann sogar sein, dass du bereits Stored Procedures in deiner alten Datenplattform hast, die mit minimalen Codeänderungen zu Amazon Redshift migriert werden können. Die Logik deiner bestehenden Prozesse mit einer externen Programmiersprache oder einer neuen ETL-Plattform neu zu erstellen, könnte ein großes Projekt sein. AWS bietet außerdem das AWS Schema Conversion Tool (SCT), einen Migrationsassistenten, der dir bei der Konvertierung von bestehendem Code in anderen Datenbankprogrammiersprachen in den nativen PL/pgSQL-Code von Amazon Redshift helfen kann.

In Beispiel 4-13 siehst du eine einfache Prozedur, die Daten aus Amazon S3 in eine Staging-Tabelle lädt und neue Datensätze in die Tabelle lineitem lädt, wobei sichergestellt wird, dass Duplikate gelöscht werden. Diese Prozedur macht sich den MERGE Operator zunutze und kann die Aufgabe mit einer einzigen Anweisung bewältigen. In diesem Beispiel gibt es konstante Variablen für l_orderyear und l_ordermonth. Dies kann jedoch leicht dynamisch gemacht werden, indem man die Funktion date_part und die Variable current_date verwendet, um das aktuelle Jahr und den aktuellen Monat zu bestimmen, oder indem man der Prozedur einen Parameter year und month übergibt.

Beispiel 4-13. Definition einer Stored Procedure

CREATE TABLE IF NOT EXISTS lineitem (
  L_ORDERKEY varchar(20) NOT NULL,
  L_PARTKEY varchar(20),
  L_SUPPKEY varchar(20),
  L_LINENUMBER integer NOT NULL,
  L_QUANTITY varchar(20),
  L_EXTENDEDPRICE varchar(20),
  L_DISCOUNT varchar(20),
  L_TAX varchar(20),
  L_RETURNFLAG varchar(1),
  L_LINESTATUS varchar(1),
  L_SHIPDATE date,
  L_COMMITDATE date,
  L_RECEIPTDATE date,
  L_SHIPINSTRUCT varchar(25),
  L_SHIPMODE varchar(10),
  L_COMMENT varchar(44));

CREATE TABLE stage_lineitem (LIKE lineitem);

CREATE OR REPLACE PROCEDURE lineitem_incremental()
AS $$
DECLARE
  yr CONSTANT INTEGER := 1998; --date_part('year',current_date);
  mon CONSTANT INTEGER := 8; --date_part('month', current_date);
  query VARCHAR;
BEGIN
  TRUNCATE stage_lineitem;
  query := 'COPY stage_lineitem ' ||
  	'FROM ''s3://redshift-immersionday-labs/data/lineitem-part/' ||
	  'l_orderyear=' || yr || '/l_ordermonth=' || mon || '/''' ||
    ' IAM_ROLE default REGION ''us-west-2'' gzip delimiter ''|''';
  EXECUTE query;

  MERGE INTO lineitem
  USING stage_lineitem s ON s.l_orderkey=lineitem.l_orderkey
  AND s.l_linenumber = lineitem.l_linenumber
  WHEN MATCHED THEN DELETE
  WHEN NOT MATCHED THEN INSERT
    VALUES ( s.L_ORDERKEY, s.L_PARTKEY, s.L_SUPPKEY, s.L_LINENUMBER,
      s.L_QUANTITY, s.L_EXTENDEDPRICE, s.L_DISCOUNT, s.L_TAX,
      s.L_RETURNFLAG, s.L_LINESTATUS, s.L_SHIPDATE, s.L_COMMITDATE,
      s.L_RECEIPTDATE, s.L_SHIPINSTRUCT, s.L_SHIPMODE, s.L_COMMENT);

END;
$$ LANGUAGE plpgsql;

Wir haben auf die IAM-Rolle default verwiesen. Achte darauf, dass du die Rolle so änderst, dass sie Lesezugriff auf diesen Amazon S3-Speicherort gewährt.

Du kannst eine gespeicherte Prozedur ausführen, indem du das Schlüsselwort call verwendest(Beispiel 4-14).

Beispiel 4-14. Zugriff auf Stored Procedures

CALL lineitem_incremental();

Weitere Details zu Amazon Redshift Stored Procedures findest du unter im Blogbeitrag "Bringing Your Stored Procedures to Amazon Redshift".

Zeitplanungsprogramm und Orchestrierung

Wenn du anfängst, über die Orchestrierung deiner Datenpipeline nachzudenken, solltest du die Komplexität des Workflows und die Abhängigkeiten von externen Prozessen berücksichtigen. Manche Nutzer müssen mehrere Systeme mit komplexen Abhängigkeiten verwalten. Möglicherweise musst du benachrichtigt werden, wenn ein Auftrag fehlschlägt oder ein SLA nicht eingehalten wird. In diesem Fall solltest du ein Zeitplanungsprogramm eines Drittanbieters in Betracht ziehen. Beliebte Zeitplanungsprogramme von Drittanbietern sind Tivoli, Control-M und AutoSys, die alle mit Amazon Redshift integriert sind und es dir ermöglichen, eine Verbindung zu initiieren und eine oder mehrere SQL-Anweisungen auszuführen. AWS bietet auch den Service Amazon Managed Workflow Orchestration for Apache Airflow (MWAA) an, der auf dem Open-Source-Projekt Apache Airflow basiert. Dies kann nützlich sein, wenn du bereits einen Apache Airflow-Workflow betreibst und ihn in die Cloud migrieren möchtest.

Wenn du jedoch deine Ladungen auf der Grundlage von zeitbasierten Triggern auslösen kannst, kannst du das Zeitplannungsprogramm für Abfragen nutzen. Wenn du das Zeitplannungsprogramm verwendest, nutzt die Benutzeroberfläche die grundlegenden Services der Amazon Redshift Data API und EventBridge.

Um mit dem Zeitplannungsprogramm einfache zeitbasierte Abfragen auszulösen, navigierst du zum Abfrage-Editor V2, bereitest deine Abfrage vor und klickst auf die Schaltfläche Planen(Abbildung 4-1). In diesem Beispiel werden wir eine COPY Anweisung verwenden, um die Tabelle stage_lineitem zu laden.

Lege die Verbindung(Abbildung 4-2) sowie die IAM-Rolle fest, die das Zeitplannungsprogramm zur Ausführung der Abfrage annehmen soll. Im folgenden Dialog wählst du das entsprechende Amazon Redshift Data Warehouse aus der Liste aus und wählst das entsprechende Konto und die Region. In unserem Fall verwenden wir "Temporäre Anmeldedaten" für die Verbindung. In Kapitel 2, "Erste Schritte mit Amazon Redshift", findest du weitere Informationen zu anderen Verbindungsstrategien.

Abbildung 4-1. Schaltfläche Zeitplanungsprogramm

Abbildung 4-2. Verbindung wählen

Als Nächstes legst du den Namen der Abfrage, die ausgeführt werden soll, und die optionale Beschreibung fest(Abbildung 4-3). Die Abfrage wird von der Editor-Seite übernommen.

Abbildung 4-3. Abfrage einstellen

Als Nächstes legst du den zeitbasierten Zeitplan entweder im Cron-Format oder durch Auswahl der entsprechenden Radiooptionen fest(Abbildung 4-4). Wähle optional aus, ob du möchtest, dass Ausführungsereignisse an ein Amazon Simple Notification Service (SNS)-Thema weitergeleitet werden, damit du Benachrichtigungen erhalten kannst. Klicke auf Änderungen speichern, um den Zeitplan zu speichern.

Abbildung 4-4. Zeitplan einstellen

Um die Liste der geplanten Abfragen zu sehen, navigierst du zur Seite Geplante Abfragen im Abfrage-Editor V2(Abbildung 4-5).

Abbildung 4-5. Liste geplanter Abfragen

Um den geplanten Auftrag zu verwalten, klicke auf die geplante Abfrage. Auf diesem Bildschirm kannst du den Auftrag ändern, deaktivieren oder löschen. Außerdem kannst du den Verlauf einsehen, der die Start-/Stoppzeit sowie den Auftragsstatus enthält (siehe Abbildung 4-6).

Abbildung 4-6. Siehe Zeitplanungsprogramm-Historie

Du kannst auch die erstellten Ressourcen in EventBridge sehen. Navigiere zur Seite EventBridge-Regeln und stelle fest, dass eine neue Zeitplanregel erstellt wurde(Abbildung 4-7).

Abbildung 4-7. Zeitplanungsprogramm

Wenn du dir das Regelziel ansiehst(Abbildung 4-8), siehst du unter Redshift cluster den Zieltyp und die Parameter, die für die Ausführung der Abfrage benötigt werden.

Abbildung 4-8. Ziel einer Zeitplanungsregel

Zugriff auf alle deine Daten

Um die ELT-Geschichte abzurunden, unterstützt Amazon Redshift den Zugriff auf Daten, auch wenn sie nicht geladen wurden. Der Amazon Redshift-Computer verarbeitet deine Daten mit allen bereits erwähnten Transformationsfunktionen, ohne dass ein separater Server für die Verarbeitung erforderlich ist. Egal, ob es sich um "Externe Amazon S3-Daten", "Externe Betriebsdaten" oder sogar "Externe Amazon Redshift-Daten" handelt, Abfragen werden in deinem Amazon Redshift Data Warehouse mit der vertrauten ANSI-SQL-Syntax gestellt; nur die zutreffenden Daten werden vom Amazon Redshift Compute verarbeitet. Sie können mit lokalen Daten verknüpft und zum Auffüllen lokaler Tabellen in deinem Amazon Redshift Data Warehouse verwendet werden.

Externe Amazon S3 Daten

Mit Amazon Redshift kannst du lesen und externe Daten, die in Amazon S3 gespeichert sind, mit einfachen SQL-Abfragen schreiben. Der Zugriff auf Daten in Amazon S3 verbessert die Interoperabilität deiner Daten, da du über Amazon Redshift hinaus von mehreren Rechenplattformen aus auf dieselben Amazon S3-Daten zugreifen kannst. Zu diesen Plattformen gehören Amazon Athena, Amazon EMR, Presto und jede andere Rechenplattform, die auf Amazon S3 zugreifen kann. Mit dieser Funktion kann Amazon Redshift externe Amazon S3-Tabellen mit Tabellen verbinden, die sich auf der lokalen Festplatte deines Amazon Redshift Data Warehouse befinden. Bei der Verwendung eines bereitgestellten Clusters nutzt Amazon Redshift eine Flotte von Knoten namens Amazon Redshift Spectrum, die die Amazon S3-Verarbeitung weiter isoliert und Optimierungen wie Prädikats-Pushdown und Aggregation auf die Amazon Redshift Spectrum-Berechnungsebene anwendet, um die Abfrageleistung zu verbessern. Zu den Prädikatsoperatoren, die du auf Amazon Redshift Spectrum übertragen kannst, gehören: = LIKE , IS NULL und CASE WHEN. Außerdem kannst du Transformationslogik verwenden, bei der viele Aggregations- und String-Funktionen auf die Amazon Redshift Spectrum-Ebene übertragen werden. Zu den Arten von Aggregationsfunktionen gehören: COUNT, SUM, AVG, MIN und MAX.

Amazon Redshift Spectrum verarbeitet Amazon S3-Daten und nutzt dabei die 10-fache Anzahl der Slices in deinem bereitgestellten Cluster. Außerdem fallen Kosten in Höhe von $5/TB für die Abfrage an. Wenn du dagegen Amazon S3-Daten mit Amazon Redshift serverless abfragst, erfolgt die Verarbeitung auf deinem Amazon Redshift-Compute und die Kosten sind Teil der RPU-Preise.

Das Abfragen von externen Amazon S3-Daten funktioniert durch die Nutzung eines externen metadata catalog, der Datensätze in databases und tables organisiert. Anschließend ordnest du eine Datenbank einem Amazon Redshift schema zu und gibst deine Anmeldedaten über IAM ROLE an, die bestimmen, welche Zugriffsrechte du hast. In Beispiel 4-15 ist dein Metadatenkatalog der AWS Glue data catalog, der eine Datenbank namens externaldb enthält. Wenn diese Datenbank nicht existiert, wird sie mit diesem Befehl erstellt. Wir haben diese Datenbank mit der IAM-Rolle default, die dem Data Warehouse zugeordnet ist, einem neuen Schema externalschema zugeordnet. Zusätzlich zu AWS Glue data catalog können Benutzer auch hive metastore zuordnen, wenn sich deine Daten in einem EMR-Cluster oder in einer selbstverwalteten Apache Hadoop-Umgebung befinden. Weitere Einzelheiten zu den Optionen bei der Erstellung externer Schemata findest du in der Online-Dokumentation.

Beispiel 4-15. Externes S3-Schema erstellen

CREATE EXTERNAL SCHEMA IF NOT EXISTS externalschema
FROM data catalog DATABASE 'externaldb'
IAM_ROLE default
CREATE EXTERNAL DATABASE IF NOT EXISTS;

Wir haben auf die default IAM-Rolle verwiesen. Achte darauf, dass du die Rolle so änderst, dass sie Zugriff auf die Verwaltung des AWS Glue Data Catalog hat.

Sobald das externe Schema erstellt ist, kannst du die Daten ganz einfach abfragen, ähnlich wie eine Tabelle, die in Amazon Redshift geladen wurde. In Beispiel 4-16 kannst du Daten aus deiner externen Tabelle zusammen mit lokal gespeicherten Daten abfragen. Bitte beachte, dass die Datensätze, auf die im folgenden Beispiel verwiesen wird, nicht eingerichtet wurden. Die Abfrage dient nur zur Veranschaulichung.

Beispiel 4-16. Externer S3-Tabellenzugriff

SELECT
 t.returnflag,
 t.linestatus,
 c.zip,
 sum(t.quantity) AS sum_qty,
 sum(t.extendedprice*(1-t.discount)*(1+t.tax)) AS sum_charge
FROM externalschema.transactions t
JOIN public.customers c on c.id = t.customer_id
WHERE t.year = 2022 AND t.month = 1
GROUP BY t.returnflag, t.linestatus, c.zip;

Diese Abfrage enthält einen Filter, der die Daten aus der externen Tabelle auf Januar 2022 einschränkt, sowie eine einfache Aggregation. Bei der Verwendung eines bereitgestellten Clusters werden dieser Filter und die teilweise Aggregation auf der Amazon Redshift Spectrum-Ebene verarbeitet, wodurch die an deine Rechenknoten gesendete Datenmenge reduziert und die Abfrageleistung verbessert wird.

Da du die beste Leistung erhältst, wenn du Daten abfragst, die lokal in Amazon Redshift gespeichert sind, ist es eine bewährte Methode, deine aktuellsten Daten in Amazon Redshift zu laden und Daten, auf die weniger häufig zugegriffen wird, von externen Quellen abzufragen. Mit dieser Strategie kannst du sicherstellen, dass die heißesten Daten möglichst nah am Rechner und in einem für die analytische Verarbeitung optimierten Format gespeichert sind. In Beispiel 4-17 hast du vielleicht einen Ladeprozess, der die Transaktionstabelle mit den Daten des letzten Monats auffüllt, aber alle deine Daten liegen in Amazon S3. Wenn du sie deinen Nutzern zur Verfügung stellst, sehen sie eine konsolidierte Ansicht der Daten, aber wenn sie auf die heißesten Daten zugreifen, ruft Amazon Redshift sie aus der lokalen Speicherung ab. Bitte beachte, dass die Datensätze, auf die im folgenden Beispiel verwiesen wird, nicht eingerichtet wurden. Die angegebene Abfrage dient nur zur Veranschaulichung.

Beispiel 4-17. Union S3 und lokale Daten

CREATE VIEW public.transactions_all AS
  SELECT … FROM public.transactions
  UNION ALL
  SELECT … FROM externalschema.transactions
  WHERE year != date_part(YEAR, current_date)
    AND month != date_part(MONTH, current_date);
WITH NO SCHEMA BINDING;

Die Klausel NO SCHEMA BINDING muss für externe Tabellen verwendet werden, um sicherzustellen, dass die Daten in Amazon S3 geladen werden können, ohne dass es Auswirkungen oder Abhängigkeiten von Amazon Redshift gibt.

Weitere Informationen zu den Optimierungstechniken von Amazon Redshift Spectrum findest du im Blog zu den bewährten Methoden von Amazon Redshift Spectrum.

Externe betriebliche Daten

Amazon Redshift federated query ermöglicht dir die direkte Abfrage von Daten, die in Transaktionsdatenbanken gespeichert sind, um Daten in Echtzeit zu integrieren und die ETL-Verarbeitung zu vereinfachen. Mit federated query kannst du deinen Nutzern Einblicke in Echtzeit bieten. Ein typischer Anwendungsfall ist, wenn du eine Batch-Ingestion in dein Data Warehouse hast, aber Echtzeit-Analysen benötigst. Du kannst eine kombinierte Ansicht der Daten, die im Batch von Amazon Redshift geladen wurden, und der aktuellen Echtzeitdaten in der Transaktionsdatenbank bereitstellen. Federated Query stellt auch die Metadaten aus diesen Quelldatenbanken als externe Tabellen zur Verfügung, so dass BI-Tools wie Tableau und Amazon QuickSight föderierte Quellen abfragen können. Dies ermöglicht neue Data-Warehouse-Anwendungsfälle, bei denen du Betriebsdaten nahtlos abfragen, ETL-Pipelines vereinfachen und Daten in eine Late-Binding-Ansicht einbauen kannst, die Betriebsdaten mit lokalen Amazon Redshift-Daten kombiniert. Ab 2022 werden die transaktionalen Datenbanken Amazon Aurora PostgreSQL/MySQL und Amazon RDS für PostgreSQL/MySQL unterstützt.

Amazon Redshift federated query funktioniert indem es eine TCP/IP-Verbindung zu deinem operativen Datenspeicher herstellt und diesen auf ein externes Schema abbildet. Du gibst den Datenbanktyp und die Verbindungsinformationen sowie die Anmeldeinformationen für die Verbindung über ein AWS Secrets Manager Geheimnis an. In Beispiel 4-18 ist der Datenbanktyp POSTGRES und die Verbindungsinformationen geben die DATABASE, SCHEMA und URI der DB an. Weitere Einzelheiten zu den Optionen bei der Erstellung von externen Schemata mit der föderierten Abfrage findest du in der Online-Dokumentation.

Beispiel 4-18. Externes Schema erstellen

CREATE EXTERNAL SCHEMA IF NOT EXISTS federatedschema
FROM POSTGRES DATABASE 'db1' SCHEMA 'pgschema'
URI '<rdsname>.<hashkey>.<region>.rds.amazonaws.com'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:123456789012:secret:pgsecret'
IAM_ROLE default;

Wir haben auf die IAM-Rolle default verwiesen. Achte darauf, dass du die Rolle so änderst, dass sie den Zugriff auf den Secrets Manager gewährt, um ein Geheimnis namens pgsecret abzurufen.

Sobald das externe Schema erstellt ist, kannst du die Tabellen so abfragen, wie du eine lokale Amazon Redshift-Tabelle abfragen würdest. In Beispiel 4-19 kannst du Daten aus deiner externen Tabelle zusammen mit lokal gespeicherten Daten abfragen, ähnlich wie bei der Abfrage von externen Amazon S3-Daten. Die Abfrage enthält außerdem einen Filter, der die Daten aus der Verbundtabelle auf den Januar 2022 beschränkt. Die föderierte Abfrage von Amazon Redshift schiebt Prädikate intelligent nach unten, um die Menge der aus der föderierten Quelle gescannten Daten zu beschränken, was die Abfrageleistung erheblich verbessert. Bitte beachte, dass die Datensätze, auf die im folgenden Beispiel verwiesen wird, nicht eingerichtet wurden. Die angegebene Abfrage dient nur zur Veranschaulichung.

Beispiel 4-19. Externer Tabellenzugriff

SELECT
 t.returnflag,
 t.linestatus,
 c.zip,
 sum(t.quantity) AS sum_qty,
 sum(t.extendedprice*(1-t.discount)*(1+t.tax)) AS sum_charge
FROM federatedschema.transactions t
JOIN public.customers c ON c.id = t.customer_id
WHERE t.year = 2022 AND t.month = 1
GROUP by t.returnflag, t.linestatus, c.zip;

Da die föderierte Abfrage Abfragen auf dem transaktionalen System ausführt, solltest du darauf achten, die abgefragten Daten zu begrenzen. Eine gute Praxis ist es, Daten in lokalen Amazon Redshift-Tabellen für historische Daten zu verwenden und nur auf die neuesten Daten in der föderierten Datenbank zuzugreifen.

Neben der Abfrage von Live-Daten bietet die föderierte Abfrage auch die Möglichkeit, ETL-Prozesse zu vereinfachen. Ein gängiges ETL-Muster, das viele Unternehmen beim Aufbau ihres Data Warehouse verwenden, ist upsert. Bei upsert müssen Dateningenieure die Quelle deiner Data-Warehouse-Tabelle überprüfen und entscheiden, ob neue Datensätze eingefügt oder bestehende Datensätze aktualisiert/gelöscht werden sollen. In der Vergangenheit wurde dies in mehreren Schritten erledigt:

1. Erstellen eines vollständigen Extrakts deiner Quelltabelle oder, wenn deine Quelle über eine Änderungsverfolgung verfügt, Extrahieren der Datensätze seit der letzten Verarbeitung der Ladung.

2. Verschieben dieses Extrakts an einen Ort, der sich in deinem Data Warehouse befindet. Im Fall von Amazon Redshift wäre das Amazon S3.

3. Mit einem Bulk Loader werden diese Daten in eine Staging-Tabelle geladen. Im Fall von Amazon Redshift wäre das der Befehl COPY.

4. Ausführen der Befehle MERGE (UPSERT-UPDATE und INSERT) gegen deine Zieltabelle auf der Grundlage der bereitgestellten Daten.

Mit der föderierten Abfrage kannst du die Notwendigkeit von inkrementellen Extrakten in Amazon S3 und das anschließende Laden über COPY umgehen, indem du die Daten direkt in der Quelldatenbank abfragst. In Beispiel 4-20 haben wir gezeigt, wie die Kundentabelle aus der operativen Quelle mit einer einzigen MERGE Anweisung synchronisiert werden kann. Bitte beachte, dass die Datensätze, auf die im folgenden Beispiel verwiesen wird, nicht eingerichtet wurden. Die Abfrage dient nur zur Veranschaulichung.

Beispiel 4-20. Inkrementelle Aktualisierung mit MERGE

MERGE INTO customer
USING federatedschema.customer p ON p.customer_id = customer.customer_id
  AND p.updatets > current_date-1 and p.updatets < current_date
WHEN MATCHED THEN UPDATE SET customer_id = p.customer_id,
  name = p.name, address = p.address,
  nationkey = p.nationkey, mktsegment = p.mktsegment
WHEN NOT MATCHED THEN INSERT (custkey, name, address, nationkey, mktsegment)
  VALUES ( p.customer_id, p.name, p.address, p.nationkey, p.mktsegment )

Weitere Einzelheiten zu den Optimierungstechniken für föderierte Abfragen findest du im Blogbeitrag "Bewährte Methoden für Amazon Redshift Federated Query", und . Weitere Einzelheiten zu anderen Möglichkeiten, wie du deine ETL-Strategie vereinfachen kannst, findest du im Blogbeitrag "Build a Simplified ETL and Live Data Query Solution Using Amazon Redshift Federated Query".

Externe Amazon Redshift Daten

Die Amazon Redshift Datenfreigabe ermöglicht dir die direkte Abfrage von Live-Daten, die im Amazon RMS eines anderen Amazon Redshift Data Warehouse gespeichert sind, unabhängig davon, ob es sich um einen bereitgestellten Cluster mit dem RA3-Knotentyp oder ein serverloses Data Warehouse handelt. Mit dieser Funktion können Daten, die in einem Amazon Redshift Data Warehouse erzeugt wurden, in einem anderen Amazon Redshift Data Warehouse abgerufen werden. Ähnlich wie bei anderen externen Datenquellen stellt die Data-Sharing-Funktionalität auch die Metadaten aus dem produzierenden Amazon Redshift Data Warehouse als externe Tabellen zur Verfügung, so dass der Verbraucher diese Daten abfragen kann, ohne lokale Kopien erstellen zu müssen. Dies ermöglicht neue Data-Warehouse-Nutzungsfälle wie die Verteilung des Eigentums an den Daten und die Isolierung der Ausführung verschiedener Arbeitslasten. In Kapitel 7, "Zusammenarbeit mit gemeinsamer Datennutzung", gehen wir näher auf diese Anwendungsfälle ein. Im folgenden Beispiel erfährst du, wie du eine Datenfreigabe mit einer SQL-Anweisung konfigurierst und wie du sie in deinen ETL/ELT-Prozessen einsetzen kannst. Weitere Informationen darüber, wie du die Datenfreigabe über die Redshift-Konsole aktivieren und konfigurieren kannst, findest du in der Online-Dokumentation.

Der erste Schritt bei der gemeinsamen Nutzung von Daten besteht darin, die namespace deiner Producer- und Consumer-Data-Warehouses zu verstehen. Führe die folgenden Schritte in jedem Data Warehouse aus, um die entsprechenden Werte abzurufen(Beispiel 4-21).

Beispiel 4-21. Aktueller Namensraum

SELECT current_namespace;

Als nächstes erstellst du ein Datashare-Objekt und fügst Datenbankobjekte wie schema und table im Producer Data Warehouse hinzu(Beispiel 4-22).

Beispiel 4-22. Datashare erstellen

CREATE DATASHARE transactions_datashare;
ALTER DATASHARE transactions_datashare
  ADD SCHEMA transactions_schema;
ALTER DATASHARE transactions_datashare
  ADD ALL TABLES IN SCHEMA transactions_schema;

Du kannst nun der Datenfreigabe Zugriff vom Producer zum Consumer gewähren, indem du auf seine namespace verweist(Beispiel 4-23).

Beispiel 4-23. Datashare-Nutzung gewähren

GRANT USAGE ON DATASHARE transactions_datashare
TO NAMESPACE '1m137c4-1187-4bf3-8ce2-CONSUMER-NAMESPACE';

Zum Schluss erstellst du eine Datenbank auf dem Konsumenten, die auf den Datashare-Namen sowie auf die namespace des Produzenten verweist(Beispiel 4-24).

Beispiel 4-24. Datashare-Datenbank erstellen

CREATE DATABASE transactions_database from DATASHARE transactions_datashare
OF NAMESPACE '45b137c4-1287-4vf3-8cw2-PRODUCER-NAMESPACE';

Datenfreigaben können auch kontenübergreifend erteilt werden. In diesem Fall ist ein zusätzlicher Schritt durch den mit der Datenfreigabe verbundenen Administrator erforderlich. Weitere Informationen findest du in der Online-Dokumentation.

Sobald die externe Datenbank erstellt ist, kannst du die Daten genauso einfach abfragen wie eine Tabelle, die lokal in deinem Amazon Redshift Data Warehouse gespeichert ist. In Beispiel 4-25 fragst du Daten aus deiner externen Tabelle ab, die mit lokal gespeicherten Daten verknüpft sind, ähnlich wie bei der Abfrage, die bei der Verwendung externer Amazon S3- und Betriebsdaten ausgeführt wird. Auch diese Abfrage enthält einen Filter, der die Daten aus der externen Tabelle auf den Januar 2022 beschränkt. Bitte beachte, dass die Datensätze, auf die im folgenden Beispiel verwiesen wird, nicht eingerichtet wurden. Die Abfrage dient nur zur Veranschaulichung.

Beispiel 4-25. Datashare-Zugang

SELECT
 t.returnflag,
 t.linestatus,
 c.zip,
 sum(t.quantity) as sum_qty,
 sum(t.extendedprice*(1-t.discount)*(1+t.tax)) as sum_charge
FROM transactions_database.transcations_schema.transactions t
JOIN public.customers c on c.id = t.customer_id
WHERE t.year = 2022 AND t.month = 1
GROUP by t.returnflag, t.linestatus, c.zip;

Du kannst dir vorstellen, dass eine Abteilung für die Verwaltung der Verkaufstransaktionen und eine andere für die Kundenbeziehungen zuständig ist. Die Abteilung für Kundenbeziehungen ist daran interessiert, ihre besten und schlechtesten Kunden zu ermitteln, um ihnen gezieltes Marketing zukommen zu lassen. Anstatt ein einziges Data Warehouse zu pflegen und Ressourcen zu teilen, kann jede Abteilung ihr eigenes Amazon Redshift Data Warehouse nutzen und für ihre eigenen Daten verantwortlich sein. Anstatt die Transaktionsdaten zu duplizieren, kann die Kundenbeziehungsgruppe sie direkt abfragen. Sie kann ein Aggregat dieser Daten erstellen und pflegen und es mit Daten über frühere Marketinginitiativen sowie mit Daten zur Kundenstimmung verknüpfen, um ihre Marketingkampagne zu erstellen.

Mehr über die gemeinsame Nutzung von Daten erfährst du in den Kapiteln "Sichere gemeinsame Nutzung von Amazon Redshift-Daten in Amazon Redshift-Clustern zur Isolierung von Arbeitslasten" und "Bewährte Methoden und Überlegungen zur gemeinsamen Nutzung von Amazon Redshift-Daten".

Externe Transformation

In Szenarien, in denen du ein externes Tool für deine Datentransformationen verwenden möchtest, kann Amazon Redshift über JDBC- und ODBC-Treiber, die entweder in diesen Anwendungen enthalten sind oder heruntergeladen werden können, eine Verbindung zu einer ETL-Plattform deiner Wahl herstellen. Zu den beliebten ETL-Plattformen, die mit Amazon Redshift integriert werden können, gehören Tools von Drittanbietern wie Informatica, Matillion und dbt sowie AWS-eigene Tools wie "AWS Glue". ETL-Tools sind eine wertvolle Möglichkeit, alle Komponenten deiner Datenpipeline zu verwalten. Sie stellen ein Auftrags-Repository zur Verfügung, um Metadaten zu organisieren und zu pflegen. Das macht es für Unternehmen einfacher, ihren Code zu verwalten, anstatt die Logik in SQL-Skripten und gespeicherten Prozeduren zu speichern. Außerdem verfügen sie über Zeitplanungsprogramme, die die Orchestrierung von Aufträgen erleichtern. Das kann nützlich sein, wenn du die in AWS verfügbaren "Zeitplanungs- und Orchestrierungsprogramme" nicht nutzt.

Einige ETL-Tools haben auch die Möglichkeit, die Transformationslogik "nach unten zu schieben". Wenn du aus deinem Amazon Redshift Data Warehouse liest und schreibst, kannst du deinen Auftrag mit den visuellen Funktionen des ETL-Tools entwerfen, aber anstatt die Daten tatsächlich auf den ETL-Server zu extrahieren, wird der Code in SQL-Anweisungen umgewandelt, die auf Amazon Redshift laufen. Diese Strategie kann sehr leistungsfähig sein, wenn es um die Umwandlung großer Datenmengen geht, kann aber auch viele Ressourcen verbrauchen, die deine Endnutzer/innen für die Analyse der Daten benötigen. Wenn du die Push-Down-Funktionen deines ETL-Tools nicht nutzt, weil dein Auftrag nicht in Amazon Redshift liest und schreibt oder weil du beschlossen hast, die Transformationslogik auszulagern, ist es wichtig sicherzustellen, dass dein ETL-Tool die Daten von Amazon Redshift performant liest und schreibt.

Wie in Kapitel 3, "Einrichten deiner Datenmodelleund Einlesen von Daten", beschrieben , ist die performanteste Art, Daten zu laden, die Anweisung COPY. Aufgrund der Partnerschaft zwischen AWS und ETL-Anbietern wie Informatica und Matillion hat AWS dafür gesorgt, dass die Anbieter Konnektoren mit Blick auf diese Strategie entwickelt haben. In der Informatica Amazon Redshift-Architektur in Abbildung 4-9 kannst du zum Beispiel sehen, dass das Tool, wenn du ein Amazon Redshift-Ziel und einen Staging-Bereich in Amazon S3 angegeben hast, statt das Ziel direkt über eine Einfügung zu laden, stattdessen in Amazon S3 schreibt und dann die Amazon Redshift COPY -Anweisung zum Laden in die Zieltabelle verwendet. Dieselbe Strategie funktioniert auch für die Anweisungen update und delete, nur dass Informatica die Zieltabelle nicht direkt lädt, sondern in eine Staging-Tabelle schreibt und die Anweisungen update und delete nach dem Laden ausführt. Diese Optimierung ist möglich, weil AWS mit mehreren Softwareanbietern zusammenarbeitet, um sicherzustellen, dass die Benutzer das Tool problemlos nutzen können und ihre Datenpipelines performant sind. In den folgenden Leitfäden findest du weitere Details zu bewährten Methoden bei der Verwendung einiger beliebter ETL-Tools von Drittanbietern:

• Bewährte Methoden für den Informatica-Amazon Redshift Connector

• Matillion-Amazon Redshift Bewährte Methoden für die ETL-Verarbeitung

• dbt-Best Methoden für die Nutzung von Amazon Redshift und dbt

Abbildung 4-9. Informatica Amazon Redshift Architektur

AWS-Kleber

AWS Glue ist einer der nativen serverlosen Datenintegrationsservices, die häufig verwendet werden, um Daten mithilfe von Python oder Scala umzuwandeln und auf einer Datenverarbeitungsmaschine auszuführen. Mit AWS Glue(Abbildung 4-10) kannst du Amazon S3-Daten lesen, Umwandlungen vornehmen und Daten in Amazon Redshift Data Warehouses und andere Datenplattformen einspeisen. AWS Glue erleichtert das Erkennen, Aufbereiten, Verschieben und Integrieren von Daten aus verschiedenen Quellen für Analysen, ML und Anwendungsentwicklung. Es bietet mehrere Datenintegrations-Engines, darunter AWS Glue für Apache Spark, AWS Glue für Ray und AWS Glue für Python Shell. Je nach den Merkmalen deiner Arbeitslast und den Vorlieben deiner Entwickler und Analysten kannst du die passende Engine für deine Arbeitslast verwenden.

Abbildung 4-10. ETL-Integration mit AWS Glue

Seit AWS Glue V4 ist ein neuer Amazon Redshift Spark Connector mit einem neuen JDBC-Treiber in AWS Glue ETL-Aufträgen enthalten. Damit kannst du Apache Spark-Anwendungen erstellen, die als Teil deiner Dateneingabe- und -umwandlungspipelines Daten aus Amazon Redshift lesen und in sie schreiben. Der neue Konnektor und Treiber unterstützt das Pushing von relationalen Operationen wie Joins, Aggregationen, Sortierungen und skalaren Funktionen von Spark auf Amazon Redshift, um die Leistung deiner Aufträge zu verbessern, indem die zu verarbeitende Datenmenge reduziert wird. Außerdem unterstützt er IAM-basierte Rollen, um Single-Sign-On-Funktionen zu ermöglichen, und ist mit AWS Secrets Manager integriert, um Schlüssel sicher zu verwalten.

Um deine AWS Glue-Aufträge zu verwalten, bietet AWS ein visuelles Authoring-Tool, AWS Glue Studio. Dieser Service befolgt viele der bewährten Methoden der bereits erwähnten ETL-Tools von Drittanbietern; aufgrund der Integration sind jedoch weniger Schritte zur Erstellung und Verwaltung deiner Datenpipelines erforderlich.

In Beispiel 4-26 erstellen wir einen Auftrag, der inkrementelle Transaktionsdaten aus Amazon S3 lädt und sie in eine Tabelle lineitem mit dem Schlüssel (l_orderkey, l⁠_⁠l⁠i⁠n⁠e​n⁠u⁠m⁠b⁠e⁠r) in deinem Amazon Redshift Data Warehouse zusammenführt.

Beispiel 4-26. Tabelle lineitem erstellen

CREATE TABLE IF NOT EXISTS lineitem (
  L_ORDERKEY varchar(20) NOT NULL,
  L_PARTKEY varchar(20),
  L_SUPPKEY varchar(20),
  L_LINENUMBER integer NOT NULL,
  L_QUANTITY varchar(20),
  L_EXTENDEDPRICE varchar(20),
  L_DISCOUNT varchar(20),
  L_TAX varchar(20),
  L_RETURNFLAG varchar(1),
  L_LINESTATUS varchar(1),
  L_SHIPDATE date,
  L_COMMITDATE date,
  L_RECEIPTDATE date,
  L_SHIPINSTRUCT varchar(25),
  L_SHIPMODE varchar(10),
  L_COMMENT varchar(44));

Um einen Klebeauftrag zu erstellen, folgen wir den Anweisungen in den nächsten beiden Abschnitten.

Amazon Redshift Zielverbindung registrieren

Navigiere zu "Verbindung erstellen", um eine neue AWS Glue-Verbindung zu erstellen. Benenne die Verbindung und wähle als Verbindungstyp Amazon Redshift (siehe Abbildung 4-11).

Abbildung 4-11. Amazon Redshift Verbindungsname

Als Nächstes wählst du die Datenbankinstanz aus der Liste der automatisch entdeckten Amazon Redshift Data Warehouses in deinem AWS-Konto und deiner Region aus. Lege den Datenbanknamen und die Zugangsdaten fest. Du hast die Möglichkeit, entweder einen Benutzernamen und ein Passwort festzulegen oder den AWS Secrets Manager zu verwenden. Klicke schließlich auf "Verbindung erstellen" (siehe Abbildung 4-12).

Abbildung 4-12. Amazon Redshift Verbindungsinstanz

Erstelle und führe deinen AWS Glue-Auftrag aus

Um einen AWS Glue-Auftrag zu erstellen, rufe die Seite AWS Glue Studio Aufträge auf. Du siehst ein Eingabeaufforderung mit Optionen für deinen Auftrag(Abbildung 4-13). In diesem Beispiel wählen wir "Visual mit einer Quelle und einem Ziel". Ändere das Ziel auf Amazon Redshift und wähle "Erstellen".

Abbildung 4-13. AWS Glue Auftrag erstellen

Als Nächstes erhältst du eine visuelle Darstellung deines Auftrags. Im ersten Schritt wählst du den Knoten Datenquelle aus und legst den Typ der S3-Quelle fest(Abbildung 4-14). Für unseren Anwendungsfall verwenden wir einen S3-Speicherort und geben den Speicherort unserer Daten ein: s3://redshift-immersionday-labs/data/lineitem-part/. Wähle die Parsing-Details wie Datenformat, Begrenzungszeichen, Escape-Zeichen usw. Für unseren Anwendungsfall haben die Dateien ein CSV-Format, sind durch Pipe (|) getrennt und haben keine Spaltenüberschriften. Klicke schließlich auf die Schaltfläche "Schema ableiten".

Abbildung 4-14. AWS Glue Set Amazon S3 Bucket

Wenn du einen Data Lake eingerichtet hast, den du für Abfragen mit anderen AWS-Diensten wie Amazon Athena, Amazon EMR oder sogar Amazon Redshift als externe Tabelle verwendest, kannst du alternativ die Option "Datenkatalogtabelle" verwenden.

Als Nächstes können wir unsere Daten umwandeln (Abbildung 4-15). Der Auftrag wird mit einem einfachen ApplyMapping-Knoten erstellt, aber du hast viele Möglichkeiten, deine Daten zu transformieren, wie z. B. das Zusammenführen, Aufteilen und Aggregieren von Daten. Weitere Transformationsknoten findest du in der AWS-Dokumentation "Bearbeiten von AWS Glue Managed Data Transform Nodes". Wähle den Transform-Knoten aus und lege den Zielschlüssel fest, der mit dem Quellschlüssel übereinstimmt. In unserem Fall hatten die Quelldaten keine Spaltenüberschriften und wurden mit generischen Spalten (col#) registriert. Ordne sie den entsprechenden Spalten in deiner lineitem Tabelle zu.

Abbildung 4-15. AWS Glue apply mapping

Jetzt kannst du die Amazon Redshift Details festlegen(Abbildung 4-16). Wähle "Direkte Datenverbindung" und wähle das entsprechende Schema (public) und die Tabelle (lineitem). Du kannst auch festlegen, wie der Auftrag mit neuen Datensätzen umgehen soll. Du kannst entweder einfach jeden Datensatz einfügen oder einen Schlüssel festlegen, damit der Auftrag Daten aktualisieren kann, die neu verarbeitet werden müssen. Für unseren Anwendungsfall wählen wir MERGE und setzen die Schlüssel l_orderkey und l_linenumber. Auf diese Weise werden die Daten bei der Ausführung des Auftrags zunächst in eine Staging-Tabelle geladen, dann wird eine MERGE -Anweisung auf der Grundlage der bereits im Ziel vorhandenen Daten ausgeführt, bevor die neuen Daten mit einer INSERT -Anweisung geladen werden.

Abbildung 4-16. AWS Glue setzt Amazon Redshift Ziel

Bevor du den Auftrag speichern und ausführen kannst, musst du einige zusätzliche Auftragsdetails festlegen, wie die IAM-Rolle, mit der der Auftrag ausgeführt werden soll, und den Dateinamen des Skripts(Abbildung 4-17). Die Rolle sollte die Berechtigung haben, auf die Dateien in deinem Amazon S3-Speicherort zuzugreifen, und sie sollte auch vom AWS Glue-Service übernommen werden können. Sobald du die IAM-Rolle erstellt und festgelegt hast, klicke auf Speichern und Ausführen, um deinen Auftrag auszuführen.

Abbildung 4-17. AWS Glue Set Auftragsdetails

Du kannst den Joblauf überprüfen, indem du zur Registerkarte Aufträge navigierst. Dort siehst du Details zur Job-ID und die Laufstatistik(Abbildung 4-18).

Abbildung 4-18. Details zum AWS Glue-Auftragslauf

Damit AWS Glue auf Amazon S3, zugreifen kann, musst du einen VPC-Endpunkt erstellen, falls du noch keinen erstellt hast. In der Online-Dokumentation findest du weitere Informationen.

Sobald der Auftrag abgeschlossen ist, kannst du zur Amazon Redshift-Konsole navigieren, um die Abfragen und Ladungen zu überprüfen(Abbildung 4-19). Du siehst die Abfragen, die erforderlich sind, um die temporäre Tabelle zu erstellen, die Amazon S3-Dateien zu laden und die Merge-Anweisung auszuführen, die alte Daten löscht und neue Daten einfügt.

Abbildung 4-19. Amazon Redshift Abfrageverlauf

Zusammenfassung

In diesem Kapitel wurden die verschiedenen Möglichkeiten beschrieben, wie du Daten mit Amazon Redshift transformieren kannst. Dank der Fähigkeit von Amazon Redshift, auf alle Daten zuzugreifen, unabhängig davon, ob sie geladen wurden oder nicht, kannst du Daten in deinem Data Lake, in operativen Quellen oder in anderen Amazon Redshift Data Warehouses schnell und einfach umwandeln. Außerdem haben wir gezeigt, dass du mit dem Zeitplannungsprogramm für Abfragen von Amazon Redshift zeitbasierte Zeitpläne implementieren kannst, um diese Aufträge zu orchestrieren. Schließlich haben wir uns damit befasst, wie Amazon Redshift mit Drittanbietern von ETL- und Orchestrierungslösungen zusammenarbeitet, um eine optimale Ausführungsleistung zu erzielen und sich in die Tools zu integrieren, die du vielleicht schon in deinem Unternehmen hast.

Im nächsten Kapitel werden wir darüber sprechen, wie Amazon Redshift skaliert, wenn du Änderungen an deiner Arbeitslast vornimmst. Wir werden auch darauf eingehen, wie ein serverloses Data Warehouse von Amazon Redshift automatisch skaliert wird und wie du die Kontrolle über die Skalierung deines bereitgestellten Data Warehouses hast. Wir werden auch darüber sprechen, wie du mit Amazon Redshift die beste Preisleistung erzielen kannst, indem du bewährte Methoden einsetzt.