时间戳

首页 > 解决方案 > 在 Azure 数据工厂的复制活动中每月自动更改日期，以将数据从 SAP HANA 复制到 Azure SQLDB

sql - 在 Azure 数据工厂的复制活动中每月自动更改日期，以将数据从 SAP HANA 复制到 Azure SQLDB

问题描述

我正在使用azure data factory (ADF)将数据从SAP HANA复制到Azure SQLDB。我在 ADF 中使用“复制活动”来执行此操作，使用 SQL 查询来查询 HANA 中的表并将其接收到 SQLDB。该查询包含reporting_date我需要每月手动更新以获取数据的内容。

我想删除手动更改日期的冗余过程，然后每个月运行它，因为我有 350 多个管道每个月更改日期，这个任务变得非常忙碌和耗时。有什么方法可以在一个地方更改日期，它可以作为源查询的输入，并且查询会自动更新日期？请根据我的要求找到随附的屏幕截图。

ADF 屏幕截图

待更新查询：

SELECT "SAPINSTANCE", "MCO", "COUNTRY", "BUKRS", "EntityName", "XBLNR", "HKONT", "TXT50", "GSBER", "BELNR", "BLART", "LTEXT", "CC_BLDAT", "CC_BUDAT", "WAERS", "MONAT", "SGTXT", "GJAHR", "BKTXT", "UMSKZ", "S_LTEXT", "AUFNR", "PROJK", "PRCTR", "EBELN", "KOART", "AUGBL", "CC_AUGDT", "REBZG", "LIFNR", "NAME1", "CC_SHKZG", "KOSTL", "BSCHL", "BUZEI", "EBELP", "KTOKS", "ZUONR", "XINTB", "XLOEB", "XSPEB", "XOPVW", "XKRES", "TYPE_WISE_CLASS", "DESCRIPTION", "MCLASS", "CC_NUM_OF_DAYS_AGEING", "CC_AGEING_BUCKET", "CC_GROUP_CURRENCY", "CC_REPORTING_DATE_VAR", "MARKET_CLUSTER", "HWAER", "CURR_UNIT_TO_EURO_1", sum("CC_WRBTR") AS "CC_WRBTR", sum("CC_GROUP_CURRENCY_VALUE") AS "CC_GROUP_CURRENCY_VALUE", sum("CC_LOC_AMT_IN_MIN") AS "CC_LOC_AMT_IN_MIN", sum("CC_AMT_IN_EUR_MIN") AS "CC_AMT_IN_EUR_MIN", sum("CC_DMBTR_E") AS "CC_DMBTR_E" 
FROM "_SYS_BIC"."table"('PLACEHOLDER' = ('$$IP_CLUSTER$$', 'Africa'), 'PLACEHOLDER' = ('$$IP_COUNTRY$$', '*'), 'PLACEHOLDER' = ('$$IP_FISCAL_YEAR$$', '*'), 'PLACEHOLDER' = '$$IP_REPORTING_DATE$$', '20200831'), 'PLACEHOLDER' = ('$$IP_AGEING_BUCKET$$', '''*'''), 'PLACEHOLDER' = ('$$IP_BUKRS$$', '*')) 
GROUP BY "SAPINSTANCE", "MCO", "COUNTRY", "BUKRS", "EntityName", "XBLNR", "HKONT", "TXT50", "GSBER", "BELNR", "BLART", "LTEXT", "CC_BLDAT", "CC_BUDAT", "WAERS", "MONAT", "SGTXT", "GJAHR", "BKTXT", "UMSKZ", "S_LTEXT", "AUFNR", "PROJK", "PRCTR", "EBELN", "KOART", "AUGBL", "CC_AUGDT", "REBZG", "LIFNR", "NAME1", "CC_SHKZG", "KOSTL", "BSCHL", "BUZEI", "EBELP", "KTOKS", "ZUONR", "XINTB", "XLOEB", "XSPEB", "XOPVW", "XKRES", "TYPE_WISE_CLASS", "DESCRIPTION", "MCLASS", "CC_NUM_OF_DAYS_AGEING", "CC_AGEING_BUCKET", "CC_GROUP_CURRENCY", "CC_REPORTING_DATE_VAR", "MARKET_CLUSTER", "HWAER", "CURR_UNIT_TO_EURO_1"

上述查询作为复制活动的输入，需要每月手动更新日期。请帮忙！

标签： sqldatesql-updateazure-data-factorycopy-activity

解决方案

我假设您想在使用预定事件（例如每周或每月）触发管道执行时使用“current_date”或类似的动态值。

您可以通过将查询与当前日期动态连接来实现这一点，或者在必要时执行更高级的日期函数操作：

@concat('SELECT FROM schema.table t WHERE reporting_date =', formatDateTime(utcnow(), 'yyyy-MM-dd'))

您可以在以下 url 中找到更详细的列表：https ://docs.microsoft.com/en-us/azure/data-factory/control-flow-expression-language-functions#date-functions

推荐阅读