def fetch_listens_from_storage(self, user_name, from_ts, to_ts, limit, order):
""" The timestamps are stored as UTC in the postgres datebase while on retrieving
the value they are converted to the local server's timezone. So to compare
datetime object we need to create a object in the same timezone as the server.
from_ts: seconds since epoch, in float
to_ts: seconds since epoch, in float
"""
# Quote single quote characters which could be used to mount an injection attack.
# Sadly, influxdb does not provide a means to do this in the client library
query = 'SELECT * FROM ' + get_escaped_measurement_name(user_name)
if from_ts is not None:
query += "WHERE time > " + get_influx_query_timestamp(from_ts)
else:
query += "WHERE time < " + get_influx_query_timestamp(to_ts)
query += " ORDER BY time " + ORDER_TEXT[order] + " LIMIT " + str(limit)
try:
results = self.influx.query(query)
except Exception as err:
self.log.error("Cannot query influx while getting listens for user: %s: %s", user_name, str(err), exc_info=True)
return []
listens = []
for result in results.get_points(measurement=get_measurement_name(user_name)):
listens.append(Listen.from_influx(result))
if order == ORDER_ASC:
listens.reverse()
return listens
def fetch_listens_from_storage(self, user_name, from_ts, to_ts, limit,
order):
""" The timestamps are stored as UTC in the postgres datebase while on retrieving
the value they are converted to the local server's timezone. So to compare
datetime object we need to create a object in the same timezone as the server.
from_ts: seconds since epoch, in float
to_ts: seconds since epoch, in float
"""
# Quote single quote characters which could be used to mount an injection attack.
# Sadly, influxdb does not provide a means to do this in the client library
query = 'SELECT * FROM ' + get_escaped_measurement_name(user_name)
if from_ts is not None:
query += "WHERE time > " + get_influx_query_timestamp(from_ts)
else:
query += "WHERE time < " + get_influx_query_timestamp(to_ts)
query += " ORDER BY time " + ORDER_TEXT[order] + " LIMIT " + str(limit)
try:
results = self.influx.query(query)
except Exception as err:
self.log.error("Cannot query influx: %s" % str(err))
return []
listens = []
for result in results.get_points(
measurement=get_measurement_name(user_name)):
listens.append(Listen.from_influx(result))
if order == ORDER_ASC:
listens.reverse()
return listens
def get_listens_batch_for_dump(self, username, dump_time, offset):
# loop until we get this chunk of listens
while True:
try:
return self.influx.query("""
SELECT *
FROM {measurement}
WHERE time <= {timestamp}
ORDER BY time DESC
LIMIT {limit}
OFFSET {offset}
""".format(
measurement=get_escaped_measurement_name(username),
timestamp=get_influx_query_timestamp(
dump_time.strftime('%s')),
limit=DUMP_CHUNK_SIZE,
offset=offset,
))
except Exception as e:
self.log.error(
'Error while getting listens to dump for user %s: %s',
username,
str(e),
exc_info=True)
time.sleep(3)
def fetch_recent_listens_for_users(self, user_list, limit=2, max_age=3600):
""" Fetch recent listens for a list of users, given a limit which applies per user. If you
have a limit of 3 and 3 users you should get 9 listens if they are available.
user_list: A list containing the users for which you'd like to retrieve recent listens.
limit: the maximum number of listens for each user to fetch.
max_age: Only return listens if they are no more than max_age seconds old. Default 3600 seconds
"""
escaped_user_list = []
for user_name in user_list:
escaped_user_list.append(get_escaped_measurement_name(user_name))
query = "SELECT username, * FROM " + ",".join(escaped_user_list)
query += " WHERE time > " + get_influx_query_timestamp(
int(time.time()) - max_age)
query += " ORDER BY time DESC LIMIT " + str(limit)
try:
results = self.influx.query(query)
except Exception as err:
self.log.error(
"Cannot query influx while getting listens for users: %s: %s",
user_list,
str(err),
exc_info=True)
return []
listens = []
for user in user_list:
for result in results.get_points(
measurement=get_measurement_name(user)):
l = Listen.from_influx(result)
listens.append(l)
return listens
def get_total_listen_count(self, cache_value=True):
""" Returns the total number of listens stored in the ListenStore.
First checks the brainzutils cache for the value, if not present there
makes a query to the db and caches it in brainzutils cache.
"""
if cache_value:
count = cache.get(
InfluxListenStore.REDIS_INFLUX_TOTAL_LISTEN_COUNT,
decode=False)
if count:
return int(count)
try:
result = self.influx.query(
"""SELECT %s
FROM "%s"
ORDER BY time DESC
LIMIT 1""" %
(COUNT_MEASUREMENT_NAME, TIMELINE_COUNT_MEASUREMENT))
except (InfluxDBServerError, InfluxDBClientError) as err:
self.log.error("Cannot query influx: %s" % str(err), exc_info=True)
raise
try:
item = result.get_points(
measurement=TIMELINE_COUNT_MEASUREMENT).__next__()
count = int(item[COUNT_MEASUREMENT_NAME])
timestamp = convert_to_unix_timestamp(item['time'])
except (KeyError, ValueError, StopIteration):
timestamp = 0
count = 0
# Now sum counts that have been added in the interval we're interested in
try:
result = self.influx.query(
"""SELECT sum(%s) as total
FROM "%s"
WHERE time > %s""" %
(COUNT_MEASUREMENT_NAME, TEMP_COUNT_MEASUREMENT,
get_influx_query_timestamp(timestamp)))
except (InfluxDBServerError, InfluxDBClientError) as err:
self.log.error("Cannot query influx: %s" % str(err), exc_info=True)
raise
try:
data = result.get_points(
measurement=TEMP_COUNT_MEASUREMENT).__next__()
count += int(data['total'])
except StopIteration:
pass
if cache_value:
cache.set(
InfluxListenStore.REDIS_INFLUX_TOTAL_LISTEN_COUNT,
int(count),
InfluxListenStore.TOTAL_LISTEN_COUNT_CACHE_TIME,
encode=False,
)
return count
def get_listens_batch_for_dump(self, username, end_time, offset):
""" Get a batch of listens for the full dump.
This does not query the `inserted_timestamp` field because not all of the listens
in the production database have them, so for full dumps this query needs to be independent
of the the `inserted_timestamp` key.
"""
while True:
try:
return self.influx.query("""
SELECT *
FROM {measurement}
WHERE time <= {timestamp}
ORDER BY time DESC
LIMIT {limit}
OFFSET {offset}
""".format(
measurement=get_escaped_measurement_name(username),
timestamp=get_influx_query_timestamp(
end_time.strftime('%s')),
limit=DUMP_CHUNK_SIZE,
offset=offset,
))
except Exception as e:
self.log.error(
'Error while getting listens to dump for user %s: %s',
username,
str(e),
exc_info=True)
time.sleep(3)
def write(self, listen_dicts):
submit = []
unique = []
duplicate_count = 0
unique_count = 0
# Partition the listens on the basis of user names
# and then store the time range for each user
users = {}
for listen in listen_dicts:
t = int(listen['listened_at'])
user_name = listen['user_name']
if user_name not in users:
users[user_name] = {
'min_time': t,
'max_time': t,
'listens': [listen],
}
continue
if t > users[user_name]['max_time']:
users[user_name]['max_time'] = t
if t < users[user_name]['min_time']:
users[user_name]['min_time'] = t
users[user_name]['listens'].append(listen)
# get listens in the time range for each user and
# remove duplicates on the basis of timestamps
for user_name in users:
# get the range of time that we need to get from influx for
# deduplication of listens
min_time = users[user_name]['min_time']
max_time = users[user_name]['max_time']
query = """SELECT time, recording_msid
FROM %s
WHERE time >= %s
AND time <= %s
""" % (get_escaped_measurement_name(user_name), get_influx_query_timestamp(min_time), get_influx_query_timestamp(max_time))
while True:
try:
results = self.influx.query(query)
break
except Exception as e:
self.log.error("Cannot query influx: %s" % str(e))
sleep(3)
# collect all the timestamps for this given time range.
timestamps = defaultdict(list) # dict of list of listens indexed by timestamp
for result in results.get_points(measurement=get_measurement_name(user_name)):
timestamps[convert_to_unix_timestamp(result['time'])].append(result)
for listen in users[user_name]['listens']:
# Check if a listen with the same timestamp and recording msid is already present in
# Influx DB and if it is, mark current listen as duplicate
t = int(listen['listened_at'])
recording_msid = listen['recording_msid']
dup = False
if t in timestamps:
for row in timestamps[t]:
if row['recording_msid'] == recording_msid:
duplicate_count += 1
dup = True
break
else:
# if there are listens with the same timestamp but different
# metadata, we add a tag specifically for making sure that
# influxdb doesn't drop one of the listens. This value
# is monotonically increasing and defaults to 0
listen['dedup_tag'] = len(timestamps[t])
if not dup:
unique_count += 1
submit.append(Listen.from_json(listen))
unique.append(listen)
timestamps[t].append({
'time': convert_timestamp_to_influx_row_format(t),
'recording_msid': recording_msid
})
t0 = time()
submitted_count = self.insert_to_listenstore(submit)
self.time += time() - t0
self.log.error("dups: %d, unique: %d, submitted: %d" % (duplicate_count, unique_count, submitted_count))
if not unique_count:
return True
while True:
try:
self.unique_ch.basic_publish(
exchange=self.config.UNIQUE_EXCHANGE,
routing_key='',
body=ujson.dumps(unique),
properties=pika.BasicProperties(delivery_mode = 2,),
)
break
except pika.exceptions.ConnectionClosed:
self.connect_to_rabbitmq()
return True
def dump_user(self, username, fileobj, dump_time):
""" Dump specified user's listens into specified file object.
Args:
username (str): the MusicBrainz ID of the user whose listens are to be dumped
fileobj (file): the file into which listens should be written
dump_time (datetime): the time at which the specific data dump was initiated
Returns:
int: the number of bytes this user's listens take in the dump file
"""
t0 = time.time()
offset = 0
bytes_written = 0
listen_count = 0
# Get this user's listens in chunks
while True:
# loop until we get this chunk of listens
while True:
try:
result = self.influx.query("""
SELECT *
FROM {measurement}
WHERE time <= {timestamp}
ORDER BY time DESC
LIMIT {limit}
OFFSET {offset}
""".format(
measurement=get_escaped_measurement_name(username),
timestamp=get_influx_query_timestamp(
dump_time.strftime('%s')),
limit=DUMP_CHUNK_SIZE,
offset=offset,
))
break
except Exception as e:
self.log.error('Error while getting listens for user %s',
user['musicbrainz_id'])
self.log.error(str(e))
time.sleep(3)
rows_added = 0
for row in result.get_points(get_measurement_name(username)):
listen = Listen.from_influx(row).to_api()
try:
bytes_written += fileobj.write(ujson.dumps(listen))
bytes_written += fileobj.write('\n')
rows_added += 1
except IOError as e:
log_ioerrors(self.log, e)
raise
except Exception as e:
self.log.error(
'Exception while creating json for user: %s',
user['musicbrainz_id'])
self.log.error(str(e))
raise
listen_count += rows_added
if not rows_added:
break
offset += DUMP_CHUNK_SIZE
time_taken = time.time() - t0
self.log.info(
'Listens for user %s dumped, total %d listens written at %.2f listens / sec!',
username, listen_count, listen_count / time_taken)
# the size for this user should not include the last newline we wrote
# hence return bytes_written - 1 as the size in the dump for this user
return bytes_written - 1
def dump_user(self, username, fileobj, dump_time):
""" Dump specified user's listens into specified file object.
Args:
username (str): the MusicBrainz ID of the user whose listens are to be dumped
fileobj (file): the file into which listens should be written
dump_time (datetime): the time at which the specific data dump was initiated
Returns:
int: the number of bytes this user's listens take in the dump file
"""
t0 = time.time()
offset = 0
bytes_written = 0
listen_count = 0
# Get this user's listens in chunks
while True:
# loop until we get this chunk of listens
while True:
try:
result = self.influx.query("""
SELECT *
FROM {measurement}
WHERE time <= {timestamp}
ORDER BY time DESC
LIMIT {limit}
OFFSET {offset}
""".format(
measurement=get_escaped_measurement_name(username),
timestamp=get_influx_query_timestamp(dump_time.strftime('%s')),
limit=DUMP_CHUNK_SIZE,
offset=offset,
))
break
except Exception as e:
self.log.error('Error while getting listens to dump for user %s: %s', user['musicbrainz_id'], str(e), exc_info=True)
time.sleep(3)
rows_added = 0
for row in result.get_points(get_measurement_name(username)):
listen = Listen.from_influx(row).to_api()
listen['user_name'] = username
try:
bytes_written += fileobj.write(ujson.dumps(listen))
bytes_written += fileobj.write('\n')
rows_added += 1
except IOError as e:
self.log.critical('IOError while writing listens into file for user %s', username, exc_info=True)
raise
except Exception as e:
self.log.error('Exception while creating json for user %s: %s', user['musicbrainz_id'], str(e), exc_info=True)
raise
listen_count += rows_added
if not rows_added:
break
offset += DUMP_CHUNK_SIZE
time_taken = time.time() - t0
self.log.info('Listens for user %s dumped, total %d listens written at %.2f listens / sec!',
username, listen_count, listen_count / time_taken)
# the size for this user should not include the last newline we wrote
# hence return bytes_written - 1 as the size in the dump for this user
return bytes_written - 1
def get_total_listen_count(self, cache_value=True):
""" Returns the total number of listens stored in the ListenStore.
First checks the brainzutils cache for the value, if not present there
makes a query to the db and caches it in brainzutils cache.
"""
if cache_value:
count = cache.get(InfluxListenStore.REDIS_INFLUX_TOTAL_LISTEN_COUNT, decode=False)
if count:
return int(count)
try:
result = self.influx.query("""SELECT %s
FROM "%s"
ORDER BY time DESC
LIMIT 1""" % (COUNT_MEASUREMENT_NAME, TIMELINE_COUNT_MEASUREMENT))
except (InfluxDBServerError, InfluxDBClientError) as err:
self.log.error("Cannot query influx: %s" % str(err), exc_info=True)
raise
try:
item = result.get_points(measurement=TIMELINE_COUNT_MEASUREMENT).__next__()
count = int(item[COUNT_MEASUREMENT_NAME])
timestamp = convert_to_unix_timestamp(item['time'])
except (KeyError, ValueError, StopIteration):
timestamp = 0
count = 0
# Now sum counts that have been added in the interval we're interested in
try:
result = self.influx.query("""SELECT sum(%s) as total
FROM "%s"
WHERE time > %s""" % (COUNT_MEASUREMENT_NAME, TEMP_COUNT_MEASUREMENT, get_influx_query_timestamp(timestamp)))
except (InfluxDBServerError, InfluxDBClientError) as err:
self.log.error("Cannot query influx: %s" % str(err), exc_info=True)
raise
try:
data = result.get_points(measurement=TEMP_COUNT_MEASUREMENT).__next__()
count += int(data['total'])
except StopIteration:
pass
if cache_value:
cache.set(
InfluxListenStore.REDIS_INFLUX_TOTAL_LISTEN_COUNT,
int(count),
InfluxListenStore.TOTAL_LISTEN_COUNT_CACHE_TIME,
encode=False,
)
return count
def dump_listens(self, location, dump_time=datetime.today(), threads=None):
""" Fetches listens of each user from her measurement and dumps them into a file.
These files are compressed into an archive.
Args:
location: the directory where the listens dump archive should be created
dump_time (datetime): the time at which the data dump was started
threads (int): the number of threads to user for compression
Returns:
the path to the dump archive
"""
self.log.info('Beginning dump of listens from InfluxDB...')
self.log.info(
'Getting list of users whose listens are to be dumped...')
users = db_user.get_all_users()
self.log.info('Total number of users: %d', len(users))
archive_name = 'listenbrainz-listens-dump-{time}'.format(
time=dump_time.strftime('%Y%m%d-%H%M%S'))
archive_path = os.path.join(
location, '{filename}.tar.xz'.format(filename=archive_name))
with open(archive_path, 'w') as archive:
pxz_command = ['pxz', '--compress']
if threads is not None:
pxz_command.append('-T {threads}'.format(threads=threads))
pxz = subprocess.Popen(pxz_command,
stdin=subprocess.PIPE,
stdout=archive)
with tarfile.open(fileobj=pxz.stdin, mode='w|') as tar:
temp_dir = tempfile.mkdtemp()
try:
# add timestamp
timestamp_path = os.path.join(temp_dir, 'TIMESTAMP')
with open(timestamp_path, 'w') as f:
f.write(dump_time.isoformat(' '))
tar.add(timestamp_path,
arcname=os.path.join(archive_name, 'TIMESTAMP'))
# add schema version
schema_version_path = os.path.join(temp_dir,
'SCHEMA_SEQUENCE')
with open(schema_version_path, 'w') as f:
f.write(str(LISTENS_DUMP_SCHEMA_VERSION))
tar.add(schema_version_path,
arcname=os.path.join(archive_name,
'SCHEMA_SEQUENCE'))
# add copyright notice
tar.add(DUMP_LICENSE_FILE_PATH,
arcname=os.path.join(archive_name, 'COPYING'))
except IOError as e:
log_ioerrors(self.log, e)
raise
except Exception as e:
self.log.error('Exception while adding dump metadata: %s',
str(e))
raise
listens_path = os.path.join(temp_dir, 'listens')
create_path(listens_path)
# get listens from all measurements and write them to files in
# a temporary dir before adding them to the archive
for user in users:
username = user['musicbrainz_id']
offset = 0
user_listens_file = '{username}.listens'.format(
username=username)
user_listens_path = os.path.join(listens_path,
user_listens_file)
with open(user_listens_path, 'w') as f:
# Get this user's listens in chunks
while True:
# loop until we get this chunk of listens
while True:
try:
result = self.influx.query("""
SELECT *
FROM {measurement}
WHERE time <= {timestamp}
ORDER BY time DESC
LIMIT {limit}
OFFSET {offset}
""".format(
measurement=
get_escaped_measurement_name(username),
timestamp=get_influx_query_timestamp(
dump_time.strftime('%s')),
limit=DUMP_CHUNK_SIZE,
offset=offset,
))
break
except Exception as e:
self.log.error(
'Error while getting listens for user %s',
user['musicbrainz_id'])
self.log.error(str(e))
time.sleep(3)
rows = list(
result.get_points(
get_measurement_name(username)))
if not rows:
break
for row in rows:
listen = Listen.from_influx(row).to_api()
try:
f.write(ujson.dumps(listen))
f.write('\n')
except IOError as e:
log_ioerrors(self.log, e)
raise
except Exception as e:
self.log.error(
'Exception while creating json for user: %s',
user['musicbrainz_id'])
self.log.error(str(e))
raise
offset += DUMP_CHUNK_SIZE
# add the listens directory to the archive
self.log.info('Got all listens, adding them to the archive...')
tar.add(listens_path,
arcname=os.path.join(archive_name, 'listens'))
# remove the temporary directory
shutil.rmtree(temp_dir)
pxz.stdin.close()
self.log.info('ListenBrainz listen dump done!')
self.log.info('Dump present at %s!', archive_path)
return archive_path
def write(self, listen_dicts):
submit = []
unique = []
duplicate_count = 0
unique_count = 0
# Partition the listens on the basis of user names
# and then store the time range for each user
users = {}
for listen in listen_dicts:
t = int(listen['listened_at'])
user_name = listen['user_name']
if user_name not in users:
users[user_name] = {
'min_time': t,
'max_time': t,
'listens': [listen],
}
continue
if t > users[user_name]['max_time']:
users[user_name]['max_time'] = t
if t < users[user_name]['min_time']:
users[user_name]['min_time'] = t
users[user_name]['listens'].append(listen)
# get listens in the time range for each user and
# remove duplicates on the basis of timestamps
for user_name in users:
# get the range of time that we need to get from influx for
# deduplication of listens
min_time = users[user_name]['min_time']
max_time = users[user_name]['max_time']
# quering for artist name here, since a field must be included in the query.
query = """SELECT time, artist_name
FROM %s
WHERE time >= %s
AND time <= %s
""" % (get_escaped_measurement_name(user_name),
get_influx_query_timestamp(min_time),
get_influx_query_timestamp(max_time))
while True:
try:
results = self.influx.query(query)
break
except Exception as e:
self.log.error("Cannot query influx: %s" % str(e))
sleep(3)
# collect all the timestamps for this given time range.
timestamps = {}
for result in results.get_points(
measurement=get_measurement_name(user_name)):
timestamps[convert_to_unix_timestamp(result['time'])] = 1
for listen in users[user_name]['listens']:
# Check if this listen is already present in Influx DB and if it is
# mark current listen as duplicate
t = int(listen['listened_at'])
if t in timestamps:
duplicate_count += 1
continue
else:
unique_count += 1
submit.append(Listen.from_json(listen))
unique.append(listen)
timestamps[t] = 1
t0 = time()
submitted_count = self.insert_to_listenstore(submit)
self.time += time() - t0
self.log.error("dups: %d, unique: %d, submitted: %d" %
(duplicate_count, unique_count, submitted_count))
if not unique_count:
return True
while True:
try:
self.unique_ch.basic_publish(exchange='unique',
routing_key='',
body=ujson.dumps(unique),
properties=pika.BasicProperties(
delivery_mode=2, ))
break
except pika.exceptions.ConnectionClosed:
self.connect_to_rabbitmq()
return True
