+1Hello:
I would like to use GraphQL queries to extract results of a monitoring project - and load the results into my own database table for reporting and other purposes. I need to make it split the invalid records - in a way - that each record should be for one failed rule - even if the primary key record - has multiple DQ issues (failed rules).
Can some one help me with sample code on how you may have accomplished it - if you did such a thing?
Hi Prasad.
I would not recommend directly calling against a GraphQL query to access monitoring project results. This is as the GraphQL APIs are unversioned and subject to change between releases, increasing maintenance considerations for your pipeline.
Instead, I would point you to use a Post Processing Plan for each dataset in the monitoring project (if you are on v14 or earlier, this will be in ONE Desktop, but in v15 onwards can be done through the Web App). When you generate the post processing plan, it will be a template that returns the source dataset + the DQ results (both pass and fail).
You can then use a Filter step to filter for invalid rows of data where that have failed (will be along the lines of `invalid_rules IS NOT NULL`.
Because this dataset shows as 1 row of data per 1 row of source data, all rule failures for each row are combined together in a single string (e.g. `failed_rule_1;failed_rule_56;failed_rule_78`). You can therefore use a Splitter step, and split by ‘;’ to explode out multiple reasons for rule failures into 1 line for each rule failure.
You can then finally use a JDBC writer step to write to your database.
Hope this helps!
-Oliver
Hi Prasad.
I would not recommend directly calling against a GraphQL query to access monitoring project results. This is as the GraphQL APIs are unversioned and subject to change between releases, increasing maintenance considerations for your pipeline.
Instead, I would point you to use a Post Processing Plan for each dataset in the monitoring project (if you are on v14 or earlier, this will be in ONE Desktop, but in v15 onwards can be done through the Web App). When you generate the post processing plan, it will be a template that returns the source dataset + the DQ results (both pass and fail).
You can then use a Filter step to filter for invalid rows of data where that have failed (will be along the lines of `invalid_rules IS NOT NULL`.
Because this dataset shows as 1 row of data per 1 row of source data, all rule failures for each row are combined together in a single string (e.g. `failed_rule_1;failed_rule_56;failed_rule_78`). You can therefore use a Splitter step, and split by ‘;’ to explode out multiple reasons for rule failures into 1 line for each rule failure.
You can then finally use a JDBC writer step to write to your database.
Hope this helps!
-Oliver
+1Oliver:
Have you tried the Metadata reader to extract DQ results? Do you have any template that you could share ?
Hi Prasad.
A few thoughts here:
I do not believe it possible to use metadata readers to extract DQ results from monitoring projects vs using postprocessing plans. Regardless, if you directly ran a plan/workflow in this way, then this would not be pushed down to Snowflake
You may be interested in Data Export projects - however they operate directly against tables and not monitoring projects.
I appreciate that this situation is not ideal for you, and I have passed this feedback to our Product team
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
