const fs = require('fs'); const crypto = require('crypto'); const { BigQuery } =…
- 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 \{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 \{DATASET_ID}.kmeans_model` OPTIONS(model_type='kmeans', num_clusters={numClusters}) AS SELECT embedding FROM \`{PROJECT_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 \{DATASET_ID}.{VISUALIZATION_TABLE_ID}\` AS WITH pca_data AS ( SELECT c.*, e.embedding FROM \`{PROJECT_ID}.{CLUSTERS_TABLE_ID}` c JOIN `{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.
- 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 isML.PRINCIPAL_COMPONENTS.Explanation:
-
Incorrect Function Name: In your SQL query within the
prepareVisualizationDatafunction, you usedML.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, notML.PCA. -
Correct Function Usage: You should replace
ML.PCAwithML.PRINCIPAL_COMPONENTS. Additionally, the parameter for specifying the number of principal components isnum_components, notnum_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.PCAtoML.PRINCIPAL_COMPONENTSin your SQL query. -
Correct the Parameter Name: Use
num_componentsinstead ofnum_dimas 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 toML.PRINCIPAL_COMPONENTSand fixing the parameter name tonum_components, you will resolve the error and enable the query to execute successfully. -