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.

Note:

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)