#13531: Converting MySQL ER Model from MySQL to Amazon Redshift with TIME datatype , gets mapped to Varchar instead of Timestamp

Shavi57 reported 2015-07-17T07:49:04Z · last modified 2018-03-08T22:59:45Z

Converting MySQL ER Model from MySQL to Amazon Redshift with TIME datatype , gets mapped to Varchar instead of Timestamp

Dev	tomconrad
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	DB - MySQL
Version	Future

Product: Aqua Data Studio

Version: 17.0.0-dev-82

Build #: 44760

Build Date: 2015-Jul-16 11:47:01 AM

Operating Environment: Linux (3.19.0-23-generic, amd64) / UTF-8 / en / IN / Oracle Corporation 1.8.0_40-b26

Memory: Max=704,643,072; Total=527,958,016; Free=193,119,328; CPUs=8

Database : MySQL 5.6

# Steps to reproduce issue:

Create table using attached .sql file

Go to schema browser

Select created table "time1"

Now Right click on created table "time1"

Select "Tools > ER Diagram Generator" option

In ER Diagram Generator window set below options:

Object types: Tables

Objects : select only created table "time1"

Click on Next button -> Entity Relationship Diagram window gets opened.

In Entity Relationship Diagram window, go to Tools menu

Select "Convert model" option

In Convert model window, Select Amazon Redshift

Click on Next button

Again click on Next button

Observe the datatype

Actual Result:

- Time datatype is mapped to Varchar datatype in Amazon Redshift.

- Amazon Redshift supports timestamp datatype.

Expected Result: Time datatype should be mapped to timestamp datatype in Amazon Redshift as we do for Apache Hive,Apache Cassandra,etc.

6 attachments

time_cassandra.xed

2015-07-17T08:40:51Z

621 B
ADS17-dev-81_13531.png

2015-07-17T08:40:51Z

223 KB
time_mysql.xed

2015-07-17T08:40:51Z

609 B
Table - time1.sql

2015-07-17T08:40:51Z

142 B
time_redshift.xed

2015-07-17T08:40:51Z

619 B
time_mongoDB.xed

2015-07-17T08:56:45Z

616 B

All Comments (5) Change History

Shavi57 2015-07-17T08:41:55Z

Reproducible in v15 and v16 builds.

Shavi57 2015-07-17T10:42:17Z · (edited)

- Converting MySQL ER Model from MySQL to MongoDB with TIME datatype , gets mapped to String instead of Timestamp.

- Converting MySQL ER Model from MySQL to SQlite with TIME datatype , gets mapped to Numeric instead of Timestamp.

- Converting MySQL ER Model from MySQL to VoltDB with TIME datatype , gets mapped to Varchar instead of Timestamp.

Shavi57 2015-07-17T10:27:14Z

Converting MySQL ER Model from MySQL to Oracle with TIME datatype , gets mapped to Date instead of Timestamp

SachinPrakash 2017-06-08T15:26:34Z

@ivan: if the changes are MySQL specific, then go ahead & make them. If the changes will impact other DBs as well, then move it to v20.

tomconrad 2017-08-01T20:19:02Z

The code for each database mentioned in the issue - Redshift, Mongo, Sqllite, Oracle and Volt will have to change to support time. A sample of the Redshift code is below - see red

When doing the conversion, the primary database creates a column object. In this case using a time datatype. The secondary database attempts to create it's own column object based on the primary column object(time). If it cannot find a datatype that will match based on the code below, it will just use the default datatype usually varchar. The fix is easy for each database but will have to change across the different databases listed above.

public EColumn createColumn(DDesc d, String name) {
   if (d.isString()) {
      DDesc.STRING dd = d.getString();
      if (dd.isVariable()) {
         return new EColumn(VARCHAR, name, dd.getLength());
      }
      else {
         return new EColumn(CHAR, name, dd.getLength());
      }
   }
   else if (d.isNumber()) {
      DDesc.NUMBER dd = d.getNumber();
      if (dd.isFloatPoint()) {
         if (dd.getWidth() > 32) {
            return new EColumn(FLOAT8, name);
         }
         else {
            return new EColumn(FLOAT4, name);
         }
      }
      else if (dd.isInteger()) {
         if (dd.getWidth() > 32) {
            return new EColumn(BIGINT, name);
         }
         else if (dd.getWidth() > 16) {
            return new EColumn(INT, name);
         }
         else if (dd.getWidth() > 1) {
            return new EColumn(SMALLINT, name);
         }
         else {
            return new EColumn(BOOLEAN, name);
         }
      }
      else {
         return new EColumn(DECIMAL, name, dd.getPrecision(), dd.getScale());
      }
   }
   else if (d.isDate()) {
      return new EColumn(DATE, name);
   }
   else if (d.isDateTime() || d.isTime()) {
      return new EColumn(TIMESTAMP, name);
   }
   else if (d.isBit()) {
      DDesc.BIT dd = d.getBit();
      return new EColumn(VARCHAR, name, dd.getByteLength(CHAR_LIMIT));
   }
   return null;
}

The code for each database mentioned in the issue - Redshift, Mongo, Sqllite, Oracle and Volt will have to change to support time. A sample of the Redshift code is below - see red

public EColumn createColumn(DDesc d, String name) {
   if (d.isString()) {
      DDesc.STRING dd = d.getString();
      if (dd.isVariable()) {
         return new EColumn(VARCHAR, name, dd.getLength());
      }
      else {
         return new EColumn(CHAR, name, dd.getLength());
      }
   }
   else if (d.isNumber()) {
      DDesc.NUMBER dd = d.getNumber();
      if (dd.isFloatPoint()) {
         if (dd.getWidth() > 32) {
            return new EColumn(FLOAT8, name);
         }
         else {
            return new EColumn(FLOAT4, name);
         }
      }
      else if (dd.isInteger()) {
         if (dd.getWidth() > 32) {
            return new EColumn(BIGINT, name);
         }
         else if (dd.getWidth() > 16) {
            return new EColumn(INT, name);
         }
         else if (dd.getWidth() > 1) {
            return new EColumn(SMALLINT, name);
         }
         else {
            return new EColumn(BOOLEAN, name);
         }
      }
      else {
         return new EColumn(DECIMAL, name, dd.getPrecision(), dd.getScale());
      }
   }
   else if (d.isDate()) {
      return new EColumn(DATE, name);
   }
   else if (d.isDateTime() || d.isTime()) {
      return new EColumn(TIMESTAMP, name);
   }
   else if (d.isBit()) {
      DDesc.BIT dd = d.getBit();
      return new EColumn(VARCHAR, name, dd.getByteLength(CHAR_LIMIT));
   }
   return null;
}

Search Tips

Aqua Data Studio / nhilam

Converting MySQL ER Model from MySQL to Amazon Redshift with TIME datatype , gets mapped to Varchar instead of Timestamp

6 attachments

Issue #13531

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Converting MySQL ER Model from MySQL to Amazon Redshift with TIME datatype , gets mapped to Varchar instead of Timestamp

6 attachments

Issue #13531

Completion