Adding a date field into an existing data set using SQL in Magic ETL

user054938

Hi Everyone, I have an existing data set that I'm trying to add today's business date to. I need to the data set with the business date field as a part of a data flow. I'm trying to use Magic ETL and use the SQL tool to "Add Transformation".

 

For some reason, I'm not able to nail down what I think should be a trivial query. Any advice here is really appreciated. Thanks!

 

select *, date() as BUS_DATE from `TABLE_NAME`

GrantSmith

Hi @user054938 

 

Your use case is a little unclear. Are you wanting the same date for every single row in your dataset or are you taking snapshots of your data and want the date when that snapshot occurred?

 

If you're just looking for the same current date value for all the rows you have two options. The first being a Magic ETL - You can add an "Add Constant" tile and create a new column and set it to a Date type (or timestamp if you need date and time). You can then select "Current Date" from the bottom of the date pop up.

 

Alternatively you can create a BeastMode for the cards (This is if you just need to display the date and not utilize it for additional ETL processing prior to display) and use the CURRENT_DATE() function:

 

CURRENT_DATE()

 

 

 

If you're looking for more of a timestamp / snapshot type method you'll have two other options:

The first being a Recursive / Snapshot data flow. These are outlined in the knowledge base:

Magic ETL v1: Creating a Recursive/Snapshot ETL DataFlow

Magic ETL v2: Creating a Recursive/Snapshot DataFlow in Magic ETL v2 (Beta)

 

The downside to this is that the larger your snapshot dataset gets the slower the execution will become so you'll need to keep an eye on the size of your data set and possibly trim some data as the dataset gets too large.

 

A benefit here though is that it would remove duplicate records.

 

The other option is to include the date in your initial database query with either the CURRENT_DATE() or CURDATE() functions and then set your dataset to append instead of replace when ingesting your data. This will be faster ingestion / processing however there is the possibility that if the job is run twice on the same day you would have duplicate records in your dataset.

 

select *, CURRENT_DATE() as BUS_DATE from `TABLE_NAME`