Cassandra`COPY FROM`无法将GMT日期字符串强制转换为格式化日期（长整数） [英] Cassandra `COPY FROM`unable to coerce GMT date string to a formatted date (long)
问题描述
我一直试图使用 COPY FROM 插入具有 timestamp 类型列的Cassandra表中。但是，我遇到以下错误：
I have been trying to use COPY FROM to insert into a Cassandra table that has a timestamp type column. However, I encountered the following error:
code=2200 [Invalid query] message="unable to coerce '2015-03-06 18:11:33GMT' to a formatted date (long)"
Aborting import at record #3. Previously-inserted values still present.
0 rows imported in 0.211 seconds.
CSV文件的内容实际上是使用COPY TO命令创建的。我的 TZ 环境变量已设置为 GMT 。
The content of the CSV file was actually created with a COPY TO command. My TZ environment variable has been set to GMT.
我做了一些搜索，发现其中的帖子提到使用 Z 而不是 GMT 作为数据字符串中的时区，即'2015-03-06 18：11：33Z'。如果我将CSV中的所有 GMT 替换为 Z ，则 COPY FROM 有效。帖子链接：
无法将'2012/11/11'强制转换为格式化日期（长）
I did some searching and found a post here that mentioned using Z instead of GMT as the timezone in the data string, i.e. '2015-03-06 18:11:33Z'. If I replace all the GMT in my CSV with Z, COPY FROM worked. Link for the post here:
unable to coerce '2012/11/11' to a formatted date (long)
当我运行 SELECT 在此表上，datetime列以以下格式显示： 2015-03-06 17：53：23GMT 。
When I run a SELECT on this table, the datetime column shows up in the format of: 2015-03-06 17:53:23GMT.
更多信息，关于 Z时区存在错误，但已修复。链接： https://issues.apache.org/jira/browse/CASSANDRA-6973
Further info, there was a bug about 'Z' timezone but it was fixed. Link: https://issues.apache.org/jira/browse/CASSANDRA-6973
所以我的问题是，有没有一种方法可以运行 COPY TO 使其写为 Z 而不是 GMT 的时区？
So my question is, is there a way that I can run COPY TO so that it writes Z instead of GMT for time zone?
或者，有什么方法可以使 COPY FROM 与 GMT一起工作？
Alternatively, is there a way I can make COPY FROM work with GMT?
谢谢。
注意：该解决方案在@Aaron的评论中。是的，它是一种hack，但是可以。
推荐答案
我认为这里正在发生什么，就是您正在在〜/ .cassandra / cqlshrc 文件中的 time_format 属性中位。 COPY 在 COPY TO 期间导出时间戳数据时使用此设置。 CQLSH使用 Python strftime格式。有趣的是，小写的％z和大写的％Z似乎代表了您的问题。
I think what is happening here, is that you are getting bit by your time_format property in your ~/.cassandra/cqlshrc file. COPY uses this setting when exporting your timestamp data during a COPY TO. CQLSH uses the Python strftime formats. It is interesting to note that the lowercase %z and uppercase %Z seem to represent your problem.
当我 SELECT 带有％Z（上部）的时间戳数据，看起来像这样：
When I SELECT timestamp data with %Z (upper), it looks like this:
aploetz@cqlsh:stackoverflow> SELECT * FROm posts1;
userid | posttime | postcontent | postid
--------+------------------------+--------------+--------------------------------------
1 | 2015-01-25 13:25:00CST | blahblah5 | 13218139-991c-4ddc-a11a-86992f6fed66
1 | 2015-01-25 13:22:00CST | blahblah2 | eacdebcc-35c5-45f7-9374-d5fd987e699f
0 | 2015-03-12 14:10:00CDT | sdgfjdsgojr | 82766df6-4cca-4ad1-ae59-ba4488103da4
0 | 2015-03-12 13:56:00CDT | kdsjfsdjflds | bd5c2be8-be66-41da-b9ff-98e9a4836000
0 | 2015-03-12 09:10:00CDT | sdgfjdsgojr | 6865216f-fc4d-431c-8067-c27cf20b6be7
当我尝试 INSERT 使用该日期格式的记录，它将失败：
When I try to INSERT a record using that date format, it fails:
aploetz@cqlsh:stackoverflow> INSERT INTO posts1 (userid,posttime,postcontent,postid) VALUES (0,'2015-03-12 14:27CST','sdgfjdsgojr',uuid());
code=2200 [Invalid query] message="unable to coerce '2015-03-12 14:27CST' to a formatted date (long)"
但是当我更改 time_format 以使用（小写）％z时，同一查询会生成以下内容：
But when I alter time_format to use the (lowercase) %z the same query produces this:
aploetz@cqlsh:stackoverflow> SELECT * FROm posts1;
userid | posttime | postcontent | postid
--------+--------------------------+--------------+--------------------------------------
1 | 2015-01-25 13:25:00-0600 | blahblah5 | 13218139-991c-4ddc-a11a-86992f6fed66
1 | 2015-01-25 13:22:00-0600 | blahblah2 | eacdebcc-35c5-45f7-9374-d5fd987e699f
0 | 2015-03-12 14:10:00-0500 | sdgfjdsgojr | 82766df6-4cca-4ad1-ae59-ba4488103da4
0 | 2015-03-12 13:56:00-0500 | kdsjfsdjflds | bd5c2be8-be66-41da-b9ff-98e9a4836000
0 | 2015-03-12 09:10:00-0500 | sdgfjdsgojr | 6865216f-fc4d-431c-8067-c27cf20b6be7
我也可以 INSERT 数据的格式如下：
I can also INSERT data in this format:
INSERT INTO posts1 (userid,posttime,postcontent,postid)
VALUES (0,'2015-03-12 14:27-0500','sdgfjdsgojr',uuid());
当我运行 COPY TO 和相同数据/文件的 COPY FROM 也可以。
It also appears in this way when I run a COPY TO, and a COPY FROM of the same data/file also works.
总而言之，请检查您的〜/ .cassandra / cqlshrc ，并确保您使用默认设置，或者使用 [ui] 部分：
In summary, check your ~/.cassandra/cqlshrc and make sure that you are either using the default setting, or this setting in the [ui] section:
[ui]
time_format = %Y-%m-%d %H:%M:%S%z
它不会像您要求的那样为您提供 Z，但这将允许您复制到/从复制数据，而无需处理CSV文件。
It won't get you the 'Z' like you asked for, but it will allow you to COPY TO/FROM your data without having to muck with the CSV file.
编辑
对于那些可怜的人，在Windows上使用CQLSH（或Cassandra，上帝帮助您），<$的默认位置c $ c> cqlshrc 文件为 c：\Users\％USERNAME％\.cassandra\cqlshrc 。
For those of you poor souls out there using CQLSH (or Cassandra, God help you) on Windows, the default location of the cqlshrc file is c:\Users\%USERNAME%\.cassandra\cqlshrc.
编辑-20150903
受此问题的启发，我提交了补丁程序（ CASSANDRA-8970 ）允许用户使用 COPY ，并且昨天它被标记为准备提交。基本上，此补丁可以通过以下操作解决此问题：
Inspired by this question, I submitted a patch (CASSANDRA-8970) to allow users to specify a custom time format with COPY, and it was marked as "Ready To Commit" yesterday. Basically, this patch will allow this problem to be solved by doing the following:
COPY posts1 TO '/home/aploetz/posts1.csv' WITH DELIMITER='|' AND HEADER=true
AND TIME_FORMAT='%Y-%m-%d %H:%M:%SZ;
编辑-20161010
COPY命令已经在Cassandra 2.2.5中得到了改进，并且 TIMEFORMAT 选项已重命名为 DATETIMEFORMAT 。
The COPY command was improved in Cassandra 2.2.5, and the TIMEFORMAT option has been renamed to DATETIMEFORMAT.
DATETIMEFORMAT，以前称为TIMEFORMAT，该字符串包含日期和时间值的Python strftime格式，例如'％Y-％m-％d％H：％M：％S％z'。它默认为cqlshrc中的time_format值。
DATETIMEFORMAT, which used to be called TIMEFORMAT, a string containing the Python strftime format for date and time values, such as ‘%Y-%m-%d %H:%M:%S%z’. It defaults to the time_format value in cqlshrc.
这篇关于Cassandra`COPY FROM`无法将GMT日期字符串强制转换为格式化日期（长整数）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
