Aws-data-wrangler: Add more DDL functions for Athena

[bryanyang0528]

Is your feature request related to a problem? Please describe.
DDL queries of Athena are very important and common commands for users, like describe table, show create table, etc. (More DDL queries, please see the official doc: https://docs.aws.amazon.com/athena/latest/ug/language-reference.html)

Describe the solution you'd like
As create_database, create_parquet_table in catelog, repaire_table in athena, add more functions like describe_table, show_create_table in athena.

def describe_table(
    database: str
    table: str
) -> str:

    query = f"describe `{database}`.`{table}`;"
    if (database is not None) and (not database.startswith("`")):
        database = f"`{database}`"
    session: boto3.Session = _utils.ensure_session(session=boto3_session)
    query_id = start_query_execution(
        sql=query,
        database=database,
        s3_output=s3_output,
        workgroup=workgroup,
        encryption=encryption,
        kms_key=kms_key,
        boto3_session=session,
    )
    response: Dict[str, Any] = wait_query(query_execution_id=query_id, boto3_session=session)
    return response["QueryExecution"]["Status"]["State"]

[igorborgest]

Cool! 🚀

[bryanyang0528]

I found that the result of describe table is a text file. If I use s3.read_csv to read that file, the result will be read as a Pandas Dataframe like that:

        col_name           data_type                comment
0      string_id1           string                from deserializer
1      srting_id2           string                from deserializer
2      string_id3           string                from deserializer
3      dt                   string
4                     <NA>
5  # Partition Information
6  # col_name              data_type             comment
7                     <NA>
8     dt                    string

Although the result is directly from Athena, it looks a little wired.
@igorborgest do you have any suggestions on it or this is ok for users?

[igorborgest]

Hi @bryanyang0528!

Hmm.. I'm not sure. I think would be better to return a DataFrame similar to wr.catalog.table() where partitions are identified thought a boolean column.

In addition, I'm wondering if we should get this result directly from the Athena API instead of S3 due the tiny result size. If so, we already have the wr.athena.start_query_execution() and wr.athena.wait_query() functions, and we will only need some other function to abstract the get_query_results() call from Boto3.

What do you think?

[bryanyang0528]

Hi @igorborgest,
Appreciate your suggestions. In this case, instead of getting the result from Athena API directly, now I used the way as you mentioned start_query_execution(), wait_query() to generate the result, and I will use another result parser for reading results from s3 and parsing it. wr.catalog.table() is a good example, I will let the result of describe_table as same as wr.catelog.table for consistency.

[igorborgest]

Hi @bryanyang0528 !

It is totally off-topic, but we are stating a _"Who uses AWS Data Wrangler?"_ section. So feel free to add yourself if you want 😄 .