Azure Cosmos DB partitioning and Indexing of Data in SQL API using PATH











up vote
0
down vote

favorite












I am collecting IoT data to Azure cosmos DB, I know COSOMOS DB SQL API is auto indexed by Path . I have around 150 sensors in each document , And most of sql queries are of



DeviceId is already Partition Key




Select c.sensorVariable From c where c.DeviceId = 'dev1' AND c.time= date1'



{ "DeviceId" : 'dev1' , "time" : 123333 , "sensor1" : 20 , "sensor2" : 40}




I will Fetch the various sensors data but all my queries are depend on depend on deviceId and time( which is in Unix Timestamp )



Is it possible to index data on deviceID and time and exclude other keys , which are also in the same path / .



And for collection by default



"includedPaths": [
{
"path": "/*",
"indexes": [
{
"kind": "Range",
"dataType": "Number",
"precision": -1
},
{
"kind": "Range",
"dataType": "String",
"precision": -1
},
{
"kind": "Spatial",
"dataType": "Point"
}
]
}
],


it comes with this i feel As for DataType String should't it be having Hash kind indexing rather than Range ? . And what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?



If i have 100 devices and putting data every second level what type of indexing is better ?










share|improve this question
























  • Hi,does my answer helps you?
    – Jay Gong
    Nov 8 at 1:57










  • Partially Jay , but it helped to gain knowledge
    – Amjath Khan
    Nov 8 at 17:19















up vote
0
down vote

favorite












I am collecting IoT data to Azure cosmos DB, I know COSOMOS DB SQL API is auto indexed by Path . I have around 150 sensors in each document , And most of sql queries are of



DeviceId is already Partition Key




Select c.sensorVariable From c where c.DeviceId = 'dev1' AND c.time= date1'



{ "DeviceId" : 'dev1' , "time" : 123333 , "sensor1" : 20 , "sensor2" : 40}




I will Fetch the various sensors data but all my queries are depend on depend on deviceId and time( which is in Unix Timestamp )



Is it possible to index data on deviceID and time and exclude other keys , which are also in the same path / .



And for collection by default



"includedPaths": [
{
"path": "/*",
"indexes": [
{
"kind": "Range",
"dataType": "Number",
"precision": -1
},
{
"kind": "Range",
"dataType": "String",
"precision": -1
},
{
"kind": "Spatial",
"dataType": "Point"
}
]
}
],


it comes with this i feel As for DataType String should't it be having Hash kind indexing rather than Range ? . And what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?



If i have 100 devices and putting data every second level what type of indexing is better ?










share|improve this question
























  • Hi,does my answer helps you?
    – Jay Gong
    Nov 8 at 1:57










  • Partially Jay , but it helped to gain knowledge
    – Amjath Khan
    Nov 8 at 17:19













up vote
0
down vote

favorite









up vote
0
down vote

favorite











I am collecting IoT data to Azure cosmos DB, I know COSOMOS DB SQL API is auto indexed by Path . I have around 150 sensors in each document , And most of sql queries are of



DeviceId is already Partition Key




Select c.sensorVariable From c where c.DeviceId = 'dev1' AND c.time= date1'



{ "DeviceId" : 'dev1' , "time" : 123333 , "sensor1" : 20 , "sensor2" : 40}




I will Fetch the various sensors data but all my queries are depend on depend on deviceId and time( which is in Unix Timestamp )



Is it possible to index data on deviceID and time and exclude other keys , which are also in the same path / .



And for collection by default



"includedPaths": [
{
"path": "/*",
"indexes": [
{
"kind": "Range",
"dataType": "Number",
"precision": -1
},
{
"kind": "Range",
"dataType": "String",
"precision": -1
},
{
"kind": "Spatial",
"dataType": "Point"
}
]
}
],


it comes with this i feel As for DataType String should't it be having Hash kind indexing rather than Range ? . And what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?



If i have 100 devices and putting data every second level what type of indexing is better ?










share|improve this question















I am collecting IoT data to Azure cosmos DB, I know COSOMOS DB SQL API is auto indexed by Path . I have around 150 sensors in each document , And most of sql queries are of



DeviceId is already Partition Key




Select c.sensorVariable From c where c.DeviceId = 'dev1' AND c.time= date1'



{ "DeviceId" : 'dev1' , "time" : 123333 , "sensor1" : 20 , "sensor2" : 40}




I will Fetch the various sensors data but all my queries are depend on depend on deviceId and time( which is in Unix Timestamp )



Is it possible to index data on deviceID and time and exclude other keys , which are also in the same path / .



And for collection by default



"includedPaths": [
{
"path": "/*",
"indexes": [
{
"kind": "Range",
"dataType": "Number",
"precision": -1
},
{
"kind": "Range",
"dataType": "String",
"precision": -1
},
{
"kind": "Spatial",
"dataType": "Point"
}
]
}
],


it comes with this i feel As for DataType String should't it be having Hash kind indexing rather than Range ? . And what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?



If i have 100 devices and putting data every second level what type of indexing is better ?







azure azure-cosmosdb azure-cosmosdb-sqlapi






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 7 at 22:11









Lukas Kabrt

4,40143452




4,40143452










asked Nov 7 at 7:47









Amjath Khan

405




405












  • Hi,does my answer helps you?
    – Jay Gong
    Nov 8 at 1:57










  • Partially Jay , but it helped to gain knowledge
    – Amjath Khan
    Nov 8 at 17:19


















  • Hi,does my answer helps you?
    – Jay Gong
    Nov 8 at 1:57










  • Partially Jay , but it helped to gain knowledge
    – Amjath Khan
    Nov 8 at 17:19
















Hi,does my answer helps you?
– Jay Gong
Nov 8 at 1:57




Hi,does my answer helps you?
– Jay Gong
Nov 8 at 1:57












Partially Jay , but it helped to gain knowledge
– Amjath Khan
Nov 8 at 17:19




Partially Jay , but it helped to gain knowledge
– Amjath Khan
Nov 8 at 17:19












1 Answer
1






active

oldest

votes

















up vote
1
down vote














Is it possible to index data on deviceID and time and exclude other
keys , which are also in the same path




Yes.You could custom your index policy by IncludedPaths and ExcludedPaths.



Such as :



var excluded = new DocumentCollection { Id = "excludedPathCollection" };
excluded.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
excluded.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/nonIndexedContent/*" });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), excluded);


Please refer to more details here.




what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?




Based on Index data types, kinds, and precisions:



For a Hash index, this varies from 1 to 8 for both strings and numbers. The default is 3. For a Range index, this value can be -1 (maximum precision). It can vary from between 1 and 100 (maximum precision) for string or number values.



You could focus on this statement to make your choices.




If i have 100 devices and putting data every second level what type of
indexing is better ?




It's hard to say which index mode is the best choice. It should be considered with consistency level and your requirements for read and write performance. You could refer to this paragraph.



Hope it helps you.






share|improve this answer





















  • Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
    – Amjath Khan
    Nov 8 at 17:18










  • @AmjathKhan Thank you for your sharing. So, any further questions?
    – Jay Gong
    Nov 9 at 2:03










  • I will test it as i have shared . Thank you , you are so much help for me.
    – Amjath Khan
    Nov 9 at 11:38











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














 

draft saved


draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53185320%2fazure-cosmos-db-partitioning-and-indexing-of-data-in-sql-api-using-path%23new-answer', 'question_page');
}
);

Post as a guest
































1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes








up vote
1
down vote














Is it possible to index data on deviceID and time and exclude other
keys , which are also in the same path




Yes.You could custom your index policy by IncludedPaths and ExcludedPaths.



Such as :



var excluded = new DocumentCollection { Id = "excludedPathCollection" };
excluded.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
excluded.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/nonIndexedContent/*" });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), excluded);


Please refer to more details here.




what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?




Based on Index data types, kinds, and precisions:



For a Hash index, this varies from 1 to 8 for both strings and numbers. The default is 3. For a Range index, this value can be -1 (maximum precision). It can vary from between 1 and 100 (maximum precision) for string or number values.



You could focus on this statement to make your choices.




If i have 100 devices and putting data every second level what type of
indexing is better ?




It's hard to say which index mode is the best choice. It should be considered with consistency level and your requirements for read and write performance. You could refer to this paragraph.



Hope it helps you.






share|improve this answer





















  • Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
    – Amjath Khan
    Nov 8 at 17:18










  • @AmjathKhan Thank you for your sharing. So, any further questions?
    – Jay Gong
    Nov 9 at 2:03










  • I will test it as i have shared . Thank you , you are so much help for me.
    – Amjath Khan
    Nov 9 at 11:38















up vote
1
down vote














Is it possible to index data on deviceID and time and exclude other
keys , which are also in the same path




Yes.You could custom your index policy by IncludedPaths and ExcludedPaths.



Such as :



var excluded = new DocumentCollection { Id = "excludedPathCollection" };
excluded.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
excluded.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/nonIndexedContent/*" });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), excluded);


Please refer to more details here.




what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?




Based on Index data types, kinds, and precisions:



For a Hash index, this varies from 1 to 8 for both strings and numbers. The default is 3. For a Range index, this value can be -1 (maximum precision). It can vary from between 1 and 100 (maximum precision) for string or number values.



You could focus on this statement to make your choices.




If i have 100 devices and putting data every second level what type of
indexing is better ?




It's hard to say which index mode is the best choice. It should be considered with consistency level and your requirements for read and write performance. You could refer to this paragraph.



Hope it helps you.






share|improve this answer





















  • Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
    – Amjath Khan
    Nov 8 at 17:18










  • @AmjathKhan Thank you for your sharing. So, any further questions?
    – Jay Gong
    Nov 9 at 2:03










  • I will test it as i have shared . Thank you , you are so much help for me.
    – Amjath Khan
    Nov 9 at 11:38













up vote
1
down vote










up vote
1
down vote










Is it possible to index data on deviceID and time and exclude other
keys , which are also in the same path




Yes.You could custom your index policy by IncludedPaths and ExcludedPaths.



Such as :



var excluded = new DocumentCollection { Id = "excludedPathCollection" };
excluded.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
excluded.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/nonIndexedContent/*" });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), excluded);


Please refer to more details here.




what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?




Based on Index data types, kinds, and precisions:



For a Hash index, this varies from 1 to 8 for both strings and numbers. The default is 3. For a Range index, this value can be -1 (maximum precision). It can vary from between 1 and 100 (maximum precision) for string or number values.



You could focus on this statement to make your choices.




If i have 100 devices and putting data every second level what type of
indexing is better ?




It's hard to say which index mode is the best choice. It should be considered with consistency level and your requirements for read and write performance. You could refer to this paragraph.



Hope it helps you.






share|improve this answer













Is it possible to index data on deviceID and time and exclude other
keys , which are also in the same path




Yes.You could custom your index policy by IncludedPaths and ExcludedPaths.



Such as :



var excluded = new DocumentCollection { Id = "excludedPathCollection" };
excluded.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/*" });
excluded.IndexingPolicy.ExcludedPaths.Add(new ExcludedPath { Path = "/nonIndexedContent/*" });

await client.CreateDocumentCollectionAsync(UriFactory.CreateDatabaseUri("db"), excluded);


Please refer to more details here.




what is this Precision : -1



In Azure cosmos DB doc examples i have seen precision as 3 for string , i did not understood why ?




Based on Index data types, kinds, and precisions:



For a Hash index, this varies from 1 to 8 for both strings and numbers. The default is 3. For a Range index, this value can be -1 (maximum precision). It can vary from between 1 and 100 (maximum precision) for string or number values.



You could focus on this statement to make your choices.




If i have 100 devices and putting data every second level what type of
indexing is better ?




It's hard to say which index mode is the best choice. It should be considered with consistency level and your requirements for read and write performance. You could refer to this paragraph.



Hope it helps you.







share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 7 at 8:05









Jay Gong

7,0281411




7,0281411












  • Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
    – Amjath Khan
    Nov 8 at 17:18










  • @AmjathKhan Thank you for your sharing. So, any further questions?
    – Jay Gong
    Nov 9 at 2:03










  • I will test it as i have shared . Thank you , you are so much help for me.
    – Amjath Khan
    Nov 9 at 11:38


















  • Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
    – Amjath Khan
    Nov 8 at 17:18










  • @AmjathKhan Thank you for your sharing. So, any further questions?
    – Jay Gong
    Nov 9 at 2:03










  • I will test it as i have shared . Thank you , you are so much help for me.
    – Amjath Khan
    Nov 9 at 11:38
















Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
– Amjath Khan
Nov 8 at 17:18




Thanks for the reply . One more thing i observed is { "datapoint1" : 23 , "datapoint2" : 20 , "datapoint3" : 45 } like i have 150 sensors { "keyDataPoint" : 22 , "Data" : { "datapoin1" , "datapoint2" .... } } if i keep the data in above format and index data to only one level , that will improve performance.
– Amjath Khan
Nov 8 at 17:18












@AmjathKhan Thank you for your sharing. So, any further questions?
– Jay Gong
Nov 9 at 2:03




@AmjathKhan Thank you for your sharing. So, any further questions?
– Jay Gong
Nov 9 at 2:03












I will test it as i have shared . Thank you , you are so much help for me.
– Amjath Khan
Nov 9 at 11:38




I will test it as i have shared . Thank you , you are so much help for me.
– Amjath Khan
Nov 9 at 11:38


















 

draft saved


draft discarded



















































 


draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53185320%2fazure-cosmos-db-partitioning-and-indexing-of-data-in-sql-api-using-path%23new-answer', 'question_page');
}
);

Post as a guest




















































































這個網誌中的熱門文章

Xamarin.form Move up view when keyboard appear

Post-Redirect-Get with Spring WebFlux and Thymeleaf

Anylogic : not able to use stopDelay()