Ingest data into a Timescale Cloud service

This tutorial uses a dataset that contains second-by-second trade data for the most-traded crypto-assets. You optimize this time-series data in a a hypertable called assets_real_time. You also create a separate table of asset symbols in a regular PostgreSQL table named assets.

The dataset is updated on a nightly basis and contains data from the last four weeks, typically around 8 million rows of data. Trades are recorded in real-time from 180+ cryptocurrency exchanges.

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 a hypertable

Hypertables are the core of TimescaleDB, they enable Timescale Cloud to work efficiently with time-series data. Hypertables are PostgreSQL tables that automatically partition your time-series data by time. When you run a query, Timescale Cloud identifies the correct partition and runs the query on it, instead of going through the entire table.

Because TimescaleDB is 100% PostgreSQL, you can create standard PostgreSQL tables, indexes, stored procedures, and other objects alongside your Timescale 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. You can also connect to your service using psql.

Create a standard PostgreSQL table to store the real-time cryptocurrency data:


CREATE TABLE crypto_ticks (
    "time" TIMESTAMPTZ,
    symbol TEXT,
    price DOUBLE PRECISION,
    day_volume NUMERIC
);

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('crypto_ticks', by_range('time'));
```

Create a standard PostgreSQL table for relational data

When you have relational data that enhances your time-series data, store that data in standard PostgreSQL relational tables.

Add a table to store the asset symbol and name in a relational table:


CREATE TABLE crypto_assets (
    symbol TEXT UNIQUE,
    "name" TEXT
);

You now have two tables within your Timescale Cloud service. A hypertable named crypto_ticks, and a normal PostgreSQL table named crypto_assets.

Load financial data

This tutorial uses real-time cryptocurrency data, also known as tick data, from Twelve Data. To ingest data into the tables that you created, you need to download the dataset, then upload the data to your Timescale Cloud service.

Unzip
crypto_sample.zip
to a <local folder>.
This test dataset contains second-by-second trade data for the most-traded crypto-assets and a regular table of asset symbols and company names.
To import up to 100GB of data directly from your current PostgreSQL based database, migrate with downtime using native PostgreSQL tooling. To seamlessly import 100GB-10TB+ of data, use the live migration tooling supplied by Timescale. To add data from non-PostgreSQL data sources, see Import and ingest data.

In Terminal, navigate to <local folder> and connect to your service.
```
psql -d "postgres://<username>:<password>@<host>:<port>/<database-name>"
```
The connection information for a service is available in the file you downloaded when you created it.
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 crypto_ticks FROM 'tutorial_sample_tick.csv' CSV HEADER;
```
```
\COPY crypto_assets FROM 'tutorial_sample_assets.csv' 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.

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.