Materialize

https://materialize.com/docs/
Admin
Materialize is a streaming database that enables real-time data processing and analytics by allowing...
Tokens:2,709,924
Snippets:22,200
Trust Score:9.9
Update:2 weeks ago
Show doc for...
Tokens:
Raw
### SHOW SUBSOURCES examples

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Examples showing SHOW SOURCES output and then SHOW SUBSOURCES ON for the postgres and kafka sources, with their respective output tables.

```mzsql
SHOW SOURCES;
```

```nofmt
    name
----------
 postgres
 kafka
```

```mzsql
SHOW SUBSOURCES ON pg;
```

```nofmt
        name        | type
--------------------+-----------
 postgres_progress  | progress
 table1_in_postgres | subsource
 table2_in_postgres | subsource
```

```mzsql
SHOW SUBSOURCES ON kafka;
```

```nofmt
            name    | typef
--------------------+----------
 kafka_progress     | progress
```

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

### SHOW CREATE INDEX example

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Shows how to list indexes on a view and then display the DDL for a specific index. The output includes the index name, cluster, key columns, and the CREATE INDEX statement.

```mzsql
SHOW INDEXES FROM my_view;
```

```nofmt
     name    | on  | cluster    | key
-------------+-----+------------+--------------------------------------------
 my_view_idx | t   | quickstart | {a, b}
```

```mzsql
SHOW CREATE INDEX my_view_idx;
```

```nofmt
              name              |                                           create_sql
--------------------------------+------------------------------------------------------------------------------------------------
 materialize.public.my_view_idx | CREATE INDEX "my_view_idx" IN CLUSTER "default" ON "materialize"."public"."my_view" ("a", "b")
```

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

### SHOW SCHEMAS example with SHOW DATABASES

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Shows the databases available in Materialize, then lists the schemas in the 'my_db' database. The output format for SHOW SCHEMAS is a table with one column, 'name'.

```mzsql
SHOW DATABASES;
```

```nofmt
   name
-----------
materialize
my_db
```

```mzsql
SHOW SCHEMAS FROM my_db
```

```nofmt
  name
--------
 public
```

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

### Map/Filter/Project operator example

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Shows the output of the EXPLAIN plan for a Map/Filter/Project operator. The Filter predicate is (#0{a} < 7) and the Map expression is (#0{a} + #1{b}).

```mzsql
→Map/Filter/Project 
  Filter: (#0{a} < 7) 
  Map: (#0{a} + #1{b}) 
```

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

### Table Function operator example

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Shows the output of the EXPLAIN plan for a Table Function operator using generate_series with input key (#0{a}).

```mzsql
→Table Function generate_series(#0{a}, #1{b}, 1) 
  Input key: (#0{a}) 
```

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

### SHOW ROLE MEMBERSHIP FOR example

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Shows role memberships for the specified role r2. The output is filtered to that role.

```mzsql
SHOW ROLE MEMBERSHIP FOR r2;
```

```nofmt
 role | member |  grantor
------+--------+-----------
 r3   | r2     | mz_system
 r4   | r3     | mz_system
```

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

### COPY FROM STDIN with delimiter and format options

Source: https://materialize.com/docs/markdown-docs/sql/index.md

These three examples show different ways to copy data from standard input into a table. The first uses the WITH clause to specify a delimiter, the second uses the FORMAT option to specify CSV format, and the third uses the DELIMITER option directly. Use these when you need to load data from STDIN with specific formatting options.

```mzsql
COPY t FROM STDIN WITH (DELIMITER '|');
```

```mzsql
COPY t FROM STDIN (FORMAT CSV);
```

```mzsql
COPY t FROM STDIN (DELIMITER '|');
```

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

### Accumulable GroupAggregate EXPLAIN output

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Example output of an EXPLAIN plan showing an Accumulable GroupAggregate operator with a count(*) aggregate and a post-process filter. The arrow indicates the operator's output, and the indentation shows the operator hierarchy.

```mzsql
→Accumulable GroupAggregate 
  Simple aggregates: count(*) 
  Post-process Map/Filter/Project 
    Filter: (#0 > 1) 
```

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

### COPY INTO from AWS S3 with AWS connection

Source: https://materialize.com/docs/markdown-docs/sql/index.md

These examples show how to perform bulk imports from AWS S3 using an AWS connection. The first uses the FILES option to specify a list of files, the second uses the full S3 URI to specify a single file, and the third uses the PATTERN option to match files by pattern. Use these when you need to import data from S3 with specific file selection criteria.

```mzsql
COPY INTO csv_table FROM 's3://example_bucket' (FORMAT CSV, AWS CONNECTION = example_aws_conn, FILES = ['example_data.csv']);
```

```mzsql
COPY INTO csv_table FROM 's3://example_bucket/example_data.csv' (FORMAT CSV, AWS CONNECTION = example_aws_conn);
```

```mzsql
COPY INTO parquet_table FROM 's3://example_bucket' (FORMAT PARQUET, AWS CONNECTION = example_aws_conn, PATTERN = '*parquet*');
```

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

### SUBSCRIBE with SNAPSHOT = false

Source: https://materialize.com/docs/markdown-docs/sql/index.md

Two examples of SUBSCRIBE with the SNAPSHOT option set to false. The first subscribes to a query that sums a column, requiring Materialize to fetch and process snapshot data to compute the correct result. The second subscribes directly to a collection (materialized view, table, source, or index), allowing Materialize to generally skip fetching or processing snapshot data entirely.

```mzsql
SUBSCRIBE TO SELECT SUM(column) FROM table WITH (SNAPSHOT = false)
```

```mzsql
SUBSCRIBE TO <object> WITH (SNAPSHOT = false)
```

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

### Create source, materialized view, and index for auction data

Source: https://materialize.com/docs/transform-data/dataflow-troubleshooting

Creates a source from the AUCTION load generator, a materialized view that joins bids and auctions to count bids per item, and an index on the view. This is the example from the getting started guide used to illustrate dataflow translation.

```sql
CREATE SOURCE auction_house
  FROM LOAD GENERATOR AUCTION
  (TICK INTERVAL '1s', AS OF 100000)
  FOR ALL TABLES;

CREATE MATERIALIZED VIEW num_bids AS
  SELECT auctions.item, count(bids.id) AS number_of_bids
  FROM bids
  JOIN auctions ON bids.auction_id = auctions.id
  WHERE bids.bid_time < auctions.end_time
  GROUP BY auctions.item;

CREATE INDEX num_bids_idx ON num_bids (item);

```

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

### SHOW CREATE TABLE output example

Source: https://materialize.com/docs/sql/show-create-table

Example output of SHOW CREATE TABLE, showing the create_sql column with the full CREATE TABLE statement.

```text
         name         |                                             create_sql
----------------------+-----------------------------------------------------------------------------------------------------
 materialize.public.t | CREATE TABLE "materialize"."public"."t" ("a" "pg_catalog"."int4", "b" "pg_catalog"."text" NOT NULL)
```

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

### Setup script to install mz-deploy in Claude Code cloud sandbox

Source: https://materialize.com/docs/developer-tools/mz-deploy/agent-setup

Setup script for Claude Code on the web to install mz-deploy in the fresh cloud sandbox. Runs once as root before Claude Code starts. The sandbox runs Ubuntu on Linux, so it always uses the `unknown-linux-gnu` build and resolves the architecture (`x86_64` or `aarch64`) at runtime. The binary lands in `/usr/local/bin`, which is already on `PATH`; no `sudo` is needed. Setup scripts have network access under the default **Trusted** network mode; if your environment uses **None**, the download will fail.

```bash
#!/bin/bash
set -euo pipefail
ARCH=$(uname -m)
curl -L "https://binaries.materialize.com/mz-deploy-latest-$ARCH-unknown-linux-gnu.tar.gz" \
| tar -xzC /usr/local --strip-components=1

```

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

### Create and show databases example

Source: https://materialize.com/docs/markdown-docs/sql/show-databases/index.md

Creates a database named my_db, then shows all databases. The output lists materialize and my_db.

```mzsql
CREATE DATABASE my_db;
```

```mzsql
SHOW DATABASES;
```

```nofmt
materialize
my_db
```

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

### mz-deploy help command usage

Source: https://materialize.com/docs/markdown-docs/developer-tools/mz-deploy/agent-setup/index.md

Use mz-deploy help to get full guides with behavior notes, examples, error recovery steps, and related commands, unlike --help which prints brief CLI usage.

```bash
mz-deploy help <command>    # Detailed guide for a single command
mz-deploy help --all        # All command guides concatenated
```

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

### SHOW CREATE TABLE example

Source: https://materialize.com/docs/sql/show-create-table

Displays the SQL used to create table t.

```sql
SHOW CREATE TABLE t;
```

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

### Get detailed mz-deploy help

Source: https://materialize.com/docs/developer-tools/mz-deploy/agent-setup

Agent-optimized help commands. Unlike `--help` (which prints brief CLI usage), `help` returns full guides with behavior notes, examples, error recovery steps, and related commands.

```bash
mz-deploy help <command>    # Detailed guide for a single command
mz-deploy help --all        # All command guides concatenated

```

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

### Create and populate example_table

Source: https://materialize.com/docs/sql/update

Creates the example table used in the examples and inserts two rows.

```sql
CREATE TABLE example_table (a int, b text);
INSERT INTO example_table VALUES (1, 'hello'), (2, 'goodbye');

```

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

### Get operator example

Source: https://materialize.com/docs/markdown-docs/sql/explain-plan-operators/index.md

Example of the Get operator, which produces rows from an existing relation or a previous CTE. It does not increase data size or use memory.

```mzsql
Get materialize.public.ordered
```

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

### Arranged Get operator example

Source: https://materialize.com/docs/markdown-docs/sql/explain-plan-operators/index.md

Example of the Arranged Get operator in an LIR plan, indicating that results are arranged in memory. It does not increase data size and does not use memory.

```mzsql
Arranged materialize.public.t
```

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

### SHOW CREATE CONNECTION example output

Source: https://materialize.com/docs/sql/show-create-connection

Example output from the SHOW CREATE CONNECTION command, showing the create_sql for the kafka_connection.

```text
    name          |    create_sql
------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 kafka_connection | CREATE CONNECTION "materialize"."public"."kafka_connection" TO KAFKA (BROKER 'unique-jellyfish-0000.us-east-1.aws.confluent.cloud:9092', SASL MECHANISMS = 'PLAIN', SASL USERNAME = SECRET sasl_username, SASL PASSWORD = SECRET sasl_password)
```

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

### SHOW CREATE SOURCE example with output

Source: https://materialize.com/docs/sql/show-create-source

Example of SHOW CREATE SOURCE for the market_orders_raw source, followed by its output showing the source name and the CREATE SOURCE statement.

```sql
SHOW CREATE SOURCE market_orders_raw;
```

```text
                 name                 |                                      create_sql
--------------------------------------+--------------------------------------------------------------------------------------------------------------
 materialize.public.market_orders_raw | CREATE SOURCE "materialize"."public"."market_orders_raw" IN CLUSTER "c" FROM LOAD GENERATOR COUNTER

```

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

### Install pg gem

Source: https://materialize.com/docs/markdown-docs/serve-results/client-libraries/ruby/index.md

Installs the pg gem, required to use the Ruby examples on this page.

```bash
gem install pg
```

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

### SHOW SOURCES example

Source: https://materialize.com/docs/markdown-docs/sql/show-subsources/index.md

Example of the SHOW SOURCES command, which lists available sources. The output shows two sources: postgres and kafka.

```mzsql
SHOW SOURCES;
```

```nofmt
    name
----------
 postgres
 kafka
```

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

### Verify Materialize operator installation with kubectl get all

Source: https://materialize.com/docs/self-managed-deployments/installation/install-on-local-kind

Run `kubectl get all -n materialize` to verify the Materialize operator installation. The expected output shows the operator pods, service, deployment, and replicaset in the `Running` state.

```bash
kubectl get all -n materialize

```

```text
NAME                                           READY   STATUS    RESTARTS   AGE
pod/my-materialize-operator-6c9d55567f-ldmd7   1/1     Running   0          4s
pod/my-materialize-operator-6c9d55567f-svwks   1/1     Running   0          4s

NAME                              TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
service/my-materialize-operator   ClusterIP   10.96.231.91   <none>        8001/TCP   4s

NAME                                      READY   UP-TO-DATE   AVAILABLE   AGE
deployment.apps/my-materialize-operator   2/2     2            2           4s

NAME                                                 DESIRED   CURRENT   READY   AGE
replicaset.apps/my-materialize-operator-6c9d55567f   2         2         2       4s

```
Tokens:
Raw