Skip to content

Instantly share code, notes, and snippets.

View zseta's full-sized avatar

Attila Tóth zseta

View GitHub Profile
@zseta
zseta / linux_csv.md
Created April 21, 2024 15:53
Working with large CSV files in Linux to generate database schema

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
zseta / backup_docker_ssh.md
Created January 2, 2024 20:07
Backup Docker PostgreSQL database using SSH connection

Backup process with vanilla PostgreSQL or TimescaleDB

Connect to remote instance using SSH:

ssh example.com

List running Docker containers:

docker ps
# 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
zseta / scylladb-docker.md
Last active April 21, 2024 12:58
ScyllaDB docker cheat sheet

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 / main.ipynb
Created September 20, 2023 01:24
Python meetup demo
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
/*
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
/* 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,
Loading
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
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
/* 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