CODEWITHSUNDEEP
pythonpython-3.xpandasamazon-web-servicesamazon-s3
ASH
ASH

Reputation: 20302

How can we write a dataframe to a table in AWS Athena?

I read through AWS documentation for Pandas, and it seem like you should be able tom write a dataframe to a table in Athena. When I run the code below, I get an error that says 'ValueError: need more than 2 values to unpack'.

import awswrangler as wr
import pandas as pd

df = pd.DataFrame({
"id": [1, 2, 3],
"value": ["foo", "boo", "zoo"],
"name": ["teddy", "timmy", "tommy"]
})
print(df)

wr.s3.to_parquet(
    df=df,
    path='s3://client/awsdatacatalog/',
    dataset=True,
    mode="overwrite",
    database="client_rs",
    table='newtable'
)
wr.s3.read_parquet(path, dataset=True)

Any idea what could be wrong here. This seems pretty straightforward, but nevertheless, it's not working.

https://readthedocs.org/projects/aws-data-wrangler/downloads/pdf/latest/

Upvotes: 0

Views: 2325

Answers (1)

Prabhakar Reddy
Prabhakar Reddy

Reputation: 5124

I tried the same exact code as your and didn't find any issues. Also using this script you are not directly writing to Athena instead you will be writing to Glue catalog from which Athena can read table information and retrieve data from S3.For me this looks like a problem with version, can you use below versions which should fix it for you.

python version used :

prabhakar@MacBook-Pro Downloads % python3 --version
Python 3.9.6
prabhakar@MacBook-Pro D

awsdatawrangler version used:

prabhakar@MacBook-Pro Downloads % pip3 freeze | grep awswrangler
awswrangler==2.16.1
prabhakar@MacBook-Pro Downloads %

And the script I used which is exactly same as yours with different table and database names :

import awswrangler as wr
import pandas as pd

df = pd.DataFrame({
"id": [1, 2, 3],
"value": ["foo", "boo", "zoo"],
"name": ["teddy", "timmy", "tommy"]
})
print(df)

wr.s3.to_parquet(
    df=df,
    path='s3://testing/sf_73750110/',
    dataset=True,
    mode="overwrite",
    database="sampledb",
    table='newtable'
)
df = wr.s3.read_parquet("s3://testing/sf_73750110/", dataset=True)


df = wr.athena.read_sql_query("SELECT * FROM newtable", database="sampledb")
print(df.head())

Below is the output generated where I was able to write parquet table to Glue catalog/s3 and read it using Athena:

enter image description here

Upvotes: 3

Related Questions