Skip to content

README.md

Latest commit

 

History

History
504 lines (465 loc) · 16.2 KB

README.md

File metadata and controls

504 lines (465 loc) · 16.2 KB
Raw

Example Control Table Records

Important Notes

  1. All control table records require trailing forward slash for SinkObjectSettings.folderPath

Landing to Raw and Raw to Staging

  1. SourceObjectSettings.FileName and SourceObjectSettings.FolderPath should utilize wildcards (%)
    • If the pipeline must move over any parquet file in a folderPath, SourceObjectSettings.FileName = "%.parquet"
    • Folderpaths will include YYYY/MM/DD at end because naming pattern is autogenerated by prior pipelines. So SourceObjectSettings.FolderPath should include a % after the base path. For example, Finance/SAP/%

Source to Landing

  1. Multiple control table records can be assigned the same trigger name. To enable automated ingestion, a trigger needs to be created in Azure Data Factory with the same name as indicated in the control table record
    • For testing, you can manually trigger the PL_1_Source_to_Landing_Step1 pipeline and input the trigger name from the control table record in the TriggerNameOverride parameter

Source to Landing Control Table Records

Oracle

  1. Example Syntax to Add Oracle Record to the Control Table for Source to Landing in case of

    • watermark column data type: DateTime + No Partition in source table

    Values enclosed in [] to be replaced with actual values as per source setup like "schema": "[INSERT SCHEMA NAME]" can be as "schema": "dbo"

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "schema": "[INSERT SCHEMA NAME]", "table": "[INSERT TABLE NAME]", "query": "SELECT * FROM [INSERT SCHEMA NAME].[INSERT TABLE NAME] WHERE [INSERT WATERMARK COLUMN] > TO_TIMESTAMP(''WATERMARKVALUE'', ''YYYY-MM-DD HH24:MI:SS.FF'')" }'
--SourceConnectionSettingsName
,'{ "keyVaultSecretName": "[INSERT KV SECRET NAME]" }'
--CopySourceSettings
,'{ "watermark_column": "[INSERT WATERMARK COLUMN NAME]", "watermark_column_data_type": "Datetime", "partitioningOption": "None"}'
--SinkObjectSettings
,'{ "fileName": "[INSERT CUSTOM FILE NAME PATTERN]_YYYYMMDDHHMMSS.parquet", "folderPath": "finance/sap/", "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_1_Source_to_Landing_Step1'
--TriggerName
,'[INSERT TRIGGER NAME]'
--DataLoadingBehaviorSettings
,'{ "ingestionPattern": "Oracle" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

  1. Example Syntax to Add Oracle Record to the Control Table for Source to Landing in case of

    • watermark column data type: Integer + No Partition in source table

    Values enclosed in [] to be replaced with actual values as per source setup like "schema": "[INSERT SCHEMA NAME]" can be as "schema": "dbo"

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "schema": "[INSERT SCHEMA NAME]", "table": "[INSERT TABLE NAME]", "query": "SELECT * FROM [INSERT SCHEMA NAME].[INSERT TABLE NAME] WHERE [INSERT WATERMARK COLUMN] > WATERMARKVALUE }'
--SourceConnectionSettingsName
,'{ "keyVaultSecretName": "[INSERT KV SECRET NAME]" }'
--CopySourceSettings
,'{ "watermark_column": "[INSERT WATERMARK COLUMN NAME]", "watermark_column_data_type": "Integer", "partitioningOption": "None"}'
--SinkObjectSettings
,'{ "fileName": "[INSERT CUSTOM FILE NAME PATTERN]_YYYYMMDDHHMMSS.parquet", "folderPath": "[INSERT CUSTOM FOLDER NAME PATTERN]", "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_1_Source_to_Landing_Step1'
--TriggerName
,'[INSERT TRIGGER NAME]'
--DataLoadingBehaviorSettings
,'{ "ingestionPattern": "Oracle" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

  1. Example Syntax to Add Oracle Record to the Control Table for Source to Landing in case of

    • watermark column data type: DateTime + Physical Partition in source table

    Values enclosed in [] to be replaced with actual values as per source setup like "schema": "[INSERT SCHEMA NAME]" can be as "schema": "dbo"

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "schema": "[INSERT SCHEMA NAME]", "table": "[INSERT TABLE NAME]", "query": "SELECT * FROM [INSERT SCHEMA NAME].[INSERT TABLE NAME] WHERE [INSERT WATERMARK COLUMN] > TO_TIMESTAMP(''WATERMARKVALUE'', ''YYYY-MM-DD HH24:MI:SS.FF'')" }'
--SourceConnectionSettingsName
,'{ "keyVaultSecretName": "[INSERT KV SECRET NAME]" }'
--CopySourceSettings
,'{ "watermark_column": "[INSERT WATERMARK COLUMN NAME]", "watermark_column_data_type": "Datetime", "partitioningOption": "PhysicalPartitionsOfTable"}'
--SinkObjectSettings
,'{ "fileName": "[INSERT CUSTOM FILE NAME PATTERN]_YYYYMMDDHHMMSS.parquet", "folderPath": "[INSERT CUSTOM FOLDER NAME PATTERN]", "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_1_Source_to_Landing_Step1'
--TriggerName
,'[INSERT TRIGGER NAME]'
--DataLoadingBehaviorSettings
,'{ "ingestionPattern": "Oracle" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

  1. Example Syntax to Add Oracle Record to the Control Table for Source to Landing in case of

    • No Watermark Value + Physical Partition in source table

    Values enclosed in [] to be replaced with actual values as per source setup like "schema": "[INSERT SCHEMA NAME]" can be as "schema": "dbo"

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "schema": "[INSERT SCHEMA NAME]", "table": "[INSERT TABLE NAME]", "query": "SELECT * FROM [INSERT SCHEMA NAME].[INSERT TABLE NAME]" }'
--SourceConnectionSettingsName
,'{ "keyVaultSecretName": "[INSERT KV SECRET NAME]" }'
--CopySourceSettings
,'{ "watermark_column": "", "watermark_column_data_type": "", "partitioningOption": "PhysicalPartitionsOfTable"}'
--SinkObjectSettings
,'{ "fileName": "[INSERT CUSTOM FILE NAME PATTERN]_YYYYMMDDHHMMSS.parquet", "folderPath": "[INSERT CUSTOM FOLDER NAME PATTERN]", "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_1_Source_to_Landing_Step1'
--TriggerName
,'[INSERT TRIGGER NAME]'
--DataLoadingBehaviorSettings
,'{ "ingestionPattern": "Oracle" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

M365

  1. Example Syntax to Add M365 Record to the Control Table for Source to Landing

    Required fields may vary by dataset

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "tableName": "[INSERT TABLE NAME]", "scopeFilter": "[INSERT LIMITED SCOPE OR LEAVE BLANK FOR ALL]", "filterOnDate": [True OR False] }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
,'{ "dateFilterColumn": "[INSERT DATE FILTER COLUMN - LEAVE BLANK IF filterOnDate SET TO False]", "startDate": "[INSERT START DATE - LEAVE BLANK IF filterOnDate SET TO False]", "endDate": "[INSERT END DATE - LEAVE BLANK IF filterOnDate SET TO False]","watermark_column_data_type":"[DateTime OR NA IF filterOnDate SET TO False]","outputColumns":[{"name":"[INSERT COLUMN 1]"},{"name":"[INSERT COLUMN 2]"}...}'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Department/Datasource/Schema/Dataset/", "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_1_Source_to_Landing_Step1'
--TriggerName
,'[INSERT TRIGGER NAME]'
--DataLoadingBehaviorSettings
,'{ "ingestionPattern": "M365" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

Landing to Raw Control Table Records

  1. Example Syntax to Add an Excel Record to the Control Table
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.xlsx", "folderPath": "%/%", "container": "landing" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
,'[{ "SheetName": "Sheet1", "HeaderRow": "1"}, { "SheetName": "Sheet2", "HeaderRow": "1"}]'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": null, "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_2_Process_Landed_Files_Step2'
--TriggerName
,'TR_FileCreated_Landing_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Extract_Excel_Sheets" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)
  1. Example Syntax to Add a CSV Record to the Control Table
    • Allowed values for compression for None, bzip2, gzip, deflate
    • 2 landing to raw control table records are required for each dataset. One for full/initial load and one for incremental data loads
      • DataLoadingBehaviorSettings.loadType = "incremental" for incremental
      • DataLoadingBehaviorSettings.loadType = "full" for full initial load
      • SourceObjectSettings.folderPath should be different for each control table record
      • All other values should be the same between the 2 control table records
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.csv", "folderPath": "%/%", "container": "landing" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
--Allowed values for compression for "None", "bzip2", "gzip", "deflate"
,'{ "fileType": "delimitedText", "delimiter": ",", "compression": "None"}'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Department/Datasource/Dataset/", "container": "raw" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_2_Process_Landed_Files_Step2'
--TriggerName
,'TR_FileCreated_Landing_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Copy_to_Raw", "loadType": "full" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)
  1. Example Syntax to Add a Parquet Record to the Control Table
    • Leave compression as blank
    • 2 landing to raw control table records are required for each dataset. One for full/initial load and one for incremental data loads
      • DataLoadingBehaviorSettings.loadType = "incremental" for incremental
      • DataLoadingBehaviorSettings.loadType = "full" for full initial load
      • SourceObjectSettings.folderPath should be different for each control table record
      • All other values should be the same between the 2 control table records
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.parquet", "folderPath": "Finance/SAP/%", "container": "landing" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
--Leave compression as blank
,'{ "fileType": "parquet", "compression": ""}'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Finance/SAP/", "container": "raw" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_2_Process_Landed_Files_Step2'
--TriggerName
,'TR_FileCreated_Landing_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Copy_to_Raw", "loadType": "incremental" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)
  1. Example Syntax to Add a JSON Record to the Control Table
    • Allowed values for compression for None, bzip2, gzip, deflate
    • Set multiline to true if a single json record spans multiple lines in the document
    • 2 landing to raw control table records are required for each dataset. One for full/initial load and one for incremental data loads
      • DataLoadingBehaviorSettings.loadType = "incremental" for incremental
      • DataLoadingBehaviorSettings.loadType = "full" for full initial load
      • SourceObjectSettings.folderPath should be different for each control table record
      • All other values should be the same between the 2 control table records
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.json", "folderPath": "%/%", "container": "landing" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
--Allowed values for compression for "None", "bzip2", "gzip", "deflate"
--Set multiline to true if a single json record spans multiple lines in the document
,'{ "fileType": "json", "multiline": false, "compression": "None"}'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Department/Datasource/Dataset/", "container": "raw" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_2_Process_Landed_Files_Step2'
--TriggerName
,'TR_FileCreated_Landing_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Copy_to_Raw", "loadType": "incremental" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)
  1. Example Syntax to Add a PDF/Image Record to the Control Table for later form recognizer extraction 
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.PDF/JPEG/JPG/PNG/BMP/TIFF", "folderPath": "%/%", "container": "landing" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
--model examples include: 'prebuilt-invoice', 'prebuilt-receipt', 'prebuilt-tax.us.w2', 'prebuilt-idDocument', 'prebuilt-businessCard'
,'{ "model": "prebuilt-invoice" }'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": null, "container": "landing" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_2_Process_Landed_Files_Step2'
--TriggerName
,'TR_FileCreated_Landing_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Form_Recognizer_Extraction" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)

Raw to Staging Control Table Records

Notes

  1. SourceObjectSettings.folderPath - The wildcard folder path needs to match the actual folder path of the data that lands in raw.

    • Do not include the version, incremental/full, or date in the folder path

  2. CopySourceSettings.partition_cols can be set to "" if no partition columns should be used

  3. If a date/datetime column should be used for partitioning:

    • CopySourceSettings.partition_cols should include all or at least one of the following columns: ''CalcYear'', ''CalcMonth'', ''CalcDayOfMonth''
    • CopySourceSettings.date_partition_column should be set to the name of the date/datetime column
      • For example: "date_partition_column": "Modified_Date"

  4. Example Syntax to Add a Record to the Control Table for Raw to Staging for Json data in raw

INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.json", "folderPath": "%/%", "container": "raw" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
,'{ "primary_key_cols": "[''primaryColName1'', ''primaryColName2'', ''etc'']", "partition_cols": "[''partitionCol1'', ''partitionCol2'', ''etc'']", "date_partition_column":"", "file_type":"json" }'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Department/Datasource/Schema/Dataset/", "container": "staging" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_3_MoveToStaging_Step2'
--TriggerName
,'TR_FileCreated_Raw_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Copy_to_Staging" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)
  1. Example Syntax to Add a Record to the Control Table for Raw to Staging for Parquet data in raw
INSERT INTO [dbo].[ControlTable]
VALUES (
--SourceObjectSettings
'{ "fileName": "%.parquet", "folderPath": "%/%", "container": "raw" }'
--SourceConnectionSettingsName
,''
--CopySourceSettings
,'{ "primary_key_cols": "[''primaryColName1'', ''primaryColName2'', ''etc'']", "partition_cols": "[''partitionCol1'', ''partitionCol2'', ''etc'']", "date_partition_column":"", "file_type":"" }'
--SinkObjectSettings
,'{ "fileName": null, "folderPath": "Department/Datasource/Schema/Dataset/", "container": "staging" }'
--SinkConnectionSettingsName
,''
--CopySinkSettings
,''
--CopyActivitySettings
,''
--TopLevelPipelineName
,'PL_3_MoveToStaging_Step2'
--TriggerName
,'TR_FileCreated_Raw_EventLog'
--DataLoadingBehaviorSettings
,'{ "dataLoadingBehavior": "Copy_to_Staging" }'
--TaskId
,0
--CopyEnabled
,1
--DataContract
,'{}'
--PurviewScanEnabled
,1
)