Question

How to create an extract in csv format querying data from oracle DB table on StreamSets On-Prem?

  • 6 December 2022
  • 3 replies
  • 61 views
R
Rank

I need to create an extract in csv format from a table in Oracle db on Streamsets on Prem and then push this extract csv to load data on S3.

I am new to Streamsets. kindly help.

3 replies

Bikram
Rank
Userlevel 5
Badge +1

@Rachit_2022 

Hi Rachit

Please find attached the sample pipeline to connect on premise data base and store data to S3 bucket in CSV format .

 

I hope you have the connectivity is ready ,to connect on-premise DB and fetch data from it and also configure S3 bucket for storing data in CSV format.

 

NB : I just provided dummy config details for DB and s3 connectivity in the pipeline but you can use your original connectivity to make it work.

 

Kindly let me know if you are looking for this case or do you have any other case to handle in streamsets , so i can help you on it.

 

Thanks & Regards

Bikram_

 

1 Attachment
R
Rank

Hi Bikram,

Thanks a lot for the advise and sharing the test pipeline. Helped me a lot.
Even after configuring the pipeline as per the specifications, while running it i am getting below error:

When Database Source is SQL Server:
“JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: The TCP/IP connection to the host lonms12905.fm.rbsgrp.net, port 15010 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".: hikariConfigBean.connectionString”.

 

When database source is Oracle:

“JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: IO Error: The Network Adapter could not establish the connection: hikariConf.connectionString”

Could you please suggest what might be the issue and any possible resolution to get it running.

 

Thanks in advance.

Bikram
Rank
Userlevel 5
Badge +1

@Rachit_2022 

 

The above error shows that some issue in DB connnectivity from streamsets. 

There might be an issues in firewall opening for the port  and make sure your jdbc url and port has been configured correctly.

The jdbc url should looks like below as per your DB.

 

can you please try to ping your host with port from the command prompt and check if its connecting or not. If its throwing error then the issue is in the host and port.

 

 

  • MySQL - jdbc:mysql://<host>:<port>/<database_name>
  • Oracle - jdbc:oracle:<driver_type>:@<host>:<port>:<service_name>
  • PostgreSQL - jdbc:postgresql://<host>:<port>/<database_name>
  • SQL Server - jdbc:sqlserver://<host>:<port>;databaseName=<database_name>

 

 

Reply


Terms & Conditions