분류 Nodejs

MySQL 및 Cube.js로 BigQuery 속도 향상

컨텐츠 정보

  • 조회 448 (작성일 )

본문

BigQuery는 큰 데이터 세트를 처리하는 데 유용하지만 작은 데이터 세트에서도 1 초 미만의 응답을 제공하지 않습니다. 사용자가 다른 날짜 범위를 선택하거나 필터를 변경할 수 있는 대시 보드 및 차트, 특히 동적 대기 시간으로 이어집니다. 

내부 BI에는 거의 항상 적합하지만 고객용 분석에는 적합하지 않습니다. 우리는 UI가 열악하고 내부 도구의 성능과 같은 많은 것들을 용납하지만 고객에게 제공하는 것은 아닙니다.


그러나 여전히 BigQuery의 저렴한 데이터 스토리지와 강력한 데이터 세트를 처리하는 능력을 활용하면서도 성능을 포기할 수는 없습니다. BigQuery는 단일 소스 소스로 작동하고 모든 원시 데이터를 저장하므로 MySQL은 그 위에 캐시 계층으로 작동하고 작은 집계 테이블만 저장할 수 있으며 원하는 초 단위 응답을 제공합니다.


https://cube.dev/blog/when-mysql-is-faster-than-bigquery/ 


여기서 데모와 Github의 소스 코드를 확인할 수 있습니다. 날짜 범위 및 스위처와 함께 재생하십시오. 동적 대시 보드는 사전 집계의 이점을 최대한 활용할 수 있습니다.


cubejs-external-rollups.gif 


Cube.js는 사전 집계 계층을 2 단계 캐싱 시스템의 일부로 활용합니다. 우리는 최근 사용자가 여러 데이터베이스를 결합하여 두 세계를 최대한 활용할 수있는 사용 사례를 대상으로 외부 사전 집계 지원을 발표했습니다. 아래 스키마는 BigQuery 및 MySQL을 사용한 Cube.js의 일반적인 설정을 보여줍니다.

mysql-bigquery.png 


외부 롤업 기능을 사용하려면 BigQuery와 MySQL 모두에 연결하도록 Cube.js를 구성하고 외부에서 구축 할 사전 집계를 지정해야 합니다. Cube.js를 처음 사용하는 경우 이 101 스타일 자습서를 먼저 확인한 다음 여기로 돌아 오는 것이 좋습니다. 우리는 샘플 애플리케이션을 위해 BigQuery의 공개 Hacker News 데이터 셋을 사용할 것입니다.


먼저 Cube.js CLI를 설치하고 새 애플리케이션을 작성하겠습니다.


$ npm install -g cubejs-cli
$ cubejs create external-rollups -d bigquery


기본 데이터베이스를 BigQuery로 만들기 위해 -d bigquery를 설정했습니다. 그런 다음 bigquery-mysql 폴더로 이동하여 올바른 자격 증명으로 .env를 구성하십시오.


CUBEJS_DB_TYPE=bigquery
CUBEJS_DB_BQ_PROJECT_ID=<BIGQUERY PROJECT ID>
CUBEJS_DB_BQ_CREDENTIALS=<BIGQUERY BASE64-ENCODED KEY>
CUBEJS_EXT_DB_NAME=preags
CUBEJS_EXT_DB_HOST=localhost
CUBEJS_EXT_DB_USER=root
CUBEJS_EXT_DB_PASS=12345


여기에서는 기본 DB (BigQuery)와 사전 집계를 위한 외부 DB (MySQL) 모두에 대한 자격 증명을 설정합니다. Cube.js 문서에서 BigQuery 자격 증명을 얻는 방법에 대한 자세한 내용은 여기를 참조하십시오. 또한 MySQL 내에서 사전 집계를 빌드 하려면 Cube.js에 사전 집계 테이블이 저장되는 stb_pre_aggregations 스키마에 대한 쓰기 액세스 권한이 있어야 합니다.


이제 Cube.js MySQL 드라이버를 설치하겠습니다.


$ npm install @cubejs-backend/mysql-driver --save


구성이 끝나면 마지막 구성 단계는 externalDbType 및 externalDriverFactory 속성을 통해 MySQL 관련 옵션을 제공하는 것입니다. index.js 파일의 내용을 다음으로 바꾸십시오.


const CubejsServer = require("@cubejs-backend/server");
const MySQLDriver = require('@cubejs-backend/mysql-driver');

const server = new CubejsServer({
  externalDbType: 'mysql',
  externalDriverFactory: () => new MySQLDriver({
    host: process.env.CUBEJS_EXT_DB_HOST,
    database: process.env.CUBEJS_EXT_DB_NAME,
    user: process.env.CUBEJS_EXT_DB_USER,
    password: process.env.CUBEJS_EXT_DB_PASS.toString()
  })
});

server.listen().then(({ port }) => {
  console.log(`? Cube.js server is listening on ${port}`);
});


이것이 Cube.js가 BigQuery와 MySQL에 모두 연결 되도록 하는 데 필요한 전부입니다. 이제 첫 번째 Cube.js 데이터 스키마 파일을 만들 수 있습니다. Cube.js는 데이터 스키마를 사용하여 데이터베이스에서 실행될 SQL 코드를 생성합니다.


다음 내용으로 schema / Stories.js 파일을 작성하십시오.


cube(`Stories`, {
  sql: `select * from \`fh-bigquery.hackernews.full_partitioned\` WHERE type = 'story'`,

  measures: {
    count: {
      type: `count`,
    }
  },

  dimensions: {
    category: {
      type: `string`,
      case: {
        when: [
          { sql: `STARTS_WITH(title, "Show HN")`, label: `Show HN` },
          { sql: `STARTS_WITH(title, "Ask HN")`, label: `Ask HN` }
        ],
        else: { label: `Other` }
      }
    },

    time: {
      sql: `timestamp`,
      type: `time`
    }
  }
});


이제 index.js 노드를 실행하여 Cube.js 서버를 시작하고 http : // localhost : 4000에서 개발 놀이터로 이동하십시오.


아래에 표시된 대로 시간 측정 기준과 함께 스토리 수 측정 값 및 카테고리 차원을 선택하여 차트를 작성할 수 있습니다.

Screen Shot 2019-09-05 at 11.14.06 AM.png 


SQL 버튼을 클릭하여 생성 된 SQL을 검사하면 다음이 표시됩니다.


SELECT
  CASE
    WHEN STARTS_WITH(title, "Show HN") THEN 'Show HN'
    WHEN STARTS_WITH(title, "Ask HN") THEN 'Ask HN'
    ELSE 'Other'
  END `stories__category`,
  DATETIME_TRUNC(DATETIME(`stories`.timestamp, 'UTC'), MONTH) `stories__time_month`,
  count(*) `stories__count`
FROM
  (
    select
      *
    from
      `fh-bigquery.hackernews.full_partitioned`
    WHERE
      type = 'story'
  ) AS `stories`
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000


이 SQL은이 쿼리가 BigQuery 내부의 원시 데이터에 대해 실행됨을 보여줍니다. 이제 MySQL 내에서 미리 집계 된 테이블에 대해 실행 해 봅시다. 이를 위해 사전 집계를 정의 할 것입니다. 일반적으로 동일한 큐브 내에서 이루어 지지만 자습서를 위해 사전 집계를 사용하거나 사용하지 않고 성능을 비교할 수 있으므로 새 큐브를 만들어 보겠습니다. 같은 파일 안에서 할 수 있습니다. schema / Stories.js 파일에 다음 코드를 추가하십시오.


cube(`StoriesPreAgg`, {
  extends: Stories,
  preAggregations: {
    main: {
      type: `rollup`,
      measureReferences: [count],
      dimensionReferences: [category],
      granularity: `month`,
      timeDimensionReference: time,
      external: true
    }
  }
});


위 코드에서 롤업 유형으로 사전 집계를 선언하고 집계 테이블에 포함 할 측정 값과 차원을 지정합니다. 또한 external을 참조하십시오. 이 줄은 Cube.js에게 이 사전 집계를 MySQL에 업로드하도록 지시합니다.


이제 개발 놀이터로 이동하여 이전과 동일한 측정 값과 차원을 선택합니다. 카운트, 범주 및 시간을 월별로 그룹화 하지만 이번에는 Stories PreAgg 큐브에서 선택합니다. 처음 요청하면 Cube.js는 집계 테이블을 생성하여 MySQL에 업로드합니다. 모든 후속 요청은 MySQL 내부의 집계 테이블로 직접 이동합니다. 생성 된 SQL을 검사 할 수 있으며 다음과 같아야 합니다.


SELECT
  `stories_pre_agg__category` `stories_pre_agg__category`,
  `stories_pre_agg__time_month` `stories_pre_agg__time_month`,
  sum(`stories_pre_agg__count`) `stories_pre_agg__count`
FROM
  stb_pre_aggregations.stories_pre_agg_main
GROUP BY
  1,
  2
ORDER BY
  2 ASC
LIMIT
  10000


보다시피, 이제 MySQL 내부의 stb_pre_aggregations.stories_pre_agg_main 테이블에서 데이터를 쿼리 합니다. 필터를 사용하여 원시 쿼리와 비교하여 집계 된 쿼리의 성능 향상을 확인할 수 있습니다.


또한 여러 차트로 이 데모 대시 보드를 확인하고 사전 집계 여부와 상관없이 성능을 비교할 수 있습니다. 예제 대시 보드의 소스 코드는 Github에서 제공됩니다.