join - Snowsql 加入或强制转换问题
问题描述
所以我遇到了 Snowsql 查询的连接问题。我在下面的查询中有 3 个单独的样式，并且我无法让 AUDIT_LOGGIN_TABLE_NAME 显示这个特定的表名。我什至用“命名查询”和“CTE”样式对表名进行了硬编码。但没有运气。当我单独运行每段代码时，我得到了数据集。有两个领域可以加入。Table_Name 和 ETL 数据。
对于第二轮，我认为这将是空间问题。因此还添加了 TRIM。但仍然没有运气。任何人都可以在这方面指导我大致正确的方向。选择您的查询风格。
预期的结果集也在下面。可悲的是，这将进入一个视图。所以我认为我不能在视图定义中使用临时表。
select a.Full_Table_Name
,replace(UPPER(Full_Table_Name),'RAW_DB.JA.','' ) as Short_Table_Name
,log.TABLE_NAME as AUDIT_LOGGIN_TABLE_NAME
,a.ROWS_INSERTED
,log.RECORD_COUNT AS AUDIT_LOGGING_RECORD_COUNT
,a.ETL_DATE
, to_date(concat(substring(log.ETL_DATE,0,4),'-',substring(log.ETL_DATE,5,2),'-',substring(log.ETL_DATE,7,2) ) ) as AUDIT_LOGGING_ETL_DATE
,a.START_TIME
,a.END_TIME
,a.DURATION_IN_SECONDS
,a.EXECUTION_STATUS
,case when a.ROWS_INSERTED = log.RECORD_COUNT then 1 else 0 end VALIDATION_RECORD_COUNT_INSERT
from (
select UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12)) as Full_Table_Name
,ROWS_INSERTED
,to_date(START_TIME) as ETL_DATE
,START_TIME
,END_TIME
,datediff(second,START_TIME,END_TIME) as Duration_in_seconds
,EXECUTION_STATUS
from VW_QUERY_HISTORY as vw
where substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12) like '%JA%'
and QUERY_TYPE = 'COPY'
and UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12)) like '%RAW_DB.JA%'
and to_date(START_TIME) >= dateadd(day,-8,current_date() )
) as a
LEFT JOIN SOURCE_TABLE_COUNTS as log on UPPER(replace(UPPER(log.TABLE_NAME),'MGR.','')) = replace(UPPER(Full_Table_Name),'RAW_DB.JA.','' )
and
to_date(a.ETL_DATE) = to_date(concat(substring(log.ETL_DATE,0,4),'-',substring(log.ETL_DATE,5,2),'-',substring(log.ETL_DATE,7,2) ) )
order by ETL_DATE
///////NAMED QUERY STYLE /////////////////////////////////////////////
select Full_Table_Name
,Short_Table_Name
,AUDIT_LOGGIN_TABLE_NAME
,ROWS_INSERTED
,AUDIT_LOGGING_RECORD_COUNT
,ETL_DATE
,AUDIT_LOGGING_ETL_DATE
,START_TIME
,END_TIME
,DURATION_IN_SECONDS
,case when ROWS_INSERTED = AUDIT_LOGGING_RECORD_COUNT then 1 else 0 end as VALIDATION_RECORD_COUNT_INSERT
from (
select * from (
select TRIM(UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12))) as Full_Table_Name
,TRIM(replace(UPPER(Full_Table_Name),'RAW_DB.JA.','' )) as Short_Table_Name
,ROWS_INSERTED
,to_date(START_TIME) as ETL_DATE
,START_TIME
,END_TIME
,datediff(second,START_TIME,END_TIME) as Duration_in_seconds
,EXECUTION_STATUS
from VW_QUERY_HISTORY as vw
where substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12) like '%JA%'
and QUERY_TYPE = 'COPY'
and UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12)) like '%RAW_DB.JA%'
and to_date(START_TIME) >= dateadd(day,-8,current_date() )
) as sub_qury where trim(Short_Table_Name) like '%UDT_SKU%'
) as a
left join
( select trim(UPPER(replace(UPPER(log.TABLE_NAME),'MGR.',''))) as AUDIT_LOGGIN_TABLE_NAME
,log.RECORD_COUNT AS AUDIT_LOGGING_RECORD_COUNT
, to_date(concat(substring(log.ETL_DATE,0,4),'-',substring(log.ETL_DATE,5,2),'-',substring(log.ETL_DATE,7,2) ) ) as AUDIT_LOGGING_ETL_DATE
from SOURCE_TABLE_COUNTS as log
where UPPER(replace(UPPER(log.TABLE_NAME),'MGR.','')) = 'UDT_SKU'
) as audit_log_query
on trim(a.Short_Table_Name) = trim(audit_log_query.AUDIT_LOGGIN_TABLE_NAME) and
audit_log_query.AUDIT_LOGGING_ETL_DATE = ETL_DATE
//////////// CTE style
WITH audit_log_query as
( select trim(UPPER(replace(UPPER(log.TABLE_NAME),'MGR.',''))) as AUDIT_LOGGIN_TABLE_NAME
,log.RECORD_COUNT AS AUDIT_LOGGING_RECORD_COUNT
, to_date(concat(substring(log.ETL_DATE,0,4),'-',substring(log.ETL_DATE,5,2),'-',substring(log.ETL_DATE,7,2) ) ) as AUDIT_LOGGING_ETL_DATE
from SOURCE_TABLE_COUNTS as log
where UPPER(replace(UPPER(log.TABLE_NAME),'MGR.','')) = 'UDT_SKU'
)
select Full_Table_Name
,Short_Table_Name
,AUDIT_LOGGIN_TABLE_NAME
,ROWS_INSERTED
,AUDIT_LOGGING_RECORD_COUNT
,ETL_DATE
,AUDIT_LOGGING_ETL_DATE
,START_TIME
,END_TIME
,DURATION_IN_SECONDS
,case when ROWS_INSERTED = AUDIT_LOGGING_RECORD_COUNT then 1 else 0 end as VALIDATION_RECORD_COUNT_INSERT
from (
select * from (
select TRIM(UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12))) as Full_Table_Name
,TRIM(replace(UPPER(Full_Table_Name),'RAW_DB.JA.','' )) as Short_Table_Name
,ROWS_INSERTED
,to_date(START_TIME) as ETL_DATE
,START_TIME
,END_TIME
,datediff(second,START_TIME,END_TIME) as Duration_in_seconds
,EXECUTION_STATUS
from VW_QUERY_HISTORY as vw
where substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12) like '%JA%'
and QUERY_TYPE = 'COPY'
and UPPER(substring(QUERY_TEXT,11,charindex('from',QUERY_TEXT)-12)) like '%RAW_DB.JA%'
and to_date(START_TIME) >= dateadd(day,-8,current_date() )
) as sub_qury where trim(Short_Table_Name) like '%UDT_SKU%'
) as a
left join audit_log_query on trim(a.Short_Table_Name) = trim(audit_log_query.AUDIT_LOGGIN_TABLE_NAME)
and audit_log_query.AUDIT_LOGGING_ETL_DATE = ETL_DATE
预期数据
FULL_TABLE_NAME|SHORT_TABLE_NAME|AUDIT_LOGGIN_TABLE_NAME|ROWS_INSERTED|AUDIT_LOGGING_RECORD_COUNT|ETL_DATE|AUDIT_LOGGING_ETL_DATE|START_TIME|END_TIME|DURATION_IN_SECONDS|VALIDATION_RECORD_COUNT_INSERT
RAW_DB.JDA.UDT_SKU|UDT_SKU||19697||2021-04-01||2021-04-01 07:59:39.101 -0700|2021-04-01 07:59:40.048 -0700|1|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||27144||2021-04-05||2021-04-05 08:03:37.907 -0700|2021-04-05 08:03:39.377 -0700|2|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||16536||2021-03-31||2021-03-31 08:03:05.626 -0700|2021-03-31 08:03:06.921 -0700|1|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||19182||2021-04-02||2021-04-02 08:03:33.296 -0700|2021-04-02 08:03:34.803 -0700|1|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||885||2021-04-03||2021-04-03 08:04:15.123 -0700|2021-04-03 08:04:16.071 -0700|1|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||0||2021-04-04||2021-04-04 07:30:23.213 -0700|2021-04-04 07:30:23.862 -0700|0|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||1262||2021-04-04||2021-04-04 17:35:01.110 -0700|2021-04-04 17:35:02.500 -0700|1|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||197899||2021-04-06||2021-04-06 08:00:56.860 -0700|2021-04-06 08:00:59.798 -0700|3|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||107433||2021-03-30||2021-03-30 08:02:34.231 -0700|2021-03-30 08:02:36.846 -0700|2|0
RAW_DB.JDA.UDT_SKU|UDT_SKU||17794||2021-04-07||2021-04-07 08:00:40.782 -0700|2021-04-07 08:00:41.590 -0700|1|0
解决方案
因此，正如您所指出的，表架构JDA不是JA，因此您的过滤器需要更改。
您可以选择许多地方来输出您想要的输出，然后在 WHERE 子句中重新应用相同的逻辑，但略有不同，您可以在 WHERE 中使用别名的 SELECT 列。
您的日期解析也可以只使用格式化的诗句并保存字符串拼接。
TO_DATE(log.etl_date, 'YYYYMMDD') AS audit_logging_etl_date
显示输入数据也很好，因为试图猜测审计格式是相当痛苦的。
WITH source_table_counts AS (
SELECT * FROM VALUES
('mgr.UDT_SKU',123, '20210401')
v(table_name, record_count, etl_date)
), vw_query_history AS (
SELECT * FROM VALUES
('0123456789 RAW_DB.JDA.UDT_SKU from',19697,'2021-04-01 07:59:39.101 -0700','2021-04-01 07:59:40.048 -0700',NULL,'COPY'),
('0123456789 RAW_DB.JDA.UDT_SKU from',27144,'2021-04-05 08:03:37.907 -0700','2021-04-05 08:03:39.377 -0700',NULL,'COPY'),
('0123456789 RAW_DB.JDA.UDT_SKU from',16536,'2021-03-31 08:03:05.626 -0700','2021-03-31 08:03:06.921 -0700',NULL,'COPY'),
('0123456789 RAW_DB.JDA.UDT_SKU from',19182,'2021-04-02 08:03:33.296 -0700','2021-04-02 08:03:34.803 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',885,'2021-04-03 08:04:15.123 -0700','2021-04-03 08:04:16.071 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',0,'2021-04-04 07:30:23.213 -0700','2021-04-04 07:30:23.862 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',1262,'2021-04-04 17:35:01.110 -0700','2021-04-04 17:35:02.500 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',197899,'2021-04-06 08:00:56.860 -0700','2021-04-06 08:00:59.798 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',107433,'2021-03-30 08:02:34.231 -0700','2021-03-30 08:02:36.846 -0700',NULL,'COPY'),
('01234567891RAW_DB.JDA.UDT_SKU from',17794,'2021-04-07 08:00:40.782 -0700','2021-04-07 08:00:41.590 -0700',NULL,'COPY')
v(query_text, rows_inserted, start_time, end_time, execution_status,query_type)
)
, audit_log_query AS (
select
TRIM(REPLACE(UPPER(log.table_name),'MGR.','')) AS audit_loggin_table_name
,log.record_count AS audit_logging_record_count
,TO_DATE(log.etl_date, 'YYYYMMDD') AS audit_logging_etl_date
--,TO_DATE(CONCAT(SUBSTRING(log.etl_date,0,4),'-',SUBSTRING(log.etl_date,5,2),'-', SUBSTRING(log.etl_date,7,2) ) ) AS audit_logging_etl_date
FROM source_table_counts AS log
WHERE audit_loggin_table_name = 'UDT_SKU'
)
SELECT
a.full_table_name
,a.short_table_name
,al.audit_loggin_table_name
,a.rows_inserted
,al.audit_logging_record_count
,a.etl_date
,al.audit_logging_etl_date
,a.start_time
,a.end_time
,a.duration_in_seconds
,IFF(a.rows_inserted = al.audit_logging_record_count, 1, 0) AS validation_record_count_insert
FROM (
SELECT *
FROM (
SELECT
TRIM(UPPER(SUBSTRING(query_text, 11, CHARINDEX('from', query_text)-12))) AS full_table_name
,REPLACE(full_table_name,'RAW_DB.JDA.','' ) AS short_table_name
,rows_inserted
,TO_DATE(start_time) AS etl_date
,start_time
,end_time
,DATEDIFF('second', start_time, end_time) AS duration_in_seconds
,execution_status
FROM vw_query_history AS vw
WHERE full_table_name like '%JDA%'
AND query_type = 'COPY'
AND full_table_name like '%RAW_DB.JDA%'
AND to_date(START_TIME) >= dateadd(day,-8,current_date() )
) as sub_qury
where short_table_name like '%UDT_SKU%'
) as a
LEFT JOIN audit_log_query AS al
on a.short_table_name = al.audit_loggin_table_name
and al.audit_logging_etl_date = a.etl_date;
推荐阅读
- javascript - React：更改子组件后如何正确更新父组件的状态
- javascript - 如何从javascript中的数组中过滤掉json对象
- p5.js - 如何在 p5.js 中等待
- typescript - why my icon don't change when i'm clicking on ? (ionic/vue/ts)
- javascript - 如何在 React Native 中保存 SVG 输入？
- python - 如何在 smtp 消息正文中添加功能并每 12 小时发送一次？
- pandas - 在 Python 中将年度和每月数据转换为每周数据
- git - 如何创建多个连接的 git 存储库
- android - 没有为此字段设置存储空间？
- reactjs - 为什么某些 react-chartjs-2 选项被忽略？