Skip to content

Instantly share code, notes, and snippets.

View alrocar's full-sized avatar

alrocar alrocar

54 followers · 0 following
View GitHub Profile
@alrocar
alrocar / usain_bolt.sql
Created September 22, 2022 13:37
-- run it with: cat usain_bolt.sql | clickohuse-client -mn
drop database deleteme_alrocar;
create database deleteme_alrocar;
CREATE TABLE deleteme_alrocar.deleteme
(
`number` UInt64,
`key` LowCardinality(String),
`timestamp` DateTime
@alrocar
alrocar / emoji_regex.csv
Created September 22, 2022 09:00
We can make this file beautiful and searchable if this error is corrected: No commas found in this CSV file in line 0.
"[#*0-9]\x{FE0F}?\x{20E3}|\x{A9}\x{FE0F}?|[\x{AE}\x{203C}\x{2049}\x{2122}\x{2139}\x{2194}-\x{2199}\x{21A9}\x{21AA}]\x{FE0F}?|[\x{231A}\x{231B}]|[\x{2328}\x{23CF}]\x{FE0F}?|[\x{23E9}-\x{23EC}]|[\x{23ED}-\x{23EF}]\x{FE0F}?|\x{23F0}|[\x{23F1}\x{23F2}]\x{FE0F}?|\x{23F3}|[\x{23F8}-\x{23FA}\x{24C2}\x{25AA}\x{25AB}\x{25B6}\x{25C0}\x{25FB}\x{25FC}]\x{FE0F}?|[\x{25FD}\x{25FE}]|[\x{2600}-\x{2604}\x{260E}\x{2611}]\x{FE0F}?|[\x{2614}\x{2615}]|\x{2618}\x{FE0F}?|\x{261D}[\x{FE0F}\x{1F3FB}-\x{1F3FF}]?|[\x{2620}\x{2622}\x{2623}\x{2626}\x{262A}\x{262E}\x{262F}\x{2638}-\x{263A}\x{2640}\x{2642}]\x{FE0F}?|[\x{2648}-\x{2653}]|[\x{265F}\x{2660}\x{2663}\x{2665}\x{2666}\x{2668}\x{267B}\x{267E}]\x{FE0F}?|\x{267F}|\x{2692}\x{FE0F}?|\x{2693}|[\x{2694}-\x{2697}\x{2699}\x{269B}\x{269C}\x{26A0}]\x{FE0F}?|\x{26A1}|\x{26A7}\x{FE0F}?|[\x{26AA}\x{26AB}]|[\x{26B0}\x{26B1}]\x{FE0F}?|[\x{26BD}\x{26BE}\x{26C4}\x{26C5}]|\x{26C8}\x{FE0F}?|\x{26CE}|[\x{26CF}\x{26D1}\x{26D3}]\x{FE0F}?|\x{26D4}|\x{26E9}\x{FE0F}?|\x{26EA}|[\x{26F0}\x{26F1}]\x{FE0F}?|[\
@alrocar
alrocar / clickhouse_to_markdown.md
Last active August 15, 2022 01:00
Export from ClickHouse to Markdown table

SQL

SELECT
  number a,
  number + 1 b,
  number + 2 c
FROM numbers(100) INTO OUTFILE '/tmp/tt.md'
FORMAT Template
SETTINGS
@alrocar
alrocar / anomaly_detection.pipe
Created June 22, 2021 17:06
Z-score based anomaly detection
NODE context
DESCRIPTION >
Based on https://blog.tinybird.co/2021/06/24/anomaly-detection/
SQL >
%
SELECT
sum(qty) qty,
toStartOfInterval(utc_date, INTERVAL {{Int32(interval_duration, 10)}} second) date
FROM
@alrocar
alrocar / ingest_from_array.py
Created July 20, 2020 16:07
Ingest to Tinybird from a Python array
import csv
import requests
from io import StringIO
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry
rows = [[1, 2, 3], [4, 5, 6]]
endpoint = 'https://api.tinybird.co'
@alrocar
alrocar / kuviz.html
Last active December 26, 2019 11:25
<iframe src="https://public.carto.com/kuviz/f8dcf0ef-45e5-4578-a245-af443c870ffa" height="600" width="600" />
<iframe src="https://www.youtube.com/embed/enMumwvLAug" frameborder="0" allowfullscreen="true"> </iframe>
@alrocar
alrocar / collect-terrain-tiles.py
Created March 2, 2019 10:40
#!/usr/bin/env python
from __future__ import print_function
from math import log, tan, pi
from itertools import product
from argparse import ArgumentParser
from os.path import join, splitext
import tempfile, shutil, urllib, io, sys, subprocess
tile_url = 'https://elevation-tiles-prod.s3.amazonaws.com/geotiff/{z}/{x}/{y}.tif'
@alrocar
alrocar / create_buildings_named_map.py
Created October 31, 2018 17:08
import os
import sys
import argparse
from carto.auth import APIKeyAuthClient
from carto.maps import NamedMapManager
parser = argparse.ArgumentParser(description=(
'Example of CopySQLClient usage to stream data from NEXRAD Level 2'
@alrocar
alrocar / import_us_buildings.py
Last active February 7, 2019 16:46
import os
import sys
import argparse
try:
from urllib2 import urlopen
except ImportError:
from urllib.request import urlopen
try:
from StringIO import BytesIO
@alrocar
alrocar / animate lines script
Last active July 5, 2018 10:21
animate lines script
with lines as
(SELECT cartodb_id, color, ord, ST_Segmentize(ST_MakeLine(st_centroid(the_geom), lead(st_centroid(the_geom)) OVER (ORDER BY ord))::geography, 100000)::geometry as the_geom
FROM world_borders_four_colors_1_adjacency_list),
tosplit AS (
SELECT * FROM lines
WHERE ST_XMax(the_geom) - ST_XMin(the_geom) > 180
),
nosplit AS (
SELECT * FROM lines
WHERE ST_XMax(the_geom) - ST_XMin(the_geom) <= 180