Hi, I'm using Azure ML Designer to run a pipeline. The pipeline performs a few steps and then it cancels the work throwing an error message with no further details.
If I re-submit the pipeline it completes the previously failed step but fails on the next step. If I re-submit the same thing happens (completes previously failed step to then fail the next step)... until it gets stuck in a specific sql transform step (see log below)
Here is a sequence of run ids related with the issue:
d33d23a2-2e60-4198-a6b6-f47e6e27ef4e
57e04c1e-73e8-4ddf-91a8-c407cd1ad5ef
ad7dc826-6549-4eb3-9536-9a801d8e8c0b
e6623f6f-b7b9-4f19-9501-c8c28f53ab23
It may be due to the way my pipeline is built but seems like JOIN, SQL Transform and SELECT Column operations tend to fail the most.
Would much appreciate any help on this.
2021/05/11 01:57:24 Starting App Insight Logger for task: runTaskLet
2021/05/11 01:57:24 Attempt 1 of http call to http://10.0.0.6:16384/sendlogstoartifacts/info
2021/05/11 01:57:24 Attempt 1 of http call to http://10.0.0.6:16384/sendlogstoartifacts/status
[2021-05-11T01:57:24.912444] Entering context manager injector.
[context_manager_injector.py] Command line Options: Namespace(inject=['ProjectPythonPath:context_managers.ProjectPythonPath', 'Dataset:context_managers.Datasets', 'RunHistory:context_managers.RunHistory', 'TrackUserError:context_managers.TrackUserError'], invocation=['urldecode_invoker.py', 'python', '-m', 'azureml.designer.modules.datatransform.invoker', 'ApplySqlTransModule', '--dataset', 'DatasetOutputConfig:Result_dataset', '--t1=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr', '--t2=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy', '--t3=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji', '--sqlquery=%22select+b.*%2cc.*%0d%0afrom+(%0d%0a++++select+a.customer_id%2c+a.sku_id%0d%0a++++from+(%0d%0a++++++++select+*+from+t1+cross+join+t2%0d%0a++++)+a%0d%0a++++where+exists+(%0d%0a++++++++select+t3.top_skus%0d%0a++++++++from+t3%0d%0a++++++++where+t3.sku_id+%3d+a.sku_id%0d%0a++++)%0d%0a)+b%0d%0ainner+join+(%0d%0a++++select+distinct+sku_id%2c+top_skus%0d%0a++++from+t3%0d%0a)+c%0d%0aon+c.sku_id+%3d+b.sku_id%22'])
Script type = None
[2021-05-11T01:57:26.142183] Entering Run History Context Manager.
[2021-05-11T01:57:26.734197] Current directory: /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/mounts/workspaceblobstore/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6
[2021-05-11T01:57:26.734493] Preparing to call script [urldecode_invoker.py] with arguments:['python', '-m', 'azureml.designer.modules.datatransform.invoker', 'ApplySqlTransModule', '--dataset', '$Result_dataset', '--t1=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr', '--t2=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy', '--t3=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji', '--sqlquery=%22select+b.*%2cc.*%0d%0afrom+(%0d%0a++++select+a.customer_id%2c+a.sku_id%0d%0a++++from+(%0d%0a++++++++select+*+from+t1+cross+join+t2%0d%0a++++)+a%0d%0a++++where+exists+(%0d%0a++++++++select+t3.top_skus%0d%0a++++++++from+t3%0d%0a++++++++where+t3.sku_id+%3d+a.sku_id%0d%0a++++)%0d%0a)+b%0d%0ainner+join+(%0d%0a++++select+distinct+sku_id%2c+top_skus%0d%0a++++from+t3%0d%0a)+c%0d%0aon+c.sku_id+%3d+b.sku_id%22']
[2021-05-11T01:57:26.734551] After variable expansion, calling script [urldecode_invoker.py] with arguments:['python', '-m', 'azureml.designer.modules.datatransform.invoker', 'ApplySqlTransModule', '--dataset', '/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpnbybe4mu', '--t1=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr', '--t2=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy', '--t3=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji', '--sqlquery=%22select+b.*%2cc.*%0d%0afrom+(%0d%0a++++select+a.customer_id%2c+a.sku_id%0d%0a++++from+(%0d%0a++++++++select+*+from+t1+cross+join+t2%0d%0a++++)+a%0d%0a++++where+exists+(%0d%0a++++++++select+t3.top_skus%0d%0a++++++++from+t3%0d%0a++++++++where+t3.sku_id+%3d+a.sku_id%0d%0a++++)%0d%0a)+b%0d%0ainner+join+(%0d%0a++++select+distinct+sku_id%2c+top_skus%0d%0a++++from+t3%0d%0a)+c%0d%0aon+c.sku_id+%3d+b.sku_id%22']
Session_id = 4b5b4c29-cfda-4ab6-a715-47fee287c468
Invoking module by urldecode_invoker 0.0.8.
Module type: custom module.
Using runpy to invoke module 'azureml.designer.modules.datatransform.invoker'.
/azureml-envs/azureml_7c975cabc8bb1dc19c3de94457d707fd/lib/python3.6/site-packages/azureml/designer/modules/datatransform/tools/dataframe_utils.py:2: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
from pandas.util.testing import assert_frame_equal
2021-05-11 01:57:27,324 [ invoker] [ INFO] .[main] Start custom modules
2021-05-11 01:57:27,337 [ invoker] [ INFO] .[main] Module version: 0.0.74
2021-05-11 01:57:27,344 [ invoker] [ INFO] .[main] args: azureml.designer.modules.datatransform.invoker, ApplySqlTransModule, --dataset, /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpnbybe4mu, --t1=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr, --t2=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy, --t3=/mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji, --sqlquery=select b.*,c.*
from (
select a.customer_id, a.sku_id
from (
select * from t1 cross join t2
) a
where exists (
select t3.top_skus
from t3
where t3.sku_id = a.sku_id
)
) b
inner join (
select distinct sku_id, top_skus
from t3
) c
on c.sku_id = b.sku_id
2021-05-11 01:57:27,352 [ invoker] [ INFO] .[main] "transform_module_class_name": ApplySqlTransModule
2021-05-11 01:57:27,444 [ module_base] [ INFO] ...[get_arg_parser] Construct arg parser
2021-05-11 01:57:27,460 [ module_base] [ INFO] ...[get_arg_parser] arg: t1
2021-05-11 01:57:27,468 [ module_base] [ INFO] ...[get_arg_parser] arg: t2
2021-05-11 01:57:27,476 [ module_base] [ INFO] ...[get_arg_parser] arg: t3
2021-05-11 01:57:27,484 [ module_base] [ INFO] ...[get_arg_parser] arg: dataset
2021-05-11 01:57:27,492 [ module_base] [ INFO] ...[get_arg_parser] arg: sqlquery
2021-05-11 01:57:27,500 [ module_base] [ INFO] ..[parse_and_insert_args] invoker args:
module_classname = ApplySqlTransModule
t1 = /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr
t2 = /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy
t3 = /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji
dataset = /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpnbybe4mu
sqlquery = select b.*,c.*
from (
select a.customer_id, a.sku_id
from (
select * from t1 cross join t2
) a
where exists (
select t3.top_skus
from t3
where t3.sku_id = a.sku_id
)
) b
inner join (
select distinct sku_id, top_skus
from t3
) c
on c.sku_id = b.sku_id
2021-05-11 01:57:27,508 [ invoker] [ INFO] .[main] start to run custom module: ApplySqlTransModule
2021-05-11 01:57:27,516 [apply_sql_trans_module] [ INFO] ...[run] Construct SQLite Server
2021-05-11 01:57:27,530 [ module_parameter] [ INFO] ......[data] Read data from /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpmflqzlpr
2021-05-11 01:57:29,215 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t1 with only column names
2021-05-11 01:57:29,227 [ module_parameter] [ INFO] ......[data] Read data from /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpl9h5snzy
2021/05/11 01:57:29 Not exporting to RunHistory as the exporter is either stopped or there is no data.
Stopped: false
OriginalData: 1
FilteredData: 0.
2021-05-11 01:57:30,093 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t2 with only column names
2021-05-11 01:57:30,106 [ module_parameter] [ INFO] ......[data] Read data from /mnt/batch/tasks/shared/LS_root/jobs/test/azureml/e5e84dde-4b32-4ea3-9965-adc71f7ab0f6/wd/tmpuhf3n5ji
2021-05-11 01:57:30,876 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t3 with only column names
2021-05-11 01:57:30,888 [apply_sql_trans_module] [ INFO] ...[run] Read SQL script query
2021-05-11 01:57:30,895 [apply_sql_trans_module] [ INFO] ...[run] Validate SQL script query
2021-05-11 01:57:30,912 [apply_sql_trans_module] [ INFO] ...[run] Insert data to SQLite Server
2021-05-11 01:57:30,919 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t1
2021-05-11 01:57:30,930 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t2
2021-05-11 01:57:30,970 [apply_sql_trans_module] [ INFO] ....[_transform_df_to_sql] Insert t3
2021-05-11 01:57:31,053 [apply_sql_trans_module] [ INFO] ...[run] Generate SQL query result from SQLite Server