> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Analyze energy consumption

> Get insights from energy consumption data with Grafana and Tiger Cloud

export const CAGG_CAP = 'Continuous aggregate';

export const SERVICE_LONG = 'Tiger Cloud service';

export const SELF_LONG = 'self-hosted TimescaleDB';

export const DATA_MODE = 'Data view';

export const CONSOLE = 'Tiger Console';

export const COMPANY = 'Tiger Data ';

export const PG = 'Postgres';

export const HYPERTABLE_CAP = 'Hypertable';

export const HYPERCORE = 'hypercore';

export const ROWSTORE = 'rowstore';

export const HYPERCORE_CAP = 'Hypercore';

export const CHUNK = 'chunk';

export const HYPERTABLE = 'hypertable';

export const COLUMNSTORE = 'columnstore';

export const TIMESCALE_DB = 'TimescaleDB';

export const SERVICE_SHORT = 'service';

export const CAGG = 'continuous aggregate';

Energy providers understand that customers tend to lose patience when there is not enough power for them
to complete day-to-day activities. Task one is keeping the lights on. If you are transitioning to renewable energy,
it helps to know when you need to produce energy so you can choose a suitable energy source.

Real-time analytics refers to the process of collecting, analyzing, and interpreting data instantly as it is generated.
This approach enables you to track and monitor activity, make the decisions based on real-time insights on data stored in
a {SERVICE_LONG} and keep those lights on.

[Grafana][grafana-docs] is a popular data visualization tool that enables you to create customizable dashboards
and effectively monitor your systems and applications.

![Grafana real-time analytics][grafana-real-time-analytics]

This page shows you how to integrate Grafana with a {SERVICE_LONG} and make insights based on visualization of
data optimized for size and speed in the {COLUMNSTORE}.

## Prerequisites

To follow the steps on this page:

* Create a target [{SERVICE_LONG}][create-service] with Real-time analytics enabled.<p />

  You need [your connection details][connection-info]. This procedure also
  works for [{SELF_LONG}][enable-timescaledb].

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[enable-timescaledb]: /deploy-and-operate/self-hosted/install-and-update/install-self-hosted

[connection-info]: /integrations/find-connection-details

* Install and run [self-managed Grafana][grafana-self-managed], or sign up for [Grafana Cloud][grafana-cloud].

## Optimize time-series data in hypertables

{HYPERTABLE_CAP}s are {PG} tables in {TIMESCALE_DB} that automatically partition your time-series data by time. Time-series data represents the way a system, process, or behavior changes over time. {HYPERTABLE_CAP}s enable {TIMESCALE_DB} to work efficiently with time-series data. Each {HYPERTABLE} is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, {TIMESCALE_DB} identifies the correct chunk and runs the query on it, instead of going through the entire table.

[{HYPERCORE_CAP}][hypercore] is the hybrid row-columnar storage engine in {TIMESCALE_DB} used by {HYPERTABLE}s. Traditional
databases force a trade-off between fast inserts (row-based storage) and efficient analytics
(columnar storage). {HYPERCORE_CAP} eliminates this trade-off, allowing real-time analytics without sacrificing
transactional capabilities.

{HYPERCORE_CAP} dynamically stores data in the most efficient format for its lifecycle:

![Move from rowstore to columstore in hypercore][move-from-rowstore-to-columstore-in-hypercore]

* **Row-based storage for recent data**: the most recent chunk (and possibly more) is always stored in the {ROWSTORE},
  ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a
  writethrough for inserts and updates to columnar storage.
* **Columnar storage for analytical performance**: chunks are automatically compressed into the {COLUMNSTORE}, optimizing
  storage efficiency and accelerating analytical queries.

Unlike traditional columnar databases, {HYPERCORE} allows data to be inserted or modified at any stage, making it a
flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database.

[hypercore]: /manage-data/capabilities/hypercore/understand-hypercore

[move-from-rowstore-to-columstore-in-hypercore]: https://assets.timescale.com/docs/images/hypercore_intro.svg

Because {TIMESCALE_DB} is 100% {PG}, you can use all the standard {PG} tables, indexes, stored procedures, and other objects alongside your {HYPERTABLE}s. This makes creating and working with {HYPERTABLE}s similar to standard {PG}.

1. **Import time-series data into a {HYPERTABLE}**

   1. Unzip [metrics.csv.gz][metricscsvgz] to a `<local folder>`.

      This test dataset contains energy consumption data.

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

   2. In Terminal, navigate to `<local folder>` and update the following string with [your connection details][connection-info]
      to connect to your {SERVICE_SHORT}.

      ```bash theme={"dark"}
      psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>?sslmode=require"
      ```

   3. Create an optimized {HYPERTABLE} for your time-series data:

      1. Create a [{HYPERTABLE}][hypertables-section] with [{HYPERCORE}][hypercore] enabled by default for your
         time-series data using [CREATE TABLE][hypertable-create-table]. For [efficient queries][secondary-indexes]
         on data in the {COLUMNSTORE}, remember to `segmentby` the column you will use most often to filter your data.

         In your sql client, run the following command:

         ```sql theme={"dark"}
         CREATE TABLE "metrics"(
           created timestamp with time zone default now() not null,
           type_id integer                                not null,
           value   double precision                       not null
         ) WITH (
           tsdb.hypertable,
           tsdb.segmentby = 'type_id',
           tsdb.orderby = 'created DESC'
         );
         ```

         When you create a {HYPERTABLE} using [CREATE TABLE ... WITH ...][hypertable-create-table], the default partitioning
         column is automatically the first column with a timestamp data type. Also, {TIMESCALE_DB} creates a
         [columnstore policy][add_columnstore_policy] that automatically converts your data to the {COLUMNSTORE}, after an
         interval equal to the value of the [chunk\_interval][create_table_arguments], defined through `compress_after` in the
         policy. This columnar format enables fast scanning and
         aggregation, optimizing performance for analytical workloads while also saving significant storage space. In the
         {COLUMNSTORE} conversion, {HYPERTABLE} {CHUNK}s are compressed by up to 98%, and organized for efficient, large-scale queries.

         You can customize this policy later using [alter\_job][alter_job_samples]. However, to change `after` or
         `created_before`, the compression settings, or the {HYPERTABLE} the policy is acting on, you must
         [remove the columnstore policy][remove_columnstore_policy] and [add a new one][add_columnstore_policy].

         You can also manually [convert {CHUNK}s][convert_to_columnstore] in a {HYPERTABLE} to the {COLUMNSTORE}.

         [add_columnstore_policy]: /api-reference/timescaledb/hypercore/add_columnstore_policy

         [alter_job_samples]: /api-reference/timescaledb/jobs-automation/alter_job#samples

         [convert_to_columnstore]: /api-reference/timescaledb/hypercore/convert_to_columnstore

         [create_table_arguments]: /api-reference/timescaledb/hypertables/create_table#arguments

         [hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

         [remove_columnstore_policy]: /api-reference/timescaledb/hypercore/remove_columnstore_policy

   4. Upload the dataset to your {SERVICE_SHORT}
      ```sql theme={"dark"}
      \COPY metrics FROM metrics.csv CSV;
      ```

2. **Have a quick look at your data**

   You query {HYPERTABLE}s in exactly the same way as you would a relational {PG} 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 [{CONSOLE}][portal-data-mode] for all your {SERVICE_LONG}s.
   * **SQL editor**: write, fix, and organize SQL faster and more accurately in [{CONSOLE}][portal-ops-mode] for a {SERVICE_LONG}.
   * **psql**: easily run queries on your {SERVICE_LONG}s or {SELF_LONG} deployment from Terminal.

   ```sql theme={"dark"}
   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;
   ```

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

   | Time                   | value |
   | ---------------------- | ----- |
   | 2023-05-29 22:00:00+00 | 23.1  |
   | 2023-05-28 22:00:00+00 | 19.5  |
   | 2023-05-30 22:00:00+00 | 25    |
   | 2023-05-31 22:00:00+00 | 8.1   |

[connection-info]: /integrations/find-connection-details

[data-ingest]: /manage-data/import-and-ingest/import-terminal

[hypercore]: /manage-data/data-management/hypercore/understand-hypercore

[hypertable-create-table]: /api-reference/timescaledb/hypertables/create_table

[hypertables-section]: /manage-data/data-management/hypertables/understand-hypertables

[metricscsvgz]: https://assets.timescale.com/docs/downloads/metrics.csv.gz

[migrate-live]: /manage-data/import-and-ingest/live-migration

[migrate-with-downtime]: /manage-data/import-and-ingest/migrate-with-downtime

[portal-data-mode]: https://console.cloud.timescale.com/dashboard/services?popsql

[portal-ops-mode]: https://console.cloud.timescale.com/dashboard/services

[secondary-indexes]: /use-timescale/hypercore/secondary-indexes/

## Write fast analytical queries

Aggregation is a way of combining data to get insights from it. Average, sum, and count are all examples of simple
aggregates. However, with large amounts of data aggregation slows things down, quickly. {CAGG_CAP}s
are a kind of {HYPERTABLE} that is refreshed automatically in the background as new data is added, or old data is
modified. Changes to your dataset are tracked, and the {HYPERTABLE} behind the {CAGG} is automatically
updated in the background.

By default, querying {CAGG}s provides you with real-time data. Pre-aggregated data from the materialized
view is combined with recent data that hasn't been aggregated yet. This gives you up-to-date results on every query.

You create {CAGG}s on uncompressed data in high-performance storage. They continue to work
on [data in the {COLUMNSTORE}][test-drive-enable-compression]
and [rarely accessed data in tiered storage][test-drive-tiered-storage]. You can even
create [{CAGG}s on top of your {CAGG}s][hierarchical-caggs].

1. **Monitor energy consumption on a day-to-day basis**

   1. Create a {CAGG} `kwh_day_by_day` for energy consumption:

      ```sql theme={"dark"}
      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;
      ```

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

      ```sql theme={"dark"}
      SELECT add_continuous_aggregate_policy('kwh_day_by_day',
         start_offset => NULL,
         end_offset => INTERVAL '1 hour',
         schedule_interval => INTERVAL '1 hour');
      ```

2. **Monitor energy consumption on an hourly basis**

   1. Create a {CAGG} `kwh_hour_by_hour` for energy consumption:

      ```sql theme={"dark"}
      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;
      ```

   2. Add a refresh policy to keep the {CAGG} up-to-date:

      ```sql theme={"dark"}
      SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
       start_offset => NULL,
          end_offset => INTERVAL '1 hour',
          schedule_interval => INTERVAL '1 hour');
      ```

3. **Analyze your data**

   Now you have made {CAGG}s, 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:

   ```sql theme={"dark"}
     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);
   ```

   You see something like:

   | day | ordinal | value              |
   | --- | ------- | ------------------ |
   | Mon | 2       | 23.08078714975423  |
   | Sun | 1       | 19.511430831944395 |
   | Tue | 3       | 25.003118897837307 |
   | Wed | 4       | 8.09300571759772   |

## Connect Grafana to Tiger Cloud

To visualize the results of your queries, enable Grafana to read the data in your {SERVICE_SHORT}:

1. **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.
2. **Add your {SERVICE_SHORT} 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][connection-info]. `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.

[cloud-login]: https://console.cloud.timescale.com/

[connection-info]: /integrations/find-connection-details

[create-service]: /deploy-and-operate/tiger-cloud/get-started/create-services

[grafana-cloud]: https://grafana.com/get/

[grafana-self-managed]: https://grafana.com/get/?tab=self-managed

## Visualize energy consumption

A Grafana dashboard represents a view into the performance of a system, and each dashboard consists of one or
more panels, which represent information about a specific metric related to that system.

To visually monitor the volume of energy consumption over time:

1. **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 {SERVICE_LONG} and the `Bar chart`
      visualization type in the top right.

      ![Grafana create dashboard][grafana-create-dashboard]

   3. In the `Queries` section, select `Code` on the right, then run the following query based on your {CAGG}:

      ```sql theme={"dark"}
      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;
      ```

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

      ![Grafana real-time analytics][grafana-real-time-analytics]

      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.

2. **Click `Save dashboard`**

You have integrated Grafana with a {SERVICE_LONG} and made insights based on visualization of your data.

[grafana-cloud]: https://grafana.com/get/

[grafana-create-dashboard]: https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-configure-dashboard.png

[grafana-docs]: https://grafana.com/docs/

[grafana-real-time-analytics]: https://assets.timescale.com/docs/images/use-case-rta-grafana-timescale-energy-cagg.png

[grafana-self-managed]: https://grafana.com/get/?tab=self-managed

[hierarchical-caggs]: /use-timescale/continuous-aggregates/hierarchical-continuous-aggregates/

[test-drive-enable-compression]: /getting-started/try-key-features-timescale-products/#enhance-query-performance-for-analytics

[test-drive-tiered-storage]: /deploy-and-operate/tiger-cloud/storage/query-tiered-data