TWC_STAQ_AWS
Homework:
- AWS UI Login User/Pass: Waiting for systems team to create.
- AWS API Keys to run Copy commands to load redshift from s3 and Unload data from redshift to s3. (Waiting for Systems)
- Tableau Driver to Postgresql for connection to redshift. (See Link Below)
- Redshift Database User/Pass to also connect to Tableau (Temporary ones created below)
- SQLWorkbench/J UI install
- PostgreSQL Docs: http://www.postgresql.org/docs/9.4/static/queries-with.html
- Link to JDBC Driver for Redshift: http://downloads.tableausoftware.com/drivers/tableau/8.2/TableauDrivers.dmg
- Link to SQLWorkbench: http://www.sql-workbench.net/Workbench-Build117.zip
- Link to JDBC Driver jar file for SQLWorkbench: https://jdbc.postgresql.org/download/postgresql-9.4-1200.jdbc41.jar
- Java SDK: http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html
In [150]:
#URL In Caption Below:
#jdbc:postgresql://wfx-nonprod-analytics.citte9qqv5jw.us-east-1.redshift.amazonaws.com:5439/wfxanalytics
Image(url='http://s3.amazonaws.com/wfxnp-dev-robby/screen_redshift.png')
Out[150]:
Temporary user/pass for training
In []:
staq_user='twc_staq'
staq_pass='Lg3@*JKU'
url='wfx-nonprod-analytics.citte9qqv5jw.us-east-1.redshift.amazonaws.com'
database='wfxanalytics'
A. Data Prep and Formatting
- Retrieve file from s3
- Data Prep and formatting
- Upload final data to s3 after splitting and compressing
In [3]:
getfroms3(prefix='staq/staq_sample_output.xlsx')
In [8]:
#Some string formatting
staq_sample = pd.read_excel('staq_sample_output.xlsx')
staq_sample.columns = [col.lower().replace(' ','').rstrip('($)') for col in staq_sample.columns]
staq_sample = staq_sample.replace('-', np.nan)
staq_sample['date'] = staq_sample['date'].apply(lambda x: x.strftime('%Y-%m-%d'))
staq_sample['datevalue'] = staq_sample['datevalue'].apply(lambda x: x.strftime('%Y-%m-%d'))
In [120]:
staq_sample.info()
In [28]:
#String Length review for creating table
staq_sample.applymap(lambda x: len(str(x))).max()
Out[28]:
B. Database Table DDL and Loading
In [75]:
%%file /ebs/staq/staq_sample_ddl.sql
drop table if exists staq.staq_sample;
create table staq.staq_sample(
lineitem varchar(512),
order_name varchar(512),
date date,
lineitemid varchar(64),
orderid varchar(64),
orderponumber varchar(64),
rate double precision,
total_impressions bigint,
total_clicks bigint,
lineitem_date varchar(64),
type_of_inv varchar(64),
director_network varchar(64),
type_of_revenue varchar(32),
metarefresh boolean,
revenue double precision,
placement varchar(64),
lineitem_type varchar(64),
position varchar(64),
cr_size varchar(64),
source varchar(32),
site varchar(64),
property varchar(64),
billed_imps double precision,
datevalue date
)
diststyle key distkey(lineitemid)
sortkey(date, lineitemid, orderid);
In [76]:
%%bash -s "$devhost_rs" "$devport_rs" "$staq_user" "$staq_pass" "$devdb_rs"
PGPASSWORD=$4 psql -h $1 -p $2 -U $3 $5 -f '/ebs/staq/staq_sample_ddl.sql'
In [95]:
%%bash -s "$devhost_rs" "$devport_rs" "$staq_user" "$staq_pass" "$devdb_rs"
PGPASSWORD=$4 psql -h $1 -p $2 -U $3 $5 -c """set search_path to 'staq';\
select * from pg_table_def where schemaname='staq'"""
In [122]:
staq_sample.to_csv('staq_sample.csv', header=None, index=False)
In [123]:
uploadtos3(filepattern='staq_sample.csv', s3path='staq', split=True, compression=True)
Copy command for loading data from s3 to redshift
In [2]:
%%file /ebs/staq/staq_sample_copy.sql
copy staq.staq_sample from 's3://redshift_etl_files/staq/staq_sample.csv'
credentials 'aws_access_key_id=xxx;aws_secret_access_key=xxx'
gzip
csv;
In [126]:
%%bash -s "$devhost_rs" "$devport_rs" "$staq_user" "$staq_pass" "$devdb_rs"
PGPASSWORD=$4 psql -h $1 -p $2 -U $3 $5 -f '/ebs/staq/staq_sample_copy.sql'
Check Final Table in Redshift
In [140]:
querydev("""select * from staq.staq_sample limit 3""")
Out[140]:
In [130]:
from IPython.display import HTML, Javascript, display
In [138]:
js = """
<script type='text/javascript' src='https://online.tableausoftware.com/javascripts/api/viz_v1.js'>\
</script><div class='tableauPlaceholder' style='width: 1024px; height: 768px;'>\
<object class='tableauViz' width='1024' height='768' style='display:none;'>\
<param name='host_url' value='https%3A%2F%2Fonline.tableausoftware.com%2F' /> \
<param name='site_root' value='/t/theweatherchannelllcweatherfx' />\
<param name='name' value='staq_demo/Sheet1' /><param name='tabs' value='no' />\
<param name='toolbar' value='yes' /></object></div>"""
Sample Output File (URL) Displayed as Hosted HTML/JS file from S3 after Pulishing Tableau Extract using Redshift Connection
- Interactive
In [139]:
HTML(js)
Out[139]: