Design schema and ingest tick data

This tutorial shows you how to store real-time cryptocurrency or stock tick data in TimescaleDB. The initial schema provides the foundation to store tick data only. Once you begin to store individual transactions, you can calculate the candlestick values using TimescaleDB continuous aggregates based on the raw tick data. This means that our initial schema doesn't need to specifically store candlestick data.

Schema

This schema uses two tables:

  • crypto_assets: a relational table that stores the symbols to monitor. You can also include additional information about each symbol, such as social links.
  • crypto_ticks: a time-series table that stores the real-time tick data.

crypto_assets:

FieldDescription
symbolThe symbol of the crypto currency pair, such as BTC/USD
nameThe name of the pair, such as Bitcoin USD

crypto_ticks:

FieldDescription
timeTimestamp, in UTC time zone
symbolCrypto pair symbol from the crypto_assets table
priceThe price registered on the exchange at that time
day_volumeTotal volume for the given day (incremental)

Create the tables:

CREATE TABLE crypto_assets (
    symbol TEXT UNIQUE,
    "name" TEXT
);
 
CREATE TABLE crypto_ticks (
    "time" TIMESTAMPTZ,
    symbol TEXT,
    price DOUBLE PRECISION,
    day_volume NUMERIC
);

You also need to turn the time-series table into a hypertable:

-- convert the regular 'crypto_ticks' table into a TimescaleDB hypertable with 7-day chunks
SELECT create_hypertable('crypto_ticks', 'time');

This is an important step in order to efficiently store your time-series data in TimescaleDB.

Using TIMESTAMP data types

It is best practice to store time values using the TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ) data type. This makes it easier to query your data using different time zones. TimescaleDB stores TIMESTAMPTZ values in UTC internally and makes the necessary conversions for your queries.

Insert tick data

With the hypertable and relational table created, download the sample files containing crypto assets and tick data from the last three weeks. Insert the data into your TimescaleDB instance.

Inserting sample data

  1. Download the sample .csv files (provided by Twelve Data):

    wget https://assets.timescale.com/docs/downloads/candlestick/crypto_sample.zip
  2. Unzip the file and change the directory if you need to:

    unzip crypto_sample.zip
    cd crypto_sample
  3. At the psql prompt, insert the content of the .csv files into the database.

    psql -x "postgres://tsdbadmin:{YOUR_PASSWORD_HERE}@{YOUR_HOSTNAME_HERE}:{YOUR_PORT_HERE}/tsdb?sslmode=require"
    
    \COPY crypto_assets FROM 'crypto_assets.csv' CSV HEADER;
    \COPY crypto_ticks FROM 'crypto_ticks.csv' CSV HEADER;

Found an issue on this page?

Report an issue!

Keywords

Related Content