#5_create_scheduled_query.js

// Imports the Google Cloud client library
const bigqueryDataTransfer = require('@google-cloud/bigquery-data-transfer');

// ------------------------------------------------
// TODO: Developer, make sure to add your own values here!
const projectId = 'GCP_PROJECT_ID';
const datasetId = 'DATASET_ID';
const keyFilename = 'KEY_FILE.json';
const rawDataTable = 'RAW_DATA_TABLE_ID';
const datasetLocation = 'US';
// ------------------------------------------------

// Create the client
const bqdt = new bigqueryDataTransfer.v1.DataTransferServiceClient({
    keyFilename,
    projectId
});

/**
 * The Query to aggregate browser usage per day
 */
const query = `
    SELECT 
        date, 
        browser,
        count(*) as count,
        @run_date AS run_date,
    FROM 
        ${projectId}.${datasetId}.${rawDataTable}
    WHERE 
        DATE( date ) =  DATE( DATE_SUB( @run_date, INTERVAL 1 DAY ) )
    GROUP BY 
        date,
        browser
    LIMIT 
        1000
`;

const createScheduledQuery = async () => {
    // Create project resource name string
    const formattedParent = bqdt.projectPath(projectId);

    // Create scheduled query
    await bqdt.createTransferConfig(
        {
            parent: formattedParent,
            transferConfig: {
                name: 'scheduled-session-aggregation-query',
                destinationDatasetId: datasetId,
                displayName: 'My Session Aggregation',
                dataSourceId: "scheduled_query",
                params: {
                    fields: {
                        query: { stringValue: query },
                        destination_table_name_template: { stringValue: "my_scheduled_table" },
                        write_disposition: { stringValue: "WRITE_APPEND" },
                        partitioning_field: { stringValue: "date" },
                    }
                },
                schedule: "every 24 hours",
                datasetRegion: datasetLocation
            }
        },
        {}
    );
}
createScheduledQuery();