Ingest data into a Timescale Cloud service

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 in hypertables

Hypertables are the core of Timescale. Hypertables enable Timescale to work efficiently with time-series data. Because Timescale is PostgreSQL, all the standard PostgreSQL tables, indexes, stored procedures and other objects can be created alongside your Timescale hypertables. This makes creating and working with Timescale tables similar to standard PostgreSQL.

Create a standard PostgreSQL table to store the Bitcoin blockchain data using CREATE TABLE:


CREATE TABLE transactions (
   time TIMESTAMPTZ,
   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
);

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('transactions', by_range('time'));
```
Note
The by_range dimension builder is an addition to TimescaleDB 2.13.
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:
```
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);
```

Note

When you create a hypertable, it is automatically partitioned on the time column you provide as the second parameter to create_hypertable(). Also, Timescale automatically creates an index on the time column. However, you'll often filter your time-series data on other columns as well. Using indexes appropriately helps your queries perform better.

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
```
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.