20 comments

Discussion is closed. Login to edit/delete existing comments.

• 

  Douglas Isaksson August 17, 2023 0

  Collapse this comment Copy link

  Cool stuff. Single source is a bit hempering but at least it’s something. “Where clause in the materialized view definition is not supported.”, does that mean you cant filter the data in any way? It’s only for projecting the data and performance?

  Can the source container be in another cosmos database account?

  • 

    Abhinav Tripathi Author August 17, 2023 0

    Collapse this comment Copy link

    Hi Douglas,
    Multi source MV is under discussion but not a priority right now. “WHERE” clause is work in progress and it will be available when the feature is out of Public Preview.
    The source cannot be in a different database account as of now.

    • 

      Douglas Isaksson August 18, 2023 0

      Collapse this comment Copy link

      Would be very nice if you could have them in separate database accounts. Right now we separate db accounts on DDD subdomains, sometimes you want to bring in data from another subdomain and model that after your needs within said subdomain, classic MV scenario. Keeps the boundraries clear and neat.

• 

  Nitin Rahim July 10, 2023 0

  Collapse this comment Copy link

  Hi Abhinav,

  In materialized view are we still bound by logical partition size limit of 20 GB? If we are having a unique key (high cardinality value) as the partition key in the materialized view target container which is read only there can be potentially billions of logical partitions (also items) based on the workload. But since the materialized view container is read only and we know the partition key in the search we can ...

  Read more

  Hi Abhinav,

  In materialized view are we still bound by logical partition size limit of 20 GB? If we are having a unique key (high cardinality value) as the partition key in the materialized view target container which is read only there can be potentially billions of logical partitions (also items) based on the workload. But since the materialized view container is read only and we know the partition key in the search we can do a read item and can escape the cross partition scenario in the target container. But our concern is do we have a limit to the number of logical partitions being created? Since we are not writing to the logical partitions in the materialized view hot partition scenario is not rising but considering our workload we can have billions of unique items based on the partition key(the unique id that is selected).Will that cause a potential limitation down the line?

  With Regards,
  Nitin Rahim

  Read less
  • 

    Abhinav Tripathi Author July 12, 2023 0

    Collapse this comment Copy link

    The Materialized View containers are similar to the normal containers in all aspects except that they are read-only containers for the applications. Just like the case with normal containers, you can have UNLIMITED number of distinct (logical) partition keys.

    • 

      Nitin Rahim July 12, 2023 0

      Collapse this comment Copy link

      Thanks Abhinav.

      With Regards,
      Nitin Rahim

• 

  Nitin Rahim June 29, 2023 1

  Collapse this comment Copy link

  Hi Abhinav,

  Does materialized view support hierarchical partitioning in target container? Its currently documented it is not supported in source container as of now. Wanted to confirm if that’s the case with target mv container as well?

  With Regards,
  Nitin Rahim

  • 

    Abhinav Tripathi Author June 30, 2023 0

    Collapse this comment Copy link

    Thanks for your question Nitin!
    Yes, hierarchical partitioning is supported on the target container.
    For a source item like
    <code>

    You can specify officeId as the partition key by specifying the path as below:
    <code>

    Read more

    Thanks for your question Nitin!
    Yes, hierarchical partitioning is supported on the target container.
    For a source item like

    {
        "consultationId": "c1",
        "consultationDate": "2019-01-01",
        "doctor": {
            "doctorId": "d1",
            "doctorName": "Dr. A",
            "office": {
                "officeId": "o1"
            }
        },
        "patient": {
            "patientId": "p1",
            "patientName": "P1"
        }
    }

    You can specify officeId as the partition key by specifying the path as below:

    {
        "location": "West US 2",
        "tags": {},
        "properties": {
          "resource": {
            "id": "mv-target-double",
            "partitionKey": {
              "paths": [
                "/doctor/office/officeId"
              ],
              "kind": "Hash"
            },
            "materializedViewDefinition": {
              "sourceCollectionId": "mv-src",
              "definition": "SELECT * FROM s"
            }
          },
          "options": {
            "throughput": 400
          }
        }
      }    

    Read less
    • 

      Nitin Rahim July 1, 2023 · Edited 0

      Collapse this comment Copy link

      Hi Abhinav,

      Thanks for your response. It seems this is still 1 level of partitioning. What I was referring to was In hierarchical partitioning we can specify up to 3 as mentioned in the below link.

      https://learn.microsoft.com/en-us/azure/cosmos-db/hierarchical-partition-keys?tabs=net-v3%2Cbicep

      eg.

      “partitionKey”: {
      “paths”: [
      “/TenantId”,
      “/UserId”,
      “/SessionId”
      ],
      “kind”: “MultiHash”,
      “version”: 2
      }

      Can we specify accordingly in the target container in materialized view setup json?

      Thanks in advance.

      With Regards,
      Nitin Rahim

    • 

      Abhinav Tripathi Author July 1, 2023 0

      Collapse this comment Copy link

      Ah, got your question wrong earlier. No, hierarchical partioning is currently not supported on target containers

    • 

      Nitin Rahim July 1, 2023 0

      Collapse this comment Copy link

      Okay Thanks Abhinav.

      With Regards,
      Nitin Rahim

• 

  Erik O'Leary June 24, 2023 1

  Collapse this comment Copy link

  There should be a serverless usage-based offering for materialized view builders that users can opt-in to instead of the $0.38/hour provisioned VM

  • 

    Abhinav Tripathi Author June 30, 2023 0

    Collapse this comment Copy link

    Thanks Erik for the suggestion. That’s a tough ask but worth considering for sure.

• 

  Olena Borzenko-Turianska June 21, 2023 · Edited 1

  Collapse this comment Copy link

  I've tried to follow the guide and found two small mistakes in the REST API call command:
  1. –url should be with double dash <code>
  2. content-type—application/json uses an em dash (—) instead of an equal sign (=): content-type=application/json;

  Here is the full command that worked well for me:
  <code>

  Read more

  I’ve tried to follow the guide and found two small mistakes in the REST API call command:
  1. –url should be with double dash

  --url
  

  2. content-type—application/json uses an em dash (—) instead of an equal sign (=): content-type=application/json;

  Here is the full command that worked well for me:

  az rest --method put --body @mv_definition.json --url https://management.azure.com/subscriptions/SUBSCRIPTION_ID/resourceGroups/RG_NAME/providers/Microsoft.DocumentDB/databaseAccounts/DB_ACCOUNT_NAME/sqlDatabases/DB_NAME/containers/MV_CONTAINER_NAME?api-version=2022-11-15-preview --headers content-type=application/json 

  Read less
  • 

    Abhinav Tripathi Author June 21, 2023 0

    Collapse this comment Copy link

    Thanks Olena – fixed.

• 

  Anders Wåglund May 23, 2023 0

  Collapse this comment Copy link

  ”a single Materialized View container cannot fetch data from multiple source containers”

  Why this again?

  We have been using Cosmos for a couple of years and have worked around all quirks (cannot combine group by and limit etc). We waited for this years build hoping that some of these issues will be resolved, alas no.

  Thanks for everything but we will migrate to another database now.

  • 

    Abhinav Tripathi Author May 30, 2023 0

    Collapse this comment Copy link

    Hi Anders,
    A lot of use-cases get served with single source based MVs (like reduction in cross partition queries) but I do understand that MVs based on multiple source containers is also an ask from our customers. This feature is currently in Public Preview and we are continuously improving. If you’d like to, I’d be happy to understand your use case. Let me know if we can connect.

    • 

      Douglas Isaksson August 17, 2023 0

      Collapse this comment Copy link

      Agree and yes also: Please fix the group by together with limit and/or order by. It’s been planned forever.

    • 

      Abhinav Tripathi Author August 17, 2023 0

      Collapse this comment Copy link

      Hey Douglas, are you facing limitations in queries in general or you are stating this in MV context?

    • 

      Douglas Isaksson August 18, 2023 0

      Collapse this comment Copy link

      This is about querying in cosmos in general, as Anders pointed out above.