简介面向医疗信息标准化工程师、术语服务开发者及健康数据研究人员这份资源提供了一套完整的SNOMED CT数据库脚本用于将RF2分发格式的术语版本创建并填充到MySQL、PostgreSQL、MSSQL或Neo4j等关系或图数据库中解决从原始发布包到可查询术语库的转换与加载难题。压缩包共115个文件整体约434KB其中64个sql脚本覆盖建表、索引与数据装载py、sh、bat脚本承担自动化处理与批量导入cfg、ini、cnf等配置模板则便于调整数据库连接与路径参数。目前已有1103人学习浏览。资源按数据库平台分子目录组织包含MySQL运行与加载脚本、Neo4j图库更新失败检查脚本、bcp命令行格式文件及配置文件示例读者可据此快速搭建SNOMED CT术语环境也能参考脚本结构进行二次开发或迁移到其他数据库平台。对于需要将SNOMED CT术语库嵌入业务系统或开展术语映射工作的团队这些脚本同样提供了可直接复用的基础框架。1. SNOMED CT 进关系数据库先把它当“图”再决定怎么拆成“表”某医院信息科的A同学接到需求把SNOMED CT术语库接进HIS做诊断编码推荐。他解压官方发布包后直接当成“字典”导进业务库结果几十万条术语互相引用光是“找某个概念的所有子类”这一种查询就跑到超时。问题不在数据量而在SNOMED CT本质上是一张巨大的语义图概念与概念通过is-a和几十种属性关系相连描述与概念之间又是一对多。要在关系数据库中表示SNOMED CT不是建一张字典表能解决的必须把这张图拆成少数几张结构清晰的表再为“树状遍历”补上物化索引。下面这条路径就是从RF2发布文件开始到建表、灌数、做闭包表、排错完整落进PostgreSQL的过程。2. RF2 先读懂再建库概念、描述、关系三张表怎么拆2.1 RF2三驾马车的职责边界SNOMED CT的官方发布格式叫RF2Release Format 2。解压后你会看到一堆以tab分隔的文本文件每个文件第一行是列名后面每一行是一条记录。RF2的核心术语文件按组件分三类概念文件、描述文件、关系文件外加一个同构的Stated Relationship文件它们的职责完全不同。概念Concept是骨架。每个概念有唯一的SCTID例如“糖尿病”是一个概念“2型糖尿病”是另一个概念二者不能合并。概念文件里每一行只回答“这个概念在某个生效时间点上是有效还是废弃、定义得充分不充分”不携带任何名称。把概念表当成字典表去展示文本是第一个常见的建模错误。描述Description是概念的人类可读文字。一个概念对应多条描述包括Fully Specified Name全称、同义词以及不同语言Code下的名称。描述表里最关键的列是concept_id、Term、Language Code和Type Id。数据导入后但凡忘记按概念分组取首选描述查询出来的就是一个概念拖着几十条描述的“脏”结果。关系Relationship是概念之间的边。每条关系有source_id和destination_id两个端点type_id表示关系类型is-a对应的type_id是116680003这个值建议在建模第一步就固化成常量变量。relationship_group表示该属性关系属于第几组characteristic_type_id区分定义性关系与推断性关系。Stated Relationship文件与关系文件几乎同构维护的是逻辑定义用的“声明式”关系临床查询通常看inferred侧。2.2 版本化字段怎么参与主键一张表还是两张表RF2每个文件里都有三列通用字段effective_time、active、module_id。effective_time表示这个版本从哪一天开始生效active表示该版本当前是否有效module_id表示记录属于核心模块还是某个扩展模块。要正确落库这三列一个都不能丢因为它们参与“一个SCTID到底代表哪一版概念”的判定。主键设计取决于你打算存多少历史。只存当前快照概念表主键直接用id即可但如果每天用增量包刷新旧行可能被新行替代同一个SCTID会在不同effective_time下出现多行。我一般会把基表主键设计成(id, effective_time)组合键再用一个视图专门取每个id的最新版本。这样一条路径同时支撑“当前查询”和“历史版本回溯”索引会大一点但对诊断编码这类需要追踪当时语义的数据而言非常值得。描述表和关系表同理描述表用(description_id, effective_time)关系表用(relationship_id, effective_time)。关系表还有一个天然的重复来源同一个source、destination、type、group完全相同的两条记录只要characteristic_type_id不同就会同时存在。所以关系表的主键只能落在物理行的id上不能“去重”后把(source, destination, type)当主键否则一进增量就炸。2.3 最小DDLPostgreSQL建表语句与约束细节下面这套DDL是我在PostgreSQL里最常用的最小结构。为了节省篇幅只放了概念、描述、关系三张核心表。CREATE TABLE snomed_concept ( id BIGINT NOT NULL, effective_time CHAR(8) NOT NULL, -- YYYYMMDD active BOOLEAN NOT NULL, module_id BIGINT NOT NULL, definition_status_id BIGINT NOT NULL, -- 充分定义 / 必要定义 PRIMARY KEY (id, effective_time) ); CREATE TABLE snomed_description ( id BIGINT NOT NULL, effective_time CHAR(8) NOT NULL, active BOOLEAN NOT NULL, module_id BIGINT NOT NULL, concept_id BIGINT NOT NULL, -- 指向概念 language_code TEXT NOT NULL, -- 如 en、en-us、zh type_id BIGINT NOT NULL, -- FSN / 同义词 term TEXT NOT NULL, case_significance_id BIGINT NOT NULL, PRIMARY KEY (id, effective_time) ); CREATE TABLE snomed_relationship ( id BIGINT NOT NULL, effective_time CHAR(8) NOT NULL, active BOOLEAN NOT NULL, module_id BIGINT NOT NULL, source_id BIGINT NOT NULL, destination_id BIGINT NOT NULL, relationship_group INT NOT NULL DEFAULT 0, type_id BIGINT NOT NULL, characteristic_type_id BIGINT NOT NULL, modifier_id BIGINT NOT NULL, PRIMARY KEY (id, effective_time) );这里我没有加物理外键只保留普通索引。原因是在导入Full历史包时某条关系引用的概念可能只存在于更早的版本里物理外键会让导入直接失败。对外键有洁癖的团队可以在“当前快照视图”建好之后单独做校验而不是建表时就锁死。列类型上SCTID最大是18位数字BIGINT足够effective_time用CHAR(8)而非DATE便于和发布包文件名里的日期直接比对。3. 打开官方包灌进PostgreSQL全量装载与增量刷新3.1 三种发布文件Snapshot、Delta与Full的用途差异RF2发布包打开后会有以版本日期命名的目录术语文件按模块和发布类型拆成多种形式。最需要分清三种发布粒度Snapshot快照、Delta增量、Full全历史。Snapshot是截至某日期的全量现行状态里面同时包含active1和active0的记录适合第一次灌库。Delta是从上一个发布日期到当前日期的变化记录active1表示新增或更新active0表示停用适合把已跑起来的库推进到新版本。Full是自发布以来所有历史的集合同一id会出现多行不同effective_time只有做历史回溯时才用它。很多团队第一次导入就把Full误当成快照结果概念表主键被重复id撑爆。我一般直接用最新一期的Snapshot做初始库主键天然唯一等业务稳定后再考虑进入Delta流程。如果是国际版核心发布包目录里通常还带Refset和Cross Map文件比如到ICD的映射表这些不是三张核心表但往往是业务真正用起来的入口。3.2 全量导入最小命令\copy的核心参数与导入顺序导入顺序必须按引用方向从无到有先概念再描述最后关系。别看官方文件体积大用PostgreSQL的\copy走文本流非常快几十万行的核心表十几秒就能完成。下面是我常用的命令。# 用 psql 的 \copy 把快照文件导入三张基表 # \copy 在客户端执行文件路径是本地路径COPY 在服务端执行路径要落在数据库服务器上 psql $CONN_STR SQL TRUNCATE snomed_concept, snomed_description, snomed_relationship; \copy snomed_concept FROM sct2_Concept_Snapshot_INT_20250131.txt WITH (FORMAT csv, DELIMITER E\t, NULL ); \copy snomed_description FROM sct2_Description_Snapshot_INT_20250131.txt WITH (FORMAT csv, DELIMITER E\t, NULL ); \copy snomed_relationship FROM sct2_Relationship_Snapshot_INT_20250131.txt WITH (FORMAT csv, DELIMITER E\t, NULL ); SQL三个文件我都加了FORMAT csv和DELIMITER E\t。RF2文件本身是制表符分隔文本直接按csv读完全没问题。列名在第一行\copy会把它当成数据所以要先处理掉表头空字段在部分历史包里会出现NULL 可以让它正确落成NULL而不是空字符串。提示\copy与COPY的性能差异不大区别只在读取文件的位置。客户端拿不到数据库服务器文件系统权限时用\copy是最省事的选择。TRUNCATE和\copy在同一个会话里顺序执行表上没有外键是前提这也是前面不做物理外键的好处灌数据时可以随时清空重建。3.3 Delta增量两张策略选哪种提前定增量刷新最朴素且最不容易出错的策略是“以Delta为准把库里的旧版本直接替换”。具体做法是先把Delta文件导到一张临时表再对三张基表分别执行“删除同id所有旧行、再插入delta行”。-- 以概念表为例description、relationship 同理 BEGIN; DELETE FROM snomed_concept WHERE id IN (SELECT DISTINCT id FROM concept_delta); INSERT INTO snomed_concept SELECT * FROM concept_delta; COMMIT;这个方案牺牲了历史版本换来的是主键永远是当前最新状态查询和关系表join时只需过滤active1不需要每次取最新。如果业务要求保留历史就把DELETE换成“按id取最大effective_time将旧行置为active0”再把新行insert进去相当于用时间戳标记废弃版本。两个策略都要求Delta文件必须与基线快照日期衔接否则最终结果会和官方浏览器对不上。跑完合并后我习惯做一次数量核对取概念表的active1行数和该版本发布说明里的有效概念数对比差一条都要查导入链路。4. 让 is-a 层级查询跑得快闭包表生成与索引设计4.1 递归查询为什么在这里“玄学”地慢很多工程师第一次接触SNOMED CT时会觉得“父子关系不就是递归查询吗”。但在概念规模几十万、层级深度十几层到三十几层的模型里每条查询都要把从当前节点出发的整棵子树重新计算一遍。PostgreSQL的递归CTE在中间结果集变大后会明显变慢再加上诊断推荐这类业务往往要在几百个候选概念上反复做“找子类”递归次数就乘上了候选数慢得没有规律。这就是我常说的“玄学”同样的SQL在这个模块快换个模块慢很难提前预测。与其让查询时现算不如在数据入表时就把“谁是祖先、谁是后代、隔了几层”算好存好。这就是关系数据库表示SNOMED CT时最实用的闭包表closure table。4.2 一条SQL生成闭包表从is-a边到全部祖先-后代对闭包表的核心是只保留真正的父子边然后反复连接把任意两个节点之间“可推导出的祖先-后代关系”全部写进一张表。生成前先确认关系表里只取active1、type_id116680003、relationship_group0的行这三层过滤缺一不可。CREATE TABLE snomed_closure ( ancestor_id BIGINT NOT NULL, descendant_id BIGINT NOT NULL, distance INT NOT NULL, PRIMARY KEY (ancestor_id, descendant_id) ); -- 从父子边推导所有传递关系限制最大深度防止极端异常 WITH RECURSIVE isa AS ( SELECT source_id, destination_id FROM snomed_relationship WHERE active TRUE AND type_id 116680003 ), closure AS ( SELECT source_id AS ancestor_id, destination_id AS descendant_id, 1 AS distance FROM isa UNION ALL SELECT c.ancestor_id, i.destination_id, c.distance 1 FROM closure c JOIN isa i ON c.descendant_id i.source_id WHERE c.distance 39 ) INSERT INTO snomed_closure (ancestor_id, descendant_id, distance) SELECT DISTINCT ancestor_id, descendant_id, MIN(distance) FROM closure GROUP BY ancestor_id, descendant_id;递归部分每迭代一次往下走一层distance从1开始限制到39层是因为SNOMED CT的实际层级深度不会超过这个量级同时能挡住脏数据造成的无限循环。最外层用GROUP BY MIN(distance)是因为同一对祖先和后代可能存在多条路径闭包表只需要最短距离。生成一次之后日常的“找所有子类”从递归变成一条主键查询。之后每次发布新版本两条路全量重建或只针对增量涉及的局部节点重建。全量重建成本并不高千万行的闭包表在普通服务器上也就是几分钟所以我建议直接重建。4.3 必建索引清单与EXPLAIN验证方法闭包表建好不代表万事大吉索引要按查询方向配齐。SNOMED CT业务里“向上找祖先”和“向下找子类”都是高频所以两个方向都要有索引。单靠PRIMARY KEY (ancestor_id, descendant_id)只能支持“给一个后代找祖先”反向“给一个祖先找所有后代”会全表扫描。CREATE INDEX idx_closure_descendant ON snomed_closure (descendant_id, ancestor_id); CREATE INDEX idx_closure_distance ON snomed_closure (descendant_id, distance);索引配置思路可以按场景整理成表查询场景推荐索引说明向下找子类(ancestor_id, descendant_id)主键已覆盖无需另建向上找祖先(descendant_id, ancestor_id)反向遍历的关键索引按层级深度过滤descendant_id distance统计叶子数量时有用验证方法很直接随便拿一个已知概念比如“糖尿病”跑一句带EXPLAIN的查询看有没有命中索引而不是Seq Scan。如果命中索引但速度仍不理想再看是不是返回的节点太多这时加LIMIT和分页比继续调索引更有效。5. 关系库落地SNOMED CT的5个高频坑现象、原因与处理5.1 同一个概念查出来好几个“标准名称”现象做完描述表查询后一个concept_id下冒出一大串term完全分不清展示给用户用哪个。原因描述表里同时存在FSN和多个同义词语言Code可能还分en、en-us所有active1的记录都被当成“标准名称”返回了。解决建首选描述视图。查询时按concept_id分组在描述类型里按优先级取一条语言Code上按当前系统语言排序。不要试图在ETL阶段把其他term删掉保留全量描述是为了后续检索和模糊匹配。5.2 Delta增量导入后数据越导越多现象按Delta合并时只做“插入”处理结果概念表出现大量重复ID新老版本同时有效。原因Delta里的active1既表示“新概念”也表示“旧概念的更新版本”。只插入不删除同一id就出现两条当前有效记录。解决合并时先按id把旧记录清理掉再插入delta行。处理关系表时不要只看id要连同characteristic_type_id一起清理因为同一个关系在inferred和stated两侧各有一条记录混在一起就是两倍。5.3 关系方向搞反把父类查成子类现象查“低血糖的病因”时返回了一大堆“血糖”的祖先而不是子类。原因SNOMED CT关系表的语义是source_id指向宿主概念destination_id指向目标概念。is-a的读法是“source is a destination”source是子类、destination是父类方向反掉整棵树都会倒过来。解决闭包表和业务视图里统一约定“ancestor_id为上级descendant_id为下级”生成闭包时确认亲子方向不要靠临时记忆。写查询前先拿“糖尿病”和“2型糖尿病”这对已知关系验证一次方向。5.4 闭包表行数膨胀到上千万查询反而更慢现象闭包表生成后行数巨大跑一次“所有子类”还是慢甚至比递归更慢。原因闭包表把“所有可能路径”都物化了没有做结果集限制。某些顶级概念直接拥有几万后代一次返回几十万行排序和统计被拖垮。解决看查询目标。只显示下一级子类时用父子边界表不用闭包表要统计叶子节点时在查询里加层级过滤或分页。闭包表适合“判断两个节点是否连通”和“统计子类数量”不是所有层级查询都能靠它无脑提速。5.5 把Stated Relationship当Inferred关系用层级和官方对不上现象闭包表生成后某些概念的层级跟官方浏览器看到的不一致多出不少重复祖先。原因关系文件里既有stated又有inferred两套关系。Stated关系是逻辑定义侧的“声明式父类”包含了一些可推断但不直接显示的边官方浏览器的类目层级大多按inferred关系展示。解决生成闭包表时明确筛选inferred侧的特征类型id只保留这一类的边。如果两种关系都要入库就必须在关系表上加视图把“查询用关系”和“定义用关系”分开。6. 把表封装成接口首选词视图、官方核对与迁移图库的时机6.1 一个常用视图取一个概念的首选描述描述表保留全量后“取首选词”就成了高频操作。我习惯把它做成物化视图让业务查询只通过一条join取到展示名。CREATE MATERIALIZED VIEW v_concept_preferred_description AS SELECT DISTINCT ON (d.concept_id) d.concept_id, d.id AS description_id, d.term AS preferred_term, d.language_code, d.type_id FROM snomed_description d WHERE d.active TRUE ORDER BY d.concept_id, CASE WHEN d.language_code en THEN 0 ELSE 1 END, CASE WHEN d.type_id 900000000000013009 THEN 0 ELSE 1 END;DISTINCT ON的作用是按concept_id分组后只保留排序第一的行。ORDER BY里语言Code优先FSN类型其次这样在英文环境优先给FSN做权威名避免把同义词当正名。这个视图依赖一个事实FSN的type_id是900000000000013009如果你用的扩展包改动过类型定义要先把映射表查出来不要硬编码这个值。6.2 两类核对方法层级数和版本维度数据灌完后我至少做两次核对。第一次拿一个中间层概念查它的直接子类个数并与官方浏览器展示的子类树比对确认is-a方向没反、过滤条件没漏。第二次核对版本维度执行select distinct effective_time from snomed_concept结果应该只有一个日期这个日期要和发布包目录名一致。只要有第二个日期混进来说明混了Delta或上一个快照的残留查询结果一定对不上。6.3 什么时候该换图数据库如果业务涉及“找两个概念之间的最短路径”“语义相似度计算”这类需要反复跨多层关系的计算关系库里的闭包表维护成本会变高这时候迁移到图数据库是自然的演进方向。图数据库天然保存节点和边大部分场景不再需要闭包表。我自己的习惯是核心业务先用关系库把术语字典跑通把周边系统集成做扎实等真有图计算需求了再额外导一份到图数据库而不是一开始就把它当主存储。这两年我经手过好几个被“图数据库热潮”带偏的项目最后都靠关系库里的闭包表撑着上线。先明确你要解决的是“集成问题”还是“图计算问题”再决定要不要动迁移。希望帮到你。本文还有配套的精品资源点击获取
