1

I tried using HikariCP connection pool to connect snowflake using JDBC. I use Spring Boot configuration XML file to create a bean. I have the private key for my authentication. So I tried the following,

Properties File:

snowflake.url=jdbc:snowflake://{account_name}.snowflakecomputing.com/?warehouse=${snowflake.warehouse}&db=${snowflake.database}&schema=${snowflake.schema}&role=${snowflake.role}

Spring Configuration XML:

<bean id="dbUtil" class="utilities.DbUtils">
    <constructor-arg>
        <bean id="snowflakeDataSource" class="com.zaxxer.hikari.HikariDataSource">
            <property name="driverClassName" value="net.snowflake.client.jdbc.SnowflakeDriver"/>
            <property name="jdbcUrl" value="${snowflake.url}"/>
            <property name="username" value="${snowflake.user}"/>
            <property name="dataSourceProperties">
                <props>
                    <prop key="privateKeyFile">${snowflake.privateKeyPath}</prop>
                    <prop key="privateKeyPassphrase">${snowflake.privateKeyPassphrase}</prop>
                </props>
            </property>
        </bean>
    </constructor-arg>
</bean>

When trying to execute the query with commonsdbUtils library with the above configuration it throws the following error:

HikariPool-1 - Exception during pool initialization.

net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Missing password.
at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:117)
at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:85)
at net.snowflake.client.jdbc.SnowflakeConnectionV1.initConnectionWithImpl(SnowflakeConnectionV1.java:116)
at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:96)
at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:180)
at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:121)
at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:359)
at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:201)
at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:470)
at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561)
at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:100)
at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)
at org.apache.commons.dbutils.AbstractQueryRunner.prepareConnection(AbstractQueryRunner.java:319)
at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:327)
andrewJames
  • 19,570
  • 8
  • 19
  • 51
Saravanan
  • 23
  • 1
  • 5
  • It seems for some reason the key is interpreted as a String rather than key, see here: https://github.com/snowflakedb/snowflake-jdbc/issues/1053. There's also a workaround for Hikari in linked thread (provide key+passphrase as part of URL like so `&private_key_file=/PATH_TO/key.p8&private_key_file_pwd=YOUR_PRIVATE_KEY_PASSPHRASE`) – pafau k. Jun 04 '23 at 18:33
  • @pafau k. tried adding the private_key_file and private_key_file_pwd to the jdbcUrl property and it now throws an error, `Exception thrown while parsing Snowflake connect string. Illegal character in url. java.lang.RuntimeException: Driver net.snowflake.client.jdbc.SnowflakeDriver claims to not accept jdbcUrl, jdbc:snowflake://{account_name}.snowflakecomputing.com/?user={user}&warehouse={warehouse}&db=PROD&schema={schema}&role={role}&private_key_file=key.p8&private_key_file_pwd={password_string}` – Saravanan Jun 04 '23 at 19:15

0 Answers0