Analytics on energy consumption

Import time-series data into a hypertable

1. Unzip

   metrics.csv.gz
   to a <local folder>.

   This test dataset contains energy consumption data.

   To import up to 100GB of data directly from your current PostgreSQL based database, migrate with downtime using native PostgreSQL tooling. To seamlessly import 100GB-10TB+ of data, use the live migration tooling supplied by Timescale. To add data from non-PostgreSQL data sources, see Import and ingest data.

2. In Terminal, navigate to <local folder> and update the following string with your connection details to connect to your service.

   psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"

   Copy

3. Create tables to import time-series data:

   1. In your sql client, create a normal PostgreSQL table:

      CREATE TABLE "metrics"(
        created timestamp with time zone default now() not null,
        type_id integer                                not null,
        value   double precision                       not null
      );

      Copy

   2. Convert metrics to a hypertable and partitioned on time:

      SELECT create_hypertable('metrics', by_range('created'));

      Copy

      To more fully understand how hypertables work, and how to optimize them for performance by tuning chunk intervals and enabling chunk skipping, see the hypertables documentation.

4. Upload the dataset to your service

   \COPY metrics FROM metrics.csv CSV;

   Copy

   To more fully understand how hypertables work, and how to optimize them for performance by tuning chunk intervals and enabling chunk skipping, see the hypertables documentation.

Have a quick look at your data

You query hypertables in exactly the same way as you would a relational PostgreSQL table. Use one of the following SQL editors to run a query and see the data you uploaded:

• Data mode: write queries, visualize data, and share your results in Timescale Console for all your Timescale Cloud services.
• SQL editor: write, fix, and organize SQL faster and more accurately in Timescale Console for a Timescale Cloud service.
• psql: easily run queries on your Timescale Cloud services or self-hosted TimescaleDB deployment from Terminal.

SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics                                   
WHERE type_id = 5
GROUP BY 1;

Copy

On this amount of data, this query on data in the rowstore takes about 3.6 seconds. You see something like:

Monitor energy consumption on a day-to-day basis

1. Create a continuous aggregate kwh_day_by_day for energy consumption:

   CREATE MATERIALIZED VIEW kwh_day_by_day(time, value)
      with (timescaledb.continuous) as
   SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
          round((last(value, created) - first(value, created)) * 100.) / 100. AS value
   FROM metrics
   WHERE type_id = 5
   GROUP BY 1;

   Copy

2. Add a refresh policy to keep kwh_day_by_day up-to-date:

   SELECT add_continuous_aggregate_policy('kwh_day_by_day',
      start_offset => NULL,
      end_offset => INTERVAL '1 hour',
      schedule_interval => INTERVAL '1 hour');

   Copy

Monitor energy consumption on an hourly basis

1. Create a continuous aggregate kwh_hour_by_hour for energy consumption:

   CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value)
     with (timescaledb.continuous) as
   SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time",
          round((last(value, created) - first(value, created)) * 100.) / 100. AS value
   FROM metrics
   WHERE type_id = 5
   GROUP BY 1;

   Copy

2. Add a refresh policy to keep the continuous aggregate up-to-date:

   SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
    start_offset => NULL,
       end_offset => INTERVAL '1 hour',
       schedule_interval => INTERVAL '1 hour');

   Copy

Analyze your data

Now you have made continuous aggregates, it could be a good idea to use them to perform analytics on your data. For example, to see how average energy consumption changes during weekdays over the last year, run the following query:

WITH per_day AS (
   SELECT
     time,
     value
   FROM kwh_day_by_day
   WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
   ORDER BY 1
  ), daily AS (
      SELECT
         to_char(time, 'Dy') as day,
         value
      FROM per_day
  ), percentile AS (
      SELECT
          day,
          approx_percentile(0.50, percentile_agg(value)) as value
      FROM daily
      GROUP BY 1
      ORDER BY 1
  )
  SELECT
      d.day,
      d.ordinal,
      pd.value
  FROM unnest(array['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat']) WITH ORDINALITY AS d(day, ordinal)
  LEFT JOIN percentile pd ON lower(pd.day) = lower(d.day);

Copy

You see something like:

Connect to your Timescale Cloud service

In Timescale Console open an SQL editor. The in-Console editors display the query speed. You can also connect to your service using psql.

Enable columnstore on a hypertable

Create a job that automatically moves chunks in a hypertable to the columnstore at a specific time interval. By default, your table is orderedby the time column. For efficient queries on columnstore data, remember to segmentby the column you will use most often to filter your data:

ALTER TABLE metrics SET (
  timescaledb.enable_columnstore = true,
  timescaledb.segmentby = 'type_id',
  timescaledb.orderby = 'created DESC'
);

Copy

Add a policy to convert chunks to the columnstore at a specific time interval

For example, 60 days after the data was added to the table:

CALL add_columnstore_policy('metrics', INTERVAL '8 days');

Copy

See add_columnstore_policy.

View your data space saving

When you convert data to the columnstore, as well as being optimized for analytics, it is compressed by more than 90%. This saves on storage costs and keeps your queries operating at lightning speed. To see the amount of space saved:

SELECT 
     pg_size_pretty(before_compression_total_bytes) as before,
     pg_size_pretty(after_compression_total_bytes) as after
FROM hypertable_compression_stats('metrics');

Copy

You see something like:

Faster analytical queries on data in the columnstore

Now run the analytical query again:

SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
 round((last(value, created) - first(value, created)) * 100.) / 100. AS value
 FROM metrics                                   
 WHERE type_id = 5
 GROUP BY 1;

Copy

On this amount of data, this analytical query on data in the columnstore takes about 0.8 seconds.

Log in to Grafana

In your browser, log in to either:

• Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
• Grafana Cloud: use the URL and credentials you set when you created your account.

Add your service as a data source

1. Open Connections > Data sources, then click Add new data source.

2. Select PostgreSQL from the list.

3. Configure the connection:

   • Host URL, Database name, Username, and Password

     Configure using your connection details. Host URL is in the format <host>:<port>.

   • TLS/SSL Mode: select require.

   • PostgreSQL options: enable TimescaleDB.

   • Leave the default setting for all other fields.

4. Click Save & test.

   Grafana checks that your details are set correctly.

Create the dashboard

1. On the Dashboards page, click New and select New dashboard.

2. Click Add visualization, then select the data source that connects to your Timescale Cloud service and the Bar chart visualization.

   

   Expand image

3. In the Queries section, select Code, then run the following query based on your continuous aggregate:

   WITH per_hour AS (
   SELECT
   time,
   value
   FROM kwh_hour_by_hour
   WHERE "time" at time zone 'Europe/Berlin' > date_trunc('month', time) - interval '1 year'
   ORDER BY 1
   ), hourly AS (
    SELECT
         extract(HOUR FROM time) * interval '1 hour' as hour,
         value
    FROM per_hour
   )
   SELECT
       hour,
       approx_percentile(0.50, percentile_agg(value)) as median,
       max(value) as maximum
   FROM hourly
   GROUP BY 1
   ORDER BY 1;

   Copy

   This query averages the results for households in a specific time zone by hour and orders them by time. Because you use a continuous aggregate, this data is always correct in real time.

   

   Expand image

   You see that energy consumption is highest in the evening and at breakfast time. You also know that the wind drops off in the evening. This data proves that you need to supply a supplementary power source for peak times, or plan to store energy during the day for peak times.

Click Save dashboard