def targeting_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Options', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('TargetingOption')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Targeting Options', 'A2:Z') table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Assigned', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('AssignedTargetingOption'))
def put_data(endpoint, method): schema = Discovery_To_BigQuery('displayvideo', 'v1').method_schema(endpoint, method) out = {} if 'dataset' in project.task['out']: out['bigquery'] = { 'dataset': project.task['out']['dataset'], 'table': 'DV360_%s' % endpoint.replace('.', '_'), 'schema': schema, 'skip_rows': 0, 'format': 'JSON', } if 'sheet' in project.task: out['sheets'] = { 'url': project.task['out']['sheet'], 'tab': endpoint, 'range': 'A1:A1', 'delete': True } return out
def inventory_group_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Inventory_Groups', Discovery_To_BigQuery( 'displayvideo', 'v1').method_schema('inventorySourceGroups.list'))
def insertion_order_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_InsertionOrders", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.insertionOrders.list"))
def advertiser_load(): # load multiple partners from user defined sheet def advertiser_load_multiple(): rows = get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Partners', 'range': 'A2:A' } }) for row in rows: yield from API_DV360(project.task['auth_dv'], iterate=True).advertisers().list( partnerId=lookup_id(row[0])).execute() # write advertisers to database and sheet put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Advertisers', 'schema': Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.list'), 'format': 'JSON' } }, advertiser_load_multiple()) # write advertisers to sheet put_rows(project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Advertisers', 'range': 'B2' } }, rows=get_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'query': """SELECT CONCAT(P.displayName, ' - ', P.partnerId), CONCAT(A.displayName, ' - ', A.advertiserId), A.entityStatus FROM `{dataset}.DV_Advertisers` AS A LEFT JOIN `{dataset}.DV_Partners` AS P ON A.partnerId=P.partnerId """.format(**project.task), 'legacy': False } }))
def insertion_order_load(): # load multiple from user defined sheet def insertion_order_load_multiple(): rows = get_rows( project.task["auth_sheets"], { "sheets": { "sheet": project.task["sheet"], "tab": "Advertisers", "range": "A2:A" } }) for row in rows: yield from API_DV360( project.task["auth_dv"], iterate=True).advertisers().insertionOrders().list( advertiserId=lookup_id(row[0])).execute() # write insertion orders to database put_rows( project.task["auth_bigquery"], { "bigquery": { "dataset": project.task["dataset"], "table": "DV_InsertionOrders", "schema": Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.insertionOrders.list"), "format": "JSON" } }, insertion_order_load_multiple())
def first_and_third_party_audience_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_First_And_Third_Party_Audiences', Discovery_To_BigQuery( 'displayvideo', 'v1').method_schema('firstAndThirdPartyAudiences.list'))
def combined_audience_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Combined_Audiences', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('combinedAudiences.list'))
def custom_list_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Custom_Lists', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('customLists.list'))
def negative_keyword_list_load(): # load multiple from user defined sheet def load_multiple(): advertisers = get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Advertisers', 'range': 'A2:A' } }) for advertiser in advertisers: yield from API_DV360( project.task['auth_dv'], iterate=True).advertisers().negativeKeywordLists().list( advertiserId=lookup_id(advertiser[0])).execute() # write inventorys to database and sheet put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Negative_Keywod_Lists', 'schema': Discovery_To_BigQuery('displayvideo', 'v1').method_schema( 'advertisers.negativeKeywordLists.list'), 'format': 'JSON' } }, load_multiple()) # write inventorys to sheet put_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Targeting Options', 'range': 'J2' } }, get_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'query': """SELECT CONCAT(A.displayName, ' - ', A.advertiserId, ' > ', L.displayName, ' - ', L.negativeKeywordListId), FROM `{dataset}.DV_Negative_Keywod_Lists` AS L LEFT JOIN `{dataset}.DV_Advertisers` AS A ON L.advertiserId=A.advertiserId """.format(**project.task), 'legacy': False } }))
def location_list_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Location_Lists', Discovery_To_BigQuery( 'displayvideo', 'v1').method_schema('advertisers.locationLists.list'))
def google_api_execute(auth, api_call, results, errors, limit=None): try: rows = API(api_call).execute() if results: # check if single object needs conversion to rows if isinstance(rows, dict): rows = [rows] rows = map(lambda r: Discovery_To_BigQuery.clean(r), rows) put_rows(auth, results, rows) if 'bigquery' in results: results['bigquery']['disposition'] = 'WRITE_APPEND' except HttpError as e: if errors: rows = [{ 'Error': str(e), 'Parameters': [{ 'Key': k, 'Value': str(v) } for k, v in api_call['kwargs'].items()] }] put_rows(auth, errors, rows) if 'bigquery' in errors: errors['bigquery']['disposition'] = 'WRITE_APPEND' else: raise e
def channel_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Channels', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.channels.list'))
def combined_audience_load(): # load multiple from user defined sheet def load_multiple(): advertisers = get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Advertisers', 'range': 'A2:A' }} ) for advertiser in advertisers: yield from API_DV360( project.task['auth_dv'], iterate=True ).combinedAudiences().list( advertiserId=lookup_id(advertiser[0]) ).execute() # write audience to database and sheet put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Combined_Audiences', 'schema': Discovery_To_BigQuery( 'displayvideo', 'v1' ).method_schema( 'combinedAudiences.list' ), 'format': 'JSON' }}, load_multiple() ) # write audience to sheet put_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Targeting Options', 'range': 'O2' }}, get_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'query': """SELECT CONCAT(I.displayName, ' - ', I.combinedAudienceId), FROM `{dataset}.DV_Combined_Audiences` AS I GROUP BY 1 ORDER BY 1 """.format(**project.task), 'legacy': False }} ) )
def negative_keyword_list_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Negative_Keywod_Lists', Discovery_To_BigQuery( 'displayvideo', 'v1').method_schema('advertisers.negativeKeywordLists.list'))
def line_item_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_LineItems", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.lineItems.list")) sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Line Items", "B2:Z")
def advertiser_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Advertisers', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.list')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Advertisers', 'B2:D')
def google_api_execute(auth, api_call, results, errors, limit=None): """Execute the actual API call and write to the end points defined. The API call is completely defined at this point. The results and error definition is optional. Args: auth (string): either "user" or "service" to make the API call. api_call (dict): the JSON for the API call as defined in recipe. results (dict): defines where the data will be written errors (dict): defines where the errors will be written limit (int): Reduce the number of calls ( mostly for debugging ) Returns (dict): None, all data is transfered between API / BigQuery Raises: ValueError: If a required key in the recipe is missing. """ try: rows = API(api_call).execute() if results: # check if single object needs conversion to rows if isinstance(rows, dict): rows = [rows] # check if simple string API results elif results.get('bigquery', {}).get('format', 'JSON') == 'CSV': rows = [[r] for r in rows] rows = map(lambda r: Discovery_To_BigQuery.clean(r), rows) put_rows(auth, results, rows) if 'bigquery' in results: results['bigquery']['disposition'] = 'WRITE_APPEND' except HttpError as e: if errors: rows = [{ 'Error': str(e), 'Parameters': [{ 'Key': k, 'Value': str(v) } for k, v in api_call['kwargs'].items()] }] put_rows(auth, errors, rows) if 'bigquery' in errors: errors['bigquery']['disposition'] = 'WRITE_APPEND' else: raise e
def inventory_source_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Inventory_Sources', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('inventorySources.list')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Inventory Sources', 'A2:Z')
def campaign_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Campaigns', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('advertisers.campaigns.list'), ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Campaigns', 'B2:Z')
def partner_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Partners', Discovery_To_BigQuery('displayvideo', 'v1').method_schema('partners.list'), ) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Partners', 'B2:Z')
def creative_clear(): table_create( project.task["auth_bigquery"], project.id, project.task["dataset"], "DV_Creatives", Discovery_To_BigQuery( "displayvideo", "v1").method_schema("advertisers.creatives.list"), ) sheets_clear(project.task["auth_sheets"], project.task["sheet"], "Creatives", "B2:Z")
def line_item_load_targeting(): def load_bulk(): # TODO: incorporate filters into line item fetch line_items = [ lookup_id(p[0]) for p in get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Line Items', 'range': 'A2:A' } }) ] parameters = get_rows(project.task["auth_bigquery"], { "bigquery": { "dataset": project.task["dataset"], "query": "SELECT advertiserId, lineItemId FROM `{dataset}.DV_LineItems`" .format(**project.task) } }, as_object=True) for parameter in parameters: yield from API_DV360( project.task["auth_dv"], iterate=True).advertisers().lineItems( ).bulkListLineItemAssignedTargetingOptions( advertiserId=str(parameter['advertiserId']), lineItemId=str(parameter['lineItemId']), ).execute() put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Targeting', 'schema': Discovery_To_BigQuery( 'displayvideo', 'v1').resource_schema('AssignedTargetingOption'), 'disposition': 'WRITE_APPEND', 'format': 'JSON' } }, load_bulk())
def targeting_clear(): table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Options', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('TargetingOption')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Targeting Options', 'A2:Z') table_create( project.task['auth_bigquery'], project.id, project.task['dataset'], 'DV_Targeting_Assigned', Discovery_To_BigQuery('displayvideo', 'v1').resource_schema('AssignedTargetingOption')) sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Destination Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Brand Safety Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Demographic Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Audience Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Device Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Geography Targeting', 'A2:Z') sheets_clear(project.task['auth_sheets'], project.task['sheet'], 'Viewability Targeting', 'A2:Z')
def partner_load(): partner_clear() # write partners to BQ put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Partners', 'schema': Discovery_To_BigQuery('displayvideo', 'v1').method_schema('partners.list'), 'format': 'JSON' } }, API_DV360(project.task['auth_dv'], iterate=True).partners().list().execute()) # write partners to sheet put_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Partners', 'header': False, 'range': 'B2' } }, get_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'query': "SELECT CONCAT(displayName, ' - ', partnerId), entityStatus FROM `%s.DV_Partners`" % project.task['dataset'], 'legacy': False } }))
def google_api_build_results(auth, api_call, results): """Builds the BigQuery table to house the Google API call results. Optional piece of the recipe, will create a BigQuery table for results. Takes results, which defines a bigquery endpoint, and adds fields. Args: auth (string): either "user" or "service" to make the BigQuery call. api_call (dict): the JSON for the API call as defined in recipe. results (dict): defines where the data will be written Returns (dict): A modified results JSON with additional API values added. Raises: ValueError: If a required key in the recipe is missing. """ if 'bigquery' in results: if 'schema' not in results['bigquery']: results['bigquery']['schema'] = Discovery_To_BigQuery( api_call['api'], api_call['version'], api_call.get('key', None), ).method_schema(api_call['function'], api_call.get('iterate', False)) if 'format' not in results['bigquery']: results['bigquery']['format'] = 'JSON' results['bigquery']['skip_rows'] = 0 table_create(results['bigquery'].get('auth', auth), project.id, results['bigquery']['dataset'], results['bigquery']['table'], results['bigquery']['schema'], overwrite=False) return results
def google_api_build_results(auth, api_call, results): if 'bigquery' in results: results['bigquery']['schema'] = Discovery_To_BigQuery( api_call['api'], api_call['version'], api_call.get('key', None), ).method_schema(api_call['function']) #TODO: Fix format to sometimes be CSV, probably refactor BigQuery to # determine format based on rows or schema results['bigquery']['format'] = 'JSON' results['bigquery']['skip_rows'] = 0 #results['bigquery']['disposition'] = 'WRITE_TRUNCATE' table_create(results['bigquery'].get('auth', auth), project.id, results['bigquery']['dataset'], results['bigquery']['table'], results['bigquery']['schema'], overwrite=False) return results
def campaign_load(): # load multiple partners from user defined sheet def campaign_load_multiple(): rows = get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Advertisers', 'range': 'A2:A' }} ) for row in rows: yield from API_DV360( project.task['auth_dv'], iterate=True ).advertisers().campaigns().list( advertiserId=lookup_id(row[0]) ).execute() # write campaigns to database put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Campaigns', 'schema': Discovery_To_BigQuery( 'displayvideo', 'v1' ).method_schema( 'advertisers.campaigns.list' ), 'format': 'JSON' }}, campaign_load_multiple() )
def partner_load_targeting(): def load_bulk(): partners = [ lookup_id(p[0]) for p in get_rows( project.task['auth_sheets'], { 'sheets': { 'sheet': project.task['sheet'], 'tab': 'Partners', 'range': 'A2:A' } }) ] for partner in partners: yield from API_DV360( project.task["auth_dv"], iterate=True).partners( ).targetingTypes().assignedTargetingOptions().list( partnerId=str(partner), targetingType='TARGETING_TYPE_CHANNEL').execute() put_rows( project.task['auth_bigquery'], { 'bigquery': { 'dataset': project.task['dataset'], 'table': 'DV_Targeting', 'schema': Discovery_To_BigQuery( 'displayvideo', 'v1').resource_schema('AssignedTargetingOption'), 'disposition': 'WRITE_APPEND', 'format': 'JSON' } }, load_bulk())
def put_data(endpoint, method): out = {} schema = Discovery_To_BigQuery('dfareporting', 'v3.4').method_schema(endpoint, method) if 'dataset' in project.task['out']: out['bigquery'] = { 'dataset': project.task['out']['dataset'], 'table': 'CM_%s' % endpoint, 'schema': schema, 'skip_rows': 0, 'format': 'JSON', } if 'sheet' in project.task: out['sheets'] = { 'url': project.task['out']['sheet'], 'tab': 'CM_%s' % endpoint, 'range': 'A1:A1', 'delete': True } return out