# duckdb

**Repository Path**: hulu_fly/duckdb

## Basic Information

- **Project Name**: duckdb
- **Description**: 已经在线上验证，可用于同步mysql 数据源到 duckdb ，以及duckdb数据库文件管理，查询
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2026-08-23
- **Last Updated**: 2026-09-10

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# duckdb-analysis

基于 **DuckDB + Spring Boot** 的轻量级 OLAP 只读分析服务。从 MySQL 同步数据到 Parquet 文件，Java 端通过 JDBC 在内存模式中 `read_parquet()` 直接分析，适用于大表 `GROUP BY`、复杂函数计算、动态 `WHERE` 与多表 `JOIN` 场景。

## 软件架构

采用 **Parquet 直读架构（v3，最终方案）**：

```
MySQL ──(shell/sync_agent_user_info.sh)──> {data-dir}/{table}.parquet   [固定文件名，原子 mv 替换]
                                                  │
                                  (原子 mv -f，JDBC 读到完整旧版或完整新版)
                                                  ▼
                Java JDBC (:memory:) ── read_parquet('{data-dir}/{table}.parquet')
                (大表 GROUP BY + 动态 WHERE + 多表 JOIN)
```

设计要点：

- **纯 OLAP 只读**：使用 DuckDB 内存模式 `:memory:`，每次查询通过 `read_parquet()` 读取 Parquet 文件，无 `.duckdb` 文件锁 / WAL 开销。
- **物理文件隔离**：同步脚本直接产出最终 Parquet 文件，写入 `.tmp.parquet` 后 `mv -f` 原子替换，Java 端永远不会读到半写的损坏文件。
- **列式裁剪**：Parquet 列存 + ZSTD 压缩 + 行组统计，查询仅扫描所需列与行组，配合动态 `WHERE` 谓词下推。
- **表名安全**：`DuckDbConfig.resolveParquetPath(table)` 仅允许 `[A-Za-z0-9_]+`，在表名层拦截路径穿越 / 注入。

## 目录结构

```
duckdb-analysis/
├── bin/                          # Windows 构建/测试/打包脚本（自动探测 JDK 8，可用 JAVA_HOME 覆盖）
│   ├── build.bat                 # 编译（mvn compile）
│   ├── package.bat               # 编译 + 跳过测试打包（mvn clean package -DskipTests）
│   └── test.bat                  # 运行单元测试（mvn test）
├── shell/                        # 数据同步脚本
│   └── sync_agent_user_info.sh   # MySQL → Parquet 全量/增量同步
├── src/
│   ├── main/
│   │   ├── java/com/example/duckdb/
│   │   │   ├── DuckdbAnalysisApplication.java # 启动类（含 CLI 密码重置分支）
│   │   │   ├── auth/                          # RBAC 认证（AuthService/拦截器/注解/LoginUser/CLI）
│   │   │   ├── config/                        # DuckDb/SQLite/权限树种子/Auth 拦截器注册
│   │   │   ├── controller/                    # 业务控制器（数据源/导出/定时/查询/系统参数）
│   │   │   │   └── system/                    # 系统管理（用户/角色/权限树/审计）
│   │   │   ├── domain/                        # 实体（含 SysUser/SysRole/SysMenu/SysAuditLog）
│   │   │   ├── repository/                    # JdbcTemplate 数据访问（含 Sys* 仓库）
│   │   │   ├── service/                       # 业务服务（含 User/Role/Perm/Audit）
│   │   │   └── task/                          # 定时任务
│   │   └── resources/
│   │       ├── application.yml                # duckdb.file-path / data-dir / sqlite.path
│   │       ├── db/sqlite/schema.sql           # SQLite 建表（业务表 + RBAC 表，幂等）
│   │       └── templates/                     # Thymeleaf（layout 左侧导航 + 各页面 + login）
│   └── test/java/com/example/duckdb/          # JUnit5：RBAC/认证/系统服务/既有业务测试
├── docs/specs/rbac-用户权限管理-spec.md # RBAC 规格说明（权限点清单/设计）
├── doc/设计说明.md               # 架构演进与设计细节
├── Dockerfile                    # 容器化部署
└── pom.xml                       # JDK 8 + Spring Boot 2.7.18（DuckDB JDBC 1.3.2.0）
```

## 环境要求

| 组件 | 版本 | 说明 |
|------|------|------|
| JDK | **1.8** | pom 中 `maven.compiler.source/target=8`，与 Docker 基础镜像 `eclipse-temurin:8-jdk` 一致。 |
| Maven | 3.6+ | 建议与 JDK 8 搭配运行。 |
| DuckDB 镜像 | `duckdb/duckdb:v1.5.5` | 仅历史同步脚本 `docker run` 使用，锁定小版本保证可复现（当前导出已改为进程内执行，不再依赖）。 |
| 数据库 | MySQL | 源数据，通过 DuckDB `mysql` 扩展 ATTACH 读取。 |

> ⚠️ **编译与运行统一使用 JDK 8**。用更高版本 JDK 编译 `target=8` 会产生 bootstrap classpath 告警，且无法保证与运行时行为一致；本机默认 `java`/`mvn` 若不是 JDK 8，请先切换：
> ```powershell
> $env:JAVA_HOME = "C:\Program Files\Java\jdk1.8.0_xxx"
> ```

## 构建与测试

在项目根目录下执行 `bin/` 中的脚本（脚本已自动切换到项目根，优先使用环境变量 `JAVA_HOME`，未设置时自动探测 JDK 8）：

```powershell
# 编译
.\bin\build.bat

# 运行冒烟测试（read_parquet 直读、表名安全校验、只读防护、CSV/Excel 导出；无需 MySQL）
.\bin\test.bat

# 编译并打包（跳过测试）
.\bin\package.bat
```

也可直接使用 Maven（需 JDK 8）：

```powershell
$env:JAVA_HOME = "C:\Program Files\Java\jdk1.8.0_xxx"
mvn clean test
mvn clean package -DskipTests
```

仅跑 RBAC 相关测试（Windows/PowerShell）：

```powershell
mvn "-Dtest=AuthSchemaSeedTest,PasswordServiceTest,AuthServiceTest,SystemRbacServiceTest" -DfailIfNoTests=false test
```

产物：`target/duckdb-analysis-1.0.0.jar`。

## 数据同步

同步脚本将 MySQL 表 `agent_user_info` 导出为 Parquet 文件，供 Java 端分析。

```bash
# 增量同步（默认同步昨天的数据，按 exp_date 去重合并）
bash shell/sync_agent_user_info.sh

# 全量同步
bash shell/sync_agent_user_info.sh --full

# 同步指定日期
bash shell/sync_agent_user_info.sh 2026-01-01
```

**配置项（环境变量）：**

| 变量 | 必填 | 说明 |
|------|------|------|
| `PORLODB_HOST` | 是 | MySQL 主机 |
| `PORLODB_USERNAME` | 是 | MySQL 用户 |
| `PORLODB_PASSWORD` | 是 | MySQL 密码（通过 stdin 传入容器，不出现在 `ps` 进程列表） |
| `DATA_DIR` | 否 | Parquet 产出目录，默认 `./data`（与 `application.yml` 的 `duckdb.data-dir` 对齐）；Docker 部署时覆盖为 `/data/duckdb/data` |

**关键语义：**

- **幂等增量**：每次同步提取当天 `exp_date` 的数据，合并时剔除目标文件中当天的旧数据再 `UNION ALL` 最新增量，重跑同一天不会产生重复。
- **首次运行安全**：`read_parquet()` 在文件不存在时返回空集，自动退化为纯增量写入。
- **原子替换**：先写 `.tmp.parquet`，校验行数 / 大小后再 `mv -f`，Java 端只读完整文件。

## 接口说明

服务端口 `8080`。**除 `/login` 登录页外，所有页面与接口均需登录并持有对应权限**（无登录返回 401、无权限返回 403，JSON 接口统一 `{"code":401|403,...}`）。权限点与菜单/接口的对应关系见「权限点清单」一节。以下 `curl` 示例需先用 `-c/-b` 维持登录 Cookie，或直接在登录后的浏览器控制台访问。

### 1. 预定义预览（需要 `query` 权限）

`GET /api/query/preview?table={表名}&limit={行数}`

返回指定 Parquet 表的前 N 行（`limit` 默认 100，上限 1000）。表名仅允许 `[A-Za-z0-9_]+`。

```bash
curl -b cookies.txt "http://localhost:8080/api/query/preview?table=agent_user_info&limit=10"
```

### 2. 预定义统计（需要 `query` 权限）

`GET /api/query/count?table={表名}`

返回指定表的总行数。

```bash
curl -b cookies.txt "http://localhost:8080/api/query/count?table=agent_info"
```

### 3. SQL 直通查询（需要 `query:sql` 权限）

`POST /api/query/sql`  body: `{"sql": "SELECT ..."}`

直接执行 SQL。安全限制：

- 必须以 `SELECT` 或 `WITH` 开头；
- 仅允许只读查询，不支持多语句（分号）。

```bash
curl -b cookies.txt -X POST "http://localhost:8080/api/query/sql" \
  -H "Content-Type: application/json" \
  -d "{\"sql\":\"SELECT s_name, COUNT(*) FROM read_parquet('data/agent_user_info.parquet') GROUP BY s_name\"}"
```

## 容器化部署

```bash
# 构建镜像（Dockerfile 已配置 data-dir=/data/duckdb/data）
docker build -t duckdb-analysis .

# 运行（挂载数据卷，与同步脚本 DATA_DIR 一致）
docker run -d -p 8080:8080 \
  -v /data/duckdb:/data/duckdb \
  duckdb-analysis
```

同步脚本与 Java 容器需共享同一数据卷：同步脚本传 `DATA_DIR=/data/duckdb/data`，Java 容器 `data-dir=/data/duckdb/data`，挂载 `-v /data/duckdb:/data/duckdb`。

## 运维管理平台（Web 后台）

Spring Boot 单体 + Thymeleaf 的运维管理界面，统一管理「MySQL→Parquet 导出」与「Parquet 查询」。登录后左侧导航按当前账号权限动态显示菜单。

### 业务页面

| 页面 | 路径 | 说明 |
|------|------|------|
| MySQL 数据源 | `/datasource` | 管理 MySQL 连接参数（host/port/user/password/database），密码明文存库、页面掩码 |
| 导出配置 | `/export` | 配置导出 SQL + 关联数据源 + 输出目录/文件名 |
| 定时同步 | `/schedule` | 配置 cron + 关联导出配置，支持立即执行与状态回写 |
| Parquet 目录 | `/parquet-dir` | 管理 Parquet 输出目录，浏览目录内文件与 schema |
| Parquet 查询 | `/query` | 输入 SQL（含 `read_parquet` 绝对路径/通配/多文件关联），DuckDB JDBC 内存模式执行 |
| 系统参数 | `/sys-config` | 平台运行参数（导出并发/内存上限/密码策略/锁定策略） |

### 系统管理页面（需被授权）

| 页面 | 路径 | 说明 |
|------|------|------|
| 用户管理 | `/system/user` | 账号 CRUD、启停、解锁、重置密码、绑定角色 |
| 角色管理 | `/system/role` | 创建角色并勾选权限树（菜单 → 操作叶子） |
| 权限树 | `/system/perm` | 查看/新增/编辑权限点（内置点不可删） |
| 审计日志 | `/system/audit` | 查询登录与关键操作（含变更前后值） |

### 架构要点

- **导出（MySQL→Parquet）进程内执行（跨平台）**：`ScriptExecutorService` 在 Java 进程内开启独立 DuckDB 连接，加载 `mysql` 扩展后 `ATTACH ... (TYPE MYSQL)` + `COPY (查询) TO parquet`，Windows/Linux 同一份代码可跑，不再依赖 bash/Docker。`shell/prod/0_full_sync_data_from_mysql.sh` 仅作历史参考保留。密码与查询 SQL 经单引号转义内联，避免注入。
- **查询复用 DuckDB JDBC**：`/query` 页面调用既有 `/api/query/sql` 接口（内存模式 `read_parquet`），不起 docker，更快。
- **配置库 SQLite**：数据源/导出配置/定时任务/用户/角色/权限点/审计日志存于 `app.sqlite.path`（默认 `./data/duckdb-ops.db`），启动时按 `db/sqlite/schema.sql` 幂等建表并播种权限点。
- **接口鉴权**：所有接口方法标注 `@RequirePerm("permCode")`，由拦截器统一校验（admin 旁路全权限）。

## 用户权限管理（RBAC）

平台采用「账号 → 角色 → 权限点」三层 RBAC。权限点 = 页面/分组菜单（如 `datasource`）+ 页面下的操作叶子（如 `datasource:delete`）。用户登录后持有其所有角色的权限并集，仅能看到被授权菜单、调用被授权接口。

### 首次启动与登录

- 启动时若无任何用户，自动创建内置管理员 **`admin`**，初始密码 **`admin123`**（日志会提示）。
- 首次登录强制修改密码（满足密码策略后放行），随后正常使用平台。

### 使用流程（管理员第一次配置）

1. **系统参数**（可选）：按需调整密码/锁定策略（`/sys-config`）。
2. **权限树**（可选核对）：确认内置页面与操作叶子齐全（`/system/perm`）。
3. **角色管理 → 新增角色 → 分配权限**：勾选该角色可用的菜单与操作（如「Parquet 查询 + 执行 SQL」）。
4. **用户管理 → 新增用户**：填写账号/初始密码，勾选角色；用户首次登录被强制改密。
5. 该用户登录后：左侧导航只显示被授权菜单；越权直接调用接口返回 401/403。

### 密码策略与账号锁定（可配置，默认值）

| 系统参数 key | 含义 | 默认 |
|---|---|---|
| `auth.pwd-min-length` | 密码最小长度 | 8 |
| `auth.pwd-require-letter-digit` | 须同时含字母与数字 | 1（是） |
| `auth.pwd-expire-days` | 密码有效期（天），0 不强制 | 90 |
| `auth.pwd-history-count` | 历史最近 N 次不可重复，0 不限制 | 3 |
| `auth.max-fail-count` | 连续失败锁定阈值 | 5 |
| `auth.lock-minutes` | 锁定时长（分钟） | 30 |
| `auth.session-timeout-minutes` | 会话无操作超时（分钟） | 30 |

- 密码以 BCrypt 存储；锁定在锁定期内拒绝登录，到期自动解锁，也可由管理员在用户管理解锁。
- 定时自动导出等无登录上下文场景以 `system:schedule` 身份记录。

### 权限点清单（内置种子，与页面/URL 语义一致）

| 模块（菜单） | 菜单权限点 | 操作叶子 |
|---|---|---|
| MySQL 数据源 | `datasource` | `datasource:save` / `datasource:delete` / `datasource:test` |
| 导出配置 | `exportConfig` | `exportConfig:save` / `exportConfig:delete` |
| 定时同步 | `schedule` | `schedule:save` / `schedule:delete` / `schedule:run` / `schedule:toggle` |
| Parquet 目录 | `parquetDir` | `parquetDir:save` / `parquetDir:delete` |
| Parquet 查询 | `query` | `query:sql` / `query:export` |
| 系统参数 | `sysConfig` | `sysConfig:save` |
| 系统管理（分组） | `system`（容器） | — |
| ├ 用户管理 | `user` | `user:save` / `user:delete` / `user:resetPwd` / `user:status` |
| ├ 角色管理 | `role` | `role:save` / `role:delete` / `role:assignPerm` |
| ├ 权限树 | `perm` | `perm:save` / `perm:delete` |
| └ 审计日志 | `audit` | —（查看即菜单权限） |

> 说明：新增平台模块 = 开发新增页面与接口（挂对应 `@RequirePerm`）→ 启动播种/权限树登记权限点 → 管理员在角色中授权，左侧导航随之按权限出现。

### 审计日志

- 记录范围：登录/登出、配置类增删改（数据源、导出配置、定时任务、Parquet 目录、系统参数、用户/角色/权限树）、手动执行任务；
- 修改/删除记录**变更前后摘要**（密码等敏感字段掩码为 `******`）；
- 查询入口 `/system/audit`（可按操作人/模块/动作过滤），日志只增不改不删（本期不提供自动清理）。

### 忘记密码 / 账号被锁

- 管理员可登录后在「用户管理」对目标账号**重置密码**或**解锁**。
- 内置 `admin` 忘记密码时，在服务器上以 CLI 方式重置（进程执行后退出，不启动 Web 服务）：

```powershell
# Windows / 通用 jar（PowerShell）
java -jar target\duckdb-analysis-1.0.0.jar --cli.reset-password --cli.username=admin --cli.new-password=Admin@123456

# Linux
java -jar target/duckdb-analysis-1.0.0.jar --cli.reset-password --cli.username=admin --cli.new-password=Admin@123456
```

重置同时清除锁定与强制改密标记并写入审计（操作人 `cli`）。

## 运维注意事项

- **文件膨胀拐点**：MERGE 模式每次需重写整个 Parquet 文件。当单文件超过 **5~10GB** 时，建议按周 / 月分文件（如 `agent_user_info_2026-W34.parquet`），查询时通过 `read_parquet('..._2026-W*.parquet')` 通配。
- **密码特殊字符**：导出连接串中的密码会由 `ScriptExecutorService` 自动对单引号转义，普通特殊字符无需额外处理；仅当密码本身含未闭合引号等极端情形才需留意。
- **表名安全**：所有对外接口均校验表名白名单 `[A-Za-z0-9_]+`，禁止路径穿越与注入。
- **旧数据清理**：若此前产生过重复数据，需先删除旧的 `*.parquet`，再按时间顺序补跑，否则旧重复记录会被保留。
- **升级旧库**：老版本 `duckdb-ops.db` 无权限表，启动时自动幂等补建，业务数据不受影响。
- **内置保护**：`admin` 账号不允许在界面删除/禁用，防止系统失管；忘记密码走上方 CLI 重置。

## 参与贡献

1. Fork 本仓库
2. 新建 `feat_xxx` 分支
3. 提交代码（提交信息使用祈使语气，说明做了什么与为什么）
4. 新建 Pull Request