ClickHouse SQL 구문

ClickHouse SQL 구문 (Syntax)

이 섹션에서는 ClickHouse의 SQL 구문을 살펴볼게요. ClickHouse는 SQL 기반 구문을 사용하지만 다양한 확장과 최적화를 제공해요.

출처: 문서

본문

이 섹션에서는 ClickHouse의 SQL 구문을 살펴봐요. ClickHouse는 SQL 기반 구문을 사용하지만 여러 확장과 최적화를 제공해요.

Query Parsing (쿼리 파싱)

ClickHouse에는 두 종류의 파서가 있어요.

  • 전체 SQL 파서(재귀 하강 파서, recursive descent parser).
  • 데이터 형식 파서(빠른 스트림 파서).

전체 SQL 파서는 INSERT 쿼리를 제외한 모든 경우에 사용되며, INSERT는 두 파서를 모두 사용해요. 다음 쿼리를 살펴볼게요.

INSERT INTO t VALUES (1, 'Hello, world'), (2, 'abc'), (3, 'def')

앞서 언급했듯이 INSERT 쿼리는 두 파서를 모두 사용해요. INSERT INTO t VALUES 부분은 전체 파서로 파싱되고, 데이터 (1, 'Hello, world'), (2, 'abc'), (3, 'def')는 데이터 형식 파서(빠른 스트림 파서)로 파싱돼요.

전체 파서 켜기 input_format_values_interpret_expressions 설정을 사용해 데이터에도 전체 파서를 켤 수 있어요. 해당 설정이 1로 설정되면 ClickHouse는 먼저 빠른 스트림 파서로 값을 파싱하려고 시도해요. 실패하면 ClickHouse는 데이터에 전체 파서를 사용해 SQL 표현식처럼 취급해요. 데이터는 어떤 형식이든 될 수 있어요. 쿼리를 받으면 서버는 요청의 max_query_size 바이트(기본 1MB)까지만 RAM에서 계산하고 나머지는 스트림으로 파싱해요. 이는 ClickHouse에서 데이터를 삽입하는 권장 방식인 큰 INSERT 쿼리의 문제를 피하기 위한 것이에요. INSERT 쿼리에서 Values 형식을 사용하면 데이터가 SELECT 쿼리의 표현식과 같은 방식으로 파싱되는 것처럼 보일 수 있지만 실제로는 그렇지 않아요. Values 형식은 훨씬 더 제한적이에요. 이 섹션의 나머지는 전체 파서를 다룹니다. 형식 파서에 대한 자세한 내용은 Formats 섹션을 참고해요.

Spaces (공백)

  • 구문 구성 사이(쿼리의 시작과 끝 포함)에는 임의 개수의 공백 기호가 있을 수 있어요.
  • 공백 기호는 space, tab, line feed, CR, form feed를 포함해요.

Comments (주석)

ClickHouse는 SQL 스타일과 C 스타일 주석을 모두 지원해요.

  • SQL 스타일 주석은 --, #! 또는 # 로 시작하며 줄 끝까지 이어져요. --와 #! 뒤의 공백은 생략할 수 있어요.
  • C 스타일 주석: //(또는 2개 이상의 / 문자) 뒤에 줄 끝까지의 텍스트. / 뒤의 공백은 필요하지 않아요. 여러 줄 주석은 /*에서 */까지 이어질 수 있어요. 공백도 필요하지 않아요. C 스타일 주석은 중첩될 수 있어요.

예를 들어:

/*
 * Compute the number of days between two dates.
 * /* Returns NULL if either argument is NULL */
 */
SELECT
    dateDiff('day', toDate('2024-01-01'), toDate('2024-12-31')) AS days_in_year, -- 365
    dateDiff('day', toDate('2020-01-01'), today()) AS days_since  #! since 2020
    ///////////////////////////////////////////////////////////////////
    # TODO: add hour/minute variants

Keywords (키워드)

ClickHouse의 키워드는 문맥에 따라 대소문자 구분 또는 대소문자 무시일 수 있어요. 키워드는 다음에 해당할 때 대소문자를 구분하지 않아요.

  • SQL 표준. 예를 들어 SELECT, select, SeLeCt는 모두 유효해요.
  • 널리 쓰이는 DBMS(MySQL 또는 Postgres)의 구현. 예를 들어 DateTime은 datetime과 같아요.

데이터 타입 이름이 대소문자를 구분하는지 여부는 system.data_type_families 테이블에서 확인할 수 있어요. 표준 SQL과 달리, 그 외의 모든 키워드(함수 이름 포함)는 대소문자를 구분해요. 게다가 키워드는 예약되지 않아요. 해당 문맥에서만 키워드로 취급돼요. 키워드와 같은 이름의 식별자를 사용하려면 큰따옴표나 백틱으로 감싸요. 예를 들어 table_name 테이블에 "FROM"이라는 이름의 열이 있다면 다음 쿼리는 유효해요.

SELECT "FROM" FROM table_name

Identifiers (식별자)

식별자는 다음과 같아요.

식별자는 따옴표로 묶거나 묶지 않을 수 있으며, 후자가 선호돼요. 따옴표 없는 식별자는 정규식 ^[a-zA-Z_][0-9a-zA-Z_]*$과 일치해야 하고 키워드와 같을 수 없어요. 유효한 식별자와 유효하지 않은 식별자의 예는 아래 표를 참고해요.

Valid identifiers (유효한 식별자) Invalid identifiers (유효하지 않은 식별자)
xyz, internal, Id_with_underscores_123 1x, [email protected], äußerst_schön

키워드와 같은 이름의 식별자를 사용하거나 식별자에 다른 기호를 사용하려면 큰따옴표나 백틱으로 인용해요. 예: "id", id. 따옴표로 묶인 식별자에서 이스케이프하는 규칙은 문자열 리터럴에서도 동일하게 적용돼요. 자세한 내용은 String을 참고해요.

열 이름에 점 사용 피하기 점을 포함한 열 이름, 공통 점 접두사를 공유하는 열, Array 타입의 열은 flatten_nested = 1(기본값)일 때 펼쳐진 Nested 구조의 일부로 해석될 수 있어요. 이는 삽입 시 예상치 못한 배열 길이 검증과 이름 변경 제한을 일으킬 수 있어요. 가능하면 열 이름에 점을 사용하지 마세요. 의도적으로 Nested 의미론이 필요하지 않다면 점 대신 밑줄(_)이나 다른 구분자를 사용해요.

Literals (리터럴)

ClickHouse에서 리터럴은 쿼리에 직접 표현되는 값이에요. 즉 쿼리 실행 중에 변하지 않는 고정 값이에요. 리터럴은 다음과 같을 수 있어요.

각각에 대해 아래 섹션에서 더 자세히 살펴볼게요.

String

문자열 리터럴은 작은따옴표로 묶어야 해요. 큰따옴표는 지원되지 않아요. 이스케이프는 다음 두 가지 방식으로 동작해요.

  • 앞에 작은따옴표를 두는 방식으로, 작은따옴표 문자 '(이 문자만)를 ''로 이스케이프할 수 있어요, 또는
  • 앞에 백슬래시를 두는 방식으로, 아래 표에 나열된 지원 이스케이프 시퀀스를 사용해요.

백슬래시는 아래 나열된 문자 이외의 문자 앞에 오면 특별한 의미를 잃고, 즉 문자 그대로 해석돼요.

Supported Escape (지원 이스케이프) Description (설명)
\xHH 8비트 문자 지정, 임의 개수의 16진수(H)가 뒤따름.
\N 예약됨, 아무것도 하지 않음 (예: SELECT 'a\Nb'는 ab를 반환).
\a 알람(alert)
\b 백스페이스
\e 이스케이프 문자
\f 폼 피드
\n 줄 바꿈
\r 캐리지 리턴
\t 수평 탭
\v 수직 탭
\0 null 문자
\ 백슬래시
' (또는 '' ) 작은따옴표
" 큰따옴표
` 백틱
/ 슬래시
= 등호
ASCII 제어 문자 (c <= 31).

문자열 리터럴에서는 최소한 '와 \를 이스케이프 코드 \'(또는: '')와 \\로 이스케이프해야 해요.

Numeric

숫자 리터럴은 다음과 같이 파싱돼요.

  • 리터럴 앞에 마이너스 부호 -가 붙으면 토큰은 건너뛰고 파싱 후 결과가 부정돼요.
  • 숫자 리터럴은 먼저 strtoull 함수를 사용해 64비트 부호 없는 정수로 파싱돼요. 값에 0b 또는 0x / 0X 접두사가 붙으면 숫자는 각각 이진 또는 16진수로 파싱돼요. 값이 음수이고 절대 크기가 2^63보다 크면 오류가 반환돼요.
  • 실패하면 값은 다음으로 strtod 함수를 사용해 부동 소수점 숫자로 파싱돼요.
  • 그 외에는 오류가 반환돼요.

리터럴 값은 그 값이 들어맞는 가장 작은 타입으로 캐스팅돼요. 예를 들어:

  • 1은 UInt8로 파싱돼요.
  • 256은 UInt16으로 파싱돼요.

중요 64비트보다 넓은 정수 값(UInt128, Int128, UInt256, Int256)은 제대로 파싱하려면 더 큰 타입으로 캐스팅해야 해요.

-170141183460469231731687303715884105728::Int128
340282366920938463463374607431768211455::UInt128
-57896044618658097711785492504343953926634992332820282019728792003956564819968::Int256
115792089237316195423570985008687907853269984665640564039457584007913129639935::UInt256

이렇게 하면 위 알고리즘을 우회하고 임의 정밀도를 지원하는 루틴으로 정수를 파싱해요. 그렇지 않으면 리터럴은 부동 소수점 숫자로 파싱되어 잘림으로 인한 정밀도 손실이 발생해요. 자세한 내용은 Data types를 참고해요. 숫자 리터럴 안의 밑줄 _는 무시되며 읽기 쉽게 하는 데 사용할 수 있어요. 다음 숫자 리터럴이 지원돼요.

Numeric Literal (숫자 리터럴) Examples (예시)
Integers 1, 10_000_000, 18446744073709551615, 01
Decimals 0.1
Exponential notation 1e100, -1e-100
Floating point numbers 123.456, inf, nan
Hex 0xc0fe
SQL Standard compatible hex string x'c0fe'
Binary 0b1101
SQL Standard compatible binary string b'1101'

8진수 리터럴은 해석상의 우발적 오류를 피하기 위해 지원되지 않아요.

Compound

배열은 []로 구성돼요: [1, 2, 3]. 튜플은 ()로 구성돼요: (1, 'Hello, world!', 2). 기술적으로 이들은 리터럴이 아니라 배열 생성 연산자와 튜플 생성 연산자를 가진 표현식이에요. 배열은 최소 한 개의 항목으로 구성되어야 하고, 튜플은 최소 두 개의 항목을 가져야 해요. SELECT 쿼리의 IN 절에 튜플이 나타나는 별도의 경우가 있어요. 쿼리 결과에는 튜플이 포함될 수 있지만, 튜플은 데이터베이스에 저장될 수 없어요(Memory 엔진을 사용하는 테이블 제외).

NULL

NULL은 값이 없음을 나타내는 데 사용돼요. NULL을 테이블 필드에 저장하려면 그 필드가 Nullable 타입이어야 해요. NULL에 대해 다음 사항을 유의해요.

  • 데이터 형식(입력 또는 출력)에 따라 NULL은 다른 표현을 가질 수 있어요. 자세한 내용은 data formats을 참고해요.
  • NULL 처리는 미묘해요. 예를 들어 비교 연산의 인자 중 하나라도 NULL이면 그 연산의 결과도 NULL이에요. 곱셈, 덧셈 및 다른 연산에서도 마찬가지예요. 각 연산의 문서를 읽는 것을 권장해요.
  • 쿼리에서 IS NULL 및 IS NOT NULL 연산자와 관련 함수 isNull, isNotNull을 사용해 NULL을 검사할 수 있어요.

Heredoc

heredoc은 원래 형식을 유지하면서 문자열(종종 여러 줄)을 정의하는 방법이에요. heredoc은 두 개의 $ 기호 사이에 배치된 커스텀 문자열 리터럴로 정의돼요. 예를 들어:

SELECT $heredoc$SHOW CREATE VIEW my_view$heredoc$;

┌─'SHOW CREATE VIEW my_view'─┐
│ SHOW CREATE VIEW my_view   │
└────────────────────────────┘
  • 두 heredoc 사이의 값은 "그대로(as-is)" 처리돼요.
  • heredoc을 사용해 SQL, HTML, XML 코드 조각 등을 삽입할 수 있어요.

Defining and Using Query Parameters (쿼리 매개변수 정의 및 사용)

쿼리 매개변수를 사용하면 구체적인 식별자 대신 추상적인 플레이스홀더를 포함하는 일반적인 쿼리를 작성할 수 있어요. 쿼리 매개변수가 있는 쿼리를 실행하면 모든 플레이스홀더가 해석되어 실제 쿼리 매개변수 값으로 대체돼요. 쿼리 매개변수는 여러 방식으로 정의할 수 있어요.

  • SET param_<name>=<value> — 쿼리에서 SET 명령으로.
  • --param_<name>='<value>' — 명령줄에서 clickhouse-client에 인자로.
  • param_<name>=<value> — HTTP 인터페이스의 URL 쿼리 문자열 매개변수로.

쿼리 매개변수는 {<name>: <datatype>}을 사용해 쿼리에서 참조할 수 있으며, 여기서 <name>은 쿼리 매개변수 이름이고 <datatype>은 변환되는 데이터 타입이에요.

SET 명령 예시 예를 들어 다음 SQL은 각각 다른 데이터 타입을 가진 a, b, c, d라는 이름의 매개변수를 정의해요.

SET param_a = 13;
SET param_b = 'str';
SET param_c = '2022-08-04 18:30:53';
SET param_d = {'10': [11, 12], '13': [14, 15]};

SELECT
   {a: UInt32},
   {b: String},
   {c: DateTime},
   {d: Map(String, Array(UInt8))};

13    str    2022-08-04 18:30:53    {'10':[11,12],'13':[14,15]}

clickhouse-client 예시 clickhouse-client를 사용한다면 매개변수는 --param_name=value로 지정돼요. 예를 들어 다음 매개변수는 message라는 이름이고 String으로 검색돼요.

clickhouse-client --param_message='hello' --query="SELECT {message: String}"

hello

쿼리 매개변수가 데이터베이스, 테이블, 함수 또는 다른 식별자의 이름을 나타낸다면 그 타입에 Identifier를 사용해요. 예를 들어 다음 쿼리는 uk_price_paid라는 이름의 테이블에서 행을 반환해요.

SET param_mytablename = "uk_price_paid";
SELECT * FROM {mytablename:Identifier};

HTTP 인터페이스 예시 쿼리 매개변수는 param_ 접두사를 가진 URL 쿼리 문자열 매개변수로 전달할 수 있어요. 예를 들어:

curl -s "http://localhost:8123/?param_message=hello" --data-binary "SELECT {message: String}"

hello

Web UI 예시 내장 Web UI(play.html)는 쿼리에서 {name:Type} 매개변수 플레이스홀더를 자동으로 감지하고 각 매개변수에 대해 라벨이 붙은 입력 필드를 표시해요. 매개변수 값은 HTTP 요청에 포함되며 북마크와 공유를 위해 페이지 URL에도 유지돼요. 쿼리 매개변수는 임의 SQL 쿼리의 임의 위치에서 사용할 수 있는 일반 텍스트 치환이 아니에요. 주로 SELECT 문에서 식별자나 리터럴을 대신해 동작하도록 설계됐어요.

Functions (함수)

함수 호출은 () 안에 인자 목록(비어 있을 수 있음)이 있는 식별자처럼 작성돼요. 표준 SQL과 달리 비어 있는 인자 목록에서도 괄호가 필수예요. 예를 들어:

now()

다음도 존재해요.

일부 집계 함수는 괄호 안에 두 개의 인자 목록을 포함할 수 있어요. 예를 들어:

quantile (0.9)(x)

이런 집계 함수는 "파라메트릭(parametric)" 함수라고 불리며, 첫 번째 목록의 인자는 "매개변수"라고 불러요. 매개변수 없는 집계 함수의 구문은 일반 함수와 같아요.

Operators (연산자)

연산자는 쿼리 파싱 중에 우선순위와 결합성(associativity)을 고려해 해당 함수로 변환돼요. 예를 들어 표현식

1 + 2 * 3 + 4

는

plus(plus(1, multiply(2, 3)), 4)

로 변환돼요.

Data Types and Database Table Engines (데이터 타입과 데이터베이스 테이블 엔진)

CREATE 쿼리의 데이터 타입과 테이블 엔진은 식별자나 함수와 같은 방식으로 작성돼요. 즉 괄호 안에 인자 목록을 포함하거나 포함하지 않을 수 있어요. 자세한 내용은 다음 섹션을 참고해요.

Expressions (표현식)

표현식은 다음 중 하나일 수 있어요.

  • 함수
  • 식별자
  • 리터럴
  • 연산자의 적용
  • 괄호 안의 표현식
  • 서브쿼리
  • 별표(asterisk)

별칭도 포함할 수 있어요. 표현식 목록은 쉼표로 구분된 하나 이상의 표현식이에요. 함수와 연산자는 차례로 표현식을 인자로 가질 수 있어요. 상수 표현식은 결과가 쿼리 분석 중, 즉 실행 전에 알려진 표현식이에요. 예를 들어 리터럴에 대한 표현식은 상수 표현식이에요.

Expression Aliases (표현식 별칭)

별칭은 쿼리에서 표현식에 대한 사용자 정의 이름이에요.

expr AS alias

위 구문의 각 부분은 아래에서 설명돼요.

Part of syntax (구문 부분) Description (설명) Example Notes (참고)
AS 별칭을 정의하는 키워드. SELECT 절에서 AS 키워드 없이 테이블 이름이나 열 이름의 별칭을 정의할 수 있어요. SELECT table_name_alias.column_name FROM table_name table_name_alias. CAST 함수에서 AS 키워드는 다른 의미를 가져요. 함수 설명을 참고하세요.
expr ClickHouse가 지원하는 모든 표현식. SELECT column_name * 2 AS double FROM some_table
alias expr의 이름. 별칭은 식별자 구문을 따라야 해요. SELECT "table t".column_name FROM table_name AS "table t".

Notes on Usage (사용 참고 사항)

  • 별칭은 쿼리나 서브쿼리에 대해 전역이며, 쿼리의 어떤 부분에서든 어떤 표현식에 대해서도 별칭을 정의할 수 있어요. 예를 들어:
SELECT (1 AS n) + 2, n.
  • 별칭은 서브쿼리 안이나 서브쿼리 사이에서는 보이지 않아요. 예를 들어 다음 쿼리를 실행하면 ClickHouse는 Unknown identifier: num 예외를 생성해요.
`SELECT (SELECT sum(b.a) + num FROM b) - a.a AS num FROM a`
  • 서브쿼리의 SELECT 절에서 결과 열에 별칭이 정의되면 이 열들은 바깥 쿼리에서 보여요. 예를 들어:
SELECT n + m FROM (SELECT 1 AS n, 2 AS m).
  • 열 이름이나 테이블 이름과 같은 별칭에 주의해요. 다음 예시를 고려해 보세요.
CREATE TABLE t
(
    a Int,
    b Int
)
ENGINE = TinyLog();

SELECT
    argMax(a, b),
    sum(b) AS b
FROM t;

Received exception from server (version 18.14.17):
Code: 184. DB::Exception: Received from localhost:9000, 127.0.0.1. DB::Exception: Aggregate function sum(b) is found inside another aggregate function in query.

앞선 예시에서 열 b를 가진 테이블 t를 선언했어요. 그런 다음 데이터를 선택할 때 sum(b) AS b 별칭을 정의했어요. 별칭이 전역이므로 ClickHouse는 표현식 argMax(a, b)의 리터럴 b를 표현식 sum(b)로 대체했어요. 이 대체가 예외를 일으킨 것이에요. prefer_column_name_to_alias를 1로 설정해 이 기본 동작을 바꿀 수 있어요.

Asterisk (별표)

SELECT 쿼리에서 별표는 표현식을 대체할 수 있어요. 자세한 내용은 SELECT 섹션을 참고해요.

더 알아보기 (Learn more)