AWS Athena `dbt` information sheet

athena_dbt.md

1. What options are there for connecting to the warehouse? Eg: ODBC, a python module, etc

   • PyAthena
   • PyAthenaJDBC

2. Is there a consensus around which of the options in #1 is the most featureful/best supported/most mature/etc option?

   • It would most probably be PyAthena, as it does not have any other non-pythonic requirements.

3. Does the warehouse support namespaces/schemas/datasets (or similar)?

   • Yes, it does -- see https://docs.aws.amazon.com/athena/latest/ug/create-database.html

4. Can schemas/namespaces be created using SQL? Eg: create schema my_schema

   • Yes, it does -- see https://docs.aws.amazon.com/athena/latest/ug/create-database.html

5. Does the warehouse support logical databases?

   • I am not exactly certain what this means.

6. Does the warehouse support standard-ish SQL? Are there any noteworthy caveats?

   • As it is based on Presto, it supports "standard-ish" SQL, with some limitations.

   • There are also some further limitations when it comes to Hive DDL.

7. Are transactions supported?

   • No, it does not seem so:

   Athena does not support transaction-based operations (such as the ones found in Hive or Presto) on table data. For a full list of keywords not supported, see Unsupported DDL. -- https://docs.aws.amazon.com/athena/latest/ug/creating-tables.html

8. Can tables be created with create table schema.table as (...)?

   • Yes, see https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html

9. Can views be created with create view schema.view as (...)?

   • Yes, see https://docs.aws.amazon.com/athena/latest/ug/create-view.html

10. Can tables/views be renamed with alter table {table_name} rename to {new_name}?

    • No, see https://docs.aws.amazon.com/athena/latest/ug/unsupported-ddl.html

11. Does the warehouse support insert statements? Any caveats?

    • Not directly (INSERT INTO can be found in https://docs.aws.amazon.com/athena/latest/ug/other-notable-limitations.html) but this is generally worked around using CTAS queries: https://docs.aws.amazon.com/athena/latest/ug/ctas.html

12. Does the warehouse support delete statements? Any caveats?

    • No, it does not seem to.

13. Does the warehouse support update statements? Any caveats?

    • No, it does not seem to, but this is generally worked around using CTAS queries: https://docs.aws.amazon.com/athena/latest/ug/ctas.html

14. Does the warehouse support merge statements? Any caveats?

    • No, it does not seem to.

15. Does the warehouse support drop table/drop view statements? Any caveats?

    • Yes, it does.

    See:

    • https://docs.aws.amazon.com/athena/latest/ug/drop-table.html
    • https://docs.aws.amazon.com/athena/latest/ug/drop-view.html

16. Does the warehouse support truncate statements? Any caveats?

    • No, it does not seem to.

17. Does the warehouse support temporary tables? Any caveats?

    • No, it does not seem to, see https://forums.aws.amazon.com/thread.jspa?threadID=249786

18. Can queries be cancelled?

    • Yes, see cancel method in README: https://github.com/laughingman7743/PyAthena

19. Are views bound to the relations they select from? Ie. do drop table statements require a ...cascade argument?

    • No, it does not seem so. See https://docs.aws.amazon.com/athena/latest/ug/drop-view.html

20. Does the warehouse support querying for existing relations (ie. their existence and type)? Is this via an API call or a SELECT statement? Eg: select * from information_schema.tables.

    • Yes, something like the following works

    SELECT table_name
                FROM information_schema.tables
                WHERE table_schema = 'table_name'

21. Does the warehouse support querying for the the columns in a relation? Is this via an API call or a SELECT statement? Eg: select * from information_schema.columns

    • Yes, something like the following works

    SELECT
                  table_schema,
                  table_name,
                  column_name,
                  data_type,
                  is_nullable,
                  column_default,
                  ordinal_position,
                  comment
                FROM information_schema.columns

22. Can columns be added and removed using DDL? Eg: alter table add column ?

    • No, it does not seem so: https://docs.aws.amazon.com/athena/latest/ug/unsupported-ddl.html

23. Does the warehouse support non-standard performance configurations? Ie. clustering, partitioning, sort/dist keys, etc. What are they, and how are they used? Can they be supplied in create table as statements?

    • Yes, it supports partitioning and bucketing, both of which can be supplied as part of create table as statements: https://docs.aws.amazon.com/athena/latest/ug/create-table-as.html

24. Which column types does the warehouse support? Are text types varchars (with sizes) or unsized string columns? Are numeric types (with fixed precision) supported? Is there a different type for timestamps with timezones? Any caveats?

    • All supported data types (along with potential caveats) can be found here: https://docs.aws.amazon.com/athena/latest/ug/data-types.html

25. Does the warehouse support column-level constraints (eg. unique, not null, foreign key, primary key)? Are they enforced? Can they be defined in create table as statements?

    • No, it does not seem so.

26. Are there other noteworthy quirks/features of the warehouse that have not been specified above? If so: please enumerate them here!