class PostgresOperator(BaseOperator):
"""
Executes sql code in a specific Postgres database
:param postgres_conn_id: reference to a specific postgres database
:type postgres_conn_id: string
:param sql: the sql code to be executed
:type sql: Can receive a str representing a sql statement,
a list of str (sql statements), or reference to a template file.
Template reference are recognized by str ending in '.sql'
"""
template_fields = ('sql',)
template_ext = ('.sql',)
ui_color = '#ededed'
@apply_defaults
def __init__(
self, sql,
postgres_conn_id='postgres_default', autocommit=False,
*args, **kwargs):
super(PostgresOperator, self).__init__(*args, **kwargs)
self.sql = sql
self.postgres_conn_id = postgres_conn_id
self.autocommit = autocommit
def execute(self, context):
logging.info('Executing: ' + str(self.sql))
self.hook = PostgresHook(postgres_conn_id=self.postgres_conn_id)
self.hook.run(self.sql, self.autocommit)
class PostgresOperator(BaseOperator):
"""
Executes sql code in a specific Postgres database
:param postgres_conn_id: reference to a specific postgres database
:type postgres_conn_id: string
:param sql: the sql code to be executed
:type sql: string or string pointing to a template file. Fil must have
a '.sql' extensions.
"""
template_fields = ('sql',)
template_ext = ('.sql',)
ui_color = '#ededed'
@apply_defaults
def __init__(
self, sql,
postgres_conn_id='postgres_default', autocommit=False,
*args, **kwargs):
super(PostgresOperator, self).__init__(*args, **kwargs)
self.sql = sql
self.postgres_conn_id = postgres_conn_id
self.autocommit = autocommit
def execute(self, context):
logging.info('Executing: ' + self.sql)
self.hook = PostgresHook(postgres_conn_id=self.postgres_conn_id)
self.hook.run(self.sql, self.autocommit)
def monthly_spent(TaskName , ds, prev_ds, **kwargs ):
facebook = Variable.get("facebook_auth", deserialize_json=True)
access_token = facebook["access_token"]
ad_account_id = facebook["ad_account_id"]
FacebookAdsApi.init(access_token=access_token)
fromDate = prev_ds
toDate = (datetime.strptime(ds, '%Y-%m-%d') + timedelta(days=-1)).strftime('%Y-%m-%d')
fields = [ 'spend', 'account_id', ]
params = { 'level': 'account', 'time_range': {'since': fromDate ,'until': toDate },}
spendrows = AdAccount(ad_account_id).get_insights( fields=fields, params=params,)
total = 0.0
for row in spendrows:
total = total + float ( row["spend"])
row = (total, ds)
connDB = PostgresHook (postgres_conn_id = 'airflow')
FB_json_conn = BaseHook.get_connection('FB_json_conn').extra_dejson
scommand = "insert into monthly_cost values( %s ,%s) "
connDB.run(scommand, parameters =row)
print ( "done, Cost from "+ prev_ds+ " to " + ds )
def get_stocks(ds, **context):
symbol = context['params']['symbol']
pg_hook = PostgresHook(postgres_conn_id='stocks')
api_hook = HttpHook(http_conn_id='alphavantage', method='GET')
# If either of these raises an exception then we'll be notified via
# Airflow
resp = api_hook.run(f'query?function=TIME_SERIES_DAILY_ADJUSTED&symbol={symbol}&apikey=537201H9R203WT4C&datatype=csv')
resp = json.loads(resp.content)
# These are the only valid stocks the DB supports at the moment. Anything
# else that turns up will be ignored.
stocks_insert = """INSERT INTO stocks (symbol, valid_until, price)
VALUES (%s, %s, %s);"""
# If this raises an exception then we'll be notified via Airflow
valid_until = datetime.fromtimestamp(resp['timestamp'])
for iso2, price in resp['stocks'].items():
# If converting the price to a float fails for whatever reason then
# just move on.
try:
price = float(price)
except:
continue
iso2 = iso2.upper().strip()
if iso2 not in stocks or price < 0:
continue
pg_hook.run(stocks_insert, parameters=(iso2,
valid_until,
price))
def delete_from_staging():
staging_hook = PostgresHook('snql_staging')
staging_hook.run('DELETE FROM dim_sneakers;')
def redshift_call(sql, type_='run'):
pghook = PostgresHook(postgres_conn_id="naveen_redshift")
if type_ == 'run':
return pghook.run(sql)
else:
return pghook.get_records(sql)
def create_tres(**kwargs):
conn_id = kwargs.get('conn_id')
pg_hook = PostgresHook(conn_id)
sql = """CREATE TABLE IF NOT EXISTS tres (id_id INT8 PRIMARY KEY) DISTRIBUTE BY HASH(id_id);"""
pg_hook.run(sql)
def create_tables():
conn = PostgresHook(postgres_conn_id='sumzero')
conn.run(CREATE_TABLE_QUERY)
def create_table(ds, **kwargs):
pg_hook = PostgresHook(postgres_conn_id='postgres_lab')
sql = '''create table humanresources.airflow_dept as select * from humanresources.department where 1 = 2;'''
pg_hook.run(sql)
def load_data(ds, **kwargs):
pg_hook = PostgresHook(postgres_conn_id='postgres_lab')
sql_load = '''insert into humanresources.airflow_dept (select * from humanresources.department);'''
pg_hook.run(sql_load)
