// Copyright 2014 Google LLC

//

// Licensed under the Apache License, Version 2.0 (the "License");

// you may not use this file except in compliance with the License.

// You may obtain a copy of the License at

//

// http://www.apache.org/licenses/LICENSE-2.0

//

// Unless required by applicable law or agreed to in writing, software

// distributed under the License is distributed on an "AS IS" BASIS,

// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

// See the License for the specific language governing permissions and

// limitations under the License.

import {ApiError} from '@google-cloud/common';

import {

DecorateRequestOptions,

GoogleErrorBody,

} from '@google-cloud/common/build/src/util';

import {Storage} from '@google-cloud/storage';

import * as assert from 'assert';

import {describe, it, before, after} from 'mocha';

import * as Big from 'big.js';

import * as fs from 'fs';

import {randomUUID} from 'crypto';

import {Readable} from 'stream';

import {

BigQuery,

Dataset,

Job,

Model,

RowMetadata,

Routine,

Table,

InsertRowsStreamResponse,

QueryOptions,

} from '../src';

import bq from '../src/types';

const bigquery = new BigQuery();

const storage = new Storage();

describe('BigQuery', () => {

const GCLOUD_TESTS_PREFIX = 'nodejs_bq_test';

const minCreationTime = Date.now().toString();

const dataset = bigquery.dataset(generateName('dataset'));

const table = dataset.table(generateName('table'));

const bucket = storage.bucket(generateName('bucket'));

const query = 'SELECT url FROM `publicdata.samples.github_nested` LIMIT 100';

const SCHEMA = [

{

name: 'place',

type: 'GEOGRAPHY',

},

{

name: 'id',

type: 'INTEGER',

},

{

name: 'breed',

type: 'STRING',

},

{

name: 'name',

type: 'STRING',

},

{

name: 'dob',

type: 'TIMESTAMP',

},

{

name: 'around',

type: 'BOOLEAN',

},

{

name: 'buffer',

type: 'BYTES',

},

{

name: 'arrayOfInts',

type: 'INTEGER',

mode: 'REPEATED',

},

{

name: 'recordOfRecords',

type: 'RECORD',

fields: [

{

name: 'records',

type: 'RECORD',

mode: 'REPEATED',

fields: [

{

name: 'record',

type: 'BOOLEAN',

},

],

},

],

},

];

before(async () => {

// Remove buckets created for the tests.

await deleteBuckets();

// Remove datasets created for the tests.

await deleteDatasets();

// Create the test dataset with a label tagging this as a test run.

await dataset.create({labels: {[GCLOUD_TESTS_PREFIX]: ''}});

// Create the test table.

await table.create({schema: SCHEMA});

// Create a Bucket.

await bucket.create();

});

after(async () => {

await Promise.all([

// Remove buckets created for the tests.

deleteBuckets(),

// Remove datasets created for the tests.

deleteDatasets(),

]);

});

it('should get a list of datasets', done => {

bigquery.getDatasets((err, datasets) => {

assert(datasets!.length > 0);

assert(datasets![0] instanceof Dataset);

done();

});

});

it('should allow limiting API calls', done => {

const maxApiCalls = 1;

let numRequestsMade = 0;

const bigquery = new BigQuery();

// eslint-disable-next-line @typescript-eslint/no-explicit-any

(bigquery as any).interceptors.push({

request: (reqOpts: DecorateRequestOptions) => {

numRequestsMade++;

return reqOpts;

},

});

bigquery.getDatasets({maxApiCalls}, err => {

assert.ifError(err);

assert.strictEqual(numRequestsMade, 1);

done();

});

});

it('should return a promise', () => {

return bigquery.getDatasets().then(data => {

const datasets = data[0];

assert(datasets.length > 0);

assert(datasets[0] instanceof Dataset);

});

});

it('should allow limiting API calls via promises', () => {

const maxApiCalls = 1;

let numRequestsMade = 0;

const bigquery = new BigQuery();

// eslint-disable-next-line @typescript-eslint/no-explicit-any

(bigquery as any).interceptors.push({

request: (reqOpts: DecorateRequestOptions) => {

numRequestsMade++;

return reqOpts;

},

});

return bigquery

.getDatasets({

maxApiCalls,

})

.then(() => {

assert.strictEqual(numRequestsMade, maxApiCalls);

});

});

it('should allow for manual pagination in promise mode', async () => {

const [datasets, , apiResponse] = await bigquery.getDatasets({

autoPaginate: false,

filter: `labels.${GCLOUD_TESTS_PREFIX}`,

});

assert(datasets[0] instanceof Dataset);

assert(apiResponse);

});

it('should list datasets as a stream', done => {

let datasetEmitted = false;

bigquery

.getDatasetsStream()

.on('error', done)

.on('data', dataset => {

datasetEmitted = dataset instanceof Dataset;

})

.on('end', () => {

assert.strictEqual(datasetEmitted, true);

done();

});

});

it('should run a query job, then get results', done => {

bigquery.createQueryJob(query, (err, job) => {

assert.ifError(err);

assert(job instanceof Job);

job!.getQueryResults((err, rows) => {

assert.ifError(err);

assert.strictEqual(rows!.length, 100);

assert.strictEqual(typeof rows![0].url, 'string');

done();

});

});

});

it('should run a query job as a promise', () => {

let job: Job;

return bigquery

.createQueryJob(query)

.then(response => {

job = response[0];

return job.promise();

})

.then(() => {

return job.getQueryResults();

})

.then(response => {

const rows = response[0];

assert.strictEqual(rows!.length, 100);

assert.strictEqual(typeof rows[0].url, 'string');

});

});

it('should get query results as a stream', done => {

bigquery.createQueryJob(query, (err, job) => {

assert.ifError(err);

const rowsEmitted = new Array<RowMetadata>();

job!

.getQueryResultsStream()

.on('error', done)

.on('data', row => rowsEmitted.push(row))

.on('end', () => {

assert.strictEqual(rowsEmitted.length, 100);

assert.strictEqual(typeof rowsEmitted[0].url, 'string');

done();

});

});

});

it('should honor the job prefix option', done => {

const options = {

query,

jobPrefix: 'hi-im-a-prefix',

};

bigquery.createQueryJob(options, (err, job) => {

assert.ifError(err);

assert.strictEqual(job!.id!.indexOf(options.jobPrefix), 0);

job!.getQueryResults((err, rows) => {

assert.ifError(err);

assert.strictEqual(rows!.length, 100);

assert.strictEqual(typeof rows![0].url, 'string');

done();

});

});

});

it('should honor the job id option', done => {

const jobId = `hi-im-a-job-id-${randomUUID()}`;

const options = {query, jobId};

bigquery.createQueryJob(options, (err, job) => {

assert.ifError(err);

assert.strictEqual(job!.id, jobId);

job!.getQueryResults(done);

});

});

it('should honor the dryRun option', done => {

const options = {

query,

dryRun: true,

};

bigquery.createQueryJob(options, (err, job) => {

assert.ifError(err);

assert(job!.metadata.statistics);

done();

});

});

it('should honor the labels option', done => {

const options = {

query,

labels: {foo: 'bar'},

};

bigquery.createQueryJob(options, (err, _, job) => {

assert.ifError(err);

assert(job!.configuration!.labels);

done();

});

});

it('should query as a stream', done => {

let rowsEmitted = 0;

bigquery

.createQueryStream(query)

.on('data', row => {

rowsEmitted++;

assert.strictEqual(typeof row.url, 'string');

})

.on('error', done)

.on('end', () => {

assert.strictEqual(rowsEmitted, 100);

done();

});

});

it('should query', done => {

bigquery.query(query, (err, rows) => {

assert.ifError(err);

assert.strictEqual(rows!.length, 100);

done();

});

});

it('should query with jobs.query and return all PagedResponse as positional parameters', async () => {

const [rows, q, response] = await bigquery.query(query);

const res: bq.IQueryResponse = response!;

assert.strictEqual(rows!.length, 100);

assert.notEqual(q?.job?.id, undefined);

assert.notEqual(res, undefined);

assert.strictEqual(res.kind, 'bigquery#queryResponse');

assert.notEqual(res.queryId, undefined);

assert.strictEqual(res.totalRows, '100');

});

it('should query without jobs.query and return all PagedResponse as positional parameters', async () => {

// force jobs.getQueryResult instead of fast query path

const jobId = generateName('job');

const qOpts: QueryOptions = {

job: bigquery.job(jobId),

};

const [rows, q, response] = await bigquery.query(query, qOpts);

const res: bq.IGetQueryResultsResponse = response!;

assert.strictEqual(rows!.length, 100);

assert.strictEqual(q?.job?.id, jobId);

assert.notEqual(res, undefined);

assert.strictEqual(res.kind, 'bigquery#getQueryResultsResponse');

assert.strictEqual(res.totalRows, '100');

});

describe('Query timeout', () => {

let longRunningQuery = '';

beforeEach(() => {

const tableId = generateName('table');

longRunningQuery = `CREATE TABLE ${dataset.projectId}.${dataset.id}.${tableId} AS SELECT num FROM UNNEST(GENERATE_ARRAY(1,1000000)) as num`;

});

it('should throw a timeout error with jobs.query', async () => {

const qOpts: QueryOptions = {

timeoutMs: 1000,

};

let foundError: Error | null = null;

try {

await bigquery.query(longRunningQuery, qOpts);

} catch (error: unknown) {

foundError = error as Error;

}

assert.notEqual(foundError, null);

assert.equal(

foundError?.message,

'The query did not complete before 1000ms',

);

});

it('should throw a timeout error without jobs.query', async () => {

const jobId = generateName('job');

const qOpts: QueryOptions = {

job: bigquery.job(jobId),

timeoutMs: 1000,

};

let foundError: Error | null = null;

try {

await bigquery.query(longRunningQuery, qOpts);

} catch (error: unknown) {

foundError = error as Error;

}

assert.notEqual(foundError, null);

assert.equal(

foundError?.message,

'The query did not complete before 1000ms',

);

});

});

it('should allow querying in series', done => {

bigquery.query(

query,

{

maxResults: 10,

},

(err, rows, nextQuery) => {

assert.ifError(err);

assert.strictEqual(rows!.length, 10);

assert.strictEqual(typeof nextQuery!.pageToken, 'string');

done();

},

);

});

it('should accept the dryRun option', done => {

bigquery.query(

{

query,

dryRun: true,

},

(err, rows, resp) => {

assert.ifError(err);

assert.deepStrictEqual(rows, []);

// eslint-disable-next-line @typescript-eslint/no-explicit-any

assert((resp as any).statistics.query);

done();

},

);

});

it('should get a list of jobs', done => {

bigquery.getJobs({minCreationTime}, (err, jobs) => {

assert.ifError(err);

assert(jobs![0] instanceof Job);

done();

});

});

it('should list jobs as a stream', done => {

let jobEmitted = false;

bigquery

.getJobsStream({minCreationTime})

.on('error', done)

.on('data', job => {

jobEmitted = job instanceof Job;

})

.on('end', () => {

assert.strictEqual(jobEmitted, true);

done();

});

});

it('should cancel a job', done => {

const query = 'SELECT url FROM `publicdata.samples.github_nested` LIMIT 10';

bigquery.createQueryJob(query, (err, job) => {

assert.ifError(err);

job!.cancel(err => {

assert.ifError(err);

job!.on('error', done).on('complete', () => {

done();

});

});

});

});

describe('BigQuery/Dataset', () => {

it('should set & get metadata', async () => {

const metadata = {

description: 'yay description',

};

await dataset.setMetadata(metadata);

const [result] = await dataset.getMetadata();

assert.strictEqual(result.description, metadata.description);

});

it('should use etags for locking', async () => {

await dataset.getMetadata();

const etag = dataset.metadata.etag;

await dataset.setMetadata({

etag,

description: 'another description',

});

// the etag should be updated

assert.notStrictEqual(etag, dataset.metadata.etag);

});

it('should error out for bad etags', async () => {

await assert.rejects(

() =>

dataset.setMetadata({

etag: 'a-fake-etag',

description: 'oh no!',

}),

/precondition/i,

);

});

it('should get tables', done => {

dataset.getTables((err, tables) => {

assert.ifError(err);

assert(tables![0] instanceof Table);

done();

});

});

it('should get tables as a stream', done => {

let tableEmitted = false;

dataset

.getTablesStream()

.on('error', done)

.on('data', table => (tableEmitted = table instanceof Table))

.on('end', () => {

assert.strictEqual(tableEmitted, true);

done();

});

});

it('should create a Table with a nested schema', async () => {

const table = dataset.table(generateName('table'));

const schema = {

fields: [

{

name: 'id',

type: 'INTEGER',

},

{

name: 'details',

type: 'RECORD',

fields: [

{

name: 'nested_id',

type: 'INTEGER',

},

],

},

],

};

await table.create({schema});

const [metadata] = await table.getMetadata();

assert.deepStrictEqual(metadata.schema, schema);

});

describe('location', () => {

const LOCATION = 'asia-northeast1';

const dataset = bigquery.dataset(generateName('dataset'), {

location: LOCATION,

});

const table = dataset.table(generateName('table'));

let job: Job;

const QUERY = `SELECT * FROM \`${table.id}\``;

// eslint-disable-next-line @typescript-eslint/no-var-requires

const SCHEMA = require('../../system-test/data/schema.json');

const TEST_DATA_FILE =

require.resolve('../../system-test/data/location-test-data.json');

before(async () => {

// create a dataset in a certain location will cascade the location

// to any jobs created through it

await dataset.create();

await table.create({schema: SCHEMA});

// eslint-disable-next-line @typescript-eslint/no-explicit-any

job = ((await table.createLoadJob(TEST_DATA_FILE)) as any)[0];

await job.promise();

});

it('should create a load job in the correct location', () => {

assert.strictEqual(job.location, LOCATION);

});

describe('job.get', async () => {

it('should fail to reload if the location is not set', async () => {

const badJob = bigquery.job(job.id!);

await assert.rejects(() => badJob.getMetadata(), {code: 404});

});

it('should fail to reload if the location is wrong', async () => {

const badJob = bigquery.job(job.id!, {location: 'US'});

await assert.rejects(() => badJob.getMetadata(), {code: 404});

});

it('should reload if the location matches', async () => {

const goodJob = bigquery.job(job.id!, {location: LOCATION});

await goodJob.getMetadata();

assert.strictEqual(goodJob.location, LOCATION);

});

});

describe('job.cancel', () => {

let job: Job;

before(() => {

return dataset.createQueryJob(QUERY).then(data => {

job = data[0];

});

});

it('should fail if the job location is incorrect', done => {

const badJob = bigquery.job(job.id!, {location: 'US'});

badJob.cancel(err => {

assert.strictEqual((err as ApiError).code, 404);

done();

});

});

it('should cancel a job', done => {

job.cancel(done);

});

});

describe('job.getQueryResults', () => {

it('should fail if the job location is incorrect', done => {

const badDataset = bigquery.dataset(dataset.id!, {location: 'US'});

badDataset.createQueryJob(

{

query: QUERY,

},

(e, job) => {

const err = e as {} as GoogleErrorBody;

assert.strictEqual(err.errors![0].reason, 'notFound');

assert.strictEqual(job!.location, 'US');

done();

},

);

});

it('should get query results', async () => {

const [job] = await dataset.createQueryJob(QUERY);

assert.strictEqual(job.location, LOCATION);

await job.promise();

const [rows] = await job.getQueryResults();

assert(rows!.length > 0);

});

it('should return error if timeout passed', async () => {

const [job] = await dataset.createQueryJob(QUERY);

assert.strictEqual(job.location, LOCATION);

await job.promise();

const options = {timeoutMs: 1};

await job.getQueryResults(options);

});

});

describe('job.insert', () => {

describe('copy', () => {

const otherTable = dataset.table(generateName('table'));

it('should fail if the job location is incorrect', done => {

const badTable = dataset.table(table.id!, {location: 'US'});

badTable.createCopyJob(otherTable, err => {

assert.notEqual(err, null); // TODO(alvarowolfx): check for 404 ?

done();

});

});

it('should copy the table', () => {

return table.createCopyJob(otherTable).then(data => {

const job = data[0];

assert.strictEqual(job.location, LOCATION);

return job.promise();

});

});

});

describe('extract', () => {

const bucket = storage.bucket(generateName('bucket'));

const extractFile = bucket.file('location-extract-data.json');

before(() => {

return bucket.create({location: LOCATION});

});

it('should fail if the job location is incorrect', done => {

const badTable = dataset.table(table.id!, {location: 'US'});

badTable.createExtractJob(extractFile, err => {

assert.strictEqual((err as ApiError).code, 404);

done();

});

});

it('should extract a table', () => {

return table.createExtractJob(extractFile).then(data => {

const job = data[0];

assert.strictEqual(job.location, LOCATION);

return job.promise();

});

});

});

});

});

});

describe('BigQuery/Job', () => {

it('should delete a job', async () => {

const opts = {

configuration: {

query: {

query: 'SELECT 100 as foo',

},

},

location: 'us-east1',

};

const [job] = await bigquery.createJob(opts);

// Job must finish before we can delete. Wait for results.

await job.getQueryResults();

const [resp] = await job.delete();

const [exists] = await job.exists();

assert.deepStrictEqual(resp, {});

assert.strictEqual(exists, false);

});

});

describe('BigQuery/Model', () => {

let model: Model;

const bucket = storage.bucket(generateName('bucket'));

const extractDest =

'gs://' + bucket.name + '/' + generateName('model-export');

before(async () => {

await bucket.create();

model = dataset.model('testmodel');

return bigquery.query(`

CREATE MODEL \`${dataset.id}.${model.id}\`

OPTIONS (

model_type='linear_reg',

max_iterations=1,

learn_rate=0.4,

learn_rate_strategy='constant'

) AS (

SELECT 'a' AS f1, 2.0 AS label

UNION ALL

SELECT 'b' AS f2, 3.8 AS label

)

`);

});

after(() => model.delete());

it('should get a list of models', async () => {

const [models] = await dataset.getModels();

assert.strictEqual(models.length, 1);

assert.ok(models[0] instanceof Model);

});

it('should check if a model exists', async () => {

const [exists] = await model.exists();

assert.ok(exists);

});

it('should get a model', async () => {

const [model2] = await model.get();

assert.deepStrictEqual(model, model2);

});

it('should get a model metadata', async () => {

const [metadata] = await model.getMetadata();

assert.deepStrictEqual(metadata, model.metadata);

});

it('should set model metadata', async () => {

const friendlyName = 'modelfriend';

await model.setMetadata({friendlyName});

const [metadata] = await model.getMetadata();

assert.strictEqual(metadata.friendlyName, friendlyName);

});

it('should extract a model', async () => {

const jobId = generateName('model-export-job');

const [job] = await model.createExtractJob(extractDest, {jobId});

assert.strictEqual(job.id, jobId);

});

});

describe('BigQuery/Routine', () => {

before(() => {

const routineId = `${bigquery.projectId}.${dataset.id}.my_ddl_routine`;

return bigquery.query(`

CREATE FUNCTION \`${routineId}\`(

arr ARRAY<STRUCT<name STRING, val INT64>>

) AS (

(SELECT SUM(IF(elem.name = "foo",elem.val,null)) FROM UNNEST(arr) AS elem)

)

`);

});

after(async () => {

const [routines] = await dataset.getRoutines();

return Promise.all(routines.map(routine => routine.delete()));

});

it('should create a routine via insert', () => {

return dataset.createRoutine('my_routine', {

arguments: [

{

name: 'x',

dataType: {

typeKind: 'INT64',

},

},

],

definitionBody: 'x * 3',

routineType: 'SCALAR_FUNCTION',

returnType: {

typeKind: 'INT64',

},

});

});

it('should list all the routines', async () => {

const [routines] = await dataset.getRoutines();

assert.ok(routines.length > 0);

assert.ok(routines[0] instanceof Routine);

});

it('should get the routines as a stream', done => {

const routines: Routine[] = [];

dataset

.getRoutinesStream()

.on('error', done)

.on('data', routine => {

routines.push(routine);

})

.on('end', () => {

assert.ok(routines.length > 0);

assert.ok(routines[0] instanceof Routine);

done();

});

});

it('should check to see if a routine exists', async () => {

const routine = dataset.routine('my_ddl_routine');

const [exists] = await routine.exists();

assert.ok(exists);

});

it('should update an existing routine', async () => {

const routine = dataset.routine('my_ddl_routine');

const description = 'A routine!';

await routine.setMetadata({description});

assert.strictEqual(routine.metadata.description, description);

});

});

describe('BigQuery/Table', () => {

const TEST_DATA_JSON_PATH =

require.resolve('../../system-test/data/kitten-test-data.json');

it('should have created the correct schema', () => {

assert.deepStrictEqual(table.metadata.schema.fields, SCHEMA);

});

it('should get the rows in a table', done => {

table.getRows((err, rows) => {

assert.ifError(err);

assert(Array.isArray(rows));

done();

});

});

// Empty results sets will not fetch table schema

it('should get the rows from an empty table', async () => {

const emptyTableId = generateName('empty-table');

await dataset.createTable(emptyTableId, {

schema: [{name: 'id', type: 'STRING'}],

});

const emptyTable = dataset.table(emptyTableId);

const [rows] = await emptyTable.getRows();

assert(Array.isArray(rows));

assert.equal(rows.length, 0);

});

it('should get the rows in a table via stream', done => {

table

.createReadStream()

.on('error', done)

.on('data', () => {})

.on('end', done);

});

it('should insert rows via stream', done => {

let job: Job;

fs.createReadStream(TEST_DATA_JSON_PATH)

.pipe(table.createWriteStream('json'))

.on('error', done)

.on('complete', _job => {

job = _job;

})

.on('finish', () => {

assert.strictEqual(job.metadata.status.state, 'DONE');

done();

});

});

it('should insert rows with null values', () => {

return table.insert({

id: 1,

name: null,

});

});

it('should insert rows via insert stream', done => {

const stream = new Readable({objectMode: true});

stream._read = () => {};

for (let i = 0; i < 10; i++) {

stream.push({name: 'foo', id: i});

}

const expectedResponse = 'bigquery#tableDataInsertAllResponse';

stream

.pipe(table.createInsertStream())

.on('response', (response: InsertRowsStreamResponse) => {

assert.deepStrictEqual(response.kind, expectedResponse);

done();

});

});

it('should return errors from insert stream', done => {

const stream = new Readable({objectMode: true});

stream._read = () => {};

stream.push({wrong_name: 'foo', id: 1});

stream.pipe(table.createInsertStream()).on('error', (err: Error) => {

assert.deepStrictEqual(err.name, 'PartialFailureError');

done();

});

});

it('should set & get metadata', async () => {

const description = 'catsandstuff';

await table.setMetadata({description});

const [metadata] = await table.getMetadata();

const metadataProps = Object.keys(metadata);

assert.strictEqual(metadataProps.includes('numBytes'), true);

assert.notStrictEqual(metadata.numBytes, undefined);

assert.strictEqual(metadataProps.includes('lastModifiedTime'), true);

assert.notStrictEqual(metadata.lastModifiedTime, undefined);

assert.strictEqual(metadata.description, description);

});

it('should set & get partial metadata', async () => {

const options = {

view: 'BASIC',

};

const [basicMetadata] = await table.get(options);

const basicMetadataProps = Object.values(

Object.keys(basicMetadata.metadata),

);

assert.strictEqual(basicMetadataProps.includes('numBytes'), false);

assert.strictEqual(basicMetadata.metadata.numBytes, undefined);

assert.strictEqual(

basicMetadataProps.includes('lastModifiedTime'),

false,

);

assert.strictEqual(basicMetadata.metadata.lastModifiedTime, undefined);

});

it('should create a table with a numeric field having picosecond precision', async () => {

const table = dataset.table(generateName('numeric-precision-table'));