1

I would like to create via Terraform an Athena database including tables and views. I have already searched a lot and found some posts, e.g. here: Create AWS Athena view programmatically

I know that I can use Terraform provisioners to execute AWS CLI commands to create these resources, for example like this: AWS Athena Create table view with SQL

But I don't want to do that. I want to create everything (as far as possible) with Terraform so that I don't have to worry about lifecycle etc.

As far as I understand, an Athena database can be a Glue database, depending on the source you choose. If I choose the AWSDataCatalog (Glue) as data source in Athena, it should not matter if I create an Athena database or a Glue database with Terraform, correct?

In Glue I can also create tables, but no views. Do the Glue tables automatically correspond to Athena tables? How can I create Athena views? I would like to create everything with SQL DDL, just like you can do it in the AWS Web Console. How does this work via Terraform? If this functionality is not available, what is the best way to go? I am grateful for every tip and help!

Stefan
  • 1,253
  • 2
  • 12
  • 36
  • 1
    If you want to create the tables with Terraform directly (and not via running a separate one off command via the AWS CLI or something else) then you'd need to create Glue tables which don't take SQL DDL. Because there's not really a direct way to turn SQL DDL into a Glue table via the API and then manage it as a long lived resource (you'd need to create and run a query and then somehow map the reading of the Glue catalog back to the state) then I don't think it's something that can be easily added either. I personally have Glue crawlers create the schema for tables instead. – ydaetskcoR Oct 06 '20 at 12:20
  • Ok, actually it does not matter if it works via SQL DDL or not. Important is that it is going to be a terraform resource. How can I define a view? I have defined a view via the Web Console and I don't see any information in the properties about the data source. So which attributes are used from which tables, etc. I also don't really see anything via the AWS CLI, except column names and TableType "VIRTUAL_VIEW". How can I provide this information in a view? How does Athena know where this data comes from? – Stefan Oct 06 '20 at 18:45
  • Does this answer your question? [Glue Virtual View (terraform created) not appearing in Athena](https://stackoverflow.com/questions/60660158/glue-virtual-view-terraform-created-not-appearing-in-athena) – Theo Oct 06 '20 at 20:08

1 Answers1

3

Athena uses the Glue Data Catalog to store metadata about databases, tables, and views. All Athena tables are Glue tables. However, not all Glue tables work with Athena – you can create tables in Glue that won't be visible in Athena, and you can create tables that will be visible but won't work (for example cause runtime errors when you query them).

Athena uses Glue Data Catalog for views, but the format is very specific to Athena, unlike regular tables which can be made interoperable with for example Spark.

In an answer to the question you link to I explain in detail the anatomy of an Athena view. I have created views with CloudFormation with that information so it can be done with Terraform too. Unless you write code you will have to jump through all the hoops and repeat most of the information as Presto metadata, unfortunately.

Theo
  • 131,503
  • 21
  • 160
  • 205