Querying data in a collaboration

As the member who can query, you can do one of the following:

• Build a SQL query manually using the SQL code editor.

• Use the Analysis builder UI to build a query without having to write SQL code.

• Use an approved analysis template.

When the member who can query runs a SQL query on the tables in the collaboration, AWS Clean Rooms assumes the relevant roles to access the tables on their behalf. AWS Clean Rooms applies the analysis rules as necessary to the input query and its output.

AWS Clean Rooms supports SQL queries that can be different than other query engines. For specifications, see the AWS Clean Rooms SQL Reference. If you want to run queries on data tables protected with differential privacy, you should ensure that your queries are compatible with the general-purpose query structure of AWS Clean Rooms Differential Privacy.

Note

When using Cryptographic Computing for Clean Rooms, not all SQL operations generate valid results. For example, you can conduct a COUNT on an encrypted column but conducting a SUM on encrypted numbers leads to errors. In addition, queries might also yield incorrect results. For example, queries that SUM sealed columns produce errors. However, a GROUP BY query over sealed columns seems to succeed but produces different groups than those produced by a GROUP BY query over the cleartext.

The following topics explain how to query data in a collaboration using the AWS Clean Rooms console.

For information about how to query data or view queries by calling the AWS Clean Rooms StartProtectedQuery API operation directly or by using the AWS SDKs, see the AWS Clean Rooms API Reference.

For information about query logging, see Query logging in AWS Clean Rooms.

Note

If you run a query on encrypted data tables, the results from the encrypted columns are encrypted.

For information about receiving query results, see Receiving query results.

Using the SQL code editor

As a member who can query, you can build a query manually by writing SQL code in the SQL code editor. The SQL code editor is located in the Analysis section of the Queries tab in the AWS Clean Rooms console.

The SQL code editor is displayed by default. If you want to use the analysis builder to build queries, see Using the analysis builder.

Important

If you start writing a SQL query in the code editor and then turn on the Analysis builder UI, your query isn't saved.

AWS Clean Rooms supports many SQL commands, functions, and conditions. For more information, see the AWS Clean Rooms SQL Reference.

Tip

If a scheduled maintenance occurs while a query is running, the query is terminated and rolled back. You must restart the query.

To build the query manually using the SQL code editor

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

2. In the left navigation pane, choose Collaborations.

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

4. On the Queries 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 Queries tab, under Tables, view the list of tables and their associated analysis rule type (Aggregation analysis rule, List analysis rule, or Custom analysis rule).

   Note

   If you don’t see the tables that you expect in the list, it might be for the following reasons:

   • The tables haven't been associated.

   • The tables don't have an analysis rule configured.

6. (Optional) To view the table's schema and analysis rule controls, expand the table by selecting the plus sign icon (+).

7. 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 column names or functions

   Select the three vertical dots next to the table. Under Insert in editor, choose Example query. Note Inserting an Example query appends the query already in the editor. The query example appears. All of the tables listed under Tables are included in the query. Edit the placeholder values in the query.

   Select the three vertical dots next to a column. Under Insert in editor, choose Column name. To manually insert a function that is permitted on a column, select the three vertical dots next to a column, select Insert in editor, and then select the name of the permitted function (such as INNER JOIN, SUM, SUM DISTINCT, or COUNT). Press Ctrl + Space to view the table schemas in the code editor. Note Members who can query can view and use the partition columns in each configured table association. Ensure the partition column is labeled as a partition column in the AWS Glue table underlying the configured table. Edit the placeholder values in the query.

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

Using the analysis builder

You can use the analysis builder to build queries without having to write SQL code. With the analysis builder, you can build a query for a collaboration that has:

• A single table that uses the aggregation analysis rule with no JOIN required

• Two tables (one from each member) that both use the aggregation analysis rule

• Two tables (one from each member) that both use the list analysis rule

• Two tables (one from each member) that both use the aggregation analysis rule and two tables (one from each member) that both use the list analysis rule

If you want to manually write SQL queries, see Using the SQL code editor.

The analysis builder appears as the Analysis builder UI option in the Analysis section of the Queries tab in the AWS Clean Rooms console.

Important

If you turn on the Analysis builder UI, start building a query in the analysis builder, and then turn off the Analysis builder UI, your query isn't saved.

Tip

If a scheduled maintenance occurs while a query is running, the query is terminated and rolled back. You must restart the query.

The following topics explain how to use the analysis builder.

Topics

• Use the analysis builder to query a single table (aggregation)
• Use the analysis builder to query two tables (aggregation or list)

Use the analysis builder to query a single table (aggregation)

This procedure demonstrates how to use the Analysis builder UI in the AWS Clean Rooms console to build a query. The query is for a collaboration that has a single table that uses the aggregation analysis rule with no JOIN required.

To use the analysis builder to query a single table

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

2. In the left navigation pane, choose Collaborations.

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

4. On the Queries tab, under Tables, view the table and its associated analysis rule type. (The analysis rule type should be the Aggregation analysis rule.)

   Note

   If you don’t see the table you expect, it might be for the following reasons:

   • The table hasn't been associated.

   • The table doesn't have an analysis rule configured.

5. Under the Analysis section, turn on Analysis builder UI.

6. Build a query.

   If you want to see all of the aggregation metrics, skip to step 9.

   1. For Choose metrics, review the aggregate metrics that have been preselected by default and remove any metric if needed.

   2. (Optional) For Add segments – optional, choose one or more parameters.

      Note

      Add segments – optional is only displayed if dimensions are specified for the table.

   3. (Optional) For Add filters – optional, choose Add filter, and then choose a Parameter, operator, and Value.

      To add more filters, choose Add another filter.

      To remove a filter, choose Remove.

      Note

      ORDER BY is not supported for aggregation queries.

      Only the AND operator is supported in filters.

   4. (Optional) For Add description – optional, enter a description to help identify the query in the list of queries.

7. Expand Preview SQL code.

   1. View the SQL code that is generated from the analysis builder.

   2. To copy the SQL code, choose Copy.

   3. To edit the SQL code, choose Edit in SQL code editor.

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

Use the analysis builder to query two tables (aggregation or list)

This procedure describes how to use the analysis builder in the AWS Clean Rooms console to build a query for a collaboration that has:

• Two tables (one from each member) that both use the aggregation analysis rule

• Two tables (one from each member) that both use the list analysis rule

• Two tables (one from each member) that both use the aggregation analysis rule and two tables (one from each member) that both use the list analysis rule

To use the analysis builder to query two tables

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

2. In the left navigation pane, choose Collaborations.

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

4. On the Queries tab, under Tables, view the two tables and their associated analysis rule type (Aggregation analysis rule or List analysis rule).

   Note

   If you don’t see the tables you expect in the list, it might be for the following reasons:

   • The tables haven't been associated.

   • The tables don't have an analysis rule configured.

5. Under the Analysis section, turn on Analysis builder UI.

6. Build a query.

   If the collaboration contains two tables that use the Aggregation analysis rule and two tables that use the List analysis rule, first choose Aggregation or List, and then follow the prompts based on the selected analysis rule.

   If the two tables use the aggregation analysis rule

   If the two tables use the list analysis rule

   For Choose metrics, review the aggregate metrics that have been preselected by default and remove any metric if needed. For Match records, choose one or more records. Note When using the analysis builder, you can match only on a single pair of columns. (Optional) For Add segments – optional, choose one or more parameters. Note Add segments – optional is only displayed if dimensions are specified for the table. (Optional) For Add filters – optional, choose Add filter, and then choose a parameter, operator, and value. To add more filters, choose Add another filter. To remove a filter, choose Remove. Note ORDER BY is not supported for aggregation queries. Only the AND operator is supported in filters. (Optional) For Add description – optional, enter a description to help identify the query in the list of recent queries.

   For Choose attributes, review the list attributes that have been preselected by default and remove any metric if needed. For Match records, choose one or more records. Note When using the analysis builder, you can match only on a single pair of columns. (Optional) For Add filters – optional, choose Add filter, and then choose a parameter, operator, and value. To add more filters, choose Add another filter. To remove a filter, choose Remove. Note LIMIT is not supported for list queries. Only the AND operator is supported in filters. (Optional) For Add description – optional, enter a description to help identify the query in the list of recent queries.

7. Expand Preview SQL code.

   1. View the SQL code that is generated from the analysis builder.

   2. To copy the SQL code, choose Copy.

   3. To edit the SQL code, choose Edit in SQL code editor.

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

Querying data with differential privacy

In general, writing and running queries does not change when differential privacy is turned on. However, you can't run a query if there is not enough privacy budget remaining. As you run queries and consume the privacy budget, you can see approximately how many aggregations you can run and how that might impact future queries.

To view the impact of differential privacy in a collaboration

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

2. In the left navigation pane, choose Collaborations.

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

4. On the Queries tab, under Tables, view the remaining privacy budget. This is displayed as the estimated number of aggregation functions remaining and the Utility used (rendered as a percentage).

   Note

   The estimated number of aggregate functions remaining and the percentage of the Utility used only display for the member who can query.

5. Choose View impact to view how much noise is injected into the results and approximately how many aggregation functions you can run.

Viewing recent queries

You can view the queries that ran in the last 90 days on the Recent queries tab.

Note

If your only member ability is Contribute data, and you are not the member paying for query compute costs, the Queries tab doesn't appear on the console.

To view recent queries

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

2. In the left navigation pane, choose Collaborations.

3. Choose a collaboration.

4. On the Queries tab, under Queries, view the queries that have been run in the last 90 days.

5. To sort recent queries by Status, select a status from the All statuses dropdown list.

   The statuses are: Submitted, Started, Cancelled, Success, Failed, and Timed out.

Viewing query details

You can view the query details as the member who can run queries or as a member who can receive results.

To view the details of the query

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

2. In the left navigation pane, choose Collaborations.

3. Choose a collaboration.

4. On the Queries tab, do one of the following:

   • Choose the option button for the specific query you want to view, and then choose View details.

   • Choose the Protected query ID.

5. On the Query details page,

   • If you are the member who can run queries, view the Query details, SQL text and Results.

     You see a message confirming that the query results were delivered to the member who can receive results.

   • If you are the member who can receive results, view the Query details and Results.