Snowflake App RuntimeでElementaryのレポートをDockerなしでホスティングしてみた

https://<id>-<org>-<account>-<region>.snowflakecomputing.app/

ALTER APPLICATION SERVICE
  SAGARA_SNOWFLAKE_APPS.PUBLIC.ELEMENTARY_REPORT_APP
SET
  AUTO_RESUME = TRUE,
  AUTO_SUSPEND_SECS = 900;

snow --version

snow app setup --help

snow connection test

snow connection test --connection <connection_name>

node --version

dbt_project/
├── dbt_project.yml
├── models/
├── edr_target/
│   ├── elementary_report.html
│   └── elementary_output.json

mkdir -p elementary-report-app/public

cp edr_target/elementary_report.html elementary-report-app/public/index.html
cp edr_target/elementary_output.json elementary-report-app/public/elementary_output.json

cd elementary-report-app

elementary-report-app/
├── app.yml
├── package.json
├── server.mjs
├── snowflake.yml        # snow app setup で生成
└── public/
    ├── index.html
    └── elementary_output.json

// server.mjs
import http from "node:http";
import { readFile, stat, readdir } from "node:fs/promises";
import path from "node:path";
import { fileURLToPath } from "node:url";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// App Runtime の実行パッケージ root をドキュメントルートにする
const documentRoot = __dirname;

const port = Number(process.env.PORT || process.env.SERVER_PORT || 8080);
const host = "0.0.0.0";

const mimeTypes = {
  ".html": "text/html; charset=utf-8",
  ".json": "application/json; charset=utf-8",
  ".js": "text/javascript; charset=utf-8",
  ".css": "text/css; charset=utf-8",
  ".svg": "image/svg+xml",
  ".png": "image/png",
  ".jpg": "image/jpeg",
  ".jpeg": "image/jpeg",
  ".ico": "image/x-icon",
  ".map": "application/json; charset=utf-8",
  ".txt": "text/plain; charset=utf-8",
};

async function listFilesRecursive(dir, baseDir = dir) {
  const entries = await readdir(dir, { withFileTypes: true });
  const files = [];

  for (const entry of entries) {
    const fullPath = path.join(dir, entry.name);
    const relativePath = path.relative(baseDir, fullPath);

    if (entry.isDirectory()) {
      const childFiles = await listFilesRecursive(fullPath, baseDir);
      files.push(...childFiles);
    } else {
      files.push(relativePath);
    }
  }

  return files;
}

function safeResolve(urlPath) {
  const decodedPath = decodeURIComponent(urlPath.split("?")[0]);

  // ルートアクセス時は index.html を返す
  const normalizedPath = decodedPath === "/" ? "/index.html" : decodedPath;

  const filePath = path.normalize(path.join(documentRoot, normalizedPath));

  // ディレクトリトラバーサル対策
  if (!filePath.startsWith(documentRoot)) {
    return null;
  }

  return filePath;
}

const server = http.createServer(async (req, res) => {
  try {
    if (!req.url) {
      res.writeHead(400, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Bad Request");
      return;
    }

    console.log(JSON.stringify({
      message: "request",
      url: req.url,
      cwd: process.cwd(),
      dirname: __dirname,
      documentRoot,
    }));

    if (req.url === "/healthz") {
      res.writeHead(200, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("ok");
      return;
    }

    // デバッグ用: 実行環境内に存在するファイル一覧を確認する
    if (req.url === "/__debug/files") {
      const files = await listFilesRecursive(documentRoot);

      res.writeHead(200, {
        "content-type": "application/json; charset=utf-8",
      });
      res.end(JSON.stringify({
        cwd: process.cwd(),
        dirname: __dirname,
        documentRoot,
        files,
      }, null, 2));
      return;
    }

    const filePath = safeResolve(req.url);

    console.log(JSON.stringify({
      message: "resolved file path",
      url: req.url,
      filePath,
    }));

    if (!filePath) {
      res.writeHead(403, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Forbidden");
      return;
    }

    const fileStat = await stat(filePath);

    if (!fileStat.isFile()) {
      res.writeHead(404, {
        "content-type": "text/plain; charset=utf-8",
      });
      res.end("Not Found");
      return;
    }

    const body = await readFile(filePath);
    const ext = path.extname(filePath).toLowerCase();
    const contentType = mimeTypes[ext] || "application/octet-stream";

    res.writeHead(200, {
      "content-type": contentType,
      "cache-control": "no-store",
    });
    res.end(body);
  } catch (err) {
    console.error(JSON.stringify({
      message: "request failed",
      url: req.url,
      error: err instanceof Error ? err.message : String(err),
    }));

    res.writeHead(404, {
      "content-type": "text/plain; charset=utf-8",
    });
    res.end("Not Found");
  }
});

server.listen(port, host, () => {
  console.log(JSON.stringify({
    message: "Elementary report server started",
    url: `http://${host}:${port}`,
    cwd: process.cwd(),
    dirname: __dirname,
    documentRoot,
  }));
});

{
  "name": "elementary-report-app",
  "version": "1.0.0",
  "private": true,
  "type": "module",
  "engines": {
    "node": ">=22"
  },
  "scripts": {
    "start": "node server.mjs"
  }
}

run:
  command: [node, server.mjs]

artifacts:
  - src: "server.mjs"
    dest: "."
  - src: "package.json"
    dest: "."
  - src: "public/index.html"
    dest: "."
  - src: "public/elementary_output.json"
    dest: "."

profile:
  label: "Elementary Report"
  description: "Static hosting for Elementary report on Snowflake App Runtime"

npm start

curl http://localhost:8080/healthz

ok

USE ROLE ACCOUNTADMIN;

CREATE DATABASE IF NOT EXISTS SAGARA_SNOWFLAKE_APPS;

CREATE WAREHOUSE IF NOT EXISTS SAGARA_SNOWFLAKE_APPS_QUERY_WH
  WAREHOUSE_SIZE = XSMALL
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE SCHEMA IF NOT EXISTS SAGARA_SNOWFLAKE_APPS.PUBLIC;

USE ROLE ACCOUNTADMIN;

GRANT USAGE ON WAREHOUSE SAGARA_SNOWFLAKE_APPS_QUERY_WH TO ROLE SYSADMIN;

GRANT USAGE ON DATABASE SAGARA_SNOWFLAKE_APPS TO ROLE SYSADMIN;
GRANT USAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;

GRANT CREATE APPLICATION SERVICE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;
GRANT CREATE STAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE SYSADMIN;

snow app setup \
  --app-name elementary_report_app \
  --database SAGARA_SNOWFLAKE_APPS \
  --schema PUBLIC \
  --warehouse SAGARA_SNOWFLAKE_APPS_QUERY_WH

snow app setup \
  --connection <connection_name> \
  --app-name elementary_report_app \
  --database SAGARA_SNOWFLAKE_APPS \
  --schema PUBLIC \
  --warehouse SAGARA_SNOWFLAKE_APPS_QUERY_WH

definition_version: "2"

entities:
  elementary_report_app:
    type: snowflake-app
    identifier:
      name: ELEMENTARY_REPORT_APP
      database: SAGARA_SNOWFLAKE_APPS
      schema: PUBLIC
    artifacts:
      - src: ./*
        dest: ./
        ignore:
          - node_modules
          - .env*
          - __pycache__
          - "*.pyc"
          - .next
          - .git
          - snowflake.log

    query_warehouse: SAGARA_SNOWFLAKE_APPS_QUERY_WH
    code_stage: ELEMENTARY_REPORT_APP_CODE

snow app deploy

snow app deploy --connection <connection_name>

snow app open

snow app open --connection <connection_name>

snow app open --print-only

USE ROLE ACCOUNTADMIN;

GRANT USAGE ON DATABASE SAGARA_SNOWFLAKE_APPS TO ROLE ANALYST;

GRANT USAGE ON SCHEMA SAGARA_SNOWFLAKE_APPS.PUBLIC TO ROLE ANALYST;

GRANT USAGE ON APPLICATION SERVICE
  SAGARA_SNOWFLAKE_APPS.PUBLIC.ELEMENTARY_REPORT_APP
  TO ROLE ANALYST;

USE DATABASE SAGARA_SNOWFLAKE_APPS;
USE SCHEMA PUBLIC;

SHOW APPLICATION SERVICES;

SHOW APPLICATION SERVICES LIKE 'ELEMENTARY_REPORT_APP';

cp edr_target/elementary_report.html elementary-report-app/public/index.html
cp edr_target/elementary_output.json elementary-report-app/public/elementary_output.json

cd elementary-report-app

snow app deploy

cp ../edr_target/elementary_report.html public/index.html
cp ../edr_target/elementary_output.json public/elementary_output.json

snow app deploy

snow app teardown --connection <connection_name>

snow app teardown --connection <connection_name> --force

cd elementary-report-app

snow app teardown --connection <connection_name>

USE DATABASE SAGARA_SNOWFLAKE_APPS;
USE SCHEMA PUBLIC;

SHOW APPLICATION SERVICES LIKE 'ELEMENTARY_REPORT_APP';

USE ROLE ACCOUNTADMIN;

DROP WAREHOUSE IF EXISTS SAGARA_SNOWFLAKE_APPS_QUERY_WH;
DROP DATABASE IF EXISTS SAGARA_SNOWFLAKE_APPS;

snow app deploy --connection <connection_name>

Uploaded public/elementary_output.json -> ...
Uploaded public/index.html -> ...

artifacts:
  - src: "public/**"
    dest: "public"

artifacts:
  - src: "server.mjs"
    dest: "."
  - src: "package.json"
    dest: "."
  - src: "public/index.html"
    dest: "."
  - src: "public/elementary_output.json"
    dest: "."

const documentRoot = __dirname;

/
├── server.mjs
├── package.json
├── index.html
└── elementary_output.json