#4_run_queries.js

// Imports the Google Cloud client library
const { BigQuery } = require('@google-cloud/bigquery');

// ------------------------------------------------
// TODO: Developer, make sure to add your own values here!
const projectId = 'YOUR_GCP_PROJECT_ID';
const datasetId = 'YOUR_DATASET_ID';
const keyFilename = 'YOUR_KEYFILE.json';
const datasetLocation = 'US';
// ------------------------------------------------


// Creates a client
const bigquery = new BigQuery({
    keyFilename,
    projectId
});

/**
 * Run query on BigQuery
 * @param {string} query The query to run
 * @param {string} location The location of the dataset
 * @param {Object} printConfig Configuraiton regarding printing
 * @param {boolean} printConfig.statistics If statistics should be printed
 * @param {boolean} printConfig.result If results should be printed
 * @returns {Promise<void>}
 */
const runQuery = async (query, location, printConfig) => {
    const options = {
        query,
        // Location must match that of the dataset(s) referenced in the query.
        location,
        // Normally this should be kept to 'true' but we set it to false 
        // to be able to measure everything correctly
        useQueryCache: false,
    };

    // Run the query as a job
    let [job] = await bigquery.createQueryJob(options);

    // Wait for the query to finish
    let result = await job.getQueryResults();

    if (printConfig?.result) {
        console.log(result);
    }

    if (printConfig?.statistics) {
        // Getting fresh job data and print them
        [job] = await job.get();
        printJobStatistics(job);
    }
}

/**
 * Takes a BigQuery job and print certain statistical values to the console
 * @param {Job} job The BigQuery job to analyze
 */
const printJobStatistics = (job) => {
    const statistics = job.metadata.statistics;
    const timeTaken = statistics.endTime - statistics.startTime;
    const totalBytesProcessed = parseInt(statistics.totalBytesProcessed);
    const totalBytesBilled = parseInt(statistics.query.totalBytesBilled);
    const totalPartitionsProcessed = parseInt(statistics.query.totalPartitionsProcessed);

    const rowsRead = parseInt(statistics.query.queryPlan[0].recordsRead);

    console.log(`--- Job Statistics ---`);
    console.table([
        { Description: 'Cache hit', Value: statistics.query.cacheHit, Unit: 'Bool' },
        { Description: 'Time taken', Value: timeTaken, Unit: 'ms' },
        { Description: 'Partitions processed', Value: totalPartitionsProcessed, Unit: 'Count' },
        { Description: 'Rows read', Value: rowsRead, Unit: 'Count' },
        { Description: 'Bytes processed', Value: totalBytesProcessed, Unit: 'Bytes' },
        { Description: 'Bytes billed', Value: totalBytesBilled, Unit: 'Bytes' },
    ]);
    console.log(`-------------------------------\n`);
}

const main = async () => {
    const name = '1';

    const createQuery = (tableId) => `
        SELECT *
        FROM \`${projectId}.${datasetId}.${tableId}\`
        WHERE name = "${name}"
        LIMIT 1
    `;

    const createCountQuery = (tableId) => `
        SELECT count(*) as count
        FROM \`${projectId}.${datasetId}.${tableId}\`
        WHERE name = "${name}"
        LIMIT 10
    `;

    console.log(`--- Without Clustering ---`);
    await runQuery(
        createQuery('non_clustered_table'),
        datasetLocation,
        { statistics: true }
    );

    console.log(`--- With Clustering ---`);
    await runQuery(
        createQuery('my_clustered_table'),
        datasetLocation,
        { statistics: true }
    );

    console.log(`--- Count 'name = ${name}' ---`);
    await runQuery(
        createCountQuery('my_clustered_table'),
        datasetLocation,
        { result: true }
    )
}
main();