Druid: Inserting Nested Json into Druid

[Harish346]

Hi,
Is it possible to insert nested json into druid? If so then how to insert json array dynamically?

[gianm]

Hi @Harish346, Druid has a flat data model, but it can ingest nested json if you write a flattenSpec that tells Druid how to flatten nested data: http://druid.io/docs/latest/ingestion/flatten-json.html

[Harish346]

Hi @gianm ,I read the document but I have a json array in which number of values vary each time. Is it possible to write flatten spec for all varying values in an array?For example below is my json data in which the number of values in array1 varies each time
{
"Sheet1": [
{
"array1": [
"xyz",
"zzz",
"yyy"
]
}
]
}

[gianm]

Hi @Harish346, you could write a flattenSpec that pulls out $.Sheet1.array1, and that would create a multi-value column in Druid. This doc explains how those work: http://druid.io/docs/latest/querying/multi-value-dimensions.html

[Harish346]

Hi @gianm
This is my ingestion spec file

{
"type": "index_hadoop",
"spec": {
"ioConfig": {
"type": "hadoop",
"inputSpec": {
"type": "static",
"paths": "quickstart/samplefile.json"
}
},
"dataSchema": {
"dataSource": "samplefile",
"granularitySpec": {
"type": "uniform",
"segmentGranularity": "day",
"queryGranularity": {
"type": "none"
},
"intervals": null
},
"parser": {
"type": "hadoopyString",
"parseSpec": {
"format": "json",
"flattenSpec": {
"useFieldDiscovery": true,
"fields": [
{
"type": "path",
"name": "category",
"expr": "$..category"
},
{
"type": "path",
"name": "author",
"expr": "$..author"
},
{
"type": "path",
"name": "price",
"expr": "$..price"
}
]
},
"dimensionsSpec": {
"dimensions": [
"category",
"author"
]
},
"timestampSpec": {
"format": "iso",
"column": "timestamp"
}
}
},
"metricsSpec": [ ]
},
"tuningConfig": {
"type": "hadoop",
"partitionsSpec": {
"type": "hashed",
"targetPartitionSize": 5000000
},
"jobProperties": {
}
}
}
}

and following is my json data

{
"store": {
"timestamp": "2018-08-01",
"book": [
{
"category": "reference",
"author": "Nigel Rees",
"price": 8.95
},
{
"category": "fiction",
"author": "Evelyn Waugh",
"price": 12.99
},
{
"category": "fiction",
"author": "Herman Melville",
"price": 8.99
},
{
"category": "fiction",
"author": "J. R. R. Tolkien",
"price": 22.99
}
]
}
}

when i try to insert data into druid the task get created but the status gets always failed.
Below is my log

uid-indexing-service-0.12.1.jar:0.12.1]
at java.util.concurrent.FutureTask.run(FutureTask.java:266) [?:1.8.0_171]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_171]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_171]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_171]
Caused by: java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
at io.druid.indexing.common.task.HadoopTask.invokeForeignLoader(HadoopTask.java:219) ~[druid-indexing-service-0.12.1.jar:0.12.1]
... 7 more
Caused by: io.druid.java.util.common.ISE: Job[class io.druid.indexer.DetermineHashedPartitionsJob] failed!
at io.druid.indexer.JobHelper.runJobs(JobHelper.java:391) ~[druid-indexing-hadoop-0.12.1.jar:0.12.1]
at io.druid.indexer.HadoopDruidDetermineConfigurationJob.run(HadoopDruidDetermineConfigurationJob.java:91) ~[druid-indexing-hadoop-0.12.1.jar:0.12.1]
at io.druid.indexing.common.task.HadoopIndexTask$HadoopDetermineConfigInnerProcessing.runTask(HadoopIndexTask.java:325) ~[druid-indexing-service-0.12.1.jar:0.12.1]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_171]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_171]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_171]
at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_171]
at io.druid.indexing.common.task.HadoopTask.invokeForeignLoader(HadoopTask.java:219) ~[druid-indexing-service-0.12.1.jar:0.12.1]
... 7 more
2018-08-08T18:23:07,101 INFO [task-runner-0-priority-0] io.druid.indexing.overlord.TaskRunnerUtils - Task [index_hadoop_samplefile_2018-08-08T18:22:53.394Z] status changed to [FAILED].
2018-08-08T18:23:07,105 INFO [task-runner-0-priority-0] io.druid.indexing.worker.executor.ExecutorLifecycle - Task completed with status: {
"id" : "index_hadoop_samplefile_2018-08-08T18:22:53.394Z",
"status" : "FAILED",
"duration" : 7055
}

[Harish346]

hi @gianm can you help me in this?

[stale[bot]]

This issue has been marked as stale due to 280 days of inactivity. It will be closed in 2 weeks if no further activity occurs. If this issue is still relevant, please simply write any comment. Even if closed, you can still revive the issue at any time or discuss it on the [email protected] list. Thank you for your contributions.

[cf-is-your-dad]

did anyone ever figure this out?

[gamebusterz]

Is there any way to do this yet?

[gianm]

Flattening is still the best way — https://druid.apache.org/docs/latest/ingestion/data-formats.html#flattenspec

[cf-is-your-dad]

tbc, i do not thing druid flattening is the way to flatten nested json. do
this prior to reaching druid is the way to go.

On Thu, Apr 30, 2020 at 12:11 AM Gian Merlino [e-mail]
wrote:

Flattening is still the best way —
https://druid.apache.org/docs/latest/ingestion/data-formats.html#flattenspec

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/apache/druid/issues/6113#issuecomment-621601184, or
unsubscribe
https://github.com/notifications/unsubscribe-auth/AMWPY6FAMGSJ5GDD26S2XMTRPD3ALANCNFSM4FOBEYVQ
.

[gamebusterz]

@gianm Yup, seems to work for now, but I'll need to constantly change the inputSpec whenever an unexpected field comes in the data.

One way to probably handle it could be flattening all the elements of the JSON, and allowing one to specify what parts not to flatten or which keys to ignore/not flatten. But that may require a lot of changes, so hoping to have something like this in the future. 🤞

[averma111]

Hello Everyone,

I have a use case where I have to load complete API response into druid in json and xml format as it is, without making any changes to the API response.

Its 7 years worth of data and its not read intensive so performance is not the issue.

Any suggestions.

Thanks,
Ashish