本文从Teradata和KADB的数据类型、函数、DDL语句三个方面说明列举两个产品的兼容情况,以此分析从Teradata迁移至KADB的可行性。
- Teradata数据类型兼容
注:不兼容的数据类型标注为:X
数据类型 | 是否ANSI扩展 | KADB |
Array | ||
ARRAY/VARRAY | 是 | [] |
Byte | ||
BLOB[(n)] | 是 | text |
BYTE[(n)] | bytea | |
VARBYTE[(n)] | bit varying | |
Numeric | ||
BIGINT | 是 | bigint |
BYTEINT | smallint | |
DATE | date | |
DECIMAL [(n[,m])] | 是 | decimal [ (p, s) ] |
DOUBLE PRECISION | 是 | double precision |
FLOAT | 是 | numeric |
INTEGER | 是 | integer |
NUMBER(n[,m]) | numeric(n[,m] | |
NUMBER[(*[,m])] | numeric(n[,m] | |
NUMERIC [(n[,m])] | 是 | numeric(n[,m] |
REAL | 是 | real |
SMALLINT | 是 | smallint |
DateTime | ||
DATE | 是 | date |
TIME [(n)] | 是 | time [ (p) ] |
TIMESTAMP [(n)] | timestamp [ (p) ] | |
Interval | ||
INTERVAL | 是 | interval [ (p) ] |
INTERVAL DAY [(n)] | 是 | INTERVAL DAY [(n)] |
INTERVAL DAY [(n)] TO HOUR | 是 | INTERVAL DAY [(n)] TO HOUR |
INTERVAL DAY [(n)] TO MINUTE | 是 | INTERVAL DAY [(n)] TO MINUTE |
INTERVAL DAY [(n)] TO SECOND | 是 | INTERVAL DAY [(n)] TO SECOND |
INTERVAL HOUR [(n)] | INTERVAL HOUR [(n)] | |
INTERVAL HOUR [(n)] TO MINUTE | 是 | INTERVAL HOUR [(n)] TO MINUTE |
INTERVAL HOUR [(n)] TO SECOND | 是 | INTERVAL HOUR [(n)] TO SECOND |
INTERVAL MINUTE [(n)] | 是 | INTERVAL MINUTE [(n)] |
INTERVAL MINUTE [(n)] TO SECOND [(m)] | 是 | INTERVAL MINUTE [(n)] TO SECOND [(m)] |
INTERVAL MONTH X | 是 | INTERVAL MONTH X |
INTERVAL SECOND [(n,[m])] | 是 | INTERVAL SECOND [(n,[m])] |
INTERVAL YEAR [(n)] | 是 | INTERVAL YEAR [(n)] |
INTERVAL YEAR [(n)] TO MONTH | INTERVAL YEAR [(n)] TO MONTH | |
Character | ||
CHAR[(n)] | 是 | char [ (n) ] |
CHARACTER(n) CHARACTER SET GRAPHIC | 是 | char |
CLOB | 是 | text |
CHAR VARYING(n) | 是 | varchar [ (n) ] |
LONG VARCHAR | 是 | text |
LONG VARCHAR CHARACTER SET GRAPHIC | 是 | text |
VARCHAR(n) | 是 | varchar |
VARCHAR(n) CHARACTER SET GRAPHIC | varchar | |
Period | ||
PERIOD(DATE) | 是 | X |
PERIOD(TIME [(n)]) | 是 | X |
PERIOD(TIMESTAMP [(n)]) | 是 | X |
UDT | ||
udt_name | 是 | type |
- Teradata函数兼容
注:不兼容的数据类型标注为:X
函数 | KADB |
Aggregate Functions | |
AVG | AVG |
COVAR_SAMP | COVAR_SAMP |
COVAR_POP | COVAR_POP |
UNPIVOT | X |
SUM | SUM |
STDDEV_SAMP | stddev |
STDDEV_POP | STDDEV_POP |
SKEW | X |
REGR_SYY | REGR_SYY |
REGR_SXY | REGR_SXY |
REGR_SXX | REGR_SXX |
REGR_SLOPE | REGR_SLOPE |
REGR_R2 | REGR_R2 |
REGR_INTERCEPT | REGR_INTERCEPT |
REGR_COUNT | REGR_COUNT |
REGR_AVGY | REGR_AVGY |
REGR_AVGX | REGR_AVGX |
PIVOT | crosstab |
MIN | MIN |
MAX | MAX |
KURTOSIS | X |
GROUPING | GROUPING |
COVAR_SAMP | COVAR_SAMP |
COVAR_POP | COVAR_POP |
COUNT | COUNT |
CORR | CORR |
Attribute Functions | |
BIT_LENGTH | BIT_LENGTH |
BYTES | X |
CHARACTER LENGTH | CHARACTER LENGTH |
DEFAULT | X |
FORMAT | FORMAT |
OCTET_LENGTH | OCTET_LENGTH |
TITLE | X |
TYPE | X |
Bit/Byte Manipulation Functions | |
BITAND | & |
BITNOT | ~ |
BITOR | | |
BITXOR | # |
COUNTSET | bit_length |
GETBIT | get_bit |
ROTATELEFT | X |
ROTATERIGHT | X |
SETBIT | SET_BIT |
SHIFTLEFT | X |
SHIFTRIGHT | X |
SUBBITSTR | substring |
TO_BYTE | X |
Built-In Functions | |
ACCOUNT | current_user |
CURRENT_DATE/CURDATE | CURRENT_DATE |
CURRENT_ROLE | CURRENT_ROLE |
CURRENT_TIME/CURTIME | CURRENT_TIME |
CURRENT_TIMESTAMP | CURRENT_TIMESTAMP |
CURRENT_USER | CURRENT_USER |
DATE | DATE |
NOW | NOW |
SESSION | SESSION |
TEMPORAL_DATE | X |
TEMPORAL_TIMESTAMP | X |
TIME | TIME |
USER | USER |
Comparison Operators and the Function | |
DECODE | DECODE |
GREATEST | GREATEST |
LEAST | LEAST |
Null-Handling Functions | |
NVL | NVL |
NVL2 | X |
Window Aggregate Functions | |
CSUM | X |
CUME_DIST | CUME_DIST |
DENSE_RANK | DENSE_RANK |
FIRST_VALUE / LAST_VALUE | FIRST_VALUE / LAST_VALUE |
LAG/LEAD | LAG/LEAD |
MAVG | X |
MDIFF | X |
MEDIAN | X |
MLINREG | X |
MSUM | SUM |
PERCENT_RANK | PERCENT_RANK |
PERCENTILE_CONT / PERCENTILE_DISC | PERCENTILE_CONT/ PERCENTILE_DISC |
QUANTILE | X |
RANK (ANSI) | RANK (ANSI) |
ROW_NUMBER | ROW_NUMBER |
String Functions | |
ASCII | ASCII |
CHAR2HEXINT | to_hex |
CONCAT | CONCAT |
CSV | X |
CSVLD | X |
EDITDISTANCE | X |
INDEX | position |
INITCAP | INITCAP |
INSTR | INSTR |
LEFT | LEFT |
LENGTH | LENGTH |
LOCATE | position |
LOWER | LOWER |
LPAD | LPAD |
LTRIM | LTRIM |
NGRAM | X |
NVP | X |
OREPLACE | X |
OTRANSLATE | X |
POSITION | POSITION |
REVERSE | REVERSE |
RIGHT | RIGHT |
RPAD | RPAD |
RTRIM | RTRIM |
SOUNDEX | SOUNDEX |
STRING_CS | X |
STRTOK | X |
STRTOK_SPLIT_TO_TABLE | X |
SUBSTRING/SUBSTR | SUBSTRING/SUBSTR |
TRANSLATE | TRANSLATE |
TRANSLATE_CHK | X |
TRIM | TRIM |
UPPER/UCASE | UPPER/UCASE |
VARGRAPHIC | X |