Ask Your Question
1

FORCE_00 - A configuration is invalid because : Badly formed SOQL Query

asked 2018-07-30 15:09:29 -0600

Mehul gravatar image

updated 2018-07-31 13:44:11 -0600

metadaddy gravatar image

Hello everyone,

I am using Streamsets to ingest data. The source system is Salesforce and target is AWS S3. The connection works fine but while sending query "SELECT id, Name from Account WHERE Id > '${OFFSET}' ORDER BY Id" , I am getting following validation error.

FORCE_00 - A configuration is invalid because: Badly formed SOQL Query: SELECT Id, Name from Account WHERE Id > '${OFFSET}' ORDER BY Id: connectorConfig

Has anyone encountered same issue? The API version is 34.0.

The sdc.log shows following stack trace.

2018-07-31 16:33:37,588 [user:*admin] [pipeline:SalesforcePipeline/7e5f1b20-c21a-4e8f-91dd-2218ffef7257] [thread:ProductionPipelineRunnable-400916cc-d98c-4f53-9db0-f7598154da66-SalesforcePipeline] INFO  ForceSource - Successfully authenticated as datalake@example.com.qa
2018-07-31 16:33:37,677 [user:*admin] [pipeline:SalesforcePipeline/7e5f1b20-c21a-4e8f-91dd-2218ffef7257] [thread:ProductionPipelineRunnable-400916cc-d98c-4f53-9db0-f7598154da66-SalesforcePipeline] ERROR ProductionPipelineRunnable - An exception occurred while running the pipeline, com.streamsets.datacollector.runner.PipelineRuntimeException: CONTAINER_0800 - Pipeline '7e5f1b20-c21a-4e8f-91dd-2218ffef7257' validation error : FORCE_00 - A configuration is invalid because: Badly formed SOQL Query: SELECT Id, Name from Account WHERE Id > '${offset}' ORDER BY Id
com.streamsets.datacollector.runner.PipelineRuntimeException: CONTAINER_0800 - Pipeline '7e5f1b20-c21a-4e8f-91dd-2218ffef7257' validation error : FORCE_00 - A configuration is invalid because: Badly formed SOQL Query: SELECT Id, Name from Account WHERE Id > '${offset}' ORDER BY Id
        at com.streamsets.datacollector.execution.runner.common.ProductionPipeline.run(ProductionPipeline.java:127)
        at com.streamsets.datacollector.execution.runner.common.ProductionPipelineRunnable.run(ProductionPipelineRunnable.java:79)
        at com.streamsets.datacollector.execution.runner.standalone.StandaloneRunner.start(StandaloneRunner.java:766)
        at com.streamsets.datacollector.execution.runner.common.AsyncRunner$4.call(AsyncRunner.java:153)
        at com.streamsets.pipeline.lib.executor.SafeScheduledExecutorService$SafeCallable.call(SafeScheduledExecutorService.java:233)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
        at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Thanks, Mehul

edit retag flag offensive close merge delete

Comments

Can you copy the full stack trace from sdc.log and edit it into your question?

metadaddy gravatar imagemetadaddy ( 2018-07-30 16:39:44 -0600 )edit

Also, what are the settings in the Salesforce origin Query tab? In particular, the Initial Offset setting.

metadaddy gravatar imagemetadaddy ( 2018-07-30 16:45:49 -0600 )edit

Selected Bulk API. SOQL Query: SELECT Id, Name from Account WHERE Id > '${OFFSET}' ORDER BY Id Initial offset : 000000000000000 Offset Field: Id

Mehul gravatar imageMehul ( 2018-07-31 11:43:26 -0600 )edit

There should be one or more 'Caused By' sections after that initial stack trace - could you add those?

metadaddy gravatar imagemetadaddy ( 2018-07-31 13:47:59 -0600 )edit

No. There is no other log information.

Mehul gravatar imageMehul ( 2018-07-31 17:22:01 -0600 )edit

1 Answer

Sort by ยป oldest newest most voted
0

answered 2018-08-01 13:07:20 -0600

metadaddy gravatar image

You are hitting SDC-5848 - queries are rejected unless they use uppercase SELECT ... FROM. This was fixed in StreamSets Data Collector 2.5.0.0. You can work around it by changing your query from

SELECT Id, Name from Account WHERE Id > '${OFFSET}' ORDER BY Id

to

SELECT Id, Name FROM Account WHERE Id > '${OFFSET}' ORDER BY Id

I would recommend upgrading to the current version - 2.4.1.0 is very old and there have been many bug fixes and improvements since then.

edit flag offensive delete link more

Comments

I upgraded to 3.4.0 and it worked fine.

Mehul gravatar imageMehul ( 2018-08-03 11:50:19 -0600 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-07-30 15:09:29 -0600

Seen: 76 times

Last updated: Aug 01