/ /

Atlas Data Federation Schema Commands

In Atlas Data Federation, you manage the schema that the SQL Interface uses through three commands: sqlGenerateSchema, sqlGetSchema, and sqlSetSchema. You run these commands from mongosh against a federated database instance, or you perform the equivalent actions in the Atlas UI. This page documents those commands and their UI equivalents.

Atlas Data Federation samples documents from your collections to generate the initial schema. For background on schema management and the other supported deployment types, see Schema Management.

Note

The sqlGenerateSchema, sqlGetSchema, and sqlSetSchema commands and their Atlas UI equivalents apply only to the Atlas Data Federation deployment type. They don't apply to self-managed Enterprise Advanced (EA) deployments, which use the MongoDB SQL Schema Builder CLI, or to queries against a standard Atlas cluster that doesn't use Atlas Data Federation.

sqlGenerateSchema

The sqlGenerateSchema command generates a SQL Interface schema for the specified collections or views. Atlas Data Federation samples documents from each namespace to derive the schema.

Syntax

When you use the sampleNamespaces parameter, you must run the command against the admin database.

use admin
db.runCommand({
   sqlGenerateSchema: 1,
   sampleNamespaces: [<namespace>],
   sampleSize: <int>,
   setSchemas: true|false
})

Parameters

Parameter	Type	Description	Necessity
`sampleNamespaces`	array of strings	Specifies the comma-separated list of namespaces for which to generate schemas. A namespace includes the database name, a dot (`.`) separator, and the collection or view name (that is, `<database>.<collection>\|<view>`). To generate schemas for all the collections in a database, specify `` instead of the collection or view name (that is, `<database>.`). If omitted, Atlas Data Federation generates schemas for all collections and views in the current database.	Optional
`sampleSize`	integer	Specifies the number of documents to use as a sample to create the schema. If omitted, defaults to `1000`.	Optional
`setSchemas`	boolean	Specifies whether to store the generated schema for the collection or view. Value can be one of the following: `true` to store the schema. If a schema already exists for the collection or view, Atlas Data Federation overwrites the existing schema. `false` to not store the schema. If omitted, defaults to `false`.	Optional

Output

The command returns the following output if it succeeds:

{
  "ok" : 1,
  "schemas" : [
    {
      "databaseName" : "<database-name>",
      "namespaces" : [
        {
          "name" : "<collection-name>",
          "schema" : {
            "version" : NumberLong(1),
            "jsonSchema" : { ... }
          }
        }
      ]
    },
    ...
  ]
}

The schemas object contains the following fields.

Parameter	Type	Description
`databaseName`	string	Name of the database.
`namespaces`	array of objects	Name and generated schema of each collection or view.
`namespaces.name`	string	Name of the collection or view.
`namespaces[n].schema`	document	Schema of the collection or view.
`namespaces[n].schema.version`	integer	Format version of the schema. Value is always 1.
`namespaces[n].schema.jsonSchema`	document	JSON schema of the collection or view. The JSON schema can contain the following fields: `bsonType` `properties` `items` `additionalProperties` `required` To learn more about these fields, see JSON Schema Keywords.

If you set the schema for the collection or view with the setSchemas option, you can verify that the command succeeded by running the sqlGetSchema command. The sqlGetSchema command metadata.description field contains the following value:

"set using sqlGenerateSchema with setSchemas = true"

Errors

The command returns the following error if it fails:

"failedNamespaces": [
  {
    "namespace" : "<db.ns>",
    "error" : "no documents found in sample namespace"
  }
]

Atlas Data Federation returns this error if the specified namespaces do not exist in the storage configuration or are empty. Atlas Data Federation also returns this error if it could not set the schema for a given namespace.

sqlGetSchema

The sqlGetSchema command retrieves the schema stored for the specified collection or view.

Syntax

db.getSiblingDB("<dbName>").runCommand({
   sqlGetSchema: "<collection-name>|<view-name>"
})

Parameters

Parameter	Type	Description	Necessity
`<collection-name>`	string	Name of the collection for which to retrieve the schema. Provide either the collection name or the view name.	Conditional
`<view-name>`	string	Name of the view for which to retrieve the schema. Provide either the view name or the collection name.	Conditional

Output

The command returns the following output if the collection or view does not have a schema:

{ "ok" : 1, "metadata" : { }, "schema" : { } }

The command returns output similar to the following if the collection or view has a schema:

{
  "ok": 1,
  "metadata": {
    "description": "<description>"
  },
  "schema": {
    "version": NumberLong(1),
    "jsonSchema": { ... }
  }
}

The metadata.description field describes how the schema was set for the collection. Value can be one of the following:

generated automatically by Atlas Data Federation: Indicates that the schema was automatically generated by Atlas Data Federation.

set using sqlGenerateSchema with setSchemas = true: Indicates that the schema was set by the sqlGenerateSchema command because the setSchema option was set to true.

set using sqlSetSchema: Indicates that the schema was set using the sqlSetSchema command.

The schema document contains the following fields:

Parameter	Type	Description
`schema.version`	integer	Format version of the schema. Value is always 1.
`schema.jsonSchema`	document	JSON schema of the collection or view. The JSON schema can contain the following fields: `bsonType` `properties` `items` To learn more about these fields, see JSON Schema Keywords.

sqlSetSchema

The sqlSetSchema command sets or removes the schema for a collection or view. The command applies the schema you provide directly. The command does not validate the provided schema against the data in the collection.

Syntax

db.getSiblingDB("<dbName>").runCommand({
   sqlSetSchema: "<collection-name>|<view-name>",
   schema: {
      "version": 1,
      "jsonSchema": <jsonSchema>
   }
})

db.getSiblingDB("<dbName>").runCommand({
   sqlSetSchema: "<collection-name>|<view-name>",
   schema: {}
})

Parameters

Parameter	Type	Description	Necessity
`<collection-name>`	string	Name of the collection for which to set the schema. Provide either a collection name or a view name.	Conditional
`<view-name>`	string	Name of the view for which to set the schema. Provide either a view name or a collection name.	Conditional
`schema`	document	The format version of the schema and either: the JSON schema for the collection or view an empty document to remove the schema for the collection or view You can provide a single document or an array of documents in the `items` field. When you retrieve the schema, `items` shows the form that you used to set the schema.	Required

Output

The command returns the following output if it succeeds:

{ "ok" : 1 }

You can verify that the command succeeded by running the sqlGetSchema command. The metadata.description field in the response contains the following value:

"set using sqlSetSchema"

Examples

Consider a collection named egData in a database named sampleDB with the following documents:

{"a": {"b": {"c": [1, 2, 3]}}, "s": 1}
{"a": {"b": {"c": [4, 5, 6]}}, "s": 2}
{"a": {"b": [7, 8, 9]}, "s": 3}
{"a": {"b": {"c": []}}, "s": 4}
{"a": {"b": {"c": "hello"}}, "s": 5}
{"a": {"b": {"c": {"d": 1}}}, "s": 6}
{"a": {"b": {"c": null}}}
{"s": 7}

The following examples use the Atlas Data Federation schema commands to generate, retrieve, set, and remove the schema for the preceding collection.

Generate a Schema

The following command generates a schema for the collection named sampleDB.egData in the storage configuration. The command uses two randomly selected documents from the collection to create the schema because sampleSize is 2. The command does not set the schema for the collection because the setSchemas option is not specified and defaults to false.

use admin
db.runCommand({
   sqlGenerateSchema: 1,
   sampleNamespaces: ["sampleDB.egData"],
   sampleSize: 2
})

The previous command returns the following output. To learn more about the fields in the output, see Output.

{
  "ok" : 1,
  "schemas" : [
    {
      "databaseName" : "sampleDB",
      "namespaces" : [
        {
          "name" : "egData",
          "schema" : {
            "version" : NumberLong(1),
            "jsonSchema" : {
              "bsonType" : [ "object" ],
              "properties" : {
                "a" : {
                  "bsonType" : [ "object" ],
                  "properties" : {
                    "b" : {
                      "bsonType" : [ "object" ],
                      "properties" : {
                        "c" : {
                          "bsonType" : [ "array" ],
                          "items" : {
                            "bsonType" : [ "int" ]
                          }
                        }
                      }
                    }
                  }
                },
                "s" : {
                  "bsonType" : [ "int" ]
                }
              }
            }
          }
        }
      ]
    }
  ]
}

Generate and Set a Schema

The following command generates a schema for the collection named sampleDB.egData in the storage configuration. The command uses up to 1000 documents in the collection to create the schema because the sampleSize option is not specified and defaults to 1000. The command sets the generated schema as the schema to use for the collection because setSchemas is true.

use admin
db.runCommand({
   sqlGenerateSchema: 1,
   sampleNamespaces: ["sampleDB.egData"],
   setSchemas: true
})

Retrieve a Schema

The following command retrieves the schema stored for the egData collection:

db.getSiblingDB("sampleDB").runCommand({
   sqlGetSchema: "egData"
})

The previous command returns the following output. To learn more about the fields in the output, see Output.

{
  "ok" : 1,
  "metadata" : {
    "description" : "set using sqlGenerateSchema with setSchemas = true"
  },
  "schema" : {
    "version" : NumberLong(1),
    "jsonSchema" : {
      "bsonType" : [ "object" ],
      "properties" : {
        "a" : {
          "bsonType" : [ "object" ],
          "properties" : {
            "b" : {
              "bsonType" : [ "object", "array" ],
              "properties" : {
                "c" : {
                  "bsonType" : [ "array", "string", "object", "null" ],
                  "properties" : {
                    "d" : {
                      "bsonType" : [ "int" ]
                    }
                  },
                  "items" : {
                    "bsonType" : [ "int" ]
                  }
                }
              },
              "items" : {
                "bsonType" : [ "int" ]
              }
            }
          }
        },
        "s" : {
          "bsonType" : [ "int", "object" ]
        }
      }
    }
  }
}

Set a Schema

The following sqlSetSchema command sets the schema for the egData collection:

db.getSiblingDB("sampleDB").runCommand({
   sqlSetSchema : "egData",
   "schema" : {
      "version" : NumberLong(1),
      "jsonSchema" : {
         "bsonType" : [ "object" ],
         "properties" : {
            "a" : {
               "bsonType" : [ "object" ],
               "properties" : {
                  "b" : {
                     "bsonType" : [ "object", "array" ],
                     "properties" : {
                        "c" : {
                           "bsonType" : [ "array", "string", "object", "null" ],
                           "properties" : {
                              "d" : {
                                 "bsonType" : [ "int" ]
                              }
                           },
                           "items" : {
                              "bsonType" : [ "int" ]
                           }
                        }
                     },
                     "items" : {
                        "bsonType" : [ "int" ]
                     }
                  }
               }
            },
            "s" : {
               "bsonType" : [ "int", "object" ]
            }
         }
      }
   }
})

The previous command returns the following output:

{ "ok" : 1 }

Remove a Schema

The following sqlSetSchema command removes the schema for the egData collection by passing an empty schema document:

db.getSiblingDB("sampleDB").runCommand({
   sqlSetSchema: "egData",
   schema: {}
})

The previous command returns the following output:

{ "ok" : 1 }

Manage Schemas in the Atlas UI

You can perform the same schema actions in the Atlas UI from the Manage SQL Schemas page. The UI samples documents to generate schemas, the same as the sqlGenerateSchema command.

Create a Schema

When you create a quick start connection, Atlas Data Federation generates the schema automatically. To create a schema manually, use the following procedure.

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

Create a schema.

On a collection that has an empty schema:

Click the .
Click Generate new schema from sample, or provide your own JSON.
Click Save.

Regenerate a Schema

Regenerate a schema any time the shape of your underlying data changes, such as when you add fields or change field types, so that your SQL queries reflect the current data. To keep schemas current automatically instead, see Schedule Schema Updates.

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

Regenerate the schema.

On a collection that already has a schema:

Click the .
Click Generate new schema from sample.
Click Save.

View a Schema

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

On this page, you can view all existing schemas. To view a specific schema in JSON format, click the .

Edit a Schema

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

Edit the schema.

Next to a schema, click the .
Edit the JSON.
Click Save.

Delete a Schema

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

On this page, you can view all existing schemas.

Delete the schema.

For the schema that you want to delete, click the .
Click Clear....
Click Clear schema to confirm.

Delete All Schemas

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

On this page, you can view all existing schemas.

Delete all schemas.

Click the icon in the top-right corner.
Click Clear all schemas.
Click Clear all schemas again to confirm.

Schedule Schema Updates

Scheduled schema updates help you maintain schema accuracy over time. Scheduled schema updates sample each namespace and merge the new schema with the existing one. For example, scheduled schema updates allow Atlas Data Federation to automatically pick up new fields added to collections.

In Atlas, go to your federated database instance for your project.

If it's not already displayed, select the organization that contains your project from the Organizations menu in the navigation bar.
If it's not already displayed, select your project from the Projects menu in the navigation bar.
In the sidebar, click Data Federation under the Services heading.

The Data Federation page displays.

Navigate to the Manage SQL Schemas page.

From the Federated Database Instances section, click the icon to the right of the schema, and then select Manage SQL Schemas from the dropdown.

Enable scheduled schema updates.

Click Configure schema update schedule.
Select a frequency.
Click Save.

Learn More

Back

MongoDB SQL Schema Builder CLI

SQL Tutorials