在BigQuery + Angular + Firebase Functions中,由于BigQuery具有超大尺寸限制,当查询结果超过限制时,可以采取以下解决方法之一:
// Angular Component
import { Component } from '@angular/core';
import { AngularFireFunctions } from '@angular/fire/functions';
@Component({
selector: 'app-results',
templateUrl: './results.component.html',
styleUrls: ['./results.component.css']
})
export class ResultsComponent {
currentPage: number = 1;
pageSize: number = 10;
totalResults: number = 0;
results: any[] = [];
constructor(private functions: AngularFireFunctions) {}
ngOnInit() {
this.loadResults();
}
loadResults() {
const query = this.functions.httpsCallable('queryBigQuery');
query({ page: this.currentPage, pageSize: this.pageSize }).subscribe(
(response: any) => {
this.totalResults = response.totalResults;
this.results = response.results;
},
(error: any) => {
console.error(error);
}
);
}
nextPage() {
this.currentPage++;
this.loadResults();
}
previousPage() {
this.currentPage--;
this.loadResults();
}
}
// Firebase Functions
const { BigQuery } = require('@google-cloud/bigquery');
exports.queryBigQuery = functions.https.onCall(async (data, context) => {
const page = data.page || 1;
const pageSize = data.pageSize || 10;
const bigquery = new BigQuery();
const query = `SELECT * FROM myTable LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}`;
const options = {
query: query,
location: 'US',
};
const [rows] = await bigquery.query(options);
const totalResults = rows.length; // Total results for pagination
const results = rows.map(row => row); // Modify this based on your actual results structure
return {
totalResults: totalResults,
results: results
};
});
// Angular Component
import { Component } from '@angular/core';
import { AngularFireStorage } from '@angular/fire/storage';
import { AngularFireFunctions } from '@angular/fire/functions';
@Component({
selector: 'app-results',
templateUrl: './results.component.html',
styleUrls: ['./results.component.css']
})
export class ResultsComponent {
downloadUrl: string = '';
loading: boolean = false;
constructor(private storage: AngularFireStorage, private functions: AngularFireFunctions) {}
ngOnInit() {
this.executeQuery();
}
executeQuery() {
const query = this.functions.httpsCallable('executeBigQuery');
query({}).subscribe(
(response: any) => {
this.downloadUrl = response.downloadUrl;
},
(error: any) => {
console.error(error);
}
);
}
downloadResults() {
this.loading = true;
const ref = this.storage.refFromURL(this.downloadUrl);
ref.getDownloadURL().subscribe(
(url: string) => {
window.open(url, '_blank');
this.loading = false;
},
(error: any) => {
console.error(error);
this.loading = false;
}
);
}
}
// Firebase Functions
const { BigQuery } = require('@google-cloud/bigquery');
exports.executeBigQuery = functions.https.onCall(async (data, context) => {
const bigquery = new BigQuery();
const query = `SELECT * FROM myTable`;
const options = {
query: query,
location: 'US',
};
const [job] = await bigquery.createQueryJob(options);
// Wait for the job to complete
await job.getQueryResults();
const [files] = await job.getQueryResults({ destinationFormat: 'CSV', gzip: true });
const [file] = files;
const bucketName = file.metadata.bucket;
const fileName = file.metadata.name;
const downloadUrl = `https://