def get_team_data():
conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn()
conn.autocommit = True
cur = conn.cursor()
params = {"token": Variable.get("workboard_token")}
base_url = "https://www.myworkboard.com/wb/apis"
r = requests.get(base_url + "/team?include=org_teams", params=params)
if r.status_code != 200:
raise ValueError("Request not successful. Status code {}".format(
r.status_code))
else:
teams = r.json()["data"]["team"]
logging.info("There are {} teams".format(len(teams)))
for t in teams:
try:
cur.execute(
"""INSERT INTO teams_raw (team_id, team_data)
VALUES (%s, %s) ON CONFLICT (team_id)
DO UPDATE SET team_data = EXCLUDED.team_data
""",
[t["team_id"], json.dumps(t)],
)
logging.info("Team id #{} inserted into the table".format(
t["team_id"]))
except BaseException:
logging.warning("Record skipped")
pass
cur.close()
conn.close()
def get_ads_html():
conn = PostgresHook(postgres_conn_id="postgres_apple").get_conn()
conn.autocommit = True
cur = conn.cursor()
urls = apple.get_urls()
for url in urls:
html = apple.get_html(url)
cur.execute(
"""INSERT INTO raw (url, html)
VALUES (%s, %s)""",
[url, html],
)
cur.close()
conn.close()
def get_goal_data(goal_status=1):
conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn()
conn.autocommit = True
cur = conn.cursor()
params = {
"token": Variable.get("workboard_token"),
"goal_status": goal_status
}
base_url = "https://www.myworkboard.com/wb/apis"
r = requests.get(base_url + "/goal", params=params)
if r.status_code != 200:
raise ValueError("Request not successful. Status code {}".format(
r.status_code))
else:
goals = r.json()["data"]["goal"]
logging.info("There are {} person(s) with goals".format(len(goals)))
for person in goals:
logging.info("{} has {} goals".format(person["user_email"],
len(person["people_goals"])))
for g in person["people_goals"]:
try:
cur.execute(
"""INSERT INTO goals_raw (goal_id, goal_data)
VALUES (%s, %s) ON CONFLICT (goal_id)
DO UPDATE SET goal_data = EXCLUDED.goal_data
""",
[int(g["goal_id"]), json.dumps(g)],
)
logging.info("Goal id #{} inserted into the table".format(
g["goal_id"]))
except BaseException:
logging.warning("Record skipped")
pass
cur.close()
conn.close()
def get_team_member_data():
conn = PostgresHook(postgres_conn_id="postgres_workboard").get_conn()
conn.autocommit = True
cur = conn.cursor()
cur.execute("SELECT team_id FROM teams_raw")
team_ids = [line[0] for line in cur]
params = {"token": Variable.get("workboard_token")}
base_url = "https://www.myworkboard.com/wb/apis"
for t in team_ids:
r = requests.get(base_url + "/team/{}".format(t), params=params)
if r.status_code != 200:
logging.warning("Request not successful. Status code {}".format(
r.status_code))
pass
else:
team = r.json()["data"]["team"]
try:
cur.execute(
"""INSERT INTO team_members_raw (team_id, team_member_data)
VALUES (%s, %s) ON CONFLICT (team_id)
DO UPDATE SET team_member_data = EXCLUDED.team_member_data
""",
[team["team_id"], json.dumps(team)],
)
logging.info("Team id #{} inserted into the table".format(
team["team_id"]))
except BaseException:
logging.warning("Record skipped")
pass
cur.close()
conn.close()
default_args = {
'owner': 'airflow',
'depends_on_past': False,
'start_date': datetime(2020, 3, 13),
'email': ['*****@*****.**'],
'email_on_failure': False,
'email_on_retry': False,
'retries': 1,
'retry_delay': timedelta(minutes=5),
}
dag = DAG('refresh_sensors',
default_args=default_args,
schedule_interval='0 * * * *')
conn = PostgresHook(postgres_conn_id='agg2_db')
conn.autocommit = True
def query_func(ds, **kwargs):
print(ds)
print(query)
result = ''
result = conn.run(query)
return result
sshHook = SSHHook(ssh_conn_id='agg2')
query = \
"""
REFRESH MATERIALIZED VIEW charger.estp_sensors;
"""
