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)