Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

Cassandra json extraction

I would like to know how do we get cassandra's column output like <attached>
into RS.


{
"id": "0001",
"type": "donut",
"name": "Cake",
"ppu": 0.55,
"batters":
{
"batter":
[
{ "id": "1001", "type": "Regular" },
{ "id": "1002", "type": "Chocolate" },
{ "id": "1003", "type": "Blueberry" },
{ "id": "1004", "type": "Devil's Food" }
]
},
"topping":
[
{ "id": "5001", "type": "None" },
{ "id": "5002", "type": "Glazed" },
{ "id": "5005", "type": "Sugar" },
{ "id": "5007", "type": "Powdered Sugar" },
{ "id": "5006", "type": "Chocolate with Sprinkles" },
{ "id": "5003", "type": "Chocolate" },
{ "id": "5004", "type": "Maple" }
]
}

3 Community Answers

Matillion Agent  

Laura Malins —

Hi Dhira

You can use the Cassandra Query component to pull the output into Redshift however it won’t parse the JSON. The JSON can be stored directly in the Redshift table. You will need to write this Redshift table to a file and use a JSON editor like JQ from a Bash component to parse the JSON and split it out into separate columns.

Thanks
Laura


Dhira Dharman —

Does Matillion currently has any built-in component that could do this ?


Matillion Agent  

Ian Funnell —

Hi Dhira,

Matillion takes advantage of the target database’s own JSON functions, rather than creating bespoke components for this. So for example, with Redshift you can use JSON_EXTRACT_PATH_TEXT among other JSON functions

To get access to any SQL function, you need to use a Calculator component in a Transformation job.

Best regards,
Ian

Post Your Community Answer

To add an answer please login