Gerenciar dados do Spanner Graph

Observação: esse recurso está disponível nas edições Enterprise e Enterprise Plus do Spanner. Para mais informações, consulte o resumo das edições do Spanner.

Este documento mostra como gerenciar dados em um gráfico inserindo, atualizando e excluindo nós e arestas. O Spanner Graph mapeia dados de tabelas para nós e arestas de gráficos. Para fazer mutações em dados em um gráfico, é necessário fazer mutações nos dados das tabelas de entrada correspondentes. É possível usar o Google Cloud console, a Google Cloud CLI ou as bibliotecas de cliente do Spanner para gerenciar dados de gráficos.

Configurar o Spanner Graph

Antes de gerenciar dados no Spanner Graph, é necessário configurar um gráfico fazendo o seguinte:

1. Crie uma instância do Spanner.

2. Crie um banco de dados na sua instância do Spanner.

3. Insira dados de gráficos no seu banco de dados.

Os exemplos nestas seções usam a instância e o banco de dados criados ao configurar o Spanner Graph com as etapas anteriores.

Inserir nós ou arestas

Para inserir nós ou arestas em tabelas de nós ou arestas, use o Google Cloud console, a CLI gcloud ou as bibliotecas de cliente do Spanner.

No Google Cloud console e na CLI gcloud, é possível usar a linguagem de manipulação de dados (DML) do GoogleSQL. Na biblioteca de cliente do Spanner, é possível usar APIs DML ou Mutation.

Antes de inserir uma aresta, verifique se os nós de origem e destino conectados pela aresta existem. Se você inserir uma aresta quando o nó de origem ou destino conectado por ela não existir, poderá receber erros de violação de integridade referencial. Para mais informações, consulte O nó de origem ausente viola o relacionamento INTERLEAVE IN e O nó de destino ausente viola a chave externa estrangeira.

Os exemplos a seguir inserem nós Account e arestas Transfer no banco de dados criado em Configurar o Spanner Graph.

Console

No Google Cloud console, execute a seguinte instrução DML. Para mais informações, consulte Executar instruções no Google Cloud console.

-- Insert 2 Account nodes.
INSERT INTO Account (id, create_time, is_blocked)
VALUES (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false);
INSERT INTO Account (id, create_time, is_blocked)
VALUES (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true);

-- Insert 2 Transfer edges.
INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
VALUES (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100);
INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
VALUES (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200);

gcloud

Na CLI gcloud, execute os comandos a seguir. Para mais informações, consulte Executar instruções com a CLI gcloud.

gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="INSERT INTO Account (id, create_time, is_blocked) VALUES (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false)"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="INSERT INTO Account (id, create_time, is_blocked) VALUES (2, CAST('2000-08-12 07:13:16.463959-03:41'  AS TIMESTAMP), true)"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) VALUES (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100)"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) VALUES (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200)"

Substitua:

• DATABASE_NAME: o nome do banco de dados.
• INSTANCE_NAME: o nome da instância.

Bibliotecas de cliente

Python

def insert_data_with_dml(instance_id, database_id):
    """Inserts sample data into the given database using a DML statement."""

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

    def insert_accounts(transaction):
        row_ct = transaction.execute_update(
            "INSERT INTO Account (id, create_time, is_blocked) "
            "  VALUES"
            "    (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false),"
            "    (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true)"
        )

        print("{} record(s) inserted into Account.".format(row_ct))

    def insert_transfers(transaction):
        row_ct = transaction.execute_update(
            "INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) "
            "  VALUES"
            "    (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100),"
            "    (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200) "
        )

        print("{} record(s) inserted into AccountTransferAccount.".format(row_ct))

    database.run_in_transaction(insert_accounts)
    database.run_in_transaction(insert_transfers)

Java

static void insertUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql =
                "INSERT INTO Account (id, create_time, is_blocked) "
                    + "  VALUES"
                    + "    (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false),"
                    + "    (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true)";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d record(s) inserted into Account.\n", rowCount);
            return null;
          });

  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql =
                "INSERT INTO AccountTransferAccount (id, to_id, create_time, amount) "
                    + "  VALUES"
                    + "    (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100),"
                    + "    (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200) ";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d record(s) inserted into AccountTransferAccount.\n", rowCount);
            return null;
          });
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

	// Execute a ReadWriteTransaction to insert values into the 'Account' table
	// underpinning 'Account' nodes in 'FinGraph'. The function run by ReadWriteTransaction
	// executes an 'INSERT' SQL DML statement. Graph queries run after this
	// transaction is committed will observe the effects of the new 'Account's
	// added to the graph.
	_, err1 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO Account (id, create_time, is_blocked)
            		VALUES
            	    	(1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false),
            			(2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true)`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d Account record(s) inserted.\n", rowCount)
		return err
	})

	if err1 != nil {
		return err1
	}

	// Execute a ReadWriteTransaction to insert values into the 'AccountTransferAccount'
	// table underpinning 'AccountTransferAccount' edges in 'FinGraph'. The function run
	// by ReadWriteTransaction executes an 'INSERT' SQL DML statement.
	// Graph queries run after this transaction is committed will observe the effects
	// of the edges added to the graph.
	_, err2 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
					VALUES
						(1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100),
						(1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200)`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d AccountTransferAccount record(s) inserted.\n", rowCount)
		return err
	})

	return err2
}

C++

void InsertDataWithDml(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  std::int64_t rows_inserted;
  auto commit_result = client.Commit(
      [&client, &rows_inserted](
          spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert =
            client.ExecuteDml(std::move(txn), spanner::SqlStatement(R"""(
          INSERT INTO Account (id, create_time, is_blocked)
          VALUES
          (1, CAST('2000-08-10 08:18:48.463959-07:52' AS TIMESTAMP), false),
          (2, CAST('2000-08-12 07:13:16.463959-03:41' AS TIMESTAMP), true)
        )"""));
        if (!insert) return std::move(insert).status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Rows inserted into Account: " << rows_inserted << "\n";

  commit_result = client.Commit(
      [&client, &rows_inserted](
          spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto insert =
            client.ExecuteDml(std::move(txn), spanner::SqlStatement(R"""(
          INSERT INTO AccountTransferAccount (id, to_id, create_time, amount)
          VALUES
          (1, 2, CAST('2000-09-11 03:11:18.463959-06:36' AS TIMESTAMP), 100),
          (1, 1, CAST('2000-09-12 04:09:34.463959-05:12' AS TIMESTAMP), 200)
        )"""));
        if (!insert) return std::move(insert).status();
        rows_inserted = insert->RowsModified();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Rows inserted into AccountTransferAccount: " << rows_inserted
            << "\n";

  std::cout << "Insert was successful [spanner_insert_graph_data_with_dml]\n";
}

Atualizar nós ou arestas

Para atualizar nós ou arestas atuais, use o Google Cloud console, a CLI gcloud ou as bibliotecas de cliente do Spanner.

É possível atualizar nós ou arestas atuais usando uma instrução da linguagem de manipulação de dados (DML) do GoogleSQL Data Manipulation Language (DML) ou consultas do Spanner Graph com uma instrução DML. Se você usar uma biblioteca de cliente do Spanner, também poderá usar APIs Mutation.

Atualizar nós ou arestas com DML

Os exemplos a seguir usam DML para atualizar o nó Account e a aresta Transfer adicionados em Inserir nós ou arestas.

Console

No Google Cloud console, execute a seguinte instrução DML. Para mais informações, consulte Executar instruções no Google Cloud console.

-- Update Account node
UPDATE Account SET is_blocked = false WHERE id = 2;

-- Update Transfer edge
UPDATE AccountTransferAccount
SET amount = 300
WHERE id = 1 AND to_id = 2;

gcloud

1. Execute instruções com a CLI gcloud.
2. Na CLI gcloud, execute os comandos a seguir:

gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="UPDATE Account SET is_blocked = false WHERE id = 2"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2"

Substitua:

• DATABASE_NAME: o nome do banco de dados.
• INSTANCE_NAME: o nome da instância.

Bibliotecas de cliente

Python

def update_data_with_dml(instance_id, database_id):
    """Updates sample data from the database using a DML statement."""

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

    def update_accounts(transaction):
        row_ct = transaction.execute_update(
            "UPDATE Account SET is_blocked = false WHERE id = 2"
        )

        print("{} Account record(s) updated.".format(row_ct))

    def update_transfers(transaction):
        row_ct = transaction.execute_update(
            "UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2"
        )

        print("{} AccountTransferAccount record(s) updated.".format(row_ct))

    database.run_in_transaction(update_accounts)
    database.run_in_transaction(update_transfers)

Java

static void updateUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql = "UPDATE Account SET is_blocked = false WHERE id = 2";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d Account record(s) updated.\n", rowCount);
            return null;
          });

  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql =
                "UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d AccountTransferAccount record(s) updated.\n", rowCount);
            return null;
          });
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

	// Execute a ReadWriteTransaction to update the 'Account' table underpinning
	// 'Account' nodes in 'FinGraph'. The function run by ReadWriteTransaction
	// executes an 'UPDATE' SQL DML statement. Graph queries run after this
	// transaction is committed will observe the effects of the update to 'Account'
	// with 'id' = 2.
	_, err1 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `UPDATE Account SET is_blocked = false WHERE id = 2`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d Account record(s) updated.\n", rowCount)
		return err
	})

	if err1 != nil {
		return err1
	}

	// Execute a ReadWriteTransaction to update the 'AccountTransferAccount' table
	// underpinning 'AccountTransferAccount' edges in 'FinGraph'. The function run
	// by ReadWriteTransaction executes an 'UPDATE' SQL DML statement.
	// Graph queries run after this transaction is committed will observe the effects
	// of the update to 'AccountTransferAccount' where the source of the transfer has
	// 'id' 1 and the destination has 'id' 2.
	_, err2 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d AccountTransferAccount record(s) updated.\n", rowCount)
		return err
	})

	return err2
}

C++

void UpdateDataWithDml(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement(
                "UPDATE Account SET is_blocked = false WHERE id = 2"));
        if (!update) return std::move(update).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();

  commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update =
            client.ExecuteDml(std::move(txn), spanner::SqlStatement(R"""(
          UPDATE AccountTransferAccount
            SET amount = 300 WHERE id = 1 AND to_id = 2)"""));
        if (!update) return std::move(update).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();

  std::cout << "Update was successful [spanner_update_graph_data_with_dml]\n";
}

Atualizar nós ou arestas com consultas de gráficos e DML

Os exemplos a seguir usam consultas do Spanner Graph com DML para atualizar o Account nó e Transfer aresta adicionados em Inserir nós ou arestas.

Console

No Google Cloud console, execute a seguinte instrução DML. Para mais informações, consulte Executar instruções no Google Cloud console.

-- Use Graph pattern matching to identify Account nodes to update:
UPDATE Account SET is_blocked = false
WHERE id IN {
  GRAPH FinGraph
  MATCH (a:Account WHERE a.id = 1)-[:Transfers]->{1,2}(b:Account)
  RETURN b.id
}

gcloud

Na CLI gcloud, execute os comandos a seguir. Para mais informações, consulte Executar instruções com a CLI gcloud.

gcloud spanner databases execute-sql DATABASE-NAME --instance=INSTANCE_NAME \
    --sql="UPDATE Account SET is_blocked = false"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="UPDATE AccountTransferAccount SET amount = 300 WHERE id = 1 AND to_id = 2"
    --sql=" WHERE id IN { GRAPH FinGraph MATCH (a:Account WHERE a.id = 1)-[:Transfers]->{1,2}(b:Account) RETURN b.id }"

Substitua:

• DATABASE_NAME: o nome do banco de dados.
• INSTANCE_NAME: o nome da instância.

Bibliotecas de cliente

Python

def update_data_with_graph_query_in_dml(instance_id, database_id):
    """Updates sample data from the database using a DML statement."""

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

    def update_accounts(transaction):
        row_ct = transaction.execute_update(
            "UPDATE Account SET is_blocked = true "
            "WHERE id IN {"
            "  GRAPH FinGraph"
            "  MATCH (a:Account WHERE a.id = 1)-[:TRANSFERS]->{1,2}(b:Account)"
            "  RETURN b.id}"
        )

        print("{} Account record(s) updated.".format(row_ct))

    database.run_in_transaction(update_accounts)

Java

static void updateUsingGraphQueryInDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql =
                "UPDATE Account SET is_blocked = true "
                    + "WHERE id IN {"
                    + "  GRAPH FinGraph"
                    + "  MATCH (a:Account WHERE a.id = 1)-[:TRANSFERS]->{1,2}(b:Account)"
                    + "  RETURN b.id}";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d Account record(s) updated.\n", rowCount);
            return null;
          });
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

	// Execute a ReadWriteTransaction to update the 'Account' table underpinning
	// 'Account' nodes in 'FinGraph'. The function run by ReadWriteTransaction
	// executes an 'UPDATE' SQL DML statement. Graph queries run after this
	// transaction is committed will observe the effects of the updates to 'Account's
	//
	// The update is performed for all 'Account's whose 'id' is returned by
	// the graph query in the 'IN' subquery, i.e., all 'Account's that have
	// received transfers directly or via one intermediary from an 'Account'
	// whose 'id' is 1.
	_, err = client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{
			SQL: `UPDATE Account SET is_blocked = true 
            	  WHERE id IN {
            	    GRAPH FinGraph 
            	    MATCH (a:Account WHERE a.id = 1)-[:TRANSFERS]->{1,2}(b:Account)
            	    RETURN b.id}`,
		}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d Account record(s) updated.\n", rowCount)
		return err
	})

	return err
}

C++

void UpdateDataWithGraphQueryInDml(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;
  auto commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto update =
            client.ExecuteDml(std::move(txn), spanner::SqlStatement(R"""(
              UPDATE Account SET is_blocked = true
              WHERE id IN {
                GRAPH FinGraph
                MATCH (a:Account WHERE a.id = 1)-[:TRANSFERS]->{1,2}(b:Account)
                RETURN b.id})"""));
        if (!update) return std::move(update).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();
  std::cout << "Update was successful "
            << "[spanner_update_graph_data_with_graph_query_in_dml]\n";
}

Excluir nós ou arestas

Para excluir nós ou arestas atuais, use o Google Cloud console, a CLI gcloud ou as bibliotecas de cliente do Spanner.

No Google Cloud console e na CLI gcloud, você usa a linguagem de manipulação de dados (DML) do GoogleSQL para excluir. Na biblioteca de cliente do Spanner, é possível usar APIs DML ou Mutation para excluir nós ou arestas.

Para evitar erros de violação de integridade referencial, verifique se nenhuma aresta se refere a um nó ao excluir o nó. Para mais informações, consulte A aresta de saída órfã viola o relacionamento pai-filho e A aresta de entrada órfã viola o relacionamento pai-filho.

Os exemplos a seguir excluem uma aresta Transfer e um nó Account do gráfico.

Console

No Google Cloud console, execute a seguinte instrução DML. Para mais informações, consulte Executar instruções no Google Cloud console.

-- Delete Transfer edge
DELETE FROM AccountTransferAccount
WHERE id = 1 AND to_id = 2;

-- Delete Account node
DELETE FROM Account WHERE id = 2;

gcloud

Na CLI gcloud, execute os comandos a seguir. Para mais informações, consulte Executar instruções com a CLI gcloud.

gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"
gcloud spanner databases execute-sql  DATABASE-NAME --instance=INSTANCE-NAME \
    --sql="DELETE FROM Account WHERE id = 2"

Substitua:

• DATABASE_NAME: o nome do banco de dados.
• INSTANCE_NAME: o nome da instância.

Bibliotecas de cliente

Python

def delete_data_with_dml(instance_id, database_id):
    """Deletes sample data from the database using a DML statement."""

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

    def delete_transfers(transaction):
        row_ct = transaction.execute_update(
            "DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"
        )

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

    def delete_accounts(transaction):
        row_ct = transaction.execute_update("DELETE FROM Account WHERE id = 2")

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

    database.run_in_transaction(delete_transfers)
    database.run_in_transaction(delete_accounts)

Java

static void deleteUsingDml(DatabaseClient dbClient) {
  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql = "DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d AccountTransferAccount record(s) deleted.\n", rowCount);
            return null;
          });

  dbClient
      .readWriteTransaction()
      .run(
          transaction -> {
            String sql = "DELETE FROM Account WHERE id = 2";
            long rowCount = transaction.executeUpdate(Statement.of(sql));
            System.out.printf("%d Account record(s) deleted.\n", rowCount);
            return null;
          });
}

Go

import (
	"context"
	"fmt"
	"io"

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

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

	// Execute a ReadWriteTransaction to update the 'AccountTransferAccount'
	// table underpinning 'AccountTransferAccount' edges in 'FinGraph'. The
	// function run by ReadWriteTransaction executes an 'DELETE' SQL DML
	// statement. This has the effect of deleting the 'AccountTransferAccount'
	// edge where the source 'id' is 1 and the destination 'id' is 2 from the graph.
	_, err1 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{SQL: `DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2`}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d AccountTransferAccount record(s) deleted.\n", rowCount)
		return nil
	})

	if err1 != nil {
		return err1
	}

	// Execute a ReadWriteTransaction to update the 'Account' table underpinning
	//'Account' nodes in 'FinGraph'. In 'FinGraph', nodes can only be deleted
	// after any edges referencing the nodes have been deleted first. The function
	// run by ReadWriteTransaction executes an 'DELETE' SQL DML statement. This has
	// the effect of deleting the 'Account' node whose 'id' is 1 from the graph.
	_, err2 := client.ReadWriteTransaction(ctx, func(ctx context.Context, txn *spanner.ReadWriteTransaction) error {
		stmt := spanner.Statement{SQL: `DELETE FROM Account WHERE id = 2`}
		rowCount, err := txn.Update(ctx, stmt)
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "%d Account record(s) deleted.\n", rowCount)
		return nil
	})

	return err2
}

C++

void DeleteDataWithDml(google::cloud::spanner::Client client) {
  using ::google::cloud::StatusOr;
  namespace spanner = ::google::cloud::spanner;

  auto commit_result = client.Commit([&client](spanner::Transaction txn)
                                         -> StatusOr<spanner::Mutations> {
    auto deleted = client.ExecuteDml(
        std::move(txn),
        spanner::SqlStatement(
            "DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"));
    if (!deleted) return std::move(deleted).status();
    return spanner::Mutations{};
  });
  if (!commit_result) throw std::move(commit_result).status();

  commit_result = client.Commit(
      [&client](spanner::Transaction txn) -> StatusOr<spanner::Mutations> {
        auto deleted = client.ExecuteDml(
            std::move(txn),
            spanner::SqlStatement("DELETE FROM Account WHERE id = 2"));
        if (!deleted) return std::move(deleted).status();
        return spanner::Mutations{};
      });
  if (!commit_result) throw std::move(commit_result).status();

  std::cout << "Delete was successful [spanner_delete_graph_data_with_dml]\n";
}

É possível combinar consultas do Spanner Graph com a instrução DML, conforme mostrado no exemplo a seguir:

  -- Use Graph pattern matching to identify Account nodes to delete:
  DELETE FROM AccountTransferAccount
  WHERE id IN {
    GRAPH FinGraph
    MATCH (a:Account WHERE a.id = 1)-[:Transfers]->(b:Account)
    RETURN b.id
  }

Operações de dados automatizadas e em massa

Além de usar DML para inserir, atualizar e excluir nós e arestas individuais, também é possível usar os métodos a seguir para gerenciar os dados do Spanner Graph:

• É possível automatizar a exclusão de arestas em um gráfico usando a ação ON DELETE CASCADE.

• É possível automatizar a exclusão de nós e arestas no gráfico usando uma TTL. Para mais informações, consulte TTL em nós e arestas.

• Atualize e exclua nós e arestas no gráfico de maneira eficiente usando DML particionada.

A seguir

• Leia a visão geral das consultas do Spanner Graph.
• Conheça as práticas recomendadas para ajustar as consultas do Spanner Graph.