ETL Select Columns "Shortcut"

EChaney
EChaney Member
in Magic ETL

Hello, I work with Datasets with high counts of columns. When I create ETL's where I have to Select Columns, I can be going through 150+ columns at a time to choose the columns I need. If I only have a handful to add, scrolling through the list and adding one by one is not really an issue, but when I need to add 30+, it get's very time consuming scrolling through the list over and over to add each column. Adding all columns, and then going through the list to remove the columns I don't need, takes just as long when I'm only selecting, say, 40 columns out of the 150+. 

 

Is there a faster way to add multiple columns (30+), than going through the whole list of 150+ fields and selecting columns one by one? For example, is there a way to select multiple columns at once to add, and then once added, go down the selected list and set the properties for each? 

 

If not, having selection boxes next to the column names, or some other way of choosing multiples to add, would be a great enhancement for future upgrades. Thank you!

 

Note: Due to the size of our company, count of users, and the amount of data in some of our Datasets (Millions of rows), access to Workbench is limited to our IT Dept, so I do not have access.

Best Answer

  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you are just doing SELECT statements, it is not too daunting. I would encourage you to review this KB article that should give you enough basic knowledge to do what you want to do.

    https://knowledge.domo.com/Prepare/Magic_Transforms/SQL_DataFlows/01Creating_an_SQL_DataFlow

    You would select your source dataset as the input dataset and then add a transform and choose table to then select the columns that you want.

     

    A basic SELECT statement would look like this

    SELECT `date`, `value`, `series`
    FROM input_dataset_1

    This would return just those 3 columns from your dataset, for example. While it won't let you drag and drop columns into your SELECT statement, you could highlight the columns from the list on the left when you are in the transform editor and then paste them into a text editor or even Excel and then remove the columns you don't want and add the necessary comma after each column name. 

    You would then add an output dataset and then you are done. 

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    In the select columns, you can use type-ahead to slightly ease the pain of selecting your columns, but you are still limited to do it one at a time. This has sped it up for me as I have a dataset with nearly 600 columns. 

    Perhaps a better option would be to use a SQL dataflow whose only purpose is to select the columns that you want as you could then take advantage of copy and paste functionality and other text editor type features. You would then use the output dataset as the new input dataset in the ETL. 

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • DomoJesseK
    DomoJesseK Domo Employee

    I usually create a separate dataset that has only the most commonly-used fields. For example, a dataset containing all 300+ cols related to a Salesforce activity, then, in the same dataflow that produces the larger set, a second output set containing only the 50 most popular cols. That's the one I generally join to via ETL. Then you can just add all columns.

    Jesse

    Dojo Admin
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • EChaney
    EChaney Member
    Thank you for your input Mark! Unfortunately, I am not well versed in SQL (slowly learning though), which limits me quite a bit right now.
    I am new to Domo and we have hundreds of datasets and billions of rows of data, so I am learning as I go.
  • MarkSnodgrass
    MarkSnodgrass Coach
    Answer ✓

    If you are just doing SELECT statements, it is not too daunting. I would encourage you to review this KB article that should give you enough basic knowledge to do what you want to do.

    https://knowledge.domo.com/Prepare/Magic_Transforms/SQL_DataFlows/01Creating_an_SQL_DataFlow

    You would select your source dataset as the input dataset and then add a transform and choose table to then select the columns that you want.

     

    A basic SELECT statement would look like this

    SELECT `date`, `value`, `series`
    FROM input_dataset_1

    This would return just those 3 columns from your dataset, for example. While it won't let you drag and drop columns into your SELECT statement, you could highlight the columns from the list on the left when you are in the transform editor and then paste them into a text editor or even Excel and then remove the columns you don't want and add the necessary comma after each column name. 

    You would then add an output dataset and then you are done. 

    Hope this helps.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • ST_-Superman-_
    ST_-Superman-_ Coach

    You can also use 

    ALTER TABLE your_table DROP COLUMN `Column1`,`Column2`,...;

    If you are only looking to remove a few columns.  (This would be in a MySQL dataflow as well)

     


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman

Categories