Table Of Contents

1.2.1. Data in rows

[1]:
import quasardb
import datetime as dt
import numpy as np
import matplotlib.pyplot as plt
[2]:
sample_size=10
print(quasardb.version())
c = quasardb.Cluster("qdb://qdb-server:2836")
3.4.0

Create table with two columns with different types

[4]:
batch_columns = [quasardb.BatchColumnInfo("ts_batch1_1", "close", sample_size),
                 quasardb.BatchColumnInfo("ts_batch1_1", "volume", sample_size)]
batch_inserter = c.ts_batch(batch_columns)

Create data to be added to batch transaction

[5]:
#create timestamps to associate to timeseries
timestamps = np.array('2019-03-14', dtype=np.datetime64)+ np.arange(sample_size)

# Generate random doubles of timeserie
double_values = np.random.uniform(-100.0, 100.0, sample_size)
# Generate random doubles of timeserie
int_values = np.random.randint(0, 100, sample_size)

Add rows to batch transaction

[7]:
q = c.query("select * from ts_batch1_1 ")
res = q.run()
print ( "Table returned:", len( res.tables))

Table returned: 0

The data will be pushed to the server as a single transaction, guaranteeing all data is committed in one operation

[8]:
# send to the server
batch_inserter.push()

The data is now queryable

[9]:
q = c.query("select * from ts_batch1_1 ")
res = q.run()

for col in res.tables["ts_batch1_1"]:
    # col.name is a string for the name of the column
    # col.data is a numpy array of the proper type
    assert len(col.data) ==sample_size
    print(col.name, ": ", col.data)

$timestamp :  ['2019-03-14T00:00:00.000000000' '2019-03-15T00:00:00.000000000'
 '2019-03-16T00:00:00.000000000' '2019-03-17T00:00:00.000000000'
 '2019-03-18T00:00:00.000000000' '2019-03-19T00:00:00.000000000'
 '2019-03-20T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
 '2019-03-22T00:00:00.000000000' '2019-03-23T00:00:00.000000000']
close :  [-60.85205048  41.73138072  74.44200688 -32.07885668  77.48857371
 -65.33563988 -22.63410781 -67.74808919 -58.33288574  29.1826944 ]
volume :  [27 71 79 52 12 36 21 75 84 94]

You can specify ranges

[10]:
q = c.query("select * from ts_batch1_1 in range(now(), -20y)")
res = q.run()

for col in res.tables["ts_batch1_1"]:
    assert len(col.data) ==sample_size
    print(col.name, ": ", col.data)


$timestamp :  ['2019-03-14T00:00:00.000000000' '2019-03-15T00:00:00.000000000'
 '2019-03-16T00:00:00.000000000' '2019-03-17T00:00:00.000000000'
 '2019-03-18T00:00:00.000000000' '2019-03-19T00:00:00.000000000'
 '2019-03-20T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
 '2019-03-22T00:00:00.000000000' '2019-03-23T00:00:00.000000000']
close :  [-60.85205048  41.73138072  74.44200688 -32.07885668  77.48857371
 -65.33563988 -22.63410781 -67.74808919 -58.33288574  29.1826944 ]
volume :  [27 71 79 52 12 36 21 75 84 94]

Operations

[11]:
q = c.query("select min(close) from ts_batch1_1 in range(now(), -20y)")
res = q.run()

for col in res.tables["ts_batch1_1"]:
    print(col.name, ": ", col.data)
$timestamp :  ['2019-03-21T00:00:00.000000000']
min(close) :  [-67.74808919]
[12]:
q = c.query("select arithmetic_mean(close) from ts_batch1_1 in range(2019, +1y) group by day")
res = q.run()
for col in res.tables["ts_batch1_1"]:
    print(col.name, ": ", col.data)
$timestamp :  ['2019-03-14T00:00:00.000000000' '2019-03-15T00:00:00.000000000'
 '2019-03-16T00:00:00.000000000' '2019-03-17T00:00:00.000000000'
 '2019-03-18T00:00:00.000000000' '2019-03-19T00:00:00.000000000'
 '2019-03-20T00:00:00.000000000' '2019-03-21T00:00:00.000000000'
 '2019-03-22T00:00:00.000000000' '2019-03-23T00:00:00.000000000']
arithmetic_mean(close) :  [-60.85205048  41.73138072  74.44200688 -32.07885668  77.48857371
 -65.33563988 -22.63410781 -67.74808919 -58.33288574  29.1826944 ]

You can use an iterator on the table to access access data without using a query

[13]:
for row in table.reader(dict=True):
    print(row['close'])

-60.85205048134441
41.73138072415969
74.44200687653017
-32.078856675815246
77.48857370798564
-65.3356398798189
-22.634107809696744
-67.74808918583156
-58.33288573632207
29.18269439948108
[14]:
assert table.column_type_by_id('volume')== quasardb.quasardb.ColumnType.Int64
assert table.column_type_by_id('close')== quasardb.quasardb.ColumnType.Double
[15]:
#tearing down data for this sample
table = c.ts("ts_batch1_1")
table.remove()
[ ]:

arrow_backPrevious
1.2. python notebooks
Next arrow_forward
1.2.2. Using Pandas DataFrames