2

I am trying to write a df into Athena, but the created table is always empty. I use python 3.8 and windows 11 system. I use pyathena writing dataframes to Athena but problems have never occurred till now.

from pyathena import connect
from pyathena.pandas.util import to_sql

conn = connect(aws_access_key_id="KEY",
           aws_secret_access_key="SKEY",
           s3_staging_dir="STAGINGDIR",  # query location dir
           region_name="eu-central-1")

to_sql(df, 
   "TABLENAME", 
   conn, 
   "MYS3PATH",
   schema="MYSCHEMA", 
   index=False, 
   if_exists="replace"
   )
dkantor
  • 162
  • 6

1 Answers1

0

This usually happens when you miss a '/' at the end of the S3 Path. I usually use a function to format the s3 path at right before passing the var to to_sql no matter what just to be sure.

def format_s3_path(s3_path):
    if strip(str(s3_path))[-1] != '/':
        return str(s3_path) + '/'
    return str(s3_path)
to_sql(df, 
   "TABLENAME", 
   conn, 
   format_s3_path("MYS3PATH"),
   schema="MYSCHEMA", 
   index=False, 
   if_exists="replace"
   )

This should work.

Jihjohn
  • 398
  • 4
  • 19
  • I'm a bit confused because it works again. With or without the '/' at the end of a path. – dkantor Aug 08 '22 at 13:44
  • I don't fully understand what you mean. First of all, were you able to write tables? Did you use the code I provided with '/' and the path or did you use `format_s3_path` function in your code? Or did your initial code itself worked without any changes? – Jihjohn Aug 09 '22 at 05:41
  • My code works again without changes. Of course I tried your code and it works too. – dkantor Aug 19 '22 at 06:40
  • Cool. I have a prestodb backend on the athena and whenever I miss the ending '/', it gives me empty tables only. I am confused how it's working for you and didn't work for me – Jihjohn Aug 19 '22 at 09:06
  • That is the 1 million $ question :) . I have no clue. Before I posted this question it had worked perfectly. That time didn't. Now works again. Have you tried it recently? – dkantor Aug 19 '22 at 12:09