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:

crypto_ticks:

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

   crypto_sample.csv

   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;

If you want to ingest real-time market data, instead of sample data, check out our complementing tutorial Ingest real-time financial websocket data to ingest data directly from the Twelve Data financial API.