1905357 - Airflow task bqetl_subplat.mozilla_vpn_external__devices__v1 failed for exec_date 2024-06-28

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Mike Williams [:mwilliams]]

Airflow task bqetl_subplat.mozilla_vpn_external__devices__v1 failed for exec_date 2024-06-28
mozilla_vpn_external__subscriptions__v1 task also failed with basically the same error:

- Unrecognized name: devices_v1
+ Unrecognized name: subscriptions_v1

Task link:
https://workflow.telemetry.mozilla.org/dags/bqetl_subplat/grid?dag_run_id=scheduled__2024-06-27T01%3A45%3A00%2B00%3A00&task_id=mozilla_vpn_external__devices__v1&tab=logs
https://workflow.telemetry.mozilla.org/dags/bqetl_subplat/grid?dag_run_id=scheduled__2024-06-27T01%3A45%3A00%2B00%3A00&task_id=mozilla_vpn_external__subscriptions__v1&tab=logs

Log extract:

[2024-06-28, 02:49:16 UTC] {pod_manager.py:466} INFO - [base] Error in query string: Error processing job 'moz-fx-data-shared-
[2024-06-28, 02:49:16 UTC] {pod_manager.py:466} INFO - [base] prod:bqjob_r2d28eab20118b1f_000001905cbf3981_1': Unrecognized name: devices_v1
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base] at [3:41]
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base] Traceback (most recent call last):
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "<string>", line 1, in <module>
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/app/bigquery_etl/cli/__init__.py", line 76, in cli
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     group(prog_name=prog_name)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return self.main(*args, **kwargs)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]            ^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/rich_click/rich_command.py", line 126, in main
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     rv = self.invoke(ctx)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]          ^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return _process_result(sub_ctx.command.invoke(sub_ctx))
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return _process_result(sub_ctx.command.invoke(sub_ctx))
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return ctx.invoke(self.callback, **ctx.params)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/core.py", line 783, in invoke
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return __callback(*args, **kwargs)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/site-packages/click/decorators.py", line 33, in new_func
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     return f(get_current_context(), *args, **kwargs)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/app/bigquery_etl/cli/query.py", line 868, in run
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     _run_query(
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/app/bigquery_etl/cli/query.py", line 1013, in _run_query
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     subprocess.check_call(["bq"] + query_arguments, stdin=query_stream)
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]   File "/usr/local/lib/python3.11/subprocess.py", line 413, in check_call
[2024-06-28, 02:49:17 UTC] {pod_manager.py:466} INFO - [base]     raise CalledProcessError(retcode, cmd)
[2024-06-28, 02:49:20 UTC] {pod_manager.py:483} INFO - [base] subprocess.CalledProcessError: Command '['bq', 'query', "--parameter=external_database_query:STRING: SELECT\n  id,\n  user_id,\n  name,\n  mullvad_id,\n  pubkey,\n  ipv4_address,\n  ipv6_address,\n  created_at,\n  updated_at,\n  uid,\n  platform,\n  useragent,\n  unique_id\nFROM devices WHERE DATE(updated_at) = DATE '2024-06-27'", '--project_id=moz-fx-data-shared-prod', '--destination_table=devices_v1', '--use_legacy_sql=False', '--dataset_id=mozilla_vpn_external']' returned non-zero exit status 1.
[2024-06-28, 02:49:20 UTC] {pod_manager.py:616} INFO - Pod mozilla-vpn-external--devices--v1-e134ksxz has phase Running

Comment 1

[BMO Github Automation]

Attachment: [mozilla/bigquery-etl] Bug 1905357: use table alias to fix references in query (#5866)

Comment 2

[Mike Williams [:mwilliams]]

This is fixed: linked PR is deployed and I cleared the tasks, which ran successfully.