athena create table using python

Question

All:

On trying to create external table using pyathenajdbc with python, it had error as:

def create_tbl(self):
       #res = None
       full_s3_abs_path = self.check_files()
       conn_db = self.conn()
       #try:
       with conn_db.cursor() as cursor:
            cursor.execute(
             """CREATE EXTERNAL TABLE IF NOT EXISTS %s.%s (
                   ord_no              varchar(40),
                   ord_tmstp           varchar(32),
                   ord_hdr_mdfy_tmstp  varchar(32),
                   actn                varchar(32),
                   doc_type            varchar(20),
                   eod_bus_tmstp       varchar(32),
                   eod_msg             varchar(6),
                   entered_by          varchar(254),
                   entry_type          varchar(20),
                   max_ord_sts_desc    varchar(40),
                   min_ord_sts_desc    varchar(40),
                   mdfy_ts             varchar(32),
                   ord_cmplt_fl        varchar(6),
                   ord_purp            varchar(20),
                   ord_sts             varchar(100),
                   ord_hdr_key         varchar(40),
                   cre_user            varchar(40),
                   max_sts_tmstp       varchar(32),
                   grand_ttl           varchar(32),
                   grand_tax           varchar(32),
                   ln_sub_ttl          varchar(32),
                   str_no              varchar(32),
                   ord_src             varchar(32),
                   bp_id               varchar(40),
                   promo_id            varchar(38),
                   web_shper_id        varchar(32),
                   aff_info_site_tag   varchar(256),
                   aff_type            varchar(20),
                   aff_info_tm         varchar(32),
                   tax_exmpt_id        varchar(40)

            )
            ROW FORMAT DELIMITED
            FIELDS TERMINATED BY '\037'
            LOCATION '%s'
            TBLPROPERTIES (
              'skip.header.line.count'='1');""" % (self.sch_name, self.tbl_nm, full_s3_abs_path)
        )
            print "Athena Table %s Created Successfully" % (self.tbl_nm)
       #except:
       #    print "Unable to create DDL for athena table {}".format(self.tbl_nm)
       #    sys.exit(1)
       #finally:
       #  conn_db.close()

when creating an external table using pyathenajdbc driver in python 2.7, below is the error:

"""...('skip.header.line.count'='1');""" % (self.sch_name,self.tbl_nm,full_s3_abs_path) pyathenajdbc.error.DatabaseError: java.sql.SQLException: Failed to run query

Answer 1

Here's a little trick to help when debugging DDL submitted through the CLI or through the SDK in Athena. Try checking the logs in the console (see my reply on this post), to see exactly what your code submitted to the Athena engine.