def new(self, environment_type, path_info): conf.log.warning("New context created, Check your db! '%s', '%s'" % (environment_type, path_info)) insert_new = "INSERT INTO context_dim VALUES(null, %s, %s, %s, %s, null)" new_id = None with analytical_transaction() as cursor: try: now = now_str() # We don't know the context name so lets just use first part of the path info path_info = '/'.join(path_info.split('/')[:2]) context = path_info path_info += '.*' cursor.execute(insert_new, (context, environment_type, path_info, now)) cursor.execute("SELECT last_insert_id() FROM context_dim LIMIT 1") row = cursor.fetchone() if row and len(row) > 0: new_id = row[0] is_home = environment_type == 'home' cache = {True: self.home_prefix_cache, False: self.project_prefix_cache}[is_home] cache[context] = row[0] except: conf.log.exception("Failed creating a new record to context dimension. %s" % str(path_info)) raise return new_id
def add_new_partitions(self, table_plan): date = DateDimension() now = date.date_sk_utcnow() # Calculate partition limits latest = table_plan.partitions[-1] step = table_plan.partition_size first = latest + step limit = now + (table_plan.partition_size * (table_plan.buffer_partitions + 1)) if limit <= first: return # Create sql statement partitions = [] for partition in range(first, limit, step): partitions.append("PARTITION p_%d VALUES LESS THAN (%d)" % (partition, partition)) sql = "ALTER TABLE %s ADD PARTITION (%s)" % (table_plan.table, ','.join(partitions)) # Run partitioning command with analytical_transaction() as cursor: try: cursor.execute(sql) except: conf.log.exception("Adding partitions FAILED! %s" % sql) conf.log.info("Added partitions for %s" % table_plan.table)
def new(self, environment_type, path_info): conf.log.warning("New context created, Check your db! '%s', '%s'" % (environment_type, path_info)) insert_new = "INSERT INTO context_dim VALUES(null, %s, %s, %s, %s, null)" new_id = None with analytical_transaction() as cursor: try: now = now_str() # We don't know the context name so lets just use first part of the path info path_info = '/'.join(path_info.split('/')[:2]) context = path_info path_info += '.*' cursor.execute(insert_new, (context, environment_type, path_info, now)) cursor.execute( "SELECT last_insert_id() FROM context_dim LIMIT 1") row = cursor.fetchone() if row and len(row) > 0: new_id = row[0] is_home = environment_type == 'home' cache = { True: self.home_prefix_cache, False: self.project_prefix_cache }[is_home] cache[context] = row[0] except: conf.log.exception( "Failed creating a new record to context dimension. %s" % str(path_info)) raise return new_id
def write_event(self, event): """ Write one event into database queue. Trigger notify event if `refresh_global_timeline` is enabled. :param dict event: Event data Example: {'timestamp': <datetime>, 'event': 'wiki_create', 'project': 'bestproject', 'username': '******', 'forum_id': '35'} """ # use this server's datetime as timestamp, UTC curdate = datetime.datetime.utcnow() event['timestamp'] = curdate.isoformat() # generate JSON from dict json_data = json.dumps(event) with analytical_transaction() as cursor: cursor.execute('INSERT INTO `data_queue` (`data`) VALUES (%s)', json_data) # Refresh global timeline if needed is_home_env = event['project'] == 'home' is_page_view = event['event'] == 'page_request' is_enabled = conf.refresh_global_timeline if is_enabled and not (is_home_env or is_page_view): _notify_event_occurred(event['project'], event['event'])
def consume_queue(self): """ Iterates over queued unprocessed json data. Deletes all iterated rows. """ with analytical_transaction() as cursor: cursor.execute('SELECT `id`, `data` FROM `data_queue`') rows = cursor.fetchall() for row in rows: cursor.execute('DELETE FROM data_queue WHERE id = %s', row[0]) yield json.loads(row[1])
def new(self, project_identifier, forum_id): dibo = self.from_operational(project_identifier, forum_id) if not dibo: return None dibo['now'] = now_str() dibo_insert = """ INSERT INTO discussion_dim(discussion_sk, discussion_name, author, moderators, subscribers, subject, description, forum_key, project_key, project_name, project_identifier, VALID_FROM) VALUES(null, '%(discussion_name)s', '%(author)s', '%(moderators)s', '%(subscribers)s', '%(subject)s', '%(description)s', %(forum_key)d, %(project_key)d, '%(project_name)s', '%(project_identifier)s', '%(now)s')""" % dibo invalidate_old = """ UPDATE discussion_dim SET VALID_TO = '%(now)s' WHERE project_key = %(project_key)d AND forum_key = %(forum_key)d AND VALID_TO IS NULL""" % dibo with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(dibo_insert) except: conf.log.exception( "Failed creating a new record to discussion dimension. %s" % str(dibo)) raise return self.get_active_discussion_sk(project_identifier, forum_id)
def new(self, project_identifier, forum_id): dibo = self.from_operational(project_identifier, forum_id) if not dibo: return None dibo['now'] = now_str() dibo_insert = """ INSERT INTO discussion_dim(discussion_sk, discussion_name, author, moderators, subscribers, subject, description, forum_key, project_key, project_name, project_identifier, VALID_FROM) VALUES(null, '%(discussion_name)s', '%(author)s', '%(moderators)s', '%(subscribers)s', '%(subject)s', '%(description)s', %(forum_key)d, %(project_key)d, '%(project_name)s', '%(project_identifier)s', '%(now)s')""" % dibo invalidate_old = """ UPDATE discussion_dim SET VALID_TO = '%(now)s' WHERE project_key = %(project_key)d AND forum_key = %(forum_key)d AND VALID_TO IS NULL""" % dibo with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(dibo_insert) except: conf.log.exception("Failed creating a new record to discussion dimension. %s" % str(dibo)) raise return self.get_active_discussion_sk(project_identifier, forum_id)
def new(self, username): """ Create new dimension record from user """ user = self.from_operational(username) if not user: return None # Current time for user user['now'] = now_str() user_insert = """ INSERT INTO user_dim(user_sk, username, mail, mobile, givenName, lastName, authentication, status, user_key, VALID_FROM) VALUES(null, '%(username)s', '%(mail)s', '%(mobile)s', '%(givenName)s', '%(lastName)s', '%(authentication)s', '%(status)s', %(user_key)d, '%(now)s')""" % user invalidate_old = """ UPDATE user_dim SET VALID_TO = '%(now)s' WHERE username = '******' AND VALID_TO IS NULL""" % user with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(user_insert) except: conf.log.exception( "Failed creating a new user record to dimension. %s" % str(user)) raise return self.get_active_user_sk(username)
def drop_aged_partitions(self, table_plan): """ Drop aged partitions from the end so that max_partitions rule holds """ partitions_to_drop = len(table_plan.partitions) - table_plan.max_partitions # Sanity check calculation and then run if 0 < partitions_to_drop < table_plan.partitions: with analytical_transaction() as cursor: for partition in table_plan.partitions[:partitions_to_drop]: partition_name = 'p_%s' % partition drop_clause = "ALTER TABLE %s DROP PARTITION %s" % (table_plan.table, partition_name) try: cursor.execute(drop_clause) except: conf.log.exception("Unable to drop partition from table. %s" % drop_clause) conf.log.info("Dropped partitions for %s" % table_plan.table)
def new(self, identifier): """ Create new dimension record from project """ project = self.from_operational(identifier) if not project: return None # Current time for project project['now'] = now_str() project_insert = """ INSERT INTO project_dim(project_sk, identifier, project_name, author, created, updated, published, project_key, VALID_FROM) VALUES(null, '%(identifier)s', '%(project_name)s', '%(author)s', '%(created)s', '%(updated)s', '%(published)s', '%(project_key)s', '%(now)s')""" % project invalidate_old = """ UPDATE project_dim SET VALID_TO = '%(now)s' WHERE identifier = '%(identifier)s' AND VALID_TO IS NULL""" % project with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(project_insert) except Exception: conf.log.exception( "Failed creating a new project record to dimension. insert: %s\nupdate:%s" % (project_insert, invalidate_old)) raise return self.get_active_project_sk(identifier)
def new(self, username): """ Create new dimension record from user """ user = self.from_operational(username) if not user: return None # Current time for user user['now'] = now_str() user_insert = """ INSERT INTO user_dim(user_sk, username, mail, mobile, givenName, lastName, authentication, status, user_key, VALID_FROM) VALUES(null, '%(username)s', '%(mail)s', '%(mobile)s', '%(givenName)s', '%(lastName)s', '%(authentication)s', '%(status)s', %(user_key)d, '%(now)s')""" % user invalidate_old = """ UPDATE user_dim SET VALID_TO = '%(now)s' WHERE username = '******' AND VALID_TO IS NULL""" % user with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(user_insert) except: conf.log.exception("Failed creating a new user record to dimension. %s" % str(user)) raise return self.get_active_user_sk(username)
def new(self, identifier): """ Create new dimension record from project """ project = self.from_operational(identifier) if not project: return None # Current time for project project['now'] = now_str() project_insert = """ INSERT INTO project_dim(project_sk, identifier, project_name, author, created, updated, published, project_key, VALID_FROM) VALUES(null, '%(identifier)s', '%(project_name)s', '%(author)s', '%(created)s', '%(updated)s', '%(published)s', '%(project_key)s', '%(now)s')""" % project invalidate_old = """ UPDATE project_dim SET VALID_TO = '%(now)s' WHERE identifier = '%(identifier)s' AND VALID_TO IS NULL""" % project with analytical_transaction() as cursor: try: cursor.execute(invalidate_old) cursor.execute(project_insert) except Exception: conf.log.exception("Failed creating a new project record to dimension. insert: %s\nupdate:%s" % (project_insert, invalidate_old)) raise return self.get_active_project_sk(identifier)
def load_sql(self, sql_rows): """ Helper method to be used for loading sql into analytical database. If sql fails we write sqldump """ with analytical_transaction() as cursor: try: for row in sql_rows: cursor.execute(row) except: conf.log.exception("Doing sql dump failed.") self.dump_sql("\n".join(sql_rows)) # Used to return false here prior to rollback, but now we need to raise # the exception for the rollback to be called. raise return True
def drop_aged_partitions(self, table_plan): """ Drop aged partitions from the end so that max_partitions rule holds """ partitions_to_drop = len( table_plan.partitions) - table_plan.max_partitions # Sanity check calculation and then run if 0 < partitions_to_drop < table_plan.partitions: with analytical_transaction() as cursor: for partition in table_plan.partitions[:partitions_to_drop]: partition_name = 'p_%s' % partition drop_clause = "ALTER TABLE %s DROP PARTITION %s" % ( table_plan.table, partition_name) try: cursor.execute(drop_clause) except: conf.log.exception( "Unable to drop partition from table. %s" % drop_clause) conf.log.info("Dropped partitions for %s" % table_plan.table)
def run_in_analytical_db(self, sql): try: with analytical_transaction() as cursor: cursor.execute(sql) except: conf.log.exception("Summarizing failed with sql: %s" % sql)