Call three pre-trained AI services, then query a CSV in S3 with Athena

short · 35 min · Task statement 3.7

Before you start — what this lab needs, and what it can cost

This lab runs in your own AWS account. Work in AWS CloudShell, signed in as a non-root administrative identity: an IAM Identity Center user, or an IAM user with administrator permissions. Never use the root user for this lab. It uses the AWS Free Tier where possible, but charges are possible: Comprehend, Translate and Polly charge by the amount of text they process, Athena charges by the data each query scans, and S3 charges for storage and requests. The amounts here are tiny, and current rates are on each service's pricing page and in the AWS Pricing Calculator. The budget alert you set in lesson 1's lab stays on and emails you if anything here starts to cost money.

The commands use US East (N. Virginia), us-east-1, where all four services are available. Open CloudShell in that Region, or replace us-east-1 throughout and check the AWS Regional Services List first. Replace 111122223333 with your own account ID everywhere it appears (aws sts get-caller-identity --query Account --output text prints it). All the text and data in this lab are invented; do not paste in anything real.

This lab ends with a clean-up. Do it the same day: anything left running in your account is billed to you.

Task

Use three pre-trained AI services from AWS CloudShell, each for the one job it does: Amazon Comprehend to read the sentiment of a sentence, Amazon Translate to turn it into French, and Amazon Polly to read a sentence aloud. Then put a small CSV file of invented sales figures in a private Amazon S3 bucket, record its structure in the AWS Glue Data Catalog, and answer a question about it with one SQL query in Amazon Athena, without loading the data into any database. At the end you can say which service did which step, and why none of it needed a trained model of your own (the job Amazon SageMaker AI exists for).

Steps

Ask Amazon Comprehend, the natural language processing service, what a sentence means. It reads the sentence and returns its sentiment; it does not translate it:

aws comprehend detect-sentiment --language-code en --text 'The delivery was late and the box arrived damaged. I am very disappointed.' --query Sentiment --output text

It prints NEGATIVE. Try a cheerful sentence of your own and see the answer change to POSITIVE. 2. Translate a sentence into French with Amazon Translate, save the result, then ask Comprehend which language the saved text is in:

aws translate translate-text --source-language-code en --target-language-code fr --text 'The delivery was late and the box arrived damaged.' --query TranslatedText --output text > clf-lab20-fr.txt
cat clf-lab20-fr.txt
aws comprehend detect-dominant-language --text "$(cat clf-lab20-fr.txt)" --query 'Languages[0].LanguageCode' --output text

The last command prints fr. Two services, two different jobs: Translate converts language to language, Comprehend analyses what it is given. 3. Turn text into speech with Amazon Polly, the opposite direction to Amazon Transcribe:

aws polly synthesize-speech --output-format mp3 --voice-id Amy --text 'Your order has been dispatched and will arrive tomorrow.' clf-lab20-hello.mp3

In CloudShell, choose Actions, then Download file, enter clf-lab20-hello.mp3 and play it on your computer. You have now used three AI services without training anything: that is what "pre-trained" means. 4. Now the analytics half. Make a private bucket and a small CSV file of invented sales figures, and upload the file under a data/ prefix. New buckets keep S3 Block Public Access on, and this lab leaves it on:

aws s3 mb s3://clf-lab20-111122223333 --region us-east-1
echo 'area,product,units' > sales.csv
echo 'north,kettle,12' >> sales.csv
echo 'south,kettle,8' >> sales.csv
echo 'north,toaster,5' >> sales.csv
echo 'east,toaster,9' >> sales.csv
aws s3 cp sales.csv s3://clf-lab20-111122223333/data/sales.csv

Create a database in the AWS Glue Data Catalog, the central record of what your data looks like:

aws glue create-database --database-input Name=clf_lab20_db

Then describe the CSV file to the catalogue as a table, by running a statement in Athena. Athena writes each query's results to S3, so OUT names a results/ prefix in your bucket:

OUT=OutputLocation=s3://clf-lab20-111122223333/results/
QID=$(aws athena start-query-execution --work-group primary --result-configuration $OUT --query-string "CREATE EXTERNAL TABLE clf_lab20_db.sales (area string, product string, units int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION 's3://clf-lab20-111122223333/data/' TBLPROPERTIES ('skip.header.line.count'='1')" --query QueryExecutionId --output text)
aws athena get-query-execution --query-execution-id $QID --query 'QueryExecution.Status.State' --output text

Repeat the last command until it prints SUCCEEDED. Nothing was copied: the table is only a description in the catalogue that points at the file where it sits. 6. Ask the question in SQL. Athena reads the file in S3 in place, with no servers and no database to load:

QID=$(aws athena start-query-execution --work-group primary --result-configuration $OUT --query-string "SELECT product, SUM(units) AS total_units FROM clf_lab20_db.sales GROUP BY product ORDER BY total_units DESC" --query QueryExecutionId --output text)
aws athena get-query-execution --query-execution-id $QID --query 'QueryExecution.Status.State' --output text
aws athena get-query-results --query-execution-id $QID --query 'ResultSet.Rows[].Data[].VarCharValue' --output text

Run the last command only once the state is SUCCEEDED (repeat the second until it is); the results then show the header, then kettle with 20, then toaster with 14. 7. Open the AWS Glue console, choose Data Catalog, then Tables, and open sales. Its schema lists the three columns Athena now uses, and its location is the data/ prefix of your bucket. This is the pairing the lesson describes: Glue catalogues files in S3, and Athena queries them through that catalogue. A dashboard over these results would be Amazon Quick Sight's job, and a live stream of new sales would be Amazon Kinesis's; neither is needed here.

Verify

In the same CloudShell session (if it timed out, set OUT and re-run step 6 first), read back the catalogue entry and the query's answer:

aws glue get-table --database-name clf_lab20_db --name sales --query 'Table.StorageDescriptor.Columns[].Name' --output text

Expected output: area, product and units, separated by tabs.

aws athena get-query-results --query-execution-id $QID --query 'ResultSet.Rows[1].Data[].VarCharValue' --output text

Expected output: kettle and 20, separated by a tab.

Cost and clean-up

Comprehend, Translate, Polly, Athena and S3 may each charge a very small amount for what this lab does, and the AWS Free Tier covers some of it where your account is eligible. Nothing here charges by the hour, so nothing grows while you read, but the bucket's objects are stored until you delete them. Delete everything below as soon as you have verified the lab.

Created by this lab: the bucket clf-lab20-111122223333, holding data/sales.csv and Athena's query results under results/; the Glue database clf_lab20_db and its table sales; the CloudShell files clf-lab20-fr.txt, clf-lab20-hello.mp3 and sales.csv.

Delete the table and the database from the Glue Data Catalog:

aws glue delete-table --database-name clf_lab20_db --name sales
aws glue delete-database --name clf_lab20_db

Empty and delete the bucket, which removes data/sales.csv and every Athena result under results/ with it:

aws s3 rb s3://clf-lab20-111122223333 --force

Remove the local files from your CloudShell home directory, where they would otherwise stay:

rm -f clf-lab20-fr.txt clf-lab20-hello.mp3 sales.csv

Athena keeps a history of the queries you ran. It is a record, not a resource, and there is nothing to delete.

Check it is gone

aws glue get-databases --query "length(DatabaseList[?Name=='clf_lab20_db'])"

Expected output: 0.

aws s3api list-buckets --query "length(Buckets[?Name=='clf-lab20-111122223333'])"

Expected output: 0.

Notes

If detect-sentiment gives a different answer from the one shown, the sentence was probably less one-sided than intended: Comprehend also returns NEUTRAL and MIXED. The point stands: it analyses meaning, and Translate changes language.

Rekognition (images and video) and Textract (forms and scanned documents) are left out on purpose: both need an image, and this lab uses only text you can see and type.

This is an independent study companion for the AWS Certified Cloud Practitioner (CLF-C02) exam and is not produced by or endorsed by Amazon Web Services. AWS, Amazon Web Services and AWS Certified Cloud Practitioner are trademarks of Amazon.com, Inc. or its affiliates.

This is an independent study companion for AWS Certified Cloud Practitioner CLF-C02 and is not produced by or endorsed by CompTIA.