配置 JDBC 驱动程序

本主题介绍如何配置 JDBC 驱动程序，包括如何使用驱动程序连接到 Snowflake。

Note

The connection parameters are now documented in the JDBC Driver connection parameter reference.

JDBC 驱动程序类

Use net.snowflake.client.api.driver.SnowflakeDriver as the driver class in your JDBC application.

Note

  • Don’t reference any other Snowflake classes or methods in your application code because they are subject to change in the future to implement improvements and fixes.
  • The previous driver class, net.snowflake.client.api.driver.SnowflakeDriver, is still supported but is deprecated (meaning it will be removed in a future release). Any code that references the previous class name will continue to work, but you should update the code to reference the new class name because the change has been implemented.

JDBC 驱动程序连接字符串

Important

Beginning with Snowflake version 8.24, network administrators have the option to require multi-factor authentication (MFA) for all connections to Snowflake. If your administrator decides to enable this feature, you must configure your client or driver to use MFA when connecting to Snowflake. For more information, see the following resources:

使用 JDBC 驱动程序连接到 Snowflake 需要使用下述语法的连接字符串。

You can generate the basic connection string in Snowsight. For information, see Configuring a client, driver, library, or third-party application to connect to Snowflake.

Note

You cannot set the SEARCH_PATH parameter within a JDBC client connection string. You must establish a session before setting a search path.

语法

jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/?<connection_params>

连接参数

Note

For documentation on individual connection parameters, see the JDBC Driver connection parameter reference.

<account_identifier>

Specifies the account identifier for your Snowflake account. For details, see Configuring a client, driver, library, or third-party application to connect to Snowflake. For examples of the account identifier used in a JDBC connection string, see 示例.

<connection_params>

Specifies a series of one or more JDBC connection parameters and session parameters, in the form of <param>=<value>, with each parameter separated by the ampersand character (&), and no spaces anywhere in the connection string.

If you need to set parameter values that use spaces, ampersands (&), equals signs (=), or other special characters, you should URL-encode the special characters. For example, if you need to specify a value that contains a space, ampersand, and equals sign in the query_tag session parameter:

String connectionURL = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?query_tag='folder=folder1 folder2&'

encode the space as %20, the ampersand as %26, and the equals sign as %3D:

String connectionURL = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?query_tag='folder%3Dfolder1%20folder2%26'

As an alternative, rather than specifying these parameters in the connection string, you can set these parameters in a Properties object that you pass to the DriverManager.getConnectionIO method.

Properties props = new Properties();
props.put("parameter1", parameter1Value);
props.put("parameter2", parameter2Value);
Connection con = DriverManager.getConnection("jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/", props);

Note

For documentation on individual connection parameters, see the JDBC Driver connection parameter reference.

其他参数

连接字符串中可以包含任何会话参数。例如：

BROWSER_RESPONSE_TIMEOUT=<Integer>

指定等待来自外部浏览器的成功身份验证的超时时间（以秒为单位）。

Default is 120.

CLIENT_OUT_OF_BAND_TELEMETRY_ENABLED=<Boolean>

指定是否启用带外遥测。

Default is true.

CLIENT_SESSION_KEEP_ALIVE=<Boolean>

Specifies whether to keep the current session active after a period of inactivity, or to force the user to login again. If the value is true, Snowflake keeps the session active indefinitely, even if there is no activity from the user. If the value is false, the user must log in again after four hours of inactivity.

Default is false.

CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=<Integer>

指定客户端尝试更新会话令牌的间隔秒数 (900-3600)。

Default is 3600.

net.snowflake.jdbc.commons_logging_wrapper

指定如何处理来自公共日志记录的日志。可能的值：

  • ALL: All logs from common logging are passed to SFLogger (java.util.logging or SLF4J is used internally).
  • Default: All logs from commons logging are forwarded to java.util.logging, and no logs are forwarded to the SLF4J logger.
  • OFF: No logs from commons logging are forwarded. You can use this value if you need to replace commons logging with the SLF4J bridge when using a thin JAR file.
JDBC_QUERY_RESULT_FORMAT=JSON

Specifies JSON as the result format to use while fetching or processing the results of a query sent to Snowflake.

Default is Arrow.

For descriptions of all the session parameters, see Parameters.

示例

The following is an example of the connection string that uses the account name as an identifier for the account myaccount in the organization myorganization.

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?user=peter&warehouse=mywh&db=mydb&schema=public

The following is an example of a connection string that uses the account locator xy12345 as the account identifier:

jdbc:snowflake://xy12345.snowflakecomputing.cn/?user=peter&warehouse=mywh&db=mydb&schema=public

Note that this example uses an account in the AWS US West (Oregon) region. If the account is in a different region or if the account uses a different cloud provider, you need to specify additional segments after the account locator.

Connecting using the connections.toml file

The JDBC driver lets you add connection definitions to a connections.toml configuration file. A connection definition refers to a collection of connection-related parameters. The driver currently supports TOML version 1.0.0.

The connection string prefix: jdbc:snowflake:auto tells the driver to look for the connection configuration within the predefined (default) files. The JDBC driver looks for the connections.toml file in the following locations, in order:

  • If a ~/.snowflake directory exists on your machine, Snowflake CLI uses the ~/.snowflake/connections.toml file.
  • Location specified in the SNOWFLAKE_HOME environment variable.
  • Otherwise, Snowflake CLI uses the connections.toml file in the one of the following locations, based on your operating system:
    • Linux: ~/.config/snowflake/connections.toml, but you can update it with XDG vars
    • Windows: %USERPROFILE%\AppData\Local\snowflake\connections.toml
    • Mac: ~/Library/Application Support/snowflake/connections.toml

You can generate the basic settings for the TOML configuration file in Snowsight. For information, see Configuring a client, driver, library, or third-party application to connect to Snowflake.

If you want to switch between multiple existing connections, you can configure them in the connections.toml file. The default key is default, but you change the name of the default connection by setting the SNOWFLAKE_DEFAULT_CONNECTION_NAME shell environment variable.

The following sample connections.toml files defines three connections:

[default]
account = 'my_organization-my_account'
user = 'test_user'
warehouse = 'testw'
database = 'test_db'
schema = 'test_nodejs'
protocol = 'https'
port = '443'
authenticator = 'oauth'
token_file_path = '/Users/test/.snowflake/token'

[production]
account = 'my_organization-my_account'
user = 'prod_user'
warehouse = 'prodw'
database = 'prod_db'
schema = 'prod_nodejs'
protocol = 'https'
port = '443'
authenticator = 'oauth'
token_file_path = '/Users/test/.snowflake/token'


[aws-oauth-file]
account = 'my_organization-my_account'
user = 'test_user'
warehouse = 'testw'
database = 'test_db'
schema = 'test_nodejs'
protocol = 'https'
port = '443'
authenticator = 'oauth'
token_file_path = '/Users/test/.snowflake/token'

Specifying a connection to use in the auto connection prefix

You can specify which connection configuration to use by appending the connection name to the auto prefix in the connection string. Continuing the previous example, to connect with aws-oauth-file, use the following connection string:

jdbc:snowflake:auto?connectionName=aws-oauth-file

This connection string tells the JDBC driver to look for the aws-oauth-file connection definition in the connections.toml file.

The driver determines which connection to use in the following order:

  1. Connection name specified in the connection string using the jdbc:snowflake:auto?connectionName=<connection_name_in_toml_file> syntax
  2. Connection name specified in the SNOWFLAKE_DEFAULT_CONNECTION_NAME shell environment variable
  3. The default connection name, default

If the connection name specified in the connection string does not exist in the connections.toml file, the driver does the following:

  • Logs a message indicating the missing name and the file path checked.

  • Throws an exception containing the name of the missing connection, in the following format: The Connection <connection name> not found in connections.toml file.

  • Terminates the connection attempt.

使用单点登录 (SSO) 进行身份验证

If you have configured Snowflake to use single sign-on (SSO), you can configure your client application to use SSO for authentication. See Using SSO with client applications that connect to Snowflake for details.

使用多重身份验证

Snowflake 支持缓存 MFA 令牌，包括将 MFA 令牌缓存与 SSO 相结合。

For more information, see Using MFA token caching to minimize the number of prompts during authentication — *optional*.

使用密钥对身份验证和密钥轮换

Snowflake JDBC 驱动程序支持密钥对身份验证和密钥轮换。此身份验证方法需要 2048 位（最低）RSA 密钥对。

To start, complete the initial configuration for key pair authentication as shown in Key-pair authentication and key-pair rotation.

接下来，从以下三个选项中选择一个来配置 JDBC 连接属性或 JDBC 连接字符串。

  1. 通过连接属性中的 privateKey 属性指定私钥。
  2. 在连接属性中将该文件的私钥文件名和密码指定为单独的属性。
  3. 将该文件的私钥文件名和密码指定为连接字符串的一部分。

接下来的三部分将更详细地介绍这些选项。

privateKey property in connection properties

This section provides an example of setting the privateKey property to a private key in a file.

This example uses the Bouncy Castle Crypto APIs (https://www.bouncycastle.org/java.html). In order to compile and run this example, you must include the following JAR files in your classpath:

  • the provider JAR file (bcprov-jdkversions.jar)
  • the PKIX / CMS / EAC / PKCS / OCSP / TSP / OPENSSL JAR file (bcpkix-jdkversions.jar)

where versions specifies the versions of the JDK that the JAR file supports.

要使用此示例，请执行以下操作：

  1. Copy the sample code below, and replace the following placeholder values:

    PlaceholderDescription
    path/rsa_key.p8Set this to the path and name of the private key file that you generated earlier.
    private_key_passphraseIf you generated an encrypted key, implement the getPrivateKeyPassphrase() method to return the passphrase for decrypting that key.
    account_identifierSet this to your account identifier.
    userSet this to your Snowflake login name.
    database_nameSet this to the name of the database that you want to use.
    schema_nameSet this to the name of the schema that you want to use.
    warehouse_nameSet this to the name of the warehouse that you want to use.
    roleSet this to the name of the role that you want to use.

  2. 编译并运行示例代码。在类路径中包含 Bouncy Castle JAR 文件。

例如，在 Linux 和 macOS 上：

javac -cp bcprov-jdk<versions>.jar:bcpkix-jdk<versions>.jar TestJdbc.java

java -cp .:snowflake-jdbc-<ver>.jar:bcprov-jdk<versions>.jar:bcpkix-jdk<versions>.jar TestJdbc.java

在 Windows 上：

javac -cp bcprov-jdk<versions>.jar;bcpkix-jdk<versions>.jar TestJdbc.java

java -cp .;snowflake-jdbc-<ver>.jar;bcprov-jdk<versions>.jar;bcpkix-jdk<versions>.jar TestJdbc.java

示例代码

import org.bouncycastle.asn1.pkcs.PrivateKeyInfo;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.openssl.PEMParser;
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter;
import org.bouncycastle.openssl.jcajce.JceOpenSSLPKCS8DecryptorProviderBuilder;
import org.bouncycastle.operator.InputDecryptorProvider;
import org.bouncycastle.operator.OperatorCreationException;
import org.bouncycastle.pkcs.PKCS8EncryptedPrivateKeyInfo;
import org.bouncycastle.pkcs.PKCSException;

import java.io.FileReader;
import java.io.IOException;
import java.nio.file.Paths;
import java.security.PrivateKey;
import java.security.Security;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Properties;

public class TestJdbc
{
  // Path to the private key file that you generated earlier.
  private static final String PRIVATE_KEY_FILE = "/<path>/rsa_key.p8";

  public static class PrivateKeyReader
  {

    // If you generated an encrypted private key, implement this method to return
    // the passphrase for decrypting your private key.
    private static String getPrivateKeyPassphrase() {
      return "<private_key_passphrase>";
    }

    public static PrivateKey get(String filename)
            throws Exception
    {
      PrivateKeyInfo privateKeyInfo = null;
      Security.addProvider(new BouncyCastleProvider());
      // Read an object from the private key file.
      PEMParser pemParser = new PEMParser(new FileReader(Paths.get(filename).toFile()));
      Object pemObject = pemParser.readObject();
      if (pemObject instanceof PKCS8EncryptedPrivateKeyInfo) {
        // Handle the case where the private key is encrypted.
        PKCS8EncryptedPrivateKeyInfo encryptedPrivateKeyInfo = (PKCS8EncryptedPrivateKeyInfo) pemObject;
        String passphrase = getPrivateKeyPassphrase();
        InputDecryptorProvider pkcs8Prov = new JceOpenSSLPKCS8DecryptorProviderBuilder().build(passphrase.toCharArray());
        privateKeyInfo = encryptedPrivateKeyInfo.decryptPrivateKeyInfo(pkcs8Prov);
      } else if (pemObject instanceof PrivateKeyInfo) {
        // Handle the case where the private key is unencrypted.
        privateKeyInfo = (PrivateKeyInfo) pemObject;
      }
      pemParser.close();
      JcaPEMKeyConverter converter = new JcaPEMKeyConverter().setProvider(BouncyCastleProvider.PROVIDER_NAME);
      return converter.getPrivateKey(privateKeyInfo);
    }
  }

  public static void main(String[] args)
      throws Exception
  {
    String url = "jdbc:snowflake://<account_identifier>.snowflakecomputing.cn";
    Properties prop = new Properties();
    prop.put("user", "<user>");
    prop.put("privateKey", PrivateKeyReader.get(PRIVATE_KEY_FILE));
    prop.put("db", "<database_name>");
    prop.put("schema", "<schema_name>");
    prop.put("warehouse", "<warehouse_name>");
    prop.put("role", "<role_name>");

    Connection conn = DriverManager.getConnection(url, prop);
    Statement stat = conn.createStatement();
    ResultSet res = stat.executeQuery("select 1");
    res.next();
    System.out.println(res.getString(1));
    conn.close();
  }
}

Note

在所有操作系统（包括 Windows）上，使用正斜杠作为文件路径分隔符。JDBC 驱动程序会将正斜杠替换为适合该平台的路径分隔符。

私钥文件名和密码作为连接属性

您可以将私钥文件名和密码指定为单独的连接属性，例如：

Properties props = new Properties();
props.put("private_key_file", "/tmp/rsa_key.p8");
props.put("private_key_file_pwd", "dummyPassword");
Connection connection = DriverManager.getConnection("jdbc:snowflake://myorganization-myaccount.snowflake.com", props);

If you specify the private_key_file and private_key_file_pwd parameters, do not specify the privateKey parameter in the connection properties.

Note

在所有操作系统（包括 Windows）上，使用正斜杠作为文件路径分隔符。JDBC 驱动程序会将正斜杠替换为适合该平台的路径分隔符。

连接字符串中的私钥文件名和密码

您可以在连接字符串中指定私钥文件名和密码，如下所示：

Connection connection = DriverManager.getConnection(
    "jdbc:snowflake://myorganization-myaccount.snowflake.com/?private_key_file=/tmp/rsa_key.p8&private_key_file_pwd=dummyPassword",
    props);

Note

在所有操作系统（包括 Windows）上，使用正斜杠作为文件路径分隔符。JDBC 驱动程序会将正斜杠替换为适合该平台的路径分隔符。

If you specify the private key and password in the connection string, then do not specify the parameters private_key_file, private_key_file_pwd, or privateKey in the connection properties.

密钥解密错误

如果使用 OpenSSL V3 生成的加密密钥，则可能会收到类似以下内容的错误：

java.security.NoSuchAlgorithmException: 1.2.840.113549.1.5.13 SecretKeyFactory not available

java.security.InvalidKeyException: IOException : DER input, Integer tag error

在这种情况下，您可以使用 Bouncy Castle 通过指定以下 JVM 实参来解密密钥：

-Dnet.snowflake.jdbc.useBundledBouncyCastleForPrivateKeyDecryption=true

The default value is true, which means that the bundled Bouncy Castle library is used to decrypt the key.

使用 OAuth 2.0 授权码流程

OAuth 2.0 授权码流程是一种安全的方法，客户端应用程序可以使用该流程，在不泄露用户凭据的情况下代表用户从授权服务器获取访问令牌。

要启用 OAuth 2.0 授权码流程，请执行以下操作：

  1. Set the authenticator connection parameter to oauth_authorization_code.
  2. 设置以下 OAuth 连接参数：
    • oauthClientId: Value of client id provided by the identity provider for Snowflake integration (Snowflake security integration metadata). Default: LOCAL_APPLICATION if unset and the IDP is Snowflake.
    • oauthClientSecret: Value of the client secret provided by the identity provider for Snowflake integration (Snowflake security integration metadata). Default: LOCAL_APPLICATION if unset and the IDP is Snowflake.
    • oauthAuthorizationUrl: Identity provider endpoint supplying the authorization code to the driver. When Snowflake is used as an identity provider, this value is derived from the server or account parameters.
    • oauthTokenRequestUrl: Identity provider endpoint supplying the access tokens to the driver. When Snowflake is used as an identity provider, this value is derived from the server or account parameters.
    • oauthScope: Scope requested in the identity provider authorization request. By default, it is derived from the role. When multiple scopes are required, the value should be a space-separated list of multiple scopes.
    • oauthRedirectUri: URI to use for authorization code redirection (Snowflake security integration metadata). Default: http://127.0.0.1:{randomAvailablePort}.

使用 OAuth 2.0 客户端凭据流程

OAuth 2.0 客户端凭据流程为机器对机器 (M2M) 身份验证提供了一种安全的方式，例如连接后端服务的 Snowflake Connector for Python。与 OAuth 2.0 授权码流程不同，此方法不依赖于任何用户特定的数据。

要启用 OAuth 2.0 客户端凭据流程，请执行以下操作：

  1. Set the authenticator connection parameter to oauth_client_credentials.
  2. 设置以下 OAuth 连接参数：
    • oauthClientId: Value of client id provided by the identity provider for Snowflake integration (Snowflake security integration metadata).
    • oauthClientSecret: Value of the client secret provided by the identity provider for Snowflake integration (Snowflake security integration metadata)
    • oauthTokenRequestUrl: Identity provider endpoint supplying the access tokens to the driver.
    • oauthScope: Scope requested in the identity provider authorization request. By default, it is derived from the role. When multiple scopes are required, the value should be a space-separated list of multiple scopes.

使用编程访问令牌 (PAT) 进行身份验证

Programmatic access token (PAT) is a Snowflake-specific authentication method. The feature must be enabled for the account before usage (see the Prerequisites for more information). Authentication with PAT doesn’t involve any human interaction.

Authenticating with workload identity federation (WIF)

Workload identity federation provides a service-to-service authentication method for Snowflake. This method enables applications, services, or containers to authenticate with Snowflake by leveraging their cloud provider’s native identity system, such as AWS IAM, Microsoft Entra ID, or Google Cloud service accounts. This approach eliminates the need for managing long-lived credentials and simplifies credential acquisition compared to other methods like External OAuth. Snowflake connectors are designed to automatically obtain short-lived credentials from the platform’s identity provider.

To enable the workload identity federation authenticator, do the following:

  1. Set the authenticator connection parameter to WORKLOAD_IDENTITY.
  2. Set the workloadIdentityProvider connection parameter to AWS, AZURE, GCP, or OIDC, based on your platform.
  3. For OpenID Connect (OIDC), specify the token connection parameter.

使用 SnowCD 验证与 Snowflake 的网络连接

After configuring your driver, you can evaluate and troubleshoot your network connectivity to Snowflake using SnowCD.

可以在初始配置过程中使用 SnowCD，也可以根据需要随时使用，以评估与 Snowflake 的网络连接并进行故障排除。

使用代理服务器进行连接

您可以通过以下方式将代理服务器与 Snowflake JDBC Driver 一起使用：

Note

连接字符串中指定的代理设置优先于 JVM 系统属性。

Tip

Snowflake 的安全模型不允许传输层安全 (TLS) 代理（使用 HTTPS 证书）。代理服务器必须使用公开可用的证书颁发机构 (CA)，从而降低潜在的安全风险，例如通过受感染的代理发起的 MITM（中间人）攻击。

If you must use your TLS proxy, we strongly recommend that you update the server policy to pass through the Snowflake certificate such that no certificate is altered in the middle of communications.

As an alternative, you can set the nonProxyHosts parameter in the connection string or Properties object to bypass the proxy for specific communications. For example, Amazon S3 access can be bypassed by specifying nonProxyHosts=".amazonaws.com".

通过设置 Java 系统属性来指定代理服务器

要通过代理服务器进行连接，可以设置代理系统属性。您可以在代码中设置这些属性，也可以通过命令行上将其传递给客户端应用程序的 JVM（Java 虚拟机）。

To set the system properties in your code, call System.setProperty:

System.setProperty("http.useProxy", "true");
System.setProperty("http.proxyHost", "proxyHost Value");
System.setProperty("http.proxyPort", "proxyPort Value");
System.setProperty("http.proxyUser", "proxyUser Value");
System.setProperty("http.proxyPassword", "proxyPassword Value");
System.setProperty("https.proxyHost", "proxyHost HTTPS Value");
System.setProperty("https.proxyPort", "proxyPort HTTPS Value");
System.setProperty("https.proxyUser", "proxyUser HTTPS Value");
System.setProperty("https.proxyPassword", "proxyPassword HTTPS Value");
System.setProperty("http.proxyProtocol", "https");

To pass the system properties on the command line to your JVM, use the -D command-line option:

-Dhttp.useProxy=true
-Dhttps.proxyHost=<proxy_host>
-Dhttps.proxyPort=<proxy_port>
-Dhttps.proxyUser=<proxy_user>
-Dhttps.proxyPassword=<proxy_password>
-Dhttp.proxyHost=<proxy_host>
-Dhttp.proxyPort=<proxy_port>
-Dhttp.proxyUser=<proxy_user>
-Dhttp.proxyPassword=<proxy_password>
-Dhttp.proxyProtocol="https"

To bypass the proxy for one or more IP addresses or hosts, set the http.nonProxyHosts system property to the list of these hosts:

  • Use a pipe symbol (|) to separate the host names.
  • To specify hostnames that match a pattern, use an asterisk (*) as a wildcard character.

下面的示例演示了如何在命令行中设置此系统属性：

-Dhttp.nonProxyHosts="*.example.com%localhost%myorganization-myaccount.snowflakecomputing.cn|192.168.91.*"

在 JDBC 连接字符串中指定代理服务器

Note

与其他指定代理信息的方法相比，将代理信息指定为 URL 的一部分安全性较低。

在 JDBC 连接字符串中设置以下参数，即可使用代理服务器：

If your proxy server does not require authentication, you can omit the proxyUser and proxyPassword parameters.

If your proxy server connection requires authentication using a proxy username and proxy password, those credentials may be exposed as plain text by other applications when using the HTTP protocol. To avoid exposing these credentials, use the proxyProtocol parameter to specify the HTTPS protocol.

jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/?warehouse=<warehouse_name>&useProxy=true&proxyHost=<ip_address>&proxyPort=<port>&proxyUser=test&proxyPassword=test

例如：

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?warehouse=DemoWarehouse1&useProxy=true&proxyHost=172.31.89.76&proxyPort=8888&proxyUser=test&proxyPassword=test

连接字符串中指定的代理设置优先于 JVM 系统属性。

If the proxy JVM arguments are set and you do not want to proxy any of your connections, do not set useProxy=false, as it has no effect. Instead, use the following, which effectively bypasses the JVM proxy settings:

useProxy=true
proxyHost=127.0.0.1
proxyPort=8080
nonProxyHosts=*

绕过代理服务器

If you need to bypass the proxy server when connecting to one or more hosts, specify the list of hosts in the nonProxyHosts parameter:

&nonProxyHosts=<bypass_proxy_for_these_hosts>

Separate the hostnames with a URL-escaped pipe symbol (%7C). You can also use an asterisk (*) as a wildcard. For example:

&nonProxyHosts=*.example.com%7Clocalhost%7Cmyorganization-myaccount.snowflakecomputing.cn%7C192.168.91.*

指定用于连接到代理服务器的协议

  • To specify the protocol used to connect to the proxy server, use the proxyProtocol parameter. The default value is http, but https is also valid.

例如：

&proxyProtocol=https

OCSP

当驱动程序发起连接时，Snowflake 会发送一个证书，确认其要连接的是 Snowflake 而不是冒充 Snowflake 的主机。驱动程序将该证书发送到 OCSP（在线证书状态协议）服务器，验证该证书是否被撤销。

If the driver cannot reach the OCSP server to verify the certificate, the driver can “fail open” or “fail closed”.

选择故障打开或故障关闭模式

3.8.0 之前的 JDBC 驱动程序版本默认为故障关闭。3.8.0 及以后的版本默认为故障打开模式。您可以通过以下任一方式替换默认行为：

  • Set the connection property ocspFailOpen to true or false. For example:

    Properties connection_properties = new Properties();
connection_properties.put("ocspFailOpen", "false");
...
connection = DriverManager.getConnection(connectionString, connection_properties);

  • Set the system property net.snowflake.jdbc.ocspFailOpen to true or false. For example:

    Properties p = new Properties(System.getProperties());
p.put("net.snowflake.jdbc.ocspFailOpen", "false");
System.setProperties(p);

验证 OCSP 连接器或驱动程序版本

For more information about the driver or connector version, configuration, and OCSP behavior, see OCSP Configuration.

OCSP 响应缓存服务器

Note

Snowflake JDBC 驱动程序 3.6.0 及更高版本目前支持 OCSP 响应缓存服务器。

Snowflake clients initiate every connection to a Snowflake service endpoint with a “handshake” that establishes a secure connection before actually transferring data. As part of the handshake, a client authenticates the TLS certificate for the service endpoint. The revocation status of the certificate is checked by sending a client certificate request to one of the OCSP (Online Certificate Status Protocol) servers for the CA (certificate authority).

A connection failure occurs when the response from the OCSP server is delayed beyond a reasonable time. The following caches persist the revocation status, helping alleviate these issues:

  • Memory cache, which persists for the life of the process.

  • File cache, which persists until the cache directory (e.g. ~/.cache/snowflake or ~/.snowsql/ocsp_response_cache) is purged.

  • Snowflake OCSP response cache server, which fetches OCSP responses from the CA’s OCSP servers hourly and stores them for 24 hours. Clients can then request the validation status of a given Snowflake certificate from this server cache.

    Important

    If your server policy denies access to most or all external IP addresses and web sites, you must allowlist the cache server address to allow normal service operation. The cache server hostname is ocsp*.snowflakecomputing.cn:80.

    If you need to disable the cache server for any reason, set the SF_OCSP_RESPONSE_CACHE_SERVER_ENABLED environment variable to false. Note that the value is case-sensitive and must be in lowercase.

If none of the cache layers contain the OCSP response, the client then attempts to fetch the validation status directly from the OCSP server for the CA.

文件缓存

为了提高可用性，驱动程序使用文件缓存进行身份验证和 OCSP 响应。默认情况下，这些文件存储在以下目录中：

Linux:

~/.cache/snowflake

macOS:

~/Library/Caches/Snowflake

Windows:

%USERPROFILE%AppDataLocalSnowflakeCaches

如果 JDBC 应用程序用户在本地操作系统中没有用户配置文件，则驱动程序会尝试将缓存文件存储在临时目录中。您可以使用以下环境变量将驱动程序配置为将缓存文件写入其他目录：

SF_TEMPORARY_CREDENTIAL_CACHE_DIR=string

Specifies the location of the temporary credential cache file in a local directory. This can also be configured with the JVM option -Dnet.snowflake.jdbc.temporaryCredentialCacheDir=string on launch.

SF_OCSP_RESPONSE_CACHE_DIR=string

Specifies the location of the OCSP response cache file in a local directory. This can also be configured with the JVM option -Dnet.snowflake.jdbc.ocspResponseCacheDir=string on launch.

For more information, see OCSP Response Cache Server (in this topic).

Note that the JVM options should be set on launch, and not programmatically (via System.setProperty()). If both environment variable and JVM options are provided, the JVM option will be used.

配置 JDBC 日志记录

从版本 3.0.4 开始，JDBC 驱动程序支持以下日志记录框架：

Java core logging facilities (Java.util.logging)

By default, the java.util.logging uses ConsoleHandler to write to the standard error stream. You can set the Boolean JAVA_LOGGING_CONSOLE_STD_OUT java or connection property to true, which writes all logs to the standard output stream. The default value is false.

If you enable JAVA_LOGGING_CONSOLE_STD_OUT, you can also set the JAVA_LOGGING_CONSOLE_STD_OUT_THRESHOLD java or connection property to set the maximum log level the driver should write to standard output. Any log messages with a higher level than specified are sent to standard error. Possible values for this property include:

  • OFF
  • SEVERE
  • WARNING
  • INFO
  • CONFIG
  • FINE
  • FINER
  • FINEST
  • ALL

要明确选择此记录器，请为 JVM 指定以下选项：

-Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.JDK14Logger

然后，您可以使用日志记录器的应用程序编程接口 (API)，自定义日志记录配置。

For example, create a file named logging.properties that includes the following contents:

###########################################################
#   Default Logging Configuration File
#
# You can use a different file by specifying a filename
# with the java.util.logging.config.file system property.
# For example java -Djava.util.logging.config.file=myfile
############################################################

############################################################
#   Global properties
############################################################

# "handlers" specifies a comma-separated list of log Handler
# classes.  These handlers will be installed during VM startup.
# Note that these classes must be on the system classpath.
# ConsoleHandler and FileHandler are configured here such that
# the logs are dumped into both a standard error and a file.
handlers = java.util.logging.ConsoleHandler, java.util.logging.FileHandler

# Default global logging level.
# This specifies which kinds of events are logged across
# all loggers.  For any given facility this global level
# can be overriden by a facility specific level.
# Note that the ConsoleHandler also has a separate level
# setting to limit messages printed to the console.
.level = INFO

############################################################
# Handler specific properties.
# Describes specific configuration information for Handlers.
############################################################

# default file output is in the tmp dir
java.util.logging.FileHandler.pattern = /tmp/snowflake_jdbc%u.log
java.util.logging.FileHandler.limit = 5000000000000000
java.util.logging.FileHandler.count = 10
java.util.logging.FileHandler.level = INFO
java.util.logging.FileHandler.formatter = net.snowflake.client.log.SFFormatter

# Limit the messages that are printed on the console to INFO and above.
java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = net.snowflake.client.log.SFFormatter

# Example to customize the SimpleFormatter output format
# to print one-line log message like this:
#     <level>: <log message> [<date/time>]
#
# java.util.logging.SimpleFormatter.format=%4$s: %5$s [%1$tc]%n

############################################################
# Facility specific properties.
# Provides extra control for each logger.
############################################################

# Snowflake JDBC logging level.
net.snowflake.level = INFO
net.snowflake.handler = java.util.logging.FileHandler

在命令行中指定 JVM 参数：

java -jar application.jar -Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.JDK14Logger -Djava.util.logging.config.file=logging.properties

Where application.jar references the application code for the JDBC driver. The log files are located in /tmp/snowflake_jdbc*.

Simple logging facade for Java (org.slf4j)

要选择此记录器，请设置 JVM 选项：

-Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.SLF4JLogger.

You must add slf4j-api and its implementation (for example, logback) to the classpath.

通过公共日志记录桥接日志

Some of the libraries use Apache commons-logging for logging. Handling these logs is configured by the net.snowflake.jdbc.commons_logging_wrapper JVM option that was added in version 3.22.0. For details, see Other parameters.

日志记录配置文件

Alternatively, you can easily specify the log level (https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/log/SFLogLevel.java) and the directory in which to save log files in the sf_client_config.json configuration file.

Note

此日志记录配置文件功能仅支持以下日志级别：

  • DEBUG
  • ERROR
  • INFO
  • OFF
  • TRACE
  • WARNING

This configuration file uses JSON to define the log_level and log_path logging parameters, as follows:

{
  "common": {
    "log_level": "DEBUG",
    "log_path": "/home/user/logs"
  }
}

驱动程序按以下顺序查看配置细节：

  • client_config_file connection parameter, containing the full path to the user-defined logging configuration file. For example:

    client_config_file=/opt/snowflake/snowflake_jdbc/my_jdbc_config.json

  • SF_CLIENT_CONFIG_FILE environment variable, containing the full path to the user-defined logging configuration file.

    export SF_CLIENT_CONFIG_FILE=/home/myuser/my_jdbc_config.json

  • JDBC driver installation directory, where the file must be named sf_client_config.json.

  • User’s home directory, where the file must be named sf_client_config.json.

Note

  • If the configuration file is not found in any of the preceding locations, the driver uses the Java core logging facilities.
  • If a configuration file specified in either the client_config_file connection parameter or SF_CLIENT_CONFIG_FILE environment variable cannot be found or read, the driver throws an error message.

禁用 PUT 和 GET 命令

默认情况下，JDBC 驱动程序允许您执行 PUT 和 GET 命令。如果不想允许 PUT 和 GET 命令访问本地文件系统，可以通过以下方式禁用这些命令：

Snowflake JDBC 驱动程序中的 HTTP 标头自定义功能

To programmatically add custom HTTP headers to requests made by the Snowflake JDBC driver, implement the HttpHeadersCustomizer interface and register your implementation(s). This allows flexible, programmatic injection of dynamic or static headers.

主要注意事项：

  • The driver iterates registered customizers for applicable requests (Snowflake API, S3, private link OCSP). Then it calls applies(), then newHeaders() (respecting invokeOnce() for retries).
  • 自定义程序无法替换基本的由驱动程序设置的标头。这是由驱动程序强制执行的。
  • Keep applies() and newHeaders() efficient.

The following example shows how to implement net.snowflake.client.jdbc.HttpHeadersCustomizer.

public class MyDynamicCustomizer implements HttpHeadersCustomizer {
    public boolean applies(String method, String uri, Map<String, List> headers) {
        return true;
    }

    public Map<String, List<String>> newHeaders() {
        Map<String, List<String>> headers = new HashMap<>();
        headers.put("X-Dynamic-Token", Collections.singletonList("token-" + System.nanoTime()));
        return headers;
    }

    public boolean invokeOnce() {
        return false;
    }
}

以下示例展示了注册自定义程序的不同方法：

  • Via net.snowflake.client.jdbc.SnowflakeBasicDataSource:

    SnowflakeBasicDataSource ds = new SnowflakeBasicDataSource();
// ... set URL, user, password ...
List<HttpHeadersCustomizer> myCustomizers = new ArrayList<>();
myCustomizers.add(new MyDynamicHeaderCustomizer());
Properties props = new Properties();
props.put(HttpHeadersCustomizer.HTTP_HEADER_CUSTOMIZERS_PROPERTY_KEY, myCustomizers);
ds.setConnectionProperties(props);

  • Via java.sql.DriverManager:

    Properties props = new Properties();
// ... set user, password ...
List<HttpHeadersCustomizer> myCustomizers = new ArrayList<>();
myCustomizers.add(new MyDynamicHeaderCustomizer());
props.put(HttpHeadersCustomizer.HTTP_HEADER_CUSTOMIZERS_PROPERTY_KEY, myCustomizers);
Connection conn = DriverManager.getConnection(jdbcUrl, props);

故障排除提示

确保正确设置属性

The DriverManager.getConnection() method reads only the values of the Properties parameter that match specific, predefined names (“password”, “warehouse”, etc.). If you misspell a property name, or include extra properties, the driver ignores those properties without issuing an error or warning message. This can make it difficult to detect minor misspellings.

使用正确的连接字符串和账户值

If you can’t establish a connection, verify that you are specifying the account identifier correctly in the JDBC connection string. For more information about finding your account identifier, see Configuring a client, driver, library, or third-party application to connect to Snowflake.