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.

460

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
DB2HIVEINFORMIXMSACCESS
booleanSMALLINTBOOLEANBOOLEANBIT
byteVARCHAR(80) FOR BIT DATABINARY eteBYTEVARBINARY(80)
byteCHAR(n) FOR BIT DATABINARY(n)
cbyteVARCHAR(80) FOR BIT DATABINARY eteBYTEVARBINARY(80)
cbyteCHAR(n) FOR BIT DATABINARY(n)
dateTIMESTAMPTIMESTAMP rs]DATETIME YEAR TO SECONDDATETIME
dateDATEDATEDATE
dateTIMEDATETIME HOUR TO SECONDTIME
decimalDECIMALDECIMAL terDECIMALDECIMAL
decimalDECIMAL(p)DECIMAL(p)DECIMAL(p)
decimalDECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
integerINTEGERINTINTEGERINT
longBIGINTBIGINTINT8BIGINT
numberDOUBLEDOUBLEFLOATFLOAT
stringVARCHAR(80)STRINGVARCHAR(80)VARCHAR(80)
stringCHAR(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
MYSQLORACLEPERVASIVE
booleanBITBITTINYINT(1)SMALLINTBIT
byteVARBINARY(80)VARBINARY(80)VARBINARY(80)RAW(80)LONGVARBINARY(80)
byteBINARY(n)BINARY(n)BINARY(n)RAW(n)BINARY(n)
cbyteVARBINARY(80)VARBINARY(80)VARBINARY(80)RAW(80)LONGVARBINARY(80)
cbyteBINARY(n)BINARY(n)BINARY(n)RAW(n)BINARY(n)
dateDATETIMEDATETIMEDATETIMETIMESTAMPTIMESTAMP
dateDATEYEARDATEDATE
dateTIMEDATETIME
dateTIME
decimalDECIMALDECIMALDECIMALDECIMALDECIMAL
decimalDECIMAL(p)DECIMAL(p)DECIMAL(p)DECIMAL(p)DECIMAL(p)
decimalDECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
integerINTINTINTINTEGERINTEGER
longBIGINTBIGINTBIGINTNUMBER(11,0)BIGINT
numberFLOATFLOATDOUBLEFLOATDOUBLE
stringVARCHAR(80)VARCHAR(80)VARCHAR(80)VARCHAR2(80)VARCHAR2(80)
stringCHAR(n)CHAR(n)CHAR(n)CHAR(n)CHAR(n)
DB TYPE
DATA SHAPER TYPE
POSTGRESQLSNOWFLAKESQLITESYBASEGENERIC
booleanBOOLEANBOOLEANBOOLEANBITBOOLEAN
byteBYTEAVARBINARYVARBINARY(80)VARBINARY(80)VARBINARY(80)
byteVARBINARY(80)BINARY(n)BINARY(n)
cbyteBYTEAVARBINARYVARBINARY(80)VARBINARY(80)VARBINARY(80)
cbyteBINARY(n)BINARY(n)BINARY(n)
dateTIMESTAMPTIMESTAMPTIMESTAMPDATETIMETIMESTAMP
dateDATEDATEDATEDATEDATE
dateTIMETIMETIMETIMETIME
decimalNUMERICDECIMALDECIMALDECIMALDECIMAL
decimalNUMERIC(p)DECIMAL(p)DECIMAL(p)DECIMAL(p)DECIMAL(p)
decimalNUMERIC(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)DECIMAL(p,s)
integerINTEGERDECIMAL(10,0)INTEGERINTINTEGER
longBIGINTDECIMAL(19,0)BIGINTBIGINTBIGINT
numberREALFLOATNUMERICFLOATFLOAT
stringVARCHAR(80)VARCHARVARCHAR(80)VARCHAR2(80)VARCHAR2(80)
stringCHAR(n)VARCHARCHAR(n)CHAR(n)CHAR(n)