PostgreSQLとAWS Glue Crawlerの日時のデータ型

PostgreSQLの日時のデータ型 timestamp と timestamp with timezone (timestamptz) をAWS Glue Crawlerでクロールするとデータカタログに登録されるテーブルのデータ型が何になるかを調べました。

PostgreSQLにサンプルテーブル作成

Glue CrawlerからはJDBCで接続できればよいのでPostgreSQLはAWS RDSでもEC2上のPostgreSQLでも、もしくはAWS以外の環境でも大丈夫です。ここではGCPのCloud SQLにPostgreSQLのインスタンスを立てました。

テーブル作成です。カラムは timestamp と timestamp with timezone を用意します。

CREATE TABLE sample1 (
    timestamp1 timestamp,
    timestamp2 timestamptz,
    msg text
);

レコード挿入。

INSERT INTO sample1 (timestamp1, timestamp2, msg) VALUES('2020-07-23 00:00:00', '2020-07-23 00:00:00', 'aaa');

以下はコンソール上のpsqlコマンドの様子です。

SELECTして確認します。

postgres=> select * from sample1;
     timestamp1      |       timestamp2       | msg 
---------------------+------------------------+-----
 2020-07-23 00:00:00 | 2020-07-23 00:00:00+00 | aaa
(1 rows)

タイムゾーンの設定を確認するとUTCでした。

postgres=> SHOW TIME ZONE;
 TimeZone 
----------
 UTC
(1 row)

タイムゾーンを日本時間に変えてみます。

postgres=> SET TIME ZONE 'Asia/Tokyo';
SET
postgres=> SHOW TIME ZONE;
  TimeZone  
------------
 Asia/Tokyo
(1 row)

すると timestamp with timezone のカラムだけ表示が日本時間に変わることが確認できます。

postgres=> select * from sample1;
     timestamp1      |       timestamp2       | msg 
---------------------+------------------------+-----
 2020-07-23 00:00:00 | 2020-07-23 09:00:00+09 | aaa
(1 rows)

Glue Crawler実行

Glue Crawlerを作成します。

※今回はGCPにPostgreSQLをたてたので、Glue Crawlerはインターネット経由でクロールすることになります。Glue CrawlerはプライベートIPを持つので、Glue Crawlerの置くサブネットにはNATゲートウェイへのルーティングが必要です。それがないと Glue Connection作成時のテスト接続に失敗します。

PostgreSQLのJDBCのURLはこんな感じです。

jdbc:postgresql://XXX.XXX.XXX.XXX:5432/DBNAME

実行結果

PostgreSQLの timestampe と timestamp with timezone はどちらも単に timestamp というデータ型になっています。

awscliでデータ型を見ても同じでした。

$ aws glue get-table --database-name crawler-test2 --name postgres_public_sample1 | jq ".Table.StorageDescriptor.Columns"
[
  {
    "Name": "timestamp1",
    "Type": "timestamp"
  },
  {
    "Name": "msg",
    "Type": "string"
  },
  {
    "Name": "timestamp2",
    "Type": "timestamp"
  }
]

timestamp と timestamp with timezone は区別されなさそうです。

Glue JobでアクセスしてCSVで保存してみる

以下のようなスクリプトでGlue Jobを作成して実行してみました。

import sys
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ['JOB_NAME'])

sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)
dynamicframe1 = glueContext.create_dynamic_frame.from_catalog(database = "crawler-test2", table_name = "postgres_public_sample1", transformation_ctx = "datasource0")
df = dynamicframe1.toDF()
datasink2 = glueContext.write_dynamic_frame.from_options(frame = dynamicframe1, connection_type = "s3", connection_options = {"path": "s3://xxxxxxxx/glue-output"}, format = "csv", transformation_ctx = "datasink2")
job.commit()

S3に以下のようなCSVファイルが書き出されていました。

timestamp1,timestamp2,msg
"2020-07-23 00:00:00.0","2020-07-23 00:00:00.0",aaa

timestamp のデータ(1つ目のカラム)はそのままです。

timestamp with timezone のデータ(2つ目のカラム)は、UTCのタイムゾーンで文字列に変換されているようです。

PostgreSQLの timestamp と timestamp with timezone はGlue CrawlerでData Catalogを作成するとデータ型としては区別されないように見えます。