Querying ID mapping tables using the SQL code editor - AWS Clean Rooms

Querying ID mapping tables using the SQL code editor

The following procedure describes how to run a multi-table join query on the ID mapping table to join the sourceId with the targetId.

Before you query the ID mapping table, the ID mapping table must be successfully populated.

To query ID mapping tables using the SQL code editor

  1. Sign in to the AWS Management Console and open the AWS Clean Rooms console at https://console.aws.amazon.com/cleanrooms.

  2. In the left navigation pane, choose Collaborations.

  3. Choose the collaboration that has Your member abilities status of Run queries.

  4. On the Analysis tab, go to the Analysis section.

    Note

    The Analysis section only displays if the member who can receive results and the member who is responsible to pay for query compute costs have joined the collaboration as an active member.

  5. On the Analysis tab, under Tables, view the list of ID mapping tables (under Managed by AWS Clean Rooms) and their associated analysis rule type (ID mapping table analysis rule).

    Note

    If you don’t see the ID mapping tables that you expect in the list, it might be because the ID mapping tables haven't been successfully populated. For more information, see Populating an existing ID mapping table.

  6. Build the query by typing the query into the SQL code editor.

    (Optional) If you want to use an example query (Optional) If you want to insert a table name

    1. Select the three vertical dots next to the table.

    2. Under Insert in editor, choose Example JOIN statement.

      Note

      Inserting an Example JOIN statements appends the query already in the editor.

      The example JOIN statement appears.

    3. Edit the placeholder values in the query.

    1. Select the three vertical dots next to a column.

    2. Under Insert in editor, choose Table name.

    3. Edit the placeholder values in the query.

  7. (Spark analytics engine only) Specify the supported Worker type and the Number of workers.

    Use the following table to determine the type and number or workers you need for your use case.

    Worker type vCPU Memory (GB) Storage (GB) Number of workers Total Clean Rooms Processing Units (CRPU)
    CR.1X (default) 4 30 100 4 8
    128 256
    CR.4X 16 120 400 4 32
    32 256
    Note

    Different worker types and number of workers have associated costs. To learn more about the pricing, see AWS Clean Rooms pricing.

  8. Choose Run.

    Note

    You can't run the query if the member who can receive results hasn’t configured the query results settings.

  9. View the Results.

    For more information, see Receiving and using analysis results.

  10. Continue to adjust parameters and run your query again, or choose the + button to start a new query in a new tab.

Note

AWS Clean Rooms aims to provide clear error messaging. If an error message doesn't have enough details to help you troubleshoot, contact the account team. Provide them with a description of how the error occurred and the error message (including any identifiers). For more information, see Troubleshooting AWS Clean Rooms.