Azure Cosmos DB SQL API v3 SDK批量插入大规模数据优化咨询

• 原始数据为两张无外键、共享公共属性的SQL表，通过该公共属性关联后共得到1300000条关联记录（该属性在两张表中均非唯一、可重复出现），需要使用Azure Cosmos DB v3 SDK的批量插入能力，将已加载到内存的关联列表数据写入Cosmos DB
• 核心运行问题：
  • 哪怕将吞吐量设置为50000或100000，批量插入仍需10-15分钟以上，尝试多种优化方案均无明显效果
  • 唯一运行效率较好的一次优化是通过LINQ将两表关联为父子结构列表，最终仅生成约2900个文档，批量插入耗时约33秒；但即便已完成JSON序列化优化（缩短字符串、简化日期格式、忽略空值等），部分文档体积仍超过Cosmos DB的2MB单文档限制
  • 使用100000吞吐量（测试环境）插入1300000个文档时，会触发服务不可用、TooManyRequest异常；当前测试基于Cosmos DB模拟器，对Azure Cosmos DB相关操作尚不熟悉
  • 批量插入过程中本地内存占用过高、接近耗尽

CosmosClient单例实例创建配置 #

serviceCollection.AddSingleton(x =>
{
    var primaryConnectionString = _solutionConfiguration.AzureCosmosDb.PrimaryConnectionString;
    if (string.IsNullOrEmpty(primaryConnectionString))
    {
        throw new InvalidOperationException(
            "Please specify a valid CosmosDBConnection in the solutionsettings.json file or your Azure Functions Settings.");
    }

    return new CosmosClientBuilder(primaryConnectionString)
    .WithSerializerOptions(new CosmosSerializationOptions() { PropertyNamingPolicy = CosmosPropertyNamingPolicy.CamelCase, IgnoreNullValues = true })
    .WithRequestTimeout(TimeSpan.FromMinutes(2))
    .WithBulkExecution(true).Build();
});

容器创建逻辑 #

public async static Task CreatContainerForDatabase(CosmosClient cosmosClient, string databaseId, string containerId, string partitionKey)
{
    Console.WriteLine(">>>>>> Create New Container >>>>>>");

    var containerProperties = new ContainerProperties
    {
        Id = containerId,
        PartitionKeyPath = partitionKey
    };

    var result = await cosmosClient.GetDatabase(databaseId).CreateContainerAsync(containerProperties, 150000);

    Console.WriteLine($"New container with id: {result.Container.Id} has been created");
}

数据写入执行逻辑 #

Console.WriteLine("Bulk insert!");

var cost = 0D;
var errors = 0;

var documentsToCreate = boatsWithPricesFirstYear.Count();

var executionStarted = DateTime.Now;
var container = _cosmosClient.GetContainer(_configuration.AzureCosmosDb.DatabaseId, _configuration.AzureCosmosDb.ContainerId);

var tasks = new List<Task>(documentsToCreate);

foreach (var boat in boatsWithPricesFirstYear)
{
    var result = container.CreateItemAsync(boat, new PartitionKey(boat.BoatId));

    tasks.Add(result.ContinueWith(t =>
    {
        if (t.Status == TaskStatus.RanToCompletion)
        {
            cost += t.Result.RequestCharge;
        }
        else
        {
            Console.WriteLine($"Error creating boat with id: {boat.BoatId} document: {t.Exception.Message}");
            errors++;
        }
    }));
}

await Task.WhenAll(tasks);

Console.WriteLine($"{documentsToCreate - errors} documents has been created at cost of: {cost:0.##} RUs in {DateTime.Now.Subtract(executionStarted)}");

Console.ReadLine();

• 运行统计显示，插入1300000个文档的总RU消耗约为9000000，希望找到降低插入RU消耗的方法（此前了解到SQL API单条写入约消耗10.2 RU，不确定该参考数值是否准确）
• 调整为父子结构（船舶为父实体、每日价格为子实体）的2900个文档，总RU消耗约为850000
• 已参考微软官方批量导入教程示例，最终运行结果和现有实现几乎没有差异
• 若需补充其他排查相关信息会及时提供

内容的提问来源于stack exchange，提问作者Ivan