Command line application¶
When you install the gem locally, a new command line application named tsdb
will be available on your command line.
The tsdb
CLI¶
It accepts a Postgresql URI and some extra flags that can help you to get more info from your TimescaleDB server:
Where the <uri>
is replaced with params from your connection like:
Or merely check the stats:
Here is a sample output from a database example with almost no data:
{:hypertables=>
{:count=>3,
:uncompressed=>2,
:chunks=>{:total=>1, :compressed=>0, :uncompressed=>1},
:size=>{:befoe_compressing=>"80 KB", :after_compressing=>"0 Bytes"}},
:continuous_aggregates=>{:count=>1},
:jobs_stats=>[{:success=>nil, :runs=>nil, :failures=>nil}]}
To start a interactive ruby/pry console use --console
:
The console will dynamically create models for all hypertables that it finds
in the database.
Let's consider the caggs.sql as the example of a database.
Then use tsdb
in the command line with the same URI and --stats
:
tsdb postgres://<user>@localhost:5432/playground --stats
{:hypertables=>
{:count=>1,
:uncompressed=>1,
:approximate_row_count=>{"ticks"=>352},
:chunks=>{:total=>1, :compressed=>0, :uncompressed=>1},
:size=>{:uncompressed=>"88 KB", :compressed=>"0 Bytes"}},
:continuous_aggregates=>{:total=>1},
:jobs_stats=>[{:success=>nil, :runs=>nil, :failures=>nil}]}
To have some interactive playground with the actual database using ruby, just
try the same command before changing from --stats
to --console
:
tsdb --console¶
We are using the same database from the previous example for this context which contains a hypertable named ticks
and a view called ohlc_1m
.
The tsdb
CLI will automatically create ActiveRecord models for hypertables and the continuous aggregates views.
Note that it's only created for this session and will never cache in the library or any other place.
In this case, the Tick
model comes from the ticks
hypertable found in the database.
It contains several methods inherited from the acts_as_hypertable
macro.
Let's start with the .hypertable
method.
Tick.hypertable
=> #<Timescaledb::Hypertable:0x00007fe99c258900
hypertable_schema: "public",
hypertable_name: "ticks",
owner: "jonatasdp",
num_dimensions: 1,
num_chunks: 1,
compression_enabled: false,
tablespaces: nil>
The core of the hypertables is the fragmentation of the data into chunks, the child tables that distribute the data. You can check all chunks directly from the hypertable relation.
Tick.hypertable.chunks
unknown OID 2206: failed to recognize type of 'primary_dimension_type'. It will cast as a String.
=> [#<Timescaledb::Chunk:0x00007fe99c31b068
hypertable_schema: "public",
hypertable_name: "ticks",
chunk_schema: "_timescaledb_internal",
chunk_name: "_hyper_33_17_chunk",
primary_dimension: "time",
primary_dimension_type: "timestamp without time zone",
range_start: 1999-12-30 00:00:00 +0000,
range_end: 2000-01-06 00:00:00 +0000,
range_start_integer: nil,
range_end_integer: nil,
is_compressed: false,
chunk_tablespace: nil>]
Chunks are created by partitioning the hypertable data into one (or potentially multiple) dimensions. All hypertables are partitions by the values belonging to a time column, which may be in timestamp, date, or various integer forms. If the time partitioning interval is one day, for example, then rows with timestamps that belong to the same day are co-located within the same chunk, while rows belonging to different days belong to different chunks. Learn more here.
Another core concept of TimescaleDB is compression. With data partitioned, it becomes very convenient to compress and decompress chunks independently.
Tick.hypertable.chunks.first.compress!
ActiveRecord::StatementInvalid: PG::FeatureNotSupported: ERROR: compression not enabled on "ticks"
DETAIL: It is not possible to compress chunks on a hypertable that does not have compression enabled.
HINT: Enable compression using ALTER TABLE with the timescaledb.compress option.
As compression is not enabled, let's do it by executing plain SQL directly from the actual context. To borrow a connection, let's use the Tick object.
And now, it's possible to compress and decompress:
Learn more about TimescaleDB compression here.The ohlc_1m
view is also available as an ActiveRecord:
Ohlc1m
=> Timescaledb::Ohlc1m(bucket: datetime, symbol: string, open: decimal, high: decimal, low: decimal, close: decimal, volume: integer)
And you can run any query as you do with regular active record queries.