Transformation and CTE with Datameer

2 followers
0
Avatar

Curious question.

Currently I have an ETL pipeline setup using SSIS. What this does it takes a set of files and loads them into a SQL Server. From there we import the data into Datameer.

My question, is I'd like to remove the SQL Server ingestion part. Since Datameer stores data, along with having scheduled import jobs. I'd like to use it as my primary ETL solution.


I do have a few road blocks though. For example, I have 5 data types in a data set that are an amount value but the source system will represent negative values with a trailing negative symbol e.g., ("44.23-") is there anyway within Datameer I can move the trailing symbol the head of the value.

Another operation I perform is CTE using an SQL script. Is there anyway I can perform this in Datameer?

 

Thanks

Steven Marrocco

3 comments

  • Avatar
    Alan Mark

    Hi Steven,

    1. You can definitely move the negative symbol in a few ways. 

    • You would import it as a string value.
    • You could use a Regex to remove the trailing -, and add a leading - in the same step.  You can work on such an expression at http://regex101.com.  I regularly use this tool to build regular expressions.
    • You could alternatively do a two step process where you use our RTRIM function to strip the trailing - sign, then in a new column add the leading minus sign with CONCAT.

    2. Can you please clarify the terminology here?  I'm unfamiliar with "CTE".  My general feeling here though is you can replicate the behavior of any SQL script using Datameer functions.  I guess a good starting point for you would be to look over all of the existing functions to see if you have equivalents for the type of manipulations you're doing: Datameer Functions 

    0
  • Avatar
    Steven Marrocco

    Hey Alan,

     

    Thanks for the steps! will for sure look into it. 

    • "you would import it as a string value" - Does this assume I move the "-" within a workbook? I'd like to do as much as a I can during the import
    • Using Regex worked for me using the site provided (thank you!) Though, is there anyway to apply it to the import job or the data set itself? I wouldn't want users to have to contentiously add the regex along with other functions when editing creating new workbooks 

    Sorry, should have provided more detail. Pretty much we have Hierarchical data in a table format. With a Parent Child node relation. A parent can have a many child nodes along with grand child nodes that rollup into the first child node, etc. I use CTE which kind of works recursively to continuously go through each parent child, child grandchild relation until no more grandchildren are returned. The output of this would ignore the child - grandchild relation and directly map it to the parent node for a result like:
    Parent : Child
    Parent : Grandchild


    Let me know if that makes sense. Fairly tricky for myself to explain.

    0
  • Avatar
    Alan Mark

    Hi Steven,

    Unfortunately Importing data is a relatively simple process that doesn't allow for regular expressions or even complex SQL - just basic SELECT statements.

    You'll only be able to achieve this type of manipulation using a Workbook.  The suggested workflow for a scenario where you don't want users to have to enter these types of things - is have a data prep Workbook that they will use as their source data that does this work for them, rather than having them work directly off import jobs and data links.  This does have the stipulation that such a prep workbook needs to be created for each dataset.

    0
Please sign in to leave a comment.