Db plugins enhancements: support all data types

Description

  • all DB plugins should support all basic jdbc types

  • all DB plugins should support all DB specific types or provide fallback to "string" if support is not possible

  • improvement should affect all existing DB source and sink plugins: aurora-mysql, aurora-postgres, db2, mssql, mysql, netezza, oracle, postgresql

As result of db plugins analysis:

Legend

Data type

Data type in origin DB

CDAP mapped type

Data type mapped in CDAP scheme

Support

Does CDAP supports this data type (means that there is defined converting in CommonSchemaReader or CustomSchemaReader class)

Source test

Is there a test for this type in source tests

Sink test

Is there a test for this type in sink tests

Comment

Additional information about this type

MySQL db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

BIT

Schema.Type.BOOLEAN

+

+

-

 

TINYINT

Schema.Type.INT

+

+

-

 

BOOL, BOOLEAN

Schema.Type.BOOLEAN

+

+

-

 

SMALLINT

Schema.Type.INT

+

+

-

 

MEDIUMINT

Schema.Type.INT

+

+

-

 

INT,INTEGER

Schema.Type.INT

+

+

-

 

BIGINT

Schema.Type.LONG

+

+

-

 

FLOAT

Schema.Type.FLOAT

+

+

-

 

DOUBLE

Schema.Type.DOUBLE

+

+

-

 

DECIMAL

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

DATE

Schema.Type.DATE

+

+

+

 

DATETIME

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

TIMESTAMP

Schema.LogicalType.TIMESTAMP_MICROS

+

+

+

 

TIME

Schema.LogicalType.TIME_MICROS

+

+

+

 

YEAR

Schema.Type.STRING

+

+

-

No such type in java.sql.Types, mapping to String by default

CHAR

Schema.Type.STRING

+

+

-

 

VARCHAR

Schema.Type.STRING

+

+

+

 

BINARY

Schema.Type.BYTES

+

+

-

 

VARBINARY

Schema.Type.BYTES

+

+

-

 

TINYBLOB

Schema.Type.BYTES

+

+

-

 

TINYTEXT

Schema.Type.STRING

+

-

-

 

BLOB

Schema.Type.BYTES

+

+

-

 

TEXT

Schema.Type.STRING

+

+

-

 

MEDIUMBLOB

Schema.Type.BYTES

+

+

-

 

MEDIUMTEXT

Schema.Type.STRING

+

-

-

 

LONGBLOB

Schema.Type.BYTES

+

+

-

 

LONGTEXT

Schema.Type.STRING

+

-

-

 

ENUM

Schema.Type.STRING

+

-

-

 

SET

Schema.Type.STRING

+

-

-

 

PostgreSQL db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

bigint

Schema.Type.LONG

+

+

-

 

bigserial

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

bit

Schema.Type.BOOLEAN

+

-

-

 

bit varying

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

boolean

Schema.Type.BOOLEAN

+

+

-

 

box

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

bytea

Schema.Type.BYTES

+

+

+

 

character

Schema.Type.STRING

+

+

-

 

character varying

Schema.Type.STRING

+

+

+

 

cidr

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

circle

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

date

Schema.LogicalType.DATE

+

+

+

 

double precision

Schema.Type.DOUBLE

+

-

-

 

inet

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

integer

Schema.Type.INT

+

+

-

 

interval

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

json

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

jsonb

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

line

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

lseg

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

macaddr

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

macaddr8

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

money

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

numeric

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

path

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

pg_lsn

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

point

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

polygon

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

real

Schema.Type.FLOAT

+

+

-

 

smallint

Schema.Type.INT

+

+

-

 

smallserial

Schema.Type.STRING

*

+

-

All java.sql.Types.OTHER are mapping to String

serial

Schema.Type.STRING

*

+

-

All java.sql.Types.OTHER are mapping to String

text

Schema.Type.STRING

*

+

-

All java.sql.Types.OTHER are mapping to String

time [ (p) ] [ without time zone ]

Schema.LogicalType.TIME_MICROS

+

+

+

 

time [ (p) ] with time zone

Schema.LogicalType.TIME_MICROS

+

-

-

 

timestamp [ (p) ] [ without time zone ]

Schema.LogicalType.TIMESTAMP_MICROS

+

+

+

 

timestamp [ (p) ] with time zone

Schema.LogicalType.TIMESTAMP_MICROS

+

-

-

 

tsquery

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

tsvector

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

txid_snapshot

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

uuid

Schema.Type.STRING

*

-

-

All java.sql.Types.OTHER are mapping to String

xml

Schema.Type.STRING

+

-

-

 

MS SQL db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

bigint

Schema.Type.LONG

+

+

-

 

binary

Schema.Type.BYTES

+

+

-

 

bit

Schema.Type.BOOLEAN

+

+

-

 

char

Schema.Type.STRING

+

+

-

 

date

Schema.LogicalType.DATE

+

+

+

 

datetime

Schema.LogicalType.TIMESTAMP_MICROS

+

-

-

 

datetime2

Schema.LogicalType.TIMESTAMP_MICROS

+

+

+

 

datetimeoffset (2)

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

decimal

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

float

Schema.Type.FLOAT

+

+

-

 

image

Schema.Type.BYTES

+

-

-

 

int

Schema.Type.INT

+

+

-

 

money

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

-

-

 

nchar

Schema.Type.STRING

+

-

-

 

ntext

Schema.Type.STRING

+

-

-

 

numeric

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

nvarchar

Schema.Type.STRING

+

-

-

 

nvarchar(max)

Schema.Type.STRING

+

-

-

 

real

Schema.Type.FLOAT

+

+

-

 

smalldatetime

Schema.LogicalType.TIMESTAMP_MICROS

+

-

-

 

smallint

Schema.Type.INT

+

+

-

 

smallmoney

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

-

-

 

text

Schema.Type.STRING

+

+

-

 

time

Schema.LogicalType.TIME_MICROS

+

+

+

 

timestamp

Schema.Type.BYTES

+

-

-

 

tinyint

Schema.Type.INT

+

+

-

 

udt

Schema.Type.BYTES

+

-

-

 

uniqueidentifier

Schema.Type.STRING

+

-

-

 

varbinary

Schema.Type.BYTES

+

+

-

 

varbinary(max)

Schema.Type.BYTES

+

+

-

 

varchar

Schema.Type.STRING

+

+

-

 

varchar(max)

Schema.Type.STRING

+

+

+

 

xml

Schema.Type.STRING

+

-

-

 

sqlvariant

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

geometry

Schema.Type.BYTES

+

-

-

 

geography

Schema.Type.BYTES

+

-

-

 

DB2 db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

SMALLINT

Schema.Type.INT

+

+

-

 

INTEGER

Schema.Type.INT

+

+

-

 

BIGINT

Schema.Type.LONG

+

+

-

 

DECIMAL(p,s) or NUMERIC(p,s)

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

DECFLOAT

Schema.Type.DOUBLE

+

+

-

 

REAL

Schema.Type.FLOAT

+

+

-

 

DOUBLE

Schema.Type.DOUBLE

+

+

-

 

CHAR

Schema.Type.STRING

+

+

-

 

VARCHAR

Schema.Type.STRING

+

+

+

 

CHAR FOR BIT DATA

Schema.Type.BYTES

+

+

-

 

VARCHAR FOR BIT DATA

Schema.Type.BYTES

+

+

-

 

BINARY

Schema.Type.BYTES

+

+

-

 

VARBINARY

Schema.Type.BYTES

+

+

-

 

GRAPHIC

Schema.Type.STRING

+

+

-

 

VARGRAPHIC

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

CLOB

Schema.Type.STRING

+

+

-

 

BLOB

Schema.Type.BYTES

+

+

-

 

DBCLOB

 

?

-

-

Offcial doc: No exact equivalent. Use java.sql.Clob.

ROWID

Schema.Type.STRING

+

-

-

 

XML

 

-

-

-

 

DATE

Schema.LogicalType.DATE

+

+

+

 

TIME

Schema.LogicalType.TIME_MICROS

+

+

+

 

TIMESTAMP

Schema.LogicalType.TIMESTAMP_MICROS

+

+

+

 

Netezza db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

BOOLEAN

Schema.Type.BOOLEAN

+

+

-

 

BYTEINT

Schema.Type.INT

+

-

-

 

CHAR

Schema.Type.STRING

+

+

-

 

DATE

Schema.LogicalType.DATE

+

+

+

 

DECIMAL

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

DOUBLE PRECISION

Schema.Type.DOUBLE

+

-

-

 

FLOAT

Schema.Type.FLOAT

+

+

-

 

INTEGER

Schema.Type.INT

+

-

-

 

TINYINT

Schema.Type.INT

+

-

-

 

SMALLINT

Schema.Type.INT

+

+

-

 

BIGINT

Schema.Type.LONG

+

+

-

 

NCHAR

Schema.Type.STRING

+

-

-

 

NVARCHAR

Schema.Type.STRING

+

-

-

 

NUMERIC

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

REAL

Schema.Type.FLOAT

+

+

-

 

TIME

Schema.LogicalType.TIME_MICROS

+

+

+

 

TIMETZ

Schema.LogicalType.TIME_MICROS

+

-

-

 

TIME_WITH_TIME_ZONE

Schema.LogicalType.TIME_MICROS

+

-

-

 

TIMESTAMP

Schema.LogicalType.TIMESTAMP_MICROS

+

+

+

 

VARCHAR

Schema.Type.STRING

+

+

+

 

Oracle db plugin

Data type

CDAP mapped type

Support

Source test

Sink test

Comment

VARCHAR2

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

NVARCHAR2

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

VARCHAR

Schema.Type.STRING

+

+

+

 

NUMBER

Schema.Type.DOUBLE/Schema.Type.LONG/Schema.Type.INT

+

+

-

 

FLOAT

Schema.Type.FLOAT

+

-

-

 

LONG

Schema.Type.LONG

+

-

-

 

DATE

Schema.LogicalType.DATE

+

+

+

 

BINARY_FLOAT

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

BINARY_DOUBLE

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

TIMESTAMP

Schema.LogicalType.TIMESTAMP_MICROS

+

+

-

 

TIMESTAMP WITH TIME ZONE

Schema.LogicalType.TIMESTAMP_MICROS

+

-

-

 

TIMESTAMP WITH LOCAL TIME ZONE

Schema.LogicalType.TIMESTAMP_MICROS

+

-

-

 

INTERVAL YEAR TO MONTH

Schema.Type.STRING

+

+

-

 

INTERVAL DAY TO SECOND

Schema.Type.STRING

+

+

-

 

RAW

Schema.Type.BYTES

+

+

-

 

LONG RAW

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

ROWID

Schema.Type.STRING

+

-

-

 

UROWID

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

CHAR

Schema.Type.STRING

+

+

-

 

NCHAR

Schema.Type.STRING

+

-

-

 

CLOB

Schema.Type.STRING

+

+

-

 

NCLOB

Schema.Type.STRING

+

-

-

 

BLOB

Schema.Type.BYTES

+

+

-

 

BFILE

Schema.Type.STRING

*

-

-

No such type in java.sql.Types, mapping to String by default

Release Notes

None

Activity

Show:
Oksana Fedorynenko
June 24, 2019, 11:57 AM

Submitted PR with general mapping update to use decimal logical type, which is related to all specific db plugins:

https://github.com/data-integrations/database-plugins/pull/43

Vlad Hlinskiy
July 22, 2019, 10:38 AM

Submitted PR with reference doc updates: https://github.com/data-integrations/database-plugins/pull/51

Fixed

Assignee

Vlad Hlinskiy

Reporter

Maksym Lozbin

Labels

None

Docs Impact

None

UX Impact

None

Components

Fix versions

Affects versions

Priority

Major
Configure