I am trying to figure out how to use the query operator to send a simple select query to BigQuery though BigQuery connector 1.1.5 in Mule4 flow
The documentation doesn't tell what format the query should be sent in. BigQuery Connector 1.1.5 Doc
Here is my flow:
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns:google-drive="http://www.mulesoft.org/schema/mule/google-drive"
xmlns:ee="http://www.mulesoft.org/schema/mule/ee/core"
xmlns:os="http://www.mulesoft.org/schema/mule/os"
xmlns:http="http://www.mulesoft.org/schema/mule/http"
xmlns:bigquery="http://www.mulesoft.org/schema/mule/bigquery"
xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:secure-properties="http://www.mulesoft.org/schema/mule/secure-properties" xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/ee/core http://www.mulesoft.org/schema/mule/ee/core/current/mule-ee.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/bigquery http://www.mulesoft.org/schema/mule/bigquery/current/mule-bigquery.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/os http://www.mulesoft.org/schema/mule/os/current/mule-os.xsd
http://www.mulesoft.org/schema/mule/secure-properties http://www.mulesoft.org/schema/mule/secure-properties/current/mule-secure-properties.xsd
http://www.mulesoft.org/schema/mule/google-drive http://www.mulesoft.org/schema/mule/google-drive/current/mule-google-drive.xsd">
<flow name="queryFlow">
<http:listener doc:name="Listener"
path="/query" config-ref="HTTP_Listener_config1"/>
<bigquery:query doc:name="Run Query create schema" responseTimeout="300" responseTimeoutUnit="SECONDS" config-ref="BigQueryConfiguration">
<bigquery:query-values-content ><![CDATA[{
"query": "select id from bcs-dfs-eng-sbx.raw_salesforce.stg_opportunity"
}]]></bigquery:query-values-content>
</bigquery:query>
<logger level="INFO" doc:name="Logger" message="#[%dw 2.0 output application/json --- payload]" />
</flow>
</mule>
When I try to run this, the code gets deployed but throws an error in the Query step as follows:
*******************************************************************************************************
* - - + APPLICATION + - - * - - + DOMAIN + - - * - - + STATUS + - - *
*******************************************************************************************************
* sf-test-query * default * DEPLOYED *
*******************************************************************************************************
INFO 2024-03-04 12:33:30,304 [[MuleRuntime].uber.02: [sf-test-query].uber@org.mule.runtime.module.extension.internal.runtime.source.ExtensionMessageSource.lambda$reallyDoStart$17:462 @2a75b6da] [processor: ; event: ] org.mule.runtime.module.extension.internal.runtime.source.ExtensionMessageSource: Message source 'listener' on flow 'queryFlow' successfully started
INFO 2024-03-04 12:33:47,175 [[MuleRuntime].uber.03: [sf-test-query].queryFlow.BLOCKING @427d2583] [processor: queryFlow/processors/0; event: b8f540a0-da55-11ee-aff2-acde48001122] com.mulesoft.connectors.google.bigquery.internal.operation.QueryPagedOperation: Query value content : java.io.ByteArrayInputStream@46dcc995
ERROR 2024-03-04 12:33:47,736 [[MuleRuntime].uber.03: [sf-test-query].queryFlow.BLOCKING @427d2583] [processor: queryFlow/processors/0; event: b8f540a0-da55-11ee-aff2-acde48001122] com.mulesoft.connectors.google.bigquery.internal.service.paging.RestPagingProvider: Could not get Page results
java.util.concurrent.ExecutionException: Request returned status code 400
at java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:357) ~[?:1.8.0_382]
at java.util.concurrent.CompletableFuture.get(CompletableFuture.java:1908) ~[?:1.8.0_382]
at com.mulesoft.connectors.google.bigquery.internal.service.paging.RestPagingProvider.getResult(RestPagingProvider.java:165) ~[mule4-google-bigquery-connector-1.1.5-mule-plugin.jar:?]
at com.mulesoft.connectors.google.bigquery.internal.service.paging.RestPagingProvider.doGetPage(RestPagingProvider.java:128) ~[mule4-google-bigquery-connector-1.1.5-mule-plugin.jar:?]
at com.mulesoft.connectors.google.bigquery.internal.service.paging.QueryPagingProvider.getPage(QueryPagingProvider.java:100) ~[mule4-google-bigquery-connector-1.1.5-mule-plugin.jar:?]
at com.mulesoft.connectors.google.bigquery.internal.service.paging.QueryPagingProvider.getPage(QueryPagingProvider.java:40) ~[mule4-google-bigquery-connector-1.1.5-mule-plugin.jar:?]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderWrapper.getPage(PagingProviderWrapper.java:78) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderProducer.withConnection(PagingProviderProducer.java:162) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderProducer.withConnection(PagingProviderProducer.java:155) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderProducer.performWithConnection(PagingProviderProducer.java:146) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderProducer.produce(PagingProviderProducer.java:105) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingProviderProducer.produce(PagingProviderProducer.java:55) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.core.api.streaming.iterator.ListConsumer.loadNextPage(ListConsumer.java:80) ~[mule-core-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingResultTransformer.applyChecked(PagingResultTransformer.java:61) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.streaming.PagingResultTransformer.applyChecked(PagingResultTransformer.java:28) ~[mule-module-extensions-support-4.5.1.jar:4.5.1]
at org.mule.runtime.core.api.util.func.CheckedBiFunction.apply(CheckedBiFunction.java:19) ~[mule-core-4.5.1.jar:4.5.1]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator$TransformingExecutionCallbackDecorator.complete(DefaultExecutionMediator.java:341) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.TracedOperationExecutionCallback.complete(TracedOperationExecutionCallback.java:36) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.execution.CompletableMethodOperationExecutor.doExecute(CompletableMethodOperationExecutor.java:26) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.execution.AbstractCompletableMethodOperationExecutor.execute(AbstractCompletableMethodOperationExecutor.java:61) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.executeCommand(DefaultExecutionMediator.java:241) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.executeWithInterceptors(DefaultExecutionMediator.java:224) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.lambda$execute$1(DefaultExecutionMediator.java:131) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.lambda$new$0(DefaultExecutionMediator.java:76) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.withExecutionTemplate(DefaultExecutionMediator.java:320) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.DefaultExecutionMediator.execute(DefaultExecutionMediator.java:130) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.ComponentMessageProcessor.executeOperation(ComponentMessageProcessor.java:567) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.OAuthOperationMessageProcessor.executeOperation(OAuthOperationMessageProcessor.java:68) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.ComponentMessageProcessor.prepareAndExecuteOperation(ComponentMessageProcessor.java:811) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.ComponentMessageProcessor.lambda$onEventSynchronous$16(ComponentMessageProcessor.java:469) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.ComponentMessageProcessor.onEventSynchronous(ComponentMessageProcessor.java:476) ~[?:?]
at org.mule.runtime.module.extension.internal.runtime.operation.ComponentMessageProcessor.lambda$createOuterFlux$6(ComponentMessageProcessor.java:385) ~[?:?]
at reactor.core.publisher.FluxPeekFuseable$PeekFuseableConditionalSubscriber.onNext(FluxPeekFuseable.java:489) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxMapFuseable$MapFuseableConditionalSubscriber.onNext(FluxMapFuseable.java:299) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxPeekFuseable$PeekFuseableConditionalSubscriber.onNext(FluxPeekFuseable.java:503) [reactor-core-3.4.22.jar:3.4.22]
at org.mule.runtime.core.privileged.processor.chain.AbstractMessageProcessorChain$2.onNext(AbstractMessageProcessorChain.java:629) [mule-core-4.5.1.jar:4.5.1]
at org.mule.runtime.core.privileged.processor.chain.AbstractMessageProcessorChain$2.onNext(AbstractMessageProcessorChain.java:624) [mule-core-4.5.1.jar:4.5.1]
at reactor.core.publisher.FluxHide$SuppressFuseableSubscriber.onNext(FluxHide.java:137) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxPeekFuseable$PeekFuseableSubscriber.onNext(FluxPeekFuseable.java:210) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxOnAssembly$OnAssemblySubscriber.onNext(FluxOnAssembly.java:539) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.publisher.FluxSubscribeOnValue$ScheduledScalar.run(FluxSubscribeOnValue.java:180) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.scheduler.SchedulerTask.call(SchedulerTask.java:68) [reactor-core-3.4.22.jar:3.4.22]
at reactor.core.scheduler.SchedulerTask.call(SchedulerTask.java:28) [reactor-core-3.4.22.jar:3.4.22]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_382]
at org.mule.service.scheduler.internal.AbstractRunnableFutureDecorator.doRun(AbstractRunnableFutureDecorator.java:180) [mule-service-scheduler-1.5.0.jar:1.5.0]
at org.mule.service.scheduler.internal.RunnableFutureDecorator.run(RunnableFutureDecorator.java:55) [mule-service-scheduler-1.5.0.jar:1.5.0]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_382]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_382]
at java.lang.Thread.run(Thread.java:750) [?:1.8.0_382]
Caused by: com.mulesoft.connectors.google.bigquery.internal.error.exception.RequestException: Request returned status code 400
ERROR 2024-03-04 12:33:51,976 [[MuleRuntime].uber.03: [sf-test-query].queryFlow.BLOCKING @427d2583] [processor: queryFlow/processors/0; event: b8f540a0-da55-11ee-aff2-acde48001122] org.mule.runtime.core.privileged.exception.DefaultExceptionListener:
********************************************************************************
Message : Request returned status code 400
Element : queryFlow/processors/0 @ sf-test-query:test-query.xml:20 (Run Query create schema)
Element DSL : <bigquery:query doc:name="Run Query create schema" responseTimeout="300" responseTimeoutUnit="SECONDS" config-ref="BigQueryConfiguration">
<bigquery:query-values-content><![CDATA[
{
"query": "select id from bcs-dfs-eng-sbx.raw_salesforce.stg_opportunity"
}
]]></bigquery:query-values-content>
</bigquery:query>
Error type : BIGQUERY:BAD_REQUEST
FlowStack : at queryFlow(queryFlow/processors/0 @ sf-test-query:test-query.xml:20 (Run Query create schema))
(set debug level logging or '-Dmule.verbose.exceptions=true' for everything)
********************************************************************************
INFO 2024-03-04 12:33:58,521 [[MuleRuntime].uber.03: [sf-test-query].queryFlow.BLOCKING @427d2583] [processor: queryFlow/processors/0; event: b968c2f0-da55-11ee-aff2-acde48001122] com.mulesoft.connectors.google.bigquery.internal.operation.QueryPagedOperation: Query value content : java.io.ByteArrayInputStream@7314ba8c
ERROR 2024-03-04 12:33:58,920 [[MuleRuntime].uber.03: [sf-test-query].queryFlow.BLOCKING @427d2583] [processor: queryFlow/processors/0; event: b968c2f0-da55-11ee-aff2-acde48001122] com.mulesoft.connectors.google.bigquery.internal.service.paging.RestPagingProvider: Could not get Page results
java.util.concurrent.ExecutionException: Request returned status code 400
I think I am missing in the format in which I should query the BigQuery API and if I should send any additional information in Headers. I was not able to find any concrete example online.
Try below, I have used a similar query with the same syntax and it worked.
%dw 2.0
output application/json
---
{
query: "select id from bcs-dfs-eng-sbx.raw_salesforce.stg_opportunity",
useLegacySql: false
}