WIP for the first step, adding an integerfield primary key to <code class="notranslate

Hm, do you know where this is defined? <div class="highlight highlight-source-sql

[...] I'm still struggling with INDEX issues. <p dir="a

<div class="highlight highlight-source-python notranslate position-relative overflow-auto" dir="auto

We need to create dummy sensors for <div class="snippet-clipboard-content notransl

The we can go back to, for each sensor, <div class="highlight highlight-source-pyt

WIP Generating primary key for raw_data about friskby HOT 13 CLOSED

friskbybergen commented on August 11, 2024

WIP Generating primary key for raw_data

from friskby.

Comments (13)

pgdr commented on August 11, 2024

Hm, do you know where this is defined?

CREATE INDEX sensor_sensor_7a592b3b
  ON public.sensor_sensor
  USING btree
  (sensor_type_id);

In any case, we might have to delete any INDEX before performing step 2: moving primary key from sensor_id to s_id (integer key).

from friskby.

joakim-hove commented on August 11, 2024

This PR is only step 1? That is the way it should be in my opinion. 19. feb. 2017 23:22 skrev "Pål Grønås Drange" <[email protected]>:

…

Hm, do you know where this is defined? CREATE INDEX sensor_sensor_7a592b3b ON public.sensor_sensor USING btree (sensor_type_id); In any case, we might have to delete any INDEX before performing step 2: moving primary key from sensor_id to s_id (integer key). — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#145 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ABQqwY9Z7-_OVAMIsb-c8xet9v-hK-htks5reMCugaJpZM4MFnRB> .

from friskby.

pgdr commented on August 11, 2024

This is only step 1, yes. I'm still struggling with INDEX issues. Perhaps the migration needs to drop all the indices:

Indexes:
    "filter_filterdata_pkey" PRIMARY KEY, btree (id)
    "filter_filterdata_sensor_id_6482fe566b49ce37_uniq" UNIQUE CONSTRAINT, btree (sensor_id, filter_code_id)
    "filter_filterdata_ts_id_7167e05c2931e0a5_uniq" UNIQUE CONSTRAINT, btree (ts_id)
    "filter_filterdata_4bd0d623" btree (filter_code_id)
    "filter_filterdata_7ad5859e" btree (ts_id)
    "filter_filterdata_d96d866a" btree (sensor_id)
    "filter_filterdata_filter_code_id_cdca74721628e57_like" btree (filter_code_id varchar_pattern_ops)
    "filter_filterdata_sensor_id_31261240c8b18c90_like" btree (sensor_id varchar_pattern_ops)
Foreign-key constraints:
    "filt_ts_id_7167e05c2931e0a5_fk_time_series_regulartimeseries_id" FOREIGN KEY (ts_id) REFERENCES time_series_regulartimeseries(id) DEFERRABLE INITIALLY DEFERRED
    "filter_filte_filter_code_id_cdca74721628e57_fk_filter_filter_id" FOREIGN KEY (filter_code_id) REFERENCES filter_filter(id) DEFERRABLE INITIALLY DEFERRED
    "filter_filterdat_sensor_id_31261240c8b18c90_fk_sensor_sensor_id" FOREIGN KEY (sensor_id) REFERENCES sensor_sensor(sensor_id) DEFERRABLE INITIALLY DEFERRED

from friskby.

joakim-hove commented on August 11, 2024

[...] I'm still struggling with INDEX issues.

Hehe - this stuff is beyond my SQL pay-grade 😄

from friskby.

pgdr commented on August 11, 2024

I've done it now locally.

I had to delete the filter*-tables, and I also deleted time_series* and plot-* tables.

from friskby.

pgdr commented on August 11, 2024

def forwards(apps, schema_editor):
    import sys
    if not schema_editor.connection.alias == 'default':
        return
    sensor = apps.get_model('sensor', 'Sensor')
    rawdata = apps.get_model('sensor', 'RawData')

    sensors = {}
    for row in sensor.objects.all():
        sensor_name = row.sensor_id
        sensor_id = row.s_id
        sensors[sensor_name] = row

    for row in rawdata.objects.all():
        sensor_name = row.sensor_id
        if sensor_name not in sensors:
            sys.stderr.write('\nNo sensor for key "%s".\n' % sensor_name)
            row.s_id = None
        else:
            row.s_id = sensors[sensor_name]
        row.save()

With RawData like:

class RawData(Model):
    sensor_id = CharField(max_length=128)
    s_id = ForeignKey( Sensor, null = True )
    timestamp_recieved = DateTimeField(  ) 
    timestamp_data = DateTimeField( )
    value = FloatField( default = -1 )
    processed = BooleanField( default = False )

from friskby.

pgdr commented on August 11, 2024

We need to create dummy sensors for

     84 No sensor for key "OAMTempDHT22OJBrochsGate".
    288 No sensor for key "ARUdustTest".
   2055 No sensor for key "ARUtest".
   7973 No sensor for key "OAMLysOJBrochsGate".
   7974 No sensor for key "OAMFuktOJBrochsGate".
   7974 No sensor for key "OAMStoevOJBrochsGate".
   7974 No sensor for key "OAMTempOJBrochsGate".
 413929 No sensor for key "NJBGassGroenllien".

But other than that, we're ready to go!

@joakim-hove Is it okay if we delete the filter-tables in the heroku db?

from friskby.

joakim-hove commented on August 11, 2024

Delete delete!

When it comes to dummy sensors we might go the delete route instead?

from friskby.

pgdr commented on August 11, 2024

Definitely. What's the gas sensor? Almost 500k rows?

from friskby.

pgdr commented on August 11, 2024

The we can go back to, for each sensor,

def get_data_list(sensor, start, end):
    data_all = RawData.objects.filter(s_id=sensor.s_id,
                                      timestamp_data__range=
                                      (start,end)).values("value",
                                                          "timestamp_data"
                                      ).order_by('timestamp_data')
    datalist = downsample(list(data_all), minutes=30, cutoff=100)
    datalist = map( make_timestamp , datalist )
    return datalist

And we're more or less done with the migration. :)

from friskby.

pgdr commented on August 11, 2024

Have performed the following steps upstream:

make field sensor.s_id.
make field sensor.s_id be the primary key for the Sensor model.
created foreign key rawdata.s_id that formally links each rawdata entry to a sensor. (PR imminent)

The latter step is currently under testing at my machine. If successes galore, I will heroku push and migrate. This step, however, takes appx 10 minutes (strange, but perhaps because it write 500k rows and tries to make it reversible?).

from friskby.

pgdr commented on August 11, 2024

In fact, after indexing on rawdata.timestamp (which we should consider),

                            min   p10   p25   p50   p75   p90   max
before:  0.488 +/- 0.037, (0.430 0.450 0.460 0.480 0.500 0.540 0.660)
after:   0.466 +/- 0.039, (0.410 0.430 0.440 0.450 0.480 0.530 0.640)
morten:  0.369 +/- 0.029, (0.330 0.340 0.350 0.360 0.380 0.410 0.550)
current: 0.339 +/- 0.038, (0.300 0.310 0.320 0.330 0.340 0.380 0.810)
int_id:  0.265 +/- 0.020, (0.240 0.250 0.250 0.260 0.270 0.290 0.370)
indexed: 0.188 +/- 0.024, (0.160 0.170 0.170 0.180 0.190 0.210 0.300)

When we started way back in the beginning (before quick), we were at several seconds for SQL. Now we're down to less than 0.2s!

from friskby.

pgdr commented on August 11, 2024

#173 #174 #175 fixed this

from friskby.

WIP Generating primary key for raw_data about friskby HOT 13 CLOSED

Comments (13)

Related Issues (20)

Recommend Projects

React

Vue.js

Typescript

TensorFlow

Django

Laravel

D3

Recommend Topics

javascript

web

server

Machine learning

Visualization

Game

Recommend Org

Facebook

Microsoft

Google

Alibaba

D3

Tencent