Accesso di Aurora a Amazon S3;Concessione dei privilegi per caricare dati Specifica del percorso (URI) in un bucket Amazon S3 LOAD DATA FROM S3 LOAD XML FROM S3

Caricamento dei dati in un cluster DB Amazon Aurora MySQL da file di testo in un bucket Amazon S3

Puoi usare l'istruzione LOAD DATA FROM S3 o LOAD XML FROM S3 per caricare i dati dai file memorizzati in un bucket Amazon S3. In Aurora MySQL, i file vengono prima archiviati sul disco locale e quindi importati nel database. Al termine delle importazioni nel database, i file locali vengono eliminati.

Nota

Il caricamento dei dati in una tabella dai file di testo non è supportato in Aurora Serverless v1. È supportata per Aurora Serverless v2.

Indice

Accesso di Aurora a Amazon S3;

Prima di poter caricare i dati da un bucket Amazon S3, è necessario innanzitutto concedere l'autorizzazione al cluster DB Aurora MySQL per accedere ad Amazon S3.

Per concedere ad Aurora MySQL l'accesso ad Amazon S3

Crea una policy AWS Identity and Access Management (IAM) che fornisca le autorizzazioni per bucket e oggetti che consentono al cluster Aurora MySQL DB di accedere ad Amazon S3. Per istruzioni, consulta Creazione di una policy IAM per l'accesso alle risorse Amazon S3.

Nota
In Aurora MySQL 3.05 e versioni successive, è possibile caricare oggetti crittografati utilizzando AWS KMS keys gestite dal cliente. A tale scopo, includi l'autorizzazione kms:Decrypt nella policy IAM. Per ulteriori informazioni, consulta Creazione di una policy IAM per l'accesso alle risorseAWS KMS.
Non è necessaria questa autorizzazione per caricare oggetti crittografati utilizzando Chiavi gestite da AWS o chiavi gestite da Amazon S3 (SSE-S3).
Creare un ruolo IAM e collegare la policy IAM creata in Creazione di una policy IAM per l'accesso alle risorse Amazon S3 al nuovo ruolo IAM. Per istruzioni, consulta Creazione di un ruolo IAM per consentire ad Amazon Aurora di accedere ai servizi AWS.
Assicurati che il cluster DB stia utilizzando un gruppo di parametri del cluster DB personalizzato.

Per ulteriori informazioni sulla creazione di un gruppo di parametri del cluster DB, consulta Creazione di un gruppo di parametri del cluster database.
Per Aurora MySQL versione 2, imposta il parametro del cluster di database aurora_load_from_s3_role o aws_default_s3_role sul nome della risorsa Amazon (ARN) del nuovo ruolo IAM. Se un ruolo IAM non è specificato per aurora_load_from_s3_role, Aurora utilizza il ruolo IAM specificato in aws_default_s3_role.

Per Aurora MySQL versione 3, utilizza aws_default_s3_role.

Se il cluster fa parte di un database globale Aurora, imposta questo parametro per ogni cluster Aurora nel database globale. Sebbene solo il cluster primario in un database globale Aurora può caricare i dati, un altro cluster potrebbe essere promosso dal meccanismo di failover e diventare il cluster primario.

Per ulteriori informazioni sui parametri del cluster DB, vedi Parametri dell'istanza database e del cluster database di Amazon Aurora.
Per consentire l'accesso ad Aurora MySQL agli utenti del database in un cluster DB Amazon S3, è necessario associare il ruolo creato in Creazione di un ruolo IAM per consentire ad Amazon Aurora di accedere ai servizi AWS al cluster DB. Per un database globale Aurora, associa il ruolo a ogni cluster Aurora nel database globale. Per informazioni su come associare un ruolo IAM a un cluster DB, vedi Associazione di un ruolo IAM a un cluster DB Amazon Aurora MySQL.
Configura il cluster DB Aurora MySQL per consentire le connessioni in uscita ad Amazon S3. Per istruzioni, consulta Abilitazione delle comunicazioni di rete da Amazon Aurora MySQL ad altri servizi AWS.

Se il cluster DB non è accessibile pubblicamente e si trova in una sottorete pubblica VPC significa che è privato. Puoi creare un endpoint del gateway S3 per accedere al bucket S3. Per ulteriori informazioni, consulta Endpoint gateway per Amazon S3.

Per un database globale Aurora, abilita le connessioni in uscita per ogni cluster Aurora nel database globale.

Concessione dei privilegi per caricare dati in Amazon Aurora MySQL

L'utente del database che invia l'istruzione LOAD DATA FROM S3 o LOAD XML FROM S3 deve avere un ruolo o privilegio specifico per rilasciare una delle due istruzioni. In Aurora MySQL versione 3, concedi il ruolo AWS_LOAD_S3_ACCESS. In Aurora MySQL versione 2, concedi il privilegio LOAD FROM S3. All'utente amministrativo per un cluster di database è concesso il ruolo o il privilegio appropriato per impostazione predefinita. Puoi concedere il privilegio a un altro utente usando le seguenti istruzioni.

Utilizzare la seguente istruzione per Aurora MySQL versione 3:


GRANT AWS_LOAD_S3_ACCESS TO 'user'@'domain-or-ip-address'

Suggerimento

Quando utilizzi la tecnica basata sul ruolo in Aurora MySQL versione 3, puoi anche attivare il ruolo utilizzando l'istruzione SET ROLE role_name o SET ROLE ALL. Se non si ha familiarità con il sistema dei ruoli MySQL 8.0, è possibile ottenere ulteriori informazioni in Privilegio basato sui ruoli. Per maggiori dettagli, consulta Using roles nel MySQL Reference Manual.

Questo vale solo per la sessione attiva corrente. Quando ci si riconnette, è necessario eseguire nuovamente l'SET ROLEistruzione per concedere i privilegi. Per ulteriori informazioni, consulta Istruzione SET ROLE nel Manuale di riferimento di MySQL.

Puoi utilizzare il parametro activate_all_roles_on_login del cluster di database per attivare automaticamente tutti i ruoli quando un utente si connette a un'istanza database. Quando questo parametro è impostato, in genere non è necessario chiamare l'SET ROLEistruzione in modo esplicito per attivare un ruolo. Per ulteriori informazioni, consulta activate_all_roles_on_login nel Manuale di riferimento di MySQL.

Tuttavia, è necessario chiamare SET ROLE ALL esplicitamente all'inizio di una stored procedure per attivare il ruolo, quando la stored procedure viene chiamata da un altro utente.

Utilizza la seguente istruzione per Aurora MySQL versione 2:


GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

Il ruolo AWS_LOAD_S3_ACCESS e il privilegio LOAD FROM S3 sono specifici di Amazon Aurora e non sono disponibili per i database MySQL esterni o le istanze database RDS per MySQL. Se è stata impostata la replica tra un cluster di database Aurora come master di replica e un database MySQL come client di replica, l'istruzione GRANT causa l'arresto della replica con un errore. Puoi ignorare l'errore in modo sicuro per riprendere la replica. Per ignorare l'errore su un'istanza RDS per MySQL, utilizza la procedura mysql_rds_skip_repl_error. Per ignorare l'errore su un database MySQL esterno, usa la variabile di sistema slave_skip_errors (Aurora MySQL versione 2) o replica_skip_errors (Aurora MySQL versione 3).

Nota

L'utente del database deve disporre INSERT dei privilegi per il database in cui carica i dati.

Specifica del percorso (URI) in un bucket Amazon S3

La sintassi per specificare il percorso (URI) dei file archiviati su un bucket Amazon S3 è la seguente.


s3-region://DOC-EXAMPLE-BUCKET/file-name-or-prefix

Il percorso include i seguenti valori:

region(opzionale): la AWS regione che contiene il bucket Amazon S3 da cui caricare. Questo valore è facoltativo. Se non specifichi un valore per region, Aurora carica il file da Amazon S3 nella stessa regione del cluster DB.
bucket-name – Il nome del bucket Amazon S3 che contiene i dati da caricare. Sono supportati i prefissi degli oggetti che identificano un percorso di cartella virtuale.
file-name-or-prefix – Il nome del file di testo Amazon S3 o XML o un prefisso che identifica uno o più file di testo o XML da caricare. È anche possibile specificare un file manifest che identifica uno o più file di testo da caricare. Per ulteriori informazioni sull'utilizzo di un file manifest per caricare file di testo da Amazon S3, consulta Utilizzo di un manifest per specificare i file di dati da caricare.

Copia dell'URI dei file in un bucket S3

Accedi AWS Management Console e apri la console Amazon S3 all'indirizzo https://console.aws.amazon.com/s3/.
Nel riquadro di navigazione, scegli Bucket, quindi scegli il bucket di cui desideri copiare l'URI.
Seleziona il prefisso o il file che desideri caricare da S3.
Scegli Copia URI S3.

LOAD DATA FROM S3

Puoi usare l'istruzione LOAD DATA FROM S3 per caricare i dati da un qualsiasi formato di file di testo supportato dall'istruzione MySQL LOAD DATA INFILE, ad esempio i dati di testo delimitati da virgola. I file compressi non sono supportati.

Nota

Assicurati che il cluster database Aurora MySQL consenta le connessioni in uscita a S3. Per ulteriori informazioni, consulta Abilitazione delle comunicazioni di rete da Amazon Aurora MySQL ad altri servizi AWS.

Sintassi


LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Nota

In Aurora MySQL 3.05 e versioni successive, la parola chiave FROM è facoltativa.

Parametri

L'istruzione LOAD DATA FROM S3 utilizza i seguenti parametri obbligatori e facoltativi. Altri dettagli su questi parametri sono disponibili in Istruzione LOAD DATA nella documentazione di MySQL.

FILE | PREFIX | MANIFEST

Indica se caricare i dati da un singolo file, da tutti i file che corrispondono a un determinato prefisso o da tutti i file in un manifesto specificato. FILE è il valore predefinito.

S3-URI

Specifica l'URI per un file di testo o manifesto da caricare o un prefisso Amazon S3 da utilizzare. Specificare l'URI usando la sintassi descritta in Specifica del percorso (URI) in un bucket Amazon S3.

REPLACE | IGNORE

Determina quale azione intraprendere se una riga di input ha gli stessi valori chiave univoci di una riga esistente nella tabella del database.

Specifica REPLACE se la riga di input deve sostituire la riga esistente nella tabella.
Specifica IGNORE se la riga di input deve essere scartata.

INTO TABLE

Identifica il nome della tabella del database in cui caricare le righe di input.

PARTITION

Richiede che tutte le righe di input vengano inserite nelle partizioni identificate dall'elenco specificato di nomi di partizione separati da virgola. Se una riga di input non può essere inserita in una delle partizioni specificate, l'istruzione non riesce e viene restituito un errore.

CHARACTER SET

Identifica il set di caratteri dei dati nel file di input.

FIELDS | COLUMNS

Identifica il modo in cui i campi o le colonne nel file di input sono delimitati. I campi sono delimitati da tabulazioni per impostazione predefinita.

LINES

Identifica il modo in cui le righe nel file di input sono delimitati. Le linee sono delimitate da un carattere di nuova riga ('\n') per impostazione predefinita.

IGNORE numero LINES | ROWS

Specifica di ignorare un determinato numero di righe all'inizio del file di input. Ad esempio, è possibile utilizzare IGNORE 1 LINES per ignorare la riga di intestazione iniziale contenente i nomi di colonna o IGNORE 2 ROWS per ignorare le prime due righe di dati nel file di input. Se si utilizza anche PREFIX, IGNORE ignora un numero specifico di righe all'inizio del primo file di input.

col_name_or_user_var, ...

Specifica un elenco separato da virgola di uno o più nomi di colonne o variabili utente che identificano quali colonne caricare in base al nome. Il nome di una variabile utente utilizzata per questo scopo deve corrispondere al nome di un elemento del file di testo, preceduto da @. Puoi utilizzare le variabili utente per memorizzare i valori dei campi corrispondenti per un successivo riutilizzo.

Ad esempio, la seguente istruzione carica la prima colonna dal file di input nella prima colonna di table1 e imposta il valore della colonna table_column2 in table1 sul valore di input della seconda colonna diviso per 100.


LOAD DATA FROM S3 's3://DOC-EXAMPLE-BUCKET/data.txt'
    INTO TABLE table1
    (column1, @var1)
    SET table_column2 = @var1/100;

SET

Specifica l'elenco delle operazioni di assegnazione separate da virgole che imposta i valori delle colonne della tabella sui valori non inclusi nel file di input.

Ad esempio, la seguente istruzione imposta le prime due colonne di table1 sui valori delle prime due colonne del file di input e imposta il valore della colonna column3 in table1 sul timestamp corrente.


LOAD DATA FROM S3  's3://DOC-EXAMPLE-BUCKET/data.txt'
    INTO TABLE table1
    (column1, column2)
    SET column3 = CURRENT_TIMESTAMP;

È possibile utilizzare le sottoquery nella parte destra delle assegnazioni SET. Per una sottoquery che restituisce un valore da assegnare a una colonna, puoi utilizzare solo una sottoquery scalare. Inoltre, non è possibile utilizzare una sottoquery per selezionare dalla tabella che viene caricata.

Non puoi usare la parola chiave LOCAL dell'istruzione LOAD DATA FROM S3 se stai caricando i dati da un bucket Amazon S3.

Utilizzo di un manifest per specificare i file di dati da caricare

È possibile utilizzare l'istruzione LOAD DATA FROM S3 con la parola chiave MANIFEST per specificare un file manifest in formato JSON che elenca i file di testo da caricare in una tabella nel cluster DB.

Il seguente schema JSON descrive il formato e il contenuto di un file manifest.


{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "additionalProperties": false,
    "definitions": {},
    "id": "Aurora_LoadFromS3_Manifest",
    "properties": {
        "entries": {
            "additionalItems": false,
            "id": "/properties/entries",
            "items": {
                "additionalProperties": false,
                "id": "/properties/entries/items",
                "properties": {
                    "mandatory": {
                        "default": "false",
                        "id": "/properties/entries/items/properties/mandatory",
                        "type": "boolean"
                    },
                    "url": {
                        "id": "/properties/entries/items/properties/url",
                        "maxLength": 1024,
                        "minLength": 1,
                        "type": "string"
                    }
                },
                "required": [
                    "url"
                ],
                "type": "object"
            },
            "type": "array",
            "uniqueItems": true
        }
    },
    "required": [
        "entries"
    ],
    "type": "object"
}

Ogni url nel manifest deve specificare un URL con il nome bucket e il percorso completo dell'oggetto per il file, non solo un prefisso. Puoi utilizzare un manifest per caricare file da diversi bucket, regioni o file che non condividono lo stesso prefisso. Se una regione non è specificata nell'URL, viene utilizzata la regione del cluster DB Aurora di destinazione. L'esempio seguente mostra un file manifest che carica quattro file da diversi bucket.


{
  "entries": [
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":true
    },
    {
      "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata",
      "mandatory":true
    },
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":false
    },
    {
      "url":"s3://aurora-bucket/2013-10-05-customerdata"
    }
  ]
}

Il flag facoltativo mandatory specifica se LOAD DATA FROM S3 deve restituire un errore qualora il file non venga trovato. L'impostazione predefinita del flag mandatory è false. Indipendentemente dall'impostazione di mandatory, LOAD DATA FROM S3 termina se non viene trovato alcun file.

I file manifest possono avere qualsiasi estensione. L'esempio seguente esegue l'istruzione LOAD DATA FROM S3 con manifest nell'esempio precedente, che viene denominato customer.manifest.


LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest'
    INTO TABLE CUSTOMER
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL);

Al termine dell'istruzione, per ogni file caricato viene scritta una voce nella tabella aurora_s3_load_history.

Verifica dei file caricati utilizzando la tabella aurora_s3_load_history

Ogni istruzione LOAD DATA FROM S3 con esito positivo aggiorna la tabella aurora_s3_load_history nello schema mysql con una voce per ogni file che è stato caricato.

Dopo aver eseguito l'istruzione LOAD DATA FROM S3, puoi verificare quali file sono stati caricati eseguendo una query sulla tabella aurora_s3_load_history. Per visualizzare i file caricati da un'iterazione dell'istruzione, utilizza la clausola WHERE per filtrare i record sull'URI Amazon S3 per il file manifest utilizzato nell'istruzione. Se hai utilizzato lo stesso file manifest di prima, filtra i risultati utilizzando il campo timestamp.


select * from mysql.aurora_s3_load_history where load_prefix = 'S3_URI';

La tabella seguente descrive i campi della tabella aurora_s3_load_history.

Campo	Descrizione
`load_prefix`	L'URI specificato nell'istruzione di caricamento. L'URI può mappare a uno dei seguenti elementi: Un singolo file di dati per un'istruzione `LOAD DATA FROM S3 FILE` Un prefisso Amazon S3 che mappa a più file di dati per un'istruzione `LOAD DATA FROM S3 PREFIX` Un singolo file manifest contenente i nomi dei file da caricare per un'istruzione `LOAD DATA FROM S3 MANIFEST`
`file_name`	Il nome di un file che è stato caricato in Aurora da Amazon S3 utilizzando l'URI identificato nel campo `load_prefix`.
`version_number`	Il numero di versione del file identificato dal campo `file_name` che è stato caricato se il bucket Amazon S3 ha un numero di versione.
`bytes_loaded`	Le dimensioni del file caricato in byte.
`load_timestamp`	Timestamp relativo al momento del completamento dell'istruzione `LOAD DATA FROM S3`.

Esempi

La seguente istruzione carica i dati da un bucket Amazon S3 che si trova nella stessa regione del cluster DB Aurora. L'istruzione legge i dati delimitati da virgole nel file customerdata.txt contenuto nel bucket DOC-EXAMPLE-BUCKET Amazon S3, quindi carica i dati nella tabella. store-schema.customer-table


LOAD DATA FROM S3 's3://DOC-EXAMPLE-BUCKET/customerdata.csv' 
    INTO TABLE store-schema.customer-table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);

La seguente istruzione carica i dati da un bucket Amazon S3 che si trova in una regione diversa rispetto al cluster DB Aurora. L'istruzione legge i dati delimitati da virgole da tutti i file che corrispondono al prefisso dell'employee-dataoggetto nel bucket DOC-EXAMPLE-BUCKET Amazon S3 nella regione, quindi carica i dati nella tabella. us-west-2 employees


LOAD DATA FROM S3 PREFIX 's3-us-west-2://DOC-EXAMPLE-BUCKET/employee_data'
    INTO TABLE employees
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);

La seguente istruzione carica i dati dai file specificati in un file manifest JSON denominato q1_sales.json nella tabella sales.


LOAD DATA FROM S3 MANIFEST 's3-us-west-2://DOC-EXAMPLE-BUCKET1/q1_sales.json'
    INTO TABLE sales
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (MONTH, STORE, GROSS, NET);

LOAD XML FROM S3

Puoi usare l'istruzione LOAD XML FROM S3 per caricare i dati dai file XML memorizzati in un bucket Amazon S3 in uno dei seguenti tre diversi formati XML:

I nomi di colonna come attributi di un elemento <row>. Il valore dell'attributo identifica il contenuto del campo della tabella.
```
<row column1="value1" column2="value2" .../>
```
I nomi di colonna come elementi figlio di un elemento <row>. Il valore dell'elemento figlio identifica il contenuto del campo della tabella.
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
I nomi di colonna nell'attributo name dell'elemento <field> in un elemento <row>. Il valore dell'elemento <field> identifica il contenuto del campo della tabella.
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```

Sintassi


LOAD XML FROM S3 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<element-name>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var,...)]
    [SET col_name = expr,...]

Parametri

L'istruzione LOAD XML FROM S3 utilizza i seguenti parametri obbligatori e facoltativi. Altri dettagli su questi parametri sono disponibili in Istruzione LOAD XML nella documentazione di MySQL.

FILE | PREFIX

Indica se caricare i dati da un singolo file o da tutti i file che corrispondono a un determinato prefisso. FILE è il valore predefinito.

REPLACE | IGNORE

Determina quale azione intraprendere se una riga di input ha gli stessi valori chiave univoci di una riga esistente nella tabella del database.

Specifica REPLACE se la riga di input deve sostituire la riga esistente nella tabella.
Specifica IGNORE per ignorare la riga di input. IGNORE è il valore predefinito.

INTO TABLE

Identifica il nome della tabella del database in cui caricare le righe di input.

PARTITION

CHARACTER SET

Identifica il set di caratteri dei dati nel file di input.

ROWS IDENTIFIED BY

Identifica il nome dell'elemento che identifica una riga nel file di input. Il valore predefinito è <row>.

IGNORE numero LINES | ROWS

Specifica di ignorare un determinato numero di righe all'inizio del file di input. Ad esempio, è possibile utilizzare IGNORE 1 LINES per ignorare la prima riga del file di testo o IGNORE 2 ROWS per ignorare le prime due righe di dati nell'XML di input.

field_name_or_user_var, ...

Specifica un elenco separato da virgola di uno o più nomi di elementi XML o variabili utente che identificano quali elementi caricare in base al nome. Il nome di una variabile utente utilizzata per questo scopo deve corrispondere al nome di un elemento del file XML, preceduto da @. Puoi utilizzare le variabili utente per memorizzare i valori dei campi corrispondenti per un successivo riutilizzo.


LOAD XML FROM S3 's3://DOC-EXAMPLE-BUCKET/data.xml'
   INTO TABLE table1
   (column1, @var1)
   SET table_column2 = @var1/100;

SET

Specifica l'elenco delle operazioni di assegnazione separate da virgole che imposta i valori delle colonne della tabella sui valori non inclusi nel file di input.


LOAD XML FROM S3 's3://DOC-EXAMPLE-BUCKET/data.xml'
   INTO TABLE table1
   (column1, column2)
   SET column3 = CURRENT_TIMESTAMP;

Avvertimento JavaScript è disabilitato o non è disponibile nel tuo browser.

Per usare la documentazione AWS, JavaScript deve essere abilitato. Consulta le pagine della guida del browser per le istruzioni.

Convenzioni dei documenti

Abilitazione delle comunicazioni di rete con altri servizi AWS

Salvataggio dei dati dei file di testo in Amazon S3