This tutorial uses a dataset that contains Bitcoin blockchain data for
the past five days, in a hypertable named
A service in Timescale is a cloud instance which contains your database.
Each service contains a single database, named
You can connect to a service from your local system using the
command-line utility. If you've used PostgreSQL before, you might already have
psql installed. If not, check out the installing psql section.
In the Timescale portal, click
Download the cheatsheetto download an SQL file that contains the login details for your new service. You can also copy the details directly from this page. When you have copied your password, click
I stored my password, go to service overviewat the bottom of the page.
When your service is ready to use, is shows a green
Runninglabel in the
Service Overview. You also receive an email confirming that your service is ready to use.
On your local system, at the command prompt, connect to the service using the
Service URLfrom the SQL file that you downloaded. When you are prompted, enter the password:psql -x "<SERVICE_URL>"Password for user tsdbadmin:
If your connection is successful, you'll see a message like this, followed by the
psqlprompt: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 daily and contains data from the last five days, typically around 1.5 million Bitcoin transactions. The data includes information about each transaction, including the value of the transaction in satoshi, the smallest denomination of Bitcoin. It also states if a transaction is the first transaction in a block, known as a coinbase transaction, which includes the reward a coin miner receives for mining the coin.
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
timecolumn 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'));
by_rangedimension builder is an addition to TimescaleDB 2.13.
Create an index on the
hashcolumn to make queries for individual transactions faster:CREATE INDEX hash_idx ON public.transactions USING HASH (hash);
Create an index on the
block_idcolumn to make block-level queries faster:CREATE INDEX block_idx ON public.transactions (block_id);
Create a unique index on the
hashcolumns to make sure you don't accidentally insert duplicate records:CREATE UNIQUE INDEX time_hash_idx ON public.transactions (time, hash);
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.
This tutorial uses Bitcoin transactions from the past five days.
To ingest data into the tables that you created, you need to download the dataset and copy the data to your database.
bitcoin_sample.zipfile. The file contains a
.csvfile that contains Bitcoin transactions for the past five days. Download:
In a new terminal window, run this command to unzip the
psqlprompt, use the
COPYcommand to transfer data into your Timescale instance. If the
.csvfiles 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
COPYprocess could take a few minutes depending on your internet connection and local client resources.
Found an issue on this page?Report an issue!