Salvataggio dei dati da un cluster DB Amazon Aurora MySQL nei file di testo in un bucket Amazon S3 - Amazon Aurora
Accesso di Aurora MySQL a Amazon S3;Concessione dei privilegi per salvare dati in Aurora MySQLSpecifica di un percorso in un bucket Amazon S3Creazione di un manifestSELECT INTO OUTFILE S3

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

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

Puoi utilizzare l'SELECT INTO OUTFILE S3istruzione per interrogare i dati da un cluster Amazon Aurora MySQL DB e salvarli in file di testo archiviati in un bucket Amazon S3. In Aurora MySQL, i file vengono prima archiviati sul disco locale e quindi esportati in S3. Al termine delle esportazioni, i file locali vengono eliminati.

Puoi crittografare il bucket Amazon S3 utilizzando una chiave gestita Amazon S3 (SSE-S3) o (SSE-KMS: AWS KMS key o chiave gestita dal cliente). Chiave gestita da AWS

L'LOAD DATA FROM S3istruzione può utilizzare i file creati dall'SELECT INTO OUTFILE S3istruzione per caricare dati in un cluster Aurora DB. Per ulteriori informazioni, consulta Caricamento dei dati in un cluster DB Amazon Aurora MySQL da file di testo in un bucket Amazon S3.

Nota

Questa funzionalità non è supportata per i cluster di database Aurora Serverless v1. È supportata per i cluster di database Aurora Serverless v2.

Puoi anche salvare i dati del cluster DB e i dati degli snapshot del cluster DB su Amazon S3 utilizzando AWS CLI, o AWS Management Console l'API Amazon RDS. Per ulteriori informazioni, consulta Esportazione dei dati del cluster database in Amazon S3 e Esportazione dei dati dello snapshot del cluster di database in Amazon S3.

Accesso di Aurora MySQL a Amazon S3;

Prima di poter salvare i dati in 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

  1. Crea una policy AWS Identity and Access Management (IAM) che fornisca le autorizzazioni per i bucket e gli 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 versione 3.05 e successive, è possibile crittografare gli oggetti utilizzando chiavi gestite dal cliente. AWS KMS A tale scopo, includi l'autorizzazione kms:GenerateDataKey nella policy IAM. Per ulteriori informazioni, consulta Creazione di una policy IAM per l'accesso alle risorseAWS KMS.

    Non è necessaria questa autorizzazione per crittografare gli oggetti utilizzando Chiavi gestite da AWS o le chiavi gestite di Amazon S3 (SSE-S3).

  2. 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.

  3. Per Aurora MySQL versione 2, imposta il parametro del cluster di database aurora_select_into_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_select_into_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.

    Per ulteriori informazioni sui parametri del cluster DB, vedi Parametri dell'istanza database e del cluster database di Amazon Aurora.

  4. 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.

  5. 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.

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

Concessione dei privilegi per salvare dati in Aurora MySQL

L'utente del database che emette l'istruzione SELECT INTO OUTFILE S3 deve avere un ruolo o un privilegio specifico. In Aurora MySQL versione 3, concedi il ruolo AWS_SELECT_S3_ACCESS. In Aurora MySQL versione 2, concedi il privilegio SELECT INTO 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_SELECT_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 SELECT INTO S3 ON *.* TO 'user'@'domain-or-ip-address'

Il ruolo AWS_SELECT_S3_ACCESS e privilegio SELECT INTO S3 sono specifici di Amazon Aurora MySQL e non sono disponibili per i database MySQL o le istanze database RDS per MySQL. Se è stata impostata la replica tra un cluster di database Aurora MySQL 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 database RDS for MySQL, utilizzare 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).

Specifica di un percorso in un bucket Amazon S3

La sintassi per specificare un percorso per archiviare i file manifest e di dati in un bucket Amazon S3 è simile a quella utilizzata nell'istruzione LOAD DATA FROM S3 PREFIX, come mostrato di seguito.

s3-region://bucket-name/file-prefix

Il percorso include i seguenti valori:

  • region(opzionale): la AWS regione che contiene il bucket Amazon S3 in cui salvare i dati. Questo valore è facoltativo. Se non specifichi un valore per region, Aurora salva i file in Amazon S3 nella stessa regione del cluster DB.

  • bucket-name – Il nome del bucket Amazon S3 in cui salvare i dati. Sono supportati i prefissi degli oggetti che identificano un percorso di cartella virtuale.

  • file-prefix – Il prefisso dell'oggetto Amazon S3 che identifica i file da salvare in Amazon S3.

I file di dati creati dall'istruzione SELECT INTO OUTFILE S3 utilizzano il seguente percorso, in cui 00000 rappresenta un numero intero a 5 cifre, a base zero.

s3-region://bucket-name/file-prefix.part_00000

Ad esempio, supponiamo che un'istruzione SELECT INTO OUTFILE S3 specifichi s3-us-west-2://bucket/prefix come percorso in cui archiviare i file di dati e crea tre file di dati. Il bucket Amazon S3 specificato contiene i seguenti file di dati.

  • s3-us-west-2://bucket/prefix.part_00000

  • s3-us-west-2://bucket/prefix.part_00001

  • s3-us-west-2://bucket/prefix.part_00002

Creazione di un manifest per elencare i fili di dati

È possibile utilizzare l'istruzione SELECT INTO OUTFILE S3 con l'opzione MANIFEST ON per creare un file manifest in formato JSON che elenca i file di testo creati dall'istruzione. L'istruzione LOAD DATA FROM S3 può utilizzare il file manifest per caricare i file di dati di nuovo in un cluster DB Aurora MySQL. Per ulteriori informazioni sull'utilizzo di un file manifest per caricare file di dati da Amazon S3 in un cluster DB Aurora MySQL, consulta Utilizzo di un manifest per specificare i file di dati da caricare.

I file di dati inclusi nel manifest creati dall'istruzione SELECT INTO OUTFILE S3 sono elencati nell'ordine in cui vengono creati dall'istruzione. Ad esempio, supponiamo che un'istruzione SELECT INTO OUTFILE S3 specifichi s3-us-west-2://bucket/prefix come percorso in cui archiviare i file di dati e crea tre file di dati e un file manifest. Il bucket Amazon S3 specificato contiene un file manifest denominato s3-us-west-2://bucket/prefix.manifest che contiene le seguenti informazioni.

{
  "entries": [
    {
      "url":"s3-us-west-2://bucket/prefix.part_00000"
    },
    {
      "url":"s3-us-west-2://bucket/prefix.part_00001"
    },
    {
      "url":"s3-us-west-2://bucket/prefix.part_00002"
    }
  ]
}

SELECT INTO OUTFILE S3

Puoi usare l'istruzione SELECT INTO OUTFILE S3 per eseguire una query sui dati da un cluster DB e salvarlo direttamente nei file di testo delimitato archiviati in un bucket Amazon S3.

I file compressi non sono supportati. I file crittografati sono supportati a partire da Aurora MySQL versione 2.09.0.

Sintassi

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
        [HIGH_PRIORITY]
        [STRAIGHT_JOIN]
        [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
        [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
        [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
        [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
         [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
INTO OUTFILE S3 's3_uri'
[CHARACTER SET charset_name]
    [export_options]
    [MANIFEST {ON | OFF}]
    [OVERWRITE {ON | OFF}]
    [ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id']}]

export_options:
    [FORMAT {CSV|TEXT} [HEADER]]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
]

Parametri

L'istruzione SELECT INTO OUTFILE S3 utilizza i seguenti parametri obbligatori e facoltativi specifici per Aurora.

s3-uri

Specifica l'URI per un prefisso Amazon S3 da utilizzare. Utilizza la sintassi descritta in Specifica di un percorso in un bucket Amazon S3.

FORMAT {CSV|TEXT} [HEADER]

Facoltativamente salva i dati in formato CSV.

L'opzione TEXT è quella predefinita e produce il formato di esportazione MySQL esistente.

L'opzione CSV genera valori di dati separati da virgole. Il formato CSV segue le specifiche nella RFC-4180. Se si specifica la parola chiave facoltativa HEADER, il file di output contiene una riga di intestazione. Le etichette nella riga di intestazione corrispondono ai nomi di colonna dell'istruzione SELECT. Puoi utilizzare i file CSV per addestrare modelli di dati da utilizzare con AWS i servizi ML. Per ulteriori informazioni sull'utilizzo dei dati Aurora esportati AWS con i servizi ML, vedere. Esportazione di dati su Amazon S3 SageMaker per la formazione dei modelli (Advanced)

MANIFEST {ON | OFF}

Indica se un file manifesto viene creato in Amazon S3. Il file manifest è un file JSON ( JavaScript Object Notation) che può essere utilizzato per caricare dati in un cluster Aurora DB con l'istruzione. LOAD DATA FROM S3 MANIFEST Per ulteriori informazioni su LOAD DATA FROM S3 MANIFEST, consulta Caricamento dei dati in un cluster DB Amazon Aurora MySQL da file di testo in un bucket Amazon S3.

Se MANIFEST ON è specificato nella query, il file manifest viene creato in Amazon S3 dopo che tutti i file di dati sono stati creati e caricati. Il file manifest viene creato utilizzando il seguente percorso:

s3-region://bucket-name/file-prefix.manifest

Per ulteriori informazioni sul formato del contenuto del file manifest, consulta Creazione di un manifest per elencare i fili di dati.

OVERWRITE {ON | OFF}

Indica se i file esistenti nel bucket Amazon S3 specificato vengono sovrascritti. Se OVERWRITE ON è specificato, i file esistenti che corrispondono al prefisso del file nell'URI specificato in s3-uri vengono sovrascritti. In caso contrario, si verifica un errore.

ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id']}

Indica se utilizzare la crittografia lato server con chiavi gestite di Amazon S3 (SSE-S3) AWS KMS keys o (SSE-KMS, incluse le chiavi gestite dal cliente). Chiavi gestite da AWS Le impostazioni SSE_S3 e SSE_KMS sono disponibili in Aurora MySQL 3.05 e versioni successive.

Puoi anche utilizzare la variabile di sessione aurora_select_into_s3_encryption_default anziché la clausola ENCRYPTION, come illustrato nell'esempio seguente. Usa la clausola SQL o la variabile di sessione, ma non entrambe.

set session set session aurora_select_into_s3_encryption_default={ON | OFF | SSE_S3 | SSE_KMS};

Le impostazioni SSE_S3 e SSE_KMS sono disponibili in Aurora MySQL 3.05 e versioni successive.

Impostando aurora_select_into_s3_encryption_default sul seguente valore:

  • OFF: viene seguita la policy di crittografia predefinita del bucket S3. Il valore predefinito di aurora_select_into_s3_encryption_default è OFF.

  • ON oppure SSE_S3: l'oggetto S3 viene crittografato tramite chiavi gestite da Amazon S3 (SSE-S3).

  • SSE_KMS— L'oggetto S3 è crittografato utilizzando un. AWS KMS key

    In questo caso, includi anche la variabile di sessione aurora_s3_default_cmk_id, ad esempio:

    set session aurora_select_into_s3_encryption_default={SSE_KMS};
set session aurora_s3_default_cmk_id={NULL | 'cmk_id'};

    • Quando aurora_s3_default_cmk_id è NULL, l'oggetto S3 viene crittografato utilizzando una Chiave gestita da AWS.

    • Quando aurora_s3_default_cmk_id è una stringa cmk_id non vuota, l'oggetto S3 viene crittografato utilizzando una chiave gestita dal cliente.

      Il valore di cmk_id non può essere una stringa vuota.

Quando utilizzi il comando SELECT INTO OUTFILE S3, Aurora determina la crittografia come segue:

  • Se nel comando SQL è inclusa la clausola ENCRYPTION, Aurora si basa solo sul valore di ENCRYPTION e non utilizza una variabile di sessione.

  • Se la clausola ENCRYPTION non è presente, Aurora si basa sul valore della variabile di sessione.

Per ulteriori informazioni, consulta Uso della crittografia lato server con chiavi gestite Amazon S3 (SSE-S3) e Utilizzo della crittografia lato server con chiavi (SSE-KMS) nella Guida per l'utente di Amazon AWS KMS Simple Storage Service.

Altri dettagli su questi parametri sono disponibili in Istruzione SELECT e Istruzione LOAD DATA nella documentazione di MySQL.

Considerazioni

Il numero di file scritti nel bucket Amazon S3 dipende dalla quantità di dati selezionati dall'istruzione SELECT INTO OUTFILE S3 e dalla soglia della dimensione del file per Aurora MySQL. La soglia della dimensione del file predefinita è 6 gigabyte (GB). Se i dati selezionati dall'istruzione sono inferiori alla soglia della dimensione del file, viene creato un singolo file; in caso contrario, vengono creati più file. Altre considerazioni per i file creati da questa dichiarazione includono quanto segue:

  • Aurora MySQL garantisce che le righe nei file di dati non siano suddivise tra i limiti dei file. Nel caso di più file, la dimensione di ogni file di dati tranne l'ultimo è in genere vicino alla soglia della dimensione del file. Tuttavia, ogni tanto poiché restano sotto la soglia della dimensione del file, una riga viene suddivisa su due file di dati. In questo caso, Aurora MySQL crea un file di dati che mantiene intatta la riga, ma potrebbe essere più grande della soglia della dimensione del file.

  • Perché ogni istruzione SELECT in Aurora MySQL viene eseguita come una transazione atomica, un'istruzione SELECT INTO OUTFILE S3 che seleziona un set di dati di grandi dimensioni potrebbe essere eseguita per qualche tempo. Se l'istruzione non riesce per qualsiasi motivo, potrebbe essere necessario ricominciare ed emettere nuovamente l'istruzione. Se l'istruzione non riesce, tuttavia, i file che sono già stati caricati in Amazon S3 rimangono nel bucket Amazon S3 specifico. Puoi utilizzare un'altra istruzione per caricare i dati rimanenti anziché ricominciare da capo.

  • Se la quantità di dati da selezionare è elevata (oltre 25 GB), ti consigliamo di utilizzare più istruzioni SELECT INTO OUTFILE S3 per salvare i dati in Amazon S3. Ogni istruzione dovrebbe selezionare una parte diversa dei dati da salvare e anche specificare un diverso file_prefix nel parametro s3-uri da utilizzare quando si salvano i file di dati. Il partizionamento dei dati da selezionare con più istruzioni facilita il recupero da un errore in un'istruzione. Se si verifica un errore per un'istruzione, solo una parte dei dati deve essere riselezionata e caricata in Amazon S3. L'utilizzo di più istruzioni aiuta anche a evitare una singola transazione di lunga durata, che può migliorare le prestazioni.

  • Se più istruzioni SELECT INTO OUTFILE S3 che utilizzano lo stesso file_prefix nel parametro s3-uri vengono eseguite in parallelo per selezionare i dati in Amazon S3, il comportamento non è definito.

  • I metadati, come lo schema della tabella o i metadati dei file, non vengono caricati da Aurora MySQL in Amazon S3.

  • In alcuni casi, potresti rieseguire una query SELECT INTO OUTFILE S3, ad esempio per ripristinare a causa di un errore. In questi casi, devi rimuovere tutti i file di dati esistenti nel bucket Amazon S3 con lo stesso prefisso di file specificato in s3-uri o includere OVERWRITE ON nella query SELECT INTO OUTFILE S3.

L'istruzione SELECT INTO OUTFILE S3 restituisce un tipico numero di errore MySQL e la risposta in caso di esito positivo o negativo. Se non hai accesso al numero di errore e alla risposta di MySQL, il modo più semplice per determinare quando termina l'esecuzione è specificare MANIFEST ON nell'istruzione. Il file manifest è l'ultimo file scritto dall'istruzione. In altre parole, se hai un file manifest, l'istruzione ha completato.

Al momento non è possibile monitorare direttamente l'avanzamento dell'istruzione SELECT INTO OUTFILE S3 durante l'esecuzione. Tuttavia, supponiamo di scrivere una grande quantità di dati da Aurora MySQL in Amazon S3 usando questa istruzione e di conoscere la dimensione dei dati selezionati dall'istruzione. In questo caso, puoi stimare l'avanzamento monitorando la creazione di file di dati in Amazon S3.

Per fare ciò, è possibile utilizzare il fatto che un file di dati viene creato nel bucket Amazon S3 specificato ogni circa 6 GB di dati selezionati dall'istruzione. Dividi la dimensione dei dati selezionati da 6 GB per ottenere il numero stimato di file di dati da creare. È quindi possibile stimare l'avanzamento dell'istruzione monitorando il numero di file caricati in Amazon S3 durante l'esecuzione.

Esempi

La seguente istruzione seleziona tutti i dati nella tabella employees e li salva in un bucket Amazon S3 che si trova in una regione diversa dal cluster DB Aurora MySQL. L'istruzione crea file di dati in cui ogni campo è terminato da una virgola (,) e ogni riga è terminata da un carattere di nuova riga (\n). L'istruzione restituisce un errore se i file che corrispondono al prefisso del file sample_employee_data esistono nel bucket Amazon S3 specificato.

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n';

La seguente istruzione seleziona tutti i dati nella tabella employees e li salva in un bucket Amazon S3 che si trova nella stessa regione del cluster DB Aurora MySQL. L'istruzione crea file di dati in cui ogni campo è terminato da una virgola (,) e ogni riga è terminata da un carattere di nuova riga (\n) e un file manifest. L'istruzione restituisce un errore se i file che corrispondono al prefisso del file sample_employee_data esistono nel bucket Amazon S3 specificato.

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    MANIFEST ON;

La seguente istruzione seleziona tutti i dati nella tabella employees e li salva in un bucket Amazon S3 che si trova in una regione diversa dal cluster DB Aurora. L'istruzione crea file di dati in cui ogni campo è terminato da una virgola (,) e ogni riga è terminata da un carattere di nuova riga (\n). L'istruzione sostituisce ogni file che corrisponde al prefisso del file sample_employee_data esistono nel bucket Amazon S3 specificato.

SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    OVERWRITE ON;

La seguente istruzione seleziona tutti i dati nella tabella employees e li salva in un bucket Amazon S3 che si trova nella stessa regione del cluster DB Aurora MySQL. L'istruzione crea file di dati in cui ogni campo è terminato da una virgola (,) e ogni riga è terminata da un carattere di nuova riga (\n) e un file manifest. L'istruzione sostituisce ogni file che corrisponde al prefisso del file sample_employee_data esistono nel bucket Amazon S3 specificato.

SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data'
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    MANIFEST ON
    OVERWRITE ON;