This tutorial uses a dataset that contains second-by-second stock-trade data for the top 100 most-traded symbols, in a hypertable named stocks_real_time. It also includes a separate table of company symbols and company names, in a regular PostgreSQL table named company.

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

  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:
    Timescale Cloud Portal
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.

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

  1. Sign in to the Timescale Cloud portal.

  2. Click Create 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.

  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 "<SERVICE_URL>"
    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 is updated on a nightly basis and contains data from the last four weeks, typically around 8 million rows of data. Stock trades are recorded in real-time Monday through Friday, typically during normal trading hours of the New York Stock Exchange (9:30 AM - 4:00 PM EST).

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.

  1. Create a standard PostgreSQL table to store the real-time cryptocurrency data using CREATE TABLE:

    CREATE TABLE crypto_ticks (
    "time" TIMESTAMPTZ,
    symbol TEXT,
    price DOUBLE PRECISION,
    day_volume 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('crypto_ticks', 'time');

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 is one other table of data called crypto_assets.

  1. Add a table to store the company name and symbol for the stock trade data:

    CREATE TABLE crypto_assets (
    symbol TEXT UNIQUE,
    "name" TEXT
    );
  2. You now have two tables within your TimescaleDB database. One hypertable named crypto_ticks, and one normal PostgreSQL table named crypto_assets.

This tutorial uses real-time cryptocurrency data, also known as tick data, from Twelve Data.

To ingest data into the tables that you created, you need to download the dataset and copy the data to your database.

  1. Download the crypto_sample.zip file. The file contains two .csv files; one with company information, and one with real-time stock trades for the past month. Download:

  2. In a new terminal window, run this command to unzip the .csv files:

    unzip crypto_sample.zip
  3. At the psql prompt, use the COPY command to transfer data into your TimescaleDB instance. If the .csv files aren't in your current directory, specify the file paths in these commands:

    \COPY crypto_ticks FROM 'tutorial_sample_tick.csv' CSV HEADER;
    \COPY crypto_assets FROM 'tutorial_sample_assets.csv' CSV HEADER;

    Because there are millions of rows of data, the COPY process could take a few minutes depending on your internet connection and local client resources.

Found an issue on this page?

Report an issue!

Keywords

Related Content