Otorgar acceso a Aurora a Amazon S3 Concesión de privilegios para cargar datos Especificación de una ruta (URI) a un bucket de Amazon S3 LOAD DATA FROM S3 LOAD XML FROM S3

Carga de datos en un clúster de base de datos Amazon Aurora MySQL desde archivos de texto en un bucket de Amazon S3

Puede utilizar la instrucción LOAD DATA FROM S3 o LOAD XML FROM S3 para cargar datos de archivos almacenados en un bucket de Amazon S3. En Aurora MySQL, los archivos se almacenan primero en el disco local y, a continuación, se exportan a la base de datos. Una vez finalizadas las importaciones a la base de datos, se eliminan los archivos locales.

nota

La carga de datos en una tabla desde archivos de texto no se admite en Aurora Serverless v1. Se admite para Aurora Serverless v2.

Contenido

Otorgar acceso a Aurora a Amazon S3

Para poder cargar datos desde un bucket de Amazon S3, primero debe dar al clúster de base de datos Aurora MySQL permiso para que obtenga acceso a Amazon S3.

Para conceder a Aurora MySQL acceso a Amazon S3

Cree una política de AWS Identity and Access Management (IAM) que asigne los permisos de bucket y objeto que permiten que su clúster de base de datos de Aurora MySQL acceda a Amazon S3. Para obtener instrucciones, consulte Creación de una política de IAM para acceder a los recursos de Amazon S3.

nota
En la versión 3.05 de Aurora MySQL y versiones posteriores, puede cargar objetos cifrados mediante AWS KMS keys cifrado por el cliente. Para ello, incluya el permiso kms:Decrypt en su política de IAM. Para obtener más información, consulte Creación de una política de IAM para acceder a los recursos de AWS KMS.
No necesita este permiso para cargar objetos cifrados mediante Claves administradas por AWS o claves administradas de Amazon S3 (SSE-S3).
Cree un rol de IAM y asocie la política de IAM que creó en Creación de una política de IAM para acceder a los recursos de Amazon S3 al nuevo rol de IAM. Para obtener instrucciones, consulte Creación de un rol de IAM que permita a Amazon Aurora acceder a los servicios de AWS.
Asegúrese de que el clúster de base de datos utiliza un grupo de parámetros del clúster de base de datos.

Para obtener más información acerca de cómo crear un grupo de parámetros del clúster de base de datos personalizado, consulte Creación de un grupo de parámetros de clúster de base de datos en Amazon Aurora.
Para la versión 2 de Aurora MySQL, establezca el parámetro de clúster de base de datos aurora_load_from_s3_role o aws_default_s3_role en el nombre de recurso de Amazon (ARN) del nuevo rol de IAM. Si no se ha especificado un rol de IAM para aurora_load_from_s3_role, Aurora utilizará el rol de IAM especificado en el parámetro aws_default_s3_role.

Para Aurora MySQL versión 3, use aws_default_s3_role.

Si el clúster es parte de una base de datos global de Aurora, configure este parámetro para cada clúster de Aurora en la base de datos global. Aunque solo el clúster principal de una base de datos global de Aurora puede cargar datos, se puede promover otro clúster por parte del mecanismo de conmutación por error y convertirse en clúster principal.

Para obtener más información acerca de los parámetros de clúster de base de datos, consulte Parámetros del clúster de base de datos de Amazon Aurora y de instancia de base de datos.
Para permitir el acceso a Aurora MySQL a los usuarios de base de datos un clúster de base de datos Amazon S3, asocie el rol que creó en Creación de un rol de IAM que permita a Amazon Aurora acceder a los servicios de AWS con el clúster. Para una base de datos global de Aurora, asocie el rol con cada clúster de Aurora en la base de datos global. Para obtener información acerca de cómo asociar un rol de IAM con un clúster de base de datos, consulte Asociación de un rol de IAM con un clúster de base de datos Amazon Aurora MySQL.
Configure el clúster de base de datos Aurora MySQL para permitir conexiones salientes hacia Amazon S3. Para obtener instrucciones, consulte Habilitación de la comunicación de red desde Amazon Aurora a otros servicios de AWS.

Si el clúster de base de datos de no es de acceso público y se encuentra en una subred pública de una VPC, es privado. Puede crear un punto de conexión de puerta de enlace de S3 para acceder a su bucket de S3. Para obtener más información, consulte Puntos de conexión de puerta de enlace para Amazon S3.

Para una base de datos global de Aurora, habilite las conexiones de salida para cada clúster de Aurora en la base de datos global.

Concesión de privilegios para cargar datos en Amazon Aurora MySQL

El usuario de la base de datos que utiliza la instrucción LOAD DATA FROM S3 o LOAD XML FROM S3 debe tener un rol específico o el privilegio para poder hacerlo. En Aurora MySQL versión 3, usted otorga el rol de AWS_LOAD_S3_ACCESS. En la versión 2 de Aurora MySQL, usted otorga el privilegio LOAD FROM S3. El usuario administrativo de un clúster de base de datos tiene el rol o privilegio adecuados de forma predeterminada. Para conceder el privilegio a otro usuario, puede usar una de las instrucciones siguientes.

Utilice la siguiente instrucción para Aurora MySQL versión 3:


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

sugerencia

Cuando utiliza la técnica de rol en Aurora MySQL versión 3, también puede activar el rol mediante la instrucción SET ROLE role_name o SET ROLE ALL. Si no está familiarizado con el sistema de roles MySQL 8.0, puede obtener más información en Modelo de privilegios basado en roles. Para obtener más información, consulte Using roles en el Manual de referencia de MySQL.

Esto solo se aplica a la sesión activa actual. Cuando se vuelva a conectar, debe ejecutar la instrucción SET ROLE de nuevo para conceder privilegios. Para obtener más información, consulte la instrucción SET ROLE en el Manual de referencia de MySQL.

Puede utilizar el parámetro de clúster de base de datos activate_all_roles_on_login para activar automáticamente todos los roles cuando un usuario se conecta a una instancia de base de datos. Cuando se establece este parámetro, por lo general no tiene que llamar a la instrucción SET ROLE para activar un rol. Para obtener más información, consulte activate_all_roles_on_login en el Manual de referencia de MySQL.

Sin embargo, debe llamar a SET ROLE ALL de forma explícita al principio de un procedimiento almacenado para activar el rol cuando un usuario diferente llame al procedimiento almacenado.

Utilice la siguiente instrucción para la versión 2 de Aurora MySQL:


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

El rol AWS_LOAD_S3_ACCESS y el privilegio LOAD FROM S3 son específicos de Amazon Aurora y no están disponibles en las bases de datos de MySQL externas ni en las instancias de base de datos de RDS para MySQL. Si ha configurado replicación entre un clúster de base de datos de Aurora como origen y una base de datos MySQL como cliente, la instrucción GRANT para el rol o privilegio hará que la replicación se detenga con un error. Puede pasar por alto el error para continuar la replicación. Para pasar por alto el error en una instancia de RDS para MySQL, utilice el procedimiento mysql_rds_skip_repl_error. Para omitir el error en una base de datos MySQL externa, utilice la variable de sistema slave_skip_errors (Aurora MySQL versión 2) o la variable de sistema replica_skip_errors (Aurora MySQL versión 3).

nota

El usuario de la base de datos debe contar con privilegios de INSERT para la base de datos en la que carga los datos.

Especificación de una ruta (URI) a un bucket de Amazon S3

La sintaxis para especificar la ruta de acceso (URI) a los archivos almacenados en un bucket de Amazon S3 es la siguiente.


s3-region://amzn-s3-demo-bucket/file-name-or-prefix

La ruta incluye los siguientes valores:

region (opcional): la región de AWS que contiene el bucket de Amazon S3 desde el que se va a realizar la carga. Este valor es opcional. Si no se especifica el valor region, Aurora carga el archivo de Amazon S3 desde la misma región en la que se encuentra el clúster de base de datos.
bucket-name: el nombre del bucket de Amazon S3 que contiene los datos que se van a cargar. Pueden usarse prefijos de objeto que identifiquen una ruta de carpeta virtual.
file-name-or-prefix: el nombre del archivo de texto o el archivo XML de Amazon S3, o un prefijo que identifica el archivo o los archivos de texto o XML que se van a cargar. También puede especificar un archivo de manifiesto que identifique el archivo o los archivos de texto que se van a cargar. Para obtener más información acerca de cómo utilizar un archivo de manifiesto para cargar archivos de texto desde Amazon S3, consulte Uso de un manifiesto para especificar los archivos de datos que se deben cargar.

Copia del URI para los archivos de un bucket de S3

Inicie sesión AWS Management Console Management Console y abra la consola de Amazon S3 en https://console.aws.amazon.com/s3/.
En el panel de navegación, elija Buckets y, a continuación, elija el bucket cuyo URI desea copiar.
Seleccione el prefijo o el archivo que desee cargar desde S3.
Elija Copiar URI de S3.

LOAD DATA FROM S3

Puede utilizar la instrucción LOAD DATA FROM S3 para cargar datos desde archivos de texto con cualquier formato que sea compatible con la instrucción LOAD DATA INFILE de MySQL, como los datos de texto delimitados por comas. No se admiten los archivos comprimidos.

nota

Asegúrese de que su clúster de base de datos de Aurora MySQL permita conexiones salientes a S3. Para obtener más información, consulte Habilitación de la comunicación de red desde Amazon Aurora a otros servicios de AWS.

Sintaxis


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

En la versión 3.05 de Aurora MySQL y versiones posteriores, la palabra clave FROM es opcional.

Parámetros

La instrucción LOAD DATA FROM S3 utiliza los siguientes parámetros obligatorios y opcionales. Puede encontrar más información acerca de algunos de estos parámetros en LOAD DATA Statement (Instrucción LOAD DATA) en la documentación de MySQL.

FILE | PREFIX | MANIFEST

Identifica si se deben cargar los datos de un solo archivo, de todos los archivos que coincidan con un prefijo determinado o de todos los archivos del manifiesto especificado. FILE es el valor predeterminado.

S3-URI

Especifica el URI del archivo de texto o de manifiesto que se debe cargar, o el prefijo de Amazon S3 que se debe utilizar. Especifique el URI utilizando la sintaxis descrita en Especificación de una ruta (URI) a un bucket de Amazon S3.

REPLACE | IGNORE

Determina la acción que se debe realizar si una fila de entrada tiene los mismos valores de clave única que una fila existente en la tabla de la base de datos.

Especifique REPLACE si desea que la fila de entrada sustituya la fila existente en la tabla.
Especifique IGNORE si desea descartar la fila de entrada.

INTO TABLE

Identifica el nombre de la tabla de la base de datos en la que se deben cargar las filas de entrada.

PARTITION

Requiere que todas las filas de entrada se inserten en las particiones identificadas por la lista especificada que contiene los nombres de las particiones separados por comas. Si no se puede insertar una fila de entrada en una de las particiones especificadas, la instrucción falla y se devuelve un error.

CHARACTER SET

Identifica el conjunto de caracteres de los datos del archivo de entrada.

FIELDS | COLUMNS

Identifica cómo están delimitados los campos o las columnas del archivo de entrada. De forma predeterminada, los campos están delimitados por tabuladores.

LINES

Identifica cómo están delimitadas las líneas del archivo de entrada. De forma predeterminada, las líneas están delimitadas por un carácter de nueva línea ('\n').

IGNORE number LINES | ROWS

Especifica que se deben omitir cierto número de líneas o filas al principio del archivo de entrada. Por ejemplo, puede utilizar IGNORE 1 LINES para omitir una línea de encabezado inicial que contiene los nombres de las columnas o IGNORE 2 ROWS para omitir las dos primeras filas de datos del archivo de entrada. Si también utiliza PREFIX, IGNORE omite cierto número de líneas o filas al principio del primer archivo de entrada.

col_name_or_user_var, ...

Especifica una lista separada por comas de uno o varios nombres de columna o variables de usuario que identifican las columnas que se deben cargar por nombre. El nombre de una variable de usuario utilizada para este propósito debe coincidir con el nombre de un elemento del archivo de texto, con el prefijo @. Puede utilizar variables de usuario para almacenar los valores de los campos correspondientes para utilizarlos posteriormente.

Por ejemplo, la siguiente instrucción carga la primera columna del archivo de entrada en la primera columna de table1, y establece el valor de la columna table_column2 de table1 en el valor de entrada de la segunda columna, dividido por 100.


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

SET

Especifica una lista separada por comas que contiene operaciones de asignación que asignan valores no incluidos en el archivo de entrada a las columnas de la tabla.

Por ejemplo, la siguiente instrucción asigna a las dos primeras columnas de table1 los valores de las dos primeras columnas del archivo de entrada y, a continuación, asigna la fecha y hora actuales a column3 de table1.


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

Es posible utilizar subconsultas en el lado derecho de las asignaciones SET. Para una subconsulta que devuelve un valor que se va a asignar a una columna, solo se puede utilizar una subconsulta escalar. Además, no puede utilizar una subconsulta para seleccionar datos de la tabla que se está cargando.

No se puede utilizar la palabra clave LOCAL de la instrucción LOAD DATA FROM S3 para cargar datos de un bucket de Amazon S3.

Uso de un manifiesto para especificar los archivos de datos que se deben cargar

Puede utilizar la instrucción LOAD DATA FROM S3 con la palabra clave MANIFEST para especificar un archivo de manifiesto con formato JSON que enumera los archivos de texto que se cargarán en una tabla del clúster de base de datos.

El siguiente esquema JSON describe el formato y el contenido de un archivo de manifiesto.


{
    "$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"
}

Cada url del manifiesto debe especificar una URL con el nombre del bucket y la ruta de objeto completa del archivo, no solo un prefijo. Puede usar un manifiesto para cargar archivos de diferentes buckets o regiones, o archivos que no comparten el mismo prefijo. Si no se especifica una región en la URL, se utiliza la región del clúster de base de datos de destino de Aurora. En el siguiente ejemplo se muestra un archivo de manifiesto que carga cuatro archivos desde distintos buckets.


{
  "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"
    }
  ]
}

La marca opcional mandatory especifica si LOAD DATA FROM S3 debe devolver un error en caso de que no se encuentre el archivo. De forma predeterminada, la marca mandatory tiene el valor false. Sea cual sea el valor de mandatory, LOAD DATA FROM S3 finaliza si no se encuentra ningún archivo.

Los archivos de manifiesto pueden tener cualquier extensión. En el siguiente ejemplo, se ejecuta la instrucción LOAD DATA FROM S3 con el manifiesto del ejemplo anterior, que se denomina 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);

Una vez finalizada la instrucción, se escribe una entrada en la tabla aurora_s3_load_history para cada archivo que se ha cargado correctamente.

Comprobación de los archivos cargados mediante la tabla aurora_s3_load_history

Cada vez que se ejecuta correctamente la instrucción LOAD DATA FROM S3, se actualiza la tabla aurora_s3_load_history del esquema mysql con una entrada para cada archivo que se ha cargado.

Después de ejecutar la instrucción LOAD DATA FROM S3, puede comprobar qué archivos se han cargado consultando la tabla aurora_s3_load_history. Para ver los archivos que se cargaron durante una iteración de la instrucción, utilice la cláusula WHERE para filtrar los registros utilizando el URI de Amazon S3 del archivo de manifiesto utilizado en la instrucción. Si ha utilizado el mismo archivo de manifiesto anteriormente, filtre los resultados utilizando el campo timestamp.


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

En la siguiente tabla se describen los campos de la tabla aurora_s3_load_history.

Campo	Descripción
`load_prefix`	El URI que se especificó en la instrucción load. Este URI puede mapearse a cualquiera de los elementos siguientes: Un solo archivo de datos para una instrucción `LOAD DATA FROM S3 FILE` Un prefijo de Amazon S3 mapeado a varios archivos de datos para una instrucción `LOAD DATA FROM S3 PREFIX` Un único archivo de manifiesto que contiene los nombres de los archivos que se van a cargar para una instrucción `LOAD DATA FROM S3 MANIFEST`
`file_name`	El nombre de un archivo que se cargó en Aurora desde Amazon S3 utilizando el URI identificado en el campo `load_prefix`.
`version_number`	El número de versión del archivo identificado por el campo `file_name` que se cargó, si el bucket de Amazon S3 tiene un número de versión.
`bytes_loaded`	El tamaño del archivo cargado, en bytes.
`load_timestamp`	La fecha y hora en que finalizó la instrucción `LOAD DATA FROM S3`.

Ejemplos

La siguiente instrucción carga datos desde un bucket de Amazon S3 que se encuentra en la misma región que el clúster de base de datos Aurora. La instrucción lee los datos delimitados por comas del archivo customerdata.txt, que se encuentra en el bucket de Amazon S3 amzn-s3-demo-bucket, y carga los datos en la tabla store-schema.customer-table.


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

La siguiente instrucción carga datos desde un bucket de Amazon S3 que se encuentra en una región que no coincide con la del clúster de base de datos Aurora. La instrucción lee los datos delimitados por comas de todos los archivos que coinciden con el prefijo de objeto employee-data en el bucket de Amazon S3 amzn-s3-demo-bucket, en la región us-west-2 y carga los datos en la tabla employees.


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

La siguiente instrucción carga los datos de los archivos especificados en un archivo de manifiesto JSON denominado q1_sales.json en la tabla sales.


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

LOAD XML FROM S3

Puede utilizar la instrucción LOAD XML FROM S3 para cargar datos de archivos XML almacenados en un bucket de Amazon S3 con uno de los tres formatos XML siguientes:

Con los nombres de las columnas como atributos de un elemento <row>. El valor del atributo identifica el contenido del campo de la tabla.
```
<row column1="value1" column2="value2" .../>
```
Con los nombres de las columnas como elementos secundarios de un elemento <row>. El valor del elemento secundario identifica el contenido del campo de la tabla.
```
<row>
  <column1>value1</column1>
  <column2>value2</column2>
</row>
```
Con los nombres de las columnas en el atributo name de los elementos <field> de un elemento <row>. El valor del elemento <field> identifica el contenido del campo de la tabla.
```
<row>
  <field name='column1'>value1</field>
  <field name='column2'>value2</field>
</row>
```

Sintaxis


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,...]

Parámetros

La instrucción LOAD XML FROM S3 utiliza los siguientes parámetros obligatorios y opcionales. Puede encontrar más información acerca de algunos de estos parámetros en LOAD XML Statement (Instrucción LOAD XML) en la documentación de MySQL.

FILE | PREFIX

Identifica si se deben cargar los datos de un solo archivo o de todos los archivos que coincidan con un prefijo determinado. FILE es el valor predeterminado.

REPLACE | IGNORE

Determina la acción que se debe realizar si una fila de entrada tiene los mismos valores de clave única que una fila existente en la tabla de la base de datos.

Especifique REPLACE si desea que la fila de entrada sustituya la fila existente en la tabla.
Especifique IGNORE si desea para descartar la fila de entrada. IGNORE es el valor predeterminado.

INTO TABLE

Identifica el nombre de la tabla de la base de datos en la que se deben cargar las filas de entrada.

PARTITION

CHARACTER SET

Identifica el conjunto de caracteres de los datos del archivo de entrada.

ROWS IDENTIFIED BY

Establece el nombre del elemento que identifica una fila del archivo de entrada. El valor predeterminado es <row>.

IGNORE number LINES | ROWS

Especifica que se deben omitir cierto número de líneas o filas al principio del archivo de entrada. Por ejemplo, puede utilizar IGNORE 1 LINES para omitir la primera línea del archivo de texto o IGNORE 2 ROWS para omitir las dos primeras filas de datos del archivo XML de entrada.

field_name_or_user_var, ...

Especifica una lista separada por comas de uno o varios elementos XML o variables de usuario que identifican los elementos que se deben cargar por nombre. El nombre de una variable de usuario utilizada para este propósito debe coincidir con el nombre de un elemento del archivo XML, con el prefijo @. Puede utilizar variables de usuario para almacenar los valores de los campos correspondientes para utilizarlos posteriormente.


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

SET

Especifica una lista separada por comas que contiene operaciones de asignación que asignan valores no incluidos en el archivo de entrada a las columnas de la tabla.


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

Aviso JavaScript está desactivado o no está disponible en su navegador.

Para utilizar la documentación de AWS, debe estar habilitado JavaScript. Para obtener más información, consulte las páginas de ayuda de su navegador.

Convenciones del documento

Habilitación de la comunicación de red con servicios de AWS

Almacenamiento de datos en archivos de texto en Amazon S3