Set up the database

In the Timescale portal, click Create service.

Click Download the cheatsheet to download an SQL file that contains the login details for your new service. You can also copy the details directly from this page. When you have copied your password, click I stored my password, go to service overview at the bottom of the page.

When your service is ready to use, is shows a green Running label in the Service Overview. You also receive an email confirming that your service is ready to use.

On your local system, at the command prompt, connect to the service using the Service URL from the SQL file that you downloaded. When you are prompted, enter the password:

psql -x "<SERVICE_URL>"
Password for user tsdbadmin:

Copy

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

Copy

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
);

Copy

Convert the standard table into a hypertable partitioned on the time column using the create_hypertable() function provided by Timescale. 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', by_range('pickup_datetime'), create_default_indexes=>FALSE);
SELECT add_dimension('rides', by_hash('payment_type', 2));

Copy

Note

The by_range and by_hash dimension builder is an addition to TimescaleDB 2.13.

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);

Copy

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');

Copy

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');

Copy

Download the dataset:

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

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

Log in to Grafana

In your browser, log in to either :

• Self-hosted Grafana: at http://localhost:3000/. The default credentials are admin, admin.
• Grafana Cloud: use the URL and credentials you set when you created your account.

Add your Timescale Cloud service as a data source

1. In the Grafana dashboard, navigate to Configuration > Data sources, then click Add data source.

2. In Add data source, select PostgreSQL.

3. Configure the data source using the connection in $TARGET:

   • Name: the name to use for the dataset

   • Host: the host and port for your service, in this format: <HOST>:<PORT>.

     For example: example.tsdb.cloud.timescale.com:35177.

   • Database: tsdb

   • User: tsdbadmin, or another privileged user

   • Password: the password for User

   • TLS/SSL Mode: select require

   • PostgreSQL details: enable TimescaleDB

   • Leave the default setting for all other fields

4. Click Save & test.

Grafana checks that your details are set correctly.