Set up the database

A service in Timescale is a cloud instance which contains your database. Each service contains a single database, named tsdb. You can connect to a service 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.

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

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

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.

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)

Copy

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;

   Copy

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

SELECT * FROM rides LIMIT 5;

Copy

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

Copy

The queries in this tutorial are suitable for visualizing in Grafana. If you want to visualize the results of your queries, connect your Grafana account to the NYC taxi cab dataset.

You can use Grafana to visualize queries directly from your Timescale database.

Before you begin, make sure you have:

• Created a Timescale Cloud service.
• Installed a self-managed Grafana account, or signed up for Grafana Cloud.
• Found the connection details for the database you want to use as a data source. The details are contained in the cheatsheet you downloaded when you created the service.