Creating Database Table from Metadata and Database Connection
As the last option, you can also create a database table on the basis of metadata (both internal and external).
When you select the item from each of the two context menus (called out from the Outline pane and/or Graph Editor), a wizard with a SQL query that can create database table opens.
 |
Figure 32.35. Creating Database Table from Metadata and Database Connection
You can edit the contents of this window if you want.
When you select some connection to a database. For more details see Database Connections. Such database table will be created.
![[Note]](figures/note.png) | Note |
|---|---|
If multiple SQL types are listed, actual syntax depends on particular metadata (size for fixed-length field, length, scale, etc.). |
Table 32.15. CloverETL-to-SQL Data Types Transformation Table (Part I)
| DB type | DB2 & Derby | Firebird | Hive | Informix | MSAccess |
|---|---|---|---|---|---|
| Clover type | |||||
| boolean | SMALLINT | CHAR(1) | BOOLEAN | BOOLEAN | BIT |
| byte | VARCHAR(80) FOR BIT DATA | CHAR(80) | BINARY[a] | BYTE | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | CHAR(n) | BINARY(n) | |||
| cbyte | VARCHAR(80) FOR BIT DATA | CHAR(80) | BINARY[a] | BYTE | VARBINARY(80) |
| CHAR(n) FOR BIT DATA | CHAR(n) | BINARY(n) | |||
| date | TIMESTAMP | TIMESTAMP | TIMESTAMP[a] | DATETIME YEAR TO SECOND | DATETIME |
| DATE | DATE | DATE | |||
| TIME | DATETIME HOUR TO SECOND | TIME | |||
| decimal | DECIMAL | DECIMAL | DECIMAL[b] | DECIMAL | DECIMAL |
| DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | ||
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | ||
| integer | INTEGER | INTEGER | INT | INTEGER | INT |
| long | BIGINT | BIGINT | BIGINT | INT8 | BIGINT |
| number | DOUBLE | FLOAT | DOUBLE | FLOAT | FLOAT |
| string | VARCHAR(80) | VARCHAR(80) | STRING | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | ||
[a] Available from version 0.8.0 of Hive [b] Available from version 0.11.0 of Hive | |||||
Table 32.16. CloverETL-to-SQL Data Types Transformation Table (Part II)
| DB type | MSSQL | MSSQL | MySQL | Oracle | Pervasive |
|---|---|---|---|---|---|
| Clover type | 2000-2005 | 2008 | |||
| boolean | BIT | BIT | TINYINT(1) | SMALLINT | BIT |
| byte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) | |
| cbyte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) | |
| date | DATETIME | DATETIME | DATETIME | TIMESTAMP | TIMESTAMP |
| DATE | YEAR | DATE | DATE | ||
| TIME | DATE | TIME | |||
| TIME | |||||
| decimal | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
| DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INT | INT | INT | INTEGER | INTEGER |
| long | BIGINT | BIGINT | BIGINT | NUMBER(11,0) | BIGINT |
| number | FLOAT | FLOAT | DOUBLE | FLOAT | DOUBLE |
| string | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR2(80) | VARCHAR2(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
Table 32.17. CloverETL-to-SQL Data Types Transformation Table (Part III)
| DB type | PostgreSQL | SQLite | Sybase | Generic |
|---|---|---|---|---|
| Clover type | ||||
| boolean | BOOLEAN | BOOLEAN | BIT | BOOLEAN |
| byte | BYTEA | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| VARBINARY(80) | BINARY(n) | BINARY(n) | ||
| cbyte | BYTEA | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| BINARY(n) | BINARY(n) | BINARY(n) | ||
| date | TIMESTAMP | TIMESTAMP | DATETIME | TIMESTAMP |
| DATE | DATE | DATE | DATE | |
| TIME | TIME | TIME | TIME | |
| decimal | NUMERIC | DECIMAL | DECIMAL | DECIMAL |
| NUMERIC(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| NUMERIC(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INTEGER | INTEGER | INT | INTEGER |
| long | BIGINT | BIGINT | BIGINT | BIGINT |
| number | REAL | NUMERIC | FLOAT | FLOAT |
| string | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) | VARCHAR(80) |
| CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
Revised: 2013-02-18