2026年数据库工程师（大数据）实操考核试题及答案

2026年数据库工程师（大数据）实操考核试题及答案一、基础运维与监控（12分）1.使用Prometheus+Grafana对Kafka集群进行黄金指标监控，要求：a.在Grafana中导入官方Kafka概览仪表盘ID15292，保存为“Q1_Kafka_Overview.json”；b.新增一条RecordingRule，计算每秒消息流入量5分钟增长率，指标命名为kafka_topic_messages_in_per_sec_5m_growth；c.在仪表盘新增Singlestat面板，显示该指标近10分钟最大值，背景阈值：绿色<5%，黄色5%~15%，红色>15%；d.将最终仪表盘导出为“Q1_Kafka_Growth.json”，并截图保存为“Q1_Grafana.png”。答案与解析：a.登录Grafana→Import→15292→选中Prometheus数据源→Save。b.编辑/etc/prometheus/rules/kafka.yml：groups:name:kafka_growthrules:record:kafka_topic_messages_in_per_sec_5m_growthexpr:(rate(kafka_server_brokertopicmetrics_messagesinpersec[5m])/rate(kafka_server_brokertopicmetrics_messagesinpersec[5m]offset5m)1)100c.添加Singlestat→Queries→输入kafka_topic_messages_in_per_sec_5m_growth→Options→Stat→Max→Coloring→Thresholds5,15。d.Share→Export→Savetofile→截图。评分细则：文件存在2分，指标正确3分，面板阈值3分，截图清晰4分。二、数据湖建仓（15分）2.使用Spark3.5将1.2TBCSV原始日志（/exam-data/raw/logistics/）写入Iceberg表，要求：a.按dt（日期）+hour二级分区，文件格式PARQUET，压缩ZSTD，目标目录/exam-data/warehouse/iceberg/logistics.db/event_log；b.写入后执行rewrite_data_files，target_file_size_mb=256；c.统计每个分区的recordcount、size_MB、zstd_compression_ratio，结果保存为“Q2_stats.csv”；d.使用SparkSQL查询2026-03-1815点分区，过滤event_type='order_placed'，取user_id、order_id、amount三列，写入ClickHouse本地表logistics.order_placed，引擎MergeTree()PARTITIONBYtoYYYYMM(create_time)ORDERBY(user_id,order_id)。答案与解析：a.spark-sql--packagesorg.apache.iceberg:iceberg-spark-runtime-3.5_2.12:1.6.0CREATETABLEiceberg.logistics.event_log(user_idBIGINT,order_idSTRING,event_typeSTRING,amountDECIMAL(10,2),create_timeTIMESTAMP,dtSTRING,hourSTRING)USINGicebergPARTITIONEDBY(dt,hour)TBLPROPERTIES('write.format.default'='parquet','pression-codec'='zstd');INSERTINTOiceberg.logistics.event_logSELECT,date_format(create_time,'yyyy-MM-dd')dt,hour(create_time)hourFROMcsv.`/exam-data/raw/logistics/`;b.CALLiceberg.system.rewrite_data_files(table=>'logistics.event_log',options=>map('target-file-size-bytes','268435456'));c.使用Spark读取metadata文件，解析manifest统计：valdf=spark.read.parquet("/exam-data/warehouse/iceberg/logistics.db/event_log/metadata/.avro")df.groupBy("partition.dt","partition.hour").agg(count("")records,sum("file_size_in_bytes")/1024/1024size_MB,avg("file_size_in_bytes")/avg("split_offsets.size")zstd_ratio).write.csv("/exam-data/Q2_stats.csv")d.CREATETABLElogistics.order_placed(user_idUInt64,order_idString,amountDecimal(10,2),create_timeDateTime)ENGINE=MergeTreePARTITIONBYtoYYYYMM(create_time)ORDERBY(user_id,order_id);使用ClickHouseJDBC写入：spark.read.iceberg("logistics.event_log").filter("dt='2026-03-18'andhour='15'andevent_type='order_placed'").select("user_id","order_id","amount","create_time").write.mode("append").option("driver","cc.blynk.clickhouse.ClickHouseDriver").jdbc("jdbc:clickhouse://clickhouse:8123/logistics","order_placed",newjava.util.Properties())评分细则：Iceberg表结构正确3分，分区写入3分，rewrite成功2分，统计文件3分，ClickHouse写入4分。三、实时数仓（18分）3.采用FlinkSQL构建Kafka→Kafka流式ETL，场景：用户行为埋点topic:user_behavior，格式JSON，字段user_id、item_id、behavior、ts；目标topic:user_behavior_clean，需完成：a.过滤behaviorin('pv','buy','cart')且ts在2026-03-1800:00:00之后；b.使用Flink1.20新语法CREATETABLEASSELECT（CTAS）将清洗结果写入目标topic，key格式user_id:item_id，value格式Protobuf，schema已提供/exam-data/schema/UserBto；c.打开FlinkWebUISavepoint，做一次外部触发savepoint，保存路径/exam-data/checkpoint/ctas-user-behavior；d.使用kafka-protobuf-console-consumer验证输出100条，保存为“Q3_output.log”；e.将作业打包为“Q3_flink_job.jar”，提交命令写入“Q3_submit.sh”。答案与解析：a.注册源表：CREATETABLEuser_behavior(user_idBIGINT,item_idBIGINT,behaviorSTRING,tsTIMESTAMP(3),WATERMARKFORtsAStsINTERVAL'5'SECOND)WITH('connector'='kafka','topic'='user_behavior','properties.bootstrap.servers'='kafka:9092','format'='json','scan.startup.mode'='timestamp','scan.startup.timestamp-millis'=1709251200000);b.编译Protobuf：protoc--java_out=/exam-data/generated/exam-data/schema/UserBto打包为flink-protobuf-1.0.jar放入lib。CREATETABLEuser_behavior_clean(user_idBIGINT,item_idBIGINT,behaviorSTRING,tsTIMESTAMP(3),PRIMARYKEY(user_id,item_id)NOTENFORCED)WITH('connector'='kafka','topic'='user_behavior_clean','properties.bootstrap.servers'='kafka:9092','key.format'='raw','key.fields'='user_id;item_id','value.format'='protobuf','tobuf.class-name'='com.exam.UserBehavior')EXECUTECTASuser_behavior_cleanSELECTuser_id,item_id,behavior,tsFROMuser_behaviorWHEREbehaviorIN('pv','buy','cart')ANDts>=TIMESTAMP'2026-03-1800:00:00';c.触发savepoint：./bin/flinksavepoint<jobId>/exam-data/checkpoint/ctas-user-behaviord.kafka-protobuf-console-consumer--bootstrap-serverkafka:9092--topicuser_behavior_clean--propertyvalue.deserializer=com.exam.UserBehaviorProtoDeserializer--max-messages100>/exam-data/Q3_output.loge.提交脚本：!/bin/bash/opt/flink/bin/flinkrun-application-tkubernetes-application\Dkubernetes.cluster-id=flink-cdas\corg.apache.flink.table.client.SqlClient\/exam-data/Q3_flink_job.jar-f/exam-data/Q3_ctas.sql评分细则：CTAS语法正确4分，Protobuf集成4分，savepoint路径2分，验证日志4分，打包脚本4分。四、即席查询加速（10分）4.使用Trino451对Iceberg表logistics.event_log执行多维分析：a.查询2026-03-18当天event_type的PV、UV、GMV，要求使用GROUPINGSETS输出小计、合计；b.开启Trino新特性fault-tolerantexecution，设置retry-policy=QUERY，retry-attempts=5；c.将查询结果写入MinIO桶trino-result，格式ORC，压缩LZ4，路径/event_analysis/dt=2026-03-18/；d.记录查询物理计划（JSON）保存为“Q4_plan.json”，耗时截图“Q4_trino.png”。答案与解析：a.SETSESSIONretry_policy='QUERY';INSERTINTOminio.trino_result.event_analysisSELECTdt,event_type,COUNT()pv,COUNT(DISTINCTuser_id)uv,SUM(amount)gmv,GROUPING(event_type)grpFROMiceberg.logistics.event_logWHEREdt='2026-03-18'GROUPBYGROUPINGSETS((dt,event_type),(dt));b.已在SETSESSION中完成。c.创建MinIOcatalog：CREATECATALOGminioWITH(''='hive','hive.metastore'='file','hive.catalog.dir'='s3a://trino-result/','hive.s3.endpoint'='http://minio:9000','hive.s3.aws-access-key'='minioadmin','hive.s3.aws-secret-key'='minioadmin');d.EXPLAIN(TYPEDISTRIBUTED,FORMATJSON)SELECT...;保存结果。评分细则：SQL正确4分，GROUPINGSETS2分，MinIO写入2分，计划与截图2分。五、图计算（8分）5.使用SparkGraphFrames完成物流网络最短路径：输入：/exam-data/graph/edge.csv（src,dst,distance），节点1亿，边5亿；输出：从节点0到任意可达节点的最短距离，TOP-100最远节点，保存为“Q5_top100.csv”；要求：使用GraphFrame.bfs或shortestPaths，运行内存不超过8GB，执行时间<5min。答案与解析：valedges=spark.read.option("header","true").csv("/exam-data/graph/edge.csv")valvertices=edges.select("svalg=GraphFrame(vertices,edges)valres=g.shortestPaths.landmarks(Seq("0")).run()valtop=res.select("id"top.write.csv("/exam-data/Q5_top100.csv")调优：spark.serializer=org.apache.spark.serializer.KryoSerializer，shuffle分区800，drivermemory2g，executormemory4g，动态分配。评分细则：代码正确4分，TOP100顺序2分，内存控制2分。六、数据治理与血缘（9分）6.使用ApacheAtlas2.4对Iceberg表logistics.event_log进行元数据采集，要求：a.通过AtlasHiveHook自动感知Iceberg表新建与变更；b.手动注册Trino查询SQL作为Process，输入表为logistics.event_log，输出表为minio.trino_result.event_analysis，建立血缘；c.在Atlas中搜索lineage深度=3，导出图谱JSON保存为“Q6_lineage.json”；d.使用AtlasRESTAPI批量给event_log表添加标签PII:user_id，调用脚本保存为“Q6_tag.py”。答案与解析：a.在hive-site.xml增加atlas.hook.hive.synchronous=false，重启Hivemetastore容器。b.POST/api/atlas/v2/entity{"typeName":"Process","attributes":{...},"inputs":[event_logGUID],"outputs":[event_analysisGUID]}c.GET/api/atlas/v2/lineage/{guid}/horizontal?depth=3d.Python脚本：importrequests,jsonatlas='http://atlas:21000'auth=('admin','admin')table_guid=requests.get(f"{atlas}/api/atlas/v2/entity/uniqueAttribute/type/hive_table?attr:name=logistics.event_log",auth=auth).json()['entity']['guid']payload={"classification":{"typeName":"PII","attributes":{"column":"user_id"}}}requests.post(f"{atlas}/api/atlas/v2/entity/guid/{table_guid}/classifications",data=json.dumps(payload),headers={'Content-Type':'application/json'},auth=auth)评分细则：Hook生效2分，血缘注册3分，图谱导出2分，标签脚本2分。七、性能调优（12分）7.ClickHouse24.5单表50亿订单，结构：order_idString,user_idUInt64,merchant_idUInt64,amountFloat64,create_timeDateTime,dtDate。要求：a.建立物化视图，按merchant_id汇总每日GMV、订单数，视图名merchant_daily；b.设置merge_tree分区粒度为月，orderby(merchant_id,create_time)，索引粒度8192；c.使用clickhouse-benchmark执行SQL：SELECTmerchant_id,sum(amount)gmv,count()cntFROMordersWHEREdt='2026-03-18'GROUPBYmerchant_idORDERBYgmvDESCLIMIT100;并发20，持续60秒，输出QPS、meanlatency、P99保存为“Q7_report.tsv”；d.打开query_log，分析最慢5条，将SQL、read_rows、memory_usage导出为“Q7_slow.json”。答案与解析：a.CREATEMATERIALIZEDVIEWmerchant_dailyENGINE=SummingMergeTreePARTITIONBYtoYYYYMM(dt)ORDERBY(merchant_id,dt)ASSELECTmerchant_id,dt,sum(amount)gmv,count()cntFROMordersGROUPBYmerchant_id,dt;b.ALTERTABLEordersMODIFYORDERBY(merchant_id,create_time)SETTINGSindex_granularity=8192;c.clickhouse-benchmark-c20-d60--query="select...">Q7_report.tsvd.SELECTquery,read_rows,memory_usageFROMsystem.query_logWHEREtype='QueryFinish'ORDERBYquery_duration_msDESCLIMIT5FORMATJSONEachRow>Q7_slow.json评分细则：视图正确3分，索引调优3分，压测报告3分，慢查询3分。八、安全与审计（8分）8.在PostgreSQL16中创建角色链：a.创建角色analyst、role_admin、auditor，继承关系role_admin→analyst→auditor；b.对schemasales下所有表授予analystSELECT，role_admin可GRANTSELECT；c.开启pgaudit，设置log='all',format='json'，重启后执行5条查询，将审计日志导入ElasticSearch索引pgaudit-2026.03.18；d.在Kibana创建可视化面板：按user、table、command展示柱状图，保存为“Q8_dashboard.ndjson”。答案与解析：a.CREATEROLEanalystNOINHERIT;CREATEROLErole_adminNOINHERIT;CREATEROLEauditorLOGIN;GRANTanalystTOauditor;GRANTrole_adminTOanalyst;b.ALTERDEFAULTPRIVILEGESINSCHEMAsalesGRANTSELECTONTABLESTOanalyst;GRANTSELECTONALLTABLESINSCHEMAsalesTOanalyst;c.shared_preload_libraries='pgaudit'，pgaudit.log='all'，pgaudit.log_catalog=off，重启；filebeat模块postgresql直接采集/var/log/postgresql/.log输出到ES。d.导入模板，创建VerticalBar，X-axisTermsuser.keyword，Splitseriestable.keyword，Stackedformat。评分细则：角色链2分，权限2分，审计日志2分，面板2分。九、机器学习特征仓库（8分）9.使用Feast0.40构建实时特征服务：a.定义特征视图user_click_7d，来源Kafkatopicuser_behavior_clean，字段user_id、item_id、cnt_7d；b.使用Flink作为在线流处理器，计算滑动7天点击次数，输出到Redisonlinestore；c.通过FeastPythonSDK获取特征user_id=12345的cnt_7d，打印值保存为“Q9_feature.log”；d.使用locust压测特征服务RESTAPI，并发200，RPS>8000，P99<30ms，报告保存为“Q9_locust.csv”。答案与解析：a.feature_store.yaml配置registry:s3://feast/registry.db，online_store:redis，offline_store:iceberg；user_click_7d.yaml定义entity=user,features=[cnt_7d],ttl=864007,source=kafka。b.feastapply后，flink作业自动提交，使用SlidingWindow7天1天滑动。c.fromfeastimportFeatureStore;fs=FeatureStore(repo_path=".")features=fs.get_online_features(features=["user_click_7d:cnt_7d"],entity_rows=[{"user":"12345"}])print(features.to_dict())>Q9_feature.logd.locust-fload_test.py--headless-u200-r50-t60s--csv=Q9_locust评分细则：特征