anhdanggit

##-------------------------------------#
## BankStat Prep
## PARSE BANK ACCOUNT AND DOC DATE 
## This script to parse the info of bank account iban and the doc date for bank statement
##-------------------------------------#


# --- FUNCTIONS ---------------

anhdanggit

-- Search for a columns in DB
SELECT      COLUMN_NAME AS "ColumnName",
            TABLE_NAME AS  "TableName",
            TABLE_SCHEMA
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE 'cashfac_id'
ORDER BY    TABLE_NAME,
            COLUMN_NAME;

-- List all columns one table

anhdanggit

{
	"Header": {
		"prefix": "header",
		"body": [
			"'''",
			"#FILE: $1",
			"Project: $WORKSPACE_NAME",
			"-------------------",
			"By: Anh Dang",
			"Date: $CURRENT_YEAR-$CURRENT_MONTH-$CURRENT_DATE",

anhdanggit

library(RJDBC)
library(rJava)
library(DBI)

# 1 - Set up driver connection to JDBC
fil = '~/<paht-to>/AthenaJDBC42_2.0.7.jar' ##path to the AthenaJDBC in your machine
drv <- JDBC(driverClass='com.simba.athena.jdbc.Driver', 
            classPath = fil, 
            identifier.quote="'")

anhdanggit

import pyathena
import pandas as pd

## Directly by panda
athena_conn = pyathena.connect(aws_access_key_id=os.environ['ATHENA_USER'], ##credentials of aws_access_key_id
                 aws_secret_access_key=os.environ['ATHENA_PASSWORD'], ##credentials of aws_secret_access_key
                 s3_staging_dir='s3://aws-athena-query-results-<your-details>', ##where the athena query result saved - checked in S3 ,
                 region_name='eu-west-1') ##the region you set for Athena

df = pd.read_sql("SELECT * FROM tutorial.wbcdata LIMIT 10", athena_conn)