// 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 Big from 'big.js';

import * as fs from 'fs';

import * as uuid from 'uuid';

import {

BigQuery,

Dataset,

Job,

Model,

RowMetadata,

Routine,

Table,

} from '../src';

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-${uuid.v4()}`;

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 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.strictEqual((err as ApiError).code, 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/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();

});

});

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 set & get metadata', async () => {

const description = 'catsandstuff';

await table.setMetadata({description});

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

assert.strictEqual(metadata.description, description);

});

describe('copying', () => {

interface TableItem {

data?: {tableId?: number};

table: Table;

}

const TABLES = ([{data: {tableId: 1}}, {}] as {}) as TableItem[];

const SCHEMA = 'tableId:integer';

before(async () => {

TABLES.forEach(t => (t.table = dataset.table(generateName('table'))));

await Promise.all(

TABLES.map(tableItem => {

const tableInstance = tableItem.table;

return tableInstance!.create({schema: SCHEMA});

})

);

const table1Instance = TABLES[0].table;

await table1Instance.insert(TABLES[0].data);

});

it('should start copying data from current table', done => {

const table1 = TABLES[0];

const table1Instance = table1.table;

const table2 = TABLES[1];

const table2Instance = table2.table;

table1Instance.createCopyJob(table2Instance, (err, job) => {

assert.ifError(err);

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

// Data may take up to 90 minutes to be copied*, so we cannot test

// to see that anything but the request was successful.

// *https://cloud.google.com/bigquery/streaming-data-into-bigquery

done();

});

});

});

it('should copy data from current table', done => {

const table1 = TABLES[0];

const table1Instance = table1.table;

const table2 = TABLES[1];

const table2Instance = table2.table;

table1Instance.copy(table2Instance, (err, resp) => {

assert.ifError(err);

assert.strictEqual(resp!.status!.state, 'DONE');

done();

});

});

it('should start copying data from another table', done => {

const table1 = TABLES[0];

const table1Instance = table1.table;

const table2 = TABLES[1];

const table2Instance = table2.table;

table2Instance.createCopyFromJob(table1Instance, (err, job) => {

assert.ifError(err);

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

// Data may take up to 90 minutes to be copied*, so we cannot test

// to see that anything but the request was successful.

// *https://cloud.google.com/bigquery/streaming-data-into-bigquery

done();

});

});

});

it('should copy data from another table', done => {

const table1 = TABLES[0];

const table1Instance = table1.table;

const table2 = TABLES[1];

const table2Instance = table2.table;

table2Instance.copyFrom(table1Instance, (err, resp) => {

assert.ifError(err);

assert.strictEqual(resp!.status!.state, 'DONE');

done();

});

});

});

describe('loading & extracting', () => {

const file = bucket.file('kitten-test-data-backup.json');

before(done => {

fs.createReadStream(TEST_DATA_JSON_PATH)

.pipe(file.createWriteStream({}))

.on('error', done)

.on('finish', done);

});

after(done => {

file.delete(done);

});

it('should start to load data from a storage file', done => {

table.createLoadJob(file, (err, job) => {

assert.ifError(err);

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

done();

});

});

});

it('should load data from a storage file', done => {

table.load(file, (err, resp) => {

assert.ifError(err);

assert.strictEqual(resp!.status!.state, 'DONE');

done();

});

});

it('should load data from a file via promises', () => {

return table.load(file).then(results => {

const metadata = results[0];

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

});

});

it('should return partial errors', done => {

const data = {

name: 'dave',

};

const improperData = {

name: true,

};

table.insert([data, improperData], e => {

const err = (e as {}) as GoogleErrorBody;

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

assert.strictEqual((err as any).name, 'PartialFailureError');

assert.deepStrictEqual(err.errors![0], {

errors: [

{

message: 'Conversion from bool to std::string is unsupported.',

reason: 'invalid',

},

],

row: improperData,

});

assert.deepStrictEqual(err.errors![1], {

errors: [

{

message: '',

reason: 'stopped',

},

],

row: data,

});

done();

});