def forwards(self, orm): "Write your forwards methods here." # Note: Don't use "from appname.models import ModelName". # Use orm.ModelName to refer to models in this application, # and orm['appname.ModelName'] for models in other applications. influx_client.post('query', ''' CREATE CONTINUOUS QUERY "cq_1h" ON "{0}" RESAMPLE EVERY 1h BEGIN SELECT max("value"), min("value"), mean("value"), count("value"), sum("value") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1h), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1h', INFLUX_MEASUREMENT), True) influx_client.post('query', ''' CREATE CONTINUOUS QUERY "cq_1d" ON "{0}" RESAMPLE FOR 2d BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1d), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1d', INFLUX_MEASUREMENT + '_1h'), True) influx_client.post('query', ''' CREATE CONTINUOUS QUERY "cq_1w" ON "{0}" RESAMPLE FOR 2w BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1w), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1w', INFLUX_MEASUREMENT + '_1d'), True)
def forwards(self, orm): "Write your forwards methods here." # Note: Don't use "from appname.models import ModelName". # Use orm.ModelName to refer to models in this application, # and orm['appname.ModelName'] for models in other applications. influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1h" ON "{0}" RESAMPLE EVERY 1h BEGIN SELECT max("value"), min("value"), mean("value"), count("value"), sum("value") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1h), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1h', INFLUX_MEASUREMENT), True) influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1d" ON "{0}" RESAMPLE FOR 2d BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1d), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1d', INFLUX_MEASUREMENT + '_1h'), True) influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1w" ON "{0}" RESAMPLE FOR 2w BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1w), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1w', INFLUX_MEASUREMENT + '_1d'), True)
def add_cqs(apps, schema_editor): influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1h" ON "{0}" RESAMPLE EVERY 1h BEGIN SELECT max("value"), min("value"), mean("value"), count("value"), sum("value") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1h), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1h', INFLUX_MEASUREMENT), True) influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1d" ON "{0}" RESAMPLE FOR 2d BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1d), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1d', INFLUX_MEASUREMENT + '_1h'), True) influx_client.post( 'query', ''' CREATE CONTINUOUS QUERY "cq_1w" ON "{0}" RESAMPLE FOR 2w BEGIN SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{1}" FROM "{2}" GROUP BY "sensor_id", time(1w), * END '''.format(INFLUX_DATABASE, INFLUX_MEASUREMENT + '_1w', INFLUX_MEASUREMENT + '_1d'), True)
def backwards(self, orm): "Write your backwards methods here." influx_client.post('query', 'DROP CONTINUOUS QUERY "cq_1h" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post('query', 'DROP CONTINUOUS QUERY "cq_1d" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post('query', 'DROP CONTINUOUS QUERY "cq_1w" on "{}"'.format(INFLUX_DATABASE), True)
def old_post_data(self, site_id, device_id, sensor_id, value, timestamp=None): timestamp = InfluxClient.convert_timestamp(timestamp) data = '{0},sensor_id={1},site_id={2},device_id={3} value={4}'.format( INFLUX_MEASUREMENT, sensor_id, site_id, device_id, value) if timestamp: data += ' ' + str(timestamp) response = influx_client.post('write', data) if response.status_code != HTTP_STATUS_SUCCESSFUL_WRITE: raise IntegrityError('Error storing data') return response
def remove_cqs(apps, schema_editor): influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1h" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1d" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1w" on "{}"'.format(INFLUX_DATABASE), True)
def backwards(self, orm): "Write your backwards methods here." influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1h" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1d" on "{}"'.format(INFLUX_DATABASE), True) influx_client.post( 'query', 'DROP CONTINUOUS QUERY "cq_1w" on "{}"'.format(INFLUX_DATABASE), True)
def setUpBeforeMigration(self, apps): # add the data in the old format (without the metric tag) for data in self.scalar_data: self.old_post_data(data['sensor'].device.site.id, data['sensor'].device.id, data['sensor'].id, data['value'], data['timestamp']) influx_client.post( 'query', ''' SELECT max("value"), min("value"), mean("value"), count("value"), sum("value") INTO "{0}" FROM "{1}" GROUP BY "sensor_id", time(1h), * '''.format(INFLUX_MEASUREMENT + '_1h', INFLUX_MEASUREMENT), True) influx_client.post( 'query', ''' SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{0}" FROM "{1}" GROUP BY "sensor_id", time(1d), * '''.format(INFLUX_MEASUREMENT + '_1d', INFLUX_MEASUREMENT + '_1h'), True) influx_client.post( 'query', ''' SELECT max("max"), min("min"), sum("sum")/sum("count") as "mean", sum("count") as "count", sum("sum") INTO "{0}" FROM "{1}" GROUP BY "sensor_id", time(1w), * '''.format(INFLUX_MEASUREMENT + '_1w', INFLUX_MEASUREMENT + '_1d'), True)
def add_convenience_tags(apps, schema_editor): sensors = ScalarSensor.objects.all() print("\n\nMigrating data for {} sensors".format(len(sensors))) stdout.flush() for agg in ["", "_1h", "_1d", "_1w"]: measurement = influx_client._measurement + agg print("Migrating data from {} measurement...".format(measurement)) stdout.flush() sensorsmigrated = 0 datamigrated = 0 for sensor in sensors: device = sensor.device site = device.site print( "\rMigrating {} of {} sensors (requesting count) " .format(sensorsmigrated + 1, len(sensors)), end='') stdout.flush() # doesn't really matter which column we use for the aggregates countcol = "value" if agg == "" else "mean" countdata = influx_client.get( "SELECT COUNT({}) FROM {} WHERE sensor_id = '{}' AND metric = ''" .format(countcol, measurement, sensor.id), True).json() try: assert len(countdata["results"]) == 1 result = countdata["results"][0] if result == {}: sensorsmigrated += 1 continue assert len(result["series"]) == 1 series = result["series"][0] assert len(series["columns"]) == 2 assert len(series["values"]) == 1 count = series["values"][0][series["columns"].index("count")] except: print("\n==============================") print(countdata) print("================================") raise # select all this sensor's data that doesn't yet have a metric # apparently working through large datasets with LIMIT and OFFSET # is slow, so we'll go through time instead starttime = 0 offset = 0 while True: # note this doesn't work if you specify the time as {}ns explicitly query = "SELECT * FROM {} WHERE time > {} AND sensor_id = '{}' AND metric = '' LIMIT {}".format( measurement, starttime, sensor.id, CHUNK_LIMIT) print( "\rMigrating {} of {} sensors (requesting data {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() db_data = influx_client.get_values( influx_client.get(query, True, epoch="ns")) if len(db_data) == 0: break print( "\rMigrating {} of {} sensors (processing values for {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() if agg == "": values = [d["value"] for d in db_data] print( "\rMigrating {} of {} sensors (processing timestamps for {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() try: timestamps = [ ms_to_dt(d["time"] / 1000000) for d in db_data ] except: print(d["time"]) raise # TODO: I think under-the-hood this ends up converting back and forth # between dict-of-arrays and array-of-dicts format, so there's some # opportunity for optimizastion print( "\rMigrating {} of {} sensors (posting data {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() influx_client.post_data_bulk(site.id, device.id, sensor.id, sensor.metric, values, timestamps) else: querylines = [] print( "\rMigrating {} of {} sensors (building query for data {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() for data in db_data: fieldstrs = [] for rollup in ['min', 'max', 'sum', 'mean']: if data[rollup] is not None: fieldstrs.append('{}={}'.format( rollup, data[rollup])) if data['count'] is not None: fieldstrs.append('count={}i'.format(data['count'])) querylines.append( "{},sensor_id={},site_id={},device_id={},metric={} {} {}" .format( measurement, sensor.id, site.id, device.id, sensor.metric, ",".join(fieldstrs), InfluxClient.convert_timestamp( ms_to_dt(data['time'] / 1000000)))) print( "\rMigrating {} of {} sensors (consolidating query for data {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() query = '\n'.join(querylines) print( "\rMigrating {} of {} sensors (posting data {} of {}) " .format(sensorsmigrated + 1, len(sensors), offset + 1, count), end='') stdout.flush() response = influx_client.post('write', query) if response.status_code != HTTP_STATUS_SUCCESSFUL_WRITE: raise IntegrityError( 'Failed Query(status {}):\n{}\nResponse:\n{}'. format(response.status_code, data, response.json())) starttime = db_data[-1]["time"] offset += len(db_data) datamigrated += len(db_data) # we've finished all the data for this sensor, delete the old data query = "DELETE FROM {} WHERE sensor_id = '{}' AND metric = ''".format( measurement, sensor.id) print( "\rMigrating {} of {} sensors (deleting old data) " .format(sensorsmigrated + 1, len(sensors)), end='') stdout.flush() influx_client.post("query", query, True) sensorsmigrated += 1 print("\nMigrated {} data points for measurement {}\n".format( datamigrated, measurement)) stdout.flush()