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 Create database table item from each of the two context menus (called out from the Outline pane and/or Graph Editor), a wizard opens with an SQL query that can create database table.
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.
If multiple SQL types are listed, actual syntax depends on particular metadata (size for fixed-length field, length, scale, etc.).
| DB TYPE DATA SHAPER TYPE | DB2 | HIVE | INFORMIX | MSACCESS |
|---|---|---|---|---|
| boolean | SMALLINT | BOOLEAN | BOOLEAN | BIT |
| byte | VARCHAR(80) FOR BIT DATA | BINARY ete | BYTE | VARBINARY(80) |
| byte | CHAR(n) FOR BIT DATA | BINARY(n) | ||
| cbyte | VARCHAR(80) FOR BIT DATA | BINARY ete | BYTE | VARBINARY(80) |
| cbyte | CHAR(n) FOR BIT DATA | BINARY(n) | ||
| date | TIMESTAMP | TIMESTAMP rs] | DATETIME YEAR TO SECOND | DATETIME |
| date | DATE | DATE | DATE | |
| date | TIME | DATETIME HOUR TO SECOND | TIME | |
| decimal | DECIMAL | DECIMAL ter | DECIMAL | DECIMAL |
| decimal | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | |
| decimal | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | |
| integer | INTEGER | INT | INTEGER | INT |
| long | BIGINT | BIGINT | INT8 | BIGINT |
| number | DOUBLE | DOUBLE | FLOAT | FLOAT |
| string | VARCHAR(80) | STRING | VARCHAR(80) | VARCHAR(80) |
| string | CHAR(n) | CHAR(n) | CHAR(n) |
1 Available from version 0.8.0 of Hive
2 Available from version 0.11.0 of Hive
| DB TYPE DATA SHAPER TYPE | MSSQL 2000-2005 | MSSQL 2008 | MYSQL | ORACLE | PERVASIVE |
|---|---|---|---|---|---|
| boolean | BIT | BIT | TINYINT(1) | SMALLINT | BIT |
| byte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| byte | BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) |
| cbyte | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) | RAW(80) | LONGVARBINARY(80) |
| cbyte | BINARY(n) | BINARY(n) | BINARY(n) | RAW(n) | BINARY(n) |
| date | DATETIME | DATETIME | DATETIME | TIMESTAMP | TIMESTAMP |
| date | DATE | YEAR | DATE | DATE | |
| date | TIME | DATE | TIME | ||
| date | TIME | ||||
| decimal | DECIMAL | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
| decimal | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) |
| decimal | 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) |
| string | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) |
| DB TYPE DATA SHAPER TYPE | POSTGRESQL | SNOWFLAKE | SQLITE | SYBASE | GENERIC |
|---|---|---|---|---|---|
| boolean | BOOLEAN | BOOLEAN | BOOLEAN | BIT | BOOLEAN |
| byte | BYTEA | VARBINARY | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| byte | VARBINARY(80) | BINARY(n) | BINARY(n) | ||
| cbyte | BYTEA | VARBINARY | VARBINARY(80) | VARBINARY(80) | VARBINARY(80) |
| cbyte | BINARY(n) | BINARY(n) | BINARY(n) | ||
| date | TIMESTAMP | TIMESTAMP | TIMESTAMP | DATETIME | TIMESTAMP |
| date | DATE | DATE | DATE | DATE | DATE |
| date | TIME | TIME | TIME | TIME | TIME |
| decimal | NUMERIC | DECIMAL | DECIMAL | DECIMAL | DECIMAL |
| decimal | NUMERIC(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) | DECIMAL(p) |
| decimal | NUMERIC(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) |
| integer | INTEGER | DECIMAL(10,0) | INTEGER | INT | INTEGER |
| long | BIGINT | DECIMAL(19,0) | BIGINT | BIGINT | BIGINT |
| number | REAL | FLOAT | NUMERIC | FLOAT | FLOAT |
| string | VARCHAR(80) | VARCHAR | VARCHAR(80) | VARCHAR2(80) | VARCHAR2(80) |
| string | CHAR(n) | VARCHAR | CHAR(n) | CHAR(n) | CHAR(n) |
Updated 6 months ago