如何在GCP BigQuery中提取旧表的数据插入脚本及表所有者信息
解决方案
以下是针对不同主流数据库的具体实现方法:
获取表创建者信息
INFORMATION_SCHEMA.TABLES属于SQL标准通用视图,本身不存储创建者字段,需要结合各数据库的系统表查询:
- MySQL 场景
执行如下SQL:
注:低于8.0的MySQL版本可关联SELECT TABLE_NAME, DEFINER AS table_creator FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '替换为你的数据库名' AND TABLE_NAME = '替换为你的目标表名';mysql.user系统表补充查询创建者的完整权限信息 - PostgreSQL 场景
执行如下SQL:SELECT tablename, tableowner AS table_creator FROM pg_tables WHERE schemaname = '替换为你的模式名' AND tablename = '替换为你的目标表名'; - SQL Server 场景
执行如下SQL:SELECT t.name AS table_name, p.name AS table_creator FROM sys.tables t JOIN sys.database_principals p ON t.principal_id = p.principal_id WHERE t.name = '替换为你的目标表名';
查询历史插入SQL脚本
只有提前开启了数据库审计日志、二进制日志等功能的场景才能回溯历史执行的插入语句,未开启日志的场景无法直接查询历史执行脚本,两种可行方案如下:
方案1:从日志中筛选历史插入语句(需开启对应日志)
- MySQL 开启binlog的场景,可直接用mysqlbinlog工具解析指定时间段的binlog文件,筛选目标表的插入操作:
mysqlbinlog --start-datetime="YYYY-MM-DD HH:MM:SS" --stop-datetime="YYYY-MM-DD HH:MM:SS" binlog文件路径 | grep -i 'INSERT INTO 替换为你的目标表名' - PostgreSQL 开启pgAudit或全量语句日志的场景,直接在数据库日志目录下筛选对应时间段的INSERT语句即可
- SQL Server 开启查询存储的场景,可从
sys.query_store_query等系统表中筛选历史执行的插入语句
方案2:从现有表数据反向生成插入脚本(无日志场景的替代方案)
如果仅需要目标表现有数据对应的插入脚本,不需要完全匹配历史执行的语句,可直接通过SQL批量生成:
示例MySQL语法:
SELECT CONCAT( 'INSERT INTO 替换为你的目标表名(列1,列2,列3) VALUES (', QUOTE(列1),',',QUOTE(列2),',',QUOTE(列3),');' ) FROM 替换为你的目标表名;
执行后会直接输出所有现有数据对应的标准INSERT语句。
内容的提问来源于stack exchange,提问作者Katya
相关产品推荐
相关产品推荐

