如何将TIMESTAMP列插入Redshift [英] How to Insert TIMESTAMP Column into Redshift

问题描述

我在Redshift中创建了一个表:

I created a table in Redshift:

create table myTable (
       dateTime TIMESTAMP NOT NULL,
       ...
);

但是，当我尝试插入包含 dateTime 的记录时，出现 stl_load_errors 错误.

However, when I try to insert a record that contains a dateTime of, I get an error from stl_load_errors.

20080215 04:05:06.789

20080215 04:05:06.789

自从我从 docs 开始获取此时间戳后，我本来希望它能奏效.

Since I took this timestamp from the docs, I would've expected it to have worked.

Redshift显示的错误日志:

The error logs from Redshift show:

无效的时间戳格式或值[YYYY-MM-DD HH24:MI:SS]

Invalid timestamp format or value [YYYY-MM-DD HH24:MI:SS]

但是，我想多加3秒，例如: 2015-02-01 15:49:35.123 .

However, I'd like to include 3 extra seconds, example: 2015-02-01 15:49:35.123.

我该如何修改时间戳字段以使其在几秒钟内以更高的精度插入?

How do I need to modify my timestamp field to insert it with the extra precision on seconds?

推荐答案

TL; DR-从S3文件导入Redshift时，强制导入的数据的默认时间格式为'YYYY-MM-DD HHRedshift期望:MI:SS'以便在几秒钟后获得精度，否则它将被截断.

TL;DR - When importing into Redshift from an S3 file force the imported data to have the default time format of 'YYYY-MM-DD HH:MI:SS'that Redshift expects in order to get a precision past seconds, otherwise it will be truncated.

我在尝试上传以从S3插入时遇到了同样的问题.我原来的JSON具有这样的时间戳. {"updated_at":"2014-12-08T21:14:49.351638"} .但是，当我将其拖入Redshift时，我需要设置格式，其中包括在时间之前的T.

I ran into this same issue while trying to upload to pull in from S3. My original JSON has a timestamp like this. { "updated_at" : "2014-12-08T21:14:49.351638" }. However when I went to pull it into Redshift I needed to set the format, which included the T before the time.

COPY schema.temp_table FROM 's3://s3-bucket/file-name'
    WITH CREDENTIALS 'aws_access_key_id=access-key;aws_secret_access_key=secret-key'
    format as json 'auto'
    timeformat 'YYYY-MM-DDTHH:MI:SS';

这导入了所有内容，但是时间总是被截断为秒，所以我最终在Redshift中使用 2014-12-08 21:14:49 .

This imported everything, however the time was always truncated to seconds, so I would end up with 2014-12-08 21:14:49 in Redshift.

文档看起来应该可以精确导入到6个位置，但事实并非如此.

The documentation looks like this should import with precision out to 6 places, but this was not the case.

我决定尝试使用默认格式'YYYY-MM-DD HH:MI:SS'导入Redshift，因此我不得不更改Postgres数据库以导出日期字段中的JSON正确的格式为 to_char(updated_at，'YYYY-MM-DD HH24:MI:SS.SSSSS')为Updated_at .

I decided to try out the default format 'YYYY-MM-DD HH:MI:SS' for importing to Redshift so I had to change my Postgres database to export the JSON for date fields in the correct format to_char(updated_at, 'YYYY-MM-DD HH24:MI:SS.SSSSS') as updated_at.

进行此更改后，将新的JSON导出为 {"updated_at":" 2014-12-08 21:14:49.351638"} ，然后将导入Redshift的时间格式设置为默认的 format格式为json'auto'timeformat'YYYY-MM-DD HH:MI:SS';

After making this change the new JSON exported as { "updated_at" : "2014-12-08 21:14:49.351638" } and I set the timeformat for the import into Redshift as the default format as json 'auto' timeformat 'YYYY-MM-DD HH:MI:SS';

通过进行更改以使用默认的时间格式，Redshift现在以正确的精度导入了时间戳！

By making this change to use the default timeformat Redshift now imported the timestamps with the correct precision!

这篇关于如何将TIMESTAMP列插入Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！