Skip to main content

Google BigQuery

Google BigQuery is an enterprise data warehouse that allows you to store and query massive datasets at high speed using Google's infrastructure

Credential configuration

This credential configuration is done with a single JSON that looks like this:

{
"projectId": "PROJECT_ID",
"credentials": {
"type": "service_account",
"project_id": "PROJECT_ID",
"private_key_id": "KEY_ID",
"private_key": "-----BEGIN PRIVATE KEY-----\nPRIVATE_KEY\n-----END PRIVATE KEY-----\n",
"client_email": "SERVICE_ACCOUNT_EMAIL",
"client_id": "CLIENT_ID",
"auth_uri": "https://accounts.google.com/o/oauth2/auth",
"token_uri": "https://accounts.google.com/o/oauth2/token",
"auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/SERVICE_ACCOUNT_EMAIL"
}
}

The projectId corresponds to the project ID from the Google Developer's Console.

The credentials field of the JSON corresponds to a JSON key of the service account. This key is downloaded as a JSON file when you create it. You only need to paste its content inside credentials field. If you don't have one, create a service account and then, a JSON key for that service account. The service account which you use needs permissions to access BigQuery to work properly.

Here you have an example of a filled credential configuration form in YepCode:

Google BigQuery snippets available in editor

note

The title is the triggering text for YepCode to autocomplete the script

Integration

New integration from credential
const googleBigQueryClient = yepcode.integration.googleBigQuery("credential-slug");
New integration from plain authentication data
const { BigQuery } = require("@google-cloud/bigquery");

const googleBigQueryCredentials = {
projectId: "YepCode",
credentials: {
type: "service_account",
project_id: "yepcode",
private_key_id: "XXXXX",
private_key: \`-----BEGIN PRIVATE KEY-----\nx\n-----END PRIVATE KEY-----\`,
client_email: "yepcode@example.org",
client_id: "1234567890",
auth_uri: "https://example.org",
token_uri: "https://example.org",
auth_provider_x509_cert_url: "https://example.org",
client_x509_cert_url: "https://example.org",
}
};

const googleBigQueryClient = new BigQuery(googleBigQueryCredentials);

Create a dataset

Create a dataset
async function createDataset() {
// Specify the geographic location where the dataset should reside
const options = {
location: "dataset-location",
};

const [dataset] = await googleBigQueryClient.createDataset("dataset-id", options);
console.log(`Dataset ${dataset.id} created.`);
}

createDataset();

Delete a dataset

Delete a dataset
async function deleteDataset() {
// Create a reference to the existing dataset
const dataset = googleBigQueryClient.dataset("dataset-id");

await dataset.delete({force: true});
console.log(`Dataset ${dataset.id} deleted.`);
}

deleteDataset();

Query to a dataset

Query to a dataset
async function query() {
const query = "your-SQL-query";

// For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
const options = {
query: query,
// Location must match that of the dataset(s) referenced in the query.
location: "dataset-location",
};

// Run the query as a job
const [job] = await googleBigQueryClient.createQueryJob(options);
console.log(`Job ${job.id} started.`);

// Wait for the query to finish
const [rows] = await job.getQueryResults();

// Use the result
// Your code here
}

query();