CODEWITHSUNDEEP
azureazure-cosmosdb
armadillo.mx
armadillo.mx

Reputation: 1001

Delete all/multiple documents from Azure Cosmos DB through the portal

Is it possible to delete all/multiple documents available in a collection through the azure portal, Azure cosmos SQL Query or a power shell script ?

Upvotes: 35

Views: 47096

Answers (2)

Sajeetharan
Sajeetharan

Reputation: 222722

This is currently supported via Azure Portal Data Explorer.

  • Sign in to Azure portal.
  • Navigate to your existing Azure Cosmos DB account.
  • In the resource menu, select Data Explorer.
  • Next, navigate to the database and container that contain the items you want to delete.
  • To select multiple items, you can either use the Shift or Ctrl keys on your keyboard or click on the checkboxes next to each item.
  • Once you have selected the items you want to delete, you can click on the Delete button on the toolbar, or you can use the keyboard shortcut Alt + D or Opt + D to delete items.
  • A confirmation dialog will appear, asking you to verify that you want to delete the selected items. Click on Yes to proceed, or No to cancel.
  • The selected items will be deleted from the container, and you will see a notification informing you of the outcome.

Upvotes: 0

Zaphod
Zaphod

Reputation: 1482

The fastest way to delete all documents, in my experience, is to set "time to live" on the container to 1 sec. That will remove all documents. But be aware that this process takes some time, so if you set "time to live" back to unlimited too soon the documents that haven't been removed yet will reappear.

You can set the "time to live" under "Scale and Settings" for the container: url -> https://learn.microsoft.com/en-us/azure/cosmos-db/how-to-time-to-live

You could also create a stored procedure in the container and run that..url -> https://github.com/Azure/azure-cosmosdb-js-server/blob/master/samples/stored-procedures/bulkDelete.js

The stored procedure:

/**
 * A Cosmos DB stored procedure that bulk deletes documents for a given query.<br/>
 * Note: You may need to execute this stored procedure multiple times (depending whether the stored procedure is able to delete every document within the execution timeout limit).
 *
 * @function
 * @param {string} query - A query that provides the documents to be deleted (e.g. "SELECT c._self FROM c WHERE c.founded_year = 2008"). Note: For best performance, reduce the # of properties returned per document in the query to only what's required (e.g. prefer SELECT c._self over SELECT * )
 * @returns {Object.<number, boolean>} Returns an object with the two properties:<br/>
 *   deleted - contains a count of documents deleted<br/>
 *   continuation - a boolean whether you should execute the stored procedure again (true if there are more documents to delete; false otherwise).
 */
function bulkDeleteStoredProcedure(query) {
    var collection = getContext().getCollection();
    var collectionLink = collection.getSelfLink();
    var response = getContext().getResponse();
    var responseBody = {
        deleted: 0,
        continuation: true
    };

    // Validate input.
    if (!query) throw new Error("The query is undefined or null.");

    tryQueryAndDelete();

    // Recursively runs the query w/ support for continuation tokens.
    // Calls tryDelete(documents) as soon as the query returns documents.
    function tryQueryAndDelete(continuation) {
        var requestOptions = {continuation: continuation};

        var isAccepted = collection.queryDocuments(collectionLink, query, requestOptions, function (err, retrievedDocs, responseOptions) {
            if (err) throw err;

            if (retrievedDocs.length > 0) {
                // Begin deleting documents as soon as documents are returned form the query results.
                // tryDelete() resumes querying after deleting; no need to page through continuation tokens.
                //  - this is to prioritize writes over reads given timeout constraints.
                tryDelete(retrievedDocs);
            } else if (responseOptions.continuation) {
                // Else if the query came back empty, but with a continuation token; repeat the query w/ the token.
                tryQueryAndDelete(responseOptions.continuation);
            } else {
                // Else if there are no more documents and no continuation token - we are finished deleting documents.
                responseBody.continuation = false;
                response.setBody(responseBody);
            }
        });

        // If we hit execution bounds - return continuation: true.
        if (!isAccepted) {
            response.setBody(responseBody);
        }
    }

    // Recursively deletes documents passed in as an array argument.
    // Attempts to query for more on empty array.
    function tryDelete(documents) {
        if (documents.length > 0) {
            // Delete the first document in the array.
            var isAccepted = collection.deleteDocument(documents[0]._self, {}, function (err, responseOptions) {
                if (err) throw err;

                responseBody.deleted++;
                documents.shift();
                // Delete the next document in the array.
                tryDelete(documents);
            });

            // If we hit execution bounds - return continuation: true.
            if (!isAccepted) {
                response.setBody(responseBody);
            }
        } else {
            // If the document array is empty, query for more documents.
            tryQueryAndDelete();
        }
    }
}

You could then write a powershellscript to run that stored procedure.

UPDATE

I believe another advantage of setting the "Time to Live" is that it doesn't cost any RUs, but deleting with a sproc will.

Upvotes: 37

Related Questions