def to_redshift(self,
table_name,
s3_bucket,
s3_key,
engine=None,
schema=None,
if_exists="fail",
index=False,
compress=True,
primary_key=None,
aws_access_key_id=None,
aws_secret_access_key=None,
**kwargs):
if not engine:
engine = generate_redshift_engine_string()
if not aws_access_key_id:
aws_access_key_id = os.getenv("AWS_ACCESS_KEY_ID")
if not aws_secret_access_key:
aws_secret_access_key = os.getenv("AWS_SECRET_ACCESS_KEY")
# Get Pandas SQLTable object
table = SQLTable(
table_name,
pandasSQL_builder(engine, schema=schema),
self,
if_exists=if_exists,
schema=schema,
index=index,
)
def quote(s):
return '"' + str(s) + '"'
# Full table name with schema
if schema:
full_table_name = quote(schema) + "." + quote(table_name)
else:
full_table_name = quote(table_name)
# Check table
if table.exists():
if if_exists == "fail":
raise ValueError("Table {} already exists.".format(table_name))
elif if_exists == "append":
queue = [
CopyCommand(
to=table,
data_location="s3://{}/{}".format(s3_bucket, s3_key),
access_key_id=aws_access_key_id,
secret_access_key=aws_secret_access_key,
format="CSV",
compression="GZIP" if compress else None,
)
]
elif if_exists == "replace":
queue = [
"drop table {};".format(full_table_name),
table.sql_schema() + ";",
CopyCommand(
to=table,
data_location="s3://{}/{}".format(s3_bucket, s3_key),
access_key_id=aws_access_key_id,
secret_access_key=aws_secret_access_key,
format="CSV",
compression="GZIP" if compress else None,
),
]
elif if_exists == "update":
staging_table = "{}_staging".format(table_name)
if not primary_key:
raise ValueError(
"Expected a primary key to update existing table")
queue = [
"begin;",
"drop table if exists {};".format(staging_table),
"create temporary table {} (like {});".format(
staging_table, full_table_name),
CopyCommand(
to=table,
data_location="s3://{}/{}".format(s3_bucket, s3_key),
access_key_id=aws_access_key_id,
secret_access_key=aws_secret_access_key,
format="CSV",
compression="GZIP" if compress else None,
),
"delete from {full_table_name} where {primary_key} in (select {primary_key} from {staging_table});"
.format(
full_table_name=full_table_name,
primary_key=primary_key,
staging_table=staging_table,
),
"insert into {} (select * from {});".format(
full_table_name, staging_table),
"end;",
]
else:
raise ValueError("{} is not valid for if_exists".format(if_exists))
else:
queue = [
table.sql_schema() + ";",
CopyCommand(
to=table,
data_location="s3://{}/{}".format(s3_bucket, s3_key),
access_key_id=aws_access_key_id,
secret_access_key=aws_secret_access_key,
format="CSV",
compression="GZIP" if compress else None,
),
]
# Save DataFrame to S3
self.to_s3(bucket=s3_bucket, key=s3_key, index=index, compress=compress)
# Execute queued statements
engine = _engine_builder(engine)
with engine.begin() as con:
for stmt in queue:
con.execute(stmt)
def write(self,
data_frame,
routine_name,
table_name,
bucketname=None,
if_exists='replace',
sub_routine=None):
"""Write data table
:param data_frame: dataframe
:param routine_name: routine name
:param table_name: table name
:param bucketname: bucket name
:param if_exists: method if exists
:param sub_routine: sub routine
:return: None
"""
# todo this function is pretty verbose as it is, please use logger instead of print
# todo make sure log statement is understandable for outside observer
# todo bucketname should always be project_name, redshift should know its own project_name
# todo when table is new, write metadata, but give an option to skip metadata
self.bucket = bucketname
if (table_name != 'meta_database') & (sub_routine is None):
table_name = routine_name + '/' + table_name
elif (table_name == 'meta_database') & (sub_routine is None):
table_name = table_name
else:
table_name = routine_name + '/' + sub_routine + '/' + table_name
print(table_name)
logging.info('Writing table {} :'.format(table_name))
s3 = boto3.resource('s3')
bucket = s3.Bucket(bucketname)
con = psycopg2.connect(self.redshift_path)
con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
cur = con.cursor()
# write DF to string stream
csv_buffer = StringIO()
data_frame.to_csv(csv_buffer, index=None, header=None, sep='|')
# reset stream position
csv_buffer.seek(0)
# create binary stream
gz_buffer = BytesIO()
# compress string stream using gzip
with gzip.GzipFile(mode='w', fileobj=gz_buffer) as gz_file:
gz_file.write(bytes(csv_buffer.getvalue(), 'utf-8'))
# write stream to S3
timestamp = datetime.datetime.strftime(datetime.datetime.now(),
'%Y%m%d%H%M%S')
bucket.put_object(Key='tmp_' + timestamp + '.gz',
Body=gz_buffer.getvalue())
print('saved file ')
# CREATE THE COPY STATEMENT TO SEND FROM S3 TO THE TABLE IN REDSHIFT
s3_path_tmp_file = 's3://{0}/{1}'.format(bucketname,
'tmp_' + timestamp + '.gz')
print('create table')
table = SQLTable(table_name,
pandasSQL_builder(self.engine, schema=None),
data_frame,
if_exists=if_exists,
index=None)
statements = []
if table.exists():
if if_exists == 'fail':
raise ValueError("Table Exists")
elif if_exists == 'append':
statements = []
elif if_exists == 'replace':
statements = [
""" truncate "{}"; rollback; drop table "{}";""".format(
table_name, table_name)
]
else:
raise ValueError("Bad option for `if_exists`")
statements.append(table.sql_schema() + ';')
statement = """
copy "{0}"
from '{1}'
delimiter '{2}'
region 'us-east-1'
CREDENTIALS 'aws_access_key_id={3};aws_secret_access_key={4}'
FORMAT AS CSV NULL AS '@NULL@'
GZIP
TRUNCATECOLUMNS
""".format(table_name, s3_path_tmp_file, '|',
'AKIAIVCDQREXD2TPPRAQ',
'SCemMCgkq1rUruSrIDbFdjorHthnvY6E4j8/UEfg')
statements.append(statement)
try:
logging.info('excucte statement')
for stmt in statements:
print(stmt)
cur.execute(stmt)
# con.commit()
logging.info('finish execute')
except Exception as e:
print(e)
traceback.print_exc(file=sys.stdout)
con.rollback()
raise
s3.Object(bucketname, 'tmp_' + timestamp + '.gz').delete()
logging.info('FILLING THE TABLE IN REDSHIFT')
logging.info('\n--------------- write already -----------------')
def to_redshift(self, table_name, engine, bucket, keypath=None,
schema=None, if_exists='fail', index=True, index_label=None,
aws_access_key_id=None, aws_secret_access_key=None,
columns=None, null_as=None, emptyasnull=True):
"""
Write a DataFrame to redshift via S3
Parameters
=========
table_name : str. (unqualified) name in redshift
engine : SQLA engine
bucket : str; s3 bucket
keypath : str; keypath in s3 (without bucket name)
schema : redshift schema
if_exits : str; {'fail', 'append', 'replace'}
index : bool; include DataFrames index
index_label : bool; label for the index
aws_access_key_id / aws_secret_access_key : from ~/.boto by default
columns : subset of columns to include
null_as : treat these as null
emptyasnull bool; whether '' is null
"""
url = self.to_s3(keypath, engine, bucket=bucket, index=index,
index_label=index_label)
qualname = resolve_qualname(table_name, schema)
table = SQLTable(table_name, pandasSQL_builder(engine, schema=schema),
self, if_exists=if_exists, index=index)
if columns is None:
columns = ''
else:
columns = '()'.format(','.join(columns))
print("Creating table {}".format(qualname))
if table.exists():
if if_exists == 'fail':
raise ValueError("Table Exists")
elif if_exists == 'append':
queue = []
elif if_exists == 'replace':
queue = ['drop table {}'.format(qualname), table.sql_schema()]
else:
raise ValueError("Bad option for `if_exists`")
else:
queue = [table.sql_schema()]
with engine.begin() as con:
for stmt in queue:
con.execute(stmt)
s3conn = boto.connect_s3(aws_access_key_id=aws_access_key_id,
aws_secret_access_key=aws_secret_access_key)
conn = psycopg2.connect(database=engine.url.database,
user=engine.url.username,
password=engine.url.password,
host=engine.url.host,
port=engine.url.port,
sslmode='require')
cur = conn.cursor()
if null_as is not None:
null_as = "NULL AS '{}'".format(null_as)
else:
null_as = ''
if emptyasnull:
emptyasnull = "EMPTYASNULL"
else:
emptyasnull = ''
full_keypath = 's3://' + url
print("COPYing")
stmt = ("copy {qualname} {columns} from '{keypath}' "
"credentials 'aws_access_key_id={key};aws_secret_access_key={secret}' "
"GZIP "
"{null_as} "
"{emptyasnull}"
"CSV;".format(qualname=qualname,
columns=columns,
keypath=full_keypath,
key=s3conn.aws_access_key_id,
secret=s3conn.aws_secret_access_key,
null_as=null_as,
emptyasnull=emptyasnull))
cur.execute(stmt)
conn.commit()
conn.close()
def to_redshift(self, table_name, engine, bucket, keypath=None,
schema=None, if_exists='fail', index=True, index_label=None,
aws_access_key_id=None, aws_secret_access_key=None,
columns=None, null_as=None, emptyasnull=True):
"""
Write a DataFrame to redshift via S3
Parameters
=========
table_name : str. (unqualified) name in redshift
engine : SQLA engine
bucket : str; s3 bucket
keypath : str; keypath in s3 (without bucket name)
schema : redshift schema
if_exits : str; {'fail', 'append', 'replace'}
index : bool; include DataFrames index
index_label : bool; label for the index
aws_access_key_id / aws_secret_access_key : from ~/.boto by default
columns : subset of columns to include
null_as : treat these as null
emptyasnull bool; whether '' is null
"""
url = self.to_s3(keypath, engine, bucket=bucket, index=index,
index_label=index_label)
qualname = resolve_qualname(table_name, schema)
table = SQLTable(table_name, pandasSQL_builder(engine, schema=schema),
self, if_exists=if_exists, index=index)
if columns is None:
columns = ''
else:
columns = '()'.format(','.join(columns))
print("Creating table {}".format(qualname))
if table.exists():
if if_exists == 'fail':
raise ValueError("Table Exists")
elif if_exists == 'append':
queue = []
elif if_exists == 'replace':
queue = ['drop table {}'.format(qualname), table.sql_schema()]
else:
raise ValueError("Bad option for `if_exists`")
else:
queue = [table.sql_schema()]
with engine.begin() as con:
for stmt in queue:
con.execute(stmt)
s3conn = boto.connect_s3(aws_access_key_id=aws_access_key_id,
aws_secret_access_key=aws_secret_access_key)
conn = psycopg2.connect(database=engine.url.database,
user=engine.url.username,
password=engine.url.password,
host=engine.url.host,
port=engine.url.port,
sslmode='require')
cur = conn.cursor()
if null_as is not None:
null_as = "NULL AS '{}'".format(null_as)
else:
null_as = ''
if emptyasnull:
emptyasnull = "EMPTYASNULL"
else:
emptyasnull = ''
full_keypath = 's3://' + url
print("COPYing")
stmt = ("copy {qualname} {columns} from '{keypath}' "
"credentials 'aws_access_key_id={key};aws_secret_access_key={secret}' "
"GZIP "
"{null_as} "
"{emptyasnull}"
"CSV;".format(qualname=qualname,
columns=columns,
keypath=full_keypath,
key=s3conn.aws_access_key_id,
secret=s3conn.aws_secret_access_key,
null_as=null_as,
emptyasnull=emptyasnull))
cur.execute(stmt)
conn.commit()
conn.close()