Linguagem de manipulação de dados particionada

A linguagem de manipulação de dados particionada (DML particionada) foi projetada para os seguintes tipos de atualizações e exclusões em massa:

  • Limpeza periódica e coleta de lixo. Alguns exemplos são excluir linhas antigas ou definir colunas como NULL.
  • Preenchimento de novas colunas com valores padrão. Um exemplo é o uso de uma instrução UPDATE para definir o valor de uma nova coluna como False, no qual o valor atual é NULL.

A DML particionada não é adequada para processamento de transações em pequena escala. Se você quiser executar uma instrução em algumas linhas, use DMLs transacionais com chaves primárias identificáveis. Para mais informações, consulte Como usar a DML.

Se você precisar fazer commit de um grande número de gravações cegas, mas não exigir uma transação atômica, modifique em massa as tabelas do Spanner usando a gravação em lote. Para mais informações, consulte Modificar dados usando gravações em lote.

É possível receber insights sobre consultas DML particionadas ativas e o progresso delas nas tabelas de estatísticas do banco de dados do Spanner. Para mais informações, consulte Estatísticas de DMLs particionadas ativas.

DML e DML particionada

O Spanner oferece suporte a dois modos de execução para instruções DML:

  • DML, que é adequada para o processamento de transações. Para mais informações, consulte Como usar a DML.

  • DML particionada, que permite operações em larga escala em todo o banco de dados com impacto mínimo no processamento de transações simultâneas, particionando o espaço de chaves e executando a instrução nas partições em transações separadas e com escopo menor. Para mais informações, consulte Como usar a DML particionada.

A tabela a seguir destaca algumas das diferenças entre os dois modos de execução.

DML DML particionada
Linhas que não correspondem à cláusula WHERE podem ser bloqueadas. Apenas as linhas que correspondem à cláusula WHERE são bloqueadas.
Limites de tamanho de transação são aplicáveis. O Spanner lida com os limites de transação e os limites de simultaneidade por transação.
As instruções não precisam ser idempotentes. Uma instrução DML precisa ser idempotente para garantir resultados consistentes.
Uma transação pode incluir várias declarações DML e instruções SQL. Uma transação particionada pode incluir apenas uma declaração DML.
Não há restrições à complexidade das declarações. As declarações precisam ser totalmente particionáveis.
Você cria transações de leitura/gravação no seu código de cliente. O Spanner cria as transações.

Particionável e idempotente

Quando uma instrução DML particionada é executada, as linhas de uma partição não têm acesso a linhas em outras partições, e não é possível escolher como o Spanner cria as partições. O particionamento garante a escalonabilidade, mas também significa que as instruções DML particionadas precisam ser totalmente particionáveis. Ou seja, a instrução DML particionada precisa ser expressável como a união de um conjunto de instruções, em que cada declaração acessa uma única linha da tabela e cada instrução não acessa outras tabelas. Por exemplo, uma instrução DML que acessa várias tabelas ou realiza uma união automática não é particionável. Se a instrução DML não puder ser particionada, o Spanner vai retornar o erro BadUsage.

Estas instruções DML são totalmente particionáveis porque cada uma pode ser aplicada a uma única linha na tabela:

UPDATE Singers SET LastName = NULL WHERE LastName = '';

DELETE FROM Albums WHERE MarketingBudget > 10000;

Esta instrução DML não é totalmente particionável, porque acessa várias tabelas:

# Not fully partitionable
DELETE FROM Singers WHERE
SingerId NOT IN (SELECT SingerId FROM Concerts);

O Spanner pode executar uma instrução DML particionada várias vezes em algumas partições devido a novas tentativas no nível da rede. Como resultado, uma instrução pode ser executada mais de uma vez em uma linha. A instrução precisa, portanto, ser idempotente para produzir resultados consistentes. Uma instrução é idempotente se executá-la várias vezes em uma única linha levar ao mesmo resultado.

Esta instrução DML é idempotente:

UPDATE Singers SET MarketingBudget = 1000 WHERE true;

Esta não é:

UPDATE Singers SET MarketingBudget = 1.5 * MarketingBudget WHERE true;

Excluir linhas de tabelas principais com tabelas secundárias indexadas

Quando você usa uma instrução DML particionada para excluir linhas em uma tabela principal, a operação pode falhar com o erro: The transaction contains too many mutations. Isso acontece se a tabela pai tiver tabelas filhas intercaladas que contenham um índice global. As mutações nas linhas da tabela filha não são contadas no limite de mutação da transação. No entanto, as mutações correspondentes nas entradas de índice são contabilizadas. Se um grande número de entradas de índice da tabela filha for afetado, a transação poderá exceder o limite de mutação.

Para evitar esse erro, exclua as linhas em duas instruções DML particionadas separadas:

  1. Execute uma exclusão particionada nas tabelas secundárias.
  2. Execute uma exclusão particionada na tabela principal.

Esse processo de duas etapas ajuda a manter a contagem de mutações dentro dos limites permitidos para cada transação. Como alternativa, você pode descartar o índice global na tabela filha antes de excluir as linhas mãe.

Bloqueio de linha

O Spanner adquire um bloqueio somente se uma linha for candidata a atualização ou exclusão. Esse comportamento é diferente da execução de DML, que pode bloquear a leitura de linhas que não correspondem à cláusula WHERE.

Execução e transações

Se uma declaração DML é particionada ou não, depende do método da biblioteca de cliente que você escolhe para execução. Cada biblioteca de cliente fornece métodos separados para execução de DML e execução de DML particionada.

Você pode executar apenas uma instrução DML particionada em uma chamada para o método da biblioteca de cliente.

O Spanner não aplica as instruções DML particionadas atomicamente em toda a tabela. No entanto, o Spanner aplica instruções DML particionadas atomicamente em cada partição.

A DML particionada não é compatível com consolidação ou reversão. O Spanner executa e aplica a instrução DML imediatamente.

  • Se você cancelar a operação, o Spanner cancelará as partições em execução e não iniciará as partições restantes. O Spanner não reverter partições que já foram executadas.
  • Se a execução da instrução causar um erro, a execução será interrompida em todas as partições e o Spanner vai retornar esse erro para a operação inteira. Alguns exemplos de erros são violações de restrições de tipo de dados, violações de UNIQUE INDEX e violações de ON DELETE NO ACTION. Dependendo do momento em que houve a falha na execução, a instrução poderá ter sido executada com sucesso em algumas partições e nunca ter sido executada em outras.

Se a instrução DML particionada for bem-sucedida, o Spanner vai executar a instrução pelo menos uma vez em cada partição do intervalo de chaves.

Contagem de linhas modificadas

Uma instrução DML particionada retorna um limite inferior do número de linhas modificadas. Pode não ser uma contagem exata do número de linhas modificadas, porque não há garantia de que o Spanner conta todas as linhas modificadas.

Limites de transação

O Spanner cria as partições e transações necessárias para executar uma instrução DML particionada. Limites de transação ou limites de simultaneidade por transação se aplicam, mas o Spanner tenta manter as transações dentro dos limites.

O Spanner permite um máximo de 20.000 instruções DML particionadas simultâneas por banco de dados.

Recursos não suportados

O Spanner não é compatível com alguns recursos para DML particionada:

  • INSERT não é compatível.
  • Console doGoogle Cloud : não é possível executar instruções DML particionadas no console doGoogle Cloud .
  • Planos de consulta e criação de perfil: a CLI do Google Cloud e as bibliotecas de cliente não são compatíveis com planos de consulta e criação de perfil.
  • Subconsultas que leem de outra tabela ou de uma linha diferente da mesma tabela.

Para cenários complexos, como mover uma tabela ou transformações que exigem junções entre tabelas, considere usar o conector do Dataflow.

Práticas recomendadas

Aplique as seguintes práticas recomendadas para melhorar o desempenho das instruções DML particionadas:

  • Evite alta simultaneidade:executar um grande número de instruções DML particionadas simultaneamente (por exemplo, mais de 100) pode levar a uma disputa de bloqueio em tabelas internas do sistema, degradando o desempenho. Em vez de executar um grande número de instruções simultâneas, use uma única instrução DML particionada.
  • Use PDML_MAX_PARALLELISM: Para aumentar a capacidade de processamento de uma única instrução DML particionada, especialmente em tabelas com muitas divisões, defina um valor mais alto para a dica de instrução PDML_MAX_PARALLELISM. Isso permite que a única instrução use mais paralelismo internamente. Definir um valor maior para PDML_MAX_PARALLELISM resulta em mais uso de computação. Portanto, tente equilibrar o uso de computação e o aumento da velocidade de processamento.
  • Deixe o Spanner lidar com o particionamento:evite fragmentar manualmente seus dados (por exemplo, usando intervalos chave primária) e executar instruções DML particionadas separadas em cada fragmento. A DML particionada foi projetada para particionar o trabalho de maneira eficiente em toda a tabela. O sharding personalizado geralmente aumenta a sobrecarga e pode piorar a disputa.
  • Entenda o escopo do particionamento:as operações de DML particionada são paralelizadas em todas as divisões do banco de dados inteiro, não apenas nas divisões que contêm dados da tabela que está sendo modificada. Isso significa que, para bancos de dados com um grande número de divisões, pode haver sobrecarga mesmo que a tabela de destino seja pequena ou os dados modificados estejam localizados. A DML particionada pode não ser a opção mais eficiente para modificar uma parte muito pequena de um banco de dados grande.
  • Considere alternativas para exclusões pequenas e frequentes:para casos de uso que envolvem exclusões frequentes de um pequeno número de linhas conhecidas, usar instruções DML em transações ou a API BatchWrite pode oferecer melhor performance e menor sobrecarga do que usar DML particionada.

Exemplos

O exemplo de código a seguir atualiza a coluna MarketingBudget da tabela Albums.

C++

Use a função ExecutePartitionedDml() para executar uma instrução DML particionada.

void DmlPartitionedUpdate(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto result = client.ExecutePartitionedDml(
      spanner::SqlStatement("UPDATE Albums SET MarketingBudget = 100000"
                            "  WHERE SingerId > 1"));
  if (!result) throw std::move(result).status();
  std::cout << "Updated at least " << result->row_count_lower_bound
            << " row(s) [spanner_dml_partitioned_update]\n";
}

C#

Use o método ExecutePartitionedUpdateAsync() para executar uma instrução DML particionada.


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class UpdateUsingPartitionedDmlCoreAsyncSample
{
    public async Task<long> UpdateUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1");
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();

        Console.WriteLine($"{rowCount} row(s) updated...");
        return rowCount;
    }
}

Go

Use o método PartitionedUpdate() para executar uma instrução DML particionada.


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func updateUsingPartitionedDML(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"}
	rowCount, err := client.PartitionedUpdate(ctx, stmt)
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "%d record(s) updated.\n", rowCount)
	return nil
}

Java

Use o método executePartitionedUpdate() para executar uma instrução DML particionada.

static void updateUsingPartitionedDml(DatabaseClient dbClient) {
  String sql = "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1";
  long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
  System.out.printf("%d records updated.\n", rowCount);
}

Node.js

Use o método runPartitionedUpdate() para executar uma instrução DML particionada.

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  const [rowCount] = await database.runPartitionedUpdate({
    sql: 'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1',
  });
  console.log(`Successfully updated ${rowCount} records.`);
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

Use o método executePartitionedUpdate() para executar uma instrução DML particionada.

use Google\Cloud\Spanner\SpannerClient;

/**
 * Updates sample data in the database by partition with a DML statement.
 *
 * This updates the `MarketingBudget` column which must be created before
 * running this sample. You can add the column by running the `add_column`
 * sample or by running this DDL statement against your database:
 *
 *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * Example:
 * ```
 * update_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function update_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $rowCount = $database->executePartitionedUpdate(
        'UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1'
    );

    printf('Updated %d row(s).' . PHP_EOL, $rowCount);
}

Python

Use o método execute_partitioned_dml() para executar uma instrução DML particionada.

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"

spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

row_ct = database.execute_partitioned_dml(
    "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)

print("{} records updated.".format(row_ct))

Ruby

Use o método execute_partitioned_update() para executar uma instrução DML particionada.

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

row_count = client.execute_partition_update(
  "UPDATE Albums SET MarketingBudget = 100000 WHERE SingerId > 1"
)

puts "#{row_count} records updated."

O exemplo de código a seguir exclui linhas da tabela Singers com base na coluna SingerId.

C++

void DmlPartitionedDelete(google::cloud::spanner::Client client) {
  namespace spanner = ::google::cloud::spanner;
  auto result = client.ExecutePartitionedDml(
      spanner::SqlStatement("DELETE FROM Singers WHERE SingerId > 10"));
  if (!result) throw std::move(result).status();
  std::cout << "Deleted at least " << result->row_count_lower_bound
            << " row(s) [spanner_dml_partitioned_delete]\n";
}

C#


using Google.Cloud.Spanner.Data;
using System;
using System.Threading.Tasks;

public class DeleteUsingPartitionedDmlCoreAsyncSample
{
    public async Task<long> DeleteUsingPartitionedDmlCoreAsync(string projectId, string instanceId, string databaseId)
    {
        string connectionString = $"Data Source=projects/{projectId}/instances/{instanceId}/databases/{databaseId}";

        using var connection = new SpannerConnection(connectionString);
        await connection.OpenAsync();

        using var cmd = connection.CreateDmlCommand("DELETE FROM Singers WHERE SingerId > 10");
        long rowCount = await cmd.ExecutePartitionedUpdateAsync();

        Console.WriteLine($"{rowCount} row(s) deleted...");
        return rowCount;
    }
}

Go


import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/spanner"
)

func deleteUsingPartitionedDML(w io.Writer, db string) error {
	ctx := context.Background()
	client, err := spanner.NewClient(ctx, db)
	if err != nil {
		return err
	}
	defer client.Close()

	stmt := spanner.Statement{SQL: "DELETE FROM Singers WHERE SingerId > 10"}
	rowCount, err := client.PartitionedUpdate(ctx, stmt)
	if err != nil {
		return err

	}
	fmt.Fprintf(w, "%d record(s) deleted.", rowCount)
	return nil
}

Java

static void deleteUsingPartitionedDml(DatabaseClient dbClient) {
  String sql = "DELETE FROM Singers WHERE SingerId > 10";
  long rowCount = dbClient.executePartitionedUpdate(Statement.of(sql));
  System.out.printf("%d records deleted.\n", rowCount);
}

Node.js

// Imports the Google Cloud client library
const {Spanner} = require('@google-cloud/spanner');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = 'my-project-id';
// const instanceId = 'my-instance';
// const databaseId = 'my-database';

// Creates a client
const spanner = new Spanner({
  projectId: projectId,
});

// Gets a reference to a Cloud Spanner instance and database
const instance = spanner.instance(instanceId);
const database = instance.database(databaseId);

try {
  const [rowCount] = await database.runPartitionedUpdate({
    sql: 'DELETE FROM Singers WHERE SingerId > 10',
  });
  console.log(`Successfully deleted ${rowCount} records.`);
} catch (err) {
  console.error('ERROR:', err);
} finally {
  // Close the database when finished.
  database.close();
}

PHP

use Google\Cloud\Spanner\SpannerClient;

/**
 * Delete sample data in the database by partition with a DML statement.
 *
 * This updates the `MarketingBudget` column which must be created before
 * running this sample. You can add the column by running the `add_column`
 * sample or by running this DDL statement against your database:
 *
 *     ALTER TABLE Albums ADD COLUMN MarketingBudget INT64
 *
 * Example:
 * ```
 * update_data($instanceId, $databaseId);
 * ```
 *
 * @param string $instanceId The Spanner instance ID.
 * @param string $databaseId The Spanner database ID.
 */
function delete_data_with_partitioned_dml(string $instanceId, string $databaseId): void
{
    $spanner = new SpannerClient();
    $instance = $spanner->instance($instanceId);
    $database = $instance->database($databaseId);

    $rowCount = $database->executePartitionedUpdate(
        'DELETE FROM Singers WHERE SingerId > 10'
    );

    printf('Deleted %d row(s).' . PHP_EOL, $rowCount);
}

Python

# instance_id = "your-spanner-instance"
# database_id = "your-spanner-db-id"
spanner_client = spanner.Client()
instance = spanner_client.instance(instance_id)
database = instance.database(database_id)

row_ct = database.execute_partitioned_dml("DELETE FROM Singers WHERE SingerId > 10")

print("{} record(s) deleted.".format(row_ct))

Ruby

# project_id  = "Your Google Cloud project ID"
# instance_id = "Your Spanner instance ID"
# database_id = "Your Spanner database ID"

require "google/cloud/spanner"

spanner = Google::Cloud::Spanner.new project: project_id
client  = spanner.client instance_id, database_id

row_count = client.execute_partition_update(
  "DELETE FROM Singers WHERE SingerId > 10"
)

puts "#{row_count} records deleted."

A seguir