Quickstart: Query data in Amazon S3

Configure a query result location

1. Go to the Athena console: https://console.amazonaws.cn/athena/.

2. From the left menu, choose Workgroups.

3. Follow the link for the primary workgroup and choose Edit.

4. In the Query result configuration section, enter the Amazon S3 path for your output directory and then choose Save changes.

Create an Athena data source for your Amazon S3 data

1. From the left menu in the Athena console, choose Data sources and then Create Data Source.

2. Choose S3 - Amazon Glue Data Catalog and then Next.

3. Leave the default Amazon Glue Data Catalog in this account, choose Create a crawler in Amazon Glue and then Create in Amazon Glue. This opens the Amazon Glue console.

Use Amazon Glue to crawl your data source

1. Enter a name and a description for your new crawler and then choose Next.

2. Under Data Sources, choose Add a data source.

   1. If the Amazon Amazon S3 bucket containing your data is in a different Amazon account than your SageMaker environment, choose In a different account for the Location of the S3 data.

   2. Enter the path to your dataset in Amazon S3. For example:

      s3://dsoaws/nyc-taxi-orig-cleaned-split-parquet-per-year-multiple-files/ride-info/year=2019/

   3. Keep all other default values and then choose Add an Amazon S3 data source. You should see a new Amazon S3 data source in the data sources table.

   4. Choose Next.

3. Configure the IAM role for the crawler to access your data.

   Note

   Each role is scoped down to the data source you specify. When reusing a role, edit the JSON policy to add any new resource you want to grant access to or create a new role for this data source.

   1. Choose Create new IAM role.

   2. Enter a name for the role and then choose Next.

Create or select a database for your tables

1. If you do not have an existing database in Athena, choose Add database and then Create a new database.

2. Back to your previous crawler creation tab, in Output configuration, choose the Refresh button. You should now see your newly created database in the list.

3. Select your database, add an optional prefix in Table name prefix and then choose Next.

   Note

   For the previous example where your data is located at s3://dsoaws/nyc-taxi-orig-cleaned-split-parquet-per-year-multiple-files/ride-info/year=2019/, adding the prefix taxi-ride- will create a table named taxi-ride-year_2019. Adding a prefix helps prevent table name collisions when multiple data locations have identically named folders.

Choose Create crawler.

Run your crawler to index your data. Wait for the crawler run to reach a Completed status, which may take a few minutes.

Retrieve the ARN of the execution role associated with your user profile

1. Go to the SageMaker console at https://console.amazonaws.cn/sagemaker/ and choose Domains in the left menu.

2. Follow the name for your domain name.

3. In the User profiles list, follow the name for your user profile.

4. On the User details page, copy the ARN of the execution role.

Update the policy of your execution role

1. Find your Amazon region and account ID at the top right of the SageMaker console. Use these values and your database name to update the placeholders in the following JSON policy in a text editor.

   {
   	"Version": "2012-10-17",
   	"Statement": [
   		{
   			"Sid": "GetS3AndDataSourcesMetadata",
   			"Effect": "Allow",
   			"Action": [
   				"glue:GetDatabases",
   				"glue:GetSchema",
   				"glue:GetTables",
   				"s3:ListBucket",
   				"s3:GetObject",
   				"s3:GetBucketLocation",
   				"glue:GetDatabase",
   				"glue:GetTable",
   				"glue:ListSchemas",
   				"glue:GetPartitions"
   			],
   			"Resource": [
   				"arn:aws:s3:::*",
   				"arn:aws:glue:region:account-id:catalog",
   				"arn:aws:glue:region:account-id:database/db-name"
   			]
   		},
   		{
   			"Sid": "ExecuteAthenaQueries",
   			"Effect": "Allow",
   			"Action": [
   				"athena:ListDataCatalogs",
   				"athena:ListDatabases",
   				"athena:ListTableMetadata",
   				"athena:StartQueryExecution",
   				"athena:GetQueryExecution",
   				"athena:RunQuery",
   				"athena:StartSession",
   				"athena:GetQueryResults",
   				"athena:ListWorkGroups",
   				"s3:ListMultipartUploadParts",
   				"s3:ListBucket",
   				"s3:GetBucketLocation",
   				"athena:GetDataCatalog",
   				"s3:AbortMultipartUpload",
   				"s3:GetObject",
   				"s3:PutObject",
   				"athena:GetWorkGroup"
   			],
   			"Resource": [
   				"arn:aws:s3:::*"
   			]
   		},
   		{
   			"Sid": "GetGlueConnectionsAndSecrets",
   			"Effect": "Allow",
   			"Action": [
   				"glue:GetConnections",
   				"glue:GetConnection"
   			],
   			"Resource": [
   				"*"
   			]
   		}
   	]
   }

2. Go to the IAM console: https://console.amazonaws.cn/iam/ and choose Roles in the left menu.

3. Search for your role by role name.

   Note

   You can retrieve an execution role name from its Amazon Resource Name (ARN) by splitting the ARN on '/' and taking the last element. For example, in the following example of an ARN arn:aws:iam::112233445566:role/SageMakerStudio-SQLExtension-ExecutionRole, the name of the execution role is SageMakerStudio-SQLExtension-ExecutionRole.

4. Follow the link for your role.

5. In the Permissions tab, choose Add permissions then Create inline policy.

6. Choose the JSON format in the Policy editor section.

7. Copy the policy above and then choose Next. Ensure that you have replaced all the account-id, region-name, and db-name with their values.

8. Enter a name for your policy and then choose Create policy.

Go to the SageMaker console at https://console.amazonaws.cn/sagemaker/ and choose Studio in the left menu. Using your domain and user profile, launch Studio.

Choose the JupyterLab application.

If you have not created a space for your JupyterLab application, choose Create a JupyterLab space. Enter a name for the space, keep the space as Private, and then choose Create space. Run your space using the latest version of the SageMaker Distribution image.

Otherwise, choose Run space on your space to launch a JupyterLab application.

Enable Athena default connection:

1. In your JupyterLab application, navigate to the Settings menu in the top navigation bar and open the Settings Editor menu.

2. Choose Data Discovery.

3. Check the box for Enable default Athena connection.

4. In your JupyterLab application, choose the icon in the left navigation pane to open the SQL extension.

5. Choose the Refresh button at the bottom of the data discovery panel. You should see a default-athena-connection in the list of connections.

Open the connection default-athena-connection and then AmazonDataCatalog.

Navigate to your database and choose the three dots icon ( ) on its right. Select Query in notebook.

This automatically populates a notebook cell in JupyterLab with the relevant %%sm_sql magic command to connect to the data source. It also adds a sample SQL statement to help you start querying right away.

You can further refine the SQL query using the auto-complete and highlighting features of the extension. See SQL editor features of the JupyterLab SQL extension for more information on using the SQL extension SQL editor.