BigQuery Execute fails with no meaningful error on Cloud Data Fusion

Question

I'm trying to use the BigQuery Execute function in Cloud Data Fusion (Google). The component validates fine, the SQL checks out but I get this non-meaningful error with every execution:

02/11/2022 12:51:25 ERROR Pipeline 'test-bq-execute' failed.
02/11/2022 12:51:25 ERROR Workflow service 'workflow.default.test-bq-execute.DataPipelineWorkflow.<guid>' failed.
02/11/2022 12:51:25 ERROR Program DataPipelineWorkflow execution failed.

I can see nothing else to help me debug this. Any ideas? The SQL in question is a simple DELETE from dataset.table WHERE ds = CURRENT_DATE()

This was the pipeline

{
    "name": "test-bq-execute",
    "description": "Data Pipeline Application",
    "artifact": {
        "name": "cdap-data-pipeline",
        "version": "6.5.1",
        "scope": "SYSTEM"
    },
    "config": {
        "resources": {
            "memoryMB": 2048,
            "virtualCores": 1
        },
        "driverResources": {
            "memoryMB": 2048,
            "virtualCores": 1
        },
        "connections": [],
        "comments": [],
        "postActions": [],
        "properties": {},
        "processTimingEnabled": true,
        "stageLoggingEnabled": false,
        "stages": [
            {
                "name": "BigQuery Execute",
                "plugin": {
                    "name": "BigQueryExecute",
                    "type": "action",
                    "label": "BigQuery Execute",
                    "artifact": {
                        "name": "google-cloud",
                        "version": "0.18.1",
                        "scope": "SYSTEM"
                    },
                    "properties": {
                        "project": "auto-detect",
                        "sql": "DELETE FROM GCPQuickStart.account WHERE ds = CURRENT_DATE()",
                        "dialect": "standard",
                        "mode": "batch",
                        "dataset": "GCPQuickStart",
                        "table": "account",
                        "useCache": "false",
                        "location": "US",
                        "rowAsArguments": "false",
                        "serviceAccountType": "filePath",
                        "serviceFilePath": "auto-detect"
                    }
                },
                "outputSchema": [
                    {
                        "name": "etlSchemaBody",
                        "schema": ""
                    }
                ],
                "id": "BigQuery-Execute",
                "type": "action",
                "label": "BigQuery Execute",
                "icon": "fa-plug"
            }
        ],
        "schedule": "0 1 */1 * *",
        "engine": "spark",
        "numOfRecordsPreview": 100,
        "maxConcurrentRuns": 1
    }
}

Answer 1

I was able to catch the error using Cloud Logging. To enable Cloud Logging in Cloud Data Fusion, you may use this GCP Documentation. And follow these steps to view the logs from Data Fusion to Cloud Logging. Replicating your scenario this is the error I found:

      "logMessage": "Program DataPipelineWorkflow execution failed.\njava.util.concurrent.ExecutionException: com.google.cloud.bigquery.BigQueryException: Cannot set destination table in jobs with DML statements\n    at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357)\n    at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1908)\n    at io.cdap.cdap.internal.app.runtime.distributed.AbstractProgramTwillRunnable.run(AbstractProgramTwillRunnable.java:274)\n    at org.apache.twill.interna..."
    }

What we did to resolve this error: Cannot set destination table in jobs with DML statements is we left the Dataset Name and Table Name empty inside the pipeline properties as there is no need for the destination table to be specified.

Output: