Import DATE (YYYY-MM-DD) wrong value


#1

I have created a table using:

CREATE TABLE tablename (
aa INTEGER,
mm INTEGER,
dtdoc DATE,
...

and then imported a string like: “2017-01-25”, “2017-05-27”, etc.

But the result is:

aa	mm	dtdoc
2017	1	1970-08-22
2017	1	1970-08-22
2017	1	1970-08-22

All the dates are transformed in “1970-08-22”.

In the DOCS I can read:

Accepted Date, Time, and Timestamp formats
Datatype Formats Examples
DATE YYYY-MM-DD 2013-10-31

so why is that ??


#2

Hi camgia,

If possible, could you post a few rows of the dataset you’re trying to import, or otherwise create a simplified dataset which shows the issue? It sounds like a data formatting/parsing issue since that date (1970-08-22) is about 20170125 seconds after the epoch.

Also, which method are you using to import the data? COPY from a csv, Immerse, StreamInsert, custom Thrift client, etc.


#3

Here some rows: I’m using SQLimporter , with the empty table already in place: the date is the third column, named “dtdoc”

aa, mm, dtdoc, mag, cau, codfor, codpro, codcli, trans_progr_a, trans_v, progr, qta, prz_ces, colli, tipo, voce, stato, pedane, c_n_1, c_n_4, c_n_6, tipo_mov, colli_mancanti, area_cedis, peso, lista, nrig, handling, trasporto, immobile_MM, immobile_VET
'2017', '5', '2017-05-27', '65', '3010', '595000', '402361', '566517', '3010', '174157612', '1', '10.0000', '1.3700', '1.0000', '0', '0', '0', '0', '1.06400', '1.03172', '1.03172', 'CEDI_AC', '0.00', 'G', '2500.00', '206955', '2115', '0.18050000', '0.28702911', '0.05790430', '0.02050382'
'2017', '5', '2017-05-27', '65', '3010', '166200', '403039', '566517', '3010', '174157612', '2', '10.0000', '0.9000', '1.0000', '0', '0', '0', '0', '0.68400', '0.66275', '0.66275', 'CEDI_AC', '0.00', 'G', '3750.00', '206955', '2120', '0.18050000', '0.18855927', '0.05790430', '0.02050382'
'2017', '5', '2017-05-27', '65', '3010', '25404', '593245', '566517', '3010', '174157612', '3', '14.0000', '1.8500', '1.0000', '0', '0', '0', '0', '1.73065', '1.41450', '1.39925', 'CEDI_AC', '0.00', 'G', '5250.00', '206955', '2125', '0.18050000', '0.54263168', '0.05790430', '0.02050382'
'2017', '5', '2017-05-27', '65', '3010', '166200', '403069', '566517', '3010', '174157612', '4', '28.0000', '1.3500', '2.0000', '0', '0', '0', '0', '1.03100', '0.99726', '0.99726', 'CEDI_AC', '0.00', 'G', '10500.00', '206955', '2130', '0.36100000', '0.79194893', '0.11580859', '0.04100763'

#4

Hi

What kind of database is it coming out of?

How is that column defined in the source DB

Regards


#5

It is MySQL,
in the source table dtdoc is integer, then is transformed in CHAR:

(cast(DATE(dtdoc) as CHAR(10))


#6

Hi

Please give an example of the integer value for this column in MySQL

Regards


#7

Sure, it is :

20170125


#8

Hi

Are you casting it to char in the query to SQLImporter?

Please share your SQL query complete.

I believe you should leave it as a DATE in your SQL to get what you expect

Regards


#9

Yes, I am.

java -cp /opt/mapd/mapd-ce-3.1.1/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/opt/jdbc_driver/MySql/mysql-connector-java-5.1.39/mysql-connector-java-5.1.39-bin.jar com.mapd.utility.SQLImporter -t tablename -sp psw -su usr -c jdbc:mysql://server:port/dbname -ss "select aa,mm,cast(DATE(dtdoc) as char(10)) dtdoc ,mag,cau,codfor,codpro,codcli,trans_progr_a,trans_v,progr,qta,prz_ces,colli,tipo,voce,stato,pedane,c_n_1,c_n_4,c_n_6,tipo_mov,colli_mancanti,area_cedis,peso,lista,nrig,handling,trasporto,immobile_MM,immobile_VET from tablename"

I’m trying as you suggested.


#10

Still not working: leaving it as a DATE:

select aa,mm, DATE(dtdoc) dtdoc ,mag,cau,codfor,codpro,codcli,trans_progr_a,trans_v,progr,qta,prz_ces,colli,tipo,voce,stato,pedane,c_n_1,c_n_4,c_n_6,tipo_mov,colli_mancanti,area_cedis,peso,lista,nrig,handling,trasporto,immobile_MM,immobile_VET from tablename

I Get:

./startmapd: line 102: 19573 Segmentation fault (core dumped) ./bin/mapd_server $MAPD_DATA $RO --port $MAPD_TCP_PORT --http-port $MAPD_HTTP_PORT --calcite-port MAPD_CALCITE_PORT *
./startmapd: line 1: kill: (-19572) - No such process
2017-07-13 18:48:29 ERROR SQLImporter:executeQuery:267 - TException failed - org.apache.thrift.transport.TTransportException
org.apache.thrift.transport.TTransportException
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429)
at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318)
at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77)
at com.mapd.thrift.server.MapD$Client.recv_load_table(MapD.java:1054)
at com.mapd.thrift.server.MapD$Client.load_table(MapD.java:1039)
at com.mapd.utility.SQLImporter.executeQuery(SQLImporter.java:239)
at com.mapd.utility.SQLImporter.doWork(SQLImporter.java:177)
at com.mapd.utility.SQLImporter.main(SQLImporter.java:54)
[1]+ Exit 1 sudo LD_LIBRARY_PATH=/usr/lib/jvm/jre-1.8.0-openjdk/lib/amd64/server ./startmapd --data /home/mapd-ce-3.1.1/data/ --allow-cpu-retry --allow-loop-join

In addition, I also tried with the COPY command , and with the same query the import is fine.


#11

Hi,

Not sure what you are running into there.

I tried it locally

on mysql:

mysql> create table test1 (i1 integer);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test1 values (20170501);
Query OK, 1 row affected (0.02 sec)

mysql> select i1 from test1;
+----------+
| i1       |
+----------+
| 20170501 |
+----------+
1 row in set (0.00 sec)

mysql> select date(i1) from test1;
+------------+
| date(i1)   |
+------------+
| 2017-05-01 |
+------------+
1 row in set (0.01 sec)

then from SQLImporter

java -cp /data/mapd-core/build/bin/mapd-1.0-SNAPSHOT-jar-with-dependencies.jar:/data/mysql/mysql-connector-java-5.1.39-bin.jar com.mapd.utility.SQLImporter -t test1 -sp pswd -su root -c jdbc:mysql://localhost -ss "select date(i1) as d1 from mydb.test1" -tr

then in MapD I see

mapdql> \d test1
CREATE TABLE test1 (
d1 DATE)
mapdql> select d1 from test1;
d1
2017-05-01
mapdql>

I did have to make sure I used AS on a column which was a function.

Please let me know whats different in your env?

regards


#12

Hi Dwayne,

the cause was exactly the missing “AS”, as you suggested.

Thanks for your excellent support.