Inserire, aggiornare o eliminare i dati di Spanner Graph

Nota: questa funzionalità è disponibile con le versioni Spanner Enterprise ed Enterprise Plus. Per ulteriori informazioni, consulta la panoramica delle versioni di Spanner.

Questo documento descrive come modificare i dati in Spanner Graph utilizzando la console Google Cloud, Google Cloud CLI e le librerie client. La mutazione degli elementi del grafo include l'inserimento, l'aggiornamento e l'eliminazione di nodi ed elementi in Spanner Graph.

Spanner Graph mappa i dati delle tabelle ai nodi e agli archi del grafo. Per mutare i dati in un grafico, devi mutare i dati nelle tabelle di input corrispondenti. Per ulteriori informazioni, consulta la panoramica dello schema di Spanner Graph.

Prima di iniziare

Per completare i passaggi della console Google Cloud e gli esempi di codice in questo documento, devi prima seguire i passaggi descritti in Configurare e eseguire query su Spanner Graph per eseguire le seguenti operazioni:

1. Crea un'istanza.
2. Crea un database con uno schema Spanner.
3. Inserisci i dati del grafico.

Inserire nodi o spigoli

Per inserire nodi o archi, utilizza la console Google Cloud, Google Cloud CLI o le librerie client di Spanner per inserire righe nelle tabelle dei nodi o degli archi.

Nella console Google Cloud e in Google Cloud CLI, puoi utilizzare il linguaggio Data Manipulation Language (DML) di GoogleSQL per eseguire l'inserimento. Nella libreria client Spanner, puoi utilizzare DML o API di mutazione.

Prima di inserire un bordo, assicurati che i nodi di origine e di destinazione collegati dal bordo esistano. Se inserisci un bordo quando il nodo di origine o di destinazione collegato dal bordo non esiste, potresti ricevere errori di violazione dell'integrità referenziale. Per ulteriori informazioni, consulta Nodo di origine mancante viola la relazione INTERLEAVE IN e Nodo di destinazione mancante viola la limitazione della chiave esterna.

Gli esempi seguenti inseriscono nel grafo i nodi Account e gli archi Transfer:

Console

1. Esegui istruzioni nella console Google Cloud.

2. Nella console Google Cloud, inserisci il seguente statement DML e fai clic su Esegui query:

-- 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

1. Esegui istruzioni con gcloud CLI.
2. Nell'interfaccia a riga di comando gcloud, esegui i seguenti comandi:

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --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 example-db --instance=test-instance \
    --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 example-db --instance=test-instance \
    --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 example-db --instance=test-instance \
    --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)"

Librerie client

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;
          });
}

Vai

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";
}

Aggiorna nodi o archi

Per aggiornare i nodi o gli archi esistenti, utilizza la console Google Cloud, la CLI gcloud o le librerie client Spanner.

Puoi aggiornare i nodi o gli archi esistenti utilizzando un'istruzione Data Manipulation Language (DML) di GoogleSQL o query sul grafo Spanner con un'istruzione DML. Nella libreria client Spanner, puoi anche utilizzare API di mutazione.

Aggiorna nodi o archi con DML

Gli esempi seguenti aggiornano un nodo Account e un bordo Transfer nel grafico utilizzando DML:

Console

1. Esegui istruzioni nella console Google Cloud.

2. Nella console Google Cloud, inserisci il seguente statement DML e fai clic su Esegui query:

-- 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;

Console

1. Esegui istruzioni con gcloud CLI.
2. Nell'interfaccia a riga di comando gcloud, esegui i seguenti comandi:

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

Librerie client

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;
          });
}

Vai

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";
}

Aggiorna i nodi o gli archi con query sui grafici e DML

I seguenti esempi aggiornano un nodo Account e un bordo Transfer nel grafico utilizzando le query di Spanner Graph con DML:

Console

1. Esegui istruzioni nella console Google Cloud.

2. Nella console Google Cloud, inserisci la seguente query di grafo Spanner con un'istruzione DML, quindi fai clic su Esegui query:

-- 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

1. Esegui istruzioni con gcloud CLI.
2. Nell'interfaccia a riga di comando gcloud, esegui i seguenti comandi:

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="UPDATE Account SET is_blocked = false"
gcloud spanner databases execute-sql example-db --instance=test-instance \
    --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 }"

Librerie client

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;
          });
}

Vai

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";
}

Eliminare nodi o spigoli

Per eliminare nodi o archi esistenti, utilizza la console Google Cloud, l'interfaccia a riga di comando gcloud o le librerie client Spanner.

Nella console Google Cloud e nella CLI gcloud, puoi utilizzare il Data Manipulation Language (DML) di Google SQL per eliminare. Nella libreria client Spanner puoi utilizzare DML o API di mutazione.

Prima di eliminare un nodo, assicurati che non esistano archi che fanno riferimento al nodo. Se esistono questi tipi di bordi, potresti ricevere errori di violazione dell'integrità referenziale. Per ulteriori informazioni, consulta L'elemento Orfano in uscita viola la relazione padre-figlio e L'elemento Orfano in entrata viola la relazione padre-figlio.

Gli esempi riportati di seguito eliminano un'associazione Transfer e un nodo Account dal grafico.

Console

1. Esegui istruzioni nella console Google Cloud.
2. Nella console Google Cloud, inserisci il seguente statement DML e fai clic su Esegui query:

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

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

gcloud

1. Esegui istruzioni con gcloud CLI.
2. Nell'interfaccia a riga di comando gcloud, esegui i seguenti comandi:

gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="DELETE FROM AccountTransferAccount WHERE id = 1 AND to_id = 2"
gcloud spanner databases execute-sql example-db --instance=test-instance \
    --sql="DELETE FROM Account WHERE id = 2"

Librerie client

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;
          });
}

Vai

// Copyright 2024 Google LLC
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
//     https://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.

package spanner


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";
}

Puoi combinare le query di Spanner Graph con l'istruzione DML, come mostrato nell'esempio seguente:

  -- 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
  }

Opzioni per la mutazione dei dati del grafico

Puoi mutare automaticamente i dati del grafico nei seguenti modi:

• Elimina automaticamente gli archi nel grafo utilizzando l'azione ON DELETE CASCADE.
• Elimina automaticamente i nodi e gli archi nel grafo utilizzando il criterio TTL. Per ulteriori informazioni, consulta TTL su nodi ed archi.

Per aggiornare ed eliminare in blocco in modo efficiente i nodi e gli archi nel grafo, utilizza la DML partizionata.

Passaggi successivi

• Leggi la panoramica delle query di Spanner Graph.
• Scopri le best practice per l'ottimizzazione delle query di Spanner Graph.