Skip to content
The Chat Archive
o1-preview-2024-09-12· OpenAI WildChat-4.8M (AI2)

const fs = require('fs'); const { BigQuery } = require('@google-cloud/bigquery'); const…

  1. User

    const fs = require('fs'); const { BigQuery } = require('@google-cloud/bigquery'); const { GoogleGenerativeAI } = require('@google/generative-ai');

    const PROJECT_ID = 'hidden'; const LOCATION = 'us-central1'; const DATASET_ID = 'hidden'; const EMBEDDINGS_TABLE_ID = 'embeddings'; const CLUSTERS_TABLE_ID = 'clusters'; const VISUALIZATION_TABLE_ID = 'visualization_data';

    async function loadPainPoints() { console.log("Loading pain points..."); const data = fs.readFileSync('pain_points_results.json', 'utf8'); const results = JSON.parse(data);

    const painPoints = []; for (const demographic in results) { for (const environment in results[demographic]) { const points = results[demographic][environment].pain_points; points.forEach(point => { painPoints.push({ demographic, environment, point }); }); } } console.log(Total pain points loaded: ${painPoints.length}); return painPoints; }

    const delay = (ms) => new Promise(resolve => setTimeout(resolve, ms));

    async function generateEmbeddings(painPoints) { console.log("Generating embeddings..."); const genAI = new GoogleGenerativeAI(process.env.API_KEY); const model = genAI.getGenerativeModel({ model: "embedding-001" });

    const embeddings = []; const totalPoints = painPoints.length;

    for (let i = 0; i < totalPoints; i++) { const point = painPoints[i].point;

    try {
      const result = await model.embedContent(point);
      if (result && result.embedding) {
        embeddings.push(result.embedding.values);
      } else {
        console.error('Unexpected response structure:', result);
      }
    } catch (error) {
      console.error(`Error processing item ${i}:`, point, error);
    }
    
    if (i % 10 === 0) {
      console.log(`Processed ${i + 1} / ${totalPoints} pain points`);
      await delay(1000); // 1 second delay every 10 items
    }
    

    }

    console.log(Completed processing ${embeddings.length} / ${totalPoints} pain points); return embeddings; }

    async function storeEmbeddingsInBigQuery(embeddings, painPoints) { console.log("Storing embeddings in BigQuery..."); const bigquery = new BigQuery({ projectId: PROJECT_ID });

    const dataset = bigquery.dataset(DATASET_ID); const table = dataset.table(EMBEDDINGS_TABLE_ID);

    const rows = embeddings.map((embedding, index) => ({ id: index, demographic: painPoints[index].demographic, environment: painPoints[index].environment, point: painPoints[index].point, embedding: embedding, }));

    await table.insert(rows); console.log(Stored ${rows.length} rows in BigQuery); }

    async function processClustersInBigQuery(numClusters = 100) { console.log("Processing clusters in BigQuery..."); const bigquery = new BigQuery({ projectId: PROJECT_ID });

    const query = CREATE OR REPLACE MODEL \PROJECTID.{PROJECT_ID}.{DATASET_ID}.kmeans_model` OPTIONS(model_type='kmeans', num_clusters={numClusters}) AS SELECT embedding FROM \`{PROJECT_ID}.DATASETID.{DATASET_ID}.{EMBEDDINGS_TABLE_ID}`;

    CREATE OR REPLACE TABLE \`${PROJECT_ID}.${DATASET_ID}.${CLUSTERS_TABLE_ID}\` AS
    SELECT id, demographic, environment, point, CENTROID_ID AS cluster
    FROM ML.PREDICT(MODEL \`${PROJECT_ID}.${DATASET_ID}.kmeans_model\`,
      (SELECT id, demographic, environment, point, embedding
       FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\`));
    

    `;

    const options = { query: query, location: LOCATION, };

    await bigquery.query(options); console.log("Clustering completed in BigQuery"); }

    async function prepareVisualizationData() { console.log("Preparing data for visualization..."); const bigquery = new BigQuery({ projectId: PROJECT_ID });

    const query = CREATE OR REPLACE TABLE \PROJECTID.{PROJECT_ID}.{DATASET_ID}.{VISUALIZATION_TABLE_ID}\` AS WITH pca_data AS ( SELECT c.*, e.embedding FROM \`{PROJECT_ID}.DATASETID.{DATASET_ID}.{CLUSTERS_TABLE_ID}` c JOIN `PROJECTID.{PROJECT_ID}.{DATASET_ID}.${EMBEDDINGS_TABLE_ID}` e ON c.id = e.id ), pca_components AS ( SELECT SVD(ARRAY_AGG(STRUCT(embedding))) AS svd_result FROM pca_data ) SELECT p.id, p.demographic, p.environment, p.point, p.cluster, pc.svd_result[OFFSET(0)].left_singular_vectors[OFFSET(0)].* AS pca_1, pc.svd_result[OFFSET(0)].left_singular_vectors[OFFSET(1)].* AS pca_2 FROM pca_data p CROSS JOIN pca_components pc; `;

    const options = { query: query, location: LOCATION, };

    await bigquery.query(options); console.log("Visualization data prepared in BigQuery"); }

    async function main() { try { const painPoints = await loadPainPoints(); console.log(Loaded ${painPoints.length} pain points);

    const embeddings = await generateEmbeddings(painPoints);
    console.log(`Generated ${embeddings.length} embeddings`);
    
    await storeEmbeddingsInBigQuery(embeddings, painPoints);
    await processClustersInBigQuery();
    await prepareVisualizationData();
    
    console.log("Process completed successfully. Data is ready for visualization in Looker Studio.");
    

    } catch (error) { console.error(An error occurred: ${error.message}); } }

    main();


    Modify the script above so that the embeddings are not even stored in the local machine but sent for cloud storage as soon as possible. If temporarely saved locally, the embeddings should be excluded. Also, consider how to make this execution safer, since its going to take a while and if it fails at some point, restarting from the beggining is a pain in the ass, so reliability is important

  2. Assistant

    Certainly! I've modified your script to address your requirements:

    1. Embeddings are not stored locally and are sent to cloud storage (BigQuery) as soon as possible. Embeddings are not saved temporarily on the local machine.

    2. Made the execution safer by implementing progress tracking and ensuring that if the script fails, it can resume from where it left off without starting over.

    Here is the modified script:

    const fs = require('fs');
    const crypto = require('crypto');
    const { BigQuery } = require('@google-cloud/bigquery');
    const { GoogleGenerativeAI } = require('@google/generative-ai');
    
    const PROJECT_ID = 'hidden';
    const LOCATION = 'us-central1';
    const DATASET_ID = 'hidden';
    const EMBEDDINGS_TABLE_ID = 'embeddings';
    const CLUSTERS_TABLE_ID = 'clusters';
    const VISUALIZATION_TABLE_ID = 'visualization_data';
    
    function hashPainPoint(painPoint) {
      const hash = crypto.createHash('sha256');
      hash.update(painPoint.demographic + painPoint.environment + painPoint.point);
      return hash.digest('hex');
    }
    
    async function loadPainPoints() {
      console.log("Loading pain points...");
      const data = fs.readFileSync('pain_points_results.json', 'utf8');
      const results = JSON.parse(data);
    
      const painPoints = [];
      for (const demographic in results) {
        for (const environment in results[demographic]) {
          const points = results[demographic][environment].pain_points;
          points.forEach(point => {
            const id = hashPainPoint({ demographic, environment, point });
            painPoints.push({
              id,
              demographic,
              environment,
              point
            });
          });
        }
      }
      console.log(`Total pain points loaded: ${painPoints.length}`);
      return painPoints;
    }
    
    const delay = (ms) => new Promise(resolve => setTimeout(resolve, ms));
    
    async function getProcessedPainPointIds() {
      console.log("Fetching list of processed pain points from BigQuery...");
      const bigquery = new BigQuery({ projectId: PROJECT_ID });
      const query = `SELECT id FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\``;
      const options = {
        query,
        location: LOCATION,
      };
    
      const [rows] = await bigquery.query(options);
      const processedIds = new Set(rows.map(row => row.id));
      console.log(`Fetched ${processedIds.size} processed pain point IDs.`);
      return processedIds;
    }
    
    async function generateEmbeddingsAndStore(painPoints) {
      console.log("Generating embeddings and storing in BigQuery...");
      const genAI = new GoogleGenerativeAI(process.env.API_KEY);
      const model = genAI.getGenerativeModel({ model: "embedding-001" });
      const bigquery = new BigQuery({ projectId: PROJECT_ID });
      const dataset = bigquery.dataset(DATASET_ID);
      const table = dataset.table(EMBEDDINGS_TABLE_ID);
    
      const totalPoints = painPoints.length;
      const batchSize = 10;
      let batchRows = [];
    
      for (let i = 0; i < totalPoints; i++) {
        const painPoint = painPoints[i];
    
        try {
          const result = await model.embedContent(painPoint.point);
          if (result && result.embedding) {
            const row = {
              id: painPoint.id,
              demographic: painPoint.demographic,
              environment: painPoint.environment,
              point: painPoint.point,
              embedding: result.embedding.values,
            };
            batchRows.push(row);
          } else {
            console.error('Unexpected response structure:', result);
          }
        } catch (error) {
          console.error(`Error processing item ${i}:`, painPoint.point, error);
        }
    
        if ((i + 1) % batchSize === 0 || i === totalPoints - 1) {
          try {
            await table.insert(batchRows);
            batchRows = []; // Clear batch
            console.log(`Inserted batch up to item ${i + 1} into BigQuery`);
          } catch (error) {
            console.error('Error inserting batch into BigQuery:', error);
            // Optionally implement retry logic here
          }
          await delay(1000); // 1 second delay after every batch
        }
      }
    
      console.log(`Completed processing ${totalPoints} pain points`);
    }
    
    async function processClustersInBigQuery(numClusters = 100) {
      console.log("Processing clusters in BigQuery...");
      const bigquery = new BigQuery({ projectId: PROJECT_ID });
    
      const query = `
        CREATE OR REPLACE MODEL \`${PROJECT_ID}.${DATASET_ID}.kmeans_model\`
        OPTIONS(model_type='kmeans', num_clusters=${numClusters}) AS
        SELECT embedding
        FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\`;
    
        CREATE OR REPLACE TABLE \`${PROJECT_ID}.${DATASET_ID}.${CLUSTERS_TABLE_ID}\` AS
        SELECT id, demographic, environment, point, CENTROID_ID AS cluster
        FROM ML.PREDICT(MODEL \`${PROJECT_ID}.${DATASET_ID}.kmeans_model\`,
          (SELECT id, demographic, environment, point, embedding
           FROM \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\`));
      `;
    
      const options = {
        query: query,
        location: LOCATION,
      };
    
      await bigquery.query(options);
      console.log("Clustering completed in BigQuery");
    }
    
    async function prepareVisualizationData() {
      console.log("Preparing data for visualization...");
      const bigquery = new BigQuery({ projectId: PROJECT_ID });
    
      const query = `
        CREATE OR REPLACE TABLE \`${PROJECT_ID}.${DATASET_ID}.${VISUALIZATION_TABLE_ID}\` AS
        WITH pca_data AS (
          SELECT 
            c.*,
            e.embedding
          FROM \`${PROJECT_ID}.${DATASET_ID}.${CLUSTERS_TABLE_ID}\` c
          JOIN \`${PROJECT_ID}.${DATASET_ID}.${EMBEDDINGS_TABLE_ID}\` e
          ON c.id = e.id
        ),
        pca_components AS (
          SELECT 
            *
          FROM ML.PCA(MODEL(SELECT embedding FROM pca_data), STRUCT(2 AS num_dim))
        )
        SELECT
          p.id,
          p.demographic,
          p.environment,
          p.point,
          p.cluster,
          pc.dimension_1 AS pca_1,
          pc.dimension_2 AS pca_2
        FROM pca_data p
        JOIN pca_components pc
        ON p.id = pc.id;
      `;
    
      const options = {
        query: query,
        location: LOCATION,
      };
    
      await bigquery.query(options);
      console.log("Visualization data prepared in BigQuery");
    }
    
    async function main() {
      try {
        const painPoints = await loadPainPoints();
        console.log(`Loaded ${painPoints.length} pain points`);
    
        const processedIds = await getProcessedPainPointIds();
        const unprocessedPainPoints = painPoints.filter(p => !processedIds.has(p.id));
    
        console.log(`Found ${unprocessedPainPoints.length} unprocessed pain points`);
    
        if (unprocessedPainPoints.length > 0) {
          await generateEmbeddingsAndStore(unprocessedPainPoints);
        } else {
          console.log("All pain points have already been processed.");
        }
    
        await processClustersInBigQuery();
        await prepareVisualizationData();
    
        console.log("Process completed successfully. Data is ready for visualization in Looker Studio.");
      } catch (error) {
        console.error(`An error occurred: ${error.message}`);
      }
    }
    
    main();
    

    Key Modifications:

    1. Streaming Embeddings to BigQuery:

      • Embeddings are generated and immediately sent to BigQuery without being stored locally.
      • Introduced the generateEmbeddingsAndStore function, which processes pain points one by one, generates embeddings, and inserts them into BigQuery in batches.
    2. Progress Tracking and Reliability:

      • Implemented an id for each pain point using a SHA-256 hash to uniquely identify it.
      • Before processing, the script fetches already processed pain point IDs from BigQuery using getProcessedPainPointIds.
      • Filters out already processed pain points to avoid reprocessing.
      • Errors during processing are caught and logged, preventing the script from crashing.
      • Batches are inserted into BigQuery, and the script logs progress, making it easier to resume if interrupted.
    3. No Local Storage of Embeddings:

      • Embeddings are not stored on disk or in any local temporary storage.
      • Only minimal in-memory storage is used for batch processing, which is acceptable as per your requirements.
    4. Safer Execution and Easy Recovery:

      • If the script fails or is interrupted, you can rerun it without starting over.
      • The script will skip any pain points that have already been processed.
    5. Improved PCA Calculation:

      • Adjusted the prepareVisualizationData function to correctly perform PCA using BigQuery ML's PCA functionality.

    Notes:

    • Batch Size: Adjusted batch size to 10 for efficient inserts without consuming too much memory.
    • Error Handling: Errors during embedding generation or insertion into BigQuery are caught and logged.
    • Delays: Introduced a delay after each batch to comply with rate limits and prevent overwhelming the API.

    Recommendation:

    • Retry Logic: You may want to implement retry logic for failed API calls or BigQuery insertions for increased robustness.
    • Monitoring and Logging: Consider integrating a logging system or monitoring tool to track the script's progress and handle any issues promptly.

    This modified script should meet your requirements by sending embeddings to cloud storage immediately and ensuring reliable execution that can recover smoothly from interruptions.