CODEWITHSUNDEEP
amazon-athena
RhinoH
RhinoH

Reputation: 25

Can I query a list of s3 path using athena?

I have a list of s3 paths stored in a dynamodb table. For example, the user data on April 2nd was stored in s3://bucket/2022/04/02/user__2022-04-02.json.gz. I can query the dynamo DB table using Athena to get a list of s3 paths. How can I use that list of s3 paths to create a table in Athena and query that table?

Upvotes: 0

Views: 388

Answers (1)

Jeremy Fortune
Jeremy Fortune

Reputation: 2499

If you're looking to automatically regenerate a Glue table with the output of an Athena query using only Athena, that is in the realm of stored procedures and you're out of luck.

However, you can certainly use the result of an Athena query with some client that can process the result to add partitions to a table. For example, using powershell:

$result = Get-ATHQueryResult "<some-guid>"
$partitions = $result.Rows.Data.VarCharValue 
  | Select-Object -Skip 1  # Skips the header
  | ForEach-Object { 
    "alter table <your-table> add partition (<partition_key>=<some_value>) location '$_';" 
  }

This presumes that you have set up a structure for the table and that the data represented in s3://bucket/2022/... shares a similar structure. You can then use the same client to issue the resulting commands to Athena so the partitions are added.

Upvotes: 1

Related Questions