tstack
Former Employee
Joined 9 years ago
User Profile
User Widgets
Contributions
Re: Change datatypes dynamically of every column
vsunilbabu: This is a CSV file so every column comes as a string. I want to create a pipeline which will look at the first row and change the datatype of that particular column using mapper. The CSVParser has some functionality for doing type conversion, but the types are expected to be in a separate file (see the Input Views section of the doc). If you are not able to get your data in that form, I’m attaching an example pipeline that might do what you want. This pipeline uses a Router snap to split the first row off and then a Join to merge it back in with all the remaining rows. A Mapper snap is then used to do the type conversion with the following expression: $.mapValues( (value, key) => match $types.get(key) { 'char' => value, 'integer' => parseInt(value), 'date' => Date.parse(value, "dd/mm/YY"), _ => value } ) Since that’s a little involved, I’ll go into some more detail. First, the mapValues() method is used to rewrite the value of each property in the input document. That method takes a callback that does the actual work. The callback uses the match operator to check the type of each property and then executes the conversion expression (e.g. the type of “Priority” is “integer”, so the match arm with parseInt(value) is executed). TypeConversion_2019_09_16.slp (10.5 KB)3.4KViews1like3CommentsRe: Update statement in Oracle excute for JSON format
pranil2k2: I am trying to use Oracle execute to design Update statement. I am using below syntax, but it is throwing error. I think did we need to extra cautious to handle single quote and double quote, which is part of syntax. Please someone can advice on this one. “UPDATE SMRT_TEST SET xx_DOCUMENT = “’”{“TOTAL_CR_WRNO” :’” + $TOTAL_CREATED_WRNO + “’}”’“)” First, can I ask if there is a reason you are not using the Oracle Update snap? If you do need to do this in the Execute snap, I would suggest that you not make the Statement property an expression since it is difficult to construct the statement correctly (as you have found out). You can do substitutions of values from the input document when the Statement is not a property by using a JSON-Path. So, you could put a Mapper before the Execute snap to generate the JSON string using an expression like the following: JSON.stringify({TOTAL_CR_WRNO: $TOTAL_CREATED_WRNO}) If that expression was mapped to the output document as $xx_DOCUMENT , you can then reference it in the Execute snap’s Statement property like so: UPDATE SMRT_TEST SET xx_DOCUMENT = $xx_DOCUMENT3.4KViews0likes0CommentsRe: Getting the Status from a REST call
Another option is to use the matches operator that can check a value against a pattern. The nice thing about this approach is that the pattern looks roughly like the value being matched and the type of the value is implicitly checked. So, an error won’t be raised if the value has an unexpected type. For your first example: TimBurns: So the rest call will return on success: “entity”: { “MessageId”:“0101017165ec422f-dfa2bb38-faac-4c48-bcd7-0e2869d80fb6-000000”, … } The object pattern, { MessageId } , would only match objects that contain the property MessageId . The full expression would look like this: $entity matches { MessageId } For the second example: TimBurns: And on Error: “entity”: "ERROR: An error occurred … A string pattern that uses the ... wildcard can check for a value with a prefix. The full expression would look like: $entity matches "ERROR:"... (Unfortunately, while there is documentation for the match operator, the documentation for matches seems to be missing at the moment. We’ll get that fixed)3.1KViews1like1CommentRe: Formatting JSON data according to given scenarios
vaidyarm: let itemlen = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item.length; for (i = 0; i < itemlen; i++) { let BlockAssignmentlen = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].AccountingCodingBlockDistribution.AccountingCodingBlockAssignment.length for (j = 0; j < BlockAssignmentlen; j++) { newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].AccountingCodingBlockDistribution.AccountingCodingBlockAssignment[j][“lineItemId”] = newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[i].lineItemId } }; The wildcard JSON-Path approach should work here as well, but I think you’ll need to use the Mapping Root feature of the Mapper to iterate over the outer array. So, in this case, you’d probably use the following as the Mapping Root in the Mapper: $newPayload.PurchaseOrderERPRequest_V1.PurchaseOrder.item[*] That tells the Mapper to apply the mappings in the table to all of the elements in the PurchaseOrder.item array. Then, you would add a row to the mapping table that read the lineItemId : $lineItemId And, then wrote it to every element of the AccountingCodingBlockAssignment array, using this Target Path: $AccountingCodingBlockDistribution.AccountingCodingBlockAssignment[*].lineItemId1KViews0likes1CommentRe: Error while saving file in groundplex
munish: detail: /test6.json (Permission denied) That path is the root of the file system, which the snaplex software likely does not have access to (hence the Permission Denied error). Maybe try writing into /tmp . So, something like file:///tmp/myfile.json .1.9KViews0likes0CommentsRe: Select count not working base in variable
hina_walia: Below is the sql in snowflake execute SELECT count(*) as cnt FROM FDR_SOURCE.KRONOS_LABORtest WHERE SOURCE_FILENAME = _Name If i pass some fixed value, instead of _Name, then pipeline work fine. if i use param (_Name), it throws below error. In order to access values from input documents (or other expression language variables) in a SQL statement, you need to use a JSON-Path. For example, if there was a document going into this snap, you would reference a property in it using $name . In the case of pipeline parameters though, it’s a little different. You need to use $.eval(<expr>) to evaluate an expression and then you put the pipeline parameter reference in there. So, you would write: SELECT count(*) as cnt FROM FDR_SOURCE.KRONOS_LABORtest WHERE SOURCE_FILENAME = $.eval(_Name) Note that this approach is the safest since it is using bound variables, so there is no possibility of a SQL injection vulnerability.2.1KViews0likes0CommentsRe: Run error pipeline on different snaplex
vaidyarm: we were having similar configuration earlier but as with 4.20, 8MB input limit imposed on pipeline execute snap is not allowing to do so. hence we switching to error pipelines how above case can be archived considering this limitation ? The limit is only for the pipeline parameters. If the data is large, you can pass it as a regular document or a binary document to the unlinked input view of the child.3KViews0likes0CommentsRe: Is there a way to insert a row number while mapping an object array?
wcl3y2: One of the required fields is line number for each of the invoice lines. I am trying to generate this in the mapper snap, and I cannot figure out a way I think you can use the .map() method on Array objects to do this since the callback will be passed the current index into the array. The following expression does this: $lines.map((elem, index) => { lineNumber: index + 1, ...elem }) The .map() method will iterate over each element in the $lines array and execute the given callback function (the part that starts with (elem, index) => . The callback function creates a new object with the new lineNumber property and then uses the spread operator ( ...elem ) to add in the properties from the original array element. Here’s a pipeline that demonstrates this: Community7085_2020_03_16.slp (3.6 KB)5.8KViews3likes1CommentRe: After using a JSON splitter and a filter, how do you put the object/array back together?
travis.pendleton: How do I get it back into an object so I can map it correctly? I think the GroupByN snap will do what you want. If you set the “Group Size” to zero, it will collect all of the input documents. travis.pendleton: Hi, we needed to filter on a JSON input. We managed to get that working by using a JSON splitter on the element and then piping that to a filter snap. Another approach would be to use the expression language. You could call the filter() method on the array with a callback function that checks the condition. For example, if you wanted to filter the array by the state property, you could do something like this: $allLocations.location.filter(elem => elem.state == "US-TX")1.7KViews1like1Comment