Adding an analysis rule to a configured table - AWS Clean Rooms
Adding an aggregation analysis rule to a table (guided flow)Adding a list analysis rule to a table (guided flow)Adding a custom analysis rule to a table (guided flow)Adding analysis rule to a table (JSON editor)Next steps

Adding an analysis rule to a configured table

The following sections describe how to add an analysis rule to your configured table. By defining the analysis rules, you can authorize the member who can query to run queries that match a specific analysis rule supported by AWS Clean Rooms.

AWS Clean Rooms supports the following types of analysis rules:

There can be only one analysis rule per configured table. You can configure the analysis rule any time before you associate your configured tables with the collaboration.

Important

If you are using Cryptographic Computing for Clean Rooms and have encrypted data tables in the collaboration, the analysis rule you add to the encrypted configured table should be consistent with how the data was encrypted. For example, if you encrypted the data for SELECT (aggregation analysis rule), you shouldn't add the analysis rule for JOIN (list analysis rule).

Adding an aggregation analysis rule to a table (guided flow)

The aggregation analysis rule allows queries that aggregate statistics without revealing row-level information using COUNT, SUM, and AVG functions along optional dimensions.

This procedure describes the process of adding an aggregation analysis rule to your configured table by using the Guided flow option in the AWS Clean Rooms console.

Note

Configured tables using non-S3 data sources only support custom analysis rules.

To add the aggregation analysis rule to a table (guided flow)

  1. Sign in to the AWS Management Console and open the AWS Clean Rooms console with your AWS account (if you haven't yet done so).

  2. In the left navigation pane, choose Tables.

  3. Choose the configured table.

  4. On the configured table detail page, choose Configure analysis rule.

  5. Under Step 1: Choose type, under Type, leave the Aggregation option selected by default.

  6. Under Creation method, select Guided flow, and then choose Next.

  7. Under Step 2: Specify query controls, for Aggregate functions:

    1. Choose an Aggregate function from the dropdown:

      • COUNT

      • COUNT DISTINCT

      • SUM

      • SUM DISTINCT

      • AVG

    2. Choose which columns can be used in the Aggregate function from the Columns dropdown.

    3. (Optional) Choose Add another function to add another aggregate function and associate one or more columns to that function.

      Note

      At least one aggregate function is required.

    4. (Optional) Choose Remove to remove an aggregate function.

  8. For Join controls,

    1. Choose one option for Allow table to be queried by itself:

      If you choose... Then ...
      No, only overlap can be queried The table can be queried only when joined to a table owned by the member who can query.
      Yes The table can be queried by itself or when joined to other tables.

    2. Under Specify join columns, choose the columns that you want to allow to be used in the INNER JOIN statement.

      This is optional if you have selected Yes in the previous step.

    3. Under Specify allowed operators for matching, choose which, if any, operators can be used for matching on multiple join columns. If you select two or more JOIN columns, one of these operators is required.

      If you choose... Then ...
      AND You can include AND in the INNER JOIN match conditions to join one column to another column between tables.
      OR You can include OR in the INNER JOIN match conditions to combine multiple column matches between tables. This logical operator is useful for obtaining a higher match rate.

  9. (Optional) For Dimension controls, in the Specify dimension columns dropdown, choose which columns you want to allow to be used in the SELECT statement, and the WHERE, GROUP BY, and ORDER BY parts of the query.

    Note

    Aggregate function or join columns can’t be used as Dimension columns.

  10. For Scalar functions, choose one option for Which scalar functions do you want to allow?

    If you choose... Then ...
    All currently supported by AWS Clean Rooms You allow all scalar functions currently supported by AWS Clean Rooms.

    • You can choose View list to see the entire list of Scalar functions supported in AWS Clean Rooms.
    A custom list You can customize which scalar functions to allow.

    • Choose one or more options from the Specify allowed scalar functions dropdown.
    None You don't want to allow any scalar functions.

    For more information, see Scalar functions.

  11. Choose Next.

  12. Under Step 3: Specify query results controls, for Aggregation constraints:

    1. Select the dropdown list for each Column name.

    2. Select the dropdown list for each Minimum number of distinct values that must be met for each output row to be returned, after the COUNT DISTINCT function is applied to it.

    3. Choose Add constraint to add more aggregation constraints.

    4. (Optional) Choose Remove to remove an aggregation constraint.

  13. For Additional analyses applied to output, select an option based on your goal.

    Your goal Recommended option
    Allow only direct queries on this table. Deny additional analyses from being run on query results. The table can only be used for direct querying. Not allowed
    Allow but don’t require both direct queries and additional analyses on this table. Allowed
    Require that the table can only be used in direct queries that are processed with one of the required additional analyses. Direct queries on this table must be further processed before they can be returned. Required

  14. Choose Next.

  15. Under Step 4: Review and configure, review the selections you’ve made for the previous steps, edit if necessary, and then choose Configure analysis rule.

You see a confirmation message that you’ve successfully configured an aggregation analysis rule to the table.

Adding a list analysis rule to a table (guided flow)

The list analysis rule allows queries that output row-level lists of the overlap between the associated table and a table of the member who can query.

This procedure describes the process of adding the list analysis rule to your configured table using the Guided flow option in the AWS Clean Rooms console.

Note

Configured tables using non-S3 data sources only support custom analysis rules.

To add a list analysis rule to a table (guided flow)

  1. Sign in to the AWS Management Console and open the AWS Clean Rooms console with your AWS account (if you haven't yet done so).

  2. In the left navigation pane, choose Tables.

  3. Choose the configured table.

  4. On the configured table detail page, choose Configure analysis rule.

  5. Under Step 1: Choose type, under Type, choose the List option.

  6. Under Creation method, select Guided flow, and then choose Next.

  7. Under Step 2: Specify query controls, for Join controls:

    1. Under Specify join columns, choose the columns that you want to allow to be used in the INNER JOIN statement.

    2. Under Specify allowed operators for matching, choose which, if any, operators can be used for matching on multiple join columns. If you select two or more JOIN columns, one of these operators is required.

      If you choose... Then ...
      AND You can include AND in the INNER JOIN match conditions to join one column to another column between tables.
      OR You can include OR in the INNER JOIN match conditions to combine multiple column matches between tables. This logical operator is useful for obtaining a higher match rate.

  8. (Optional) For List controls, in the Specify list columns dropdown, choose which columns you want to allow to be used in the query output (that is, used in the SELECT statement), or used to filter results (that is, the WHERE statement).

  9. Choose Next.

  10. Under Step 3: Specify query results controls, for Additional analyses applied to output, select an option based on your goal.

    Your goal Recommended option
    Allow only direct queries on this table. Deny additional analyses from being run on query results. The table can only be used for direct querying. Not allowed
    Allow but don’t require both direct queries and additional analyses on this table. Allowed
    Require that the table can only be used in direct queries that are processed with one of the required additional analyses. Direct queries on this table must be further processed before they can be returned. Required

  11. Under Step 4: Review and configure, review the selections you’ve made for the previous steps, edit if necessary, and then choose Configure analysis rule.

You see a confirmation message that you’ve successfully configured a list analysis rule for the table.

Adding a custom analysis rule to a table (guided flow)

The custom analysis rule enables custom SQL queries on a configured table. The custom analysis rule is required if you're using:

  • Analysis templates to allow a specific set of pre-approved SQL queries or a specific set of accounts that can provide queries that use your data.

  • AWS Clean Rooms Differential Privacy to protect against user-identification attempts.

  • Non-S3 data sources, such as Amazon Athena or Snowflake.

This procedure describes the process of adding the custom analysis rule to your configured table using the Guided flow option in the AWS Clean Rooms console.

To add a custom analysis rule to a table (guided flow)

  1. Sign in to the AWS Management Console and open the AWS Clean Rooms console with your AWS account (if you haven't yet done so).

  2. In the left navigation pane, choose Tables.

  3. Choose the configured table.

  4. On the configured table detail page, choose Configure analysis rule.

  5. Under Step 1: Choose type, under Type, choose the Custom option.

  6. Under Creation method, select Guided flow, and then choose Next.

  7. Under Step 2: Set differential privacy, determine whether you want differential privacy turned on or off. Differential privacy is a mathematically-proven technique to protect your data from re-identification attacks.

    Note

    AWS Clean Rooms Differential Privacy is only available for collaborations using AWS Clean Rooms SQL as the analytics engine and data stored in Amazon S3.

    1. For Differential privacy:

      If you... Then choose ...
      Have user-level data and you want protection against re-identification attempts Turn on
      Don't have user-level data or don't need protection against re-identification attempts Turn off

    2. If you have chosen to Turn on differential privacy, select the User identifier column that contains the unique identifier of your users, such as the user_id column, whose privacy you want to protect.

      To turn on differential privacy for two or more tables in a collaboration, you must configure the same column as the User identifier column in both analysis rules to maintain a consistent definition of users across tables. In case of a misconfiguration, the member who can query receives an error message that there are two columns to choose from in order to compute the number of user contributions (for example, the number of ad impressions made by a user) while running the query.

    3. Choose Next.

  8. Under Step 3: Specify query controls,

    1. For Control type, choose an option based on your goal.

      Your goal Option
      Review each new analysis template before it's run on your configured table Review each new analysis before it is allowed to be run on this table
      Let any analysis template or direct query be performed on your configured table Allow any queries created by specific collaborators to run without review on this table

    2. Choose one of the following:

      If you've chosen ... Then ...
      Review each new analysis before it is allowed to be run on this table Under Analysis templates allowed to be run, choose Add analysis template, and then choose the appropriate Collaboration and the Analysis template from the dropdown lists.
      Allow any queries created by specific collaborators to run without review on this table Under AWS accounts allowed to create any query, choose Add AWS account, and then choose the appropriate AWS account ID.

  9. Choose Next.

  10. Under Step 4: Specify query results controls,

    1. For Columns not allowed in output, choose one an option based on your goal.

      Your goal Recommended option
      Allow all columns to be returned in query outputs None
      Disallow certain columns from being returned in query outputs Custom list

    2. Choose one of the following:

      If you've chosen ... Then ...
      None Proceed to Additional analyses applied to output
      Custom list Under Specify disallowed columns, choose the columns that you want removed from query outputs.

    3. For Additional analyses applied to output select an option based on your goal.

      Your goal Recommended option
      Allow only direct queries on this table. Deny additional analyses from being run on query results. The table can only be used for direct querying. Not allowed
      Allow but don’t require both direct queries and additional analyses on this table. Allow
      Require that the table can only be used in direct queries that are processed with one of the required additional analyses. Direct queries on this table must be further processed before they can be returned. Required

  11. Choose Next.

  12. Under Step 5: Review and configure, review the selections you’ve made for the previous steps, edit if necessary, and then choose Configure analysis rule.

You see a confirmation message that you’ve successfully configured a custom analysis rule for the table.

Adding analysis rule to a table (JSON editor)

The following procedure shows how to add an analysis rule to a table using the JSON editor option in the AWS Clean Rooms console.

Note

Configured tables using non-S3 data sources only support custom analysis rules.

To add an aggregation, list, or custom analysis rule to a table (JSON editor)

  1. Sign in to the AWS Management Console and open the AWS Clean Rooms console with your AWS account (if you haven't yet done so).

  2. In the left navigation pane, choose Tables.

  3. Choose the configured table.

  4. On the configured table detail page, choose Configure analysis rule.

  5. Under Step 1: Choose type, under Type, choose either the Aggregation, List, or Custom option.

  6. Under Creation method, select JSON editor, and then choose Next.

  7. Under Step 2: Specify controls, you can choose to insert a query structure (Insert template) or insert a file (Import from file).

    If you choose... Then ...
    Insert template

    1. Specify the parameters for the selected analysis rule in the Analysis rule definition.

    2. You can press Ctrl + Spacebar to enable auto-complete.

    For more information about aggregation analysis rule parameters, see Aggregation analysis rule - query controls.

    For more information about list analysis rule parameters, see List analysis rule - query controls.
    Import from file

    1. Select your JSON file from your local drive.

    2. Choose Open.

      The Analysis rule definition displays the analysis rule from the uploaded file.

  8. Choose Next.

  9. Under Step 3: Review and configure, review the selections you’ve made for the previous steps, edit if necessary, and then choose Configure analysis rule.

You receive a confirmation message that you’ve successfully configured an analysis rule for the table.

Next steps

Now that you configured an analysis rule to your configured table, you are ready to: