vue.js, proxy server(node.js), bigQuery 데이터 불러와 사용하기

*홈페이지에 나와있는 상호명은 특정 상호명과 아무 관련이 없습니다.

ㅡ. Result

현재 상태의 화면인데 실제 입력 값에 대해 결과값을 bigQuery 에서 받아와 Vue로 뿌려주는 작업을 진행할 것입니다. 

*ref : https://cloud.google.com/bigquery/docs/reference/libraries?hl=ko#client-libraries-install-nodejs

 

npm install 수행

bigQueryApi 라는 Js 파일을 생성하여 아래 코드를 작성하였습니다. 

const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function query() {
    // Queries the U.S. given names dataset for the state of Texas.
    
      const query = `SELECT name
        FROM \`bigquery-public-data.usa_names.usa_1910_2013\`
        WHERE state = 'TX'
        LIMIT 100`;

      // 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: 'US',
      };

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

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

      // Print the results
      console.log('Rows:');
      rows.forEach(row => console.log(row));
    }

ㅡ. 일반 test.js 파일에서 node test.js 실행시켰을 때 잘 작동하는것을 확인할 수 있었습니다.

 

이 작업은 실제 Vue.js에서 실행할 수 없었습니다. node를 찾을 수 없다는 에러가 발생 이에 따라 node.js로 proxy 서버를 하나 두어 동작하도록 했습니다. 

2020/08/25 - [javascript] - node.js restful api 서버 만들기 (with bigQuery)

로 node.js 를 구상하고 bigquery.js 파일을 만들었습니다.

ㅡ. node.js

var express = require('express');
var router = express.Router();
const {BigQuery} = require('@google-cloud/bigquery');

const feature=async(res, category)=>{
  const bigquery = new BigQuery();
  
  const query = "SELECT * FROM `fluid-crane-284202.prototyping_dataset.feature_basic` WHERE category="+"'"+category+"'";
  const options = {
    query : query,
    location: 'US',
  } 
  const [job] = await bigquery.createQueryJob(options);
  const [rows] = await job.getQueryResults();
  rows.forEach(row=>console.log(row));
  res.send([rows])
}



const query=async(res, category)=>{
  // Queries the U.S. given names dataset for the state of Texas.
  const bigquery = new BigQuery();
 
  const query = "SELECT * FROM `[project-id].[project-dataset].[project-table]` WHERE category="+"'"+category+"'";
  console.log(query)
  console.log(category)
  // For all options, see https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/query
  const options = {
    query: query,
    location: 'US',
  };

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

  // Wait for the query to finish
  const [rows] = await job.getQueryResults();
  
  // Print the results
  console.log('Rows:');
  rows.forEach(row => console.log(row));
  
  res.send([rows])

}

const category_advanced=async(res, category)=>{
  const bigquery = new BigQuery();
  
  const query = "SELECT * FROM `[project-id].[project-dataset].[project-table]` WHERE category="+"'"+category+"'";
  const options = {
    query : query,
    location: 'US',
  } 
  const [job] = await bigquery.createQueryJob(options);
  const [rows] = await job.getQueryResults();
  rows.forEach(row=>console.log(row));
  res.send([rows])
}

const commit=async(res, category)=>{
  const bigquery = new BigQuery();
  const query = "SELECT * FROM `[project-id].[project-dataset].[project-table]` WHERE category="+"'"+category+"'";
  const options = {
    query : query,
    location: 'US',
  }
  const [job] = await bigquery.createQueryJob(options);
  const [rows] = await job.getQueryResults();
  rows.forEach(row=>console.log(row));
  res.send([rows])
}

const streaming=async(res, category)=>{
  const bigquery = new BigQuery();
  const query = "SELECT payloadString FROM `[project-id].[project-dataset].[project-table]` WHERE payloadString like"+"'%"+category+"%'";
  const options = {
    query : query,
    location: 'US',
  }
  const [job] = await bigquery.createQueryJob(options);
  const [rows] = await job.getQueryResults();
  rows.forEach(row=>console.log(row));
  if(rows.length == 0)
    res.send(['nodata'])
  else
    res.send([rows])
}

/* POST users listing. */
router.post('/', async function(req, res, next) {
  //res.json(users);
  res.set({'access-control-allow-origin':'*'});
  await query(res, req.query.category)
});

/* POST users listing. */
router.post('/feature', async function(req, res, next) {
  //res.json(users);
  res.set({'access-control-allow-origin':'*'});
  await feature(res, req.query.category)
});

/* POST users listing. */
router.post('/category_advanced', async function(req, res, next){
  res.set({'access-control-allow-origin':'*'});
  await category_advanced(res, req.query.category)
});


router.post('/commit', async function(req, res, next){
  res.set({'access-control-allow-origin':'*'});
  await commit(res, req.query.category);
});

router.post('/streaming', async function(req, res, next){
  res.set({'access-control-allow-origin':'*'});
  await streaming(res, req.query.category);
});

module.exports = router;

 위와 같은 코드를 구상한뒤에 vue.js에서 코드를 AXIOS 코드를 작성하면 됩니다. 

ㅡ. vue.js

import Axios from "axios";
const URL = "http://localhost:3000"


const requestCategoryBasic = (data, callback, errorCallback) =>{
    Axios.post(URL + '/bigquery/?category='+data[0])
    .then(response=>{
      callback(response);
    }).catch(exp=>{
      console.log(exp);
      errorCallback(exp);
    })
}

const requestFeatureBasic = async(data, callback, errorCallback) =>{
  await Axios.post(URL + '/bigquery/feature/?category='+data)
  .then(response=>{
    callback(response);
  }).catch(exp=>{
    console.log(exp);
    errorCallback(exp);
  })
}

const requestCategoryAdvanced = async(data, callback, errorCallback)=>{
  await Axios.post(URL + '/bigquery/category_advanced/?category='+data)
  .then(response=>{
    callback(response);
  }).catch(exp=>{
    console.log(exp);
    errorCallback(exp);
  })
}
const requestCommit = async(data, callback, errorCallback)=>{
  await Axios.post(URL + '/bigquery/commit/?category='+data)
  .then(response=>{
    callback(response);
  }).catch(exp=>{
    console.log(exp);
    errorCallback(exp);
  })
}
const requestStreaming = async(data, callback, errorCallback)=>{
  await Axios.post(URL + '/bigquery/streaming/?category='+data)
  .then(response=>{
    callback(response);
  }).catch(exp=>{
    console.log(exp)
    errorCallback(exp)
  })
}

const proxyApi = {
  requestCategoryBasic: (data, callback, errorCallback) =>requestCategoryBasic(data, callback, errorCallback),
  requestFeatureBasic: (data, callback, errorCallback) => requestFeatureBasic(data, callback, errorCallback),
  requestCategoryAdvanced: (data, callback, errorCallback) => requestCategoryAdvanced(data, callback, errorCallback),
  requestCommit: (data, callback, errorCallback) =>requestCommit(data, callback, errorCallback),
  requestStreaming : (data, callback, errorCallback) => requestStreaming(data, callback, errorCallback),
}
export default proxyApi;

 

ㅡ. bigquery proxy server 에서 처리되는 결과

bigQuery 데이터셋이 다를 수 있기 때문에 결과는 상이할 수 있습니다.

ㅡ. 화면UI 결과

받아온 응답으로 데이터를 시각화 시켰다.