Cannot acquire connection [Amazon](500150) Error setting/closing connection: UnknownHostException (RDB Loader)

dadasami · March 4, 2021, 9:25am

Hi all,

Currently, we are deploying Shredder and RDB Loader on two separate Fargate instances. The enriched data gets shredded with “shredding_complete.json” in the root folders and SQS sends messages to the RDB Loader, however we get the following error in the RDB Loader fargate task logs:

Here is our config.hocon setup:

{
  "name": "***",
  "id": "4113ba83-2797-4436-8c92-5ced0b8ac5b6",

  "region": "eu-west-1",
  "messageQueue": "SQS_QUEUE",

  "shredder": {
    "input": "SP_ENRICHED_URI",
    "output": "SP_SHREDDED_GOOD_URI",
    "outputBad": "SP_SHREDDED_BAD_URI",
    "compression": "GZIP"
  },

  "formats": {
    "default": "JSON",
    "json": [ ],
    "tsv": [ ],
    "skip": [ ]
  },

  "jsonpaths": "s3://snowplow-schemas-STAGE/jsonpaths/",

  "storage": {
    "type": "redshift",
    "host": "redshift.amazon.com",
    "database": "sp_redshift_database",
    "port": 5439,
    "roleArn": "arn:aws:iam::AWS_ACCOUNT_NUMBER:role/RedshiftLoadRole",
    "schema": "atomic",
    "username": "sp_user",
    "password": "DB_REDSHIFT_PASSWORD",
    "jdbc": {"ssl": true},
    "maxError": 10,
    "compRows": 100000
  },

  "steps": ["analyze"],

  "monitoring": {
    "snowplow": null,
    "sentry": null
  }
}

Our Redshift configuration:

The Redshift security group inbound and outbound allows all traffic, at the moment.

And here is the Redshift role policies:

mike · March 4, 2021, 9:57am

Are you setting this variable to your Redshift host endpoint?

dadasami · March 4, 2021, 11:25am

Thanks for the great catch @mike.

Now, we are getting the next error, which says “java.sql.SQLException: Amazon Invalid operation: schema “atomic” does not exist;”. I believe this comes from the config.hocon file:

dadasami:

  "storage": {
    "type": "redshift",
    "host": "redshift.amazon.com",
    "database": "sp_redshift_database",
    "port": 5439,
    "roleArn": "arn:aws:iam::AWS_ACCOUNT_NUMBER:role/RedshiftLoadRole",
    "schema": "atomic",
    "username": "sp_user",
    "password": "DB_REDSHIFT_PASSWORD",
    "jdbc": {"ssl": true},
    "maxError": 10,
    "compRows": 100000
  },

We are using self-describing json schemas. Does that mean the atomic schema type has to be changes?

Colm · March 4, 2021, 11:44am

The atomic schema and the relevant tables need to be created before you can load into them.

In the relevant setup guide, there are some pre-defined scripts to do this. The atomic schema is created here. It might be helpful to double check that the other steps in the guide have been done too.

Colm · March 4, 2021, 11:48am

Oh and to explain this part - there are two different uses of the word ‘schema’ involved - the schema in your error refers to a Redshift database schema.

JSONschema is a standard for defining the structure of events, the two aren’t related. The pipeline uses JSONschemas to determine validity of data and also to create table definitions for the database (sometimes called a ‘table schema’ just to make sure it’s as confusing as possible ).

dadasami · March 4, 2021, 5:13pm

Thanks a lot @Colm for the explanations

I tried running “psql -h sp-redshift-cluster-***.cziwymixltdg.eu-west-1.redshift.amazonaws.com -U sp_user -d sp_redshift_database -p 5439 -W -f atomic-def.sql”, from my local machine, but psql is unable to establish a connection to the redshift cluster with the following error:

psql: error: could not connect to server: Operation timed out
	Is the server running on host "sp-redshift-cluster-gitc-dev.cziwymixltdg.eu-west-1.redshift.amazonaws.com" (11.222.59.12) and accepting
	TCP/IP connections on port 5439?

I tried to apply the instructions here, but unfortunately the documentation is somewhat outdated and I could not find my way through the new AWS console. I made the security group of the redshift fully permissive and I added the second inbound rule limited to my machine IP and still it did not work. Am I missing a point?

mike · March 4, 2021, 6:57pm

This generally happens if either

The Redshift cluster isn’t publicly accessible (e.g., has a public IP)
The security group isn’t allowing access to the cluster
You are behind a firewall / network proxy that doesn’t allow outbound connections to certain resources / ports.

For 1 check if the cluster is accessible on the ‘Properties’ page under ‘Network and security settings’

For 2 check what rules are attached to the security groups in ‘Cluster security groups’ also in the Network and security settings.

dadasami · March 5, 2021, 10:54am

Once again, thanks a lot @mike Our Redshift was not public, and now we are able to create the tables. However, we ran into another issue with the RDB Loader:

INFO 2021-03-05 09:28:35.807: RDB Loader [***] has started. Listening sp-sqs-queue-***.fifo
INFO 2021-03-05 10:08:59.504: Received new message. Total 1 messages received, 0 loaded, 0 attempts has been made to load current folder
INFO 2021-03-05 10:09:00.105: New data discovery at run=2021-03-05-09-40-05 with following shredded types:
  * iglu:com.snowplowanalytics.snowplow/atomic/jsonschema/1-*-* TSV
  * iglu:com.***/generic_tracking_event/jsonschema/1-*-* (s3://snowplow-schemas-***/jsonpaths/com.***/generic_tracking_event_1.json)
  * iglu:com.***/minimal_tracking_event/jsonschema/1-*-* (s3://snowplow-schemas-***/jsonpaths/com.***/minimal_tracking_event_1.json)
RDB Loader unknown error in executeUpdate
java.lang.NullPointerException
	at com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.$anonfun$executeUpdate$1(JDBC.scala:142)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.executeUpdate(JDBC.scala:144)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at liftF @ com.snowplowanalytics.iglu.client.resolver.Resolver$.$anonfun$parse$3(Resolver.scala:240)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at tailRecM @ retry.package$RetryingOnSomeErrorsPartiallyApplied.apply(package.scala:96)
	at tailRecM @ retry.package$RetryingOnSomeErrorsPartiallyApplied.apply(package.scala:96)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.loading.Load$.load(Load.scala:68)
	at *> @ com.snowplowanalytics.snowplow.rdbloader.Main$.$anonfun$run$1(Main.scala:43)
	at use @ com.snowplowanalytics.snowplow.rdbloader.Main$.$anonfun$process$1(Main.scala:75)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$.getConnection(JDBC.scala:104)
	at flatMap @ retry.package$RetryingOnSomeErrorsPartiallyApplied.$anonfun$apply$3(package.scala:97)
	at tailRecM @ retry.package$RetryingOnSomeErrorsPartiallyApplied.apply(package.scala:96)
	at make @ com.snowplowanalytics.snowplow.rdbloader.dsl.Environment.makeBusy(Environment.scala:43)
RDB Loader unknown error in executeUpdate
java.lang.NullPointerException
	at com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.$anonfun$executeUpdate$1(JDBC.scala:142)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.executeUpdate(JDBC.scala:144)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at map @ com.snowplowanalytics.iglu.client.Client$.parseDefault(Client.scala:56)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at map @ com.snowplowanalytics.iglu.client.Client$.parseDefault(Client.scala:56)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at *> @ com.snowplowanalytics.snowplow.rdbloader.dsl.Logging$$anon$1.$anonfun$info$2(Logging.scala:86)
	at void @ fs2.concurrent.PubSub$Subscriber.complete(PubSub.scala:104)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.executeUpdate(JDBC.scala:144)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at liftF @ com.snowplowanalytics.iglu.client.resolver.Resolver$.$anonfun$parse$3(Resolver.scala:240)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at tailRecM @ retry.package$RetryingOnSomeErrorsPartiallyApplied.apply(package.scala:96)
	at tailRecM @ retry.package$RetryingOnSomeErrorsPartiallyApplied.apply(package.scala:96)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
java.lang.NullPointerException
	at com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$.$anonfun$interpreter$4(JDBC.scala:84)
	at *> @ com.snowplowanalytics.snowplow.rdbloader.dsl.Logging$$anon$1.$anonfun$info$2(Logging.scala:86)
	at void @ fs2.concurrent.PubSub$Subscriber.complete(PubSub.scala:104)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.executeUpdate(JDBC.scala:144)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at map @ com.snowplowanalytics.iglu.client.Client$.parseDefault(Client.scala:56)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at map @ com.snowplowanalytics.iglu.client.Client$.parseDefault(Client.scala:56)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at *> @ com.snowplowanalytics.snowplow.rdbloader.dsl.Logging$$anon$1.$anonfun$info$2(Logging.scala:86)
	at void @ fs2.concurrent.PubSub$Subscriber.complete(PubSub.scala:104)
	at flatMap @ com.snowplowanalytics.snowplow.rdbloader.dsl.JDBC$$anon$1.executeUpdate(JDBC.scala:144)
	at flatMap @ com.snowplowanalytics.iglu.client.resolver.Resolver$.parse(Resolver.scala:237)
	at liftF @ com.snowplowanalytics.iglu.client.resolver.Resolver$.$anonfun$parse$3(Resolver.scala:240)
ERROR 2021-03-05 10:09:01.702: Fatal failure during message processing (base s3://sp-shredded-***/good/run=2021-03-05-09-40-05/), message hasn't been ack'ed. Data loading error java.lang.NullPointerException

anton · March 8, 2021, 11:59am

Hi @dadasami,

Can you double-check your host? Specifically that it doesn’t contain something like jdbc: or :5439. The only thing that can return null there as far as I can see (but it’s hard to reverse engineer as Redshift driver doesn’t come with sources) is connect method when URI is not supported.

dadasami · March 12, 2021, 11:04am

Thanks @anton. The issue disappeared after updating the Redshift and RDB Loader roles and policies. It might have happened due to a lack of permissions.

rmichaelvp · July 1, 2021, 11:26pm

Hi @dadasami I’m haven an related error, could you tell me wish permissions did you gave to each one of them please?

Topic		Replies	Views
RDB Loader 1.0.0 "Database error: [Amazon][JDBC](10060) Connection has been closed" Storage targets	2	1742	May 28, 2021
RDBLoader failing to process SQS messages For engineers	3	1750	May 31, 2021
RDB Loader "could not load a folder" AWS real-time pipeline	1	1043	May 28, 2021
How to set region for RDB Loader? Storage targets	6	1636	October 7, 2021
S3 curl Error on rdb loader For engineers	1	2012	April 20, 2021

Cannot acquire connection [Amazon](500150) Error setting/closing connection: UnknownHostException (RDB Loader)

Related topics