{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::adjust-sample-001"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject"
            ],
            "Resource": [
                "arn:aws:s3:::adjust-sample-001/*"
            ]
        }
    ]
}

create TABLE *table_name* (
  app_name VARCHAR
  , app_version VARCHAR
  , tracker VARCHAR
  , tracker_name VARCHAR(512)
  , network_name VARCHAR
  , campaign_name VARCHAR
  , adgroup_name VARCHAR
  , creative_name VARCHAR
  , click_referer VARCHAR(4096)
  , activity_kind VARCHAR
  , click_time BIGINT
  , impression_time BIGINT
  , engagement_time BIGINT
  , installed_at BIGINT
  , created_at BIGINT
  , reattributed_at BIGINT
  , uninstalled_at BIGINT
  , reinstalled_at BIGINT
  , connection_type VARCHAR
  , cpu_type VARCHAR
  , hardware_name VARCHAR
  , network_type VARCHAR
  , device_manufacturer VARCHAR
  , adid VARCHAR
  , idfa VARCHAR
  , android_id VARCHAR
  , idfv VARCHAR
  , user_agent VARCHAR(512)
  , ip_address VARCHAR
  , isp VARCHAR
  , region VARCHAR
  , country VARCHAR
  , country_subdivision VARCHAR
  , city VARCHAR
  , language VARCHAR
  , device_name VARCHAR
  , device_type VARCHAR
  , os_name VARCHAR
  , sdk_version VARCHAR
  , os_version VARCHAR
  , random BIGINT
  , nonce VARCHAR
  , random_user_id VARCHAR
  , timezone VARCHAR
  , event VARCHAR
  , event_name VARCHAR
  , is_reattributed BOOLEAN
  , deeplink VARCHAR(4096)
  , revenue VARCHAR
  , currency VARCHAR
  , reporting_revenue VARCHAR
  , reporting_currency VARCHAR
  , cost_type VARCHAR
  , cost_amount FLOAT
  , cost_currency VARCHAR
  , reporting_cost FLOAT
  , push_token VARCHAR(512)
  , label VARCHAR
  , adwords_campaign_name VARCHAR
  , fb_campaign_group_name VARCHAR
  , fb_campaign_group_id BIGINT
  , fb_campaign_name VARCHAR(512)
  , fb_campaign_id BIGINT
  , tweet_id VARCHAR
  , iad_creative_set_name VARCHAR
  , publisher_parameters VARCHAR
);

copy *table_name*
FROM 's3://adjust-sample-001/3ilqwusnz62o_2020-05-19'
credentials 'aws_iam_role=*Redhsiftに設定済みのS3へアクセスするためのIAMロール(既存設定)*'
gzip
delimiter ','
csv
ignoreheader 1
region 'ap-northeast-1';

# PostgreSQLへ接続するためにpsycopg2を利用する
import psycopg2
from datetime import datetime, timedelta

yesterday = (datetime.today() - timedelta(days=1)).strftime('%Y-%m-%d')

# ログファイル
log_file = '/home/ubuntu/project/project_analytics/copy_log/log_{}'.format(yesterday)


def create_log(string):
    with open(log_file, 'w') as f:
        f.write(string + '\n')


def add_log(string):
    with open(log_file, 'a') as f:
        f.write(string + '\n')


# Redshift接続情報
hostName = "Redshiftのホスト名"
databaseName = "RedshiftのDB名"
portNo = "5439"
userName = "Redshiftのユーザー名"
password = "Redshiftのパスワード"

# Connectionオブジェクト作成
conn = psycopg2.connect(
    host=hostName,
    database=databaseName,
    port=portNo,
    user=userName,
    password=password
)
create_log('psycopg2 connected')

# SQL文を実行するためにはConnectionオブジェクトからさらにCursorオブジェクトを作成
cur = conn.cursor()
add_log('psycopg2 cursor opened.')

# ファイル名が昨日の日付のcsvファイルをまとめて抽出対象とする
file = 's3://adjust-sample-001/3ilqwusnz62o_{}'.format(yesterday)
# copy文
copy_statement = """
COPY *table_name* 
FROM '{}'
credentials 'aws_iam_role=*Redhsiftに設定済みのS3へアクセスするためのIAMロール(既存設定)*'
gzip
delimiter ','
csv
ignoreheader 1
region 'ap-northeast-1'
""".format(file)
add_log(copy_statement)

# Cursorオブジェクトのexecute()メソッドでSQL文を実行
add_log('COPY start.')
cur.execute(copy_statement)
add_log('COPY end.')
# 変更をDBに保存
conn.commit()
# コネクションのクローズ
cur.close()
add_log('psycopg2 cursor closed')
conn.close()
add_log('psycopg2 connection closed.')

0 10 * * * python3 /home/ubuntu/project/project_analytics/copy_script_from_adjust.py

$ crontab cron.conf