Create Empty Collection

The Simba MongoDB JDBC Driver with SQL Connector supports creating an empty collection in MongoDB using the CREATE TABLE statement.

Important:

Important:

  • This feature only supports creating base tables and not virtual tables.
  • The CREATE TABLE statement cannot be executed in a transaction under the following circumstances: 
    • You are using MongoDB 4.0 or later
    • And EnableTransaction=true
    • And IgnoreTransactions=false
  • In the table element list of the CREATE TABLE statement, the _id column must be specified and other columns cannot be specified in the same table element list. Constraints are also not supported.

The metadata of the corresponding base table is added to the temporary schema definition of the connection used to execute the CREATE TABLE statement.

For example, in order to create an empty collection with the name Customer, execute the following statement:

CREATE TABLE Customer (_id VARCHAR)

Note:

By choosing VARCHAR as the SQL type for _id, the MongoDB native type is chosen as ObjectId.

In the same connection, to insert data into the created empty collection, enable the UseJsonColumn property and insert data via the exposed DocumentAsJson column.

Note:

  • The newly created empty collection shows up only in the connection used to execute the CREATE TABLE statement and the next time when sampling is performed.
  • If the UseJsonColumn property is enabled, only the _id column and the DocumentAsJson column appear in the corresponding base table. Inserting data into the DocumentAsJson column will not update the schema of the corresponding base table until the next time sampling is performed.
  • In cases where the connection is set up to load metadata from MongoDB or from a local schema file, sampling is only performed when using the schema editor. To find out when sampling is performed, see Schema Definitions.