Scheduled incremental refresh of dataset is not working

[awswolf]

Hi all,
I’ve created a scheduled incremental refresh on a dataset which uses RDS as a datasource.
I set the incremental refresh with a window size of 6 hours, by using the column LAST_UPDATE_QS on database’s table.
(I wasn’t able to quickly change the language on the browser, so I’ll translate the relevation information in the descriptions below the pictures)

Now there are 14496 rows imported on QuickSight; on RDS I have 14497, so I expect that with an incremental refresh the new line would be added to QuickSight.

On the database I have the following values for LAST_UPDATE_QS:

2022-06-01 08:01:37
2022-05-25 07:00:00
2022-05-25 07:00:00

So, given that the first row is inside the window (and was not already present on QuickSight), it should be updated on QuickSight (in my case, inserted).

This is the schedule configuration, with repetions set to every 15 minutes:

Update: Incremental Update
Update column: LAST_UPDATE_QS
Windows size: 6 hours
Repetitions: Every 15 minutes
Start: 22022-06-01 10:05

What I understand is: at 2022-06-01 10:05 the first incremental update will start, the second will be scheduled at 2022-06-01 10:20 and so on.

However, the dataset is never updated (as you can see in the first picture - Last update: 17 hours ago).
If I check the list of updates, this is the result:

|2022/05/31 14:58:08 | Completato |15 secondi |0| 14496 |14496 | Manuale, Aggiornamento completo|
|2022/05/25 14:40:15 | Completato | 14 secondi |0| 14494 | 14494 | Manuale, Modifica|
|2022/05/25 14:39:06 | Completato | 15 secondi |0| 14494 | 14494 | Manuale, Modifica|

Where the description says: Manual, update completed.
What I understand is that the scheduled update has never started.

Is there some misconfiguration? Do I have to change something on QuickSight? Is the date format on RDS wrong?

[Massi]

Hi @awswolf,

I have experienced the same bug, I mentioned it here:
Dataset used in another dataset - Keys not recognized
You can also find a link to another previous discussion on the subject (and if you google you will also find messages on StackOverflow).
I also contacted the QS team in email and I got a reply that they would come back to me, but they never did.

I am not aware of any public QS backlog to know what they are working on, so no idea if this is sorted out.

[Koushik_Muthanna]

Hi,

What does the query in RDS which was sent from QuickSight show ? Are you able run the same query and check if there were any records returned ?

I have tested the incremental refresh and below are a few screenshots to understand what is happening , hopefully this should help you debug at your end.

Setup ( Window size : 6 hours )

Schedule

History of executions ( Every 15 minutes )

How does a select look like for incremental refresh ?

Note : included from_unixtime… as part of the query to show the time which is considered to extract records.
In this scenario , -6 hours . As per screenshot history of executions , the schedule which ran at 11:30:24(UTC) takes -6 hours from the previous incremental run ( 2022/06/03 11:15:24 - 6 hours = 2022-06-03 5:15:24 ) . This is visible in _col6

Data before update

Updated data in QuickSight (Amount 1000 )

Regards,
Koushik

[awswolf]

Hi @Massi and @Koushik_Muthanna,

thank you for your replies!

@Koushik_Muthanna right now the query returns the data that I expect (I did a simple select * on a single table)

These are results that I can see in the QuickSight preview of the dataset.

As you can see, dates are in the following format: 2022-05-25T07:00:00.000Z
On database (which is MySQL) dates are datetime with values like 2022-06-06 13:41:06.
I’ve an SQL trigger that updates automatically the date mentioned above with now() function after all insertion/update.

I tried to do the same convertion you mentioned for the date field, but it gives me an error:

select *, FROM_UNIXTIME( CAST(SUBSTR( CAST (s.LAST_UPDATE_QS as VARCHAR), 1, 10 ) as BIGINT) ) 
from KC_SCHOOL s 
order by LAST_UPDATE_QS desc;

And the error is:

Errore SQL [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘VARCHAR), 1, 10 ) as BIGINT) )
from KC_SCHOOL s
order by LAST_UPDATE_QS desc’ at line 1

It seems the same query you did in your example.

I can’t understand why, even if the data from LAST_UPDATE_QS are not in the format it expects, the scheduled refresh doesn’t start.

The other configuration on QuickSight seems exactly the same as yours. Can the simple select * be the problem that not allow the scheduled refresh to start?

Thanks again,
Andrea

[Koushik_Muthanna]

Hi @awswolf ,

Are you still facing the issue with scheduled incremental refresh of the dataset ?

Kind Regards,
Koushik

[Karthik_Tharmarajan]

Hi @awswolf , Please let us know if your incremental refresh schedule is working as expected.

Regards,
Karthik

[DeepakS]

Hi, @awswolf Did @Koushik_Muthanna solution work for you? I am marking his reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!