Set up the database

This tutorial uses a dataset that contains second-by-second stock-trade data for the top 100 most-traded symbols, in a hypertable named stocks_real_time. It also includes a separate table of company symbols and company names, in a regular PostgreSQL table named company.

A service in Timescale is a cloud instance which contains your database. Each service contains a single database, named tsdb. You can connect to a service from your local system using the psql command-line utility. If you've used PostgreSQL before, you might already have psql installed. If not, check out the installing psql section.

Create a Timescale service and connect to the service

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

The dataset is updated on a nightly basis and contains data from the last four weeks, typically around 8 million rows of data. Stock trades are recorded in real-time Monday through Friday, typically during normal trading hours of the New York Stock Exchange (9:30 AM - 4:00 PM EST).

Create a hypertable

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.

Creating a hypertable

Create a standard PostgreSQL table to store the real-time stock trade data using CREATE TABLE:


CREATE TABLE stocks_real_time (
  time TIMESTAMPTZ NOT NULL,
  symbol TEXT NOT NULL,
  price DOUBLE PRECISION NULL,
  day_volume INT NULL
);

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('stocks_real_time', by_range('time'));
```
Create an index to support efficient queries on the symbol and time columns:
```
CREATE INDEX ix_symbol_time ON stocks_real_time (symbol, time DESC);
```

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.

Because you often query the stock trade data by the company symbol, you should add an index for it. Include the time column because time-series data typically looks for data in a specific period of time.

Create standard PostgreSQL tables for relational data

When you have other relational data that enhances your time-series data, you can create standard PostgreSQL tables just as you would normally. For this dataset, there is one other table of data called company.

Creating standard PostgreSQL tables

Add a table to store the company name and symbol for the stock trade data:


CREATE TABLE company (
  symbol TEXT NOT NULL,
  name TEXT NOT NULL
);

You now have two tables within your Timescale database. One hypertable named stocks_real_time, and one normal PostgreSQL table named company.

Download the real_time_stock_data.zip file. The file contains two .csv files; one with company information, and one with real-time stock trades for the past month. Download:
real_time_stock_data.zip
In a new terminal window, run this command to unzip the .csv files:
```
unzip real_time_stock_data.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 stocks_real_time from './tutorial_sample_tick.csv' DELIMITER ',' CSV HEADER;
```
```
\COPY company from './tutorial_sample_company.csv' DELIMITER ',' CSV HEADER;
```
Because there are millions of rows of data, the COPY process could take a few minutes depending on your internet connection and local client resources.

Set up the database

Create a Timescale service and connect to your service

Create a Timescale service and connect to the service

The dataset

Create a hypertable

Creating a hypertable

Note

Create standard PostgreSQL tables for relational data

Creating standard PostgreSQL tables

Load financial data

Ingest the dataset

Ingesting the dataset

Connect to Grafana