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

Mongo DB Json Extract

Hi,

I have field in mongodb as greater than 65535 array of JSON data..

I'm used varchar(max). It only get 65535 character count from source to redshift. Any option to increase varchar limit in redshift.

I'm trying to split and load data to redshift using mongo DB component , But I'm Getting stuck in that process.

Any Possible way to solve this issue.

Thanks,
Mathan

5 Community Answers

Matillion Agent  

David Lipowitz —

Hi Mathan,

Unfortunately there is no way to increase the size of a Redshift VARCHAR field beyond 65535 bytes.

Have you tried setting the Flatten Arrays property on the MongoDB component?

Best Regards,
Dave


Mathan Selvaraj —

Hi Dave,

Badluck! My data array lenth is not static. It is changed for each Id.

Is there any possibilities pull from mongodb data without mongodb component?

Or Set a limit for array in mongodb sql query?

Thanks,
Mathan


Matillion Agent  

Laura Malins —

Hi Mathan

Unfortunately the limit of 65535 bytes is a Redshift limit and there’s nothing that can be done to change this, from Matillion or otherwise.

I assume when you say the length isn’t static that you mean the elements in the arrays vary so Flatten Arrays won’t work? Flatten Arrays should cope with varying lengths.

You need to think about how you want this mongo data to be stored in Redshift. It’s clear that your array won’t fit into single column in a Redshift table, so can it be split up. There may be a possibility to do this from the Matillion MongoDB component.

You can use python to extract the data from MongoDB as well, but again that would require you to understand how it would be loaded into Redshift because you will still hit the issue with varchar(max) being too small.

If you’re willing to share some sample data we could take a look and possibly suggest some options?

Thanks
Laura


Mathan Selvaraj —

Hi laura,

It will continue as scedule one so could not apply flattern option.because tmrw it will increase array lenth change from 100 to 1000.

Tried with mongodb sql component could not set limit for particular array if it is working i would using multiple mongodb component different array lenth.

Can you send any sample for python mongodb load?

Thanks,
Mathan


Matillion Agent  

Laura Malins —

Hi Mathan

Unfortunately I don’t have an example python script for loading data from MongoDB, however a quick Google returns many results, including:

http://api.mongodb.com/python/current/tutorial.html

As I said before if you can provide us a sample, or even an example of the sort of data you have in MongoDB we can look to see if it’s possible to use Matillion to load this.

Thanks
Laura

Post Your Community Answer

To add an answer please login