Zoho CRM to Redshift connection error

  • 147Views
  • Last Post 11 August 2017
0
votes
Jonathan Brink posted this 01 August 2017

Hi Skyvia!  We're looking to import data from Zoho CRM into our Redshift.  We've got an S3 bucket up and running for this process too.  When we attempt to connect, we get the no pg_hba.conf entry for host error.  We've verified these Redshift credentials via another partner that successfully connected, so that part is definitely correct.  The error does say, though, "SSL off" at the end: our Redshift requires SSL so perhaps that's our issue.

Do you have any insight for us?  Can SSL be activated? 

Attached Files

Order By: Standard | Newest | Votes
0
votes
Jonathan Brink posted this 01 August 2017

If that image is not working properly, the full error is:

no pg_hba.conf entry for host

"40.118.246.204", user "hotelsforhope",

database "site_10700", SSL off

0
votes
Jonathan Brink posted this 02 August 2017

For those wondering, I solved this.  It turns out there was an option to "require" SSL that I had missed previously.  The connection was successful after adjusting that.

1
votes
Mariia Zaharova posted this 03 August 2017

Jonathan, we added the SSL Mode option yesterday and did not have time to notify you after updating Skyvia.

Sorry for the delay and best regards,

Mariia

  • Supported by
  • Jonathan Brink
0
votes
Jonathan Brink posted this 03 August 2017

Thanks, Mariia!  We successfully got some Zoho data into Redshift using Skyvia.  But the import eventually failed in with an error!  To get to this point, we FIRST tried to attempt a replication. Even though it failed, the replication created a table in our Redshift.  Next, we tried the IMPORT option again.  This time, the table that the replication procedure created appeared for the destination (before, no tables would appear for destination).  We selected this and it began copying data until it failed with the error you see below.

Zoho-->Redshift import error

We are going to look into Redshift logging on our side to determine if there may be some sort of write lock at play, but we've reached a wall with how much we can determine from the Skyvia side of things.  What ideas to you have here?  Or is anyone from Skyvia available for a call please?  We are excited about this initiative

Thanks,

Jon

0
votes
Mariia Zaharova posted this 04 August 2017

Load into table 'custommodule1' failed. Check 'stl_load_errors' system table for details.

This error occurs on the Redshift side and doesn't relate to Skyvia. This error is related to the data being imported to Redshift. For example, this error may occur in these cases:

  •  data types mismatch (e.g. insert decimal_15_5 into decimal_14_0)
  •  attempt to insert special characters with Unicode = true
  •  not-null field is not filled
  •  string is too long, etc.

Please open Query Tool and run this query with your Redshift connection: select * from 'stl_load_errors'

You will get the table with the errors as a result.

 

0
votes
Jonathan Brink posted this 04 August 2017

Thanks, Mariia!  The err_reason we see is "String length exceeds DDL length", suggesting there is data that exceeds the character limit of the table.

The table was created via the Skyvia workflow;  is there a way to find out what character limit the tool has applied to the table? Is there a way to adjust this limit? Can you advise on the appropriate workflow or best practice for importing data from Zoho CRM into Redshift?

0
votes
Mariia Zaharova posted this 08 August 2017

Thank you for the reply. We are processing your request. We will contact you soon.

0
votes
Mariia Zaharova posted this 11 August 2017

Redshift has limitations on the maximum column length - it can't be more than 65535 bytes, i.e 32762 characters for varchar and 16382 characters for nvarchar: http://docs.aws.amazon.com/redshift/latest/dg/r_Character_types.html

Most likely, you are replicating the data with the longer strings.

Unfortunately, we cannot affect these Redshift limitations.

Close