Reference

Table [Dynamic Table]

Description

No description available

Supported Operations

Below section contains supported CRUD operations. Each operation is executed by some EndPoint behind the scene.
Method Supported Reference EndPoint
SELECT get_documents
INSERT create_document
UPDATE update_document
UPSERT upsert_document
DELETE delete_document
LOOKUP get_document

Examples

SSIS

Use Cosmos DB Connector in API Source component to read data or in API Destination component to read/write data:

Read from [Dynamic Table] table using API Source

API Source - Cosmos DB
Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
Cosmos DB
[Dynamic Table]
Required Parameters
Table Name (Case-Sensitive) Fill-in the parameter...
Optional Parameters
Database Name (keep blank to use default) Case-Sensitive
SSIS API Source - Read from table or endpoint

Read/write to [Dynamic Table] table using API Destination

API Destination - Cosmos DB
Connect to your Azure Cosmos DB databases to read, query, create, update, and delete documents and more!
Cosmos DB
[Dynamic Table]
Select
Required Parameters
Table Name (Case-Sensitive) Fill-in the parameter...
Optional Parameters
Database Name (keep blank to use default) Case-Sensitive
SSIS API Destination - Access table operation

ODBC application

Use these SQL queries in your ODBC application data source:

Query using Cosmos DB (Using Default Table)

Lists all containers from the default database specified (Specified for the connection settings). Check this URL for more information on query syntax: https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select 

#DirectSQL SELECT * FROM root where root.id !=null order by root._ts desc

Read all documents for a collection in Cosmos DB

Read all documents for a collection in Cosmos DB. 

SELECT * FROM TestContainer

Read a document by Id from a collection in Cosmos DB

Read a single document by Id from a collection in Cosmos DB. 

SELECT * FROM TestContainer Where Id='user2'

Create a new document with Partition Key supplied

Loads a new document into specified container with partition key. If you created container with Partition Key requirement then must supply it. Partition Key must be valid value from Document Attribute used as Partition Key. 

INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
'["user2"]', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
'{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}'
)
WITH(Upsert='true')

Create a new document from a file path (Upload from local disk file)

Loads a new document into specified container from local file path. File path must start with @ symbol 

INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
'["user2"]', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
'@c:\data\order.json'   --path must start with @ symbol
)
WITH(Upsert='true', IsMultiPart=1)

Upsert a document (Insert or Update if exists)

Upsert a document (Update if id exists else create new one). 

INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
'["user2"]', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
'{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}')
 WITH(Upsert='true')

Upsert a document (Insert or Update if exists) - Use UPSERT Keyword rather than WITH option

Upsert a document (Update if id exists else create new one). This one uses UPSERT KEY word slight simpler syntax becuase you dont need WITH clause like INSERT... WITH(UPSERT='true') example. 

UPSERT INTO TestContainer (PartitionKey, Document)
VALUES(
'["user2"]', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
'{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}')

Update (replace) a document (By default Full replace)

Update a document for a specified Document Id. 

UPDATE TestContainer 
SET  
 PartitionKey='["user2"]'
,Document=
'{
  "id" : "user2",
  "name": "John Doe at <<FUN_NOW>>",
  "email": "jdoe@contoso.com",
  "phone": ["<<FUN_TODAY>>"],
  "level": "platinum"
}'
Where Id='user2'

Partial Update a document (Patch)

Update one or more attributes in a document using PATCH method for a specified Document Id. 

UPDATE TestContainer 
SET  
 PartitionKey='["user2"]'
,Document=
'{
  "operations": [
     { "op": "set", "path": "/name", "value": "updated name" }
	,{ "op": "set", "path": "/email", "value": "updated@email.com" }
  ]
}'
Where Id='user2'
WITH(
		RequestMethod='PATCH' --Partial Replace (change name and email only)
    )
	
/*
Example Document Operations
https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update
https://learn.microsoft.com/en-us/rest/api/cosmos-db/patch-a-document

[
  { "op": "add", "path": "/color", "value": "silver" },
  { "op": "remove", "path": "/used" },
  { "op": "set", "path": "/price", "value": 355.45 }
  { "op": "incr", "path": "/inventory/quantity", "value": 10 },
  { "op": "add", "path": "/tags/-", "value": "featured-bikes" },
  { "op": "move", "from": "/color", "path": "/inventory/color" }
]

*/

Partial Update a document (Patch) from a file path (Upload from local disk file)

Update one or more attributes in a document using PATCH method for a specified Document Id read operations from local disk file. 

UPDATE TestContainer 
SET  
 PartitionKey='["user2"]'
,Document='@c:\temp\b.txt'  --path must start with @ symbol
Where Id='user2'
WITH(
		 RequestMethod='PATCH' --Partial Replace (change name and email only)
	    ,IsMultiPart=1 --this enables file upload	
    )
	
/*
Example Document Operations
https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update
https://learn.microsoft.com/en-us/rest/api/cosmos-db/patch-a-document

[
  { "op": "add", "path": "/color", "value": "silver" },
  { "op": "remove", "path": "/used" },
  { "op": "set", "path": "/price", "value": 355.45 }
  { "op": "incr", "path": "/inventory/quantity", "value": 10 },
  { "op": "add", "path": "/tags/-", "value": "featured-bikes" },
  { "op": "move", "from": "/color", "path": "/inventory/color" }
]

*/

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Query using Cosmos DB (Using Default Table)

Lists all containers from the default database specified (Specified for the connection settings). Check this URL for more information on query syntax: https://learn.microsoft.com/en-us/azure/cosmos-db/nosql/query/select 

DECLARE @MyQuery NVARCHAR(MAX) = '#DirectSQL SELECT * FROM root where root.id !=null order by root._ts desc';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Read all documents for a collection in Cosmos DB

Read all documents for a collection in Cosmos DB. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM TestContainer';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Read a document by Id from a collection in Cosmos DB

Read a single document by Id from a collection in Cosmos DB. 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM TestContainer Where Id=''user2''';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Create a new document with Partition Key supplied

Loads a new document into specified container with partition key. If you created container with Partition Key requirement then must supply it. Partition Key must be valid value from Document Attribute used as Partition Key. 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
''["user2"]'', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
''{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}''
)
WITH(Upsert=''true'')';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Create a new document from a file path (Upload from local disk file)

Loads a new document into specified container from local file path. File path must start with @ symbol 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
''["user2"]'', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
''@c:\data\order.json''   --path must start with @ symbol
)
WITH(Upsert=''true'', IsMultiPart=1)';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Upsert a document (Insert or Update if exists)

Upsert a document (Update if id exists else create new one). 

DECLARE @MyQuery NVARCHAR(MAX) = 'INSERT INTO TestContainer (PartitionKey, Document)
VALUES(
''["user2"]'', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
''{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}'')
 WITH(Upsert=''true'')';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Upsert a document (Insert or Update if exists) - Use UPSERT Keyword rather than WITH option

Upsert a document (Update if id exists else create new one). This one uses UPSERT KEY word slight simpler syntax becuase you dont need WITH clause like INSERT... WITH(UPSERT='true') example. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPSERT INTO TestContainer (PartitionKey, Document)
VALUES(
''["user2"]'', --partition key value must match its attribute from document else it will throw error. In this example container PartitionKey is /id so we used its value. For multiple key use JSON array ["val1","val2"]
''{
  "id": "user2",
  "name": "John Doe",
  "email": "jdoe@contoso.com",
  "phone": ["12345"],
  "level": "platinum"
}'')';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Update (replace) a document (By default Full replace)

Update a document for a specified Document Id. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE TestContainer 
SET  
 PartitionKey=''["user2"]''
,Document=
''{
  "id" : "user2",
  "name": "John Doe at <<FUN_NOW>>",
  "email": "jdoe@contoso.com",
  "phone": ["<<FUN_TODAY>>"],
  "level": "platinum"
}''
Where Id=''user2''';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Partial Update a document (Patch)

Update one or more attributes in a document using PATCH method for a specified Document Id. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE TestContainer 
SET  
 PartitionKey=''["user2"]''
,Document=
''{
  "operations": [
     { "op": "set", "path": "/name", "value": "updated name" }
	,{ "op": "set", "path": "/email", "value": "updated@email.com" }
  ]
}''
Where Id=''user2''
WITH(
		RequestMethod=''PATCH'' --Partial Replace (change name and email only)
    )
	
/*
Example Document Operations
https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update
https://learn.microsoft.com/en-us/rest/api/cosmos-db/patch-a-document

[
  { "op": "add", "path": "/color", "value": "silver" },
  { "op": "remove", "path": "/used" },
  { "op": "set", "path": "/price", "value": 355.45 }
  { "op": "incr", "path": "/inventory/quantity", "value": 10 },
  { "op": "add", "path": "/tags/-", "value": "featured-bikes" },
  { "op": "move", "from": "/color", "path": "/inventory/color" }
]

*/';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];

Partial Update a document (Patch) from a file path (Upload from local disk file)

Update one or more attributes in a document using PATCH method for a specified Document Id read operations from local disk file. 

DECLARE @MyQuery NVARCHAR(MAX) = 'UPDATE TestContainer 
SET  
 PartitionKey=''["user2"]''
,Document=''@c:\temp\b.txt''  --path must start with @ symbol
Where Id=''user2''
WITH(
		 RequestMethod=''PATCH'' --Partial Replace (change name and email only)
	    ,IsMultiPart=1 --this enables file upload	
    )
	
/*
Example Document Operations
https://learn.microsoft.com/en-us/azure/cosmos-db/partial-document-update
https://learn.microsoft.com/en-us/rest/api/cosmos-db/patch-a-document

[
  { "op": "add", "path": "/color", "value": "silver" },
  { "op": "remove", "path": "/used" },
  { "op": "set", "path": "/price", "value": 355.45 }
  { "op": "incr", "path": "/inventory/quantity", "value": 10 },
  { "op": "add", "path": "/tags/-", "value": "featured-bikes" },
  { "op": "move", "from": "/color", "path": "/inventory/color" }
]

*/';

EXEC (@MyQuery) AT [LS_TO_COSMOS_DB_IN_GATEWAY];