Introducción a Amazon Redshift Spectrum - Amazon Redshift
Requisitos previosCloudFormationIntroducción paso a paso a Redshift SpectrumPaso 1. Creación de un rol de IAMPaso 2: Asociar el rol de IAM a su clústerPaso 3: Crear un esquema externo y una tabla externaPaso 4: Consultar sus datos en Amazon S3Lanzamiento de su pila de CloudFormation y, a continuación, consulta de sus datos

Introducción a Amazon Redshift Spectrum

En este tutorial, aprenderá a utilizar Amazon Redshift Spectrum para consultar datos directamente en los archivos de Amazon S3. Si ya tiene un clúster y un cliente SQL, puede completar este tutorial con la configuración mínima.

nota

Las consultas de Redshift Spectrum ocasionan cargos adicionales. El costo de ejecución de las consultas de muestra en este tutorial es nominal. Para obtener más información acerca de los precios, consulte Precios de Amazon Redshift Spectrum.

Requisitos previos

Para usar Redshift Spectrum, necesita un clúster de Amazon Redshift y un cliente SQL que se conecte al clúster, de modo que pueda ejecutar comandos SQL. El clúster y los archivos de datos de Amazon S3 deben estar en la misma Región de AWS.

Para obtener más información sobre cómo crear un clúster de Amazon Redshift, consulte Introducción a los almacenamientos de datos aprovisionados de Amazon Redshift en la Guía de introducción a Amazon Redshift. Para obtener información sobre las formas de conectarse a un clúster, consulte Conexión a los almacenamientos de datos de Amazon Redshift en la Guía de introducción a Amazon Redshift.

En algunos de los ejemplos siguientes, los datos de muestra se encuentran en la región Este de EE. UU. (Norte de Virginia) (us-east-1), por lo que necesita un clúster que también esté en us-east-1. O bien, puede usar Amazon S3 para copiar objetos de datos desde los siguientes buckets y carpetas en su bucket en el Región de AWS donde se encuentra el clúster:

  • s3://redshift-downloads/tickit/spectrum/customers/*

  • s3://redshift-downloads/tickit/spectrum/sales_partition/*

  • s3://redshift-downloads/tickit/spectrum/sales/*

  • s3://redshift-downloads/tickit/spectrum/salesevent/*

Ejecute un comando de Amazon S3 similar al siguiente para copiar los datos de muestra que se encuentran en el Este de EE. UU. (Norte de Virginia) en su Región de AWS. Antes de ejecutar el comando, cree el bucket y las carpetas del bucket para que coincidan con el comando copy de Amazon S3. El resultado del comando copy de Amazon S3 confirma que los archivos se han copiado en el nombre-bucket de la Región de AWS que desea.

aws s3 cp s3://redshift-downloads/tickit/spectrum/ s3://bucket-name/tickit/spectrum/ --copy-props none --recursive

Introducción a Redshift Spectrum con AWS CloudFormation

Como alternativa a los siguientes pasos, puede acceder a la plantilla DataLake de AWS CloudFormation de Redshift Spectrum para crear una pila con un bucket de Amazon S3 en la que luego pueda realizar consultas. Para obtener más información, consulte Lanzamiento de su pila de AWS CloudFormation y, a continuación, consulta de sus datos en Amazon S3.

Introducción paso a paso a Redshift Spectrum

Para empezar a usar Amazon Redshift Spectrum, siga los pasos que se indican a continuación:

Paso 1. Crear un rol de IAM para Amazon Redshift

El clúster necesita autorización para obtener acceso al catálogo de datos externo de AWS Glue o Amazon Athena, y a los archivos de datos de Amazon S3. Para proporcionar esa autorización, se referencia un rol de AWS Identity and Access Management (IAM) que se adjunta a su clúster. Para obtener más información acerca del uso de roles con Amazon Redshift, consulte Autorización de uso de las operaciones COPY y UNLOAD mediante roles de IAM.

nota

En ciertos casos, puede migrar su catálogo de datos de Athena a un catálogo de datos de AWS Glue. Puede hacer esto si su clúster está en una región de AWS donde se admite AWS Glue y tiene tablas externas de Redshift Spectrum en el catálogo de datos de Athena. Para usar el catálogo de datos de AWS Glue con Redshift Spectrum, es posible que tenga que cambiar las políticas de IAM. Para obtener más información, consulte Actualización al catálogo de datos de AWS Glue en la Guía del usuario de Athena.

Cuando cree un rol para Amazon Redshift, elija uno de los siguientes enfoques:

Para crear un rol de IAM para Amazon Redshift

  1. Abra la consola de IAM.

  2. Seleccione Roles en el panel de navegación.

  3. Elija Create role (Crear rol).

  4. Elija Servicio de AWS como entidad de confianza y, a continuación, elija Redshift como caso de uso.

  5. En Caso de uso para otros Servicios de AWS, elija Redshift - Personalizable y, a continuación, elija Siguiente.

  6. Aparece la página Add permissions policy (Agregar política de permisos). Elija AmazonS3ReadOnlyAccess y AWSGlueConsoleFullAccess, si está utilizando el catálogo de datos AWS Glue. O bien, elija AmazonAthenaFullAccess si está utilizando el catálogo de datos de Athena. Elija Next (Siguiente).

    nota

    La política AmazonS3ReadOnlyAccess proporciona al clúster acceso de solo lectura a todos los buckets de Amazon S3. Para conceder acceso solo al bucket de datos de muestra de AWS, cree una política nueva y agregue los siguientes permisos.

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": "arn:aws:s3:::redshift-downloads/*"
        }
    ]
}

  7. En Role Name (Nombre de la función), escriba un nombre para la función; por ejemplo, myspectrum_role.

  8. Revise la información y seleccione Create role (Crear función).

  9. Seleccione Roles en el panel de navegación. Elija el nombre de la nueva función para ver el resumen y copie el ARN de función en el portapapeles. Esta valor es el nombre de recurso de Amazon (ARN) de la función que acaba de crear. Usa ese valor cuando crea tablas externas para referenciar sus archivos de datos en Amazon S3.

Para crear un rol de IAM para Amazon Redshift mediante un AWS Glue Data Catalog habilitado para AWS Lake Formation

  1. Abra la consola de IAM en https://console.aws.amazon.com/iam/.

  2. En el panel de navegación, seleccione Policies (Políticas).

    Si es la primera vez que elige Policies (Políticas), aparecerá la página Welcome to Managed Policies (Bienvenido a políticas administradas). Elija Get Started (Comenzar).

  3. Elija Create Policy (Crear política).

  4. Elija crear la política en la pestaña JSON.

  5. Pegue el siguiente documento de política JSON, el cual concede acceso al catálogo de datos, pero deniega el permiso de administrador para Lake Formation.

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "RedshiftPolicyForLF",
            "Effect": "Allow",
            "Action": [
                "glue:*",
                "lakeformation:GetDataAccess"
            ],
            "Resource": "*"
        }
    ]
}

  6. Cuando haya terminado, seleccione Review (Revisar) para revisar la política. El validador de políticas notifica los errores de sintaxis.

  7. En la página Review policy (Revisar política), en el campo Name (Nombre), ingrese myspectrum_policy para asignarle un nombre a la política que está creando. (Opcional) Introduzca una descripción. Revise el Summary (Resumen) de la política para ver los permisos concedidos por su política. A continuación, elija Create policy (Crear política) para guardar su trabajo.

    Después de crear una política, puede proporcionar acceso a los clientes.

Para dar acceso, agregue permisos a los usuarios, grupos o roles:

Para otorgar permisos SELECT en la tabla para realizar consultas en la base de datos de Lake Formation

  1. Abra la consola de Lake Formation en https://console.aws.amazon.com/lakeformation/.

  2. En el panel de navegación, elija Permisos de lago de datos y, a continuación, elija Otorgar.

  3. Siga las instrucciones de Otorgar permisos de tabla mediante el método de recurso designado de la Guía para desarrolladores de AWS Lake Formation. Proporcione la información siguiente:

    • En IAM role (Rol de IAM), elija el rol de IAM que creó, myspectrum_role. Cuando se ejecuta el editor de consultas de Amazon Redshift, este utiliza el rol de IAM para dar permiso a los datos.

      nota

      Si desea otorgar permisos SELECT en la tabla de un catálogo de datos habilitado para Lake Formation para realizar consultas, haga lo siguiente:

      • Registre la ruta para los datos en Lake Formation.

      • Otorgue a los usuarios permisos para la ruta en Lake Formation.

      • Las tablas creadas se pueden encontrar en la ruta registrada en Lake Formation.

  4. Elija Conceder.

importante

Como práctica recomendada, permita el acceso únicamente a los objetos de Amazon S3 subyacentes a través de permisos de Lake Formation. Para evitar accesos no aprobados, quite cualquier permiso que haya otorgado a los objetos de Amazon S3 fuera de Lake Formation. Si ya ha accedido a objetos de Amazon S3 antes de configurar Lake Formation, quite las políticas de IAM o los permisos de los buckets que se hayan configurado con anterioridad. Para obtener más información, consulte Actualización de permisos de datos de AWS Glue en el modelo de AWS Lake Formation y Permisos de Lake Formation.

Paso 2: Asociar el rol de IAM a su clúster

Ahora, dispone de un rol de IAM que autoriza a Amazon Redshift a acceder al catálogo de datos externo y a Amazon S3 por usted. A esta altura, debe asociar ese rol a su clúster de Amazon Redshift.

Asociar un rol de IAM con un clúster

  1. Inicie sesión en la AWS Management Console y abra la consola de Amazon Redshift en https://console.aws.amazon.com/redshiftv2/.

  2. En el menú de navegación, elija Clusters (Clústeres) y, a continuación, elija el nombre del clúster que desea actualizar.

  3. En Actions (Acciones), seleccione Manage IAM roles (Administrar funciones de IAM). Aparece la página IAM roles (Roles de IAM).

  4. Seleccione Enter ARN (Introducir ARN) e introduzca un ARN o un rol de IAM o seleccione un rol de IAM de la lista. Luego elija Add IAM role (Agregar un rol de IAM) para agregarlo a la lista de Attached IAM roles (Roles de IAM agregados).

  5. Seleccione Done (Hecho) para asociar el rol de IAM al clúster. El clúster se modifica para completar el cambio.

Paso 3: Crear un esquema externo y una tabla externa

Crear tablas externas en un esquema externo. El esquema externo referencia una base de datos en el catálogo de datos externo y proporciona el ARN del rol de IAM que autoriza a su clúster a obtener acceso a Amazon S3 en su nombre. Puede crear una base de datos externa en un catálogo de datos de Amazon Athena, AWS Glue Data Catalog, o un metastore de Apache Hive, como Amazon EMR. En este ejemplo, crea la base de datos externa en un catálogo de datos de Amazon Athena cuando crea el esquema externo de Amazon Redshift. Para obtener más información, consulte Esquemas externos en Amazon Redshift Spectrum.

Pasos para crear un esquema externo y una tabla externa

  1. Para crear un esquema externo, reemplace el ARN de la función de IAM en el siguiente comando por el ARN de la función que creó en el Paso 1. Luego, ejecute el comando en cliente SQL.

    create external schema myspectrum_schema 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

  2. Para crear una tabla externa, ejecute el siguiente comando CREATE EXTERNAL TABLE.

    nota

    Su clúster y el bucket de Amazon S3 deben estar en el mismo Región de AWS. Para este ejemplo de comando CREATE EXTERNAL TABLE, el bucket de Amazon S3 con los datos de muestra se encuentra en la Región de AWS Este de EE. UU. (Norte de Virginia). Para ver los datos de origen, descargue el archivo sales_ts.000.

    Puede modificar este ejemplo para que se ejecute en otra Región de AWS. Cree un bucket de Amazon S3 en la Región de AWS que desee. Cree los datos de ventas con un comando copy de Amazon S3. A continuación, actualice la opción de ubicación en el comando CREATE EXTERNAL TABLE de ejemplo para su bucket.

    aws s3 cp s3://redshift-downloads/tickit/spectrum/sales/ s3://bucket-name/tickit/spectrum/sales/ --copy-props none --recursive

    El resultado del comando copy de Amazon S3 confirma que el archivo se ha copiado en el nombre-bucket de la Región de AWS que desea.

    copy: s3://redshift-downloads/tickit/spectrum/sales/sales_ts.000 to s3://bucket-name/tickit/spectrum/sales/sales_ts.000
    create external table myspectrum_schema.sales(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/'
table properties ('numRows'='172000');

Paso 4: Consultar sus datos en Amazon S3

Una vez creadas las tablas externas, puede consultarlas con las mismas instrucciones SELECT que usa para consultar otras tablas de Amazon Redshift. Estas consultas de la instrucción SELECT incluyen tablas combinadas, datos de agregación y filtrado de predicados.

Para consultar los datos en Amazon S3

  1. Obtenga la cantidad de filas en la tabla MYSPECTRUM_SCHEMA.SALES. 

    select count(*) from myspectrum_schema.sales;
    count 
------
172462

  2. Como práctica recomendada, es conveniente que guarde las tablas de hechos más grandes en Amazon S3 y las tablas de dimensiones más pequeñas en Amazon Redshift. Si cargó los datos de muestra en Cargar datos, tendrá una tabla llamada EVENT en la base de datos. Si no es así, cree la tabla EVENT utilizando el siguiente comando.

    create table event(
eventid integer not null distkey,
venueid smallint not null,
catid smallint not null,
dateid smallint not null sortkey,
eventname varchar(200),
starttime timestamp);

  3. Cargue la tabla EVENT reemplazando el ARN del rol de IAM en el siguiente comando COPY por el ARN del rol que creó en Paso 1. Crear un rol de IAM para Amazon Redshift. Si lo desea, puede descargar y ver los datos de origen para allevents_pipe.txtdesde un bucket de Amazon S3 en la Región de AWS us-east-1.

    copy event from 's3://redshift-downloads/tickit/allevents_pipe.txt' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-east-1';

    En el siguiente ejemplo, se combina la tabla externa de Amazon S3 MYSPECTRUM_SCHEMA.SALES con la tabla local de Amazon Redshift EVENT para obtener el total de ventas de los 10 principales eventos.

    select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;
    eventid | sum     
--------+---------
    289 | 51846.00
   7895 | 51049.00
   1602 | 50301.00
    851 | 49956.00
   7315 | 49823.00
   6471 | 47997.00
   2118 | 47863.00
    984 | 46780.00
   7851 | 46661.00
   5638 | 46280.00

  4. Vea el plan de consulta de la consulta anterior. Observe los pasos S3 Seq Scan, S3 HashAggregate y S3 Query Scan que se ejecutaron sobre los datos de Amazon S3.

    explain
select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) 
from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;
    
QUERY PLAN                                                                                                                                                                                
-----------------------------------------------------------------------------
XN Limit  (cost=1001055770628.63..1001055770628.65 rows=10 width=31)                                                                                                                      
  ->  XN Merge  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                               
        Merge Key: sum(sales.derived_col2)                                                                                                                                                
        ->  XN Network  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                       
              Send to leader                                                                                                                                                              
              ->  XN Sort  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                    
                    Sort Key: sum(sales.derived_col2)                                                                                                                                     
                    ->  XN HashAggregate  (cost=1055770620.49..1055770620.99 rows=200 width=31)                                                                                           
                          ->  XN Hash Join DS_BCAST_INNER  (cost=3119.97..1055769620.49 rows=200000 width=31)                                                                             
                                Hash Cond: ("outer".derived_col1 = "inner".eventid)                                                                                                       
                                ->  XN S3 Query Scan sales  (cost=3010.00..5010.50 rows=200000 width=31)                                                                                  
                                      ->  S3 HashAggregate  (cost=3010.00..3010.50 rows=200000 width=16)                                                                                  
                                            ->  S3 Seq Scan myspectrum_schema.sales location:"s3://redshift-downloads/tickit/spectrum/sales" format:TEXT  (cost=0.00..2150.00 rows=172000 width=16)
                                                  Filter: (pricepaid > 30.00)                                                                                                             
                                ->  XN Hash  (cost=87.98..87.98 rows=8798 width=4)                                                                                                        
                                      ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=4)

Lanzamiento de su pila de AWS CloudFormation y, a continuación, consulta de sus datos en Amazon S3

Una vez que haya creado un clúster de Amazon Redshift y se haya conectado a él, podrá instalar su plantilla DataLake de AWS CloudFormation de Redshift Spectrum y, a continuación, consultar sus datos.

CloudFormation instala la plantilla DataLake de Introducción a Redshift Spectrum y crea una pila que comprende lo siguiente:

  • un rol denominado myspectrum_role asociado a su clúster de Redshift

  • un esquema externo denominado myspectrum_schema

  • una tabla externa denominada sales en un bucket de Amazon S3

  • una tabla de Redshift denominada event con datos cargados

Para lanzar la pila DataLake de CloudFormation de Introducción a Redshift Spectrum.

  1. Elija Launch CFN stack (Lanzar pila de CFN). La consola de CloudFormation se abre con la plantilla Datalake.yml seleccionada.

    También puede descargar y personalizar la plantilla CFN de introducción a DataLake CloudFormation de Redshift Spectrum y, después, abrir la consola de CloudFormation (https://console.aws.amazon.com/cloudformation) y crear una pila con la plantilla personalizada.

  2. Elija Next (Siguiente).

  3. En la opción Parameters (Parámetros), ingrese el nombre del clúster de Amazon Redshift, el nombre de la base de datos y su correspondiente nombre de usuario.

  4. Elija Next (Siguiente).

    Aparecerán las opciones de la pila.

  5. Elija Next (Siguiente) para aceptar la configuración predeterminada.

  6. Revise la información y, en la opción Capabilities (Capacidades), elija I acknowledge that AWS CloudFormation might create IAM resources (Acepto que CFN podría crear recursos de IAM).

  7. Seleccione Crear pila.

Si se produce un error durante la creación de la pila, consulte la siguiente información:

  • Consulte la pestaña Events (Eventos) de CloudFormation para obtener información que pueda serle de utilidad para resolver el error.

  • Elimine la pila DataLake de CloudFormation antes de reintentar la operación.

  • Asegúrese de que se ha conectado a su base de datos de Amazon Redshift.

  • Asegúrese de que ingresó la información adecuada relativa al nombre del clúster de Amazon Redshift, el nombre de la base de datos y su correspondiente nombre de usuario.

Consulta de los datos en Amazon S3

Las tablas externas se consultan con las mismas instrucciones SELECT que se utilizan para hacerlo con otras tablas de Amazon Redshift. Estas consultas de la instrucción SELECT incluyen tablas combinadas, datos de agregación y filtrado de predicados.

La siguiente consulta devuelve la cantidad de filas de la tabla externa myspectrum_schema.sales. 

select count(*) from myspectrum_schema.sales;
count 
------
172462

Combinación de una tabla externa con una tabla local

En el siguiente ejemplo, se combina la tabla externa myspectrum_schema.sales con la tabla local event para obtener el total de ventas de los 10 eventos principales.

select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;
eventid | sum     
--------+---------
    289 | 51846.00
   7895 | 51049.00
   1602 | 50301.00
    851 | 49956.00
   7315 | 49823.00
   6471 | 47997.00
   2118 | 47863.00
    984 | 46780.00
   7851 | 46661.00
   5638 | 46280.00

Visualización del plan de consulta

Vea el plan de consulta de la consulta anterior. Tenga en cuenta los pasos S3 Seq Scan, S3 HashAggregate y S3 Query Scan que se ejecutaron sobre los datos de Amazon S3.

explain
select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) 
from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;

QUERY PLAN                                                                                                                                                                                
-----------------------------------------------------------------------------
XN Limit  (cost=1001055770628.63..1001055770628.65 rows=10 width=31)                                                                                                                      
  ->  XN Merge  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                               
        Merge Key: sum(sales.derived_col2)                                                                                                                                                
        ->  XN Network  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                       
              Send to leader                                                                                                                                                              
              ->  XN Sort  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                    
                    Sort Key: sum(sales.derived_col2)                                                                                                                                     
                    ->  XN HashAggregate  (cost=1055770620.49..1055770620.99 rows=200 width=31)                                                                                           
                          ->  XN Hash Join DS_BCAST_INNER  (cost=3119.97..1055769620.49 rows=200000 width=31)                                                                             
                                Hash Cond: ("outer".derived_col1 = "inner".eventid)                                                                                                       
                                ->  XN S3 Query Scan sales  (cost=3010.00..5010.50 rows=200000 width=31)                                                                                  
                                      ->  S3 HashAggregate  (cost=3010.00..3010.50 rows=200000 width=16)                                                                                  
                                            ->  S3 Seq Scan spectrum.sales location:"s3://redshift-downloads/tickit/spectrum/sales" format:TEXT  (cost=0.00..2150.00 rows=172000 width=16)
                                                  Filter: (pricepaid > 30.00)                                                                                                             
                                ->  XN Hash  (cost=87.98..87.98 rows=8798 width=4)                                                                                                        
                                      ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=4)