2021 Fillmore Street #1128

}

24/7 solutions

Top 10 Cloud Providers

How to Connect to a 3rd Party's Google Big Query using Node.js on Mac using a Service Account Key

How to Connect to a 3rd Party’s Google Big Query using Node.js on Mac using a Service Account Key One of our clients provided us with access to their Google Big Query the other day. We only received the credentials. We had no idea what was in the project or dataset. They provided us with […]

How to Connect to a 3rd Party’s Google Big Query using Node.js on Mac using a Service Account Key

One of our clients provided us with access to their Google Big Query the other day. We only received the credentials. We had no idea what was in the project or dataset.
They provided us with credentials in a json format that looked like


{
"type": "service_account",
"project_id": "allcode-dev",
"private_key_id": "xxxxxxxxxxxxxxxxx",
"private_key": "-----BEGIN PRIVATE KEY-----nyyyyyyyyyyy",
"client_email": "[email protected]",
"client_id": "yyyyyy",
"auth_uri": "http://accounts.google.com/o/oauth2/auth",
"token_uri": "http://oauth2.googleapis.com/token",
"auth_provider_x509_cert_url": "http://www.googleapis.com/oauth2/v1/certs",
"client_x509_cert_url": "http://www.googleapis.com/robot/v1/metadata/x509/allcode-gbq.iam.gserviceaccount.com"
}

The credentials are a service account key.
I took the following steps to validate connectivity to this GBQ instance.

  1. I set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the path of the JSON service account file in my ~/.bash_profile
     export GOOGLE_APPLICATION_CREDENTIALS="/home/user/Downloads/service-account-file.json"
  2. Next, I confirmed the environment variable by doing the following
     export $GOOGLE_APPLICATION_CREDENTIALS
  3. I installed the latest version of NVM using brew
    brew update
    brew install nvm
  4. I installed the NPM Big Query SDK
     npm install --save @google-cloud/bigquery
  5. Next, I wrote a test_connect.js whose sole purpose was to query the meta tables to understand what tables are in the project. The contents of the test_connect.js are
    const {BigQuery} = require('@google-cloud/bigquery');
    var bigqueryClient = new BigQuery();
    //console.log( bigqueryClient);
    async function queryMetaData() {
    // Queries a default databases MetaData.
    // Create a client
    const bigqueryClient = new BigQuery();
    // The SQL query to run
    const sqlQuery = `SELECT
    * EXCEPT(is_typed)
    FROM
    mydataset.INFORMATION_SCHEMA.TABLES`;
    const options = {
    query: sqlQuery,
    // Location must match that of the dataset(s) referenced in the query.
    location: 'US',
    };
    // Run the query
    const [rows] = await bigqueryClient.query(options);
    console.log('Query Results:');
    rows.forEach(row => {
    const url = row['url'];
    const viewCount = row['view_count'];
    console.log(`url: ${url}, ${viewCount} views`);
    });
    }
    queryMetaData();
    
  6. When I executed this command with
    node test_connect.js

    I received the following errors stating that the permissions on my account needed to be improved in order to run queries.

    
    (node:76490) UnhandledPromiseRejectionWarning: Error: Access Denied: Project allcode-dev:
    User does not have bigquery.jobs.create permission in project allcode-dev.
        at new ApiError (/Users/joelgarcia/node_modules/@google-cloud/common/build/src/util.js:58:15)
        at Util.parseHttpRespBody (/Users/joelgarcia/node_modules/@google-cloud/common/build/src/util.js:193:38)
        at Util.handleResp (/Users/joelgarcia/node_modules/@google-cloud/common/build/src/util.js:134:117)
        at /Users/joelgarcia/node_modules/@google-cloud/common/build/src/util.js:432:22
        at onResponse (/Users/joelgarcia/node_modules/retry-request/index.js:206:7)
        at /Users/joelgarcia/node_modules/teeny-request/build/src/index.js:233:13
        at processTicksAndRejections (internal/process/task_queues.js:97:5)
    (node:76490) UnhandledPromiseRejectionWarning: Unhandled promise rejection.
    This error originated either by throwing inside of an async function without a catch block, or by rejecting a pro
    
  7. It looks like the credentials work, but I don’t have the proper credentials. Progress!

Related Articles

AWS Free Tier

AWS Free Tier

Understandably, making the jump to the AWS cloud is not an easy investment to evaluate, especially from the perspective of a startup. Fortunately, AWS is generous enough to provide some hands-on experience with its various tools and utilities free of charge for either a short period of time or indefinitely depending on the service type. Here’s a rundown of everything you can expect from AWS’ Free Tier.

AWS Beginner Guide

AWS Beginner Guide

AWS (Amazon Web Services) is the current largest provider in Infrastructure as a Service (IaaS) and Platform as a Service (PaaS) for everything from databases to extra computing power. It provides solutions for whatever problems are brought by clients and it allows for ease of scalability so that users will only pay for what they need and use; especially during times of expected lower traffic.

How to Migrate an On-Premises Database to AWS

How to Migrate an On-Premises Database to AWS

The AWS Database Migration Service (AWS DMS) assists you in performing a safe and speedy migration of databases to AWS. The source database continues to function normally throughout the migration, hence reducing the amount of downtime experienced by applications that are dependent on the database. Data can be moved to or from the most popular commercial and open-source databases with the help of the AWS Database Migration Service.

Download our 10-Step Cloud Migration ChecklistYou'll get direct access to our full-length guide on Google Docs. From here, you will be able to make a copy, download the content, and share it with your team.