def do_slave(task): date = task['date'] game_id = task['game_id'] verbose = task['verbose'] dry_run = task['dry_run'] commit_interval = task['commit_interval'] start_time = SpinConfig.cal_to_unix((int(date[0:4]),int(date[4:6]),int(date[6:8]))) end_time = start_time + 86400 gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id=game_id))) STORE = {} [get_store_items(STORE, sku) for sku in gamedata['store']['catalog']] if verbose: print >> sys.stderr, 'converting date', date, 'start_time', start_time, 'end_time', end_time, '...' if not verbose: filterwarnings('ignore', category = MySQLdb.Warning) cfg = SpinConfig.get_mysql_config(game_id+'_upcache') con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) store_table = cfg['table_prefix']+game_id+'_store' s3 = SpinS3.S3(SpinConfig.aws_key_file()) bucket = 'spinpunch-logs' batch = 0 total = 0 cur = con.cursor() for entry in s3.list_bucket(bucket, prefix='%s/%s-%s-metrics.json' % (date[0:6], SpinConfig.game_id_long(override_game_id=game_id), date)): filename = entry['name'].split('/')[-1] if verbose: print >> sys.stderr, 'reading', filename if entry['name'].endswith('.zip'): tf = tempfile.NamedTemporaryFile(prefix='old_metrics_to_mysql-'+filename, suffix='.zip') s3.get_file(bucket, entry['name'], tf.name) unzipper = subprocess.Popen(['unzip', '-q', '-p', tf.name], stdout = subprocess.PIPE) elif entry['name'].endswith('.gz'): fd = s3.get_open(bucket, entry['name'], allow_keepalive = False) unzipper = subprocess.Popen(['gunzip', '-c', '-'], stdin = fd.fileno(), stdout = subprocess.PIPE) for line in unzipper.stdout.xreadlines(): if '5120_buy_item' in line: #and ('item:token' in line): entry = SpinJSON.loads(line) if entry['event_name'] != '5120_buy_item': continue if 'price_currency' not in entry: # old metric, need to fill in manually if entry['items'][0]['spec'] in STORE: entry['price_currency'] = 'item:token' entry['price'] = STORE[entry['items'][0]['spec']] if verbose: print >> sys.stderr, SpinJSON.dumps(entry) if entry.get('price_currency','unknown') != 'item:token': continue if '_id' in entry: entry_id = entry['_id'] else: id_generator.set_time(int(time.time())) entry_id = id_generator.generate() # arbitrary assert len(entry['items']) == 1 item = entry['items'][0] keyvals = [('_id', entry_id), ('time', entry['time']), ('user_id', entry['user_id']), ('price', entry['price']), ('currency', entry['price_currency']), ('item', item['spec']), ('stack', item.get('stack',1))] query = "INSERT INTO " + store_table + \ "("+', '.join(['`'+k+'`' for k,v in keyvals])+")"+ \ " VALUES ("+', '.join(['%s'] * len(keyvals)) +")" if dry_run: print >> sys.stderr, query, [v for k,v in keyvals] else: cur.execute(query, [v for k,v in keyvals]) batch += 1 total += 1 if commit_interval > 0 and batch >= commit_interval: batch = 0 con.commit() cur = con.cursor() if verbose: print >> sys.stderr, total, 'inserted' if not dry_run: con.commit()
do_optimize = False opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q', ['prune', 'optimize']) for key, val in opts: if key == '-g': game_id = val elif key == '-c': commit_interval = int(val) elif key == '-q': verbose = False elif key == '--prune': do_prune = True elif key == '--optimize': do_optimize = True sql_util = SpinSQLUtil.MySQLUtil() if not verbose: sql_util.disable_warnings() cfg = SpinConfig.get_mysql_config(game_id + '_upcache') con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) cur = con.cursor(MySQLdb.cursors.DictCursor) # INPUTS upcache_table = cfg['table_prefix'] + game_id + { 'mf': '_upcache_lite' }.get(game_id, '_upcache') sessions_table = cfg['table_prefix'] + game_id + '_sessions' # OUTPUTS acquisitions_table = cfg['table_prefix'] + game_id + '_acquisitions' acquisitions_daily_summary_table = cfg[ 'table_prefix'] + game_id + '_acquisitions_daily_summary' sql_util.ensure_table(cur, acquisitions_table,
for key, val in opts: if key == '-q': verbose = False elif key == '--prune': do_prune = True elif key == '--min-sec': min_sec = int(val) elif key == '--dry-run': dry_run = True elif key == '--email': email_to = val if len(args) < 1: sys.stderr.write('please specify mysql_servers entry\n') sys.exit(1) mysql_server = args[0] sql_util = SpinSQLUtil.MySQLUtil() if not verbose: sql_util.disable_warnings() cfg = SpinConfig.get_mysql_config(mysql_server) con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) cur = con.cursor(MySQLdb.cursors.DictCursor) subject = 'Automated reminder from ' + socket.gethostname() body = None cur.execute( '''SELECT start_time, user_host, TIME_TO_SEC(query_time) AS total_sec, db, sql_text FROM mysql.slow_log WHERE start_time >= NOW() - INTERVAL 24 HOUR and TIME_TO_SEC(query_time) >= %s ORDER BY total_sec DESC;''', [ min_sec, ]) rows = cur.fetchall()
opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q', ['dry-run', 'source=']) for key, val in opts: if key == '-g': game_id = val elif key == '-c': commit_interval = int(val) elif key == '-q': verbose = False elif key == '--dry-run': dry_run = True elif key == '--source': assert val in ('mongodb', 's3') source = val sql_util = SpinSQLUtil.MySQLUtil() if not verbose: sql_util.disable_warnings() cfg = SpinConfig.get_mysql_config('skynet') con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) conversions_table = cfg['table_prefix'] + 'conversions' cur = con.cursor(MySQLdb.cursors.DictCursor) if not dry_run: sql_util.ensure_table(cur, conversions_table, conversions_schema) con.commit() # find most recent already-converted action start_time = calendar.timegm( [2013, 9, 1, 0, 0, 0]) # this is about when we started recording this data end_time = time_now - 60 # don't get too close to "now" since we assume if we have one event for a given second, we have them all end_time = min(
# tool to quickly invoke mysql on a database using credentials from config.json import SpinConfig import os, sys, getopt if __name__=='__main__': whichdb = SpinConfig.config['game_id']+'_upcache' execute = None mode = 'connect' opts, args = getopt.gnu_getopt(sys.argv[1:], '', ['sizes']) for key, val in opts: if key == '--sizes': mode = 'size' if len(args)>0: whichdb = args[0] if len(args) > 1: execute = args[1] conf = SpinConfig.get_mysql_config(whichdb) cmd_args=['mysql', '-u', conf['username'], "-p"+conf['password'], '-h', conf['host'], '--port', '%d' % conf['port'], '--protocol=tcp', conf['dbname']] if mode == 'size': cmd_args += ['-e', '''SELECT table_name, round(((data_length + index_length) / 1024 / 1024), 0) as `size (MB)` FROM information_schema.TABLES WHERE table_schema = '%s' and (data_length+index_length) > 10*1024*1024 order by (data_length+index_length) DESC;''' % conf['dbname']] elif execute: cmd_args += ['-e', execute] os.execvp(cmd_args[0], cmd_args)
def do_slave(task): date = task['date'] game_id = task['game_id'] verbose = task['verbose'] dry_run = task['dry_run'] start_time = SpinConfig.cal_to_unix( (int(date[0:4]), int(date[4:6]), int(date[6:8]))) end_time = start_time + 86400 if verbose: print >> sys.stderr, 'converting date', date, 'start_time', start_time, 'end_time', end_time, '...' # gamedata = SpinJSON.load(open(SpinConfig.gamedata_filename(override_game_id = game_id))) if not verbose: filterwarnings('ignore', category=MySQLdb.Warning) quarries = SpinConfig.load( SpinConfig.gamedata_component_filename('quarries_compiled.json')) hives = SpinConfig.load( SpinConfig.gamedata_component_filename('hives_compiled.json')) # ensure that the spawn list is ordered by id_start - necessary for find_template() below for spawn_list in quarries['spawn'], hives['spawn']: spawn_list.sort(key=lambda x: x['id_start']) cfg = SpinConfig.get_mysql_config(game_id + '_upcache') con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) battles_table = cfg['table_prefix'] + game_id + '_battles' if 0: # find any already-converted battles cur = con.cursor() cur.execute( "SELECT COUNT(*) FROM %s WHERE time >= %%s and time < %%s" % battles_table, (start_time, end_time)) row = cur.fetchone() con.commit() if row and row[0] > 0: print >> sys.stderr, 'there are already', row[ 0], 'entries in this time range, aborting!' return s3 = SpinS3.S3(SpinConfig.aws_key_file()) bucket = 'spinpunch-%sprod-battle-logs' % game_id for entry in s3.list_bucket(bucket, prefix='%s-battles-%s/%s' % (game_id, date[0:6], date)): filename = entry['name'].split('/')[-1] event_time, attacker_id, defender_id, base_id = parse_battle_log_filename( filename) if (not base_id) or event_time < start_time or event_time >= end_time: continue if base_id[0] != 'v': continue # only look at hives print >> sys.stderr, event_time, SpinLog.pretty_time( time.gmtime(event_time)), filename fd = s3.get_open(bucket, entry['name'], allow_keepalive=False) unzipper = subprocess.Popen(['gunzip', '-c', '-'], stdin=fd.fileno(), stdout=subprocess.PIPE) battle_start = None battle_end = None for line in unzipper.stdout.xreadlines(): if '3820_battle_start' in line: battle_start = SpinJSON.loads(line) elif '3830_battle_end' in line: battle_end = SpinJSON.loads(line) if (not battle_start) or (not battle_end): continue base_template = find_template(hives['spawn'], int(base_id[1:])) if not base_template: sys.stderr.write('unknown hive %s\n' % base_id) continue # generate a fake summary summary = { 'time': event_time, 'attacker_id': battle_start['attacker_user_id'], 'attacker_level': battle_start['attacker_level'], 'attacker_outcome': battle_end['battle_outcome'], 'defender_id': battle_start['opponent_user_id'], 'defender_level': battle_start['opponent_level'], 'defender_outcome': 'victory' if battle_end['battle_outcome'] == 'defeat' else 'defeat', 'base_damage': battle_end['base_damage'], 'base_id': battle_start['base_id'], 'base_type': 'hive', 'base_template': base_template, 'loot': battle_end['loot'] } cur = con.cursor() cur.execute( "SELECT battle_id FROM %s WHERE time = %%s and attacker_id = %%s and defender_id = %%s" % battles_table, (event_time, battle_start['attacker_user_id'], battle_start['opponent_user_id'])) row = cur.fetchone() con.commit() if row: sys.stderr.write('appears to be a duplicate, skipping!\n') continue id_generator.set_time(int(time.time())) battle_id = id_generator.generate() # arbitrary keys = [ 'battle_id', ] values = [ battle_id, ] for kname, ktype in battle_fields.iteritems(): path = kname.split(':') probe = summary val = None for i in xrange(len(path)): if path[i] not in probe: break elif i == len(path) - 1: val = probe[path[i]] break else: probe = probe[path[i]] if val is not None: keys.append(kname) values.append(val) query = "INSERT INTO " + battles_table + \ "("+', '.join(['`'+x+'`' for x in keys])+")"+ \ " VALUES ("+', '.join(['%s'] * len(values)) +")" print >> sys.stderr, query print >> sys.stderr, values if not dry_run: cur = con.cursor() cur.execute(query, values) con.commit()
fix_missing_data = False opts, args = getopt.gnu_getopt(sys.argv[1:], 'g:c:q', ['dry-run', 'fix-missing-data']) for key, val in opts: if key == '-g': game_id = val elif key == '-c': commit_interval = int(val) elif key == '-q': verbose = False elif key == '--dry-run': dry_run = True elif key == '--fix-missing-data': fix_missing_data = True sql_util = SpinSQLUtil.MySQLUtil() if not verbose: sql_util.disable_warnings() cfg = SpinConfig.get_mysql_config( 'skynet' if fix_missing_data else 'skynet_readonly') con = MySQLdb.connect(*cfg['connect_args'], **cfg['connect_kwargs']) adstats_table = cfg['table_prefix'] + 'adstats_hourly' nosql_config = SpinConfig.get_mongodb_config('skynet') nosql_client = pymongo.MongoClient(*nosql_config['connect_args'], **nosql_config['connect_kwargs']) nosql_db = nosql_client[nosql_config['dbname']] cur = con.cursor() if not dry_run: sql_util.ensure_table(cur, adstats_table, adstats_schema) con.commit() # find most recent entry start_time = -1