Start coding with TigerData

Easily integrate your app with or . Use your favorite programming language to connect to your , create and manage hypertables, then ingest and query data.

Ruby
Python
Node.js
Go
Java

Prerequisites

To follow the steps on this page:

Create a target with time-series and analytics enabled.
You need your connection details. This procedure also works for .

Install Rails.

Connect a Rails app to your service

Every is a 100% database hosted in with extensions such as . You connect to your from a standard Rails app configured for .

Optimize time-series data in hypertables

Hypertables are tables designed to simplify and accelerate data analysis. Anything you can do with regular tables, you can do with hypertables - but much faster and more conveniently.In this section, you use the helpers in the gem to create and manage a hypertable.

Insert data your service

The gem provides efficient ways to insert data into hypertables. This section shows you how to ingest test data into your hypertable.

Reference

This section lists the most common tasks you might perform with the gem.

Query scopes

The gem provides several convenient scopes for querying your time-series data.

Built-in time-based scopes:

PageLoad.last_hour.count
PageLoad.today.count
PageLoad.this_week.count
PageLoad.this_month.count

Browser-specific scopes:

# Count requests by browser
PageLoad.chrome_users.last_hour.count
PageLoad.firefox_users.last_hour.count
PageLoad.safari_users.last_hour.count

# Performance analysis
PageLoad.slow_requests.last_hour.count
PageLoad.fast_requests.last_hour.count

Query continuous aggregates: This query fetches the average and standard deviation from the performance stats for the /products path over the last day.

# Access aggregated performance stats through generated classes
PageLoad::PerformanceStatsPerMinute.last_hour
PageLoad::PerformanceStatsPerHour.last_day
PageLoad::PerformanceStatsPerDay.last_month

# Get statistics for a specific path
stats = PageLoad::PerformanceStatsPerHour.last_day.where(path: '/products').select("average(stats_agg) as average, stddev(stats_agg) as stddev").first
puts "Average: #{stats.average}"
puts "Standard Deviation: #{stats.stddev}"

features

The gem provides utility methods to access hypertable and chunk information. Every model that uses the acts_as_hypertable method has access to these methods.

Access hypertable and chunk information

View chunk or hypertable information:

PageLoad.chunks.count
PageLoad.hypertable.detailed_size

Compress/Decompress chunks:

PageLoad.chunks.uncompressed.first.compress!  # Compress the first uncompressed chunk
PageLoad.chunks.compressed.first.decompress!  # Decompress the oldest chunk
PageLoad.hypertable.compression_stats # View compression stats

Access hypertable stats

You collect hypertable stats using methods that provide insights into your hypertable’s structure, size, and compression status:

Get basic hypertable information:

hypertable = PageLoad.hypertable
hypertable.hypertable_name  # The name of your hypertable
hypertable.schema_name      # The schema where the hypertable is located

Get detailed size information:

hypertable.detailed_size # Get detailed size information for the hypertable
hypertable.compression_stats # Get compression statistics
hypertable.chunks_detailed_size # Get chunk information
hypertable.approximate_row_count # Get approximate row count
hypertable.dimensions.map(&:column_name) # Get dimension information
hypertable.continuous_aggregates.map(&:view_name) # Get continuous aggregate view names

Continuous aggregates

The continuous_aggregates method generates a class for each continuous aggregate.

Get all the continuous aggregate classes:

PageLoad.descendants # Get all continuous aggregate classes

Manually refresh a continuous aggregate:
PageLoad.refresh_aggregates
Create or drop a continuous aggregate: Create or drop all the continuous aggregates in the proper order to build them hierarchically. See more about how it works in this blog post.
PageLoad.create_continuous_aggregates PageLoad.drop_continuous_aggregates

Next steps

Now that you have integrated the ruby gem into your app:

Learn more about the gem.
Check out the official docs.
Follow the LTTB, Open AI long-term storage, and candlesticks tutorials.

Prerequisites

To follow the steps on this page:

Create a target with time-series and analytics enabled.
You need your connection details. This procedure also works for .

Install the psycopg2 library.

For more information, see the psycopg2 documentation.

Create a Python virtual environment. (optional)

Connect to TimescaleDB

In this section, you create a connection to using the psycopg2 library. This library is one of the most popular libraries for Python. It allows you to execute raw SQL queries efficiently and safely, and prevents common attacks such as SQL injection.

Create a relational table

In this section, you create a table called sensors which holds the ID, type, and location of your fictional sensors. Additionally, you create a hypertable called sensor_data which holds the measurements of those sensors. The measurements contain the time, sensor_id, temperature reading, and CPU percentage of the sensors.

Create a hypertable

When you have created the relational table, you can create a hypertable. Creating tables and indexes, altering tables, inserting data, selecting data, and most other tasks are executed on the hypertable.

Insert rows of data

You can insert data into your hypertables in several different ways. In this section, you can use psycopg2 with prepared statements, or you can use pgcopy for a faster insert.If you choose to use pgcopy instead, install the pgcopy package using pip, and then add this line to your list of import statements:

from pgcopy import CopyManager

Execute a query

This section covers how to execute queries against your database.The first procedure shows a simple SELECT * query. For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.For more information about properly using placeholders in psycopg2, see the basic module usage document. For more information about how to execute more complex queries in psycopg2, see the psycopg2 documentation.

Execute a query

For more complex queries, you can use prepared statements to ensure queries are executed safely against the database.

Execute queries using prepared statements

Prerequisites

To follow the steps on this page:

Create a target with time-series and analytics enabled.
You need your connection details. This procedure also works for .

Install Node.js.
Install the Node.js package manager npm.

Connect to

In this section, you create a connection to with a common Node.js ORM (object relational mapper) called Sequelize.

Create a relational table

In this section, you create a relational table called page_loads.

Create a hypertable

Insert rows of data

This section covers how to insert data into your hypertables.

Execute a query

This section covers how to execute queries against your database. In this example, every time the page is reloaded, all information currently in the table is displayed.

Prerequisites

To follow the steps on this page:

Create a target with time-series and analytics enabled.
You need your connection details. This procedure also works for .

Install Go.
Install the PGX driver for Go.

Connect to your

In this section, you create a connection to using the PGX driver. PGX is a toolkit designed to help Go developers work directly with . You can use it to help your Go application interact directly with TimescaleDB.Alternatively, you can connect to using a connection pool. Connection pooling is useful to conserve computing resources, and can also result in faster database queries:

Create a relational table

Generate a hypertable

Insert rows of data

You can insert rows into your database in a couple of different ways. Each of these example inserts the data from the two arrays, sensorTypes and sensorLocations, into the relational table named sensors.The first example inserts a single row of data at a time. The second example inserts multiple rows of data. The third example uses batch inserts to speed up the process.Instead of inserting a single row of data at a time, you can use this procedure to insert multiple rows of data, instead:Inserting multiple rows of data using this method executes as many insert statements as there are samples to be inserted. This can make ingestion of data slow. To speed up ingestion, you can batch insert data instead.Here’s a sample pattern for how to do so, using the sample data you generated in the previous procedure. It uses the pgx Batch object:

Execute a query

This section covers how to execute queries against your database.

Next steps

Now that you’re able to connect, read, and write to a instance from your Go application, be sure to check out these advanced tutorials:

Refer to the pgx documentation for more information about pgx.
Get up and running with with the Getting Started tutorial.
Want fast inserts on CSV data? Check out parallel copy, a tool for fast inserts, written in Go.

Prerequisites

To follow the steps on this page:

Create a target with time-series and analytics enabled.
You need your connection details. This procedure also works for .

Install the Java Development Kit (JDK).
Install the PostgreSQL JDBC driver.

All code in this quick start is for Java 16 and later. If you are working with older JDK versions, use legacy coding techniques.

Connect to your

In this section, you create a connection to your using an application in a single file. You can use any of your favorite build tools, including gradle or maven.

Create a relational table

Create a hypertable

Insert data

You can insert data into your hypertables in several different ways. In this section, you can insert single rows, or insert by batches of rows.If you want to insert a batch of rows by using a batching mechanism. In this example, you generate some sample time-series data to insert into the sensor_data hypertable:

Execute a query

This section covers how to execute queries against your database.

Next steps

Now that you’re able to connect, read, and write to a instance from your Java application, and generate the scaffolding necessary to build a new application from an existing instance, be sure to check out these advanced tutorials:

Complete code samples

This section contains complete code samples.

Complete code sample

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

Execute more complex queries

package com.timescale.java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;

public class Main {

    public static void main(String... args) {
        final var connUrl = "jdbc:postgresql://<HOSTNAME>:<PORT>/<DATABASE_NAME>?user=<USERNAME>&password=<PASSWORD>";
        try (var conn = DriverManager.getConnection(connUrl)) {
            createSchema(conn);
            insertData(conn);
            executeQueries(conn);
        } catch (SQLException ex) {
            System.err.println(ex.getMessage());
        }
    }

    private static void createSchema(final Connection conn) throws SQLException {
        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensors (
                        id SERIAL PRIMARY KEY,
                        type TEXT NOT NULL,
                        location TEXT NOT NULL
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("""
                    CREATE TABLE sensor_data (
                        time TIMESTAMPTZ NOT NULL,
                        sensor_id INTEGER REFERENCES sensors (id),
                        value DOUBLE PRECISION
                    )
                    """);
        }

        try (var stmt = conn.createStatement()) {
            stmt.execute("SELECT create_hypertable('sensor_data', by_range('time'))");
        }
    }

    private static void insertData(final Connection conn) throws SQLException {
        final List<Sensor> sensors = List.of(
                new Sensor("temperature", "bedroom"),
                new Sensor("temperature", "living room"),
                new Sensor("temperature", "outside"),
                new Sensor("humidity", "kitchen"),
                new Sensor("humidity", "outside"));
        for (final var sensor : sensors) {
            try (var stmt = conn.prepareStatement("INSERT INTO sensors (type, location) VALUES (?, ?)")) {
                stmt.setString(1, sensor.type());
                stmt.setString(2, sensor.location());
                stmt.executeUpdate();
            }
        }

        final var sensorDataCount = 100;
        final var insertBatchSize = 10;
        try (var stmt = conn.prepareStatement("""
                INSERT INTO sensor_data (time, sensor_id, value)
                VALUES (
                    generate_series(now() - INTERVAL '24 hours', now(), INTERVAL '5 minutes'),
                    floor(random() * 4 + 1)::INTEGER,
                    random()
                )
                """)) {
            for (int i = 0; i < sensorDataCount; i++) {
                stmt.addBatch();

                if ((i > 0 && i % insertBatchSize == 0) || i == sensorDataCount - 1) {
                    stmt.executeBatch();
                }
            }
        }
    }

    private static void executeQueries(final Connection conn) throws SQLException {
        try (var stmt = conn.prepareStatement("""
                SELECT time_bucket('15 minutes', time) AS bucket, avg(value)
                FROM sensor_data
                JOIN sensors ON sensors.id = sensor_data.sensor_id
                WHERE sensors.type = ? AND sensors.location = ?
                GROUP BY bucket
                ORDER BY bucket DESC
                """)) {
            stmt.setString(1, "temperature");
            stmt.setString(2, "living room");

            try (var rs = stmt.executeQuery()) {
                while (rs.next()) {
                    System.out.printf("%s: %f%n", rs.getTimestamp(1), rs.getDouble(2));
                }
            }
        }
    }

    private record Sensor(String type, String location) {
    }
}

You are not limited to these languages. is based on , you can interface with and using any client driver.

​Prerequisites

​Connect a Rails app to your service

​Optimize time-series data in hypertables

​Insert data your service

​Reference

​Query scopes

​ features

​Access hypertable and chunk information

​Access hypertable stats

​Continuous aggregates

​Next steps

​Prerequisites

​Connect to TimescaleDB

​Create a relational table

​Create a hypertable

​Insert rows of data

​Execute a query

​Execute a query

​Execute queries using prepared statements

​Prerequisites

​Connect to

​Create a relational table

​Create a hypertable

​Insert rows of data

​Execute a query

​Prerequisites

​Connect to your

​Create a relational table

​Generate a hypertable

​Insert rows of data

​Execute a query

​Next steps

​Prerequisites

​Connect to your

​Create a relational table

​Create a hypertable

​Insert data

​Execute a query

​Next steps

​Complete code samples

​Complete code sample

​Execute more complex queries

Prerequisites

Connect a Rails app to your service

Optimize time-series data in hypertables

Insert data your service

Reference

Query scopes

features

Access hypertable and chunk information

Access hypertable stats

Continuous aggregates

Next steps

Prerequisites

Connect to TimescaleDB

Create a relational table

Create a hypertable

Insert rows of data

Execute a query

Execute a query

Execute queries using prepared statements

Prerequisites

Connect to

Create a relational table

Create a hypertable

Insert rows of data

Execute a query

Prerequisites

Connect to your

Create a relational table

Generate a hypertable

Insert rows of data

Execute a query

Next steps

Prerequisites

Connect to your

Create a relational table

Create a hypertable

Insert data

Execute a query

Next steps

Complete code samples

Complete code sample

Execute more complex queries