Inserire, aggiornare o eliminare i dati di Spanner Graph

Anteprima - Spanner Graph

Questa funzionalità è soggetta ai "Termini delle Offerte pre-GA" nella sezione Termini generali del servizio dei Termini specifici dei servizi. Le funzionalità pre-GA sono disponibili "così come sono" e potrebbero avere un supporto limitato. Per saperne di più, consulta le descrizioni della fase di lancio.

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 mutare i dati in Spanner Graph utilizzando il comando Console Google Cloud, Google Cloud CLI e librerie client. La mutazione degli elementi del grafo include l'inserimento, l'aggiornamento e l'eliminazione di nodi ed elementi in Spanner Graph.

Il grafico Spanner mappa i dati dalle tabelle ai nodi e ai bordi del grafico. Modificare 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, oppure il Librerie client di Spanner per inserire righe nelle tabelle di nodi o perimetrali.

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

Prima di inserire un perimetro, assicurati che i nodi di origine e di destinazione connesse dal perimetro. 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, vedi Il nodo di origine mancante viola la relazione INTERLEAVE IN e Il nodo di destinazione mancante viola un vincolo di chiave esterna.

I seguenti esempi inseriscono Account nodi e Transfer archi all'interno 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:

-- 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. Eseguire le 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 margini

Per aggiornare nodi o archi esistenti, utilizza la classe Google Cloud, gcloud CLI, oppure Librerie client di Spanner.

Puoi aggiornare nodi o edge esistenti utilizzando un prompt GoogleSQL Data Manipulation Language (DML) un'istruzione DML oppure query su Spanner Graph 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 nella 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 l'interfaccia a riga di comando gcloud.
2. In gcloud CLI, esegui questi 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

Gli esempi seguenti aggiornano un nodo Account e un bordo Transfer nella utilizzando le query Grafico di Spanner con DML:

Console

1. Esegui istruzioni nella console Google Cloud.

2. Nella console Google Cloud, inserisci il seguente grafico 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 l'interfaccia a riga di comando gcloud.
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 Console Google Cloud, gcloud CLI o Librerie client di Spanner.

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

Prima di eliminare un nodo, assicurati che non esistano bordi che facciano riferimento al nodo. Se esistono questi tipi di periferiche, potresti ricevere una violazione dell'integrità referenziale errori. Per ulteriori informazioni, vedi Il bordo in uscita orfano viola la relazione genitore-figlio e Il perimetro in entrata orfano viola la relazione genitore-figlio.

I seguenti esempi eliminano un perimetro Transfer e un nodo Account dall' 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. Eseguire le 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 del grafico Spanner con la tua istruzione DML, come mostrato in 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 i bordi del grafico utilizzando il metodo ELIMINAZIONE A CASCATA un'azione.
• Elimina automaticamente i nodi e gli archi nel grafo utilizzando il criterio TTL. Per ulteriori informazioni, vedi TTL su nodi e Edge.

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.
• Scopri le best practice per l'ottimizzazione delle query del grafico di Spanner.