将格式为rfc822的日期时间复制到redshift中 [英] Copy a datetime with the format rfc822 into redshift

问题描述

我有以下红移表:

DROP TABLE IF EXISTS "logs";
CREATE TABLE "logs" (
  "source" varchar(255) DEFAULT NULL,
  "method" varchar(255) DEFAULT NULL,
  "path" varchar(1023) DEFAULT NULL,
  "format" varchar(255) DEFAULT NULL,
  "controller" varchar(255) DEFAULT NULL,
  "action" varchar(255) DEFAULT NULL,
  "status" integer DEFAULT NULL,
  "duration" float DEFAULT NULL,
  "view" float DEFAULT NULL,
  "db" float DEFAULT NULL,
  "ip" varchar(255)DEFAULT NULL,
  "route" varchar(255) DEFAULT NULL,
  "request_id" varchar(255) DEFAULT NULL,
  "user" INTEGER DEFAULT  NULL,
  "school" varchar(255) DEFAULT NULL,
  "timestamp" datetime DEFAULT NULL
);

到目前为止一切都很好.

So far so good.

唯一的问题是我的源文件在s3上的日期时间如下:"2015-01-13T11:13:08.869941+00:00".看起来像是rfc822(或rfc3339或rfc2822).

The only problem is that the datetime in my source file on s3 is the following: "2015-01-13T11:13:08.869941+00:00". This looks like rfc822 (or rfc3339 or rfc2822).

COPY命令支持一些时间格式(请参阅doc: http ://docs.aws.amazon.com/redshift/latest/dg/r_DATEFORMAT_and_TIMEFORMAT_strings.html ).但不是我的rfc822格式.

A few timeformats are supported by the COPY command (see doc: http://docs.aws.amazon.com/redshift/latest/dg/r_DATEFORMAT_and_TIMEFORMAT_strings.html). But not my rfc822 format.

我尝试了以下操作:

TRUNCATE logs;
COPY "logs" FROM 's3://path/to/logstash_logfile.gz'
CREDENTIALS 'aws_access_key_id=THE_KEY;aws_secret_access_key=THE_SECRET'
TIMEFORMAT AS 'MM-DD-YYYYTHH:MI:SS'
JSON 's3://path/to/jsonpath.json' GZIP;

但是我得到:

SELECT * FROM stl_load_errors;

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

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

推荐答案

请改用TIMEFORMAT 'auto'.

可以导入

2015-01-13T11:13:08.869941+00:00

为

2015-01-13 11:13:08.869941.

我认为此方法只是丢弃时区信息，但至少您可以通过这种方式获取数据.

I assume this method just discards the timezone information, but at least you can get the data in this way.

例如，如果数据中有不同的时区，则可能需要进行一些预处理，以将所有内容转换为UTC.

If you have various timezones in the data, maybe you need to do some preprocessing to convert everything into UTC, for example.

不幸的是，我认为具有提供的时间格式的COPY相当严格，并且不支持时区部分.

Unfortunately I think the COPY with a provided time format is rather strict and doesn't support timezone parts.

这篇关于将格式为rfc822的日期时间复制到redshift中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！