CData ドライバーで Snowflake の TIMESTAMP 差異を管理する方法

データベースにおける TIMESTAMP 型は、日付と時刻を表すために使用されるデータ型です。 特定の時刻や日付を正確に記録することは一般的に重要ですが、データの日時に基づいて増分操作を管理したい場合は特に重要になります。

この記事では、Snowflake のタイムスタンプ型に焦点を当て、どのようなバリエーションのタイムスタンプが存在するか、 そして CData Snowflake ドライバーから特定の型をどのように使用できるかを説明します。

Snowflake の3つのタイムスタンプ型

Snowflake には3種類のタイムスタンプ型があり、 それぞれ異なる用途と特性を持っています。

TIMESTAMP_LTZ（ローカルタイムゾーン付きタイムスタンプ）

Snowflake 内部では UTC 時刻で保存されます。クエリ実行時にセッションのタイムゾーンに基づいて日付と時刻が取得されます。 グローバルアプリケーションにおいて、異なるタイムゾーンから挿入されたデータの一貫性を維持します。

TIMESTAMP_NTZ（タイムゾーンなしタイムスタンプ）

Snowflake 内部ではタイムゾーン情報を持たない壁掛け時計時刻として保存され、 最初に保存されたとおりに取得されます。タイムゾーンに依存しない日付と時刻を扱いたい場合に使用します。

TIMESTAMP_TZ（タイムゾーン付きタイムスタンプ）

Snowflake 内部では関連するタイムゾーンオフセットとともに UTC 時刻で保存され、 各レコード固有のタイムゾーンオフセット付きで取得されます。特定のタイムゾーンに関連する日時データを保存・表示する場合に使用します。

日時登録時の課題

Snowflake で INSERT や UPDATE を実行して日時データを登録または更新する場合、 セッションのタイムゾーンに基づいて日時変換が自動的に行われるため、意図しない結果になることがあります。 これを防ぐには、ALTER SESSION SET TIMEZONE 句を使用してセッションのタイムゾーンを設定するか、 タイムゾーンを考慮しない TIMESTAMP_NTZ カラムを使用します。

セッションに基づいてタイムゾーンを設定できる機能は、Snowflake インターフェース内で作業する場合に便利です。 一方、CData JDBC Driver for Snowflake を使用した操作は、JVM 実行環境のタイムゾーンに依存するため、 日時変換の仕組みがより複雑になります。そのため、ドライバー経由では期待どおりの日時を登録できないという問題がよく発生します。 この記事では、目的の日時データを登録し、登録した日時と同じ値を取得する方法を紹介します。

以下の例では、JVM 実行環境のデフォルトタイムゾーンは「Asia/Tokyo」（UTC+9:00）、 Snowflake のセッションタイムゾーンは「America/Los_Angeles」（UTC-5:00）とします。

TIMESTAMP 問題の例

上記の環境（JVM タイムゾーンが「Asia/Tokyo」、セッションタイムゾーンが「America/Los_Angeles」）で、 以下の Java コードを実行して「2023-6-15 21:00:00」を TIMESTAMP_NTZ カラム COL_TIMESTAMP_LTZ に登録してみましょう:

String query = "INSERT INTO TEST(COL_TIMESTAMP_NTZ) VALUES(?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setTimestamp(1, Timestamp.valueOf("2023-6-15 21:00:00"));
pstmt.executeUpdate();

上記のコードを実行した後、Snowflake で COL_TIMESTAMP_NTZ の値を取得すると「2023-06-15 07:00:00.000」が返されます。

これは以下のプロセスの結果と推測できます:

• ドライバーは、ローカルタイムゾーン「Asia/Tokyo」（UTC+9:00）の日時「2023-6-15 21:00:00」を UTC「2023-06-15 12:00:00」に変換して Snowflake に送信します。
• Snowflake は受信した値「2023-06-15 12:00:00」をセッションタイムゾーン「America/Los_Angeles」（UTC-5:00）に変換し、 「2023-06-15 07:00:00」を登録します。

ドライバーで COL_TIMESTAMP_NTZ の日時をリクエストすると、Snowflake は「2023-06-15 07:00:00」を返します。 これは Snowflake インターフェースから直接返される値と同じです。この結果、ドライバー経由で「2023-06-15 21:00:00」として登録したデータが、 同じドライバー経由で「2023-06-15 07:00:00」として取得されるという矛盾した体験になります。 この結果は Snowflake と JDBC ドライバーの仕様に基づいて正確ですが、プログラムやツールでドライバーを使用する場合は不便です。 しかし、以下で紹介するドライバーの設定によって、この不整合を回避する方法があります。

1. 日時を UTC として登録する

一部のデータ統合ツールは、すべての日時データを UTC として扱います。 そのようなツールでドライバーを使用する場合、入力値を UTC として登録および取得できると便利です。 送信した UTC 日時データを Snowflake でセッションタイムゾーンに変換せずに登録する方法の1つは、 CLIENT_TIMESTAMP_TYPE_MAPPING セッションパラメータを使用することです。

CLIENT_TIMESTAMP_TYPE_MAPPING セッションパラメータは、ステートメントにバインドされた java.sql.Timestamp データを 指定された Snowflake TIMESTAMP 型に変換します。このセッションパラメータをタイムゾーンを考慮しない「TIMESTAMP_NTZ」型に設定することで、 送信した日時をそのまま登録できます。CData JDBC Driver for Snowflake でセッションパラメータを設定するには、 以下のように接続プロパティに追加します:

Properties properties = new Properties();
// その他のプロパティ設定
properties.put("CLIENT_TIMESTAMP_TYPE_MAPPING", "TIMESTAMP_NTZ");
String connectStr = "jdbc:snowflake://test.us-east-1.snowflakecomputing.com";
Connection conn = DriverManager.getConnection(connectStr, properties);

TIMEZONE セッションパラメータを「UTC」に設定することでも同じ結果を得られます。

2. 日時をローカルタイムゾーンとして登録する

入力した日時を UTC やセッションタイムゾーンに変換せずにそのまま登録するには、 文字列としてバインドする方法または TIMEZONE セッションパラメータを指定する方法があります。

文字列としてバインドする

以下のようにステートメントにバインドする際に setString() メソッドで文字列を設定することで、 日時をそのまま登録できます。これは最もシンプルな方法ですが、バインドのデータ型を変更できないツールには適用できません。

pstmt.setString(2, "2023-6-15 21:00:00");

TIMEZONE セッションパラメータを指定する

「日時を UTC として登録する方法」で紹介した TIMEZONE セッションパラメータにローカルタイムゾーンを指定することで、 そのタイムゾーンの日時として登録できます。たとえば、ローカルタイムゾーンが Asia/Tokyo の環境で実行する場合は、 以下のように TIMEZONE セッションパラメータを「Asia/Tokyo」に設定します:

Properties properties = new Properties();
// その他のプロパティ設定
properties.put("TIMEZONE", "Asia/Tokyo");
String connectStr = "jdbc:snowflake://test.us-east-1.snowflakecomputing.com";
Connection conn = DriverManager.getConnection(connectStr, properties);

この設定で日時「2023-6-15 21:00:00」を登録すると、以下のように動作します:

• ドライバーは、ローカルタイムゾーン「Asia/Tokyo」（UTC+9:00）の日時「2023-6-15 21:00:00」を UTC「2023-06-15 12:00:00」に変換して Snowflake に送信します。
• Snowflake は受信した値「2023-06-15 12:00:00」をセッションタイムゾーン「Asia/Tokyo」（UTC+9:00）に変換し、 「2023-06-15 21:00:00」を登録します。

TIMEZONE セッションパラメータは TIMESTAMP_TZ 型にも有効です。パラメータに「Asia/Tokyo」を設定して TIMESTAMP_TZ カラムに日時を登録すると、以下のように「2023-06-15 21:00:00 +0900」が登録されます。

3. 登録した日時データと同じ値をドライバーで取得する

Snowflake に保存される値を気にせず、「ドライバーで登録および取得される日時値が同じ」であればよい場合は、 TIMESTAMP_LTZ 型を使用することをお勧めします。この方法ではセッションパラメータを使用しません。

TIMESTAMP_LTZ 型はタイムゾーン付きの日時を保持します。このカラムの値をドライバーで取得すると、 ローカルタイムゾーンに変換された値が返されます。たとえば、TIMESTAMP_LTZ カラム COL_TIMESTAMP_LTZ に 「2023-6-15 21:00:00」を登録すると、UTC（-9:00）に変換され、さらにセッションタイムゾーン（-5:00）に変換されて、 「2023-06-15 07:00:00.000 -0500」が登録されます。

String query = "INSERT INTO TEST(COL_TIMESTAMP_LTZ) VALUES(?)";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setTimestamp(1, Timestamp.valueOf("2023-6-15 21:00:00"));
pstmt.executeUpdate();

以下のようにドライバーでこのカラムの日時を取得すると、逆変換が行われ、「2023-6-15 21:00:00」が返されます。

String query = "SELECT COL_TIMESTAMP_LTZ FROM TEST";
Statement stmt = conn.createStatement();
stmt.executeQuery(query);
ResultSet rs = stmt.getResultSet();
while(rs.next()){
    System.out.println(rs.getString(1));
    // rs.getTimestamp(1) も同じ値を返します
}

無償トライアル & 詳細情報

CData の接続ソリューションにより、Snowflake のタイムスタンプデータの操作がこれまで以上に簡単になります。 CData ドライバーとコネクタをぜひお試しください。 また、CData Sync を使用してビジネスデータを Snowflake にレプリケートすることもできます。 すべてのソリューションの30日間無償トライアルをダウンロードするか、 CData Sync の無償トライアルをお試しください。