时间戳

首页 > 解决方案 > 使用 BTEQ 插入 teradata 时时间戳无效

sql - 使用 BTEQ 插入 teradata 时时间戳无效

问题描述

所以我想在之前用 bteq 创建的表中插入一些东西。我已经用谷歌搜索了这个（例如这里）但是我已经在专栏上投了，这并没有消除错误。

创建语句如下所示：

.logmech LDAP
.logon databae/user_id,pwd
.set width 256
.set retcancel on database libname;
DATABASE libname;
create MULTISET table libname.IRIS( "SEPAL_LENGTH" FLOAT,  "SEPAL_WIDTH" FLOAT,  "PETAL_LENGTH" FLOAT,  "PETAL_WIDTH" FLOAT,  "TARGET" FLOAT,  "TIMESTAMP1" TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)' DEFAULT NULL ,  "NULL_VALUES" FLOAT) UNIQUE PRIMARY INDEX("TimeStamp1");
.QUIT
.LOGOFF
.EXIT

插入语句如下所示：

.logmech LDAP
.logon databae/user_id,pwd
.set width 256
.set retcancel on database libname;
DATABASE libname;
.IMPORT VARTEXT FILE =/path_to_file/data.csv, skip=1;
.REPEAT *
USING "SEPAL_LENGTH" (varchar(2000)),  "SEPAL_WIDTH" (varchar(2000)),  "PETAL_LENGTH" (varchar(2000)),  "PETAL_WIDTH" (varchar(2000)),  "TARGET" (varchar(2000)),  "TIMESTAMP1" (varchar(2000)),  "NULL_VALUES" (varchar(2000)) 
INSERT INTO IRIS_delete VALUES(cast( :"SEPAL_LENGTH"  as FLOAT), cast( :"SEPAL_WIDTH"  as FLOAT), cast( :"PETAL_LENGTH"  as FLOAT), cast( :"PETAL_WIDTH"  as FLOAT), cast( :"TARGET"  as FLOAT), cast( :"TIMESTAMP1"  as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'), cast( :"NULL_VALUES"  as FLOAT));
.QUIT
.LOGOFF
.EXIT

通过执行插入语句，我得到了错误：

*** 失败 6760 时间戳无效。语句# 1，信息 =0

以下是 csv 的一些示例：

|sepal_length|sepal_width|petal_length|petal_width|target|TimeStamp1|Null_values 0|5.1|3.5|1.4|0.2|0.0|2018-09-18 13:26:30.583216|

1|4.9|3.0|1.4|0.2|0.0|2018-09-18 13:26:30.583216|

2|4.7|3.2|1.3|0.2|0.0|2018-09-18 13:26:30.583216|

3|4.6|3.1|1.5|0.2|0.0|2018-09-18 13:26:30.583216|

4|5.0|3.6|1.4|0.2|0.0|2018-09-18 13:26:30.583216|

5|5.4|3.9|1.7|0.4|0.0|2018-09-18 13:26:30.583216|

6|4.6|3.4|1.4|0.3|0.0|2018-09-18 13:26:30.583216|

7|5.0|3.4|1.5|0.2|0.0|2018-09-18 13:26:30.583216|

8|4.4|2.9|1.4|0.2|0.0|2018-09-18 13:26:30.583216|

9|4.9|3.1|1.5|0.1|0.0|2018-09-18 13:26:30.583216|

你知道时间戳有什么问题吗？

标签： sqlteradatasql-insertcreate-table

解决方案

咪咪，您的 bteq 脚本和数据文件中的列数和顺序不匹配 检查以下示例以获取脚本中指定的列和文件中的列数 

"SEPAL_LENGTH" (1),  "SEPAL_WIDTH"(2),  "PETAL_LENGTH"(3),  "PETAL_WIDTH"(4),  "TARGET"(5),  "TIMESTAMP1"(6),  "NULL_VALUES"(7)

col1->0|  col2->5.1|  col3->3.5|  col4->1.4|  col5->0.2|  col6->0.0|  col7->2018-09-18 13:26:30.583216|  col8->

您可以通过在表中的时间戳列和 bteq 脚本之前再添加 1 列来解决您的问题，如下所示，我在时间戳列之前添加了“TARGET1”

USING "SEPAL_LENGTH" (varchar(2000)),  "SEPAL_WIDTH" (varchar(2000)),  "PETAL_LENGTH" (varchar(2000)),  "PETAL_WIDTH" (varchar(2000)),  "TARGET" (varchar(2000)),  "TARGET1" (varchar(2000)),  "TIMESTAMP1" (varchar(2000)),  "NULL_VALUES" (varchar(2000)) 
INSERT INTO IRIS_delete VALUES(cast( :"SEPAL_LENGTH"  as FLOAT), cast( :"SEPAL_WIDTH"  as FLOAT), cast( :"PETAL_LENGTH"  as FLOAT), cast( :"PETAL_WIDTH"  as FLOAT), cast( :"TARGET"  as FLOAT), cast( :"TARGET1"  as FLOAT), cast( :"TIMESTAMP1"  as TIMESTAMP(6) FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)'), cast( :"NULL_VALUES"  as FLOAT));

推荐阅读