SingleStore DB 7.0에서 강화된 시계열 데이터 활용 기능

SingleStore는 데이터를 수집하고, 업데이트하고, 집계 쿼리와 동시에 쿼리하는 실시간 분석에 매우 적합니다. 실시간 분석 활용 사례는 종종 각 개별 이벤트에 타임스탬프가 있는 이벤트 데이터를 기반으로 합니다. 이러한 일련의 이벤트를 시계열로 해석하는 것은 일반적입니다.

 

7.0 릴리스 이전의 SingleStore에서도 시계열 데이터 관리에 적합한 많은 기능을 다음과 같이 제공했습니다 [Han19].

 

● 표준 SQL 인터페이스를 사용하여 트랜잭션 및 분석 워크로드를 지원하는 스케일 아웃, 비공유 아키텍처(Shared Nothing)

● 스케일 아웃과 결합된 컴파일 및 벡터화를 통한 빠른 쿼리 실행

● 분산 병렬 처리를 지원하는 내장 파이프라인 기능을 통해 데이터를 매우 빠르게 로드하는 기능

● 비 차단 동시성 제어(non-blocking concurrency control)를 통해 쓰기와 읽기가 상호 지연 시키지 않음

● 순위(Ranking), 이동 평균(Moving Average) 등을 위한 원도우 함수

● 대규모 히스토리 데이터 세트에 적합한 압축률이 높은 컬럼스토어 데이터 형식

 

이런 기능으로 이미 많은 고객들이 SingleStore를 사용하여 시계열 데이터를 관리하고 있습니다

 

SingleStore DB 7.0 릴리스의 경우 시계열 데이터를 보다 쉽게 ​​관리할 수 ​​있도록 특수 기능을 포함하였습니다. 여기에는 FIRST(), LAST(), TIME_BUCKET() 및 테이블 컬럼을 SERIES TIMESTAMP [Mem19a-d]로 지정하는 기능이 있습니다. 이를 종합하면 쿼리 규격을 통해 훨씬 적은 코드 라인과 덜 복잡한 개념으로 시계열 데이터를 요약할 수 있습니다. 이를 통해 전문 SQL 개발자의 생산성을 향상시키고 초급 개발자도 시계열 데이터를 쉽게 쿼리할 수 ​​있게 해줍니다.

 

SingleStore DB 7.0에 특별한 시계열 기능을 추가한 이유는 다음과 같습니다.

 

● 많은 고객이 이미 시계열 데이터를 위해 SingleStore를 사용하고 있었고,

● 이 고객들은 추가적인 시계열 기능을 요구했습니다.

● 일반적인 시계열 작업인 시간별 버킷팅(Bucketing)은 쉽지 않습니다.

● 원도우 함수를 사용하는 것은 시간 기반 작업에는 강력하지만 코딩할 때 복잡하고 많은 라인을 사용해야 했습니다.

● Splunk [Mil14] 및 Azure Data Explorer (Kusto) [Kus19]와 같은 이벤트 로깅 데이터 관리 플랫폼에서 시간 버킷팅에 대한 간략한 구문을 개발자가 열성적으로 사용하는 것을 보았습니다.

● 시계열 데이터를 관리하는 고객에게 시계열 전용 데이터베이스 공급 업체 보다 전반적으로 나은 데이터 관리 지원 기능을 제공할 수 있습니다. 시계열 전용 기능과 뛰어난 성능, 확장성, 안정성, SQL 지원, 연계성, 풍부한 데이터 유형 지원 등을 제공합니다.

 

메타 데이터에서 시간 속성 지정

시계열 데이터에서 간단하고 명료한 SQL 작업을 수행하기 위해 모든 새로운 시계열 함수에 시간 인수(argument)가 있음을 인식했습니다. 일반적으로 테이블에는 잘 알려진 단일 시간 속성(attribute)이 있습니다. 메타 데이터에서 이 속성을 명시적으로 나타내지 않고 시간 기반 함수의 암시적인 인수로 만들어서 시간과 관련된 모든 쿼리 표현식에서 참조할 필요가 없게 하는 이유는 무엇입니까?

 

그래서 SingleStore DB 7.0에서는 테이블의 기본 시간 컬럼을 나타내는 특수 컬럼 지정인 SERIES TIMESTAMP를 소개했습니다. 그런 다음, 이 컬럼은 시계열 함수에서 암시적 속성으로 사용됩니다. 예를 들어, 이 테이블 정의를 아래와 같이 생각해보면,

CREATE TABLE tick( ts datetime(6) series timestamp, symbol varchar(5), price numeric(18,4));

가상 주식 거래 데이터가 포함된 테이블 tick을 정의하고, ts 컬럼은 시리즈 타임스탬프(Series timestamp)로 지정되었습니다. 다음 예에서는 쿼리를 더 짧고 작성하기 쉽게 사용하는 방법을 보여줍니다.

 

시계열을 쿼리하는 오래된 방법

 

7.0에서 시계열 함수와 SERIES TIMESTAMP 지정을 사용하여 쿼리를 간단하게 작성하는 새로운 방법을 보여주기 전에 SingleStore가 7.0 이전의 시계열 데이터를 처리할 수 있는 방법의 예를 생각해 보기 바랍니다. 예로 다음의 데이터를 사용합니다.

INSERT INTO tick VALUES
  ('2020-02-18 10:55:36.179760', 'ABC', 100.00),
  ('2020-02-18 10:57:26.179761', 'ABC', 101.00),
  ('2020-02-18 10:59:16.178763', 'ABC', 102.50),
  ('2020-02-18 11:00:56.179769', 'ABC', 102.00),
  ('2020-02-18 11:01:37.179769', 'ABC', 103.00),
  ('2020-02-18 11:02:46.179769', 'ABC', 103.00),
  ('2020-02-18 11:02:59.179769', 'ABC', 102.60),
  ('2020-02-18 11:02:46.179769', 'XYZ', 103.00),
  ('2020-02-18 11:02:59.179769', 'XYZ', 102.60),
  ('2020-02-18 11:03:59.179769', 'XYZ', 102.50);

다음 쿼리는 SingleStore 6.8과 이전 버전에서도 작동합니다. 결과적으로 각 주식에 대해 별도의 행(Row)을 생성하며 매시간마다 한 번 이상 거래되었습니다. (따라서 주식이 10시간 이상, 10시간 이상 거래되면 해당 주식에 대해 10개의 행이 생성됩니다. 한 시간에 한 번의 거래 만 발생한 경우 행에는 단일 거래 또는 거래 요약이 포함됩니다. 각 행에는 시간 버킷, 주식 기호와 버킷 기간 동안의 높음, 낮음, 열림 및 닫힘이 표시됩니다. (그 시간에 한 번의 거래 만 발생한 경우 높음, 낮음, 개장과 마감은 모두 동일합니다. 해당 시간에 주식이 거래된 가격입니다.)

WITH ranked AS (
  SELECT
    symbol,
    RANK() OVER w as r,
    MIN(price) OVER w as min_pr,
    MAX(price) OVER w as max_pr,
    FIRST_VALUE(price) OVER w as first,
    LAST_VALUE(price) OVER w as last,
    from_unixtime(unix_timestamp(ts) div (60*60) * (60*60)) as ts
  FROM tick
  WINDOW w AS (PARTITION BY symbol, from_unixtime(unix_timestamp(ts) div (60*60) * (60*60))
  ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
)
SELECT ts, symbol, min_pr, max_pr, first, last
FROM ranked
WHERE r = 1
ORDER BY symbol, ts;

이 쿼리는 일반적인 출력 차트 유형인 촛대 차트 [Inv19]를 렌더링하는 데 사용할 수 있는 다음 출력을 생성합니다.

+---------------------+--------+----------+----------+----------+----------+
| ts                  | symbol | min_pr   | max_pr   | first    | last     |
+---------------------+--------+----------+----------+----------+----------+
| 2020-02-18 10:00:00 | ABC    | 100.0000 | 102.5000 | 100.0000 | 102.5000 |
| 2020-02-18 11:00:00 | ABC    | 102.0000 | 103.0000 | 102.0000 | 102.6000 |
| 2020-02-18 11:00:00 | XYZ    | 102.5000 | 103.0000 | 103.0000 | 102.5000 |
+---------------------+--------+----------+----------+----------+----------+

쿼리 텍스트는 이해할 수 있지만, 쓰기에 도전하는 것은 공통 테이블 표현 (CTE), non-trivial 함수 정의와 윈도우 함수, 그룹 당 한 개의 행을 선택하는 순위의 미묘한 사용과 불명확한 나누기/곱하기를 사용하여 시간을 60 * 60 초 버킷으로 그룹화합니다.

 

 

SingleStore DB 7.0의 새로운 시계열 함수

여기에서는 새로운 시계열 함수를 소개한 다음 새 함수를 사용하여 위의 "candlestick"쿼리에 해당하는 쿼리를 작성하는 예를 보여줍니다. 얼마나 간결한 지 확인하시기 바랍니다.

 

시계열 데이터 분석 및 새로운 시계열 함수에 대한 최신 문서를 참고하시기 바랍니다.

 

FIRST()

FIRST() 함수는 다음과 같이 두 개의 인수를 취하는 집계 함수입니다.

FIRST (value[, time]);

입력 행(Row) 집합이 주어지면 가장 작은 관련 시간의 값을 반환합니다.

 

두 번째 인수는 선택 사항입니다. 지정되지 않은 경우, 조회 중인 테이블의 SERIES TIMESTAMP 컬럼입니다. 사용 가능한 SERIES TIMESTAMP가 없거나 FIRST가 사용되는 쿼리 컨텍스트에서 둘 이상의 사용 가능한 경우 오류입니다. 이 경우 시간을 명시적으로 지정해야 합니다.

 

예를 들어, 이 쿼리는 tick 테이블의 모든 주식 간에 거래된 첫 번째 주식의 심볼을 제공합니다.

SELECT first(symbol) FROM tick;

결과는 ABC이며, 위에서 삽입 한 행에서 10 : 55 : 36.179760에 처음 거래된 것을 볼 수 있습니다.

 

LAST()

LAST는 최신 시간과 관련된 값을 제공한다는 점을 제외하고는 FIRST와 같습니다.

 

TIME_BUCKET()

TIME_BUCKET은 시간 값을 가져와서 지정된 너비로 버킷합니다. 하루에 ‘1d’, ‘5m’에 5 분 등 버킷 너비에 대한 간단한 설명을 사용할 수 있습니다. 이 함수는 다음과 같은 인수를 취합니다.

TIME_BUCKET (bucket_width [, time [,origin]])

필요한 유일한 인수는 bucket_width입니다. FIRST 및 LAST와 같이 시간 인수는 지정되지 않은 경우 SERIES TIMESTAMP로 간주됩니다. origin 인수는 버킷이 비 표준 경계에서 시작하려는 경우(예 : 매일 오전 8시에 시작하는 주간 버킷을 원하는 경우)에 사용됩니다.

 

모두 활용하기

FIRST, LAST, TIME_BUCKET 및 SERIES TIMESTAMP를 살펴보았으므로 위의 촛대 차트 쿼리를 작성하는 데 모두 사용하는 방법을 살펴보겠습니다. 동일한 쿼리의 새 버전은 다음과 같습니다.

SELECT time_bucket('1h') as ts, symbol,
  min(price) as min_pr, max(price) as max_pr, first(price) as first, last(price) as last
FROM tick
group by 2, 1
order by 2, 1;

새 버전의 쿼리는 이 출력을 생성하며 이는 원래 쿼리의 출력과 본질적으로 동일합니다.

+---------------------+--------+----------+----------+----------+----------+
| ts                  | symbol | min_pr   | max_pr   | first    | last     |
+---------------------+--------+----------+----------+----------+----------+
| 2020-02-18 10:00:00 | ABC    | 100.0000 | 102.5000 | 100.0000 | 102.5000 |
| 2020-02-18 11:00:00 | ABC    | 102.0000 | 103.0000 | 102.0000 | 102.6000 |
| 2020-02-18 11:00:00 | XYZ    | 102.5000 | 103.0000 | 103.0000 | 102.5000 |
+---------------------+--------+----------+----------+----------+----------+

이 쿼리가 얼마나 짧은지 보시기 바랍니다. 이전 버전의 경우 18줄 대 5줄 입니다. 또한 윈도우 기능이나 CTE를 사용하지 않으며 버킷 타임에 나누기/곱하기 트릭이 필요하지 않습니다. 표준 집계 함수와 스칼라 함수만 사용합니다.

 

결론

​

SingleStore DB 7.0을 사용하면 특수 함수와 SERIES TIMESTAMP 열(column) 지정을 사용하여 많은 시계열 쿼리를 훨씬 간단하게 지정할 수 있습니다. 실제 예를 들어, 우리는 코드 라인을 3배 이상 줄였으며, 보다 고급 SQL 개념을 사용할 필요가 없었습니다.

 

SingleStore의 고성능, 무제한 확장성 및 모든 SQL 지원을 고려할 때 이전 릴리스의 시계열 데이터를 위한 강력한 플랫폼이었습니다. 이제 SingleStore DB 7.0에서는 이러한 새로운 내장 기능을 통해 그 기능을 더욱 간결하게 만들었습니다. 시간 중심 데이터에 SingleStore DB 7.0을 어떻게 적용할 수 있습니까?

 

 

[참조]

[Han19] SingleStore가 시계열 응용 프로그램을 위해 할 수 있는 것 (March, 2019)

https://www.singlestore.com/blog/what-memsql-can-do-for-time-series-applications/

​

[Inv19] 기본 캔들 스틱 차트 이해, Investopedia, (2019)

https://www.investopedia.com/trading/candlestick-charting-what-is-it/

​

[Kus19] Azure Data Explorer 문서, 스칼라 값 별 요약, (2019)

https://docs.microsoft.com/en-us/azure/kusto/query/tutorial#summarize-by-scalar-values

​

[Mem19a] FIRST, SingleStore Documentation, (2019)

https://docs.singlestore.com/v7.0/reference/sql-reference/time-series-functions/first/

​

[Mem19c] TIME_BUCKET, SingleStore Documentation,

https://docs.singlestore.com/v7.0/reference/sql-reference/time-series-functions/last/

​

[Mem19c] TIME_BUCKET, SingleStore Documentation, (2019)

https://docs.singlestore.com/v7.0/reference/sql-reference/time-series-functions/time_bucket/

​

[Mem19d] CREATE TABLE Topic, SERIES TIMESTAMP, (2019)

https://docs.singlestore.com/v7.0/reference/sql-reference/data-definition-language-ddl/create-table/

​

[Mil14] James Miller, Splunk Bucketing, Mastering Splunk, O’Reilly, (2014)

https://www.oreilly.com/library/view/mastering-splunk/9781782173830/ch03s02.html

​

​

December 5, 2019

​

Eric Hanson

 

 

---

https://www.singlestore.com/blog/7-0-release-time-series-functionality/

It’s About Time: Getting More from Your Time-Series Data With SingleStoreDB On-Premises 7.0

​

※ www.a-platform.biz | info@a-platform.biz