Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usr/bin/python
- # -*- coding: utf-8 -*-
- import sys
- import getopt
- from datetime import datetime
- from sqlalchemy import create_engine
- import pandas as pd
- if __name__ == "__main__":
- unixOptions = "s:e"
- gnuOptions = ["start_dt=", "end_dt="]
- fullCmdArguments = sys.argv
- argumentList = fullCmdArguments[1:]
- try:
- arguments, values = getopt.getopt(argumentList, unixOptions, gnuOptions)
- except getopt.error as err:
- print (str(err))
- sys.exit(2)
- start_dt = ''
- end_dt = ''
- for currentArgument, currentValue in arguments:
- if currentArgument in ("-s", "--start_dt"):
- start_dt = currentValue
- elif currentArgument in ("-e", "--end_dt"):
- end_dt = currentValue
- db_config = {'user': 'my_user','pwd': 'my_user_password','host': 'localhost','port': 5432,'db': 'zen'}
- connection_string = 'postgresql://{}:{}@{}:{}/{}'.format(db_config['user'],
- db_config['pwd'],
- db_config['host'],
- db_config['port'],
- db_config['db'])
- engine = create_engine(connection_string)
- query = '''SELECT event_id, age_segment, event, item_id, item_topic,item_type, source_id, source_topic, source_type,TO_TIMESTAMP(ts / 1000) AT TIME ZONE 'Etc/UTC' as dt, user_id
- FROM log_raw
- WHERE TO_TIMESTAMP(ts / 1000) AT TIME ZONE 'Etc/UTC' BETWEEN '{}'::TIMESTAMP AND '{}'::TIMESTAMP
- '''.format(start_dt, end_dt)
- log_raw = pd.io.sql.read_sql(query, con = engine, index_col = 'event_id')
- print(log_raw.head(5))
- log_raw['dt'] = pd.to_datetime(log_raw['dt']).dt.round('min')
- dash_visits = log_raw.groupby(['item_topic','source_topic','age_segment','dt']).agg({'user_id':'count'})
- dash_visits = dash_visits.rename(columns={'user_id':'visits'})
- print(dash_visits.head(5))
- dash_engagement = log_raw.groupby(['dt','item_topic','event','age_segment']).agg({'user_id':'nunique'})
- dash_engagement = dash_engagement.rename(columns={'user_id':'unique_users'})
- print(dash_engagement.head(5))
- tables = {'dash_visits': dash_visits,
- 'dash_engagement': dash_engagement}
- for table_name, table_data in tables.items():
- query = '''
- DELETE FROM {} WHERE dt BETWEEN '{}'::TIMESTAMP AND '{}'::TIMESTAMP
- '''.format(table_name, start_dt, end_dt)
- engine.execute(query)
- table_data.to_sql(name = table_name, con = engine, if_exists = 'append', index = False)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement