CONNECT Create Table Options

Create Table statements for “CONNECT” tables are standard MariaDB create statements specifying engine=CONNECT. There are a few additional table and column options specific to CONNECT.

Table Options

Table OptionTypeDescription
AVG_ROW_LENGTHIntegerCan be specified to help CONNECT estimate the size of a variable record table length.
BLOCK_SIZEIntegerThe number of rows each block of a FIX, BIN, DBF, or VEC table contains. For an ODBC table this is the RowSet size option. For a JDBC table this is the fetch size.
CATFUNCStringThe catalog function used by a catalog table.
COLISTStringThe column list of OCCUR tables or $project of MONGO tables.
COMPRESSNumber1 or 2 if the data file is g-zip compressed. Defaults to 0. Before CONNECT 1.05.0001, this was boolean, and true if the data file is compressed.
CONNECTIONStringSpecifies the connection of an ODBC, JDBC or MYSQL table.
DATA_CHARSETStringThe character set used in the external file or data source.
DBNAMEStringThe target database for ODBC, JDBC, MYSQL, catalog, and PROXY based tables. The database concept is sometimes known as a schema.
ENGINEStringMust be specfied as CONNECT.
ENDINGIntegerEnd of line length. Defaults to 1 for Unix/Linux and 2 for Windows.
FILE_NAMEStringThe file (path) name for all table types based on files. Can be absolute or relative to the current data directory. If not specified, this is an Inward table and a default value is used.
FILTERStringTo filter an external table. Currently MONGO tables only.
HEADERIntegerApplies to CSV, VEC, and HTML files. Its meaning depends on the table type.
HTTPStringThe HTTP of the client of REST queries. From Connect 1.06.0010.
HUGEBooleanTo specify that a table file can be larger than 2GB. For a MYSQL table, prevents the result set from being stored in memory.
LRECLIntegerThe file record size (often calculated by default).
MAPPEDBooleanSpecifies whether file mapping is used to handle the table file.
MODULEStringThe (path) name of the DLL or shared lib implementing the access of a non-standard (OEM) table type.
MULTIPLEIntegerUsed to specify multiple file tables.
OPTION_LISTStringUsed to specify all other options not yet directly defined.
QCHARStringSpecifies the character used for quoting some fields of a CSV table or the identifiers of an ODBC/JDBC tables.
QUOTEDIntegerThe level of quoting used in CSV table files.
READONLYBooleanTrue if the data file must not be modified or erased.
SEP_CHARStringSpecifies the field separator character of a CSV or XCOL table. Also, used to specify the Jpath separator for JSON tables.
SEPINDEXBooleanWhen true, indexes are saved in separate files.
SPLITBooleanTrue for a VEC table when all columns are in separate files.
SRCDEFStringThe source definition of a table retrieved via ODBC, JDBC or the MySQL API or used by a PIVOT table.
SUBTYPEStringThe subtype of an OEM table type.
TABLE_LISTStringThe comma separated list of TBL table sub-tables.
TABLE_TYPEStringThe external table type: DOS, FIX, BIN, CSV, FMT, XML, JSON, INI, DBF, VEC, ODBC, JDBC, MYSQL, TBL, PROXY, XCOL, OCCUR, PIVOT, ZIP, VIR, DIR, WMI, MAC, and OEM. Defaults to DOS, MYSQL, or PROXY depending on what options are used.
TABNAMEStringThe target table or node for ODBC, JDBC, MYSQL, PROXY, or catalog tables; or the top node name for XML tables.
URIStringThe URI of a REST request.. From Connect 1.06.0010.
XFILE_NAMEStringThe file (path) base name for table index files. Can be absolute or relative to the data directory. Defaults to the file name.
ZIPPEDBooleanTrue if the table file(s) is/are zipped in one or several zip files.

All integers in the above table are unsigned big integers.

Because CONNECT handles many table types; many table type specific options are not in the above list and must be entered using the OPTION_LIST option. The syntax to use is:

... option_list='opname1=opvalue1,opname2=opvalue2...'

Be aware that until Connect 1.5.5, no blanks should be inserted before or after the '=' and ',' characters. The option name is all that is between the start of the string or the last ',' character and the next '=' character, and the option value is all that is between this '=' character and the next ',' or end of string. For instance:

option_list='name=TABLE,coltype=HTML,attribute=border=1;cellpadding=5,headattr=bgcolor=yellow';

This defines four options, 'name', 'coltype', 'attribute', and 'headattr'; with values 'TABLE', 'HTML', 'border=1;cellpadding=5', and 'bgcolor=yellow', respectively. The only restriction is that values cannot contain commas, but they can contain equal signs.

Column Options

Column OptionTypeDescription
DATE_FORMATStringThe format indicating how a date is stored in the file.
DISTRIBEnum“scattered”, “clustered”, “sorted” (ascending).
FIELD_FORMATStringThe column format for some table types.
FIELD_LENGTHIntegerSet the internal field length for DATE columns.
FLAGIntegerAn integer value whose meaning depends on the table type.
JPATHStringThe Json path of JSON table columns.
MAX_DISTIntegerMaximum number of distinct values in this column.
SPECIALStringThe name of the SPECIAL column that set this column value.
XPATHStringThe XML path of XML table columns.
  • The MAX_DIST and DISTRIB column options are used for block indexing.
  • All integers in the above table are unsigned big integers.
  • JPATH and XPATH were added to make CREATE TABLE statements more readable, but they do the same thing as FIELD_FORMAT and any of them can be used with the same result.

Index Options

Index OptionTypeDescription
DYNAMBooleanSet the index as “dynamic”.
MAPPEDBooleanUse index file mapping.

Note 1: Creating a CONNECT table based on file does not erase or create the file if the file name is specified in the CREATE TABLE statement (“outward” table). If the file does not exist, it will be populated by subsequent INSERT or LOAD commands or by the “AS select statement” of the CREATE TABLE command. Unlike the CSV engine, CONNECT easily permits the creation of tables based on already existing files, for instance files made by other applications. However, if the file name is not specified, a file with a name defaulting to tablename.tabletype will be created in the data directory (“inward” table).

Note 2: Dropping a CONNECT table is done with a standard DROP statement. For outward tables, this drops only the CONNECT table definition but does not erase the corresponding data file and index files. Use DELETE or TRUNCATE to do so. This is contrary to data and index files of inward tables are erased on DROP like for other MariaDB engines.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.