Snowflake to Elasticsearch

[Shalinicts (Shalini)]

Hi Team ,

We are trying to pull data from Snowflake database to Elasticsaerch via Logstash JDBC plugin

Input Config:

input {
  jdbc {
    jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/snowflake-jdbc-3.9.2.jar"
    jdbc_driver_class => "com.snowflake.client.jdbc.SnowflakeDriver"
    jdbc_connection_string => "jdbc:snowflake://xxx.snowflakecomputing.com/
	?db=PROD_abc&warehouse=PRODwH"
    jdbc_user => "username"
    jdbc_password => "*******"
    jdbc_paging_enabled => true
    jdbc_page_size => 100000
    schedule => "* * * * *"
    statement => "SELECT * FROM tablename LIMIT 10;"
    last_run_metadata_path => "/data/lib/logstash/snowflake/production/logstash_auditLog_snowflake_access_history.yml"
    jdbc_fetch_size => 1000
  }
}

But we get error like below :

[2023-11-20T08:58:04,520][ERROR][logstash.inputs.jdbc     ]
[snowflake_Pipeline][031fd8304bf1982583b766517b52ba1718eee5c7780a8e76345ef06ba3ae38bc]
 Unable to connect to database. Tried 1 times {:message=>"Java::JavaLang::ExceptionInInitializerError: ", 
:exception=>Sequel::DatabaseConnectionError, :cause=>java.lang.ExceptionInInitializerError,

Could you please advise on what can e checked here.

Thanks,
Shalini.

[Rios]

I would check:

• are params OK
• is port opened for the connection
• does jdbc connections work outside LS

[Shalinicts (Shalini)]

Hi Rios ,

Please find inline comments

• are params OK - Snowflake team says its correct
• is port opened for the connection - Any specific ports to be opened ?
• does jdbc connections work outside LS - How to check that

Thanks,
Shalini.

[Rios]

Any specific ports to be opened?

• TCP 443 should be opened, if is working from the same host, then it's OK
  How to check that?
• curl
  curl -v -k [https://xxx.snowflakecomputing.com]

And more:
Does "logstash" have rights on "/usr/share/logstash/logstash-core/lib/jars/snowflake-jdbc-3.9.2.jar" ? There is always workaround to put snowflake-jdbc-3.9.2.jar in /etc/logstash/

[Shalinicts (Shalini)]

Hi Rio ,

When I did : curl as below this is the response

curl -v -k xxx-xxx.snowflakecomputing.com
*   Trying 3.124.148.227:80...
* TCP_NODELAY set
* Connected to xxx-xxx.snowflakecomputing.com (3.124.148.227) port 80 (#0)
> GET / HTTP/1.1
> Host: xxx-xxx.snowflakecomputing.com
> User-Agent: curl/7.65.0
> Accept: */*
>
* Recv failure: Connection reset by peer
* Closing connection 0
curl: (56) Recv failure: Connection reset by peer

Permissions for snowflake jar is :

-rwxr-xr-x. 1 root root 30625824 Nov 9 07:40 snowflake-jdbc-3.9.2.jar

[Shalinicts (Shalini)]

Hi Rio ,

Permissions for snowflake jar is changed to Logstash from root.

Any suggestions ?

[Rios]

@Shalinicts

curl -v -k xxx-xxx.snowflakecomputing.com

You should use port 443 not default
curl -v -k https://xxx-xxx.snowflakecomputing.com

Also you might try similar

jdbc_connection_string => "jdbc:snowflake://xxx.snowflakecomputing.com:443/
	?db=PROD_abc&warehouse=PRODwH"

DatabaseConnectionError in general means there is no the network connection.
Can you check on snowflake side and server and firewall, does your jdbc request is comming?

[Shalinicts (Shalini)]

Hi Rio ,

curl -v -k https://xxx.snowflakecomputing.com
*   Trying x.x.x.x:443...
* TCP_NODELAY set
* Connected to xxx-xxx.snowflakecomputing.com (x.x.x.x) port 443 (#0)
* Initializing NSS with certpath: sql:/etc/pki/nssdb
*   CAfile: none
  CApath: none
* loaded libnssckbi.so
* NSS error -5961 (PR_CONNECT_RESET_ERROR)
* TCP connection reset by peer
* Closing connection 0
curl: (35) TCP connection reset by peer

--------------------------------------------------------------------

telnet xxx.snowflakecomputing.com 443
Trying x.x.x.x...
Connected to xxx.snowflakecomputing.com.
Escape character is '^]'.

This is the results for curl and Telnet . Is this means it is connected?

[Rios]

I would say on TCP level it is, the port has been opened.
Maybe you have some restrictions on snowflake side like a network policy. Check with command:
SHOW NETWORK POLICIES;