Die vorliegende Übersetzung wurde maschinell erstellt. Im Falle eines Konflikts oder eines Widerspruchs zwischen dieser übersetzten Fassung und der englischen Fassung (einschließlich infolge von Verzögerungen bei der Übersetzung) ist die englische Fassung maßgeblich.
BLOBDateien TEXT mithilfe der Dateikodierung in Aurora SQL Postgre-Compatible laden
Erstellt von Bhanu Ganesh Gudivada () und Jeevan Shetty () AWS AWS
Umwelt: Produktion | Quelle: Lokale Oracle-Datenbank | Ziel: Aurora Postgre-Compatible SQL |
R-Typ: Re-Architect | Arbeitslast: Oracle; Open Source | Technologien: Migration; Datenbanken |
AWSdienstleistungen: Amazon Aurora |
Übersicht
Während der Migration gibt es häufig Fälle, in denen Sie unstrukturierte und strukturierte Daten verarbeiten müssen, die aus Dateien in einem lokalen Dateisystem geladen wurden. Die Daten können sich auch in einem Zeichensatz befinden, der sich vom Zeichensatz der Datenbank unterscheidet.
Diese Dateien enthalten die folgenden Datentypen:
Metadaten — Diese Daten beschreiben die Dateistruktur.
Semistrukturierte Daten — Dies sind Textzeichenfolgen in einem bestimmten Format, z. B. JSON oder. XML Möglicherweise können Sie Aussagen zu solchen Daten treffen, z. B. „beginnt immer mit '<'“ oder „enthält keine Zeilenumbruchzeichen“.
Volltext — Diese Daten enthalten normalerweise alle Arten von Zeichen, einschließlich Zeilenumbruch- und Anführungszeichen. Sie können auch aus Multibyte-Zeichen in UTF -8 bestehen.
Binärdaten — Diese Daten können Byte oder Kombinationen von Bytes enthalten, einschließlich Nullen und Markierungen. end-of-file
Das Laden einer Mischung dieser Datentypen kann eine Herausforderung sein.
Das Muster kann mit lokalen Oracle-Datenbanken, Oracle-Datenbanken, die sich auf Amazon Elastic Compute Cloud (AmazonEC2) -Instances in der Amazon Web Services (AWS) Cloud befinden, und Amazon Relational Database Service (AmazonRDS) für Oracle-Datenbanken verwendet werden. Als Beispiel verwendet dieses Muster Amazon Aurora SQL Postgre-Compatible Edition.
In der Oracle-Datenbank können Sie mithilfe eines BFILE
(binären Datei-) Zeigers, des DBMS_LOB
Pakets und der Oracle-Systemfunktionen Daten aus einer Datei laden und CLOB mit Zeichenkodierung in Dateien konvertieren. Da Postgre den BLOB Datentyp bei der Migration zu einer Amazon Aurora SQL Postgre-Compatible Edition-Datenbank SQL nicht unterstützt, müssen diese Funktionen in Postgre-kompatible Skripts konvertiert werden. SQL
Dieses Muster bietet zwei Ansätze für das Laden einer Datei in eine einzelne Datenbankspalte in einer Amazon Aurora SQL Postgre-kompatiblen Datenbank:
Ansatz 1 — Sie importieren Daten aus Ihrem Amazon Simple Storage Service (Amazon S3) -Bucket, indem Sie die
table_import_from_s3
Funktion deraws_s3
Erweiterung mit der Kodierungsoption verwenden.Ansatz 2 — Sie kodieren außerhalb der Datenbank hexadezimal und dekodieren dann, um sie innerhalb der Datenbank anzuzeigen.
TEXT
Wir empfehlen die Verwendung von Approach 1, da Aurora SQL Postgre-Compatible direkt in die aws_s3
Erweiterung integriert ist.
Dieses Muster verwendet das Beispiel des Ladens einer Flat-Datei, die eine E-Mail-Vorlage mit Multibyte-Zeichen und unterschiedlicher Formatierung enthält, in eine Amazon Aurora SQL Postgre-kompatible Datenbank.
Voraussetzungen und Einschränkungen
Voraussetzungen
Ein aktives Konto AWS
Eine RDS Amazon-Instance oder eine Aurora SQL Postgre-kompatible Instance
Ein grundlegendes Verständnis eines SQL relationalen Datenbankmanagementsystems () RDBMS
Ein Amazon Simple Storage Service (Amazon S3) -Bucket.
Kenntnis der Systemfunktionen in Oracle und Postgre SQL
RPMPackage HexDump — XXD -0.1.1 (in Amazon Linux 2 enthalten)
Hinweis: Amazon Linux 2 nähert sich dem Ende des Supports. Weitere Informationen finden Sie unter Amazon Linux FAQs 2.
Einschränkungen
Für den
TEXT
Datentyp beträgt die längste mögliche Zeichenfolge, die gespeichert werden kann, etwa 1 GB.
Versionen der Produkte
Aurora unterstützt die in Amazon Aurora SQL Postgre-Updates aufgeführten SQL Postgre-Versionen.
Architektur
Zieltechnologie-Stack
Aurora Postgre-kompatibel SQL
Zielarchitektur
Ansatz 1 — Verwendung von aws_s3.table_import_from_s3
Von einem lokalen Server wird eine Datei, die eine E-Mail-Vorlage mit Multibyte-Zeichen und benutzerdefinierter Formatierung enthält, an Amazon S3 übertragen. Die durch dieses Muster bereitgestellte benutzerdefinierte Datenbankfunktion verwendet die aws_s3.table_import_from_s3
Funktion mitfile_encoding
, um Dateien in die Datenbank zu laden und Abfrageergebnisse als TEXT
Datentyp zurückzugeben.
Dateien werden in den Staging-S3-Bucket übertragen.
Dateien werden in die Amazon Aurora SQL Postgre-Compatible Datenbank hochgeladen.
Mithilfe des pgAdmin Clients
load_file_into_clob
wird die benutzerdefinierte Funktion in der Aurora-Datenbank bereitgestellt.Die benutzerdefinierte Funktion wird intern
table_import_from_s3
mit file_encoding verwendet. Die Ausgabe der Funktion wird durch die Verwendung vonarray_to_string
undarray_agg
alsTEXT
Ausgabe abgerufen.
Ansatz 2 — Hexadezimale Kodierung außerhalb der Datenbank und Dekodierung zur Ansicht TEXT innerhalb der Datenbank
Eine Datei von einem lokalen Server oder einem lokalen Dateisystem wird in einen Hex-Dump konvertiert. Dann wird die Datei SQL als Feld in Postgre importiert. TEXT
Konvertieren Sie die Datei in der Befehlszeile mit der Option in einen Hex-Dump.
xxd -p
Laden Sie die Hex-Dump-Dateien mithilfe der
\copy
Option in Aurora Postgre SQL -Compatible hoch und dekodieren Sie dann die Hex-Dump-Dateien in Binärdateien.Kodieren Sie die Binärdaten, die zurückgegeben werden sollen als.
TEXT
Tools
AWSDienste
Amazon Aurora SQL Postgre-Compatible Edition ist eine vollständig verwaltete, ACID konforme relationale Datenbank-Engine, die Sie bei der Einrichtung, dem Betrieb und der Skalierung von Postgre-Bereitstellungen unterstützt. SQL
AWSDie Befehlszeilenschnittstelle (AWSCLI) ist ein Open-Source-Tool, mit dem Sie mithilfe von Befehlen in Ihrer Befehlszeilen-Shell mit AWS Diensten interagieren können.
Andere Tools
pgAdmin4
ist eine Open-Source-Verwaltungs- und Entwicklungsplattform für PostgreSQL. pgAdmin4 kann unter Linux, Unix, Mac OS und Windows zur Verwaltung von SQL Postgre verwendet werden.
Epen
Aufgabe | Beschreibung | Erforderliche Fähigkeiten |
---|---|---|
Starten einer EC2-Instance | Anweisungen zum Starten einer Instance finden Sie unter Starten Sie Ihre Instance. | DBA |
Installieren Sie das SQL pgAdmin Postgre-Client-Tool. | Laden Sie pgAdmin | DBA |
Erstellen Sie eine IAM Richtlinie. | Erstellen Sie eine AWS Identity and Access Management (IAM) -Richtlinie mit dem Namen
| DBA |
Erstellen Sie eine IAM Rolle für den Objektimport von Amazon S3 nach Aurora SQL Postgre-Compatible. | Verwenden Sie den folgenden Code, um eine IAM Rolle
| DBA |
Ordnen Sie die IAM Rolle dem Cluster zu. | Führen Sie den folgenden AWS CLI Befehl aus, um die IAM Rolle dem Aurora SQL Postgre-Compatible Datenbank-Cluster zuzuordnen. Ändern Sie
| DBA |
Laden Sie das Beispiel auf Amazon S3 hoch. |
| DBA, App-Besitzer |
Stellen Sie die benutzerdefinierte Funktion bereit. |
| Besitzer der App, DBA |
Führen Sie die benutzerdefinierte Funktion zum Importieren der Daten in die Datenbank aus. | Führen Sie den folgenden SQL Befehl aus und ersetzen Sie die Elemente in spitzen Klammern durch die entsprechenden Werte.
Ersetzen Sie die Elemente in spitzen Klammern durch die entsprechenden Werte, wie im folgenden Beispiel gezeigt, bevor Sie den Befehl ausführen.
Der Befehl lädt die Datei aus Amazon S3 und gibt die Ausgabe als zurück | Besitzer der App, DBA |
Aufgabe | Beschreibung | Erforderliche Fähigkeiten |
---|---|---|
Konvertiert die Vorlagendatei in einen Hex-Dump. | Das Hexdump-Hilfsprogramm zeigt den Inhalt von Binärdateien hexadezimal, dezimal, oktal oder an. ASCII Der Um den Dateiinhalt in einen Hex-Dump zu konvertieren, führen Sie den folgenden Shell-Befehl aus.
Ersetzen Sie den Pfad und die Datei durch die entsprechenden Werte, wie im folgenden Beispiel gezeigt.
| DBA |
Laden Sie die Hexdump-Datei in das Datenbankschema. | Verwenden Sie die folgenden Befehle, um die Hexdump-Datei in die Aurora Postgre-Compatible Datenbank zu laden. SQL
| DBA |
Zugehörige Ressourcen
Referenzen
Tutorials
Zusätzliche Informationen
benutzerdefinierte Funktion load_file_into_clob
CREATE OR REPLACE FUNCTION load_file_into_clob( s3_bucket_name text, s3_bucket_region text, file_name text, file_delimiter character DEFAULT '&'::bpchar, file_encoding text DEFAULT 'UTF8'::text) RETURNS text LANGUAGE 'plpgsql' COST 100 VOLATILE PARALLEL UNSAFE AS $BODY$ DECLARE blob_data BYTEA; clob_data TEXT; l_table_name CHARACTER VARYING(50) := 'file_upload_hex'; l_column_name CHARACTER VARYING(50) := 'template'; l_return_text TEXT; l_option_text CHARACTER VARYING(150); l_sql_stmt CHARACTER VARYING(500); BEGIN EXECUTE format ('CREATE TEMPORARY TABLE %I (%I text, id_serial serial)', l_table_name, l_column_name); l_sql_stmt := 'select ''(format text, delimiter ''''' || file_delimiter || ''''', encoding ''''' || file_encoding || ''''')'' '; EXECUTE FORMAT(l_sql_stmt) INTO l_option_text; EXECUTE FORMAT('SELECT aws_s3.table_import_from_s3($1,$2,$6, aws_commons.create_s3_uri($3,$4,$5))') INTO l_return_text USING l_table_name, l_column_name, s3_bucket_name, file_name,s3_bucket_region,l_option_text; EXECUTE format('select array_to_string(array_agg(%I order by id_serial),E''\n'') from %I', l_column_name, l_table_name) INTO clob_data; drop table file_upload_hex; RETURN clob_data; END; $BODY$;
E-Mail-Vorlage
###################################################################################### ## ## ## johndoe Template Type: email ## ## File: johndoe.salary.event.notification.email.vm ## ## Author: Aimée Étienne Date 1/10/2021 ## ## Purpose: Email template used by EmplmanagerEJB to inform a johndoe they ## ## have been given access to a salary event ## ## Template Attributes: ## ## invitedUser - PersonDetails object for the invited user ## ## salaryEvent - OfferDetails object for the event the user was given access ## ## buyercollege - CompDetails object for the college owning the salary event ## ## salaryCoordinator - PersonDetails of the salary coordinator for the event ## ## idp - Identity Provider of the email recipient ## ## httpWebRoot - HTTP address of the server ## ## ## ###################################################################################### $!invitedUser.firstname $!invitedUser.lastname, Ce courriel confirme que vous avez ete invite par $!salaryCoordinator.firstname $!salaryCoordinator.lastname de $buyercollege.collegeName a participer a l'evenement "$salaryEvent.offeringtitle" sur johndoeMaster Sourcing Intelligence. Votre nom d'utilisateur est $!invitedUser.username Veuillez suivre le lien ci-dessous pour acceder a l'evenement. ${httpWebRoot}/myDashboard.do?idp=$!{idp} Si vous avez oublie votre mot de passe, utilisez le lien "Mot de passe oublie" situe sur l'ecran de connexion et entrez votre nom d'utilisateur ci-dessus. Si vous avez des questions ou des preoccupations, nous vous invitons a communiquer avec le coordonnateur de l'evenement $!salaryCoordinator.firstname $!salaryCoordinator.lastname au ${salaryCoordinator.workphone}. ******* johndoeMaster Sourcing Intelligence est une plateforme de soumission en ligne pour les equipements, les materiaux et les services. Si vous avez des difficultes ou des questions, envoyez un courriel a support@johndoeMaster.com pour obtenir de l'aide.