Menu
Amazon Mobile Analytics
User Guide

Adding New Metrics and Attributes to an Auto Export to Amazon Redshift

Event data exported to Amazon S3 contains all of the attributes and metrics that were sent to the service. There are two options you can use to manage changes to event attributes and metrics in your Amazon Redshift cluster:

  • Recreate auto export to a new Amazon Redshift cluster.

  • Add or remove attributes that are loaded to your existing Amazon Redshift cluster with AWS CloudFormation

Option 1: To recreate auto export to a new Amazon Redshift cluster

Warning

Using this method results in loss of any other data loaded into Amazon Redshift. Do not stop the auto export feature in the Mobile Analytics console while completing this method.

  1. Sign in to the AWS Management Console and open the AWS CloudFormation console at https://console.aws.amazon.com/cloudformation.

  2. Select the stack containing the Amazon Redshift cluster. This stack is named "MobileAnalyticsAutoExportToRedshift" in most cases.

  3. Choose Delete Stack.

  4. Navigate to the Mobile Analytics console and configure the auto export to Amazon Redshift feature. After completing this step, your Amazon Redshift cluster is created and populated with existing data in your Amazon S3 bucket.

Option 2: To add or remove attributes being loaded into your existing Amazon Redshift cluster with AWS CloudFormation

This method updates the tables in your Amazon Redshift cluster to include new attributes and metrics columns. It also updates the AWS CloudFormation stack to include these new attributes and metrics so they are loaded into these new columns in the Amazon Redshift cluster. Any new attributes and metrics are loaded into the Amazon Redshift cluster for new events received after completing these steps. Existing events in your Amazon Redshift cluster are not updated.

Note

Using this method requires you to terminate the EC2 instance that was created with the auto export to Amazon Redshift feature.

  1. Connect to your Amazon Redshift cluster with a user account that has sufficient privileges (for example, master privileges) using your SQL client of choice. For more information about connecting to an Amazon Redshift cluster using third-party tools, see Connecting to Clusters From Client Tools and Code.

  2. For each new attribute column you add for the event attribute (for example, “myNewAttribute”), run these commands:

    Copy
    alter table awsma.event_staging add column a_myNewAttribute VARCHAR(4000) NULL ENCODE LZO; alter table awsma.event add column a_myNewAttribute VARCHAR(4000) NULL ENCODE LZO;
  3. For each new metric column you add for the event metric (for example, “myNewMetric”), run these commands:

    Copy
    alter table awsma.event_staging add column m_myNewMetric FLOAT8 NULL; alter table awsma.event add column m_myNewMetric FLOAT8 NULL;
  4. After all attributes and metrics are added, run these commands to update the v_event view so it contains the new columns.

    Copy
    drop view AWSMA.v_event; CREATE OR REPLACE VIEW AWSMA.v_event AS select * from AWSMA.event; ALTER TABLE AWSMA.v_event OWNER to etl_user; grant select on AWSMA.v_event to group eventReaders;
  5. Update configuration with AWS CloudFormation:

    1. In the AWS CloudFormation console, choose the stack containing the Amazon Redshift Cluster (usually named "MobileAnalyticsAutoExportToRedshift").

    2. Choose Update Stack.

    3. Choose Next.

    4. Find the EventCustomMetrics and EventCustomAttributes parameters then add your new attributes and metrics as comma separated values.

    5. Find RedshiftEtlPassword, RedshiftMasterPassword and RedshiftReadPassword and then select Use Existing Value for each.

    6. Complete the rest of the update wizard.

  6. Terminate your Amazon EC2 instance so the Auto Scaling group creates a new instance with the updated configuration:

    1. In the Amazon EC2 console, choose Auto Scaling Groups.

    2. Choose the “MobileAnalyticsAutoExportToRedshift” Auto Scaling group.

    3. In the detail view select the Instances tab.

    4. Choose Instance ID.

    5. In Actions first choose Instance State and then choose Terminate.

The Auto Scaling group creates a new instance.