[アップデート] Amazon Redshift で Apache Iceberg マテリアライズドビューが作成できるようになったので試してみた

CREATE MATERIALIZED VIEW mv_name
USING ICEBERG
[LOCATION 's3://bucket/path/']
[PARTITIONED BY (partition_transform [, ...])]
[TABLE PROPERTIES ('property_name' = 'property_value' [, ...])]
AS query

% aws lakeformation list-permissions \
  --resource '{"Database":{"Name":"iceberg_mv_blog"}}' \
  --region ap-northeast-1 \
  --query 'PrincipalResourcePermissions[].[Principal.DataLakePrincipalIdentifier,join(`,`,Permissions)]' \
  --output table
-----------------------------------------------------------------------------------------------
|                                       ListPermissions                                       |
+----------------------------------------------------+----------------------------------------+
|  arn:aws:iam::123456789012:role/cm-ishikawa |  ALL,ALTER,CREATE_TABLE,DESCRIBE,DROP  |
+----------------------------------------------------+----------------------------------------+

% aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=IAM_ALLOWED_PRINCIPALS \
  --resource '{"Database":{"Name":"iceberg_mv_blog"}}' \
  --permissions ALL \
  --region ap-northeast-1

% aws lakeformation list-permissions \
  --resource '{"Database":{"Name":"iceberg_mv_blog"}}' \
  --region ap-northeast-1 \
  --query 'PrincipalResourcePermissions[].[Principal.DataLakePrincipalIdentifier,join(`,`,Permissions)]' \
  --output table
-----------------------------------------------------------------------------------------------
|                                       ListPermissions                                       |
+----------------------------------------------------+----------------------------------------+
|  arn:aws:iam::123456789012:role/cm-ishikawa |  ALL,ALTER,CREATE_TABLE,DESCRIBE,DROP  |
|  IAM_ALLOWED_PRINCIPALS                            |  ALL                                   |
+----------------------------------------------------+----------------------------------------+

CREATE EXTERNAL SCHEMA iceberg_mv_blog
FROM DATA CATALOG
DATABASE 'iceberg_mv_blog'
REGION 'ap-northeast-1'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-iceberg-mv-definer-ap-northeast-1';

ERROR: VARCHAR(N) specifying length is not supported for column "customer_name" in Iceberg table. Hint: Use VARCHAR for strings in Iceberg tables.

CREATE TABLE iceberg_mv_blog.customers (
    customer_id   INT,
    customer_name VARCHAR,
    region        VARCHAR
)
USING ICEBERG
LOCATION 's3://redshift-iceberg-mv-123456789012-ap-northeast-1/source/customers/';

CREATE TABLE iceberg_mv_blog.orders (
    order_id    INT,
    customer_id INT,
    order_date  DATE,
    amount      DECIMAL(10,2)
)
USING ICEBERG
LOCATION 's3://redshift-iceberg-mv-123456789012-ap-northeast-1/source/orders/';

INSERT INTO iceberg_mv_blog.customers VALUES
    (1, 'alice', 'jp'),
    (2, 'bob',   'us'),
    (3, 'carol', 'eu'),
    (4, 'dave',  'jp');

INSERT INTO iceberg_mv_blog.orders VALUES
    (1, 1, '2026-10-01', 100.00),
    (2, 2, '2026-10-01', 200.00),
    (3, 3, '2026-10-02', 150.00),
    (4, 1, '2026-10-02',  80.00),
    (5, 4, '2026-10-03', 120.00),
    (6, 2, '2026-10-03',  60.00);

% aws s3 cp s3://redshift-iceberg-mv-123456789012-ap-northeast-1/source/orders/metadata/00001-0e10cd25-e8f4-4c7c-9e73-6a3eeb134b51.metadata.json - --region ap-northeast-1 \
  | python3 -c 'import json,sys; d=json.load(sys.stdin); print(json.dumps({"format-version":d["format-version"],"current-snapshot-id":d.get("current-snapshot-id"),"snapshots":len(d.get("snapshots",[])),"properties":d.get("properties")},indent=2))'
{
  "format-version": 2,
  "current-snapshot-id": 16835891855951713,
  "snapshots": 1,
  "properties": {
    "write.format.default": "parquet",
    "write.parquet.compression-codec": "zstd"
  }
}

CREATE MATERIALIZED VIEW iceberg_mv_blog.daily_sales
USING ICEBERG
LOCATION 's3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/daily_sales/'
PARTITIONED BY (day(order_date))
AS
SELECT order_date,
       SUM(amount) AS total_amount,
       COUNT(*)    AS order_count
FROM iceberg_mv_blog.orders
GROUP BY order_date;

CREATE MATERIALIZED VIEW iceberg_mv_blog.region_sales
USING ICEBERG
LOCATION 's3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/region_sales/'
AS
SELECT c.region,
       SUM(o.amount) AS total_amount,
       COUNT(*)      AS order_count
FROM iceberg_mv_blog.orders o
JOIN iceberg_mv_blog.customers c
  ON o.customer_id = c.customer_id
GROUP BY c.region;

CREATE MATERIALIZED VIEW iceberg_mv_blog.region_avg
USING ICEBERG
LOCATION 's3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/region_avg/'
AS
SELECT c.region,
       AVG(o.amount) AS avg_amount
FROM iceberg_mv_blog.orders o
JOIN iceberg_mv_blog.customers c
  ON o.customer_id = c.customer_id
GROUP BY c.region;

MV iceberg_mv_blog.region_avg is not eligible for incremental refresh: Iceberg materialized views only support COUNT and SUM aggregates for incremental refresh. Creating the MV without support for incremental refresh.

SHOW TABLES FROM SCHEMA dev.iceberg_mv_blog;

SELECT * FROM iceberg_mv_blog.region_sales ORDER BY region;

SELECT * FROM iceberg_mv_blog.daily_sales ORDER BY order_date;

SELECT * FROM iceberg_mv_blog.region_avg ORDER BY region;

% aws s3 ls s3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/ --recursive --region ap-northeast-1
2026-10-19 09:27:39       1919 mv/daily_sales/data/order_date=2026-10-01/4c66c9/20261019_6058_bcf2b8c6-6724-4b63-b535-e1007c6c50ca_64.parquet
2026-10-19 09:27:39       1919 mv/daily_sales/data/order_date=2026-10-02/49aa53/20261019_6058_4d2d4a09-005a-48a1-ae2f-e2ab85f79c0f_66.parquet
2026-10-19 09:27:39       1919 mv/daily_sales/data/order_date=2026-10-03/e5d859/20261019_6058_81088fec-e8d1-4c8e-9f3e-c3d6fe33cb48_0.parquet
2026-10-19 09:27:40       1699 mv/daily_sales/metadata/00001-bd460bc4-f105-4fd0-9fc6-c9e38b0a9270.metadata.json
2026-10-19 09:27:39       4107 mv/daily_sales/metadata/20261019_6058_0f7b32f7-5930-4d9e-b319-9e7d1e0afb8f_0.manifest.avro
2026-10-19 09:27:39       4108 mv/daily_sales/metadata/20261019_6058_99fc8a44-b21f-49c6-8d66-9394cea7abaf_66.manifest.avro
2026-10-19 09:27:39       4108 mv/daily_sales/metadata/20261019_6058_b663843b-f2c7-4784-bb3a-961623f7d2ec_64.manifest.avro
2026-10-19 09:27:40       2553 mv/daily_sales/metadata/snap-5280038748078563914-1-0f865d10-e5dd-4e76-947f-a2a498597c0b.avro
2026-10-19 09:27:47        740 mv/region_avg/data/6e0d3f/20261019_6158_9fe949a5-3357-49ca-89cf-a29d3bccc5d8_0.parquet
2026-10-19 09:27:47        740 mv/region_avg/data/713352/20261019_6158_2b9b7862-ecab-4bc0-8228-0bb1a48b7b20_66.parquet
2026-10-19 09:27:47        740 mv/region_avg/data/e2ad2a/20261019_6158_97c1d275-08b1-4e82-9680-8b5050f11f19_64.parquet
2026-10-19 09:27:47       1411 mv/region_avg/metadata/00001-ca0a0fef-7884-42d0-a64f-cf08af40725d.metadata.json
2026-10-19 09:27:47       3640 mv/region_avg/metadata/20261019_6158_236b3761-56bb-4bfe-95aa-1d3ccd040d8b_0.manifest.avro
2026-10-19 09:27:47       3641 mv/region_avg/metadata/20261019_6158_56129cf1-83fd-4587-9fec-904e3ce22d02_66.manifest.avro
2026-10-19 09:27:47       3641 mv/region_avg/metadata/20261019_6158_b896a9f6-d7f2-42d2-9a32-ad51fb726594_64.manifest.avro
2026-10-19 09:27:47       2499 mv/region_avg/metadata/snap-4033410673910401238-1-220dc0a3-3a53-495e-9104-f187be4c9d33.avro
2026-10-19 09:27:43       1859 mv/region_sales/data/39f810/20261019_6145_82411241-bb7e-4e62-aeb8-1b0fb9fb996f_64.parquet
2026-10-19 09:27:43       1859 mv/region_sales/data/70a995/20261019_6145_e7a1f734-743c-4b55-8582-b5da37ebd97f_66.parquet
2026-10-19 09:27:43       1859 mv/region_sales/data/c47b5f/20261019_6145_cef3b708-b594-479a-9e07-87d5cc84e934_0.parquet
2026-10-19 09:27:44       1625 mv/region_sales/metadata/00001-fd47ad1a-25cf-43b6-9abf-4103245c98df.metadata.json
2026-10-19 09:27:44       3925 mv/region_sales/metadata/20261019_6145_0c2fa387-ba7e-4678-9bdb-30b80f5c7f90_64.manifest.avro
2026-10-19 09:27:44       3925 mv/region_sales/metadata/20261019_6145_2b409ab8-5171-4535-b2db-a4ae55fd4a5d_66.manifest.avro
2026-10-19 09:27:44       3924 mv/region_sales/metadata/20261019_6145_962e8747-c7bf-49d8-80d5-2dc911d262b8_0.manifest.avro
2026-10-19 09:27:44       2505 mv/region_sales/metadata/snap-3142918037762759303-1-39539373-690c-4d26-a0be-d726f56d0700.avro

% M=$(aws glue get-table --database-name iceberg_mv_blog --name daily_sales --region ap-northeast-1 --query 'Table.Parameters.metadata_location' --output text)
% aws s3 cp $M - --region ap-northeast-1 \
  | python3 -c 'import json,sys; d=json.load(sys.stdin); print(json.dumps({"format-version":d["format-version"],"partition-specs":d["partition-specs"],"properties":d.get("properties"),"snapshots":[{"snapshot-id":s["snapshot-id"],"summary":s["summary"]} for s in d.get("snapshots",[])]},indent=2,ensure_ascii=False))'
{
  "format-version": 2,
  "partition-specs": [
    {
      "spec-id": 0,
      "fields": [
        {
          "name": "day_order_date",
          "transform": "day",
          "source-id": 1,
          "field-id": 1000
        }
      ]
    }
  ],
  "properties": {
    "write.format.default": "parquet",
    "write.parquet.compression-codec": "zstd"
  },
  "snapshots": [
    {
      "snapshot-id": 5280038748078563914,
      "summary": {
        "operation": "append",
        "added-data-files": "3",
        "added-records": "3",
        "total-records": "3",
        "total-data-files": "3",
        "total-delete-files": "0",
        "total-position-deletes": "0",
        "total-equality-deletes": "0"
      }
    }
  ]
}

SELECT * FROM region_sales ORDER BY region;

INSERT INTO iceberg_mv_blog.orders VALUES
    (7, 3, '2026-10-03',  90.00),
    (8, 4, '2026-10-04',  50.00);

REFRESH MATERIALIZED VIEW iceberg_mv_blog.daily_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_avg;

Materialized view daily_sales was fully refreshed. Future refreshes may be incremental. Reason: Delta threshold exceeded for arn:aws:glue:ap-northeast-1:123456789012:table/iceberg_mv_blog/orders.
Materialized view region_sales was fully refreshed. Future refreshes may be incremental. Reason: Delta threshold exceeded for arn:aws:glue:ap-northeast-1:123456789012:table/iceberg_mv_blog/orders.
Materialized view region_avg was recomputed.

SELECT mv_name, TRIM(status) AS status, refresh_type, start_time, duration
FROM sys_mv_refresh_history
WHERE schema_name = 'iceberg_mv_blog'
ORDER BY start_time;

-- 1,000 行を追加
INSERT INTO iceberg_mv_blog.orders
SELECT
    1000 + a.n * 100 + b.n * 10 + c.n                        AS order_id,
    (a.n + b.n + c.n) % 4 + 1                                AS customer_id,
    DATEADD(day, (a.n + b.n + c.n) % 5, '2026-10-01')::date AS order_date,
    10.00::decimal(10, 2)                                    AS amount
FROM (
    SELECT 0 AS n UNION ALL
    SELECT 1      UNION ALL
    SELECT 2      UNION ALL
    SELECT 3      UNION ALL
    SELECT 4      UNION ALL
    SELECT 5      UNION ALL
    SELECT 6      UNION ALL
    SELECT 7      UNION ALL
    SELECT 8      UNION ALL
    SELECT 9
) a
CROSS JOIN (
    SELECT 0 AS n UNION ALL
    SELECT 1      UNION ALL
    SELECT 2      UNION ALL
    SELECT 3      UNION ALL
    SELECT 4      UNION ALL
    SELECT 5      UNION ALL
    SELECT 6      UNION ALL
    SELECT 7      UNION ALL
    SELECT 8      UNION ALL
    SELECT 9
) b
CROSS JOIN (
    SELECT 0 AS n UNION ALL
    SELECT 1      UNION ALL
    SELECT 2      UNION ALL
    SELECT 3      UNION ALL
    SELECT 4      UNION ALL
    SELECT 5      UNION ALL
    SELECT 6      UNION ALL
    SELECT 7      UNION ALL
    SELECT 8      UNION ALL
    SELECT 9
) c;

REFRESH MATERIALIZED VIEW iceberg_mv_blog.daily_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_avg;

Materialized view daily_sales was fully refreshed. Future refreshes may be incremental. Reason: Delta threshold exceeded for arn:aws:glue:ap-northeast-1:123456789012:table/iceberg_mv_blog/orders.
Materialized view region_sales was fully refreshed. Future refreshes may be incremental. Reason: Delta threshold exceeded for arn:aws:glue:ap-northeast-1:123456789012:table/iceberg_mv_blog/orders.
Materialized view region_avg was recomputed.

-- 1 行だけ追加
INSERT INTO iceberg_mv_blog.orders VALUES (9, 3, '2026-10-04', 500.00);

REFRESH MATERIALIZED VIEW iceberg_mv_blog.daily_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_avg;

Materialized view daily_sales was incrementally updated.
Materialized view region_sales was incrementally updated.
Materialized view region_avg was recomputed.

SELECT TRIM(mv_name) AS mv_name, TRIM(status) AS status, TRIM(refresh_type) AS refresh_type, start_time
FROM sys_mv_refresh_history
WHERE schema_name = 'iceberg_mv_blog'
ORDER BY start_time;

DELETE FROM iceberg_mv_blog.orders WHERE order_id = 9;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.daily_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_sales;

UPDATE iceberg_mv_blog.orders SET amount = 120.00 WHERE order_id = 1;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.daily_sales;
REFRESH MATERIALIZED VIEW iceberg_mv_blog.region_sales;

SELECT TRIM(mv_name) AS mv_name, TRIM(status) AS status, start_time
FROM sys_mv_refresh_history
WHERE schema_name = 'iceberg_mv_blog' AND start_time > '2026-10-19 00:36:00'
ORDER BY start_time;

SELECT m.region,
       m.total_amount AS mv_total, s.total_amount AS src_total,
       m.order_count  AS mv_cnt,   s.order_count  AS src_cnt
FROM iceberg_mv_blog.region_sales m
FULL JOIN (
    SELECT c.region, SUM(o.amount) AS total_amount, COUNT(*) AS order_count
    FROM iceberg_mv_blog.orders o
    JOIN iceberg_mv_blog.customers c ON o.customer_id = c.customer_id
    GROUP BY c.region
) s ON m.region = s.region
ORDER BY 1;

SELECT COALESCE(m.order_date, s.order_date) AS order_date,
       m.total_amount AS mv_total, s.total_amount AS src_total,
       m.order_count  AS mv_cnt,   s.order_count  AS src_cnt
FROM iceberg_mv_blog.daily_sales m
FULL JOIN (
    SELECT order_date, SUM(amount) AS total_amount, COUNT(*) AS order_count
    FROM iceberg_mv_blog.orders
    GROUP BY order_date
) s ON m.order_date = s.order_date
ORDER BY 1;

SELECT m.region, m.avg_amount AS mv_avg, s.avg_amount AS src_avg
FROM iceberg_mv_blog.region_avg m
FULL JOIN (
    SELECT c.region, AVG(o.amount) AS avg_amount
    FROM iceberg_mv_blog.orders o
    JOIN iceberg_mv_blog.customers c ON o.customer_id = c.customer_id
    GROUP BY c.region
) s ON m.region = s.region
ORDER BY 1;

aws glue get-table --database-name iceberg_mv_blog --name region_sales --region ap-northeast-1 \
  --query 'Table.{UpdateTime:UpdateTime,LastRefreshType:ViewDefinition.LastRefreshType,ViewVersionId:ViewDefinition.ViewVersionId,SubObjectVersionIds:ViewDefinition.SubObjectVersionIds,mv_sub_object_versions:Parameters.mv_sub_object_versions}' \
  --output json

{
    "UpdateTime": "2026-10-19T09:35:25+09:00",
    "LastRefreshType": "INCREMENTAL",
    "ViewVersionId": 6628680760292774923,
    "SubObjectVersionIds": [
        1669592421218426949,
        2956492144512067407
    ],
    "mv_sub_object_versions": "16835891855951713,1669592421218426949"
}

SELECT committed_at, snapshot_id, operation FROM "region_sales$snapshots" ORDER BY committed_at;

SELECT committed_at, snapshot_id, operation,
       summary['added-records']          AS added_records,
       summary['added-position-deletes'] AS added_pos_deletes
FROM "region_sales$snapshots"
ORDER BY committed_at;

DROP MATERIALIZED VIEW iceberg_mv_blog.region_avg;

% aws glue get-table --database-name iceberg_mv_blog --name region_avg --region ap-northeast-1 --query 'Table.Name' --output text
aws: [ERROR]: An error occurred (EntityNotFoundException) when calling the GetTable operation: Entity Not Found

% aws s3 ls s3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/region_avg/ --recursive --summarize --region ap-northeast-1 | tail -3

Total Objects: 32
   Total Size: 74049

ERROR: Cannot create Iceberg table: S3 location "s3://redshift-iceberg-mv-123456789012-ap-northeast-1/mv/region_avg" contains existing objects
  Hint: Please provide an empty S3 location or specify a different path

ERROR: External Catalog Error: Detail: ----------------------------------------------- error: External Catalog Error: code: 16000 context: Invalid column type for column $ivm_path_hash. query: 1796[child_sequence:1] location: s3_request_builder.cpp:3610 process: padbmaster [pid=1073824039] -----------------------------------------------