from db.art_relations import conn as art_relations_conn import api import config import boto3 import copy from datetime import date, datetime, timedelta import iso8601 import json from retrying import retry import sys import time """Back BACON This script crawls the YouTube API to find any videos that BACON has failed to claim and/or fingerprint in the past X number of days. Here's what it does: - Gets all BACON-enabled auto-claiming channels from the DB - Gets all new videos that were uploaded to each channel - Fetches any claims that have been made on those videos - Fetches references for any claims - Spits out the following, broken down by CMS account: - Unclaimed videos - Claimed videos that are missing references Dependencies: - art_relations credentials (see art_relations.py) - YT service account key file (stored under youtube/keys, configured in youtube/config.py) Make sure the service account has been authorized for the necessary CMS accounts. Otherwise, you will get errors and you will cry. Enjoy. """ email_message = '' @retry(stop_max_attempt_number=10, wait_fixed=0.5) def execute_call(youtube_call): return youtube_call.execute() def filter_unclaimed_videos(content_owner, video_ids): claimed_video_ids = [] missing_reference = [] for video_id in video_ids: print('Fetching claims for ' + content_owner) partner = config.api_mapping.youtube_partner list_call = partner.claimSearch().list( videoId=video_id, onBehalfOfContentOwner=content_owner) list_results = execute_call(list_call) items = list_results.get('items') or [] for item in items: if item.get('isPartnerUploaded') or (item.get('thirdPartyClaim') and item.get('status') == 'active'): claimed_video_ids.append(video_id) # see if there are any references claim_asset_id = item.get('assetId') current_asset_id = get_current_asset_id(claim_asset_id, content_owner) references = list_references(current_asset_id, content_owner) if not references: missing_reference.append({'videoId': video_id, 'assetId': current_asset_id}) break unclaimed_ids = set(video_ids) - set(claimed_video_ids) return unclaimed_ids, missing_reference def list_references(asset_id, content_owner): partner = config.api_mapping.youtube_partner list_call = partner.references().list( assetId=asset_id, onBehalfOfContentOwner=content_owner) list_results = execute_call(list_call) items = list_results.get('items') or [] return items def get_current_asset_id(claim_asset_id, content_owner): # take the asset id from the claim and get the up-to-date asset id partner = config.api_mapping.youtube_partner get_call = partner.assets().get( assetId=claim_asset_id, onBehalfOfContentOwner=content_owner) get_result = execute_call(get_call) current_asset_id = get_result.get('id') return current_asset_id def fetch_uploads(channel_id, published_after, published_before): video_ids = [] data = config.api_mapping.youtube_data keep_going = True token = None while keep_going: print('Fetching activities for channel ' + channel_id) list_call = data.search().list( channelId=channel_id, publishedAfter=published_after, publishedBefore=published_before, maxResults=50, pageToken=token, order='date', type='video', part='snippet') list_results = execute_call(list_call) videos = list_results.get('items') or [] for video in videos: ids = video.get('id') snippet = video.get('snippet') if ids and snippet: published_at = snippet.get('publishedAt') published_at_date = iso8601.parse_date(published_at) published_after_date = iso8601.parse_date(published_after) published_before_date = iso8601.parse_date(published_before) # extra check for date range in case API is unreliable if published_after_date < published_at_date < published_before_date: video_ids.append(ids.get('videoId')) token = list_results.get('nextPageToken') if not token: keep_going = False return video_ids def get_claims(video_id, owner): partner = config.api_mapping.youtube_partner search_call = partner.claimSearch().list( videoId=video_id, onBehalfOfContentOwner=owner, includeThirdPartyClaims=True) result = execute_call(search_call) items = result.get('items') or [] # filter out third party claims filtered_items = [] for item in items: if item.get('status') == 'active': filtered_items.append(item) return filtered_items def insert_reference(claim_id, owner, content_type): partner = config.api_mapping.youtube_partner insert_call = partner.references().insert( claimId=claim_id, onBehalfOfContentOwner=owner, body={'contentType': content_type}) return execute_call(insert_call) def add_missing_reference(owner, video_id): try: print('Processing video {}...'.format(video_id)) add_to_email('Processing video {}...'.format(video_id)) add_to_email('\n') claims = get_claims(video_id, owner) if len(claims) != 1: print('Got {} claims'.format(len(claims))) add_to_email('Got {} claims'.format(len(claims))) add_to_email('\n\n') return claim = claims[0] if claim.get('thirdPartyClaim'): print('Got a third-party claim') add_to_email('Got a third-party claim') add_to_email('\n\n') return claim_asset_id = claim.get('assetId') if not claim_asset_id: print('Got no asset ID') add_to_email('Got no asset ID') add_to_email('\n\n') return claim_id = claim.get('id') if not claim_id: print('Got no claim ID') add_to_email('Got no claim ID') add_to_email('\n\n') return content_type = claim.get('contentType') if not content_type: print('Got no content type') add_to_email('Got no content type') add_to_email('\n\n') return current_asset_id = get_current_asset_id(claim_asset_id, owner) existing_refs = list_references(current_asset_id, owner) if existing_refs: print('We already have references for asset {}'.format(current_asset_id)) print(existing_refs) add_to_email('We already have references for asset {}'.format(current_asset_id)) add_to_email('\n') add_to_email(existing_refs) add_to_email('\n\n') return reference = insert_reference(claim_id, owner, content_type) print('Created reference for video {}, asset {}, claim {}'.format(video_id, current_asset_id, claim_id)) add_to_email('Created reference for video {}, asset {}, claim {}'.format(video_id, current_asset_id, claim_id)) add_to_email('\n\n') print(reference) return except Exception as e: print('Error on video {}'.format(video_id)) print(str(e)) add_to_email('Error on video {}'.format(video_id)) add_to_email('\n') add_to_email(str(e)) add_to_email('\n') def get_last_bacon_claim_date(): last_claim_query = """select MAX(claim_insert_time) as max_claim from youtube_channel_video_status""" with art_relations_conn as cursor: cursor.execute(last_claim_query) result = cursor.fetchone() return result['max_claim'].strftime('%Y-%m-%d %H:%M') def add_to_email(message): global email_message email_message += message if len(sys.argv) < 2: print('Usage: {} num_days'.format(sys.argv[0])) exit() days_ago = int(sys.argv[1]) print('Looking for activities starting {} day(s) ago'.format(days_ago)) channels_query = """ SELECT ytc.id, ytc.youtube_channel_id, cmsa.cmsa_content_owner, ytcat.youtube_channel_asset_type, ytc.content_id_matching FROM youtube_channel ytc JOIN youtube_channel_cms_account_history hist ON ytc.youtube_channel_cms_account_history_id = hist.id JOIN youtube_channel_cms_account cmsa ON hist.youtube_channel_cms_account_id = cmsa.id JOIN youtube_channel_asset_types ytcat ON ytc.youtube_channel_asset_type_id = ytcat.id WHERE ytc.auto_claim = 'yes' """ with art_relations_conn as cursor: cursor.execute(channels_query) channels = [] for item in cursor.fetchall(): channels.append({ 'orchardChannelId': int(item['id']), 'youtubeChannelId': item['youtube_channel_id'], 'contentOwner': item['cmsa_content_owner'], 'assetType': item['youtube_channel_asset_type'], 'contentIdMatching': int(item['content_id_matching'])}) datetime_format_string = 'T00:00:00.00-05:00' start_date = date.today() - timedelta(days=days_ago) pub_after = str(start_date) + datetime_format_string end_date = date.today() pub_before = str(end_date) + datetime_format_string print('Grabbing all videos published after {} and before {}'.format( pub_after, pub_before)) raw_video_ids_content_id_matching_enabled = {} raw_video_ids_content_id_matching_disabled = {} # break date range into one day for each call for i in range(days_ago): pub_after_slice = start_date + timedelta(days=i) pub_before_slice = start_date + timedelta(days=i + 1) pub_after_slice_str = str(pub_after_slice) + datetime_format_string pub_before_slice_str = str(pub_before_slice) + datetime_format_string for line in channels: owner = line.get('contentOwner') contentIdMatching = line.get('contentIdMatching') if owner not in raw_video_ids_content_id_matching_enabled: raw_video_ids_content_id_matching_enabled[owner] = [] if owner not in raw_video_ids_content_id_matching_disabled: raw_video_ids_content_id_matching_disabled[owner] = [] if(contentIdMatching): raw_video_ids_content_id_matching_enabled[owner] += fetch_uploads( line.get('youtubeChannelId'), pub_after_slice_str, pub_before_slice_str) # add content id matching disabled channels to a separate list else: raw_video_ids_content_id_matching_disabled[owner] += fetch_uploads( line.get('youtubeChannelId'), pub_after_slice_str, pub_before_slice_str) vids_per_search = 100 unclaimed_video_ids = {} all_missing_reference = {} # add videos to unclaimed list and add to references list for content id matching enabled channels for owner in raw_video_ids_content_id_matching_enabled.keys(): if owner not in unclaimed_video_ids: unclaimed_video_ids[owner] = [] if owner not in all_missing_reference: all_missing_reference[owner] = [] video_ids_for_owner = raw_video_ids_content_id_matching_enabled[owner] print('RAW content id enabled video count for ' + owner + ':') print(len(video_ids_for_owner)) filtered_video_ids = [] missing_reference = [] offset = 0 while offset < len(video_ids_for_owner): cutoff = offset + vids_per_search id_slice = video_ids_for_owner[offset:cutoff] more_unclaimed_ids, more_missing_reference = filter_unclaimed_videos( owner, id_slice) filtered_video_ids += more_unclaimed_ids missing_reference += more_missing_reference offset += vids_per_search if filtered_video_ids: unclaimed_video_ids[owner].extend(filtered_video_ids) if missing_reference: all_missing_reference[owner].extend(missing_reference) # add videos to unclaimed list for content id matching disabled channels but don't add to the references list for owner in raw_video_ids_content_id_matching_disabled.keys(): if owner not in unclaimed_video_ids: unclaimed_video_ids[owner] = [] if owner not in all_missing_reference: all_missing_reference[owner] = [] video_ids_for_owner = raw_video_ids_content_id_matching_disabled[owner] print('RAW content id disabled video count for ' + owner + ':') print(len(video_ids_for_owner)) filtered_video_ids = [] offset = 0 while offset < len(video_ids_for_owner): cutoff = offset + vids_per_search id_slice = video_ids_for_owner[offset:cutoff] more_unclaimed_ids, _ = filter_unclaimed_videos( owner, id_slice) filtered_video_ids += more_unclaimed_ids offset += vids_per_search if filtered_video_ids: unclaimed_video_ids[owner].extend(filtered_video_ids) print('Unclaimed Video IDs') add_to_email('Unclaimed Video IDs') add_to_email('\n\n') for owner in unclaimed_video_ids.keys(): print("Content Owner: {}".format(owner)) add_to_email("Content Owner: {}".format(owner)) add_to_email('\n\n') print(','.join(unclaimed_video_ids[owner])) add_to_email(','.join(unclaimed_video_ids[owner])) add_to_email('\n\n') print('Claimed Videos Missing References') add_to_email('Claimed Videos Missing References') for owner in all_missing_reference.keys(): add_to_email('\n\n') print("Content Owner: {}".format(owner)) add_to_email("Content Owner: {}".format(owner)) add_to_email('\n\n') for asset_data in all_missing_reference[owner]: print("Video ID: {}, Asset ID: {}".format( asset_data['videoId'], asset_data['assetId'])) add_to_email("Video ID: {}, Asset ID: {}".format(asset_data['videoId'], asset_data['assetId'])) add_to_email('\n') add_missing_reference(owner, asset_data['videoId']) add_to_email('\n') last_bacon_claim_date = get_last_bacon_claim_date() add_to_email('Last BACON Claim: ' + last_bacon_claim_date + '\n') sns_client = boto3.client('sns') response = sns_client.publish( TopicArn=config.BACON_STATUS_ARN, Subject='BACON Status', Message=email_message )