Ingest data into a Timescale Cloud service

Timescale Cloud: Performance, Scale, Enterprise

Self-hosted products

MST

This tutorial uses a dataset that contains Bitcoin blockchain data for the past five days, in a hypertable named transactions.

Prerequisites

To follow the steps on this page:

Create a target Timescale Cloud service with time-series and analytics enabled.
You need your connection details. This procedure also works for self-hosted TimescaleDB.

Optimize time-series data using hypertables

Time-series data represents the way a system, process, or behavior changes over time. Hypertables enable TimescaleDB to work efficiently with time-series data. Hypertables are PostgreSQL tables that automatically partition your time-series data by time. Each hypertable is made up of child tables called chunks. Each chunk is assigned a range of time, and only contains data from that range. When you run a query, TimescaleDB identifies the correct chunk and runs the query on it, instead of going through the entire table.

Hypercore is the Timescale hybrid row-columnar storage engine used by hypertables. Traditional databases force a trade-off between fast inserts (row-based storage) and efficient analytics (columnar storage). Hypercore eliminates this trade-off, allowing real-time analytics without sacrificing transactional capabilities.

Hypercore dynamically stores data in the most efficient format for its lifecycle:

Row-based storage for recent data: the most recent chunk (and possibly more) is always stored in the rowstore, ensuring fast inserts, updates, and low-latency single record queries. Additionally, row-based storage is used as a writethrough for inserts and updates to columnar storage.
Columnar storage for analytical performance: chunks are automatically compressed into the columnstore, optimizing storage efficiency and accelerating analytical queries.

Unlike traditional columnar databases, hypercore allows data to be inserted or modified at any stage, making it a flexible solution for both high-ingest transactional workloads and real-time analytics—within a single database.

Because TimescaleDB is 100% PostgreSQL, you can use all the standard PostgreSQL tables, indexes, stored procedures, and other objects alongside your hypertables. This makes creating and working with hypertables similar to standard PostgreSQL.

Connect to your Timescale Cloud service
In Timescale Console open an SQL editor. The in-Console editors display the query speed. You can also connect to your service using psql.

Create a hypertable for your time-series data using CREATE TABLE. For efficient queries on data in the columnstore, remember to segmentby the column you will use most often to filter your data:


CREATE TABLE transactions (
   time TIMESTAMPTZ NOT NULL,
   block_id INT,
   hash TEXT,
   size INT,
   weight INT,
   is_coinbase BOOLEAN,
   output_total BIGINT,
   output_total_usd DOUBLE PRECISION,
   fee BIGINT,
   fee_usd DOUBLE PRECISION,
   details JSONB
) WITH (
   tsdb.hypertable,
   tsdb.partition_column='time',
   tsdb.segmentby='block_id', 
   tsdb.orderby='time DESC'
);

If you are self-hosting TimescaleDB v2.19.3 and below, create a PostgreSQL relational table, then convert it using create_hypertable. You then enable hypercore with a call to ALTER TABLE.

Create an index on the hash column to make queries for individual transactions faster:
```
CREATE INDEX hash_idx ON public.transactions USING HASH (hash);
```
Create an index on the block_id column to make block-level queries faster:
When you create a hypertable, it is partitioned on the time column. TimescaleDB automatically creates an index on the time column. However, you'll often filter your time-series data on other columns as well. You use indexes to improve query performance.
```
CREATE INDEX block_idx ON public.transactions (block_id);
```
Create a unique index on the time and hash columns to make sure you don't accidentally insert duplicate records:
```
CREATE UNIQUE INDEX time_hash_idx ON public.transactions (time, hash);
```

Load financial data

The dataset contains around 1.5 million Bitcoin transactions, the trades for five days. It includes information about each transaction, along with the value in satoshi. It also states if a trade is a coinbase transaction, and the reward a coin miner receives for mining the coin.

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

Download the bitcoin_sample.zip file. The file contains a .csv file that contains Bitcoin transactions for the past five days. Download:
bitcoin_sample.zip
In a new terminal window, run this command to unzip the .csv files:
```
unzip bitcoin_sample.zip
```
In Terminal, navigate to the folder where you unzipped the Bitcoin transactions, then connect to your service using psql.
At the psql prompt, use the COPY command to transfer data into your Timescale instance. If the .csv files aren't in your current directory, specify the file paths in these commands:
```
\COPY transactions FROM 'tutorial_bitcoin_sample.csv' CSV HEADER;
```
Because there is over a million rows of data, the COPY process could take a few minutes depending on your internet connection and local client resources.

Connect Grafana to Timescale Cloud

To visualize the results of your queries, enable Grafana to read the data in your service:

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 service as a data source
1. Open Connections > Data sources, then click Add new data source.
2. Select PostgreSQL from the list.
3. Configure the connection:
  - Host URL, Database name, Username, and Password
    Configure using your connection details. Host URL is in the format <host>:<port>.
  - TLS/SSL Mode: select require.
  - PostgreSQL options: enable TimescaleDB.
  - Leave the default setting for all other fields.
4. Click Save & test.
  Grafana checks that your details are set correctly.