• Stars
    star
    276
  • Rank 149,319 (Top 3 %)
  • Language SQL
  • License
    Apache License 2.0
  • Created over 1 year ago
  • Updated 10 months ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

NoiSQL โ€” Generating Music With SQL Queries

NoiSQL โ€” Generating Music With SQL Queries

NoiSQL (named after Robert Noyce) shows how to play sound and music with declarative SQL queries.

It contains oscillators for basic waves, envelopes, sequencers, arpeggiators, effects (distortion, delay), noise generators, AM and FM, and LFO, ... Sometimes it can generate something nice, but usually not.

Table of Contents

Quick Start - Linux

Clone the Repository.

Download clickhouse-local:

curl https://clickhouse.com/ | sh

Install (Linux):

sudo ./clickhouse install

Check the installation:

clickhouse-local --version

Quick Start - MacOS

Before beginning, please ensure that you have homebrew installed.

Clone the Repository.

Change to the Repository folder (i.e. cd NoiSQL)

Install ClickHouse (macOS):

mkdir -p bin
curl https://clickhouse.com/ | sh
mv clickhouse bin/clickhouse-local
export PATH="$(pwd)/bin:$PATH"

NOTE: If you want this to live past a terminal restart add to your profile. That may look something like the below or .bash_profile or .zshrc depending on your terminal of choice.

echo 'export PATH="'$(pwd)'/bin:$PATH"' >> .profile

In order to playback audio from the terminal (via STDIN) we use an open source project (with a convenient brew recipe) called 'sox' (note that Xcode developer tools is a dependency, and if you don't have it installed, you will need to install it first via xcode-select --install)

brew install sox

Bleeps and Bloops - a demonstration

Now, ClickHouse Local is setup and it is time to make our first noises.

Demo (Linux):

./music2.sql.sh | aplay -f cd

Demo (macOS):

./music2.sql.sh | play -t raw -b 16 -e signed -c 2 -r 44100 -v .75 -

Live editing (Linux):

sudo apt install inotifytools
./play.sh music0.sql

You can edit the music0.sql file, and the changes will automatically apply while playing. On macOS this is not possible due to the lack of inotifytools BUT the play script can be used to play any of the samples music*.sql files provided

Examples

If, you are unable to use it yourself. You can still hear the output as .mp4 here.

music3.mp4
music2.mp4
music1.mp4
music0.mp4

How It Works

An SQL query selects from the system.numbers table, containing a sequence of natural numbers, and transforms this sequence into a PCM audio signal in the CD format. This output signal is piped to the aplay -f cd tool (on Linux) to play it.

The CD (Compact Disc Audio) format is a 44100 Hz 16-bit stereo PCM signal.

  • 44100 Hz is the "sampling frequency", meaning that the signal has a value 44 100 times every second;
  • 16 bit is the precision of every value, and the value is represented by Int16 (signed, two's complement, little endian) integer;
  • stereo means that we have two values at every sample - for the left channel and for the right channel;

The signal is represented in binary, corresponding to the ClickHouse's RowBinary format. Therefore, the bit rate of the signal is 16 * 2 * 44100 = 1411 kBit.

Although we could also use the classic 8-bit mono 9 kHz format, the CD format gives more possibilities.

To get the idea, run this:

clickhouse-local --query "
    SELECT 
        (number % 44100)::Int16           AS left, 
        ((number + 22050) % 44100)::Int16 AS right 
    FROM system.numbers
    FORMAT RowBinary" | aplay -f cd

It will give you an uninteresting clicky sound.

clickhouse-local --query "
    WITH number * 2 * pi() / 44100 AS time 
    SELECT
        (sin(time * 200) * 0x7FFF)::Int16 AS left,
        (sin(time * 400) * 0x7FFF)::Int16 AS right
    FROM system.numbers
    FORMAT RowBinary" | aplay -f cd

It will give you uninteresting waves.

Making Something Interesting

Here is a query from music0.sql, that generates something at least listenable. Let's walk through this SQL query.

The WITH clause in the SQL query allows defining expressions for further use. We can define both constants and functions (in form of lambda expressions). We use the allow_experimental_analyzer setting to make this query possible.

Input

Let's define the time column to be a floating point value representing the number of seconds.

WITH

44100 AS sample_frequency
, number AS tick
, tick / sample_frequency AS time

Output Control

Let us work with the signal in the floating point format, with values in the [-1, 1] range. Here are the functions to convert it to the output PCM CD signal.

A knob for the volume:

, 1 AS master_volume

If the signal exceeds the boundaries, it will be clipped:

, level -> least(1.0, greatest(-1.0, level)) AS clamp

Floating point values are converted to Int16 for the output:

, level -> (clamp(level) * 0x7FFF * master_volume)::Int16 AS output

If we don't care about stereo - we can simply output a tuple (pair) of identical values:

, x -> (x, x) AS mono

Basic waves

We define oscillators as functions of time, with the period adjusted to be one second. You can modify the frequency simply by multiplying the time argument. For example, sine_wave(time * 400) - a sine wave of 400 Hz frequency.

The sine wave gives the cleanest and most boring sound.

, time -> sin(time * 2 * pi()) AS sine_wave

The square wave gives a very harsh sound; it can be imagined as a maximally-distorted sine wave.

, time -> time::UInt64 % 2 * 2 - 1 AS square_wave

Sawtooth wave

, time -> (time - floor(time)) * 2 - 1 AS sawtooth_wave

Triangle wave

, time -> abs(sawtooth_wave(time)) * 2 - 1 AS triangle_wave

Helpers and LFO

LFO means "Low-Frequency Oscillation," and it is used to control a parameter of one signal with another low-frequency signal. It can have multiple interesting effects.

For example, AM - amplitude modulation is modulating the volume of one signal with another signal, and it will give a trembling effect, making your sine waves sound more natural.

Another example, FM - frequency modulation, is making the frequency of one signal change with another frequency. See the explanation.

We take the wave and map it to the [from, to] interval:

, (from, to, wave, time) -> from + ((wave(time) + 1) / 2) * (to - from) AS lfo

Here is a discrete version of an LFO. It allows changing the signal as a step function:

, (from, to, steps, time) -> from + floor((time - floor(time)) * steps) / steps * (to - from) AS step_lfo

For some unknown reason, the frequencies of musical notes ("musical scale") are exponentially distributed, so sometimes we have to apply computations in the logarithmic coordinates to make a more pleasant sound:

, (from, to, steps, time) -> exp(step_lfo(log(from), log(to), steps, time)) AS exp_step_lfo

Noise

Generating noise is easy. We just need random numbers.

But we want the noise to be deterministic (determined by the time) for further processing. That's why we use cityHash64 instead of a random and erf instead of randNormal.

All the following are variants of white noise. Although we can also generate brown noise with the help of runningAccumulate.

, time -> cityHash64(time) / 0xFFFFFFFFFFFFFFFF AS uniform_noise
, time -> erf(uniform_noise(time)) AS white_noise
, time -> cityHash64(time) % 2 ? 1 : -1 AS bernoulli_noise

Distortion

Distortion alters the signal in various ways to make it sound less boring.

The harshest distortion - clipping - amplifies the signal, then clips what's above the range. It adds higher harmonics and makes sound more metallic, and makes sine waves more square.

, (x, amount) -> clamp(x * amount) AS clipping

For a milder version, it makes sense to apply the pow function, such as square root to the [-1, 1] signal:

, (x, amount) -> clamp(x > 0 ? pow(x, amount) : -pow(-x, amount)) AS power_distortion

We can reduce the number of bits in the values of the signal, making it more coarse. It adds some sort of noise, making it sound worn out.

, (x, amount) -> round(x * exp2(amount)) / exp2(amount) AS bitcrush

Lowering the sample rate makes the signal dirty. Try to apply it for white noise.

, (time, sample_frequency) -> round(time * sample_frequency) / sample_frequency AS desample

Something like compressing the periodic components in time and adding empty space - no idea why I need it:

, (time, wave, amount) -> (time - floor(time) < (1 - amount)) ? wave(time * (1 - amount)) : 0 AS thin

Skewing the waves in time making sine ways more similar to sawtooth waves:

, (time, wave, amount) -> wave(floor(time) + pow(time - floor(time), amount)) AS skew

Envelopes

The envelope is a way to make the signal sound like a note of a keyboard musical instrument, such as a piano.

It modulates the volume of the signal by:

  • attack - time for the sound to appear;
  • hold - time for the sound to play at maximum volume;
  • release - time for the sound to decay to zero;

This is a simplification of what typical envelopes are, but it's good enough.

, (time, offset, attack, hold, release) ->
       time < offset ? 0
    : (time < offset + attack                  ? ((time - offset) / attack)
    : (time < offset + attack + hold           ? 1
    : (time < offset + attack + hold + release ? (offset + attack + hold + release - time) / release
    : 0))) AS envelope

We can make the musical note sound periodically to define a rhythm. For convenience, we define "bpm" as "beats per minute" and make it sound once in every beat.

, (bpm, time, offset, attack, hold, release) ->
    envelope(
        time * (bpm / 60) - floor(time * (bpm / 60)),
        offset,
        attack,
        hold,
        release) AS running_envelope

Sequencers

To create a melody, we need a sequence of notes. A sequencer generates it.

In the first example, we simply take it from an array and make it repeat indefinitely:

, (sequence, time) -> sequence[1 + time::UInt64 % length(sequence)] AS sequencer

But the obvious way to generate a melody is to take a Sierpinski triangle.

Sierpinski triangles sound delicious:

, time -> bitAnd(time::UInt8, time::UInt8 * 8) AS sierpinski

Another way is to map the number of bits in a number to a musical note:

, time -> bitCount(time::UInt64) AS bit_count

Calculating the number of trailing zero bits gives us a nice arpeggiator:

, time -> log2(time::UInt64 > 0 ? bitXor(time::UInt64, time::UInt64 - 1) : 1) AS trailing_zero_bits

Delay

If you ever wanted to generate dub music, you cannot go without a delay effect:

, (time, wave, delay, decay, count) -> arraySum(n -> wave(time - delay * n) * pow(decay, n), range(count)) AS delay

Components

Here is a kick:

sine_wave(time * 50) * running_envelope(120, time, 0, 0.01, 0.01, 0.025) AS kick,

Here is a snare:

white_noise(time) * running_envelope(120, time, 0.5, 0.01, 0.01, 0.05) AS snare,

Let's also define five melodies.

What is the idea? Let's take a Sierpinski triangle, put it into a sine wave, add FM to make it even fancier, and apply a few LFOs over the place.

sine_wave(
    time * (100 * exp2(trailing_zero_bits(time * 8) % 12 / 6))
  + sine_wave(time * 3 + 1/4)) * 0.25 * lfo(0.5, 1, sine_wave, time * 11)
    * running_envelope(480, time, 0, 0.01, 0.1, 0.5)
    * lfo(0, 1, sine_wave, time / 12
    ) AS melody1,
  
sine_wave(time * (200 * exp2(bit_count(time * 8) % 12 / 6))
  + sine_wave(time * 3 + 1/4)) * 0.25 * lfo(0.5, 1, sine_wave, time * 11)
    * running_envelope(480, time, 0, 0.11, 0.1, 0.5) * lfo(0, 1, sine_wave, time / 24
    ) AS melody2,
    
sine_wave(time * (400 * exp2(sierpinski(time * 8) % 12 / 6))
    + sine_wave(time * 3 + 1/4)) * 0.25 * lfo(0.5, 1, sine_wave, time * 11)
    * running_envelope(480, time, 0, 0.21, 0.1, 0.5) * lfo(0, 1, sine_wave, time / 32
    ) AS melody3,
    
sine_wave(time * (800 / exp2(trailing_zero_bits(time * 8) % 12 / 6))
    + sine_wave(time * 3 + 1/4)) * 0.25 * lfo(0.5, 1, sine_wave, time * 11)
    * running_envelope(480, time, 0, 0.31, 0.1, 0.5) * lfo(0, 1, sine_wave, time / 16
    ) AS melody4

Combine It

So, what will happen if we mix together some garbage and listen to it?

SELECT

mono(output(
      1.0   * melody1
    + 0.5   * melody2
    + 0.25  * melody3
    + 1.0   * melody4
    + 1     * kick
    + 0.025 * snare))

FROM table;

Additional Commands

Generate five minutes of audio and write to a .pcm file:

clickhouse-local --format RowBinary --query "SELECT * FROM system.numbers LIMIT 44100 * 5 * 60" \
 | clickhouse-local --allow_experimental_analyzer 1 --format RowBinary --structure "number UInt64" --queries-file music0.sql \
> music0.pcm

Convert pcm to wav:

ffmpeg -f s16le -ar 44.1k -ac 2 -i music0.pcm music0.wav

Convert pcm to mp4:

ffmpeg -f s16le -ar 44.1k -ac 2 -i music0.pcm music0.mp4

Limitations

I haven't, yet, found a good way to implement filters (low-pass, high-pass, band-pass, etc.). It does not have Fourier transform, and we cannot operate on the frequency domain. However, the moving average can suffice as a simple filter.

Further Directions

You can use ClickHouse as a sampler - storing the prepared musical samples in the table and arranging them with SELECT queries. For example, the Mod Archive can help.

You can use ClickHouse as a vocoder. Just provide the microphone input signal instead of the system.numbers as a table to clickhouse-local.

You can make the queries parameterized, replacing all the hundreds of constants with parameters. Then attach a device with hundreds of knobs and faders to your PC and provide their values of them as a streaming input table. Then you can control your sound like a pro.

Real-time video generation can be added as well.

Motivation

This is a fun project and neither a good nor convenient solution to a problem. Better solutions exist.

There is not much sense in this project, although it can facilitate testing ClickHouse.

You could argue that modern AI, for example, Riffusion, can do a better job. The counterargument is - if you enjoy what you are doing, it's better not to care if someone does it better but with less pleasure.

Contributing

If you want to share new interesting examples, please make a pull request, adding them directly to this repository!

Reading Corner

More Repositories

1

ClickHouse

ClickHouseยฎ is a real-time analytics DBMS
C++
37,339
star
2

clickhouse-go

Golang driver for ClickHouse
Go
2,853
star
3

clickhouse-java

Java client and JDBC driver for ClickHouse
Java
1,324
star
4

clickhouse-presentations

Presentations, meetups and talks about ClickHouse
HTML
981
star
5

ClickBench

ClickBench: a Benchmark For Analytical Databases
HTML
642
star
6

metabase-clickhouse-driver

ClickHouse database driver for the Metabase business intelligence front-end
Clojure
439
star
7

clickhouse_exporter

This is a simple server that periodically scrapes ClickHouse stats and exports them via HTTP for Prometheus(https://prometheus.io/) consumption.
Go
361
star
8

ch-go

Low-level Go Client for ClickHouse
Go
314
star
9

clickhouse-connect

Python driver/sqlalchemy/superset connectors
Python
308
star
10

clickhouse-cpp

C++ client library for ClickHouse
C
300
star
11

clickhouse-rs

Official pure Rust typed client for ClickHouse DB
Rust
270
star
12

graphouse

Graphouse allows you to use ClickHouse as a Graphite storage.
Java
259
star
13

clickhouse-odbc

ODBC driver for ClickHouse
C
246
star
14

dbt-clickhouse

The Clickhouse plugin for dbt (data build tool)
Python
245
star
15

adsb.exposed

Interactive visualization and analytics on ADS-B data with ClickHouse
HTML
223
star
16

clickhouse-js

Official JS client for ClickHouse DB
TypeScript
212
star
17

spark-clickhouse-connector

Spark ClickHouse Connector build on DataSourceV2 API
Scala
180
star
18

clickhouse-jdbc-bridge

A JDBC proxy from ClickHouse to external databases
Java
166
star
19

clickhouse-kafka-connect

ClickHouse Kafka Connector
Java
145
star
20

github-explorer

Everything You Always Wanted To Know About GitHub (But Were Afraid To Ask)
HTML
141
star
21

examples

ClickHouse Examples
Jupyter Notebook
118
star
22

clickhouse-docs

Official documentation for ClickHouse
JavaScript
110
star
23

click-ui

The home of the ClickHouse design system and component library.
TypeScript
73
star
24

pastila

Paste toy-service on top of ClickHouse
HTML
62
star
25

homebrew-clickhouse

ClickHouse Homebrew tap (old repository, unused)
57
star
26

clickhouse-tableau-connector-jdbc

Tableau connector to ClickHouse using JDBC driver
JavaScript
57
star
27

clickpy

PyPI analytics powered by ClickHouse
JavaScript
52
star
28

power-bi-clickhouse

This connector allows you to retrieve data from ClickHouse directly into Power BI for analysis and visualization
45
star
29

reversedns.space

https://reversedns.space/
HTML
44
star
30

clickhouse-academy

ClickHouse Academy training and certification
Python
39
star
31

libhdfs3

HDFS file read access for ClickHouse
C++
33
star
32

ch-bench

Benchmarks for ch
Go
30
star
33

sysroot

Files for cross-compilation
C
27
star
34

CryptoHouse

Artifacts including queries and materialized views used for CryptoHouse
TypeScript
26
star
35

HouseClick

House prices app
JavaScript
23
star
36

ch2rs

Generate Rust structs from ClickHouse rows
Rust
21
star
37

terraform-provider-clickhouse

Terraform Provider for ClickHouse Cloud
Go
21
star
38

web-tables-demo

15
star
39

clickhub

Github analytics powered by the world's fastest real-time analytics database
Python
13
star
40

icudata

Pregenerated data for ICU library
Assembly
11
star
41

clickhouse-website-worker

TypeScript
9
star
42

keeper-extend-cluster

Experiment on how to upgrade single-node clickhouse-keeper to a cluster
Makefile
7
star
43

copier

clickhouse-copier (obsolete)
C++
7
star
44

1trc

1 trillion rows
Python
7
star
45

checkout

Wrapper around actions/checkout for flexible tuning
6
star
46

laion

Supporting code for inserting and searching laion in ClickHouse
Python
5
star
47

bedrock_rag

A simple RAG pipeline for Google Analytics with ClickHouse and Bedrock
Python
5
star
48

aretestsgreenyet

A single-page website to display the status of the open-source ClickHouse CI system.
HTML
4
star
49

fuzz-corpus

Corpuses for libFuzzer-type fuzzers
4
star
50

clickhouse-playground-old

4
star
51

ch-async-inserts-demo

Demo on how to create a Node API that sends data to CH via Async inserts
TypeScript
3
star
52

grpc

Stripped version of grpc
C++
3
star
53

clickhouse-website-content

JavaScript
3
star
54

clickhouse-recipes

Sample code for solving common problems with ClickHouse
Python
3
star
55

clickhouse_vs_snowflake

HTML
2
star
56

clickhouse-com-content

HTML
2
star
57

clickhouse.github.io

HTML
2
star
58

antlr4-runtime

Subtree of antlr4 original repo
C++
2
star
59

kafka-samples

Sample datasets for Kafka
Python
2
star
60

ssl

Minimized libressl
C
2
star
61

libpq

Copy of https://github.com/postgres/postgres/tree/master/src/interfaces/libpq with some files from root
C
2
star
62

llvm

Stripped version of LLVM for use in ClickHouse for runtime code generation.
C++
2
star
63

perspective-forex

Perspective with ClickHouse example using Apache Arrow
JavaScript
2
star
64

clickhouse-typescript-schema

TypeScript
2
star
65

protobuf

add protobuf for libhdfs3
C++
1
star
66

hive-metastore

For files generated with https://github.com/apache/thrift
Thrift
1
star
67

boost-extra

extra boost libs for libhdfs3
C++
1
star
68

UnixODBC

Mirror of http://www.unixodbc.org/
C
1
star
69

doc-pr-preview-test

Testing workflow to build Docusaurus previews for pull requests.
JavaScript
1
star
70

clickhouse-docs-content

1
star
71

clickhouse-blog-images

HTML
1
star
72

bzip2

Forked from https://gitlab.com/federicomenaquintero/bzip2
C
1
star
73

libgsasl

https://www.gnu.org/software/gsasl/
C
1
star
74

readthedocs-stub

HTML
1
star
75

boost

Minimized boost lib
C++
1
star
76

clickhouse-repos-manager

a config and artifacts for packages.clickhouse.com
Python
1
star
77

clickhouse-kafka-transforms

This is meant to hold Clickhouse created kafka transforms.
Java
1
star
78

clickhouse-fivetran-destination

ClickHouse Cloud Fivetran Destination
Go
1
star
79

clickhouse-test.github.io

HTML
1
star
80

rust_vendor

Vendor files from rust dependencies
Rust
1
star
81

simple-logging-benchmark

A simple ClickHouse benchmark for the logging usecase
Python
1
star