Suggest an Idea

[priyankaraj]

Hello Qlik Team,

We have observed that Qlik Replicate does not create a table on target ADLS when source table has zero records. 

This is a big defect in the way the tool is designed. Some times tables may not have counts when they are loaded, but they are still used by downstream teams for union of table attributes / writing some logic.

We are not able to replicate the table - DFM and / JSON file. Please assist. 

[Shelley_Brennan]

Did you open a support case for this issue?  

[Shelley_Brennan]

Also what is the output you would like to see in the target when there are no rows in the source table?  We did not think it makes sense to create a DFM where there are no files.

[priyankaraj]

Hi Shelley,

It looks like I missed the updates from you on this.  A case was created and we were recommendation for IDEATION by Support + CSM.

We would like the DFM file with table structure, irrespective # of records in the table or not. 

Attaching one for your reference. We have a requirement to have just table DFM. We do not need JSON if zero records ar there. Thanks.

{
"dfmVersion": "1.1",
"taskInfo": {
"name": "MESLA17_oracle_to_adlsgen2",
"sourceEndpoint": "src_MESLA17",
"sourceEndpointType": "ORACLE",
"sourceEndpointUser": "xxattunity",
"replicationServer": "siwdapp5048.ced.corp.cummins.com",
"operation": "dataProduced"
},
"fileInfo": {
"name": "20210914-144915117",
"extension": "json",
"location": "/archive/raw/mesla17/FLXUSER.PRINT_REQUEST_HISTORY__ct/20210914T143500_20210914T144000",
"startWriteTimestamp": "2021-09-14T14:46:21.829",
"endWriteTimestamp": "2021-09-14T14:49:15.117",
"firstTransactionTimestamp": "2021-09-14T14:35:02.000",
"lastTransactionTimestamp": "2021-09-14T14:35:02.000",
"content": "changes",
"recordCount": 1018082,
"errorCount": 0
},
"formatInfo": {
"format": "json"
},
"dataInfo": {
"sourceSchema": "FLXUSER",
"sourceTable": "PRINT_REQUEST_HISTORY",
"targetSchema": "FLXUSER",
"targetTable": "PRINT_REQUEST_HISTORY__ct",
"tableVersion": 1,
"columns": [{
"ordinal": 1,
"name": "header__change_seq",
"type": "STRING",
"length": 35,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 2,
"name": "header__change_oper",
"type": "STRING",
"length": 1,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 3,
"name": "header__change_mask",
"type": "BYTES",
"length": 128,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 4,
"name": "header__stream_position",
"type": "STRING",
"length": 128,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 5,
"name": "header__operation",
"type": "STRING",
"length": 12,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 6,
"name": "header__transaction_id",
"type": "STRING",
"length": 32,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 7,
"name": "header__timestamp",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 8,
"name": "header__partition_name",
"type": "STRING",
"length": 32,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 9,
"name": "ID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 1
}, {
"ordinal": 10,
"name": "ORIGINALPRINTREQUESTID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 11,
"name": "REPORTTYPEID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 12,
"name": "REPORTID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 13,
"name": "EMPLOYEEID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 14,
"name": "EQUIPMENTID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 15,
"name": "PRINTERID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 16,
"name": "RAISEDON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 17,
"name": "KEY1",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 18,
"name": "KEY2",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 19,
"name": "KEY3",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 20,
"name": "KEY4",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 21,
"name": "KEY5",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 22,
"name": "REQUESTOR",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 23,
"name": "CONTENT",
"type": "NCLOB",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 24,
"name": "REQUEST",
"type": "NCLOB",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 25,
"name": "REFERENCEID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 26,
"name": "LASTUPDATEON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 27,
"name": "LASTUPDATEDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 28,
"name": "CREATEDON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 29,
"name": "CREATEDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 30,
"name": "ACTIVE",
"type": "INT1",
"length": 3,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 31,
"name": "LASTDELETEON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 32,
"name": "LASTDELETEDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 33,
"name": "LASTREACTIVATEON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 34,
"name": "LASTREACTIVATEDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 35,
"name": "ARCHIVEID",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 10,
"primaryKeyPos": 0
}, {
"ordinal": 36,
"name": "LASTARCHIVEON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 37,
"name": "LASTARCHIVEDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 38,
"name": "LASTRESTOREON",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 39,
"name": "LASTRESTOREDBY",
"type": "WSTRING",
"length": 50,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 40,
"name": "ROWVERSIONSTAMP",
"type": "NUMERIC",
"length": 0,
"precision": 38,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 41,
"name": "DL_HDP_CREATEDDTM",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 42,
"name": "HDISDELETEDRECORD",
"type": "INT1",
"length": 3,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}, {
"ordinal": 43,
"name": "DL_HDP_MODIFIEDDTM",
"type": "DATETIME",
"length": 0,
"precision": 0,
"scale": 0,
"primaryKeyPos": 0
}]
}
}

[priyankaraj]

@Shelley_Brennan 

May i get an update on the ask. How we can escalate this to get attention and possible resolution.

[priyankaraj]

Hello Tzachi, Product Management Team, and Community Moderators, 

Business justification for this requirement is that we need to have the table structure in the lake to accommodate future incremental changes  / data flow. If the DFM file is not there in the initial load, our CDC Framework (in house - merge framework), is not able to process that because the baseline table structure is not there. This poses significant risk for future incremental changes and putting them in the lake. Additionally, we have use cases where empty tables are loaded, which are used as look up tables / such logic. 

Please let me know if you have any additional questions or concerns.

[Shelley_Brennan]

Thank you for the additional details.  We will keep this under consideration as we do our future planning and continue to collect feedback from others in the meantime.

[priyankaraj]

Hi Shelley,

Thanks for your response and acknowledgement once again. At this point, it's not giving us, Cummins, anything substantial or working solution. 

I want to highlight to Tzachi the following: 

- I have worked with Qlik experts (CSE / PS) resources and we don't have a workaround. Ketan I tested with passing values in replicate and that is still not able to create the DFM / JSON. Stephen and i tried the "Metadata" only option that didn't work also. 

- We had internally discussed the possibility of creating dummy records for the tables an ensuring that the tables (dfm/json) gets created. The challenged posed by this resolution / temp work around is that, it is corrupting our Production Data which is not good. some business stakeholders are not comfortable doing that. 

Please let me know. We are still evaluating and validating the dummy records approach and of course it would require a much higher approval to proceed. We have tested in CORE / Testing environment and it work.

Can you please let me know if anything can be done PM side for this ideation? Thanks. @Shelley_Brennan 

[Tzachi_Nissim]

Hi,

I'm happy to join the conversation here. So, first of all - I think I understand the requirement. I will say that the view of the DFM files has been that these are metadata files that describe the data files. So, having metadata files that describe nothing, or an empty file is a bit odd so we may need to look at those a bit differently.

I would like to make sure that I understand by articulating this myself: For the initial load, you run the task and you get DFM files that describe all the tables, even if some have no matching data files. Correct? If you take it to the extreme - running the task in "metadata only" mode will result in a bunch of DFM files with no data. Would you agree? How about if we provided this option only in metadata only mode? Would that meet your requirement as well?

Next step is CDC. Do you expect that change tables should have the same behavior. That is a bit less obvious to me, so I would value your input on that part.

Thanks for raising this and we are actively looking at it.

Regards,

Tzachi

[priyankaraj]

Hi Tzachi,

Thank sooooo much for acknowledgment and consideration of this ideation. I truly appreciate it. Sorry my comment didn't transmit yesterday. Christina alerted me and I typed it during our call but my session timed out 😞 

Below is my response to your statements: 

I'm happy to join the conversation here. So, first of all - I think I understand the requirement. I will say that the view of the DFM files has been that these are metadata files that describe the data files. So, having metadata files that describe nothing, or an empty file is a bit odd so we may need to look at those a bit differently.

Priyanka: Thanks for describing the functionality / back end process for the DFM files. I totally agree and understand that tool is working as expected for  "describing the data files" (when there's data). In our case, in tables where the data doesn't exist yet for whatever reason and will be reflected in source system in future, we want to be able to just describe / create DFM for just the table structure. Furthermore, while data is not a required to be in source system, the downstream / analytics team sometimes just require the table / structure for their reports / ETL. This is a very valid use case for those and this is A VERY COMMON occurrence in Cummins / elsewhere. The ability to read metadata irrespective of records in table or not should be a functionality that should be introduced. E.g. This works RDBMS targets (SQL DB / Exadata). We would like the DFM created and JSON file, so if counts are zero in source, can the behavior be set to have like that?

I would like to make sure that I understand by articulating this myself: For the initial load, you run the task and you get DFM files that describe all the tables, even if some have no matching data files. Correct? If you take it to the extreme - running the task in "metadata only" mode will result in a bunch of DFM files with no data. Would you agree? How about if we provided this option only in metadata only mode? Would that meet your requirement as well?

Priyanka: For the initial load, we only get DFM files when the tables at source side have records in them. Any tables with zero records / counts, doesn't populate a DFM  or JSON file in the lake (even for initial load). We have tried passing a value, doing metadata load only but nothing works. Running the task in metadata only mode doesn't work for our use case where tables have no records. To your point above, the metadata / DFM is based of  replicate process reading data and then populating it. When data doesn't exist for "abc" or "xyz" table, it doesn't populate their DFM (ever). To your point, metadata only capability would totally work for us provided that it reads tables structures / DDLs without data in it! That's exactly what we are asking for! 

Next step is CDC. Do you expect that change tables should have the same behavior. That is a bit less obvious to me, so I would value your input on that part.

Priyanka: I think as long as the first piece is there, and then CDC comes, we are fine. 

Thanks for raising this and we are actively looking at it.

Thanks so much for all of your help and review. If call is needed, we can set that up.

[Tzachi_Nissim]

Hi Priyanka,

Thanks for confirming - everything looks clear to me. I think this is very reasonable and makes sense. 

Regards,

Tzachi