Spice dataset stuck in refresh

Q&A
, , ,
1

How can I stop the below dataset from refreshing?

I did manual changes and the next refershes were succesfull but the previous one is still running.
How to stop it?

image
image1558Ă—254 23.8 KB

2

Hello @pantelis
Greetings of the day!

Regarding the stuck ingestion, you can check with API/CLI commands to try cancelling the ingestion:

a) Firstly you can use ListIngestion API call to list all ingestions of the dataset:
[+] ListIngestions - Amazon QuickSight

b) Then you can take note of the ingestion-id from the output of the above call you can check for ingestion-id that corresponds to IngestionStatus as “Running”

c) You can then cancel the ingestion using CancelIngestion API call:
[+] CancelIngestion - Amazon QuickSight

In case this does not work, I would recommend filing a case with AWS Support where we can dive into the details so that we can help you further. Here are the steps to open a support case.

If your company has someone who manages your AWS account, you might not have direct access to AWS Support and will need to raise an internal ticket to your IT team or whomever manages your AWS account. They should be able to open an AWS Support case on your behalf.

Hope this helps!

3 Likes
3

Hi @msnehali - Thanks for sharing this details. From user experience, can we also add a process or approach to cancel the SPICE refresh in UI. Can we put this as a feature request. Most of the user may not aware about the API usage.

Please advise.
Tagging @DylanM @duncan for their advise on this as well.

Regards - Sanjeeb

6 Likes
4

Hello @Sanjeeb2022,

This feature will be very helpful to us.

Best,
Vaibhav

2 Likes
5

Thanks @Sanjeeb2022 @Vaibhav.narwade
Yes, this will be a good UI feature. I will tag this as a feature request.

5 Likes
6

Hello, same happens here. The dataset is stuck trying to refresh and it does not allow me to refresh manually. The Cancel refresh button would be a great addition!

image
image1444Ă—169 17.2 KB

7

We are having the same issue, all of our hourly refresh datasets have been stuck on running since 1AM on October 20th. I assume this is related to the AWS outage yesterday, however we have been unsuccessful trying to kill the ingestion using the CLI despite getting 202 messages back, even waiting upwards of an hour. We are getting the same issue across 3 different aws accounts.

8

Same issue here:

image
image1963Ă—189 23.8 KB

A very simple dataset is stucked for 3 hours and still loading. I tryied to cancel using boto3, but not worked.

Any suggestion?

9

For someone who also have this problem:
Edit the model transforming it on Direct Query model.
After that, return to SPICE model.

Works gracefully here!

10

Managed to solve all dataset stuck with API code to change data to Direct Query and back to SPICE to force a new refresh, main issue was passing the arguments for SPICE differently as it uses a PhysicalTableMap which Direct doesnt, so just had to remove the SubType column from it to make it work

def update_import_mode(dataset_id: str, target_mode: str):

ds = get_ds_full(dataset_id)

if target_mode == "DIRECT_QUERY":

    args = build_update_args_direct(ds, dataset_id)

else:

    args = build_update_args_spice(ds, dataset_id)

qs.update_data_set(\*\*args)

def build_update_args_spice(ds: dict, dataset_id: str) → dict:

"""Full payload for switching back to SPICE (keeps SPICE props)."""

args = {

    "AwsAccountId": ACCOUNT_ID,

    "DataSetId": dataset_id,

    "Name": ds\["Name"\],

    "PhysicalTableMap": ds\["PhysicalTableMap"\],

    "LogicalTableMap": ds\["LogicalTableMap"\],

    "ImportMode": "SPICE",

}

for k in \[

    "ColumnGroups",

    "FieldFolders",

    "RowLevelPermissionDataSet",

    "RowLevelPermissionTagConfiguration",

    "ColumnLevelPermissionRules",

    "DataSetUsageConfiguration",

    "DatasetParameters",

    "DataSetRefreshProperties",  # SPICE-only; fine when returning to SPICE

\]:

    if k in ds and ds\[k\] is not None:

        args\[k\] = ds\[k\]

return args

def update_import_mode(dataset_id: str, target_mode: str):

ds = get_ds_full(dataset_id)

if target_mode == "DIRECT_QUERY":

    args = build_update_args_direct(ds, dataset_id)

else:

    args = build_update_args_spice(ds, dataset_id)

qs.update_data_set(\*\*args)

def wait_dataset_ready(dataset_id: str, target_mode: str, timeout: int = 300, poll: float = 5.0):

"""

Wait until DescribeDataSet reports ImportMode == target_mode.

Works even when ResourceStatus is absent and avoids tz issues.

"""

t0 = time.time()

consec_ok = 0



while True:

    ds = qs.describe_data_set(AwsAccountId=ACCOUNT_ID, DataSetId=dataset_id)\["DataSet"\]



    \# If ResourceStatus is present, fail fast on explicit failure.

    status = ds.get("ResourceStatus")

    if status in ("CREATION_FAILED", "UPDATE_FAILED"):

        raise RuntimeError(f"\[{dataset_id}\] Dataset update failed. status={status}")



    mode_ok = (ds.get("ImportMode") == target_mode)



    \# consider it ready after 2 consecutive confirmations to dodge eventual-consistency blips

    consec_ok = consec_ok + 1 if mode_ok else 0

    if consec_ok >= 2:

        return



    if time.time() - t0 > timeout:

        \# As a safety, one last check before giving up

        if ds.get("ImportMode") == target_mode:

            return

        raise TimeoutError(f"\[{dataset_id}\] Timed out waiting for {target_mode} (last ImportMode={ds.get('ImportMode')})")



    time.sleep(poll)

def ingest_and_wait(dataset_id: str, ingestion_type=“FULL_REFRESH”, timeout=1800):

ing_id = f"manual-{int(time.time())}"

qs.create_ingestion(

    AwsAccountId=ACCOUNT_ID,

    DataSetId=dataset_id,

    IngestionId=ing_id,

    IngestionType=ingestion_type,

)

t0 = time.time()

while True:

    ing = qs.describe_ingestion(

        AwsAccountId=ACCOUNT_ID, DataSetId=dataset_id, IngestionId=ing_id

    )\["Ingestion"\]

    st = ing\["IngestionStatus"\]

    if st == "COMPLETED":

        return ing_id

    if st in ("FAILED", "CANCELLED"):

        raise RuntimeError(f"\[{dataset_id}\] Ingestion {ing_id} ended {st}: {ing.get('ErrorInfo')}")

    if time.time() - t0 > timeout:

        raise TimeoutError(f"\[{dataset_id}\] Timed out waiting for ingestion {ing_id}")

    time.sleep(10)

print(f"[{dataset_id}] Switching to DIRECT_QUERY…")

    update_import_mode(dataset_id, "DIRECT_QUERY")

    wait_dataset_ready(dataset_id, target_mode="DIRECT_QUERY")



    print(f"\[{dataset_id}\] Switching back to SPICE…")

    update_import_mode(dataset_id, "SPICE")

    wait_dataset_ready(dataset_id, target_mode="SPICE")

    print(f"\[{dataset_id}\] Update to SPICE complete. Starting full ingestion…")

    

    ing_id = ingest_and_wait(dataset_id, "FULL_REFRESH")

    print(f"\[{dataset_id}\] Done. IngestionId={ing_id}")

Can help with debug if someone is still stucked with this issue from last week