Spanner 使用入门 (Node.js)

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  // Creates a client const spanner = new Spanner({projectId});  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  // The query to execute const query = {   sql: 'SELECT 1', };  // Execute a simple SQL statement const [rows] = await database.run(query); console.log(`Query: ${rows.length} found.`); rows.forEach(row => console.log(row));

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  database.runTransaction(async (err, transaction) => {   if (err) {     console.error(err);     return;   }   try {     const [rowCount] = await transaction.runUpdate({       sql: `INSERT Singers (SingerId, FirstName, LastName) VALUES       (12, 'Melissa', 'Garcia'),       (13, 'Russell', 'Morales'),       (14, 'Jacqueline', 'Long'),       (15, 'Dylan', 'Shaw')`,     });     console.log(`${rowCount} records inserted.`);     await transaction.commit();   } catch (err) {     console.error('ERROR:', err);   } finally {     // Close the database when finished.     database.close();   } }); 

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  // Instantiate Spanner table objects const singersTable = database.table('Singers'); const albumsTable = database.table('Albums');  // Inserts rows into the Singers table // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so // they must be converted to strings before being inserted as INT64s try {   await singersTable.insert([     {SingerId: '1', FirstName: 'Marc', LastName: 'Richards'},     {SingerId: '2', FirstName: 'Catalina', LastName: 'Smith'},     {SingerId: '3', FirstName: 'Alice', LastName: 'Trentor'},     {SingerId: '4', FirstName: 'Lea', LastName: 'Martin'},     {SingerId: '5', FirstName: 'David', LastName: 'Lomond'},   ]);    await albumsTable.insert([     {SingerId: '1', AlbumId: '1', AlbumTitle: 'Total Junk'},     {SingerId: '1', AlbumId: '2', AlbumTitle: 'Go, Go, Go'},     {SingerId: '2', AlbumId: '1', AlbumTitle: 'Green'},     {SingerId: '2', AlbumId: '2', AlbumTitle: 'Forever Hold your Peace'},     {SingerId: '2', AlbumId: '3', AlbumTitle: 'Terrified'},   ]);    console.log('Inserted data.'); } catch (err) {   console.error('ERROR:', err); } finally {   await database.close(); }

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const query = {   sql: 'SELECT SingerId, AlbumId, AlbumTitle FROM Albums', };  // Queries rows from the Albums table try {   const [rows] = await database.run(query);    rows.forEach(row => {     const json = row.toJSON();     console.log(       `SingerId: ${json.SingerId}, AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`,     );   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   await database.close(); }

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const query = {   sql: `SELECT SingerId, FirstName, LastName         FROM Singers WHERE LastName = @lastName`,   params: {     lastName: 'Garcia',   }, };  // Queries rows from the Albums table try {   const [rows] = await database.run(query);    rows.forEach(row => {     const json = row.toJSON();     console.log(       `SingerId: ${json.SingerId}, FirstName: ${json.FirstName}, LastName: ${json.LastName}`,     );   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   database.close(); }

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  // Reads rows from the Albums table const albumsTable = database.table('Albums');  const query = {   columns: ['SingerId', 'AlbumId', 'AlbumTitle'],   keySet: {     all: true,   }, };  try {   const [rows] = await albumsTable.read(query);    rows.forEach(row => {     const json = row.toJSON();     console.log(       `SingerId: ${json.SingerId}, AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`,     );   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   await database.close(); }

 /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  // creates a client const spanner = new Spanner({   projectId: projectId, });  const databaseAdminClient = spanner.getDatabaseAdminClient();  // Creates a new index in the database try {   const [operation] = await databaseAdminClient.updateDatabaseDdl({     database: databaseAdminClient.databasePath(       projectId,       instanceId,       databaseId,     ),     statements: ['ALTER TABLE Albums ADD COLUMN MarketingBudget INT64'],   });    console.log('Waiting for operation to complete...');   await operation.promise();    console.log('Added the MarketingBudget column.'); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the spanner client when finished.   // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.   spanner.close(); } 

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  // Update a row in the Albums table // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they // must be converted to strings before being inserted as INT64s const albumsTable = database.table('Albums');  try {   await albumsTable.update([     {SingerId: '1', AlbumId: '1', MarketingBudget: '100000'},     {SingerId: '2', AlbumId: '2', MarketingBudget: '500000'},   ]);   console.log('Updated data.'); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   database.close(); }

// This sample uses the `MarketingBudget` column. You can add the column // by running the `add_column` sample or by running this DDL statement against // your database: //    ALTER TABLE Albums ADD COLUMN MarketingBudget INT64  // Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const query = {   sql: 'SELECT SingerId, AlbumId, MarketingBudget FROM Albums', };  // Queries rows from the Albums table try {   const [rows] = await database.run(query);    rows.forEach(async row => {     const json = row.toJSON();      console.log(       `SingerId: ${json.SingerId}, AlbumId: ${         json.AlbumId       }, MarketingBudget: ${         json.MarketingBudget ? json.MarketingBudget : null       }`,     );   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   database.close(); }

// This sample transfers 200,000 from the MarketingBudget field // of the second Album to the first Album, as long as the second // Album has enough money in its budget. Make sure to run the // addColumn and updateData samples first (in that order).  // Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const transferAmount = 200000;  database.runTransaction((err, transaction) => {   if (err) {     console.error(err);     return;   }   let firstBudget, secondBudget;   const queryOne = `SELECT MarketingBudget FROM Albums     WHERE SingerId = 2 AND AlbumId = 2`;    const queryTwo = `SELECT MarketingBudget FROM Albums   WHERE SingerId = 1 AND AlbumId = 1`;    Promise.all([     // Reads the second album's budget     transaction.run(queryOne).then(results => {       // Gets second album's budget       const rows = results[0].map(row => row.toJSON());       secondBudget = rows[0].MarketingBudget;       console.log(`The second album's marketing budget: ${secondBudget}`);        // Makes sure the second album's budget is large enough       if (secondBudget < transferAmount) {         throw new Error(           `The second album's budget (${secondBudget}) is less than the transfer amount (${transferAmount}).`,         );       }     }),      // Reads the first album's budget     transaction.run(queryTwo).then(results => {       // Gets first album's budget       const rows = results[0].map(row => row.toJSON());       firstBudget = rows[0].MarketingBudget;       console.log(`The first album's marketing budget: ${firstBudget}`);     }),   ])     .then(() => {       // Transfers the budgets between the albums       console.log(firstBudget, secondBudget);       firstBudget += transferAmount;       secondBudget -= transferAmount;        console.log(firstBudget, secondBudget);        // Updates the database       // Note: Cloud Spanner interprets Node.js numbers as FLOAT64s, so they       // must be converted (back) to strings before being inserted as INT64s.        return transaction         .runUpdate({           sql: `UPDATE Albums SET MarketingBudget = @Budget               WHERE SingerId = 1 and AlbumId = 1`,           params: {             Budget: firstBudget,           },         })         .then(() =>           transaction.runUpdate({             sql: `UPDATE Albums SET MarketingBudget = @Budget                   WHERE SingerId = 2 and AlbumId = 2`,             params: {               Budget: secondBudget,             },           }),         );     })     .then(() => {       // Commits the transaction and send the changes to the database       return transaction.commit();     })     .then(() => {       console.log(         `Successfully executed read-write transaction using DML to transfer ${transferAmount} from Album 2 to Album 1.`,       );     })     .then(() => {       // Closes the database when finished       database.close();     }); });

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  const databaseAdminClient = spanner.getDatabaseAdminClient();  const request = ['CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle)'];  // Creates a new index in the database try {   const [operation] = await databaseAdminClient.updateDatabaseDdl({     database: databaseAdminClient.databasePath(       projectId,       instanceId,       databaseId,     ),     statements: request,   });    console.log('Waiting for operation to complete...');   await operation.promise();    console.log('Added the AlbumsByAlbumTitle index.'); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the spanner client when finished.   // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.   spanner.close(); }

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const albumsTable = database.table('Albums');  const query = {   columns: ['AlbumId', 'AlbumTitle'],   keySet: {     all: true,   },   index: 'AlbumsByAlbumTitle', };  // Reads the Albums table using an index try {   const [rows] = await albumsTable.read(query);    rows.forEach(row => {     const json = row.toJSON();     console.log(`AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`);   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   database.close(); }

// "Storing" indexes store copies of the columns they index // This speeds up queries, but takes more space compared to normal indexes // See the link below for more information: // https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause  // Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  const databaseAdminClient = spanner.getDatabaseAdminClient();  const request = [   'CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget)', ];  // Creates a new index in the database try {   const [operation] = await databaseAdminClient.updateDatabaseDdl({     database: databaseAdminClient.databasePath(       projectId,       instanceId,       databaseId,     ),     statements: request,   });    console.log('Waiting for operation to complete...');   await operation.promise();    console.log('Added the AlbumsByAlbumTitle2 index.'); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the spanner client when finished.   // The databaseAdminClient does not require explicit closure. The closure of the Spanner client will automatically close the databaseAdminClient.   spanner.close(); }

// "Storing" indexes store copies of the columns they index // This speeds up queries, but takes more space compared to normal indexes // See the link below for more information: // https://cloud.google.com/spanner/docs/secondary-indexes#storing_clause  // Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  const albumsTable = database.table('Albums');  const query = {   columns: ['AlbumId', 'AlbumTitle', 'MarketingBudget'],   keySet: {     all: true,   },   index: 'AlbumsByAlbumTitle2', };  // Reads the Albums table using a storing index try {   const [rows] = await albumsTable.read(query);    rows.forEach(row => {     const json = row.toJSON();     let rowString = `AlbumId: ${json.AlbumId}`;     rowString += `, AlbumTitle: ${json.AlbumTitle}`;     if (json.MarketingBudget) {       rowString += `, MarketingBudget: ${json.MarketingBudget}`;     }     console.log(rowString);   }); } catch (err) {   console.error('ERROR:', err); } finally {   // Close the database when finished.   database.close(); }

// Imports the Google Cloud client library const {Spanner} = require('@google-cloud/spanner');  /**  * TODO(developer): Uncomment the following lines before running the sample.  */ // const projectId = 'my-project-id'; // const instanceId = 'my-instance'; // const databaseId = 'my-database';  // Creates a client const spanner = new Spanner({   projectId: projectId, });  // Gets a reference to a Cloud Spanner instance and database const instance = spanner.instance(instanceId); const database = instance.database(databaseId);  // Gets a transaction object that captures the database state // at a specific point in time database.getSnapshot(async (err, transaction) => {   if (err) {     console.error(err);     return;   }   const queryOne = 'SELECT SingerId, AlbumId, AlbumTitle FROM Albums';    try {     // Read #1, using SQL     const [qOneRows] = await transaction.run(queryOne);      qOneRows.forEach(row => {       const json = row.toJSON();       console.log(         `SingerId: ${json.SingerId}, AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`,       );     });      const queryTwo = {       columns: ['SingerId', 'AlbumId', 'AlbumTitle'],     };      // Read #2, using the `read` method. Even if changes occur     // in-between the reads, the transaction ensures that both     // return the same data.     const [qTwoRows] = await transaction.read('Albums', queryTwo);      qTwoRows.forEach(row => {       const json = row.toJSON();       console.log(         `SingerId: ${json.SingerId}, AlbumId: ${json.AlbumId}, AlbumTitle: ${json.AlbumTitle}`,       );     });      console.log('Successfully executed read-only transaction.');   } catch (err) {     console.error('ERROR:', err);   } finally {     transaction.end();     // Close the database when finished.     await database.close();   } });