Sign up for Timescale Cloud

Install Timescale Cloud by signing up for an account. It’s free for thirty days.

Installing Timescale Cloud

1. Sign up for a Timescale Cloud account with your name and email address. You do not need to provide payment details to get started. A confirmation email is sent to the email address you provide.
2. Verify your email by clicking on the link in the email you received. Don’t forget to check your spam folder in case the email ends up there.

3. Sign in to the Timescale Cloud portal with the password you set:

   

important

Your Timescale Cloud trial is completely free for you to use for the first thirty days. This gives you enough time to complete all our tutorials and run a few test projects of your own.

Create a service

A service in Timescale Cloud is a cloud instance which contains your database. Each service contains a single database, named tsdb.

Create a Timescale Cloud service

1. Sign in to the Timescale Cloud portal.

2. Click Create service.

3. You can choose to build your service with or without demo data. Click Create service to continue with this tutorial.

Connect to your service

When you have a service up and running, you can connect to it from your local system using the psql command-line utility. If you’ve used PostgreSQL before, you might already have psql installed. If not, check out the installing psql section.

Connecting to your service from the command prompt

1. Sign in to the Timescale Cloud portal.

2. In the Services tab, find the service you want to connect to, and check it is marked as Running.

3. Click the name of the service you want to connect to see the connection information. Take a note of the Service URL.

4. Navigate to the Operations tab, and click Reset password. You can choose your own password for the service, or allow Timescale Cloud to generate a secure password for you. Take a note of your new password.

5. On your local system, at the command prompt, connect to the service using the service URL. When you are prompted for the password, enter the password you just created:

   psql -x "postgres://[email protected]:33251/tsdb?sslmode=require"
   Password for user tsdbadmin:

   If your connection is successful, you’ll see a message like this, followed by the psql prompt:

   psql (13.3, server 12.8 (Ubuntu 12.8-1.pgdg21.04+1))
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   tsdb=>

The dataset

This tutorial uses historical data from New York’s yellow taxi network, provided by the New York City Taxi and Limousine Commission NYC TLC.

Create a hypertable

Hypertables are the core of TimescaleDB. Hypertables enable TimescaleDB to work efficiently with time-series data. Because TimescaleDB is PostgreSQL, all the standard PostgreSQL tables, indexes, stored procedures and other objects can be created alongside your TimescaleDB hypertables. This makes creating and working with TimescaleDB tables similar to standard PostgreSQL.

Creating a hypertable

1. Create a standard PostgreSQL table to store the taxi trip data using CREATE TABLE:

   CREATE TABLE "rides"(
       vendor_id TEXT,
       pickup_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
       dropoff_datetime TIMESTAMP WITHOUT TIME ZONE NOT NULL,
       passenger_count NUMERIC,
       trip_distance NUMERIC,
       pickup_longitude  NUMERIC,
       pickup_latitude   NUMERIC,
       rate_code         INTEGER,
       dropoff_longitude NUMERIC,
       dropoff_latitude  NUMERIC,
       payment_type INTEGER,
       fare_amount NUMERIC,
       extra NUMERIC,
       mta_tax NUMERIC,
       tip_amount NUMERIC,
       tolls_amount NUMERIC,
       improvement_surcharge NUMERIC,
       total_amount NUMERIC
   );

2. Convert the standard table into a hypertable partitioned on the time column using the create_hypertable() function provided by TimescaleDB. You must provide the name of the table and the column in that table that holds the timestamp data to use for partitioning:

   SELECT create_hypertable('rides', 'pickup_datetime', 'payment_type', 2, create_default_indexes=>FALSE);

3. Create an index to support efficient queries by vendor, rate code, and passenger count:

   CREATE INDEX ON rides (vendor_id, pickup_datetime DESC);
   CREATE INDEX ON rides (rate_code, pickup_datetime DESC);
   CREATE INDEX ON rides (passenger_count, pickup_datetime DESC);

Create standard PostgreSQL tables for relational data

When you have other relational data that enhances your time-series data, you can create standard PostgreSQL tables just as you would normally. For this dataset, there are two other tables of data, called payment_types and rates.

Creating standard PostgreSQL tables

1. Add a table to store the payment types data:

   CREATE TABLE IF NOT EXISTS "payment_types"(
       payment_type INTEGER,
       description TEXT
   );
   INSERT INTO payment_types(payment_type, description) VALUES
   (1, 'credit card'),
   (2, 'cash'),
   (3, 'no charge'),
   (4, 'dispute'),
   (5, 'unknown'),
   (6, 'voided trip');

2. Add a table to store the rates data:

   CREATE TABLE IF NOT EXISTS "rates"(
       rate_code   INTEGER,
       description TEXT
   );
   INSERT INTO rates(rate_code, description) VALUES
   (1, 'standard rate'),
   (2, 'JFK'),
   (3, 'Newark'),
   (4, 'Nassau or Westchester'),
   (5, 'negotiated fare'),
   (6, 'group ride');

You can confirm that the scripts were successful by running the \dt command in the psql command line. You should see this:

List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | payment_types | table | tsdbadmin
 public | rates         | table | tsdbadmin
 public | rides         | table | tsdbadmin
(3 rows)

Load trip data

When you have your database set up, you can load the taxi trip data into the rides hypertable.

Loading trip data

important

This is a large dataset, so it might take a long time, depending on your network connection.

1. Download the dataset:

   nyc_data.tar.gz

2. Use your file manager to decompress the downloaded dataset, and take a note of the path to the nyc_data_rides.csv file.

3. At the psql prompt, copy the data from the nyc_data_rides.csv file into your hypertable. Make sure you point to the correct path, if it is not in your current working directory:

   \COPY rides FROM nyc_data_rides.csv CSV;

You can check that the data has been copied successfully with this command:

SELECT * FROM rides LIMIT 5;

You should get five records that look like this:

-[ RECORD 1 ]---------+--------------------
vendor_id             | 1
pickup_datetime       | 2016-01-01 00:00:01
dropoff_datetime      | 2016-01-01 00:11:55
passenger_count       | 1
trip_distance         | 1.20
pickup_longitude      | -73.979423522949219
pickup_latitude       | 40.744613647460938
rate_code             | 1
dropoff_longitude     | -73.992034912109375
dropoff_latitude      | 40.753944396972656
payment_type          | 2
fare_amount           | 9
extra                 | 0.5
mta_tax               | 0.5
tip_amount            | 0
tolls_amount          | 0
improvement_surcharge | 0.3
total_amount          | 10.3