Set up the database

In the Timescale portal, click Create service.

Click Download the cheatsheet to 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 overview at the bottom of the page.

When your service is ready to use, is shows a green Running label 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 URL from the SQL file that you downloaded. When you are prompted, enter the password:

psql -x "<SERVICE_URL>"
Password for user tsdbadmin:

Copy

If your connection is successful, you'll see a message like this, followed by the psql prompt:

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=>

Copy

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

Copy

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'));

Copy

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

Copy

Create an index on the block_id column to make block-level queries faster:

CREATE INDEX block_idx ON public.transactions (block_id);

Copy

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

Copy

Download the bitcoin_sample.zip file. The file contains a .csv file that contains Bitcoin transactions for the past five days. Download:

In a new terminal window, run this command to unzip the .csv files:

unzip bitcoin_sample.zip

Copy

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;

Copy

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.