zseta

Output the first N numer of rows from a csv into another:

head -n 5 data.csv > header.csv

Generate schema from csv

pip install csvkit

csvsql -d "," -q '"' -i postgresql spotifyschema.csv > spotify_schema.sql

zseta

Backup process with vanilla PostgreSQL or TimescaleDB

Connect to remote instance using SSH:

ssh example.com

List running Docker containers:

docker ps

zseta

# ScyllaDB Cloud API token
variable "scylla_cloud_token" {
  description = "ScyllaDB Cloud API token"
  type        = string
  default     = "ADD-YOUR-API-TOKEN-HERE"
}

# ScyllaDB Cloud region
variable "scylla_cloud_region" {
  description = "ScyllaDB Cloud region of the cluster"

zseta

create new one-node cluster

docker run -d --name scylladb-demo -d scylladb/scylla

create new three-node cluster

docker run --name scylla-demoA -d scylladb/scylla:5.2.0 --overprovisioned 1 --smp 1
docker run --name scylla-demoB -d scylladb/scylla:5.2.0 --overprovisioned 1 --smp 1 --seeds="$(docker inspect --format='{{ .NetworkSettings.IPAddress }}' scylla-demoA)"
docker run --name scylla-demoC -d scylladb/scylla:5.2.0 --overprovisioned 1 --smp 1 --seeds="$(docker inspect --format='{{ .NetworkSettings.IPAddress }}' scylla-demoA)"

zseta

zseta

/*
If last close exists: open = last_close, else open = first(price)
*/
SELECT 
time_bucket('1 day', time) AS bucket,
symbol,
CASE 
	WHEN LAST(price_close, time) IS NOT NULL THEN LAST(price_close, time)
	ELSE FIRST(price_open, time)
END AS price_open

zseta

/* Create 15min OHLCV continuous aggregate */
CREATE MATERIALIZED VIEW demo_ohlcv_15min
WITH (timescaledb.continuous) AS 
SELECT 
time_bucket('15 min', time) AS bucket,
symbol,
FIRST(price_open, time) AS open_price,
MAX(price_high) AS highest_price,
MIN(price_low) AS lowest_price,
LAST(price_close, time) AS close_price,

zseta

zseta

CREATE MATERIALIZED VIEW demo_collections_daily
WITH (timescaledb.continuous) AS
SELECT
collection_id,
time_bucket('1 day', time) AS bucket,
COUNT(*) AS volume,
SUM(total_price) AS volume_eth,
MAX(total_price) AS max_price,
MIN(total_price) AS min_price,
AVG(total_price) AS avg_price

zseta

/* Count queries */
SELECT * FROM approximate_row_count('assets') 

SELECT * FROM approximate_row_count('collections')  

SELECT * FROM approximate_row_count('accounts') 

SELECT count(*), MIN(time) AS min_date, MAX(time) AS max_date FROM nft_sales_new