Quick Start: Ruby and TimescaleDB

    Pre-requisites

    To complete this tutorial, you will need a cursory knowledge of the Structured Query Language (SQL). The tutorial will walk you through each SQL command, but it will be helpful if you’ve seen SQL before.

    To start, install TimescaleDB. Once your installation is complete, we can proceed to ingesting or creating sample data and finishing the tutorial.

    Obviously, you will need to as well.

    Step 1: Create a new Rails application

    Let’s start by creating a new Rails application configured to use PostgreSQL as the database. TimescaleDB is a PostgreSQL extension.

    Rails will finish creating and bundling your application, installing all required Gems in the process.

    Step 2: Configure the TimescaleDB database

    Locate your TimescaleDB credentials in order to connect to your TimescaleDB instance.

    You’ll need the following credentials:

    • password
    • username
    • host URL
    • port
    • database name

    In the section of the config/database.yml section, configure your database:

    1. default: &default
    2.   adapter: postgresql
    3.   encoding: unicode
    4.   # For details on connection pooling, see Rails configuration guide
    5.   # http://guides.rubyonrails.org/configuring.html#database-pooling
    6.   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
    7.   host: [your hostname]
    8.   port: [your port]
    9.   username: [your username]
    10.   password: [your password]

    Then configure the database name in the development, test, and production sections. Let’s call our database my_app_db like so:

    1. development:
    2.   <<: *default
    3.   database: my_app_db

    Repeat the step for the test and production sections further down this file.

    Your final file should look like this (without all the automatically generated comments):

    1. default: &default
    2.   adapter: postgresql
    3.   encoding: unicode
    4.   # For details on connection pooling, see Rails configuration guide
    5.   # http://guides.rubyonrails.org/configuring.html#database-pooling
    6.   pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
    7.   host: [your hostname]
    8.   port: [your port]
    9.   username: [your username]
    10.   password: [your password]
    12. development:
    13.   <<: *default
    14.   database: my_app_db
    16.   <<: *default
    17.   database: my_app_db
    19. production:
    20.   <<: *default
    21.   database: my_app_db

    Now we can run the following rake command to create the database in TimescaleDB:

    1. rake db:create

    This will create the my_app_db database in your TimescaleDB instance and a schema.rb file that represents the state of your TimescaleDB database.

    Create a relational table

    Step 1: Add TimescaleDB to your Rails migration

    First, let’s setup our database to include the TimescaleDB extension. We will start by creating a migration:

    1. rails generate migration add_timescale
    1. class AddTimescale < ActiveRecord::Migration[5.2]
    2.   def change
    3.     enable_extension("timescaledb") unless extensions.include? "timescaledb"
    4.   end
    5. end

    Step 2: Create the database

    Now we can run the following rake command to add the TimescaleDB extension to our database:

    1. rake db:migrate

    In psql you can test that the extension has been added by running the \dx command and seeing something like the following:

    Step 3: Create a table

    Suppose we wanted to create a table to store the user agent (browser) and time whenever a visitor loads our page. You could easily extend this simple example to store a host of additional web analytics of interest to you. We can generate a Rails scaffold to represent this information in a table:

    1. rails generate scaffold PageLoads user_agent:string time:timestamp

    Rails generates all the helper files and a database migration. We can then run a rake command to create the table in our database.

    1. rake db:migrate

    If we connect to our Timescale instance using psql, we can view our database and the page_loads table that was created through our migration process using the \dt command:

    1.                  List of relations
    2.  Schema |         Name         | Type  |   Owner   
    3. --------+----------------------+-------+-----------
    4.  public | ar_internal_metadata | table | tsdbadmin
    5.  public | page_loads           | table | tsdbadmin
    6.  public | schema_migrations    | table | tsdbadmin
    7. (3 rows)

    And we can view the structure of the page_loads table using the \d page_loads command:

    1.                                         Table "public.page_loads"
    2.    Column   |            Type             | Collation | Nullable |                Default                 
    3. ------------+-----------------------------+-----------+----------+----------------------------------------
    4.  id         | bigint                      |           | not null | nextval('page_loads_id_seq'::regclass)
    5.  user_agent | character varying           |           |          | 
    6.  time       | timestamp without time zone |           |          | 
    7.  created_at | timestamp without time zone |           | not null | 
    8.  updated_at | timestamp without time zone |           | not null | 
    9. Indexes:
    10.     "page_loads_pkey" PRIMARY KEY, btree (id)

    In TimescaleDB, the primary point of interaction with your data is a , the abstraction of a single continuous table across all space and time intervals, such that one can query it via standard SQL.

    Virtually all user interactions with TimescaleDB are with hypertables. Creating tables and indexes, altering tables, inserting data, selecting data, etc. can (and should) all be executed on the hypertable.

    A hypertable is defined by a standard schema with column names and types, with at least one column specifying a time value.

    TimescaleDB requires that any UNIQUE or PRIMARY KEY indexes on your table include all partitioning columns, in our case the time. A new Rails model will include a PRIMARY KEY index for id by default, so we need to either remove the column or make sure that the index includes time as part of a “composite key”. Composite keys aren’t supported natively by Rails, but if you need to keep your id column around for some reason you can add support for them with the composite_primary_keys gem.

    In our case we won’t be making use of the id column (time-series data is generally searched by time instead) so we’re just going to drop it entirely with the remove_column line below.

    Let’s create this migration to modify the page_loads database and create a hypertable by first running the following command:

    1. rails generate migration add_hypertable

    In your db/migrate project folder, you’ll see a new migration file for [some sequence of numbers]_add_hypertable.

    1. class AddHypertable < ActiveRecord::Migration[5.2]
    2.   def change
    3.     remove_column :page_loads, :id
    4.     execute "SELECT create_hypertable('page_loads', 'time');"
    5.   end
    6. end

    When we run rake db:migrate we will generate the hypertable.

    We can confirm this in psql by running the \d page_loads command and seeing the following:

    1.                          Table "public.page_loads"
    2.    Column   |            Type             | Collation | Nullable | Default 
    3. ------------+-----------------------------+-----------+----------+---------
    4.  user_agent | character varying           |           |          | 
    5.  time       | timestamp without time zone |           | not null | 
    6.  created_at | timestamp without time zone |           | not null | 
    7.  updated_at | timestamp without time zone |           | not null | 
    8. Indexes:
    9.     "page_loads_time_idx" btree ("time" DESC)
    10. Triggers:
    11.     ts_insert_blocker BEFORE INSERT ON page_loads FOR EACH ROW EXECUTE PROCEDURE _timescaledb_internal.insert_blocker()

    Insert rows into TimescaleDB

    Let’s create a new view and controller so that we can insert a value into the database and see our results. When our view displays, we will store the user agent and time into our database.

    This will generate the view and controller files for a page called in our site. Let’s first add a line to the static_pages_controller.rb file to retrieve the user agent of the site visitor’s browser:

    1.   def home
    2.     @agent = request.user_agent
    3.   end
    4. end

    Subsequently, in the home.html.erb file, we will print the @agent variable we just created:

    1. <h1>StaticPages#home</h1>
    2. <p>Find me in app/views/static_pages/home.html.erb</p>
    3. <p>
    4.   Request: <%= @agent %>
    5. </p>

    Start your Rails server on the command line:

    1. rails s

    And, in your browser, visit http://localhost:3000/static_pages/home. You should see a printout of the user agent for your browser.

    Now that we’ve successfully obtained the user agent and passed it as a variable to the view, we can create a PageLoad object, store the user agent information and time, and save the object to our TimescaleDB database. In the static_pages_controller.rb controller file, add the following:

    1. class StaticPagesController < ApplicationController
    2.   def home
    3.     @agent = request.user_agent
    5.     page_load = PageLoad.new(:user_agent => request.user_agent, :time => Time.now)
    6.     page_load.save
    8.   end
    9. end

    Go back to your browser and refresh the page several times. You should see commit messages in your Rails console window, like so:

    1. Started GET "/static_pages/home" for ::1 at 2020-04-15 14:02:18 -0700
    2. Processing by StaticPagesController#home as HTML
    3.    (79.5ms)  BEGIN
    4.   ↳ app/controllers/static_pages_controller.rb:6
    5.   PageLoad Create (79.9ms)  INSERT INTO "page_loads" ("user_agent", "time", "created_at", "updated_at") VALUES ($1, $2, $3, $4)  [["user_agent", "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15"], ["time", "2020-04-15 21:02:18.106769"], ["created_at", "2020-04-15 21:02:18.187643"], ["updated_at", "2020-04-15 21:02:18.187643"]]
    6.   ↳ app/controllers/static_pages_controller.rb:6
    7.    (80.0ms)  COMMIT
    8.   ↳ app/controllers/static_pages_controller.rb:6
    9.   Rendering static_pages/home.html.erb within layouts/application
    10.   Rendered static_pages/home.html.erb within layouts/application (0.5ms)
    11. Completed 200 OK in 266ms (Views: 20.9ms | ActiveRecord: 239.4ms)

    You can view these entries in TimescaleDB by running the following command in psql:

    1. SELECT * FROM page_loads ORDER BY time DESC;

    The output should look like this:

    1.                                                       user_agent                                                       |            time            |         created_at         |         updated_at         
    2. -----------------------------------------------------------------------------------------------------------------------+----------------------------+----------------------------+----------------------------
    3.  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15 | 2020-04-15 21:02:18.106769 | 2020-04-15 21:02:18.187643 | 2020-04-15 21:02:18.187643
    4.  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15 | 2020-04-15 21:02:17.323409 | 2020-04-15 21:02:17.404137 | 2020-04-15 21:02:17.404137
    5.  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15 | 2020-04-15 21:02:14.743669 | 2020-04-15 21:02:14.82468  | 2020-04-15 21:02:14.82468
    6.  Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/13.1 Safari/605.1.15 | 2020-04-15 21:02:12.628455 | 2020-04-15 21:02:12.957934 | 2020-04-15 21:02:12.957934
    7. (4 rows)

    So far, we’ve created a TimescaleDB table and inserted data into it. Now, let’s retrieve data and display it.

    In our static_pages_controller.rb file let’s modify the home method and all items in the page_load database and store them in an array:

    And we can modify our home.html.erb view to iterate over the array and display each item:

    1. <h1>StaticPages#home</h1>
    2. <p>Find me in app/views/static_pages/home.html.erb</p>
    4. <table>
    5.     <% @views.each do |v| %>
    6.         <tr>
    7.             <td><%= v.user_agent %></td>
    8.             <td><%= v.time %></td>
    9.         </tr>
    10.     <% end %>

    Now, each time we refresh our page, we can see that a record is being inserted into the my_app_db TimescaleDB database, and all records are being displayed on the page.

    Next steps