Tableau, dremio and mongodb - error: New schema found and recorded. Please reattempt the query. Multiple attempts may be necessary to fully learn the schema


when running a query on mongo, we continuously face this error. What is strange is that dremio doesn’t recognize the timestamp as a timestamp but as a date. It however is able to extract the exact hours, minutes etc.

The query runs without any issue in Dremio but when we run from Tableau we get the above mentioned error if we want to group the data by any of the timestamp fields. I’ve also uploaded the query profile generated by dremio when running from Tableau. (119,8 KB)

Here’s the query in dremio:

SELECT “_id”, type, loc, cast(ts as timestamp) as ts, cast(extract(year from ts) as varchar(4)) as tsyear, cast(extract(month from ts) as varchar(2)) as tsmonth, cast(extract(day from ts) as varchar(2)) as tsday,

cast(extract(hour from ts) as varchar(2)) as tshour, cast(extract(minute from ts) as varchar(2)) as tsminutes, “particleMeasurements”.“meas”[‘sampleTime’] AS sampleTime, “particleMeasurements”.“meas”[‘status’][‘statusValue’] AS statusValue, “particleMeasurements”.“meas”[‘status’][‘validationMode’] AS validationMode, “particleMeasurements”.“meas”[‘status’][‘calibrationExpired’] AS calibrationExpired, “particleMeasurements”.“meas”[‘status’][‘laser’][‘laserOK’] AS statusLaserOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘powerOK’] AS statusLaserPowerOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘currentOK’] AS statusLaserCurrentOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘supplyOK’] AS statusLaserSupplyOK, “particleMeasurements”.“meas”[‘status’][‘laser’][‘lifeOK’] AS statusLaserLifeOK, “particleMeasurements”.“meas”[‘status’][‘flow’][‘flowOK’] AS statusFlowOK, “particleMeasurements”.“meas”[‘status’][‘flow’][‘noFlow’] AS statusNoFlow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘particleOverflow’] AS statusInstrumentParticleOverflow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘instrumentOK’] AS statusInstrumentOK, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘tresholdHigh’] AS statusInstrumentTresholdHigh, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘tresholdLow’] AS statusInstrumentTresholdLow, “particleMeasurements”.“meas”[‘status’][‘instrument’][‘samplerOK’] AS statusInstrumentSamplerOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘supplyOK’] AS statusPhotoAmpSupplyOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘backgroundOK’] AS statusPhotoAmpBackgroundOK, “particleMeasurements”.“meas”[‘status’][‘photoAmp’][‘photoAmpOK’] AS statusPhotoAmpOK, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch1’] AS particleChannelCh1, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch2’] AS particleChannelCh2, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch3’] AS particleChannelCh3, “particleMeasurements”.“meas”[‘values’][‘particleChannels’][‘ch4’] AS particleChannelCh4, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch1’] AS analogChannelCh1, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch2’] AS analogChannelCh2, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch3’] AS analogChannelCh3, “particleMeasurements”.“meas”[‘values’][‘analogChannels’][‘ch4’] AS analogChannelCh4, “particleMeasurements”.“meas”[‘values’][‘technical’][‘laserSupply’] AS laserSupply, “particleMeasurements”.“meas”[‘values’][‘technical’][‘laserVoltage’] AS laserVoltage, “particleMeasurements”.“meas”[‘values’][‘technical’][‘backgroundVolt’] AS backgroundVolt, “particleMeasurements”.“meas”[‘values’][‘internalTemps’][‘head’] AS internalHeadTemp, “particleMeasurements”.“meas”[‘values’][‘internalTemps’][‘base’] AS internalBaseTemp

FROM mongoPfizerAWS.testPfizer.particleMeasurements AS particleMeasurements

Hi, @dbrys

We’re seeing the exact same two problems. For the “date vs timestamp” issue, we also were able to workaround via casting to Timestamp (see here: MongoDB Filtering Bug? (using Dremio 3.0.0, MongoDB 3.6.8))

The second issue is related to a schema guessing bug when you switch from Preview mode (which I guess you use for testing your queries in Dremio UI) to “real” mode, see here: Bug Report: Schema Discovery Problem with nested documents (Dremio 3.0.0 and MongoDB 3.6.8)). Unfortunately, I’m not aware of any workarounds – if you find one, please let me know.

Best, Tim

Hi, thx for the feedback. The casting to timestamp doesn’t solve the issue apparently.
What I’ve noticed is that when we use the mongodb odbc driver and the mongodb bi connector directly we can run queries as long as we stick to day level. Once we start to drill down to hour level we also face the same issues.

I guess it could have to do with how the date/timestamp is actually stored in Mongo but seems to be like a standard ISODate. We’ll dig further.