How to append data to a table automatically?

Sandis

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

trafalger

@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_DataFlow

 

Or this using SQL:

 

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

MarkSnodgrass

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.

MarkSnodgrass

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.

Sandis

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?

 

 

trafalger

@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_DataFlow

 

Or this using SQL:

 

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

MarkSnodgrass

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.

Sandis

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!

trafalger

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. 

Sandis

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 ?