1946069 - Airflow task bqetl_glean_usage clients_last_seen_joined__v1 tasks for all apps failing on 2025-02-05

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Lucia Vargas]

Airflow task bqetl_glean_usage clients_last_seen_joined__v1 tasks for all apps failing on 2025-02-05 due to duplicates for is_default_browser. The error is the same for all of them. These are sql_generated tables that use a single template.

Tables affected & link:

• fenix_derived__clients_last_seen_joined__v1
• klar_ios_derived__clients_last_seen_joined__v1
• focus_ios_derived__clients_last_seen_joined__v1
• focus_android_derived__clients_last_seen_joined__v1
• firefox_ios_derived__clients_last_seen_joined__v1

Log extract:

Error in query string: Error processing job 'moz-fx-data-shared- [2025-02-05, 05:55:58 UTC] {pod_manager.py:472} INFO - [base] prod:bqjob_r2d34dd0a9ad2d986_00000194d4adf2ea_1': Duplicate column names in the [2025-02-05, 05:55:58 UTC] {pod_manager.py:472} INFO - [base] result are not supported when a destination table is present. Found [2025-02-05, 05:55:58 UTC] {pod_manager.py:472} INFO - [base] duplicate(s): is_default_browser

Comment 1

[Lucia Vargas]

The duplicated column seems to be introduced in PR https://github.com/mozilla/bigquery-etl/pull/6930.

Comment 2

[Lucia Vargas]

Related PR with fix PR-6977

Comment 3

[Lucia Vargas]

Fenix running okay today.
Other mobile apps are still failing [klar_ios, firefox_ios, focus_android, focus_ios] are still failing as the column is duplicated in the schemas (is_default_browser, is_default_browser_1).
We need a DROP column / update schema.

Comment 4

[Krzysztof Ignasiak [:kik]]

The tasks now complete successfully. And yes, we should drop is_default_browser_1 in tables where it was created as the result of this.

Comment 5

[Krzysztof Ignasiak [:kik]]

Used the following query to identify which tables now contain the is_default_browser_1 field:

FROM `region-us`.INFORMATION_SCHEMA.COLUMNS 
|> WHERE LOWER(column_name) = 'is_default_browser_1' AND table_name = "clients_last_seen_joined_v1"
|> SELECT table_catalog, table_schema, table_name, column_name;

The result:

table_catalog	table_schema	table_name	                                                                  column_name
moz-fx-data-shared-prod	        focus_android_derived	clients_last_seen_joined_v1	  is_default_browser_1
moz-fx-data-shared-prod	        focus_ios_derived	        clients_last_seen_joined_v1	  is_default_browser_1
moz-fx-data-shared-prod	        firefox_ios_derived	clients_last_seen_joined_v1	  is_default_browser_1
moz-fx-data-shared-prod	        fenix_derived	                clients_last_seen_joined_v1	  is_default_browser_1
moz-fx-data-shared-prod	        klar_ios_derived	        clients_last_seen_joined_v1	  is_default_browser_1

Comment 6

[Krzysztof Ignasiak [:kik]]

Statements to drop this column in those tables:

ALTER TABLE `moz-fx-data-shared-prod.focus_android_derived.clients_last_seen_joined_v1` DROP COLUMN IF EXISTS is_default_browser_1;
ALTER TABLE `moz-fx-data-shared-prod.focus_ios_derived.clients_last_seen_joined_v1`     DROP COLUMN IF EXISTS is_default_browser_1;
ALTER TABLE `moz-fx-data-shared-prod.firefox_ios_derived.clients_last_seen_joined_v1`   DROP COLUMN IF EXISTS is_default_browser_1;
ALTER TABLE `moz-fx-data-shared-prod.fenix_derived.clients_last_seen_joined_v1`         DROP COLUMN IF EXISTS is_default_browser_1;
ALTER TABLE `moz-fx-data-shared-prod.klar_ios_derived.clients_last_seen_joined_v1`      DROP COLUMN IF EXISTS is_default_browser_1;

Comment 7

[Krzysztof Ignasiak [:kik]]

The statements were executed and columns have been marked for deletion.

Comment 8

[Krzysztof Ignasiak [:kik]]

I believe this issue is done now. If this is not the case please reopen the bug and reach out to me.

Comment 9

[gkatre]

Thanks [:kik]!