Query with Athena

Access Amazon Athena console to check the raw and stage tables created so far.

First, as a Lake Formation admin, you need grant your self permission to query the tables.

Step 1: Go to Lake Formation tables, select each of the tables and grant all permissions to your user or role you are using. Select a table and click Actions -> Grant (permissions).

bp 0

Step 2: Select the grants and your user or role in my case ‘Teamrole’. As the admin, select all the privileges for each table, and Grant.

bp 0

Repeat the process for the following tables:

  • account

  • card

  • crm

  • data

  • gbank

  • sourcemf_sourcemf_public_transactions

  • visitors

Step 3: Go to Amazon S3 console and copy your c360view-us-west-2-account id-stage bucket name.

bp 0

Step 4: Click on the stage bucket to open it.

Step 5: At the top right of the screen mark and Copy the bucket name

bp 0

Step 6: Go to Amazon Athena console and setup your bucket output to your stage bucket on settings.

bp 0

Step 7: Click on the ‘set up a query result location in Amazon S3’ message.

Type s3://< your_stage_bucket_name >/athena-results/ as location path.

bp 0

Click on Save.

Step 8: Choose c360view_raw to check the raw tables.

bp 0

Step 9: Click on the 3 dots at the right of table account, and select Preview table.

bp 0

Step 10: Check the results.

bp 0

Notice that as we had CSV without header the name of the columns where crawled as col0 to col3 in the RAW data. You can edit this on AWS Glue, but our jobs to process the data in Step Functions are already aware of it.

If you go to c360view_stage database you will find a different scenario.

Step 11: Check the other tables data and then go to the c360view_stage database.

bp 0