CREATE EXTERNAL TABLE - Amazon Redshift

CREATE EXTERNAL TABLE

Erstellt eine neue externe Tabelle im angegebenen Schema. Alle externen Tabellen müssen in einem externen Schema erstellt werden. Externe Schemata und externe Tabellen unterstützen keine Suchpfade. Weitere Informationen finden Sie unter CREATE EXTERNAL SCHEMA.

Zum Erstellen externer Tabellen müssen Sie der Eigentümer des externen Schemas oder ein Superuser sein. Mit dem Befehl ALTER SCHEMA können Sie den Besitzer eines externen Schemas ändern. Der Zugriff auf externe Tabellen wird durch den Zugriff auf die externen Schemata gesteuert. Sie können für eine externe Tabelle keine Berechtigungen gewähren (GRANT) oder widerrufen (REVOKE). Sie gewähren oder widerrufen stattdessen das Recht USAGE für das externe Schema.

Zusätzlich zu externen Tabellen, die mittels des Befehls CREATE EXTERNAL TABLE erstellt werden, kann Amazon Redshift externe Tabellen referenzieren, die in einem AWS Glue- oder AWS Lake Formation-Katalog oder in einem Apache-Hive-Metastore definiert sind. Verwenden Sie den Befehl CREATE EXTERNAL SCHEMA, um eine externe Datenbank zu registrieren, die im externen Katalog definiert ist, und um die externen Tabellen für die Verwendung in Amazon Redshift zur Verfügung zu stellen. Wenn die externe Tabelle in einem AWS Glue- oder AWS Lake Formation-Katalog oder Hive-Metastore vorhanden ist, müssen Sie die Tabelle nicht mit CREATE EXTERNAL TABLE erstellen. Um externe Tabellen anzuzeigen, führen Sie eine Abfrage für die Systemansicht SVV_EXTERNAL_TABLES aus.

Durch das Ausführen des Befehls CREATE EXTERNAL TABLE AS können Sie eine externe Tabelle basierend auf der Spaltendefinition aus einer Abfrage erstellen und die Ergebnisse dieser Abfrage in Amazon S3 schreiben. Die Ergebnisse liegen im Apache Parquet- oder im Textformat mit Trennzeichen vor. Wenn die externe Tabelle über mindestens einen Partitionsschlüssel verfügt, partitioniert Amazon Redshift neue Dateien entsprechend diesen Partitionsschlüsseln und registriert neue Partitionen automatisch im externen Katalog. Weitere Hinweise zu CREATE EXTERNAL TABLE AS finden Sie unter Nutzungshinweise.

Sie können eine externe Tabelle mit der gleichen SELECT-Syntax abfragen, die Sie auch für andere Amazon-Redshift-Tabellen verwenden. Sie können auch die INSERT-Syntax verwenden, um neue Dateien in den Speicherort der externen Tabelle auf Amazon S3 zu schreiben. Weitere Informationen finden Sie unter INSERT (externe Tabelle).

Zum Erstellen einer Ansicht mit einer externen Tabelle fügen Sie die Klausel WITH NO SCHEMA BINDING in die CREATE VIEW-Aussage ein.

CREATE EXTERNAL TABLE kann nicht innerhalb einer Transaktion (BEGIN … END) ausgeführt werden. Weitere Informationen Transaktionen finden Sie unter Serialisierbare Isolierung.

Syntax

CREATE EXTERNAL TABLE
external_schema.table_name  
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )] 
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name' 
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

Im Folgenden finden Sie die Syntax für CREATE EXTERNAL TABLE AS.

CREATE EXTERNAL TABLE
external_schema.table_name  
[ PARTITIONED BY (col_name [, … ] ) ] 
[ ROW FORMAT DELIMITED row_format ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]
 AS
 { select_statement }
         

Parameters

external_schema.table_name

Der Name der Tabelle, die erstellt werden soll, qualifiziert durch einen externen Schemanamen. Externe Tabellen müssen in einem externen Schema erstellt werden. Weitere Informationen finden Sie unter CREATE EXTERNAL SCHEMA.

Die maximale Länge des Tabellennamens beträgt 127 Bytes; längere Namen werden bei 127 Bytes abgeschnitten. Sie können UTF-8-Multibyte-Zeichen bis zu einer Länge von vier Bytes verwenden. Amazon Redshift erzwingt ein Limit von 9.900 Tabellen pro Cluster, einschließlich benutzerdefinierter temporärer Tabellen und temporärer Tabellen, die von Amazon Redshift während der Abfrageverarbeitung oder Systemwartung erstellt werden. Optional können Sie die Tabelle mit dem Datenbanknamen qualifizieren. Im folgenden Beispiel ist der Datenbankname spectrum_db, der Name des externen Schemas spectrum_schema und der Tabellenname test.

create external table spectrum_db.spectrum_schema.test (c1 int) stored as parquet location 's3://mybucket/myfolder/';

Wenn die angegebene Datenbank oder das angegebene Schema nicht vorhanden sind, wird die Tabelle nicht erstellt, und die Anweisung gibt einen Fehler zurück. Sie können in den Systemdatenbanken template0, template1 und padb_harvest keine Tabellen oder Ansichten erstellen.

Der Tabellenname muss für das angegebene Schema eindeutig sein.

Weitere Informationen zu gültigen Namen finden Sie unter Namen und Kennungen.

( column_name data_type )

Der Name und der Datentyp jeder Spalte, die erstellt wird.

Die maximale Länge des Spaltennamens beträgt 127 Bytes; längere Namen werden bei 127 Bytes abgeschnitten. Sie können UTF-8-Multibyte-Zeichen bis zu einer Länge von vier Bytes verwenden. Sie können keine Spaltennamen "$path" oder "$size" festlegen. Weitere Informationen zu gültigen Namen finden Sie unter Namen und Kennungen.

Standardmäßig erstellt Amazon Redshift externe Tabellen mit den Pseudospalten $path und $size. Sie können die Erstellung von Pseudospalten für eine Sitzung deaktivieren, indem Sie den spectrum_enable_pseudo_columns-Konfigurationsparameter auf false setzen. Weitere Informationen finden Sie unter Pseudospalten .

Falls Pseudospalten aktiviert sind, können Sie höchstens 1.598 Spalten in einer einzelnen Tabelle definieren. Wenn Pseudospalten nicht aktiviert sind, können Sie höchstens 1.600 Spalten in einer einzelnen Tabelle definieren.

Wenn Sie eine „breite Tabelle“ erstellen, achten Sie darauf, dass Ihre Spaltenliste nicht die Zeilenbreitengrenzen überschreitet, um während der Verarbeitung von Lasten und Abfragen sofort Ergebnisse bereitzustellen. Weitere Informationen finden Sie unter Nutzungshinweise.

Für den Befehl CREATE EXTERNAL TABLE AS ist keine Spaltenliste erforderlich, da Spalten von der Abfrage abgeleitet werden.

data_type

Die folgenden Datentypen werden unterstützt:

  • SMALLINT (INT2)

  • INTEGER (INT, INT4)

  • BIGINT (INT8)

  • DECIMAL (NUMERIC)

  • REAL (FLOAT4)

  • DOUBLE PRECISION (FLOAT8)

  • BOOLEAN (BOOL)

  • CHAR (CHARACTER)

  • VARCHAR (CHARACTER VARYING)

  • DATE (Der Datentyp DATE kann nur in Text-, Parquet- oder ORC-Datendateien oder als Partitionsspalte verwendet werden.)

  • TIMESTAMP

Für DATE können Sie die im Folgenden beschriebenen Formate verwenden. Für Monatswerte, die in Ziffern dargestellt werden, werden die folgenden Formate unterstützt:

  • mm-dd-yyyy Zum Beispiel 05-01-2017. Dies ist die Standardeinstellung.

  • yyyy-mm-dd, wenn das Jahr mit mehr als zwei Ziffern dargestellt werden soll. Zum Beispiel 2017-05-01.

Für Monatswerte, die mit einer Abkürzung aus drei Buchstaben dargestellt werden, werden die folgenden Formate unterstützt:

  • mmm-dd-yyyy Zum Beispiel may-01-2017. Dies ist die Standardeinstellung.

  • dd-mmm-yyyy, wenn das Jahr mit mehr als zwei Ziffern dargestellt werden soll. Zum Beispiel 01-may-2017.

  • yyyy-mmm-dd, wenn das Jahr mit mehr als zwei Ziffern dargestellt werden soll. Zum Beispiel 2017-may-01.

Für Jahreswerte, die konstant unter 100 liegen, wird das Jahr wie folgt berechnet:

  • Wenn das Jahr weniger als 70 beträgt, wird das Jahr als das Jahr plus 2000 berechnet. Im Format mm-dd-yyyy wird das Datum 05-01-17 beispielsweise zu 05-01-2017 konvertiert.

  • Wenn das Jahr weniger als 100 beträgt, aber mehr als 69, wird das Jahr als das Jahr plus 1900 berechnet. Im Format mm-dd-yyyy wird das Datum 05-01-89 beispielsweise zu 05-01-1989 konvertiert.

  • Für Jahreswerte, die mit zwei Ziffern dargestellt werden, müssen führende Nullen hinzugefügt werden, um das Jahr mit vier Ziffern darzustellen.

Die Zeitstempelwerte in Textdateien müssen das Format yyyy-mm-dd HH:mm:ss.SSSSSS haben, wie der folgende Zeitstempelwert zeigt: 2017-05-01 11:30:59.000000.

Die Länge einer VARCHAR-Spalte wird in Bytes definiert, nicht in Zeichen. Eine VARCHAR(12)-Spalte kann z. B. 12 Einzelbyte-Zeichen oder 6 Zeichen mit einer Länge von je 2 Bytes enthalten. Wenn Sie eine externe Tabelle abfragen, werden die Ergebnisse gekürzt, damit sie der definierten Spaltengröße entsprechen, ohne dass ein Fehler zurückgegeben wird. Weitere Informationen finden Sie unter Speicherung und Bereiche.

Um eine optimale Leistung zu erzielen, empfehlen wir, die kleinste Spaltengröße anzugeben, die ihren Daten entspricht. Verwenden Sie die Funktion OCTET_LENGTH, um die maximale Größe in Bytes für Werte in einer Spalte zu suchen. Das folgende Beispiel gibt die maximale Größe von Werten in der Spalte „E-Mail“ zurück.

select max(octet_length(email)) from users; max --- 62
PARTITIONED BY (col_name data_type [, … ] )

Eine Klausel, die eine partitionierte Tabelle mit einer oder mehreren Partitionsspalten festlegt. Für jede angegebene Kombination wird ein eigenes Datenverzeichnis verwendet. Dies kann die Abfrageleistung in einigen Fällen verbessern. In den Tabellendaten selbst sind keine partitionierten Spalten vorhanden. Wenn Sie einen Wert für col_name verwenden, der mit einer Tabellenspalte identisch ist, erhalten Sie einen Fehler.

Nachdem Sie eine partitionierte Tabelle erstellt haben, ändern Sie die Tabelle mit der Anweisung ALTER TABLE ... ADD PARTITION, um neue Partitionen im externen Katalog zu registrieren. Wenn Sie eine Partition hinzufügen, definieren Sie den Speicherort des Unterordners auf Amazon S3, der die Partitionsdaten enthält.

Wenn die Tabelle spectrum.lineitem_part beispielsweise mit PARTITIONED BY (l_shipdate date) definiert ist, führen Sie den folgenden ALTER TABLE-Befehl aus, um eine Partition hinzuzufügen.

ALTER TABLE spectrum.lineitem_part ADD PARTITION (l_shipdate='1992-01-29') LOCATION 's3://spectrum-public/lineitem_partition/l_shipdate=1992-01-29';

Wenn Sie CREATE EXTERNAL TABLE AS verwenden, müssen Sie ALTER TABLE nicht ausführen... ADD PARTITION . Amazon Redshift registriert neue Partitionen automatisch im externen Katalog. Amazon Redshift schreibt auch automatisch die entsprechenden Daten in Partitionen in Amazon S3, basierend auf dem Partitionsschlüssel oder den Schlüsseln, die in der Tabelle definiert sind.

Um Partitionen anzuzeigen, führen Sie eine Abfrage für die Systemansicht SVV_EXTERNAL_PARTITIONS aus.

Anmerkung

Für den Befehl CREATE EXTERNAL TABLE AS müssen Sie den Datentyp der Partitionsspalte nicht angeben, da diese Spalte von der Abfrage abgeleitet wird.

ROW FORMAT DELIMITED rowformat

Eine Klausel, die das Format der zugrundeliegenden Daten angibt. Die möglichen Werte für rowformat sind wie folgt:

  • LINES TERMINATED BY 'Trennzeichen'

  • FIELDS TERMINATED BY 'Trennzeichen'

Geben Sie ein ASCII-Zeichen für 'delimiter' an. Sie können nicht druckbare ASCII-Zeichen mithilfe von Oktal-Code im Format '\ddd' festlegen, wobei d eine Oktalziffer (0–7) bis ‘\177’ ist. Im folgenden Beispiel wird das BEL (Bell)-Zeichen anhand Oktalziffern angegeben.

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\007'

Wenn ROW FORMAT ausgelassen wird, lautet das Standardformat DELIMITED FIELDS TERMINATED BY '\A' (Anfang des Headings) und LINES TERMINATED BY '\n' (Zeilenumbruch).

ROW FORMAT SERDE 'serde_name'
[WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

Eine Klausel, die das SERDE-Format der zugrundeliegenden Daten angibt.

'serde_name'

Der Name von SerDe. Sie können die folgenden Formate angeben:

  • org.apache.hadoop.hive.serde2.RegexSerDe

  • com.amazonaws.glue.serde.GrokSerDe

  • org.apache.hadoop.hive.serde2.OpenCSVSerde

    Dieser Parameter unterstützt die folgende SerDe-Eigenschaft für OpenSvSerde:

    'wholeFile' = 'true'

    Setzen Sie die Eigenschaft wholeFile auf true, um Neue-Zeile-Zeichen (\n) innerhalb von Zeichenfolgen in Anführungszeichen für OpenCSV-Anforderungen richtig zu parsen.

  • org.openx.data.jsonserde.JsonSerDe

    • Das JSON SERDE-Format unterstützt auch ION-Dateien.

    • JSON muss wohl geformt sein.

    • Zeitstempel in Ion und JSON müssen sich im Format ISO8601 befinden.

    • Dieser Parameter unterstützt die folgende SerDe-Eigenschaft für JsonSerDe:

      'strip.outer.array'='true'

      Verarbeitet Ion/JSON-Dateien mit einem sehr großen Array in äußeren Klammern ( [ … ] ) so, als ob mehrere JSON-Datensätze innerhalb des Arrays enthalten sind.

  • com.amazon.ionhiveserde.IonHiveSerDe

    Das Amazon ION-Format bietet neben Datentypen Text- und Binärformate. Bei einer externen Tabelle, die auf Daten im ION-Format verweist, weisen Sie jede Spalte in der externen Tabelle dem entsprechenden Element in den ION-Formatdaten zu. Weitere Informationen finden Sie unter Amazon Ion. Sie müssen gegebenenfalls auch die Ein- und Ausgabeformate angeben.

WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

Optional können Sie Namen und Werte der Eigenschaften getrennt durch Kommas angeben.

Wenn ROW FORMAT ausgelassen wird, lautet das Standardformat DELIMITED FIELDS TERMINATED BY '\A' (Anfang des Headings) und LINES TERMINATED BY '\n' (Zeilenumbruch).

STORED AS Dateiformat

Das Dateiformat für Datendateien.

Gültige Formate sind folgende:

  • PARQUET

  • RCFILE (nur für Daten, die ColumnarSerDe und nicht LazyBinaryColumnarSerDe verwenden)

  • SEQUENCEFILE

  • TEXTFILE

  • ORC

  • AVRO

  • INPUTFORMAT 'input_format_classname' OUTPUTFORMAT 'output_format_classname'

Der Befehl CREATE EXTERNAL TABLE AS unterstützt nur zwei Dateiformate: TEXTFILE und PARQUET.

Geben Sie für INPUTFORMAT und OUTPUTFORMAT einen Klassennamen wie im folgenden Beispiel ein.

'org.apache.hadoop.mapred.TextInputFormat'
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file'}

Der Pfad zum -Bucket oder Amazon-S3-Ordner enthält die Datendateien oder eine Manifestdatei, die eine Liste der Amazon-S3-Objektpfade enthält. Die Buckets müssen sich in derselben AWS-Region wie der Amazon-Redshift-Cluster befinden. Eine Liste der unterstützten AWS-Regionen finden Sie unter Überlegungen zu Amazon Redshift Spectrum.

Wenn im Pfad ein Bucket oder Ordner wie 's3://mybucket/custdata/' angegeben wird, scannt Redshift Spectrum die Dateien im angegebenen Bucket oder Ordner und in allen Unterordnern. Redshift Spectrum ignoriert verborgene Dateien sowie Dateien, die mit einem Punkt oder Unterstrich beginnen.

Wenn der Pfad eine Manifestdatei angibt, muss das 's3://bucket/manifest_file'-Argument explizit auf eine einzelne Datei verweisen, zum Beispiel 's3://mybucket/manifest.txt'. Es darf kein Schlüsselpräfix referenzieren.

Das Manifest ist eine Textdatei im JSON-Format, die die URL jeder Datei, die aus Amazon S3 geladen werden soll, sowie die Größe der jeweiligen Datei in Bytes auflistet. Die URL enthält den Bucket-Namen und den vollständigen Objektpfad für die Datei. Die im Manifest angegebenen Dateien können sich in verschiedenen Buckets befinden. Alle Buckets müssen sich jedoch in derselben AWS-Region wie der Amazon-Redshift-Cluster befinden. Wenn eine Datei zweimal aufgelistet wird, wird die Datei zweimal geladen. Im folgenden Beispiel wird der JSON-Code für ein Manifest gezeigt, das drei Dateien lädt.

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

Sie können die Aufnahme einer bestimmten Datei obligatorisch machen. Fügen Sie dazu eine mandatory-Option auf Dateiebene in das Manifest ein. Wenn Sie eine externe Tabelle mit einer obligatorischen Datei abfragen, die fehlt, schlägt die SELECT-Anweisung fehl. Stellen Sie sicher, dass alle in der Definition der externen Tabelle enthaltenen Dateien vorhanden sind. Wenn sie nicht alle vorhanden sind, wird ein Fehler angezeigt, in dem die erste obligatorische Datei angezeigt wird, die nicht gefunden wird. Das folgende Beispiel zeigt den JSON für ein Manifest, wobei die mandatory-Option auf true eingestellt ist.

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "mandatory":true, "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "mandatory":false, "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

Um auf Dateien zu verweisen, die mit UNLOAD erstellt wurden, können Sie das mit UNLOAD mit dem MANIFEST-Parameter erstellte Manifest verwenden. Die Manifestdatei ist mit einer Manifestdatei für COPY aus Amazon S3 kompatibel, es werden jedoch andere Schlüssel verwendet. Nicht verwendete Schlüssel werden ignoriert.

TABLE PROPERTIES ( 'property_name'='property_value' [, ...] )

Eine Klausel, die die Tabellendefinition für Tabelleneigenschaften festlegt.

Anmerkung

Bei Tabelleneigenschaften muss die Groß-/Kleinschreibung beachtet werden.

'compression_type'='value'

Eine Eigenschaft, die den Komprimierungstyp festlegt, der verwendet wird, wenn der Dateiname keine Erweiterung enthält. Wenn Sie diese Eigenschaft festlegen und eine Dateierweiterung vorhanden ist, wird die Erweiterung ignoriert und der von der Eigenschaft festgelegte Wert verwendet. Gültige Werte für den Komprimierungstyp sind folgende:

  • bzip2

  • gzip

  • Keine

  • snappy

'numRows'='row_count'

Eine Eigenschaft, die den Wert numRows für die Tabellendefinition festlegt. Um die Statistiken einer externen Tabelle explizit zu aktualisieren, legen Sie mit der Eigenschaft numRows die Größe der Tabelle fest. Amazon Redshift analysiert keine externen Tabellen, um die Tabellenstatistiken zu generieren, die der Abfrageoptimierer verwendet, um einen Abfrageplan zu erstellen. Wenn für eine externe Tabelle keine Tabellenstatistiken festgelegt sind, generiert Amazon Redshift einen Abfrageausführungsplan basierend auf der Annahme, dass externe Tabellen die größeren Tabellen und lokale Tabellen die kleineren Tabellen sind.

'skip.header.line.count'='line_count'

Eine Eigenschaft, die die Anzahl der Reihen festgelegt, die am Anfang jeder Quelldatei übersprungen wird.

'serialization.null.format'=' '

Eine Eigenschaft, die Spectrum festlegt, muss einen NULL-Wert zurückgeben, wenn eine exakte Übereinstimmung mit dem in einem Feld angegebenen Text besteht.

'orc.schema.resolution'='mapping_type'

Eine Eigenschaft, die den Spaltenzuweisungstyp für Tabellen einrichtet, die das ORC-Datenformat verwenden. Für andere Datenformate wird diese Eigenschaft ignoriert.

Gültige Werte für den Spaltenzuweisungstyp sind folgende:

  • Name

  • position

Wenn die Eigenschaft orc.schema.resolution weggelassen wird, werden die Spalten standardmäßig nach Name zugewiesen. Wenn orc.schema.resolution auf einen anderen Wert als 'name' oder 'position' gesetzt wird, werden die Spalten nach Position zugewiesen. Weitere Informationen zur Spaltenzuweisung finden Sie unter . Zuweisung externer Tabellenspalten zu ORC-Spalten

Anmerkung

Der COPY-Befehl weist ORC-Datendateien nur nach Position zu. Die Tabelleneigenschaft orc.schema.resolution hat keine Auswirkungen auf das Verhalten des COPY-Befehls.

'write.parallel'='on / off’

Eine Eigenschaft, die festlegt, ob CREATE EXTERNAL TABLE AS Daten parallel schreiben soll. Standardmäßig schreibt CREATE EXTERNAL TABLE AS die Daten parallel in mehrere Dateien, je nach der Anzahl der Slices in dem Cluster. Die Standardoption ist eingeschaltet. Wenn 'write.parallel' auf „off“ gesetzt ist, schreibt CREATE EXTERNAL TABLE AS eine oder mehrere Datendateien seriell in Amazon S3. Diese Tabelleneigenschaft gilt auch für alle nachfolgenden INSERT-Anweisungen in derselben externen Tabelle.

‘write.maxfilesize.mb’=‘size’

Eine Eigenschaft, mit der die maximale Größe (in MB) jeder Datei festgelegt wird, die von CREATE EXTERNAL TABLE AS in Amazon S3 geschrieben wurde. Die Größe muss eine gültige Ganzzahl zwischen 5 und 6.200 sein. Die standardmäßige maximale Dateigröße beträgt 6.200 MB. Diese Tabelleneigenschaft gilt auch für alle nachfolgenden INSERT-Anweisungen in derselben externen Tabelle.

‘write.kms.key.id’=‘value

Sie können einen AWS Key Management Service-Schlüssel angeben, um die serverseitige Verschlüsselung (SSE) für Amazon-S3-Objekte zu aktivieren. Dies ist bei folgenden Werten möglich:

  • auto, um den AWS KMS-Standardschlüssel, der im Amazon S3 Bucket gespeichert ist, zu verwenden.

  • kms-key, den Sie zur Verschlüsselung von Daten angeben.

select-statement

Eine Anweisung, die mindestens eine Zeile in die externe Tabelle einfügt, indem eine beliebige Abfrage definiert wird. Alle von der Abfrage erzeugten Zeilen werden auf der Grundlage der Tabellendefinition entweder in Text- oder in Parquet-Format in Amazon S3 geschrieben.

Nutzungshinweise

Sie können die Details für Amazon-Redshift-Spectrum-Tabellen nicht mit den gleichen Ressourcen anzeigen, die Sie für Amazon-Redshift-Standardtabellen verwenden, wie PG_TABLE_DEF, STV_TBL_PERM, PG_CLASS oder information_schema. Wenn Ihr Business Intelligence- oder Analyse-Tool externe Redshift Spectrum-Tabellen nicht erkennt, konfigurieren Sie Ihre Anwendung für die Ausführung von Abfragen für SVV_EXTERNAL_TABLES und SVV_EXTERNAL_COLUMNS.

CREATE EXTERNAL TABLE AS

In einigen Fällen können Sie den Befehl CREATE EXTERNAL TABLE AS für einen AWS Glue-Datenkatalog, einen externen AWS Lake Formation-Katalog oder einen Apache Hive Metastore ausführen. In solchen Fällen verwenden Sie eine AWS Identity and Access Management (IAM)-Rolle, um das externe Schema zu erstellen. Diese IAM-Rolle muss sowohl Lese- als auch Schreibberechtigungen für Amazon S3 haben.

Wenn Sie einen Lake-Formation-Katalog verwenden, muss die IAM-Rolle über die Berechtigung verfügen, eine Tabelle im Katalog zu erstellen. In diesem Fall muss sie auch über die Data Lake-Speicherort-Berechtigung für den Amazon-S3-Zielpfad verfügen. Diese IAM-Rolle wird zum Besitzer der neuen AWS Lake Formation-Tabelle.

Um sicherzustellen, dass Dateinamen eindeutig sind, verwendet Amazon Redshift das folgende Format für den Namen jeder Datei, die standardmäßig in Amazon S3 hochgeladen wurde.

<date>_<time>_<microseconds>_<query_id>_<slice-number>_part_<part-number>.<format>.

Ein Beispiel ist 20200303_004509_810669_1007_0001_part_00.parquet.

Berücksichtigen Sie beim Ausführen des Befehls CREATE EXTERNAL TABLE AS Folgendes:

  • Der Amazon-S3-Speicherort muss leer sein.

  • Amazon Redshift unterstützt nur PARQUET- und TEXTFILE-Formate, wenn die STORED AS-Klausel verwendet wird.

  • Sie müssen keine Spaltendefinitionsliste definieren. Spaltennamen und Spaltendatentypen der neuen externen Tabelle werden direkt aus der SELECT-Abfrage abgeleitet.

  • Sie müssen den Datentyp der Partitionsspalte in der PARTITIONED BY-Klausel nicht definieren. Wenn Sie einen Partitionsschlüssel angeben, muss der Name dieser Spalte im SELECT-Abfrageergebnis vorhanden sein. Wenn mehrere Partitionsspalten vorhanden sind, spielt ihre Reihenfolge in der SELECT-Abfrage keine Rolle. Amazon Redshift verwendet die in der PARTITIONED BY-Klausel definierte Reihenfolge, um die externe Tabelle zu erstellen.

  • Amazon Redshift partitioniert Ausgabedateien automatisch basierend auf den Partitionsschlüsselwerten in Partitionsordnern. Amazon Redshift entfernt standardmäßig Partitionsspalten aus den Ausgabedateien.

  • Die LINES TERMINATED BY 'delimiter'-Klausel wird nicht unterstützt.

  • Die Klausel ROW FORMAT SERDE 'serde_name' wird nicht unterstützt.

  • Die Verwendung von Manifestdateien wird nicht unterstützt. Daher können Sie die LOCATION-Klausel in Amazon S3 nicht für eine Manifestdatei definieren.

  • Amazon Redshift aktualisiert die Tabelleneigenschaft 'numRows' am Ende des Befehls automatisch.

  • Die Tabelleneigenschaft 'compression_type' akzeptiert nur 'none' oder 'snappy' als PARQUET-Dateiformat.

  • Amazon Redshift lässt die LIMIT-Klausel in der äußeren SELECT-Abfrage nicht zu. Stattdessen können Sie eine verschachtelte LIMIT-Klausel verwenden.

  • Sie können STL_UNLOAD_LOG verwenden, um die Dateien zu verfolgen, die von jedem CREATE EXTERNAL TABLE AS-Vorgang in Amazon S3 geschrieben werden.

Berechtigungen, externe Tabellen zu erstellen und abzufragen

Um externe Tabellen zu erstellen, stellen Sie sicher, dass Sie der Besitzer des externen Schemas oder ein Superuser sind. Um das Eigentum an einem externen Schema zu übertragen, verwenden Sie ALTER SCHEMA. Das folgende Beispiel ändert den Eigentümer des Schemas spectrum_schema in newowner.

alter schema spectrum_schema owner to newowner;

Um eine Redshift Spectrum-Abfrage auszuführen, benötigen Sie die folgenden Berechtigungen:

  • Nutzungsberechtigung für das Schema

  • Berechtigung, temporäre Tabellen in der aktuellen Datenbank zu erstellen

Das folgende Beispiel erteilt der Benutzergruppe spectrum_schema Nutzungsberechtigungen für das Schema spectrumusers.

grant usage on schema spectrum_schema to group spectrumusers;

Das folgende Beispiel erteilt der Benutzergruppe spectrumdb temporäre Berechtigungen für die Datenbank spectrumusers.

grant temp on database spectrumdb to group spectrumusers;

Pseudospalten

Standardmäßig erstellt Amazon Redshift externe Tabellen mit den Pseudospalten $path und $size. Wählen Sie diese Spalten, um den Pfad zu den Datendateien auf dem Amazon S3 und die Größe der Datendateien für jede Zeile anzuzeigen, die von einer Abfrage zurückgegeben wird. Die Spaltennamen $path und $size müssen mit doppelten Anführungszeichen abgetrennt sein. Eine SELECT *-Klausel gibt die Pseudospalten nicht zurück. Sie müssen die Spaltennamen $path und $size explizit in Ihre Abfrage einfügen, wie im folgenden Beispiel gezeigt.

select "$path", "$size" from spectrum.sales_part where saledate = '2008-12-01';

Sie können die Erstellung von Pseudospalten für eine Sitzung deaktivieren, indem Sie den spectrum_enable_pseudo_columns-Konfigurationsparameter auf false setzen.

Wichtig

Bei Auswahl von $size oder $path fallen Gebühren an, weil Redshift Spectrum die Datendateien in Amazon S3 scannt, um die Größe des Ergebnissatzes zu bestimmen. Weitere Informationen finden Sie unter Amazon-Redshift-Preise.

Examples

Im folgenden Beispiel wird eine Tabelle namens SALES im externen Amazon-Redshift-Schema namens erstellt spectrum. Die Daten befinden sich in Textdateien, die Tabulatoren als Trennzeichen verwenden. Die Klausel TABLE PROPERTIES legt die Eigenschaft numRows auf 170.000 Zeilen fest.

create external table spectrum.sales( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, saledate date, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) row format delimited fields terminated by '\t' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales/' table properties ('numRows'='170000');

Im folgenden Beispiel wird eine Tabelle erstellt, die JsonSerDe verwendet, um auf Daten im JSON-Format zu verweisen.

create external table spectrum.cloudtrail_json ( event_version int, event_id bigint, event_time timestamp, event_type varchar(10), awsregion varchar(20), event_name varchar(max), event_source varchar(max), requesttime timestamp, useragent varchar(max), recipientaccountid bigint) row format serde 'org.openx.data.jsonserde.JsonSerDe' with serdeproperties ( 'dots.in.keys' = 'true', 'mapping.requesttime' = 'requesttimestamp' ) location 's3://mybucket/json/cloudtrail';

Im folgenden CREATE EXTERNAL TABLE AS-Beispiel wird eine nicht partitionierte externe Tabelle erstellt.- Dann wird das Ergebnis der SELECT-Abfrage als Apache Parquet an die Amazon-S3-Zielposition geschrieben.

CREATE EXTERNAL TABLE spectrum.lineitem STORED AS parquet LOCATION 'S3://mybucket/cetas/lineitem/' AS SELECT * FROM local_lineitem;

Im folgenden Beispiel wird eine partitionierte externe Tabelle erstellt und die Partitionsspalten werden in der SELECT-Abfrage eingeschlossen.

CREATE EXTERNAL TABLE spectrum.partitioned_lineitem PARTITIONED BY (l_shipdate, l_shipmode) STORED AS parquet LOCATION 'S3://mybucket/cetas/partitioned_lineitem/' AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;

Eine Liste der vorhandenen Datenbanken im externen Datenkatalog erhalten Sie durch Abfragen der SVV_EXTERNAL_DATABASES-Systemansicht.

select eskind,databasename,esoptions from svv_external_databases order by databasename;
eskind | databasename | esoptions -------+--------------+---------------------------------------------------------------------------------- 1 | default | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | sampledb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"} 1 | spectrumdb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}

Um Details zu externen Tabellen anzuzeigen, führen Sie eine Abfrage für die Systemansichten SVV_EXTERNAL_TABLES und SVV_EXTERNAL_COLUMNS aus.

Im folgenden Beispiel wird eine Abfrage für die Ansicht SVV_EXTERNAL_TABLES ausgeführt.

select schemaname, tablename, location from svv_external_tables;
schemaname | tablename | location -----------+----------------------+-------------------------------------------------------- spectrum | sales | s3://awssampledbuswest2/tickit/spectrum/sales spectrum | sales_part | s3://awssampledbuswest2/tickit/spectrum/sales_partition

Im folgenden Beispiel wird eine Abfrage für die Ansicht SVV_EXTERNAL_COLUMNS ausgeführt.

select * from svv_external_columns where schemaname like 'spectrum%' and tablename ='sales';
schemaname | tablename | columnname | external_type | columnnum | part_key -----------+-----------+------------+---------------+-----------+--------- spectrum | sales | salesid | int | 1 | 0 spectrum | sales | listid | int | 2 | 0 spectrum | sales | sellerid | int | 3 | 0 spectrum | sales | buyerid | int | 4 | 0 spectrum | sales | eventid | int | 5 | 0 spectrum | sales | saledate | date | 6 | 0 spectrum | sales | qtysold | smallint | 7 | 0 spectrum | sales | pricepaid | decimal(8,2) | 8 | 0 spectrum | sales | commission | decimal(8,2) | 9 | 0 spectrum | sales | saletime | timestamp | 10 | 0

Verwenden Sie die folgende Abfrage zum Anzeigen von Tabellenpartitionen.

select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename | values | location -----------+------------+----------------+------------------------------------------------------------------------- spectrum | sales_part | ["2008-01-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01 spectrum | sales_part | ["2008-02-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02 spectrum | sales_part | ["2008-03-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03 spectrum | sales_part | ["2008-04-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04 spectrum | sales_part | ["2008-05-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05 spectrum | sales_part | ["2008-06-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06 spectrum | sales_part | ["2008-07-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07 spectrum | sales_part | ["2008-08-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08 spectrum | sales_part | ["2008-09-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09 spectrum | sales_part | ["2008-10-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10 spectrum | sales_part | ["2008-11-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11 spectrum | sales_part | ["2008-12-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12

Das folgende Beispiel gibt die Gesamtgröße der entsprechenden Datendateien für eine externe Tabelle zurück.

select distinct "$path", "$size" from spectrum.sales_part; $path | $size ---------------------------------------+------- s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/ | 1616 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444

Beispiele für Partitionierungen

Führen Sie zur Erstellung einer nach Datum partitionierten Tabelle den folgenden Befehl aus.

create external table spectrum.sales_part( salesid integer, listid integer, sellerid integer, buyerid integer, eventid integer, dateid smallint, qtysold smallint, pricepaid decimal(8,2), commission decimal(8,2), saletime timestamp) partitioned by (saledate date) row format delimited fields terminated by '|' stored as textfile location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/' table properties ('numRows'='170000');

Führen Sie zum Hinzufügen der Partitionen die folgenden ALTER TABLE-Befehle aus.

alter table spectrum.sales_part add if not exists partition (saledate='2008-01-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-02-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-03-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-04-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-05-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-06-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-07-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-08-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-09-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-10-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-11-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11/'; alter table spectrum.sales_part add if not exists partition (saledate='2008-12-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12/';

Führen Sie die folgende Abfrage aus, um Daten aus der partitionierten Tabelle auszuwählen.

select top 10 spectrum.sales_part.eventid, sum(spectrum.sales_part.pricepaid) from spectrum.sales_part, event where spectrum.sales_part.eventid = event.eventid and spectrum.sales_part.pricepaid > 30 and saledate = '2008-12-01' group by spectrum.sales_part.eventid order by 2 desc;
eventid | sum --------+--------- 914 | 36173.00 5478 | 27303.00 5061 | 26383.00 4406 | 26252.00 5324 | 24015.00 1829 | 23911.00 3601 | 23616.00 3665 | 23214.00 6069 | 22869.00 5638 | 22551.00

Um externe Tabellenpartitionen anzuzeigen, führen Sie eine Abfrage für die Systemansicht SVV_EXTERNAL_PARTITIONS aus.

select schemaname, tablename, values, location from svv_external_partitions where tablename = 'sales_part';
schemaname | tablename | values | location -----------+------------+----------------+-------------------------------------------------- spectrum | sales_part | ["2008-01-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01 spectrum | sales_part | ["2008-02-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02 spectrum | sales_part | ["2008-03-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03 spectrum | sales_part | ["2008-04-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-04 spectrum | sales_part | ["2008-05-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-05 spectrum | sales_part | ["2008-06-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-06 spectrum | sales_part | ["2008-07-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-07 spectrum | sales_part | ["2008-08-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-08 spectrum | sales_part | ["2008-09-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-09 spectrum | sales_part | ["2008-10-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-10 spectrum | sales_part | ["2008-11-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-11 spectrum | sales_part | ["2008-12-01"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-12

Beispiele für Zeilenformate

Nachfolgend sehen Sie ein Beispiel der Angabe von ROW FORMAT SERDE-Parametern für Datendateien, die im AVRO-Format gespeichert sind.

create external table spectrum.sales(salesid int, listid int, sellerid int, buyerid int, eventid int, dateid int, qtysold int, pricepaid decimal(8,2), comment VARCHAR(255)) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe' WITH SERDEPROPERTIES ('avro.schema.literal'='{\"namespace\": \"dory.sample\",\"name\": \"dory_avro\",\"type\": \"record\", \"fields\": [{\"name\":\"salesid\", \"type\":\"int\"}, {\"name\":\"listid\", \"type\":\"int\"}, {\"name\":\"sellerid\", \"type\":\"int\"}, {\"name\":\"buyerid\", \"type\":\"int\"}, {\"name\":\"eventid\",\"type\":\"int\"}, {\"name\":\"dateid\",\"type\":\"int\"}, {\"name\":\"qtysold\",\"type\":\"int\"}, {\"name\":\"pricepaid\", \"type\": {\"type\": \"bytes\", \"logicalType\": \"decimal\", \"precision\": 8, \"scale\": 2}}, {\"name\":\"comment\",\"type\":\"string\"}]}') STORED AS AVRO location 's3://mybucket/avro/sales' ;

Nachfolgend sehen Sie ein Beispiel der Angabe von ROW FORMAT SERDE-Parametern mit RegEx.

create external table spectrum.types( cbigint bigint, cbigint_null bigint, cint int, cint_null int) row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe' with serdeproperties ('input.regex'='([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)') stored as textfile location 's3://mybucket/regex/types';

Nachfolgend sehen Sie ein Beispiel der Angabe von ROW FORMAT SERDE-Parametern mit Grok.

create external table spectrum.grok_log( timestamp varchar(255), pid varchar(255), loglevel varchar(255), progname varchar(255), message varchar(255)) row format serde 'com.amazonaws.glue.serde.GrokSerDe' with serdeproperties ('input.format'='[DFEWI], \\[%{TIMESTAMP_ISO8601:timestamp} #%{POSINT:pid:int}\\] *(?<loglevel>:DEBUG|FATAL|ERROR|WARN|INFO) -- +%{DATA:progname}: %{GREEDYDATA:message}') stored as textfile location 's3://mybucket/grok/logs';

Die folgende Abbildung zeigt ein Beispiel für ein Amazon-S3-Server-Zugriffsprotokoll in einem S3 Bucket. Sie können Redshift Spectrum verwenden, um Amazon-S3-Zugriffsprotokolle abzufragen.

CREATE EXTERNAL TABLE spectrum.mybucket_s3_logs( bucketowner varchar(255), bucket varchar(255), requestdatetime varchar(2000), remoteip varchar(255), requester varchar(255), requested varchar(255), operation varchar(255), key varchar(255), requesturi_operation varchar(255), requesturi_key varchar(255), requesturi_httpprotoversion varchar(255), httpstatus varchar(255), errorcode varchar(255), bytessent bigint, objectsize bigint, totaltime varchar(255), turnaroundtime varchar(255), referrer varchar(255), useragent varchar(255), versionid varchar(255) ) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*)\\s*([^ ]*)\\s*([^ ]*)\" (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*).*$') LOCATION 's3://mybucket/s3logs’;

Nachfolgend sehen Sie ein Beispiel der Angabe von ROW FORMAT SERDE-Parametern für Daten im ION-Format.

CREATE EXTERNAL TABLE tbl_name (columns) ROW FORMAT SERDE 'com.amazon.ionhiveserde.IonHiveSerDe' STORED AS INPUTFORMAT 'com.amazon.ionhiveserde.formats.IonInputFormat' OUTPUTFORMAT 'com.amazon.ionhiveserde.formats.IonOutputFormat' LOCATION 's3://s3-bucket/prefix'