1816621 - Airflow task bqetl_artifact_deployment.publish_views failing on 2023-02-13

Status: RESOLVED FIXED

(Data Platform and Tools :: General, defect)

Description

[Lucia Vargas [:lucia-vargas-a]]

Airflow task bqetl_artifact_deployment.publish_views failing on 2023-02-13

The task shows an error in the pod .

The error is related to a potential error in a query.

google.api_core.exceptions.BadRequest: 400 Column 7 in UNION ALL has incompatible types: STRUCT<jwe ARRAY<STRUCT<key STRING, value STRING>>, labeled_counter STRUCT<glean_error_invalid_label ARRAY<STRUCT<key STRING, value INT64>>, glean_error_invalid_overflow ARRAY<STRUCT<key STRING, value INT64>>, glean_error_invalid_state ARRAY<STRUCT<key STRING, value INT64>>, ...>, labeled_rate ARRAY<STRUCT<key STRING, value ARRAY<STRUCT<key STRING, value STRUCT<denominator INT64, numerator INT64>>>>>, ...>, STRUCT<jwe STRUCT<key STRING, value STRING>, labeled_counter STRUCT<glean_error_invalid_label ARRAY<STRUCT<key STRING, value INT64>>, glean_error_invalid_overflow ARRAY<STRUCT<key STRING, value INT64>>, glean_error_invalid_state ARRAY<STRUCT<key STRING, value INT64>>, ...>, labeled_rate STRUCT<key STRING, value STRUCT<key STRING, value STRUCT<denominator INT64, numerator INT64>>>, ...> at [24:1]

The related job_id is 7ac29fe4-93f0-430c-a9a8-ade7b2e3e3a0, which we are investigating next.

Comment 1

[Lucia Vargas [:lucia-vargas-a]]

The job runs in project moz-fx-data-airflow-gke-prod and is attempting to create the view moz-fx-data-shared-prod.mozilla_vpn.deletion_request. The problem is that the generated query if failing due to incompatible data types.

This is the latest commit
We'll check for changes related to the sql-generator query templates.

Comment 2

[Lucia Vargas [:lucia-vargas-a]]

As we investigated the query problem Column 7 in UNION ALL has incompatible types: STRUCT.
This is related to the metrics column being an ARRAY of STRUCT in the first table of the UNION moz-fx-data-shared-prod.mozillavpn.deletion_request, while in the second table of the UNION moz-fx-data-shared-prod.org_mozilla_firefox_vpn.deletion_request is being set as a STRUCT.

Running this query with column metrics as ARRAY makes the query valid again:

  metrics.jwe,
  metrics.labeled_counter,
  metrics.labeled_rate,
  metrics.url,
  metrics.text
FROM
  `moz-fx-data-shared-prod.mozillavpn.deletion_request`
UNION ALL
SELECT
    [STRUCT(SAFE_CAST(NULL AS STRING) AS key, SAFE_CAST(NULL AS STRING) AS value)] AS jwe,
    metrics.labeled_counter,
    [
      STRUCT(
        SAFE_CAST(NULL AS STRING) AS key,
        [STRUCT(
          SAFE_CAST(NULL AS STRING) AS key,
          STRUCT(
            SAFE_CAST(NULL AS INTEGER) AS denominator,
            SAFE_CAST(NULL AS INTEGER) AS numerator
            )AS value
      )] AS value
    ) ]
    AS labeled_rate,
    [STRUCT(SAFE_CAST(NULL AS STRING) AS key, SAFE_CAST(NULL AS STRING) AS value)] AS url,
    [STRUCT(SAFE_CAST(NULL AS STRING) AS key, SAFE_CAST(NULL AS STRING) AS value)] AS text
FROM
  `moz-fx-data-shared-prod.org_mozilla_firefox_vpn.deletion_request`

Comment 3

[Lucia Vargas [:lucia-vargas-a]]

The schema files for the two tables mozillavpn.deletion_request and org_mozilla_firefox_vpn.deletion_request are different.

The difference is for fields (jwe, labeled_rate, labeled_counter, url, text) which are defined as an ARRAY in the first table and don't exist in the second one. Therefore, the query does a safe-cast AS NULL to create these fields, but returning a STRUCT instead of an ARRAY.

The query is apparently generated in glean_app_ping_views.py and the assumption is that the schema files are being used to generate the query, so if the field doesn't exists in the source table, it defaults to this block of code. And since the mode ==REPEATED is not set for these fields then it is jumping to the else part of the statement.

:ascholtz
We have some open questions,

• Is the glean_usage autogeneration working as expected?
• Is adding the fields to the source table a possible solution?
• Should we modify the generation logic so that the query sets this field as an array?

Comment 4

[Anna Scholtz [:ascholtz]]

Attachment: GitHub Pull Request

Comment 5

[Anna Scholtz [:ascholtz]]

Is the glean_usage autogeneration working as expected?

No. The generation logic casts fields that don't exist in some view to NULL. It wasn't properly handling nested STRUCTs. For those the type information (types of all the nested) fields need to be generated and specified in the unioned view.
https://github.com/mozilla/bigquery-etl/pull/3592 should fix this.

Is adding the fields to the source table a possible solution?

These views are all generated from the ping schemas in https://github.com/mozilla-services/mozilla-pipeline-schemas. For Glean apps fields are specified in the application code, adding them isn't really an option. (But luckily we don't need to)

Should we modify the generation logic so that the query sets this field as an array?

See https://github.com/mozilla/bigquery-etl/pull/3592, setting the field as an array is only part of the fix. BigQuery needs to know the exact type information of the STRUCT that is wrapped into the array and all its sub-fields

Comment 6

[Lucia Vargas [:lucia-vargas-a]]

Today's run of the DAG is successful and the data types in both sub-queries used to create the view are now compatible.

Comment 7

[Krzysztof Ignasiak [:kik]]

Marking this bug as "resolved". Thank you :lvargas and :ascholtz for looking into this.