引用其他相关问题;通过以下配置,我可以将数据插入Redshift-
COPY "hits" FROM 's3://your-bucket/your_folder/'
CREDENTIALS 'aws_access_key_id=<AWS_ACCESS_KEY_ID>;aws_secret_access_key=<AWS_SECRET_ACCESS_KEY>'
FORMAT as JSON 's3://your-bucket/config/jsonpaths'
TIMEFORMAT as 'epochmillisecs';
它正在将"1528207694599"转换为"2018-06-05 14:08:14",但我期待"2018-06-05 14:08:14.599"。
运气好吗?提前谢谢。
似乎您可能在某个地方做错了什么,我想向您展示一个系统步骤,以证明 copy 正确填充数据,包括毫秒。
create table sales(
salesid integer not null,
category varchar(10),
update_at timestamp);
数据文件data.json的内容
[1,"Sports1","1528207694599"]
[2,"Sports2","1528207694456"]
映射文件json_path.json 的内容
{
"jsonpaths": [
"$[0]",
"$[1]",
"$[2]"
]
}
然后复制命令,
COPY sales FROM 's3://s3-path/to/data/data.json' CREDENTIALS 'aws_access_key_id=**********;aws_secret_access_key=*******' FORMAT as JSON 's3://s3-path/to/mapping/json_path.json' TIMEFORMAT as 'epochmillisecs';
Output:
COPY sales FROM .............
INFO: Load into table 'sales' completed, 2 record(s) loaded successfully.
COPY;
$Select * from Sales;
salesid | category | update_at
---------+----------+-------------------------
2 | Sports2 | 2018-06-05 14:08:14.456
1 | Sports1 | 2018-06-05 14:08:14.599
(2 rows)
如您所见,update_at
具有包括毫秒在内的值。 希望对您有所帮助。