Step 4: Preparing the Amazon Comprehend output for data visualization
To prepare the results of the sentiment and entities analysis jobs for creating data visualizations, you use AWS Glue and Amazon Athena. In this step, you extract the Amazon Comprehend results files. Then, you create an AWS Glue crawler that explores your data and automatically catalogs it in tables in the AWS Glue Data Catalog. After that, you access and transform these tables using Amazon Athena, a serverless and interactive query service. When you have finished this step, your Amazon Comprehend results are clean and ready for visualization.
For a PII entity detection job, the output file is plaintext, not a compressed
archive. The output file name is the same as the input file, with
.out
appended at the end. You don't need the step of extracting the
output file. Skip to
Load the Data into an AWS Glue Data Catalog.
Topics
Prerequisites
Before you begin, complete Step 3: Running analysis jobs on documents in Amazon S3.
Download the Output
The Amazon Comprehend uses Gzip compression to compress output files and save them as a tar
archive. The simplest way to extract the output files is to download the
output.tar.gz
archives locally.
In this step, you download the sentiment and entities output archives.
To find the output files for each job, return to the analysis job in the Amazon Comprehend console. The analysis job provides the S3 location for the output, where you can download the output file.
To download the output files (console)
-
In the Amazon Comprehend console
, in the navigation pane, return to Analysis jobs. -
Choose your sentiment analysis job
reviews-sentiment-analysis
. -
Under Output, choose the link displayed next to Output data location. This redirects you to the
output.tar.gz
archive in your S3 bucket. -
In the Overview tab, choose Download.
-
On your computer, rename the archive as
sentiment-output.tar.gz
. Since all of the output files have the same name, this helps you keep track of the sentiment and entities files. -
Repeat steps 1-4 to find and download the output from your
reviews-entities-analysis
job. On your computer, rename the archive asentities-output.tar.gz
.
To find the output files for each job, use the JobId
from the
analysis job to find the output's S3 location. Then, use the cp
command to download the output file to your computer.
To download the output files (AWS CLI)
-
To list details about your sentiment analysis job, run the following command. Replace
with the sentimentsentiment-job-id
JobId
that you saved.aws comprehend describe-sentiment-detection-job --job-id
sentiment-job-id
If you lost track of your
JobId
, you can run the following command to list all of your sentiment jobs and filter for your job by name.aws comprehend list-sentiment-detection-jobs --filter JobName="reviews-sentiment-analysis"
-
In the
OutputDataConfig
object, find theS3Uri
value. TheS3Uri
value should be similar to the following format:
. Copy this value to a text editor.s3://amzn-s3-demo-bucket/.../output/output.tar.gz
-
To download the sentiment output archive to your local directory, run the following command. Replace the S3 bucket path with the
S3Uri
you copied in the previous step. Replace
with the folder path to your local directory. The namepath/
sentiment-output.tar.gz
replaces the original archive name to help you keep track of the sentiment and entities files.aws s3 cp
s3://amzn-s3-demo-bucket/.../output/output.tar.gz
path/
sentiment-output.tar.gz -
To list details about your entities analysis job, run the following command.
aws comprehend describe-entities-detection-job --job-id
entities-job-id
If you don't know your
JobId
, run the following command to list all of your entities jobs and filter for your job by name.aws comprehend list-entities-detection-jobs --filter JobName="reviews-entities-analysis"
-
From the
OutputDataConfig
object in your entities job description, copy theS3Uri
value. -
To download the entities output archive to your local directory, run the following command. Replace the S3 bucket path with the
S3Uri
you copied in the previous step. Replace
with the folder path to your local directory. The namepath/
entities-output.tar.gz
replaces the original archive name.aws s3 cp
s3://amzn-s3-demo-bucket/.../output/output.tar.gz
path/
entities-output.tar.gz
Extract the output files
Before you can access the Amazon Comprehend results, unpack the sentiment and entities archives. You can use either your local file system or a terminal to unpack the archives.
If you use macOS, double-click the archive in your GUI file system to extract the output file from the archive.
If you use Windows, you can use a third-party tool such as 7-Zip to extract the output files in your GUI file system. In Windows, you must perform two steps to access the output file in the archive. First decompress the archive, and then extract the archive.
Rename the sentiment file as sentiment-output
and the entities
file as entities-output
to distinguish between the output
files.
If you use Linux or macOS, you can use your standard terminal. If you use Windows, you must have access to a Unix-style environment, such as Cygwin, to run tar commands.
To extract the sentiment output file from the sentiment archive, run the following command in your local terminal.
tar -xvf sentiment-output.tar.gz --transform 's,^,sentiment-,'
Note that the --transform
parameter adds the prefix
sentiment-
to the output file inside of the archive, renaming
the file as sentiment-output
. This allows you to distinguish
between the sentiment and entities output files and prevent overwriting.
To extract the entities output file from the entities archive, run the following command in your local terminal.
tar -xvf entities-output.tar.gz --transform 's,^,entities-,'
The --transform
parameter adds the prefix entities-
to the output file name.
Tip
To save storage costs in Amazon S3, you can compress the files again with Gzip before uploading them. It's important to decompress and unpack the original archives because AWS Glue can’t automatically read data from a tar archive. However, AWS Glue can read from files in Gzip format.
Upload the extracted files
After extracting the files, upload them to your bucket. You must store the sentiment and entities output files in separate folders in order for AWS Glue to read the data properly. In your bucket, create a folder for the extracted sentiment results and a second folder for the extracted entities results. You can create folders either with the Amazon S3 console or the AWS CLI.
In your S3 bucket, create one folder for the extracted sentiment results file and one folder for the entities results file. Then, upload the extracted results files to their respective folders.
To upload the extracted files to Amazon S3 (console)
Open the Amazon S3 console at https://console.aws.amazon.com/s3/
. -
In Buckets, choose your bucket and then choose Create folder.
-
For the new folder name, enter
sentiment-results
and choose Save. This folder will contain the extracted sentiment output file. -
In your bucket's Overview tab, from the list of bucket contents, choose the new folder
sentiment-results
. Choose Upload. -
Choose Add files, choose the
sentiment-output
file from your local computer, and then choose Next. -
Leave the options for Manage users, Access for other AWS account, and Manage public permissions as the defaults. Choose Next.
-
For Storage class, choose Standard. Leave the options for Encryption, Metadata, and Tag as the defaults. Choose Next.
-
Review the upload options and then choose Upload.
-
Repeat steps 1-8 to create a folder called
entities-results
, and upload theentities-output
file to it.
You can create a folder in your S3 bucket while uploading a file with the
cp
command.
To upload the extracted files to Amazon S3 (AWS CLI)
-
Create a sentiment folder and upload your sentiment file to it by running the following command. Replace
with the local path to your extracted sentiment output file.path/
aws s3 cp
path/
sentiment-output s3://amzn-s3-demo-bucket/sentiment-results/ -
Create an entities output folder and upload your entities file to it by running the following command. Replace
with the local path to your extracted entities output file.path/
aws s3 cp
path/
entities-output s3://amzn-s3-demo-bucket/entities-results/
Load the data into an AWS Glue Data Catalog
To get the results into a database, you can use an AWS Glue crawler. An AWS Glue crawler scans files and discovers the schema of the data. It then arranges the data in tables in an AWS Glue Data Catalog (a serverless database). You can create a crawler with the AWS Glue console or the AWS CLI.
Create an AWS Glue crawler that scans your sentiment-results
and
entities-results
folders separately. A new IAM role for AWS Glue
gives the crawler permission to access your S3 bucket. You create this IAM
role while setting up the crawler.
To load the data into an AWS Glue Data Catalog (console)
-
Ensure that you're in a Region which supports AWS Glue. If you're in another Region, in the navigation bar, choose a supported Region from the Region selector. For a list of Regions that support AWS Glue, see the Region Table
in the Global Infrastructure Guide. Open the AWS Glue console at https://console.aws.amazon.com/glue/
. -
In the navigation pane, choose Crawlers and then choose Add crawler.
-
For Crawler name, enter
comprehend-analysis-crawler
and then choose Next. -
For Crawler source type, choose Data stores and then choose Next.
-
For Add a data store, do the following:
-
For Choose a data store, choose S3.
-
Leave Connection blank.
-
For Crawl data in, choose Specified path in my account.
-
For Include path, enter the full S3 path of the sentiment output folder:
s3://amzn-s3-demo-bucket/sentiment-results
. -
Choose Next.
-
-
For Add another data store, choose Yes and then choose Next. Repeat Step 6, but enter the full S3 path of the entities output folder:
s3://amzn-s3-demo-bucket/entities-results
. -
For Add another data store, choose No and then choose Next.
-
For Choose an IAM role, do the following:
-
Choose Create an IAM role.
-
For IAM role, enter
glue-access-role
and then choose Next.
-
-
For Create a schedule for this crawler, choose Run on demand and choose Next.
-
For Configure the crawler's output, do the following:
-
For Database, choose Add database.
-
For Database name, enter
comprehend-results
. This database will store your Amazon Comprehend output tables. -
Leave the other options on their default settings and choose Next.
-
-
Review the crawler information and then choose Finish.
-
In the Glue console, in Crawlers, choose
comprehend-analysis-crawler
and choose Run crawler. It can take a few minutes for the crawler to finish.
Create an IAM role for AWS Glue that provides permission to access your S3 bucket. Then, create a database in the AWS Glue Data Catalog. Finally, create and run a crawler that loads your data into tables in the database.
To load the data into an AWS Glue Data Catalog (AWS CLI)
-
To create an IAM role for AWS Glue, do the following:
-
Save the following trust policy as a JSON document called
glue-trust-policy.json
on your computer.{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": "glue.amazonaws.com" }, "Action": "sts:AssumeRole" } ] }
-
To create an IAM role, run the following command. Replace
with your local computer's path to the JSON document.path/
aws iam create-role --role-name glue-access-role --assume-role-policy-document file://
path/
glue-trust-policy.json -
When the AWS CLI lists the Amazon Resource Number (ARN) for the new role, copy and save it to a text editor.
-
Save the following IAM policy as a JSON document called
glue-access-policy.json
on your computer. The policy grants AWS Glue permission to crawl your results folders.{ "Version": "2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:GetObject", "s3:PutObject" ], "Resource": [ "arn:aws:s3:::amzn-s3-demo-bucket/sentiment-results*", "arn:aws:s3:::amzn-s3-demo-bucket/entities-results*" ] } ] }
-
To create the IAM policy, run the following command. Replace
with your local computer's path to the JSON document.path/
aws iam create-policy --policy-name glue-access-policy --policy-document file://
path/
glue-access-policy.json -
When the AWS CLI lists the access policy's ARN, copy and save it to a text editor.
-
Attach the new policy to the IAM role by running the following command. Replace
with the IAM policy ARN you copied in the previous step.policy-arn
aws iam attach-role-policy --policy-arn
policy-arn
--role-name glue-access-role -
Attach the AWS managed policy
AWSGlueServiceRole
to your IAM role by running the following command.aws iam attach-role-policy --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole --role-name glue-access-role
-
-
Create an AWS Glue database by running the following command.
aws glue create-database --database-input Name="comprehend-results"
-
Create a new AWS Glue crawler by running the following command. Replace
with the ARN of your AWS Glue IAM role.glue-iam-role-arn
aws glue create-crawler --name comprehend-analysis-crawler --role
glue-iam-role-arn
--targets S3Targets=[ {Path="s3://amzn-s3-demo-bucket/sentiment-results"}, {Path="s3://amzn-s3-demo-bucket/entities-results"}] --database-name comprehend-results -
Start the crawler by running the following command.
aws glue start-crawler --name comprehend-analysis-crawler
It can take a few minutes for the crawler to finish.
Prepare the data for analysis
Now you have a database populated with the Amazon Comprehend results. However, the results are nested. To unnest them, you run a few SQL statements in Amazon Athena. Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage and it has a pay-per-query pricing model. In this step, you create new tables of cleaned data that you can use for analysis and visualization. You use the Athena console to prepare the data.
To prepare the data
Open the Athena console at https://console.aws.amazon.com/athena/
. -
In the query editor, choose Settings, then choose Manage.
-
For Location of query results, enter
s3://amzn-s3-demo-bucket/query-results/
. This creates a new folder calledquery-results
in your bucket that stores the output of the Amazon Athena queries you run. Choose Save. -
In the query editor, choose Editor.
-
For Database, choose the AWS Glue database
comprehend-results
that you created. -
In the Tables section, you should have two tables called
sentiment_results
andentities_results
. Preview the tables to make sure that the crawler loaded the data. In each table’s options (the three dots next to the table name), choose Preview table. A short query runs automatically. Check the Results pane to ensure that the tables contain data.Tip
If the tables don’t have any data, try checking the folders in your S3 bucket. Make sure that there is one folder for entities results and one folder for sentiment results. Then, try running a new AWS Glue crawler.
-
To unnest the
sentiment_results
table, enter the following query in the Query editor and choose Run.CREATE TABLE sentiment_results_final AS SELECT file, line, sentiment, sentimentscore.mixed AS mixed, sentimentscore.negative AS negative, sentimentscore.neutral AS neutral, sentimentscore.positive AS positive FROM sentiment_results
-
To begin unnesting the entities table, enter the following query in the Query editor and choose Run.
CREATE TABLE entities_results_1 AS SELECT file, line, nested FROM entities_results CROSS JOIN UNNEST(entities) as t(nested)
-
To finish unnesting the entities table, enter the following query in the Query editor and choose Run query.
CREATE TABLE entities_results_final AS SELECT file, line, nested.beginoffset AS beginoffset, nested.endoffset AS endoffset, nested.score AS score, nested.text AS entity, nested.type AS category FROM entities_results_1
Your sentiment_results_final
table should look like the following, with
columns named file, line,
sentiment, mixed,
negative, neutral, and
positive. The table should have one value per cell. The
sentiment column describes the most likely overall sentiment of a
particular review. The mixed, negative,
neutral, and positive columns give scores for
each type of sentiment.
![Screenshot of the sentiment output table in Athena.](/images/comprehend/latest/dg/images/tutorial-reviews-sentiment-table.png)
Your entities_results_final
table should look like the following, with
columns named file, line,
beginoffset, endoffset,
score, entity, and
category. The table should have one value per cell. The
score column indicates Amazon Comprehend's confidence in the
entity it detected. The category indicates
what kind of entity Comprehend detected.
![Console display of the entities output table in Athena.](/images/comprehend/latest/dg/images/tutorial-reviews-entities-table.png)
Now that you have the Amazon Comprehend results loaded into tables, you can visualize and extract meaningful insights from the data.