Home

quackpipe

Fun Queries

Parquet reader

SELECT
    town,
    district,
    count() AS c 
FROM read_parquet('https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet')
WHERE read_parquet.town == 'LONDON'
GROUP BY
    town,
    district
ORDER BY c DESC
LIMIT 10

CSV loose parsing for Influx/FLUX

SELECT strptime(_time,'%Y-%m-%dT%H:%M:%S%Z') as "Date", location, CAST(_value as FLOAT) as _value 
FROM read_csv_auto('https://influx-testdata.s3.amazonaws.com/noaa.csv', header=True, skip=3, ignore_errors=True)
WHERE _measurement == 'average_temperature'
LIMIT 100

CSV Window Function w/ remote data

SELECT strptime(_time,'%Y-%m-%dT%H:%M:%S%Z') as "Date", location, CAST(_value as FLOAT) as _value, 
    AVG(CAST(_value AS FLOAT)) OVER (
        PARTITION BY "location"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 6 HOURS PRECEDING
                  AND INTERVAL 6 HOURS FOLLOWING)
        AS "Moving Average"
FROM read_csv_auto('https://influx-testdata.s3.amazonaws.com/noaa.csv', header=True, skip=3, ignore_errors=True)
WHERE _measurement == 'average_temperature'
ORDER BY 1, 2
LIMIT 10