Start coding with Tiger Data

Integrate Tiger Cloud with your app using your preferred programming language. Connect to a service, create and manage hypertables, then and ingest and query data

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

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}"
  

TimescaleDB 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.

Python

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

Node.js

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 TimescaleDB

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

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

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.

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 Go.
• Install the PGX driver for Go.

Connect to your Tiger service

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

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.

Generate 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 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.

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 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 Tiger service

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

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 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:

• Continuous Aggregates
• Migrate Your own Data

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.