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 时，日期时间列以以下格式显示: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 对这篇文章的评论中.是的，这是一个黑客，但它有效.
推荐答案
我认为这里发生的事情是，您正在被 ~/.cassandra 中的 文件.time_format 属性所困扰/cqlshrcCOPY 在 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"，但它可以让您COPY TO/FROM 数据，而不必弄乱 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，上帝帮助你)的可怜人，cqlshrc 文件的默认位置是 c:Users\%USERNAME%.cassandracqlshrc.
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%.cassandracqlshrc.
编辑 - 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屋！
