### Install and Develop Arroyo Docs Locally

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/README.md

Installs dependencies and starts a local development server for the Arroyo documentation. Use this for making changes to the docs.

```bash
pnpm install
pnpm run dev
```

--------------------------------

### Install Arroyo

Source: https://context7.com/arroyosystems/arroyo-docs/llms.txt

Install Arroyo using package managers, a script, Docker, or Helm. After installation, start a local session cluster for development.

```shell
# macOS (Homebrew)
brew install arroyosystems/tap/arroyo
```

```shell
# Linux / macOS (install script)
curl -LsSf https://arroyo.dev/install.sh | sh
```

```shell
# Docker
docker run -p 5115:5115 ghcr.io/arroyosystems/arroyo:latest
```

```shell
# Kubernetes (Helm)
helm repo add arroyo https://helm.arroyo.dev
helm install arroyo arroyo/arroyo
```

```shell
# Start a local session cluster (Web UI at http://localhost:5115)
arroyo cluster
```

--------------------------------

### Arroyo TOML Configuration Example

Source: https://context7.com/arroyosystems/arroyo-docs/llms.txt

A comprehensive TOML configuration file for Arroyo, detailing settings for checkpointing, API, controller, database, and pipeline parameters. This example shows a full production setup.

```toml
# arroyo.toml — full production configuration example
checkpoint-url              = "s3://my-bucket/checkpoints"
default-checkpoint-interval = "30s"
disable-telemetry           = true

[api]
bind-address = "0.0.0.0"
http-port    = 5115
auth_mode    = { type = "static-api-key", api-key = "my-secret-key" }

[controller]
scheduler = "kubernetes"

[database]
type = "postgres"

[database.postgres]
host          = "db.prod.example.com"
port          = 5432
database-name = "arroyo"
user          = "arroyo"
password      = "arroyo"

[pipeline]
source-batch-size             = 256
update-aggregate-flush-interval = "500ms"
allowed-restarts              = 50
compaction.enabled            = true
compaction.checkpoints-to-compact = 6

[kubernetes-scheduler]
namespace     = "arroyo"
resource-mode = "per-slot"

[kubernetes-scheduler.worker]
image        = "ghcr.io/arroyosystems/arroyo:latest"
task-slots   = 8
resources    = { requests = { cpu = "1", memory = "2Gi" } }
```

--------------------------------

### Install Ubuntu Dependencies

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Installs necessary packages for building Arroyo on Ubuntu, including build tools, libraries, and Rust.

```bash
$ sudo apt-get install pkg-config build-essential \
  libssl-dev openssl cmake curl postgresql postgresql-client protobuf-compiler
$ sudo systemctl start postgresql
$ curl https://sh.rustup.rs -sSf | sh -s -- -y
$ cargo install refinery_cli
$ curl -fsSL https://get.pnpm.io/install.sh | sh -
```

--------------------------------

### Start Local Kafka Services

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/kafka.mdx

Use this command to start the Kafka broker and other services provided by Confluent Platform.

```bash
$ bin/confluent local services start
```

--------------------------------

### Start Frontend Development Server

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Navigates to the web UI directory and starts Vite's development server for faster frontend development cycles. Requires the Arroyo cluster to be running.

```bash
cd webui
$ pnpm run dev
```

--------------------------------

### Install Arroyo with Helm on Kubernetes

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/index.mdx

Add the Arroyo Helm repository and install Arroyo on Kubernetes.

```shellsession
helm repo add arroyo https://helm.arroyo.dev
helm install arroyo arroyo/arroyo
```

--------------------------------

### Session Window Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/windows.mdx

Use the SESSION() function to create windows based on gaps in activity. This example counts distinct auction IDs within sessions defined by a 30-minute gap.

```sql
SELECT SESSION(interval '30 minutes') as window,
    COUNT(DISTINCT auction_id) AS num_auctions
    FROM bids
    GROUP BY 1
```

--------------------------------

### Create Table with Partitioning

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/iceberg.mdx

Example of creating an Iceberg table partitioned by day and region using SQL.

```sql
CREATE TABLE partitioned_events (
    user_id BIGINT,
    region TEXT,
    event_type TEXT,
    value DOUBLE,
    event_time TIMESTAMP
) WITH (
    'connector' = 'iceberg',
    'catalog.type' = 'rest',
    'catalog.rest.url' = 'http://localhost:8181',
    'namespace' = 'analytics',
    'table_name' = 'events',
    'type' = 'sink'
)
PARTITIONED BY (
    day(event_time),
    identity(region)
);
```

--------------------------------

### Example: Using WITH Clause with Nexmark Data

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/select-statements.mdx

This example demonstrates using the WITH clause to define 'bids' and 'auctions' datasets from the nexmark source, which are then joined.

```sql
WITH bids AS
    (SELECT bid.auction AS auction, bid.price AS price
        FROM nexmark where bid is not null),
actions AS
    (SELECT auction.id AS id
        FROM nexmark where auction is not null)
SELECT * FROM bids bids
        JOIN auctions auctions
            ON bids.auction = auctions.id;
```

--------------------------------

### Start Arroyo Cluster with CLI

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.7.0.mdx

Use the `arroyo start` command to launch an Arroyo cluster within Docker. Future commands will support pipeline and query management.

```bash
$ arroyo start
```

--------------------------------

### Build Frontend with pnpm

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Installs frontend dependencies and builds the web UI using pnpm and vite.

```bash
$ cd webui
$ pnpm install
$ pnpm build
```

--------------------------------

### Kubernetes Helm Chart Values for Production

Source: https://context7.com/arroyosystems/arroyo-docs/llms.txt

Example `values.yaml` for deploying Arroyo to Kubernetes using Helm, configuring external PostgreSQL, S3 checkpointing, and AWS IAM authentication. This setup is suitable for production environments.

```yaml
# values.yaml — production EKS deployment
postgresql:
  deploy: false
  externalDatabase:
    host: arroyo-prod.cluster-dfg3fak5egvb.us-east-1.rds.amazonaws.com
    name: arroyo_prod
    user: arroyodb
    password: arroyodb

artifactUrl:   "s3://arroyo-checkpoints/artifacts"
checkpointUrl: "s3://arroyo-checkpoints/checkpoints"

env:
  - name: ARROYO__DEFAULT_CHECKPOINT_INTERVAL
    value: "30s"
  - name: AWS_ACCESS_KEY_ID
    valueFrom:
      secretKeyRef:
        name: aws-creds
        key: aws-access-key-id
  - name: AWS_SECRET_ACCESS_KEY
    valueFrom:
      secretKeyRef:
        name: aws-creds
        key: aws-secret-access-key
```

--------------------------------

### Build and Preview Arroyo Docs Statically

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/README.md

Builds a static version of the Arroyo documentation and starts a local server to preview it. Use this to test the final output.

```bash
pnpm run build
pnpm run preview
```

--------------------------------

### Install Arroyo via Homebrew

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/getting-started.mdx

Use this command to install the Arroyo CLI on MacOS using Homebrew.

```shell
brew install arroyosystems/tap/arroyo
```

--------------------------------

### Install Arroyo via Script

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/getting-started.mdx

Installs the Arroyo binary for MacOS and Linux using a curl script. Ensure you review scripts before execution.

```shell
curl -LsSf https://arroyo.dev/install.sh | sh
```

--------------------------------

### Install and Run Arroyo CLI

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.7.0.mdx

Install the Arroyo CLI using Cargo and run the `arroyo` command to see available options. This tool manages local Arroyo clusters.

```bash
$ cargo install arroyo
$ arroyo
Arroyo is a distributed stream processor that lets users ask complex
questions of high-volume real-time data by writing SQL.

This CLI can be used to run Arroyo clusters in Docker

Usage: arroyo <COMMAND>

Commands:
  start  Starts an Arroyo cluster in Docker
  stop   Stops a running Arroyo cluster
  help   Print this message or the help of the given subcommand(s)

Options:
  -h, --help     Print help
  -V, --version  Print version
```

--------------------------------

### SQL position Function Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/regex.mdx

Shows how to find the starting position of a substring within a larger string using the `position` function. Returns 0 if the substring is not found.

```sql
SELECT position('world' in 'hello world');
-- Expected output: 7
```

--------------------------------

### Query Data from Redis Sink

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/redis.mdx

Example of how data appears in Redis after being inserted by the sink connector. This shows a GET command retrieving a JSON string.

```bash
$ redis-cli
127.0.0.1:6379> GET outputs.fred
"{\"user_id\":\"fred\",\"event_count\":10}"
```

--------------------------------

### Install Arroyo Helm Chart

Source: https://context7.com/arroyosystems/arroyo-docs/llms.txt

Installs the Arroyo Helm chart. This requires adding the Arroyo Helm repository first. Ensure you have a 'values.yaml' file configured for your specific deployment.

```bash
helm repo add arroyo https://arroyosystems.github.io/helm-repo
helm install arroyo arroyo/arroyo -f values.yaml
```

--------------------------------

### TOML Configuration Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/configuration.mdx

Illustrates how to configure nested settings like scheduler and database type using TOML format. This is useful for defining application-wide settings.

```toml
checkpoint-url = 's3://my-bucket/checkpoints'

[controller]
scheduler = 'node'

[database]
type = "postgres"

```

--------------------------------

### Install MacOS Dependencies

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Installs dependencies on MacOS using Homebrew, including PostgreSQL, protobuf, and pnpm. Manually installs a specific CMake version.

```bash
$ brew install postgresql
$ brew install protobuf
$ brew install pnpm
```

```bash
$ curl -OL https://github.com/Kitware/CMake/releases/download/v3.31.7/cmake-3.31.7-macos-universal.tar.gz
$ tar xvfz cmake-3.*-macos-universal.tar.gz
$ mv cmake-3.*/Cmake.app /Applications
```

```bash
export PATH=$PATH:/Applications/CMake.app/Contents/bin
```

```bash
$ curl https://sh.rustup.rs -sSf | sh -s -- -y
```

```bash
$ cargo install refinery_cli
$ cargo install cargo-nextest --locked
```

--------------------------------

### Run Arroyo Node with Binary

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/vm.mdx

Start an Arroyo node process using the binary. Ensure the ARROYO__CONTROLLER_ENDPOINT is set to the controller's address.

```bash
$ ARROYO__CONTROLLER_ENDPOINT=http://localhost:9190 arroyo node
```

--------------------------------

### Install Kafka Connect Datagen Plugin

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/kafka.mdx

Install the datagen plugin for Kafka Connect to generate test data. This command also restarts the Connect services.

```bash
$ bin/confluent-hub install --no-prompt confluentinc/kafka-connect-datagen:latest
$ bin/confluent local services connect stop
$ bin/confluent local services connect start
```

--------------------------------

### Array Indexing Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.7.0.mdx

Demonstrates how to access elements within an array using numeric indices in SQL.

```sql
select make_array(1, 2, 3)[1]
from source;
```

--------------------------------

### Window Join Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/joins.mdx

Demonstrates an INNER join over time-oriented windows. Ensure both subqueries use the exact same window definition.

```sql
CREATE TABLE page_views (
  event_time TIMESTAMP,
  user_id TEXT,
  page_url TEXT
) WITH (
    ...
);

CREATE TABLE ad_clicks (
  event_time TIMESTAMP,
  user_id TEXT,
  ad_id TEXT
) WITH (
    ...
);

SELECT
  pv.window,
  page_views,
  ad_clicks
FROM (
  SELECT TUMBLE(INTERVAL '1 hour') AS window, COUNT(DISTINCT user_id) as page_views
  FROM page_views
  GROUP BY 1
) pv
INNER JOIN (
  SELECT TUMBLE(INTERVAL '1 hour') AS window, COUNT(DISTINCT user_id) as ad_clicks
  FROM ad_clicks
  GROUP BY 1
) ac
ON pv.window = ac.window;
```

--------------------------------

### Run Arroyo with Docker

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/index.mdx

Start an Arroyo instance using Docker, exposing the default port.

```shellsession
docker run -p 5115:5115 \
          ghcr.io/arroyosystems/arroyo:latest
```

--------------------------------

### Sliding Window Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/windows.mdx

Use the HOP() function to create sliding windows with a specified slide interval and window width. This example counts distinct auction IDs every second over a 1-minute window.

```sql
SELECT HOP(interval '1 second', interval '1 minute') as window,
    COUNT(DISTINCT auction_id) AS num_auctions
    FROM bids
    GROUP BY 1
```

--------------------------------

### Tumbling Window Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/windows.mdx

Use the TUMBLE() function to create non-overlapping windows of a fixed size. This example counts distinct auction IDs per minute.

```sql
SELECT TUMBLE(interval '1 minute') as window,
    COUNT(DISTINCT auction_id) AS num_auctions
    FROM bids
    GROUP BY window
```

--------------------------------

### Create Pipeline

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.5.0.mdx

Example of creating a new pipeline using the REST API.

```APIDOC
## POST /api/v1/pipelines

### Description
Creates a new pipeline.

### Method
POST

### Endpoint
/api/v1/pipelines

### Request Body
- **name** (string) - Required - The name of the pipeline.
- **parallelism** (integer) - Required - The desired parallelism for the pipeline.
- **query** (string) - Required - The SQL query defining the pipeline.
- **udfs** (array) - Required - A list of User Defined Functions to be used.

### Request Example
```shell
curl http://localhost:8000/api/v1/pipelines \
  -X POST -H "Content-Type: application/json" \
  --data @- << EOF
{
  "name": "my_pipeline",
  "parallelism": 1,
  "query": "\n    CREATE TABLE impulse (\n      counter BIGINT UNSIGNED NOT NULL,\n      subtask_index BIGINT UNSIGNED NOT NULL\n     ) \n     WITH (\n      connector = 'impulse',\n      event_rate = '100'\n   );\n   SELECT * from impulse;",
  "udfs": []
}
EOF
```

### Response
#### Success Response (200)
- **id** (string) - The unique identifier for the created pipeline.
- **name** (string) - The name of the pipeline.
- **stop** (string) - The stop mode for the pipeline.
- **createdAt** (integer) - Timestamp indicating when the pipeline was created.

#### Response Example
```json
{
  "id": "pl_W2UjDI6Iud",
  "name": "my_pipeline",
  "stop": "none",
  "createdAt": 1692054789252281
}
```
```

--------------------------------

### Install StreamGen with Cargo

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/udfs.mdx

Install the StreamGen tool using Cargo, the Rust package manager. This is required for generating sample data.

```bash
$ cargo install streamgen
```

--------------------------------

### Install Arroyo Helm Chart

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/kubernetes.mdx

Install the Arroyo Helm chart using a custom values.yaml file. Ensure the values.yaml file is correctly configured before running this command.

```sh
helm install arroyo arroyo/arroyo -f values.yaml
```

--------------------------------

### Update Stream Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.4.0.mdx

An example of an update stream output, showing create ('c'), update ('u'), and delete ('d') operations. This stream can be used directly or to materialize data into other databases.

```text
Time                     previous                              current                               op
7/10/23, 4:03:42 PM PDT  { "orders_store_id": 3, "count": 1 }  { "orders_store_id": 3, "count": 2 }  "u"
7/10/23, 4:03:40 PM PDT  null	                               { "orders_store_id": 1, "count": 1 }  "c"
7/10/23, 4:03:40 PM PDT  null                                  { "orders_store_id": 3, "count": 1 }  "c"
```

--------------------------------

### Start Local Arroyo Cluster

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/getting-started.mdx

Starts a complete Arroyo cluster locally. This command initiates the API server, controller, and compiler services. Access the Web UI at http://localhost:5115.

```shell
$ arroyo cluster
2024-07-01T22:58:29.316336Z  INFO arroyo_server_common: Starting cluster admin server on 0.0.0.0:8119
2024-07-01T22:58:29.339237Z  INFO arroyo_api: Starting API server on 0.0.0.0:5115
2024-07-01T22:58:29.342200Z  INFO arroyo_controller: Using process scheduler
2024-07-01T22:58:29.348490Z  INFO arroyo_controller: Starting arroyo-controller on 0.0.0.0:5116
2024-07-01T22:58:29.364186Z  INFO arroyo_compiler_service: Starting compiler service at 0.0.0.0:5117
```

--------------------------------

### Run Arroyo Cluster (Binary)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/vm.mdx

Start the Arroyo control plane as a single process using the arroyo binary. This is suitable for single-node deployments.

```bash
$ arroyo cluster
```

--------------------------------

### Updating Join Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/joins.mdx

An example of an updating join that behaves like a standard SQL join but produces incremental results. Be mindful of state size and TTL configuration.

```sql
CREATE TABLE users (
  user_id TEXT PRIMARY KEY,
  user_name TEXT
) WITH (
  ...
);

CREATE TABLE user_actions (
  event_time TIMESTAMP,
  user_id TEXT,
  action TEXT
) WITH (
  ...
);


SELECT u.user_id, u.user_name, a.action, a.event_time
FROM users u
JOIN user_actions a
ON u.user_id = a.user_id;
```

--------------------------------

### starts_with

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Tests if a string starts with a specified substring.

```APIDOC
## `starts_with`

Tests if a string starts with a substring.

```sql
starts_with(str, substr)
```

**Arguments**

- **str**: String expression to test. Can be a constant, column, or function, and any combination of string operators.
- **substr**: Substring to test for.
```

--------------------------------

### Configure SSE Connection with Auth Token

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/mastodon.mdx

Example of how to set an Authorization header for SSE connections requiring authentication, such as when using a personal Mastodon instance.

```sql
WITH (
  ...,
  headers = 'Authorization: Bearer <auth_token>'
)
```

--------------------------------

### Run Arroyo Cluster with PostgreSQL

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/debezium.mdx

Command to start the Arroyo cluster using PostgreSQL as the configuration store. This sets the necessary environment variable for the database type.

```bash
$ ARROYO__DATABASE__TYPE=postgres target/debug/arroyo cluster
```

--------------------------------

### Run Arroyo Binary

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Executes the compiled Arroyo binary to start various services or run commands.

```bash
$ target/debug/arroyo
```

--------------------------------

### Run Arroyo Cluster (Docker)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/vm.mdx

Start the Arroyo control plane as a single process using the arroyo Docker image. This is suitable for single-node deployments.

```bash
$ docker run ghcr.io/arroyosystems/arroyo:latest cluster
```

--------------------------------

### SQL regexp_replace Example with Global Flag

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/regex.mdx

Demonstrates replacing all occurrences of a pattern in a string using `regexp_replace` with the global flag 'g'. It shows how to capture groups and use backreferences in the replacement.

```sql
SELECT regexp_replace('foobarbaz', 'b(..)', 'X\1Y', 'g');
+------------------------------------------------------------------------+
| regexp_replace(Utf8("foobarbaz"),Utf8("b(..)"),Utf8("X\1Y"),Utf8("g")) |
+------------------------------------------------------------------------+
| fooXarYXazY                                                            |
+------------------------------------------------------------------------+
```

--------------------------------

### Create Kafka Source with SQL

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.4.0.mdx

Example SQL statement to create a Kafka source connector in Arroyo. Specifies connector type, format, bootstrap servers, topic, and source offset.

```sql
CREATE TABLE orders (
  customer_id INT,
  order_id INT
) WITH (
  connector = 'kafka',
  format = 'json',
  bootstrap_servers = 'broker-1.cluster:9092,broker-2.cluster:9092',
  topic = 'order_topic',
  type = 'source',
  'source.offset' = 'earliest'
);
```

--------------------------------

### Get Pipeline Start Timestamp

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/time-and-date.mdx

The `now()` function returns the UTC timestamp at the time the pipeline was compiled. This value remains constant throughout the pipeline's execution.

```sql
now()
```

--------------------------------

### Initialize and Migrate Database with Refinery

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Initializes the database schema using refinery and applies migrations.

```bash
$ refinery setup # follow the prompts
$ mv refinery.toml ~/
$ refinery migrate -c ~/refinery.toml -p crates/arroyo-api/migrations
```

--------------------------------

### Rust UDF with Git Dependencies and Environment Variables

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/udfs/rust/udfs.mdx

This example shows how to include Git dependencies in a Rust UDF, including the use of environment variables for authentication. These variables are substituted at compile time.

```rust
/*
[dependencies]
my-repo = {
  git = "https://{{ GITHUB_USER }}:{{ GITHUB_TOKEN }}@github.com/{{ GITHUB_ORG }}/my-repo.git"
}
*/
```

--------------------------------

### Create Kafka Sink Table

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/kafka.mdx

Use this SQL DDL statement to define a Kafka sink connection. Ensure 'bootstrap_servers' matches your Kafka setup.

```sql
CREATE TABLE kafka_sink (
    count BIGINT,
    store BIGINT
) WITH (
    'connector' = 'kafka',
    'type' = 'sink',
    'format' = 'json',
    -- use the same value for bootstrap_servers as you used above
    'bootstrap_servers' = 'localhost:9092',
    'topic' = 'results'
);
```

--------------------------------

### Set up Arroyo Database

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/debezium.mdx

Commands to create the 'arroyo' user, database, and run migrations for Arroyo.

```shell
$ psql postgres -c "CREATE USER arroyo WITH PASSWORD 'arroyo' SUPERUSER;"
$ createdb arroyo
$ arroyo migrate
```

--------------------------------

### Shuffle by Partition Configuration

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/iceberg.mdx

Demonstrates enabling shuffle by partition for the sink.

```markdown
You can combine multiple partition transforms:

```sql
CREATE TABLE partitioned_events (
    user_id BIGINT,
    region TEXT,
    event_type TEXT,
    value DOUBLE,
    event_time TIMESTAMP
) WITH (
    'connector' = 'iceberg',
    'catalog.type' = 'rest',
    'catalog.rest.url' = 'http://localhost:8181',
    'namespace' = 'analytics',
    'table_name' = 'events',
    'type' = 'sink'
)
PARTITIONED BY (
    day(event_time),
    identity(region)
);
```

### Shuffle by partition

<ShuffleByPartition />
```

--------------------------------

### Join Update Tables SQL Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.9.0.mdx

Demonstrates joining an aggregated update table with another stream. This enables incremental computation for analytical SQL constructs previously restricted to watermark semantics.

```sql
CREATE VIEW page_view_counts as
SELECT count(*) as count, user_id as user_id
FROM page_views
GROUP BY user_id;


SELECT T.user_id, amount, P.count as page_views
FROM transactions T
LEFT JOIN page_view_counts P on T.user_id = P.user_id;
```

--------------------------------

### Configure PostgreSQL on Ubuntu

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Sets up a PostgreSQL user and database named 'arroyo' with superuser privileges on Ubuntu.

```bash
$ sudo -u postgres psql -c "CREATE USER arroyo WITH PASSWORD 'arroyo' SUPERUSER;"
$ sudo -u postgres createdb arroyo
```

--------------------------------

### Pipeline Source Batch Size Configuration

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/configuration.mdx

Example of setting the `source-batch-size` within the `pipeline` section of a configuration file. This controls the maximum size of batches read from the source.

```toml
[pipeline]
source-batch-size = 128

```

--------------------------------

### Get Array Length with array_length

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Use `array_length` to get the size of an array. This function is also aliased as `list_length`.

```sql
select array_length([1, 2, 3, 4, 5]);
-- Result: 5
```

--------------------------------

### Create Sink Table with Schema Inference

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.8.0.mdx

Example of creating a sink table without explicitly defining its schema. Arroyo infers the schema based on how the table is used in subsequent operations.

```sql
CREATE TABLE output WITH (
    connector = 'kafka',
    type = 'sink',
    bootstrap_servers = 'localhost:9092',
    format = 'json',
    'topic' = 'outputs'
);

```

--------------------------------

### Create Kafka Source Using Connection Profile

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.8.0.mdx

Example of creating a Kafka source table in SQL by referencing a pre-configured connection profile named 'local_kafka'. This profile contains common connection details.

```sql
CREATE TABLE events WITH (
    connector = 'kafka',
    type = 'source',
    format = 'json',
    'connection_profile' = 'local_kafka'
);

```

--------------------------------

### Run Arroyo Cluster with Default Scheduler

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Starts a new Arroyo cluster using the default 'process' scheduler, running each pipeline as a separate process on the same node. Access the cluster at http://localhost:5115.

```bash
$ target/debug/arroyo cluster
```

--------------------------------

### strpos

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Returns the starting position of a specified substring within a string. Positions start at 1, and returns 0 if the substring is not found.

```APIDOC
## `strpos`

Returns the starting position of a specified substring in a string. Positions begin at 1. If the substring does not exist in the string, the function returns 0.

```sql
strpos(str, substr)
```

**Arguments**

- **str**: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- **substr**: Substring expression to search for. Can be a constant, column, or function, and any combination of string operators.

**Aliases**

- instr
```

--------------------------------

### btrim

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Trims the specified trim string from the start and end of a string. If no trim string is provided, all whitespace is removed from the start and end of the input string.

```APIDOC
## btrim

### Description
Trims the specified trim string from the start and end of a string. If no trim string is provided, all whitespace is removed from the start and end of the input string.

### Syntax
```sql
btrim(str[, trim_str])
```

### Arguments
- **str** (String expression) - The string expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- **trim_str** (String expression, optional) - The string expression to trim from the beginning and end of the input string. Can be a constant, column, or function, and any combination of arithmetic operators. Defaults to whitespace characters.

### Related functions
- [ltrim](#ltrim)
- [rtrim](#rtrim)

### Aliases
- trim
```

--------------------------------

### Run Arroyo Database Migrations (Binary)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/vm.mdx

Execute database migrations for the configuration store when using the arroyo binary. Ensure your database is accessible and configured correctly.

```bash
$ arroyo migrate
```

--------------------------------

### Get Distinct Array Elements with array_distinct

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Use `array_distinct` to get unique elements from an array, removing any duplicates. This function is also aliased as `list_distinct`.

```sql
select array_distinct([1, 3, 2, 3, 1, 2, 4]);
-- Result: [1, 2, 3, 4]
```

--------------------------------

### Display Help for `arroyo run`

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/pipeline-clusters.mdx

Use this command to view the available options and arguments for running a pipeline cluster.

```shell
$ arroyo run --help
Run a query as a local pipeline cluster

Usage: arroyo run [OPTIONS] [QUERY]

Arguments:
  [QUERY]  The query to run [default: -]

Options:
  -n, --name <NAME>                Name for this pipeline
  -s, --state-dir <STATE_DIR>      Directory or URL where checkpoints and metadata
                                   will be written and restored from
  -p, --parallelism <PARALLELISM>  Number of parallel subtasks to run [default: 1]
  -f, --force                      Force the pipeline to start even if the state
                                   file does not match the query
  -h, --help                       Print help

```

--------------------------------

### Common Filesystem Configuration

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/filesystem.mdx

This snippet shows common configurations for various filesystem paths, including local, S3, and Azure Blob Storage. Use these examples to set up your `CREATE TABLE` statement with the correct `path` URL.

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'file:///test-data/my-cool-arroyo-pipeline'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 's3://awesome-arroyo-bucket/amazing-arroyo-dir'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'https://s3.us-west-2.amazonaws.com/awesome-arroyo-bucket/amazing-arroyo-dir'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 's3::http://localhost:9123/local_bucket/sweet-dir'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'r2://my-bucket/path'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'r2://account-id@my-bucket/path'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'abfs://container@account.dfs.core.windows.net/path'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'https://account.blob.core.windows.net/container/path'
)
```

```sql
CREATE TABLE my_table (
  id INT,
  value STRING
) WITH (
  format = 'parquet',
  path = 'gs://my-bucket/path'
)
```

--------------------------------

### Configure PostgreSQL on MacOS

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Sets up a PostgreSQL user and database named 'arroyo' with superuser privileges on MacOS.

```bash
$ psql postgres -c "CREATE USER arroyo WITH PASSWORD 'arroyo' SUPERUSER;"
$ createdb arroyo
```

--------------------------------

### Arroyo CLI Help

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/getting-started.mdx

Displays the available commands and options for the Arroyo CLI. Use this to understand the different subcommands like 'run', 'api', 'cluster', etc.

```shell
$ arroyo --help
Usage: arroyo [OPTIONS] <COMMAND>

Commands:
  run         Run a query as a local pipeline cluster
  api         Starts an Arroyo API server
  controller  Starts an Arroyo Controller
  cluster     Starts a complete Arroyo cluster
  worker      Starts an Arroyo worker
  compiler    Starts an Arroyo compiler server
  node        Starts an Arroyo node server
  migrate     Runs database migrations on the configured Postgres database
  help        Print this message or the help of the given subcommand(s)

Options:
  -c, --config <CONFIG>          Path to an Arroyo config file, in TOML or YAML
                                 format
      --config-dir <CONFIG_DIR>  Directory in which to look for configuration files
  -h, --help                     Print help
  -V, --version                  Print version
```

--------------------------------

### Run Arroyo Database Migrations (Docker)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/vm.mdx

Execute database migrations for the configuration store using the arroyo Docker image. Configure PostgreSQL connection details via environment variables if necessary.

```bash
# Add other env vars as appropriate; this example will run migrations
# on the host computer in Docker for Mac
$ docker run -e ARROYO__DATABASE__POSTGRES__HOST=host.docker.internal \
    ghcr.io/arroyosystems/arroyo:latest migrate
```

--------------------------------

### Create Pipeline using REST API

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.4.0.mdx

This example demonstrates how to create a SQL pipeline using the new REST API endpoints introduced in Arroyo v0.4.0. This allows for automation and integration with external systems.

```APIDOC
## POST /v1/pipelines

### Description
Creates a new pipeline with the specified configuration.

### Method
POST

### Endpoint
/v1/pipelines

### Request Body
- **name** (string) - Required - The name of the pipeline.
- **query** (string) - Required - The SQL query that defines the pipeline.
- **udfs** (array) - Optional - A list of User Defined Functions to be used in the pipeline.
- **parallelism** (integer) - Optional - The level of parallelism for the pipeline.

### Request Example
```json
{
  "name": "orders",
  "query": "SELECT * FROM orders;",
  "udfs": [],
  "parallelism": 1
}
```

### Response
#### Success Response (200)
Details about the created pipeline (schema not specified in source).

#### Response Example
(Example not provided in source)
```

--------------------------------

### substr

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Extracts a substring of a specified number of characters from a specific starting position in a string.

```APIDOC
## `substr`

Extracts a substring of a specified number of characters from a specific starting position in a string.

```sql
substr(str, start_pos[, length])
```

**Arguments**

- **str**: String expression to operate on. Can be a constant, column, or function, and any combination of string operators.
- **start_pos**: Character position to start the substring at. The first character in the string has a position of 1.
- **length**: Number of characters to extract. If not specified, returns the rest of the string after the start position.
```

--------------------------------

### position

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/regex.mdx

Returns the starting position of a substring within a string. Returns 0 if the substring is not found.

```APIDOC
## `position`

Returns the position of `substr` in `origstr` (counting from 1). If `substr` does not appear in `origstr`, return 0.

```sql
position(substr in origstr)
```

**Arguments**

- **substr**: The pattern string.
- **origstr**: The model string.
```

--------------------------------

### Create Websocket Source and Analyze Data with SQL

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.4.0.mdx

SQL to create a Websocket source for Coinbase's API and compute the average Bitcoin price over the last minute. Requires defining table schema and connector parameters.

```sql
CREATE TABLE coinbase (
    type TEXT,
    price TEXT
) WITH (
    connector = 'websocket',
    endpoint = 'wss://ws-feed.exchange.coinbase.com',
    subscription_message = '{
      "type": "subscribe",
      "product_ids": [
        "BTC-USD"
      ],
      "channels": ["ticker"]
    }',
    format = 'json'
);

SELECT avg(CAST(price as FLOAT)) from coinbase
WHERE type = 'ticker'
GROUP BY hop(interval '5' second, interval '1 minute');
```

--------------------------------

### Build Arroyo Services with Cargo

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Builds the main Arroyo binary, which includes all services, using Cargo.

```bash
$ cargo build --package arroyo
```

--------------------------------

### Create Connection Table (Kafka Source)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/ddl.mdx

Defines a table that connects to an external system, such as Kafka. This example shows how to create a Kafka source table, specifying connector and format options, along with connection-specific parameters like bootstrap servers and topic.

```sql
CREATE TABLE orders (
  customer_id INT,
  order_id INT,
  date_string TEXT
) WITH (
  connector = 'kafka',
  format = 'json',
  type = 'source',
  bootstrap_servers = 'localhost:9092',
  topic = 'order_topic'
);
```

--------------------------------

### overlay

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Replaces a portion of a string with another string, starting at a specified position and for a specified number of characters.

```APIDOC
## `overlay`

Returns the string which is replaced by another string from the specified position and specified count length. For example, `overlay('Txxxxas' placing 'hom' from 2 for 4) → Thomas`

```sql
overlay(str PLACING substr FROM pos [FOR count])
```

**Arguments**

- **str**: String expression to operate on.
- **substr**: the string to replace part of str.
- **pos**: the start position to replace of str.
- **count**: the count of characters to be replaced from start position of str. If not specified, will use substr length instead.
```

--------------------------------

### Run Query from File

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/pipeline-clusters.mdx

Specify a SQL query file as a command-line argument to `arroyo run`.

```shell
$ arroyo run query.sql

```

--------------------------------

### Find position of substring

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Returns the starting position of the first occurrence of a substring within a string. This is an alias for `strpos`.

```sql
instr(str, substr)
```

--------------------------------

### Get Array Dimensions with array_dims

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Use `array_dims` to retrieve the dimensions of an array. This function is also aliased as `list_dims`.

```sql
select array_dims([[1, 2, 3], [4, 5, 6]]);
-- Result: [2, 3]
```

--------------------------------

### Get Event Row Timestamp

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/time-and-date.mdx

The `row_time()` function returns the event timestamp associated with the current row being processed.

```sql
row_time()
```

--------------------------------

### Check Pod Status

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/kubernetes.mdx

Verify that all Arroyo pods are running after installation. This command lists the status of pods in the Kubernetes cluster.

```sh
kubectl get pods
```

--------------------------------

### starts_with Function

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Tests if a string begins with a specified substring. Arguments include the string to test and the substring to look for.

```sql
starts_with(str, substr)
```

--------------------------------

### array_slice

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Returns a slice of the array based on 1-indexed start and end positions. Supports negative indexing and an optional stride.

```APIDOC
## `array_slice`

Returns a slice of the array based on 1-indexed start and end positions.

```sql
array_slice(array, begin, end)
```

**Arguments**

- **array**: Array expression. Can be a constant, column, or function, and any combination of array operators.
- **begin**: Index of the first element. If negative, it counts backward from the end of the array.
- **end**: Index of the last element. If negative, it counts backward from the end of the array.
- **stride**: Stride of the array slice. The default is 1.

**Example**

```sql
> select array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6);
+--------------------------------------------------------+
| array_slice(List([1,2,3,4,5,6,7,8]),Int64(3),Int64(6)) |
+--------------------------------------------------------+
| [3, 4, 5, 6]                                           |
+--------------------------------------------------------+
```

**Aliases**

- list_slice
```

--------------------------------

### Use UDF in SELECT Statement

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/udfs.mdx

Example of calling a UDF within a SELECT statement to process data from a 'logs' table.

```sql
select parse_log(value) from logs;
```

--------------------------------

### Add Arroyo Helm Repository

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/deployment/kubernetes.mdx

Add the official Arroyo Helm repository to your local Helm installation to access the Arroyo chart.

```shell
helm repo add arroyo https://arroyosystems.github.io/helm-repo
```

--------------------------------

### Build Arroyo Docker Image (Base)

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/developing/dev-setup.mdx

Builds a minimal Docker image containing only the Arroyo binary, primarily for deployment on Kubernetes or in Docker Compose. Requires a recent Docker version supporting buildx.

```bash
$ docker build . -f docker/Dockerfile -t {target} --target {target}
```

--------------------------------

### Row Number Window Function

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/window-functions.mdx

The `row_number()` function assigns a unique sequential integer to each row within its partition, starting from 1.

```sql
row_number()
```

--------------------------------

### Get Array Cardinality with cardinality

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Calculates the total number of elements within an array. This function is useful for determining the size of an array.

```sql
select cardinality([[1, 2, 3, 4], [5, 6, 7, 8]]);
```

--------------------------------

### Create an MQTT Source Table

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/mqtt.mdx

Defines a table to consume JSON messages from a specified MQTT topic. Includes metadata extraction for the topic.

```sql
create table mqtt_source (
    id TEXT,
    lat FLOAT,
    lng FLOAT,
    type INT,
    topic TEXT METADATA FROM 'topic'
) with (
    connector = 'mqtt',
    url = 'tcp://localhost:1833',
    type = 'source',
    format = 'json',
    topic = 'events'
);
```

--------------------------------

### Get Number of Array Dimensions with array_ndims

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Use `array_ndims` to determine the number of dimensions in an array. This function is also aliased as `list_ndims`.

```sql
select array_ndims([[1, 2, 3], [4, 5, 6]]);
-- Result: 2
```

--------------------------------

### File System Source DDL Example

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/connectors/filesystem.mdx

Use this DDL to create a table for reading data from a file system source, such as S3. It supports specifying the path, format, and a regex pattern for filtering files.

```sql
CREATE TABLE bids (
  auction bigint,
  bidder bigint,
  price bigint,
  datetime timestamp,
  region text,
  account_id text
) WITH (
  connector = 'filesystem',
  type = 'source',
  path = 'https://s3.us-west-2.amazonaws.com/demo/s3-uri',
  format = 'parquet',
  'source.regex-pattern' = '.*\.parquet$'
);
```

--------------------------------

### Get characters from left of string

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Extracts a specified number of characters from the beginning (left side) of a string. Useful for prefix extraction.

```sql
left(str, n)
```

--------------------------------

### Get ASCII value of a character

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/string.mdx

Returns the ASCII value of the first character in a given string. Useful for character encoding checks.

```sql
ascii(str)
```

--------------------------------

### Configure Filesystem Sink with Partitioning

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/releases/v0.7.0.mdx

Use this SQL syntax to configure the filesystem sink with both time-based and field-based partitioning. This optimizes data layout for query performance.

```sql
CREATE TABLE file_sink (
    time TIMESTAMP,
    event_type TEXT,
    user_id TEXT,
    count INT
) WITH (
    connector = 'filesystem',
    format = 'parquet',
    path = 's3://arroyo-events/realtime',
    rollover_seconds = '3600',
    time_partition_pattern = 'year=%Y/month=%m/day=%d/hour=%H',
    partition_fields = 'event_type'
);
```

--------------------------------

### Slice Array with array_slice

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/sql/scalar-functions/array.mdx

Extracts a portion of an array using 1-indexed start and end positions. Negative indices count backward from the end.

```sql
select array_slice([1, 2, 3, 4, 5, 6, 7, 8], 3, 6);
```

--------------------------------

### Restart Kafka Broker Services

Source: https://github.com/arroyosystems/arroyo-docs/blob/main/src/content/docs/tutorial/kafka.mdx

Commands to stop and then start the Confluent Kafka local services. This is necessary after modifying the server properties file.

```bash
$ bin/confluent local services stop
$ bin/confluent local services start
```