Pyathena to_sql creates empty tables

Question

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"
   )

user_12856596 · Answer

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.

Pyathena to_sql creates empty tables

1 Answers