Snowflake // 如何计算 DEV 和 PROD 数据库给定架构中每个视图的记录-解网

问：

我想比较数据库（DEV 和 PROD）之间给定架构中每个视图的记录计数。目标是验证相应的视图（在 DEV 和 PROD 中）是否具有相同的记录计数。

假设我们有这样的东西：

Database_DEV。架构视图01

Database_DEV。架构.View02

Database_DEV。架构.View03

和

Database_PROD。架构视图01

Database_PROD。架构.View02

Database_PROD。架构.View03

因此，我想要一个像这样的表格：

视图名称	Database_DEV	Database_PROD
景观01	345	345
景观02	450	450
景观03	555	666

视图名称应从表中动态获取INFORMATION_SCHEMA：

SELECT TABLE_NAME
FROM Database_PROD.INFORMATION_SCHEMA.TABLES
WHERE 1=1
AND TABLE_CATALOG = 'Database_PROD'
AND TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'Schema'

我假设我可以使用光标获得结果？

感谢您的帮助。

snowflake-cloud-data-platform 游标信息架构

SELECT listagg('select \'' || TABLE_NAME || '\' as VIEW_NAME, (select count(*) from DB_PROD.PROD.' || TABLE_NAME || ') as PROD_COUNT, (select count(*) from DB_DEV.DEV.' 
               || TABLE_NAME  || ') as DEV_COUNT\n', 'UNION ALL\n') as SQL_TO_RUN
FROM INFORMATION_SCHEMA.TABLES
WHERE 1=1
AND TABLE_CATALOG = 'DB_PROD'
AND TABLE_TYPE = 'VIEW'
AND TABLE_SCHEMA = 'PUBLIC'
;

SQL 生成器将生成一个行和列，以复制和粘贴或在脚本中使用。它看起来像这样：

select 'MY_VIEW' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW) as DEV_COUNT
UNION ALL
select 'MY_VIEW123' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW123) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW123) as DEV_COUNT
UNION ALL
select 'MY_VIEW456' as VIEW_NAME, (select count(*) from DB_PROD.PROD.MY_VIEW456) as PROD_COUNT, (select count(*) from DB_PROD.DEV.MY_VIEW456) as DEV_COUNT
UNION ALL
select 'MY_VIEW456' as DB_PROD.PROD.VIEW_NAME, (select count(*) from MY_VIEW789) as PROD_COUNT, (select count(*) from DB_DEV.DEV.MY_VIEW789) as DEV_COUNT

运行它会产生如下输出：

VIEW_NAME	PROD_COUNT	DEV_COUNT
MY_VIEW	25	25
MY_VIEW123	25	25
MY_VIEW456	25	25
MY_VIEW789	50	50

您甚至可以让 SQL 生成器添加另一列来比较 PROD_COUNT 和 DEV_COUNT，如果它们匹配或不匹配，则报告 true 或 false。

Snowflake // 如何计算 DEV 和 PROD 数据库给定架构中每个视图的记录

Snowflake // How to count records for each view in given schema for DEV and PROD database

评论

评论