question

javier-8889 avatar image
1 Vote"
javier-8889 asked javier-8889 answered

azure ml pipeline fails at sql transform task

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
azure-machine-learning
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

now i can pass an SQL transform step no matter how many times i re-submit (run id: 83ef7450-4043-41f5-b1b9-2773e7a49615)


 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
0 Votes 0 ·

I was able to run another pipeline succesfully.

I created a blank pipeline and copied and pasted the problematic pipeline and got same issue!

0 Votes 0 ·

1 Answer

javier-8889 avatar image
1 Vote"
javier-8889 answered

Found the problem.

There was a task failing but due to the size of the canvas I wasn't able to spot it at first (working late hours didn't help also).

However it certainly didn't help the fact that the error message didn't provide any info regarding which task failed, so maybe the AML team would like to add more descriptive messages in cases like this one.

thanks

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.