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

const fs = require('fs'); const crypto = require('crypto'); const { BigQuery } =…

  1. User

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

    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 \PROJECTID.{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, processedIds) { 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 = 50; let batchRows = []; let processedCount = 0;

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

    if (processedIds.has(painPoint.id)) {
      continue; // Skip already processed pain points
    }
    
    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);
        processedCount++;
      } else {
        console.error('Unexpected response structure:', result);
      }
    } catch (error) {
      console.error(`Error processing item ${processedCount}:`, painPoint.point, error);
    }
    
    if (batchRows.length === batchSize || i === totalPoints - 1) {
      try {
        await table.insert(batchRows);
        console.log(`Inserted batch. Total processed: ${processedCount}`);
        batchRows = []; // Clear batch
      } catch (error) {
        console.error('Error inserting batch into BigQuery:', error);
        // Optionally implement retry logic here
      }
      await delay(400); // 1 second delay after every batch
    }
    

    }

    console.log(Completed processing ${processedCount} new 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 \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 * FROM ML.PCA((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} total pain points);

    const processedIds = await getProcessedPainPointIds();
    console.log(`Found ${processedIds.size} previously processed pain points`);
    
    const unprocessedCount = painPoints.length - processedIds.size;
    console.log(`${unprocessedCount} pain points remain to be processed`);
    
    if (unprocessedCount > 0) {
      await generateEmbeddingsAndStore(painPoints, processedIds);
    } 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();

    The code above is producing the error: Processing clusters in BigQuery... Clustering completed in BigQuery Preparing data for visualization... An error occurred: Table-valued function not found: ML.PCA at [14:12]

    Find the reason for the error.

  2. Assistant

    The error you're encountering stems from the use of an incorrect function name in your BigQuery SQL query. Specifically, BigQuery does not have a function named ML.PCA; instead, the correct function name for performing principal component analysis in BigQuery ML is ML.PRINCIPAL_COMPONENTS.

    Explanation:

    • Incorrect Function Name: In your SQL query within the prepareVisualizationData function, you used ML.PCA:

      SELECT 
        *
      FROM ML.PCA((SELECT embedding FROM pca_data), STRUCT(2 AS num_dim))
      

      However, BigQuery ML's function for principal component analysis is actually named ML.PRINCIPAL_COMPONENTS, not ML.PCA.

    • Correct Function Usage: You should replace ML.PCA with ML.PRINCIPAL_COMPONENTS. Additionally, the parameter for specifying the number of principal components is num_components, not num_dim.

      Here is the corrected SQL 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.PRINCIPAL_COMPONENTS(
          (SELECT embedding FROM pca_data),
          STRUCT(2 AS num_components)
        )
      )
      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;
      

    Solution:

    • Update the Function Name: Change ML.PCA to ML.PRINCIPAL_COMPONENTS in your SQL query.

    • Correct the Parameter Name: Use num_components instead of num_dim as the parameter name for specifying the number of principal components.

    • Modified Query:

      SELECT 
        *
      FROM ML.PRINCIPAL_COMPONENTS((SELECT embedding FROM pca_data), STRUCT(2 AS num_components))
      

    By making these changes, your SQL query should run successfully without the "Table-valued function not found" error.

    Revised Code for prepareVisualizationData:

    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.PRINCIPAL_COMPONENTS(
            (SELECT embedding FROM pca_data),
            STRUCT(2 AS num_components)
          )
        )
        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");
    }
    

    Summary:

    The error occurs because you're calling a non-existent function ML.PCA. By correcting the function name to ML.PRINCIPAL_COMPONENTS and fixing the parameter name to num_components, you will resolve the error and enable the query to execute successfully.