How to append data to a table automatically?

Reply
Highlighted
Yellow Belt

How to append data to a table automatically?

Hi all,

 

I have been allowed to query our main table only 1 day at a time to avoid tech issues (MariaDB).

 

I am wondering how I could pull say previous day's data and then append to all the previous data pulled. Each row has a unique ID so this should be easy to avoid duplicates.

 

The other question is - what would be the best suggestion to do this manually to kick off the database? I need like the last 90 days of data and then I can switch to 1x day updates with append.

 

Thank you in advance


Accepted Solutions
Highlighted
Green Belt

Re: How to append data to a table automatically?

@Sandis - I'd build a recursive dataflow to capture and collect each days data so you have 1 dataset with all of the data. 

 

Something like this using Magic:

 

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_ETL_D...

 

Or this using SQL:

 

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_SQL_D...

View solution in original post

Highlighted
Black Belt

Re: How to append data to a table automatically?

The first thing I would look at is changing your initial dataset that is populated by the MariaDB Connector from replace to append. Go to that Connector Dataste and click on Settings and then Update Mode and see if that is set to Replace. Try changing that to Append. This will append each day's data to this dataset and you don't have to do anything else. 

If this doesn't work for you, then you would need to do something in the Magic ETL. @trafalger  just mentioned the Recursive Dataflow, which is a common method to append data. 

Since you mentioned each row has a unique ID, you might be able to get away with just using the Remove Duplicates tile in the ETL to remove any duplicate entries and you would be set.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Highlighted
Black Belt

Re: How to append data to a table automatically?

Are you using something like Domo Workbench to query your source data and then pushing it to Domo? If so, you could limit your query to just the previous days data if you have a date column in your dataset by using a WHERE clause like: 

WHERE TransactionDate = GETDATE()-1

 

In Domo, you can set your dataset to append rather than replace so that the new data will just be added to the dataset.

 

By default, Replace is the update method selected. Every time the DataSet runs, all new data brought in, based on the selections made under the Settings tab, will replace the data from the previous run.

When using the Append method, all of the new data brought in based on the selections made under the Settings tab will be added to the bottom of the existing DataSet table.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Tags (1)
Highlighted
Yellow Belt

Re: How to append data to a table automatically?

Hi @MarkSnodgrass 

I'm very new to this so I wouldn't know much about Workbench.

 

However, I have set up MariaDB Connector with a query and it pulls in yesterday's data and it updates 1x per day pulling previous day. So far so good.

 

So what I cannot figure out is what to do next as this is the source DataSet but how do I create the "repository" where the data will be merged in moving forward?

 

Do I need the first time to build ETL that just saves the data into a new (final dataset)?

And then reconnect and start merging the two together? Aka final + yesterday's data?

 

 

Highlighted
Green Belt

Re: How to append data to a table automatically?

@Sandis - I'd build a recursive dataflow to capture and collect each days data so you have 1 dataset with all of the data. 

 

Something like this using Magic:

 

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_ETL_D...

 

Or this using SQL:

 

https://knowledge.domo.com/Prepare/DataFlow_Tips_and_Tricks/Creating_a_Recursive%2F%2FSnapshot_SQL_D...

View solution in original post

Highlighted
Black Belt

Re: How to append data to a table automatically?

The first thing I would look at is changing your initial dataset that is populated by the MariaDB Connector from replace to append. Go to that Connector Dataste and click on Settings and then Update Mode and see if that is set to Replace. Try changing that to Append. This will append each day's data to this dataset and you don't have to do anything else. 

If this doesn't work for you, then you would need to do something in the Magic ETL. @trafalger  just mentioned the Recursive Dataflow, which is a common method to append data. 

Since you mentioned each row has a unique ID, you might be able to get away with just using the Remove Duplicates tile in the ETL to remove any duplicate entries and you would be set.




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Highlighted
Yellow Belt

Re: How to append data to a table automatically?

Thank you both - turned out that default instruction in the end was the correct way to go, just the data manipulations didn't seem to make sense, but now that it works, it makes a lot more sense. Thank you all!

Highlighted
Green Belt

Re: How to append data to a table automatically?

And I agree with @MarkSnodgrass, if you can change the connector to append it's a 30-second fix instead of building out a recursive dataflow. 

Highlighted
Yellow Belt

Re: How to append data to a table automatically?

Thanks, the SQL storage was the thing that solved the problem. I was able to manually run the queries to pick up previous days and now 1x per day it will add the previous day's stats. I then run a simple ETL to de-dupe any data just in case. Sometimes the best solutions are the simplest ones 🙂

Announcements
Stand out in the Dojo: Choose your own recognizable username and add a unique profile photo in your profile settings.