Ruby quick start

This code quick start enables you to easily integrate your app with Timescale Cloud. Use your favorite programming language to connect to your Timescale Cloud service, create and manage hypertables, then ingest and query data.

Prerequisites

To follow the procedure on this page, you need to:

Create a target Timescale Cloud service
You need your connection details to follow the steps in this page. This procedure also works for self-hosted TimescaleDB.

Install Rails.

Connect a Rails app to your service

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

Create a new Rails app configured for PostgreSQL
Rails creates and bundles your app, then installs the standard PostgreSQL Gems.
```
rails new my_app -d=postgresql
cd my_app
```
Install the TimescaleDB gem
1. Open Gemfile, add the following line, then save your changes:
```
gem 'timescaledb'
```
2. In Terminal, run the following command:
```
bundle install
```

Connect your app to your Timescale Cloud service

In <my_app_home>/config/database.yml update the configuration to read securely connect to your Timescale Cloud service by adding url: <%= ENV['DATABASE_URL'] %> to the default configuration:
```
default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  url: <%= ENV['DATABASE_URL'] %>
```
Set the environment variable for DATABASE_URL to the value of Service URL from your connection details
```
export DATABASE_URL="value of Service URL"
```
Create the database:
- Timescale Cloud: nothing to do. The database is part of your Timescale Cloud service.
- self-hosted TimescaleDB, create the database for the project:
```
rails db:create
```
Run migrations:
```
rails db:migrate
```

Verify the connection from your app to your Timescale Cloud service:


echo "\dx" | rails dbconsole

The result shows the list of extensions in your Timescale Cloud service

Name	Version	Schema	Description
pg_buffercache	1.5	public	examine the shared buffer cache
pg_stat_statements	1.11	public	track planning and execution statistics of all SQL statements executed
plpgsql	1.0	pg_catalog	PL/pgSQL procedural language
postgres_fdw	1.1	public	foreign-data wrapper for remote PostgreSQL servers
timescaledb	2.18.1	public	Enables scalable inserts and complex queries for time-series data (Community Edition)
timescaledb_toolkit	1.19.0	public	Library of analytical hyperfunctions, time-series pipelining, and other SQL utilities

Optimize time-series data in hypertables

Hypertables are PostgreSQL tables designed to simplify and accelerate data analysis. Anything you can do with regular PostgreSQL tables, you can do with hypertables - but much faster and more conveniently.

In this section, you use the helpers in the timescaledb gem to create and manage a hypertable.

Generate a migration to create the page loads table
```
rails generate migration create_page_loads
```
This creates the <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb migration file.

Add hypertable options

Replace the contents of <my_app_home>/db/migrate/<migration-datetime>_create_page_loads.rb with the following:


class CreatePageLoads < ActiveRecord::Migration[8.0]
  def change
    hypertable_options = {
      time_column: 'created_at',
      chunk_time_interval: '1 day',
      compress_segmentby: 'path',
      compress_orderby: 'created_at',
      compress_after: '7 days',
      drop_after: '30 days'
    }

    create_table :page_loads, id: false, primary_key: [:created_at, :user_agent, :path], hypertable: hypertable_options do |t|
      t.timestamptz :created_at, null: false
      t.string :user_agent
      t.string :path
      t.float :performance
    end
  end
end

The id column is not included in the table. This is because TimescaleDB requires that any UNIQUE or PRIMARY KEY indexes on the table include all partitioning columns. In this case, this is the time column. A new Rails model includes a PRIMARY KEY index for id by default: either remove the column or make sure that the index includes time as part of a "composite key."

For more information, check the Roby docs around composite primary keys.

Create a PageLoad model

Create a new file called <my_app_home>/app/models/page_load.rb and add the following code:


class PageLoad < ApplicationRecord
  extend Timescaledb::ActsAsHypertable
  include Timescaledb::ContinuousAggregatesHelper

  acts_as_hypertable time_column: "created_at",
    segment_by: "path",
    value_column: "performance"

  # Basic scopes for filtering by browser
  scope :chrome_users, -> { where("user_agent LIKE ?", "%Chrome%") }
  scope :firefox_users, -> { where("user_agent LIKE ?", "%Firefox%") }
  scope :safari_users, -> { where("user_agent LIKE ?", "%Safari%") }

  # Performance analysis scopes
  scope :performance_stats, -> { 
    select("stats_agg(#{value_column}) as stats_agg")
  }

  scope :slow_requests, -> { where("performance > ?", 1.0) }
  scope :fast_requests, -> { where("performance < ?", 0.1) }

  # Set up continuous aggregates for different timeframes
  continuous_aggregates scopes: [:performance_stats],
    timeframes: [:minute, :hour, :day],
    refresh_policy: {
      minute: {
        start_offset: '3 minute',
        end_offset: '1 minute',
        schedule_interval: '1 minute'
      },
      hour: {
        start_offset: '3 hours',
        end_offset: '1 hour',
        schedule_interval: '1 minute'
      },
      day: {
        start_offset: '3 day',
        end_offset: '1 day',
        schedule_interval: '1 minute'
      }
    }
end

Run the migration
```
rails db:migrate
```

Insert data your service

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

Create a controller to handle page loads

Create a new file called <my_app_home>/app/controllers/application_controller.rb and add the following code:


class ApplicationController < ActionController::Base
  around_action :track_page_load

  private

  def track_page_load
    start_time = Time.current
    yield
    end_time = Time.current
    
    PageLoad.create(
      path: request.path,
      user_agent: request.user_agent,
      performance: (end_time - start_time)
    )
  end
end

Generate some test data

Use bin/console to join a Rails console session and run the following code to define some random page load access data:


def generate_sample_page_loads(total: 1000)
  time = 1.month.ago
  paths = %w[/ /about /contact /products /blog]
  browsers = [
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.114 Safari/537.36",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:89.0) Gecko/20100101 Firefox/89.0",
    "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/14.1.1 Safari/605.1.15"
  ]

  total.times.map do
    time = time + rand(60).seconds
    {
      path: paths.sample,
      user_agent: browsers.sample,
      performance: rand(0.1..2.0),
      created_at: time,
      updated_at: time
    }
  end
end

Insert the generated data into your Timescale Cloud service


# Insert the data in batches
PageLoad.insert_all(generate_sample_page_loads, returning: false)

Validate the test data in your Timescale Cloud service
```
PageLoad.count
PageLoad.first
```

Reference

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

Query scopes

The timescaledb 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 timescaledb 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 timescaledb gem.
Check out the official docs.
Follow the LTTB, Open AI long-term storage, and candlesticks tutorials.

Related Content