Druid SQL returns way less data than what's on S3.

[didip]

Affected Version

0.21.1

Description

I see that we have way more data on the S3 folders but this query returns way less number of days.

I should already have the full July dates but many of them are missing in my query.

SELECT DATE_TRUNC('day', "__time") as "day", SUM("count") as "count"
FROM "mytable" GROUP BY DATE_TRUNC('day', "__time") order by DATE_TRUNC('day', "__time") DESC

Table:
... Where's the rest of the days? ...
2021-07-15T00:00:00.000Z 00000000001979009692
... Where's the rest of the days? ...
2021-07-11T00:00:00.000Z 00000000001693378623
... Where's the rest of the days? ...

I also unchecked these 3, but the result is the same:

All my tables are shown as "Fully Available"

[bananaaggle]

Is this data loaded by batch job? If it was a batch job, appendToExisting in IOConfig is true or false?

[didip]

Data is loaded by batch job, I will check the IOConfig.

[didip]

appendToExisting is not defined, so by default it's false.

[bananaaggle]

appendToExisting is not defined, so by default it's false.

I think some data is overwritten, you can set it be true and test.

[didip]

This problem seems to happen when user sets maxNumConcurrentSubTasks really high, for example: 100 or 200.

[didip]

Tried appendToExisting: true. The ingestion is successful but data is still not queryable.

Forgot to mention, the partitionSpec is dynamic.

[bananaaggle]

Is there some error log in ingestion or query process? Can you reproduce it stably?

[didip]

Yes, this has been happening the last 3 weeks actually. Every ingestion is successful, data appears on S3, but unquery-able.
The input data is huge, 3.5TB per day, this is why we desperately need high number of parallelism to have an acceptable SLA.

When the task UI was working, every single log from index_parallel and sub_tasks are clean, they all finished successfully.

I am currently unable to load the tasks UI because there are way too many druid_tasks records in the DB, thus I cannot get the latest logs.

I will try to search the broker queries and see if there are any errors.

[didip]

if you can produce fake data with thousands of parquet files and run ingestion with 200 parallelism, I think you can reproduce this issue.

[bananaaggle]

Can you set reportParseExceptions and logParseExceptions be true in you tunningConfig? It will allow you catch exception when some trouble happened.

[didip]

Thank you for the suggestion, will try it asap

[didip]

I rebuilt my middle managers, deleted everything inside druid_tasks, druid_tasklocks, druid_tasklogs, deleted the local files in middle manager hosts, starting only 1 index_parallel task with maxNumConcurrentSubTasks: 200.

I added reportParseExceptions: true and logParseExceptions: true, as well as appendToExisting: true in the job spec.

The tasks UI is working again now, because druid_tasks is empty.

So far, I do not see a single error in some of the sub_task logs that I randomly picked, the index_parallel logs looked good so far as well, not a single error. There's not a single failed sub_task so far.

tuningConfig: {
	type: "index_parallel",
	maxRowsPerSegment: 5000000,
	appendableIndexSpec: {
		type: "onheap"
	},
	maxRowsInMemory: 100000,
	maxBytesInMemory: 0,
	maxTotalRows: null,
	numShards: null,
	splitHintSpec: null,
	partitionsSpec: {
		type: "dynamic",
		maxRowsPerSegment: 5000000,
		maxTotalRows: null
	},
	indexSpec: {
		bitmap: {
			type: "roaring",
			compressRunOnSerialization: true
		},
		dimensionCompression: "lz4",
		metricCompression: "lz4",
		longEncoding: "longs",
		segmentLoader: null
	},
	indexSpecForIntermediatePersists: {
		bitmap: {
			type: "roaring",
			compressRunOnSerialization: true
		},
		dimensionCompression: "lz4",
		metricCompression: "lz4",
		longEncoding: "longs",
		segmentLoader: null
	},
	maxPendingPersists: 0,
	forceGuaranteedRollup: false,
	reportParseExceptions: true,
	pushTimeout: 0,
	segmentWriteOutMediumFactory: null,
	maxNumConcurrentSubTasks: 200,
	maxRetry: 5,
	taskStatusCheckPeriodMs: 1000,
	chatHandlerTimeout: "PT10S",
	chatHandlerNumRetries: 5,
	maxNumSegmentsToMerge: 100,
	totalNumMergeTasks: 10,
	logParseExceptions: true,
	maxParseExceptions: 0,
	maxSavedParseExceptions: 0,
	maxColumnsToMerge: -1,
	buildV9Directly: true,
	partitionDimensions: []
}

ioConfig: {
	type: "index_parallel",
	inputSource: {
		type: "s3",
		uris: null,
		prefixes: [
			"s3://mybucket/mytable/dt=20210807"
		],
		objects: null,
		properties: null
	},
	inputFormat: {
		type: "parquet",
		flattenSpec: {
			useFieldDiscovery: true,
			fields: []
		},
		binaryAsString: false
	},
	appendToExisting: true
}

[bananaaggle]

What is the result? SQL returned less data again?

[didip]

The index_parallel job finished in 6 hours. This is good, that means 200 parallelism works to speed up ingestion.

The log is super clean. Unfortunately I saw this in the log:

2021-08-11T05:01:18,381 WARN [task-runner-0-priority-0] org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexSupervisorTask - maxParseExceptions is not supported yet
2021-08-11T05:01:18,381 WARN [task-runner-0-priority-0] org.apache.druid.indexing.common.task.batch.parallel.ParallelIndexSupervisorTask - logParseExceptions is not supported yet

But everything finished successfully.

2021-08-11T11:19:17,142 INFO [task-monitor-0] org.apache.druid.indexing.common.task.batch.parallel.TaskMonitor - [4000/4000] tasks succeeded