Add AWS Athena as an adapter

[mrshu]

Feature

Add AWS Athena as an adapter

Feature description

AWS Athena is a pretty popular querying engine. In combination with dbt its use could get much more powerful and could make it a viable data warehousing alternative (with all the engineering batteries included).

Who will this benefit?

Vast amount of AWS Athena users

---

Hi guys,

First of all, thanks a ton for making dbt -- it is something (along with the engineering perspective around data) that was sorely needed!

Although I realize this feature request will very probably be postponed until #559 and #1106 are resolved (as it is a stripped-down combination of Hive and Presto, respectively), I would still like to offer this mostly complete information sheet on AWS Athena: https://gist.github.com/mrshu/fff58fad980581f01991048a611ff5a1

If you believe adding Athena as an adapter may not be that difficult, I'd be happy to help put the adapter together myself.

Thanks again!

[drewbanin]

Thanks for filling out the questionnaire, @mrshu. This looks really comprehensive!

Our plan is indeed to tackle Presto first (starting this week!). Building a Presto adapter will help us "bookend" our adapters-as-plugins work, which should make it much simpler to create new adapters. Once that work is complete, new adapters will be able to live outside of the dbt-core repo itself, meaning that you could conceivably prototype something like this independently from us!

That said, we'd love to provide first-class support for Athena, and it would be a great candidate for our second adapter after Presto-proper. My guess is that the Athena adapter will look pretty similar to the Presto adapter, with the key difference being the connection logic.

The other issue to keep an eye on is #560. Once we've built the Presto adapter, we'll go through and document the contract for adapters. This has historically been informally defined, so I'm looking forward to documenting this once the API is solid.

Thanks again! Will keep you apprised of updates :)

[mrshu]

Thanks a ton for your quick answer @drewbanin!

That said, we'd love to provide first-class support for Athena, and it would be a great candidate for our second adapter after Presto-proper. My guess is that the Athena adapter will look pretty similar to the Presto adapter, with the key difference being the connection logic.

I kind of hoped this would be the case -- thanks for confirming that :)

I'd be happy to join forces with you guys and make sure whatever I prototype would be then useful to you as well. With a solid API that should not be such a big problem.

Thanks again and please keep me posted!

[mrshu]

Hey @drewbanin,

It seems that thanks to 477699a there already is a way of adding various adapters to dbt. I would therefore like to ask the same quesiton: what would it currently take to create an AWS Athena adapter? I'd be happy to quickly put a prototype together.

Thanks!

[drewbanin]

Cool @mrshu! Check out the docs on creating a new adapter plugin here: https://docs.getdbt.com/docs/building-a-new-adapter

And see also the presto plugin here: https:/fishtown-analytics/dbt-presto

I imagine the Athena plugin will look pretty similar to the presto plugin. The only differences should be around connecting to the database AFAIK!

[drewbanin]

FYI for plugin maintainers: #1655

[Dandandan]

Athena adapter:

https:/Dandandan/dbt-athena/

[drewbanin]

closing this one - out of scope for core

@Dandandan we can get this athena plugin up on docs.getdbt.com -- are you interested in that?

[Dandandan]

@drewbanin sounds great! What would be the first steps for this?

[drewbanin]

@Dandandan awesome! Feel free to submit a PR that:

1. Adds the athena plugin to this folder (you can follow the format for existing plugins)
2. Adds a link to this new file in the sidebar

If all goes well, you should be able to see the new plugin in a local build of the docs!

Check out some existing examples over here, and lmk if there's anything in particular i can help out with: https://docs.getdbt.com/docs/supported-databases/profile-mssql

[Dandandan]

Thanks @drewbanin, working on it!
Can we also create a athena channel in Slack?

[Sherm4nLC]

In relation to this amazing work -- The readme states that incremental models are not supported due to the nature of athena, however, since late last year Athena does support insert into, so, presumably incremental models could be supported, or there's another blocker?
Thanks a lot!

[Dandandan]

There is no real blocker, but it currently the same limitations as the presto adapter (as dbt-athena is based on the presto adapter).

See here for some implementation ideas:

dbt-labs/dbt-presto#5

[ags2121]

would love to see this in the docs! just chiming in to say it'd be nice to see some examples of how to use this adaptor in the readme.

[drewbanin]

sure thing @Dandandan - check out #athena in Slack :)