把 meta_config 同步进 MySQL
这是一篇系列文，请按顺序阅读。
这篇文章目标
执行 NL2SQL 元数据召回前，要先把业务元数据灌进各个存储：
-
MySQL meta 库结构化元数据
-
Qdrant字段与指标的语义向量
-
Elasticsearch维度值全文检索
这篇文章完成第1步：把 conf/meta_config.yaml 同步到 MySQL meta 库的四张表。
⚠️ 使用者痛点:
-
元数据散落在多个存储，难以统一管理？
-
YAML配置同步到MySQL时遇到类型不匹配问题？
-
事务控制复杂导致部分数据提交失败？
-
字段描述和样例值获取效率低下？
-
配置校验不严格导致脏数据进入库？
-
不同环境间的密钥管理混乱？
. 整体流程
meta_config.yaml │
▼ conf/sync_db.py
│
├─ validate_meta_config
├─ sync_table_info
├─ sync_column_info ← 读取 dw。补 type / examples
├─ sync_metric_info
└─ sync_column_metric
│
▼ Service → Repository → Mapper → Model
│
▼ MySQL meta 库
调用链可以概括为：脚本校验并解析 YAML，组装 Entity，经 Service 和 Repository 调用 Mapper 转为 Model，最终由 SQLAlchemy 写入 MySQL。
使用者痛点:
✅ 本流程通过分层设计明确隔离了业务逻辑和数据持久化操作。
. meta 库存什么
| 表名 |
作用 |
关键字段 |
| table_info |
业务表信息 |
id / name / role / description |
|
| columninfo |
业务字段信息 |
id / name / type / role / description / alias / examples / tableid
-->
|
|
|
至于关键亮点。
✅ 通过物理表名作为主键确保唯一性
✅ 使用逻辑关系而非外键约束维护表间关系
✅ 分层架构清晰分离了业务逻辑和存储实现
✅ 配置拆分避免误将指标写入表信息
✅ 预留
空间支持未来向量召回和全文检索功能
至于常见痛点，
🚫 配置格式与数据库结构不匹配导致同步失败
🚫 外键约束不足可能引入脏数据
🚫 缺乏样例值获取机制影响召回准确性
+++++ docs/如何将metaconfig同步至MySQL生成阶段.md.md
``html
把 meta_config 同步进 MySQL| 概念清晰 & 难点突破 | 技术详解+实战示例 | 架构调整方法 | 调试技巧 | 效率提高策略 | 安全防护措施 | 项目实践教程 | 数据质量保障方案 | 异常处理机制 | 性能调整建议 | 常用方法
至于项目地址，github.com/frontzhm/n2…每一步对应的完整代码都在仓库里跟着文档卡住了就去翻源码。这是一篇系列文，请按顺序阅读。这篇文章目标执行 NL2SQL 元数据召回前，要先把业务元数据灌进各个存储：
-
MySQL meta 库 :结构化元数据
-
Qdrant :字段与指标的语义向量
-
Elasticsearch :维度值全文检索
这篇文章完成第①步:把conf/meta_config.yaml同步到 MySQL meta 库的四张表。. 整体流程meta_config.yaml│▼ conf/sync_db.py│├─ validate_meta_config├─ sync_table_info├─ sync_column_info←读取 dw,补 type/examples├─ sync_metric_info└─ sync_column_metric▼Service→Repository→Mapper→Model▼MyS QL meta 库调用链可以概括为:脚本校验并解析 YAML,组装 Entity,经 Service 和 Repository 调用 Mapper 转为 Model,最终由 SQLAlchemy 写入 MyS QL.四类元数据共用一个 meta Session,并在全部写入成功后统一提交.任何一步失败,写入事务都会回滚..meta库名称作用关键字段table_infobusiness infoid/name/role/descriptioncolumn_infobusiness field infoid/name/type/role/description/alias/examples/table_idmetric_infometric definitionid/name/relevant_columnscolumn_metrictable-column relationshipcolumn_id/metric_id逻辑关系如下:
tables只放业务表及其 columnsmetric_infoput at top level keynot inside tables这样可以避免把指标误写入 table_infotableinfotablescolumnsmetricinfometric infocolumn metricdimfactrelationdimfactrelation*** separate layersYAML format and database structure are differentnot suitable for reading directly and n executing INSERTEntityapp/entities/pure business object Modelapp/modelsORM mappingcorresponding to physical tableMapperapp/mappersEntity and Model mutual conversionRepositoryapp/repositoriesdata persistence and flushServiceapp/servicesbusiness capability encapsulationScriptconf/sync_db.pyvalidate configurationassemble dependenciescontrol transaction相关目录:.├── .env├── app/
│ ├── dbs/
│ ├── entities/
│ ├── mappers/
│ ├── models/
│ ├── repositories/
│ └── services/
└── conf/
├── app_config.yaml
├── get_config.py
├── meta_confi g.yaml└──sync_db.py.envMYS QL_USER=yMYS QL_PASSWORD=your_passwordLLM_API_KEY=
LLM_BASE_URL=.env exampleMYS QL_USER=yMYS QL_PASSWORD=.env.exampleLLM_API_KEY=
LLM_BASE_URL=.env is a local environment variable configuration filetypically written as KEY VALUEit is not a special file required by Python or operating systemand it does not naturally take effect automatically. project needs to use python-dotenvto read it:.from dotenv import load_dotenvload_dotenv加载后programme can read through environment variablesimport ospassword os.getenvThis file of get_confi g.py first loads.envn expands${VAR_NAME}in YAML with actual environment variable values.Data module using URL.createbuild connection addressnot directly concatenate strings.so that passwords containing@,:,/,etc.characters will not destroy URL:.from sqlalchemy import URLDB_URLURL.create
dependenciesuv add pyyaml python-dotent sqlalchemy asyncmytableinfo of layered implementationEntity is a business object that does not depend on SQLAlchemy@dataclassclass TableInfo:id strname strrole strdescriptionstrConfiguration does not have a separate table IDrefore uses physical tablename :tablename table_infoid Mappedmapped_colu mn。primary_key True)name Mappedmapped_colu mn)role Mappedmapped_colu mn)description Mappedmapped_colu mnMapper explicitly expand ORM fieldsavoid bringing SQLAlchemy internal state into Entitiesclass TableInfoMapper:def to_entity->TableInforeturn TableInfodef to_model->TableInfoModeturn TableInfoModelRepository usesoneadd_alland oneflushto add an objectto current transaction.all four types of datahave been processedafterthatby synchronization scriptunified submissiondont submit per line in loop.synchronization entryfirst validates following content:
Noneasync with async_engine.beginas connawait conn.run_syncawait conn.run_sync
reset_meta_tableswill deleteall tables registered in Base.metadatasonly suitable for local development.do not use directlyfor testpre-releaseor production databases.production environments should use migration and upsert.
rebuild belongs to DDLnot covered by rollback protection of subsequent datawrite transactions.data write shared Sessionasync def reset_meta_tables->Noneasync with MetaAsyncSessionLocalas sessionwith session.begin:table_infos await sync_table_infocolumn_infos await sync_column_intometric_infos await sync_metric_intocolumn_metrics await sync_column_metricsynchronize columninfo field configuration examplecolumns:name provinceprovincerole dimensiondescription order province.namealias sync true映射规则:id{dim_order.order_amount}relevant_columnsdim_order.order_amountrelevant_columnsdim_order.order_amountmetric_nameGmvGcurrent stage only synchronize indicatorsemantic and related fieldsbut cannot only relyon se configurationsgenerate precise indicator SQLafter needindicator modelcontinue to supplementaggregate expressionsstatistical granularityand filtering rulesconfiguration upper limitonly stores some examplesavoidSELECT DISTINCTfull loadcausesslow querymemory growthJSON field expansion.after Elasticsearch synchronizationneeds full dimensional valuesshould use independent paging read flow.characteristicsonly query onceRepository frominformation_schema.COLUMNStake current dw libraryall field typesform a dictionarywith as key.when synchronizing fieldsquery dictionarydirectlyavoiding each fieldrequesting MyS QLasynccommon problemphenomenoncausethandleNo module named appworking directory or sys.pathwrongproject root executionscript also adds project root pathNo module named asyncmysqlack of asynchronous driverexecute uv add asyncmyAccess deniedusing password NO.envis not loadedor variables are not expandedcheck.env and ${MYSQL_PASSWORD}KeyError or missing required itemsYAML configuration incompletebased on errorpathsupplement fieldsdw library does not exist specified field synchronization will report specific tablename.fieldnamerar than waiting until NULL typeis written before failingDuplicate entryprimary key conflictcheck duplicate itemsproduction synchronization change to upsertChinese display abnormalclient character set mismatchwhen queryingspecify utf8mb4check library table character setnext stepafter four tables are synchronizedcompletespecificallyimplementQdrantvectorize fields and indicator descriptionssupport semantic recallElasticsearchpage synchronizationsync:trueof dimensional valuesupport value matchingentry has been reservedsync_to_qdrant# TODOsync_to_elasticsearch# TODOMyS QL what is relational database management systemcan be first imagined as an enhanced Excel file managerdata also organized in rows and columnsbut it is more suitable for handling large amounts of datamulti-user simultaneous read writepermission controlassociation querytransactionseveral easily confused termsNameTermExampleDatabaseSchemanamespace for group tablesmetadwdwTablecollection of same kind of datatableinfofact_orderColumndata propertynameDescriptionrowconcrete recordGMV this line indicator recordwhat is SQLlanguage for operating relational databasesSELECT*FROM tableinfoDDLand DMLdistinguish former refers to operations related to defining database structuresuch as creatingdeletingmodifying tablesschemawhile latter refers to operations related to manipulating datasuch as selectinginsertingupdatingdeleting recordsin tablesTransactionwhat is *** importantTransaction treats multiple database write operations as a wholebeginning transactionwrite tableinfowrite columninfowrite metricinfowrite columnmetricsucceedCOMMITfailROLLBACKthis common sayingeir all succeedor all fail.howeverDDLin MyS QLa special commit behaviorreforethis article drop_all/create_allare outside rollback protection range of subsequent datawrite transactions.newbie should pay special attentionbeginner must particularly noteif you forgetWHEREin DELETEUPDATEit may affect entire tabledo no tdirectly concatenate user inputorwise may produce SQL injectiondatabasedatabase connectionclientand table fields should uniformly use utf8mb4primary keydetermines uniquenessindex can accelerate querybut too many indexalsowill increase writing costsdevelopment phased can rebuild tablegeneration environments usually use migration incrementally modify tablestructureDOM what isORM what doEngineSessionModel responsibilitiesEngineentry point for database connectionsaves connection configurationsmanages connection poolsexecutebottom-layer database communicationSessionwork unitfollow objectorganize SQ Lcontrol TransactionModelPython mappingof tabledeclare tablenamefield typemain keyetc.Mappingconversion 娱乐ween business Entityand Database Modelservice business capability encapsulationrepository isolates persistence operationsmapconvert 娱乐ween business objectsand ORM objectsproject its own mapperin orderto prevent strong coupling 娱乐ween entityand Modeladdflushcommitrefresh difference娱乐ween se several operationsare easily confusedaddobject into Sessionmanagementusually still haventexecuted INSERTflushsend pending modifications todatabasebut transactionhasn't committed yetcan still rollbackcommitcommit current transactionmake modifications officially effectiverollbackroll back current transactionrefreshread again from databaseupdate this row'sdatabase generated valuein repositoryuse one add_allone flushadd multiple objectsinto current transactionall four types of datahave been processedafterthatby outer layer synchronizatio nscriptunified commitdo no tsubmit per line in loop.use asynchronous SQLAlchemybecause I/O operationsequality waittime can give priorityto or tasksmore suitablefor FastAPI se kindservicesrequiring concurrent processingmultiple requests.async doesn't mean single SQ Lautomatically become fastalso doesn't mean infinite concurrency querysingle task efficiency depends on execution planshardware resourcesetc.asynchronous mainly improves resource utilization during waitingperiodsover timecan handle more concurrent requeststhan synchronous codehowevermaximum concurrent connectionsshould be reasonably controlledbased on actual hardware limitationsbesides ordinary value parameterscannot directly bindidentifier such astable namescolumn namesdirectly using textconcatenating identifier after validationcan reduce riskwhen configuringindirect injectionsuch assyntax errorselect * fromtablenamewherecolumnamewhen tablenamecontains dangerous characterslike quotesingle quotation markmay cause syntax errorif dynamic generationidentifierneed careful validationfor examplereplace non-alphanumeric characterswith underscoreorm *** re's still original native SQ Lin some scenariosORM adaptsregular CRUDoperationsbut sometimes writing direct SQ Lis clearerespecially when reading informationschemaor dynamically querying business fieldswhats domain-driven design?Domain-Driven Designusually abbreviated as DDDits core isn't about must create many foldersbut make code organized around business conceptsmake business rules avoid depending on details such as databasesWeb frameworksetc.here domain means problemsoftware needs solve.in this projecttable metadatafield metadataindicatorsrelationship among fieldsare all domain conceptsimportant idea name in code should try use unified languageunderstood by both business personnel developerscalled unified language.Ubiquitous LanguageDomain Layerapplication service layerdomain layerapplication service layerpresentation layerinterface adapterslayerhow y cooperate?synchronization scriptresponsible for flow eventransactionServicetable expresses business capabilitiesRepositoryisolates persistence operationMappermutual conversion 娱乐ween Business ObjectsORM ObjectEntitybusiness conceptdata base mappingServicebusiness capability encapsulationScriptconfiguration validationassembly dependenciescontrol eventransactionEntitypure business objectno knowledge about MY SQL SQLiteBusiness Logicdoesn't depend on specific storage implementationexamplein current projectTable Infois Business ObjectTable InfoModelis Database Objectalthough currently ir fields look similary have different responsibilitiesfuture if database changesdoesn't necessarily make Business Object completely change.script *** don't directly INSERTsmall scripts writing SQ Ldirectly isn't wrongbut project gradually grows often appearYAML parsingbusiness validation S Q LMix in same functionchange databasetry add APIagain repeat implement same Business Ruleswhen testing Business Logicmust connect real Databaseone field modificationdon't know which process will affectDDS isn't about more layers 娱乐terDDS also has costmore fileslonger call chainsimple functions seem cumbersome.judging wher one layer valuableyou can ask:
dotenv
by defaultenvironment variables existing insystem takes precedence.envwon’t override it.this waydeployment environment can inject production configurationswithout modifying project files.for exampleterminal temporarily sets export MYSQLUSER yanexport MYSQLPASSWORD yourpassworduv run python conf/syncdb.pythissetting only takes effectcurrent Shellits started subprocesses.close terminaltemporary setting usually ceases exist..env usage notes.envonly suitable local development simple deploymentnot professional Key Management Systemproduction environment prioritize platform Secretcloud Key Servicescontainer Secretdon’t log print complete configurationspasswordToken or database URLin logsdon’t contain space#$values recommended quotedconfirm parsing rulescontained spaces#$characteristic values recommended quotedconfirm parsing rulemodify.envalready started processtypically won’t automatically updateneed restart programprovide new person.env.exampleavoiding everyone guess which environment variables needed
