Destination Redshift: cannot Normalize over SSH Tunnel

[imreACTmd]

Connector Name

destination-redshift

Connector Version

0.4.0 to 0.4.7

What step the error happened?

During the sync

Revelant information

I have been using the Redshift Destination successfully so I have experience with it running properly.

Added SSH Bastion host to a new Redshift Destination. The Destination Test is successful.

Set up connection, and the Raw delivery part works correctly via the SSH tunnel, but the Normalization fails with Host unreachable. The Raw tables get delivered without a problem, but Normalization claims it's unable to connect to the destination host.

Turning off Normalization makes the Sync work using the SSH Tunnel correctly.

Relevant log output

The successful initial part:
2023-05-11 19:13:52 destination > INFO i.a.i.d.j.c.SwitchingDestination(getConsumer):65 Using destination type: STANDARD
2023-05-11 19:13:53 destination > INFO i.a.i.b.s.SshTunnel(getInstance):204 Starting connection with method: SSH_KEY_AUTH
2023-05-11 19:13:54 destination > INFO i.a.i.b.s.SshTunnel(openTunnel):367 Established tunneling session to default.279583803714.us-east-1.redshift-serverless.amazonaws.com:5439. Port forwarding started on /127.0.0.1:44253 
...
2023-05-11 19:14:59 destination > INFO i.a.i.d.r.o.RedshiftSqlOperations(insertRecordsInternal):57 actual size of batch: 5795
2023-05-11 19:15:03 destination > INFO i.a.i.d.r.InMemoryRecordBufferingStrategy(flushAllBuffers):91 Flushing completed for table
2023-05-11 19:15:03 destination > INFO i.a.i.b.IntegrationRunner(runInternal):186 Completed integration: io.airbyte.integrations.destination.redshift.RedshiftDestination
2023-05-11 19:15:03 destination > INFO i.a.i.d.r.RedshiftDestination(main):76 completed destination: class io.airbyte.integrations.destination.redshift.RedshiftDestination
2023-05-11 19:15:03 INFO i.a.w.g.DefaultReplicationWorker(replicate):274 - Source and destination threads complete.
2023-05-11 19:15:03 INFO i.a.w.g.DefaultReplicationWorker(getReplicationOutput):569 - sync summary: {
  "status" : "completed",
  "recordsSynced" : 58646,
  "bytesSynced" : 17360125,
  "startTime" : 1683832430689,
  "endTime" : 1683832503506,
    "destinationWriteEndTime" : 1683832503505,
    "destinationWriteStartTime" : 1683832430765,
    "meanSecondsBeforeSourceStateMessageEmitted" : 0,
    "maxSecondsBeforeSourceStateMessageEmitted" : 0,
    "maxSecondsBetweenStateMessageEmittedandCommitted" : 0,
    "meanSecondsBetweenStateMessageEmittedandCommitted" : 0,
    "recordsEmitted" : 58646,
    "recordsCommitted" : 58646,
    "replicationEndTime" : 1683832503506,
    "replicationStartTime" : 1683832430689,
    "sourceReadEndTime" : 1683832495491,
    "sourceReadStartTime" : 1683832430723,
    "sourceStateMessagesEmitted" : 0
  },

... and then Normalization starts:

2023-05-11 19:15:03 INFO i.a.w.t.TemporalAttemptExecution(get):141 - Executing worker wrapper. Airbyte version: 0.44.4
2023-05-11 19:15:03 INFO i.a.a.c.AirbyteApiClient(retryWithJitter):179 - Attempt 0 to save workflow id for cancellation
2023-05-11 19:15:03 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-05-11 19:15:03 INFO i.a.w.n.DefaultNormalizationRunner(runProcess):129 - Running with normalization version: airbyte/normalization-redshift:0.4.0
2023-05-11 19:15:03 INFO i.a.c.i.LineGobbler(voidCall):149 - ----- START DEFAULT NORMALIZATION -----
2023-05-11 19:15:03 INFO i.a.c.i.LineGobbler(voidCall):149 - 
2023-05-11 19:15:03 INFO i.a.c.i.LineGobbler(voidCall):149 - Checking if airbyte/normalization-redshift:0.4.0 exists...
2023-05-11 19:15:03 INFO i.a.c.i.LineGobbler(voidCall):149 - airbyte/normalization-redshift:0.4.0 was found locally.
2023-05-11 19:15:03 INFO i.a.w.p.DockerProcessFactory(create):136 - Creating docker container = normalization-redshift-normalize-1031-0-cluhl with resources io.airbyte.config.ResourceRequirements@56a22bb3[cpuRequest=,cpuLimit=,memoryRequest=,memoryLimit=,additionalProperties={}] and allowedHosts null
2023-05-11 19:15:03 INFO i.a.w.p.DockerProcessFactory(create):188 - Preparing command: docker run --rm --init -i -w /data/1031/0/normalize --log-driver none --name normalization-redshift-normalize-1031-0-cluhl --network host -v airbyte_workspace:/data -v /tmp/airbyte_local:/local -e DEPLOYMENT_MODE=OSS -e WORKER_ENVIRONMENT=DOCKER -e AIRBYTE_ROLE= -e AIRBYTE_VERSION=0.44.4 airbyte/normalization-redshift:0.4.0 run --integration-type redshift --config destination_config.json --catalog destination_catalog.json
2023-05-11 19:15:03 normalization > Running: transform-config --config destination_config.json --integration-type redshift --out /data/1031/0/normalize
2023-05-11 19:15:04 normalization > Namespace(config='destination_config.json', integration_type=<DestinationType.REDSHIFT: 'redshift'>, out='/data/1031/0/normalize')
2023-05-11 19:15:04 normalization > transform_redshift
2023-05-11 19:15:04 normalization > Running: transform-catalog --integration-type redshift --profile-config-dir /data/1031/0/normalize --catalog destination_catalog.json --out /data/1031/0/normalize/models/generated/ --json-column _airbyte_data
2023-05-11 19:15:05 normalization > Processing destination_catalog.json...
2023-05-11 19:15:05 normalization >   Generating airbyte_ctes/source/table_appended_ab1.sql from table_appended
2023-05-11 19:15:11 normalization > Partial parse save file not found. Starting full parse.
2023-05-11 19:15:13 normalization > Found 36 models, 0 tests, 0 snapshots, 0 analyses, 627 macros, 0 operations, 0 seed files, 9 sources, 0 exposures, 0 metrics
2023-05-11 19:15:37 normalization > Encountered an error:
Database Error
  connection to server at "default.0.us-east-1.redshift-serverless.amazonaws.com" (172.x.x.x), port 5439 failed: No route to host
  	Is the server running on that host and accepting TCP/IP connections?
  connection to server at "default.0.us-east-1.redshift-serverless.amazonaws.com" (172.x.x.x), port 5439 failed: timeout expired
  connection to server at "default.0.us-east-1.redshift-serverless.amazonaws.com" (172.x.x.x), port 5439 failed: timeout expired

Contribute

• Yes, I want to contribute

[evantahler]

We will not be working on this issue. In the near future, normalization (as a concept and container) will be removed from Airbyte. Learn more here #26028