Gestisci i dati di Spanner Graph

Questo documento mostra come gestire i dati in un grafico di Spanner Graph inserendo, aggiornando ed eliminando nodi e archi. Spanner Graph mappa i dati delle tabelle ai nodi e agli archi del grafico. Per modificare i dati in un grafico, devi modificare i dati nelle tabelle di input corrispondenti. Per gestire i dati del grafico, puoi utilizzare la console Google Cloud , Google Cloud CLI o le librerie client di Spanner.

Configura il grafico di Spanner Graph

Per poter gestire i dati di un grafico di Spanner Graph, devi prima configurare il grafico nel modo seguente:

Crea un'istanza di Spanner.
Crea un database nella tua istanza di Spanner.
Inserisci i dati del grafico nel database.

Gli esempi riportati in queste sezioni utilizzano l'istanza e il database che hai creato quando hai configurato il grafico di Spanner Graph seguendo i passaggi precedenti.

Inserisci nodi o archi

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

Nella console Google Cloud e in gcloud CLI, puoi utilizzare il Data Manipulation Language (DML) di GoogleSQL. Nella libreria client di Spanner, puoi utilizzare il DML o le API di mutazione.

Prima di inserire un arco, assicurati che esistano i nodi di origine e di destinazione collegati dall'arco. Se inserisci un arco quando il nodo di origine o di destinazione collegato dall'arco non esiste, potresti ricevere errori di violazione dell'integrità referenziale. Per saperne di più, consulta Il nodo di origine mancante viola la relazione INTERLEAVE IN e Il nodo di destinazione mancante viola il vincolo di chiave esterna.

Gli esempi riportati di seguito inseriscono nodi Account e archi Transfer nel database che hai creato in Configura il grafico di Spanner Graph.

Console

Nella console Google Cloud , esegui l'istruzione DML seguente. Per saperne di più, consulta Esegui istruzioni nella console Google Cloud .

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

In gcloud CLI, esegui i comandi seguenti. Per saperne di più, consulta Esegui istruzioni con gcloud CLI.

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

Sostituisci quanto segue:

DATABASE_NAME: il nome del database.
INSTANCE_NAME: il nome dell'istanza.

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

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

Aggiorna nodi o archi

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

Puoi aggiornare nodi o archi esistenti utilizzando un'istruzione Data Manipulation Language (DML) di GoogleSQL oppure query Spanner Graph con un'istruzione DML. Se utilizzi una libreria client di Spanner, puoi usare anche le API di mutazione.

Aggiorna nodi o archi con DML

I seguenti esempi utilizzano DML per aggiornare il nodo Account e l'arco Transfer che hai aggiunto nel passaggio Inserisci nodi o archi.

Console

Nella console Google Cloud , esegui l'istruzione DML seguente. Per saperne di più, consulta Esegui istruzioni nella console Google Cloud .

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

Esegui le istruzioni con gcloud CLI.
In gcloud CLI, esegui i comandi seguenti:

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"

Sostituisci quanto segue:

DATABASE_NAME: il nome del database.
INSTANCE_NAME: il nome dell'istanza.

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

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

Aggiorna nodi o archi con query Spanner Graph e DML

Gli esempi seguenti utilizzano query Spanner Graph con DML per aggiornare il nodo Account e l'arco Transfer che hai aggiunto nel passaggio Inserisci nodi o archi.

Console

Nella console Google Cloud , esegui l'istruzione DML seguente. Per saperne di più, consulta Esegui istruzioni nella console Google Cloud .

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

In gcloud CLI, esegui i comandi seguenti. Per saperne di più, consulta Esegui istruzioni con gcloud CLI.

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

Sostituisci quanto segue:

DATABASE_NAME: il nome del database.
INSTANCE_NAME: il nome dell'istanza.

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

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

Elimina nodi o archi

Per eliminare nodi o archi esistenti, utilizza la console Google Cloud , gcloud CLI o le librerie client di Spanner.

Nella console Google Cloud e in gcloud CLI, utilizza il Data Manipulation Language (DML) di GoogleSQL per eliminare questi elementi. Nella libreria client di Spanner, puoi utilizzare il DML o le API di mutazione per eliminare nodi o archi.

Per evitare errori di violazione dell'integrità referenziale, assicurati che nessun arco faccia riferimento a un nodo quando elimini quest'ultimo. Per saperne di più, consulta Un arco in uscita orfano viola la relazione padre-figlio e Un arco in entrata orfano viola la relazione padre-figlio.

Gli esempi seguenti eliminano un arco Transfer e un nodo Account dal grafico.

Console

Nella console Google Cloud , esegui l'istruzione DML seguente. Per saperne di più, consulta Esegui istruzioni nella console Google Cloud .

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

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

gcloud

In gcloud CLI, esegui i comandi seguenti. Per saperne di più, consulta Esegui istruzioni con gcloud CLI.

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"

Sostituisci quanto segue:

DATABASE_NAME: il nome del database.
INSTANCE_NAME: il nome dell'istanza.

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

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

Puoi combinare le query 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
  }

Operazioni sui dati automatizzate e collettive

Oltre a utilizzare DML per inserire, aggiornare ed eliminare singoli nodi e archi, puoi anche utilizzare i seguenti metodi per gestire i dati di Spanner Graph:

Puoi automatizzare l'eliminazione degli archi in un grafico utilizzando l'azione ON DELETE CASCADE.
Puoi automatizzare l'eliminazione di nodi e archi nel grafico utilizzando una policy TTL. Per saperne di più, consulta TTL su nodi e archi.
Per eseguire l'aggiornamento e l'eliminazione collettivi di nodi e archi nel grafico in modo efficiente, utilizza il DML partizionato.

Passaggi successivi

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

Gestisci i dati di Spanner Graph Mantieni tutto organizzato con le raccolte Salva e classifica i contenuti in base alle tue preferenze.

Configura il grafico di Spanner Graph

Inserisci nodi o archi

Console

gcloud

Librerie client

Python

Java

Go

C++

Aggiorna nodi o archi

Aggiorna nodi o archi con DML

Console

gcloud

Librerie client

Python

Java

Go

C++

Aggiorna nodi o archi con query Spanner Graph e DML

Console

gcloud

Librerie client

Python

Java

Go

C++

Elimina nodi o archi

Console

gcloud

Librerie client

Python

Java

Go

C++

Operazioni sui dati automatizzate e collettive

Passaggi successivi

Gestisci i dati di Spanner Graph