MongoDB 테이블 엔진

MongoDB 엔진은 원격 MongoDB 컬렉션에서 데이터를 읽을 수 있게 해주는 읽기 전용 테이블 엔진이에요. MongoDB >=7만 지원해요. 시드 목록(mongodb+srv)은 아직 지원되지 않아요.

출처: 문서

본문

테이블 생성하기

CREATE TABLE [IF NOT EXISTS] [db.]table_name
(
    name1 [type1],
    name2 [type2],
    ...
) ENGINE = MongoDB(host:port, database, collection, user, password[, options[, oid_columns]]);

엔진 매개변수

Parameter	Description
`host:port`	MongoDB 서버 주소
`database`	원격 데이터베이스 이름
`collection`	원격 컬렉션 이름
`user`	MongoDB 사용자
`password`	사용자 비밀번호
`options`	선택. URL 형식 문자열로 된 MongoDB 연결 문자열 options. 예: `'authSource=admin&ssl=true'`
`oid_columns`	WHERE 절에서 `oid`로 처리해야 하는 쉼표로 구분된 컬럼 목록. 기본값은 `_id`.

MongoDB Atlas 클라우드 오퍼링을 사용한다면 'Atlas SQL' 옵션에서 연결 URL을 얻을 수 있어요. 시드 목록(mongodb**+srv**)은 아직 지원되지 않지만 향후 릴리스에서 추가될 예정이에요. 또는 URI를 전달할 수도 있어요.

ENGINE = MongoDB(uri, collection[, oid_columns]);

엔진 매개변수

Parameter	Description
`uri`	MongoDB 서버의 연결 URI
`collection`	원격 컬렉션 이름
`oid_columns`	WHERE 절에서 `oid`로 처리해야 하는 쉼표로 구분된 컬럼 목록. 기본값은 `_id`.

타입 매핑

MongoDB	ClickHouse
bool, int32, int64	Decimals를 제외한 모든 숫자 타입, Boolean, String
double	Float64, String
date	Date, Date32, DateTime, DateTime64, String
string	String, 올바른 형식이면 Decimals를 제외한 모든 숫자 타입
document	String(JSON으로)
array	Array, String(JSON으로)
oid	String
binary	컬럼에 있으면 String, 배열이나 문서에 있으면 base64 인코딩 문자열
uuid (binary subtype 4)	UUID
기타 모든 타입	String

MongoDB 문서에서 키를 찾지 못하면(예: 컬럼 이름이 일치하지 않으면) 기본값 또는 컬럼이 nullable인 경우 NULL이 삽입돼요.

OID

WHERE 절에서 String을 oid로 처리하려면 테이블 엔진의 마지막 인자에 컬럼 이름을 넣어주세요. 이는 MongoDB에서 기본적으로 oid 타입을 갖는 _id 컬럼으로 레코드를 조회할 때 필요할 수 있어요. 테이블의 _id 필드가 uuid 같은 다른 타입이라면 빈 oid_columns를 지정해야 해요. 그렇지 않으면 이 매개변수의 기본값 _id가 사용되기 때문이에요.

db.sample_oid.insertMany([
    {"another_oid_column": ObjectId()},
]);

db.sample_oid.find();
[
    {
        "_id": {"$oid": "67bf6cc44ebc466d33d42fb2"},
        "another_oid_column": {"$oid": "67bf6cc40000000000ea41b1"}
    }
]

기본적으로 _id만 oid 컬럼으로 처리돼요.

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:***@host/db', 'sample_oid');

SELECT count() FROM sample_oid WHERE _id = '67bf6cc44ebc466d33d42fb2'; --will output 1.
SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; --will output 0

이 경우 ClickHouse는 another_oid_column이 oid 타입이라는 것을 알지 못하므로 출력은 0이 돼요. 고쳐볼게요.

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('mongodb://user:***@host/db', 'sample_oid', '_id,another_oid_column');

-- or

CREATE TABLE sample_oid
(
    _id String,
    another_oid_column String
) ENGINE = MongoDB('host', 'db', 'sample_oid', 'user', 'pass', '', '_id,another_oid_column');

SELECT count() FROM sample_oid WHERE another_oid_column = '67bf6cc40000000000ea41b1'; -- will output 1 now

지원되는 절(Clauses)

단순 표현식이 있는 쿼리만 지원돼요 (예: WHERE field = <constant> ORDER BY field2 LIMIT <constant>). 이러한 표현식은 MongoDB 쿼리 언어로 변환되어 서버 측에서 실행돼요. mongodb_throw_on_unsupported_query를 사용해 이러한 제한을 모두 비활성화할 수 있어요. 그 경우 ClickHouse는 최선을 다해 쿼리를 변환하려 하지만 전체 테이블 스캔과 ClickHouse 측 처리로 이어질 수 있어요.

Mongo는 엄격한 타입 필터를 요구하므로 리터럴 타입을 명시적으로 지정하는 것이 항상 더 좋아요. 예를 들어 Date로 필터링하려면:

SELECT * FROM mongo_table WHERE date = '2024-01-01'

이것은 Mongo가 문자열을 Date로 캐스팅하지 않으므로 동작하지 않아요. 수동으로 캐스팅해야 해요.

SELECT * FROM mongo_table WHERE date = '2024-01-01'::Date OR date = toDate('2024-01-01')

이것은 Date, Date32, DateTime, Bool, UUID에 적용돼요.

사용 예시

MongoDB에 sample_mflix 데이터셋이 로드되어 있다고 가정해요. MongoDB 컬렉션에서 데이터를 읽을 수 있는 ClickHouse 테이블을 만들어볼게요.

Query

CREATE TABLE sample_mflix_table
(
    _id String,
    title String,
    plot String,
    genres Array(String),
    directors Array(String),
    writers Array(String),
    released Date,
    imdb String,
    year String
) ENGINE = MongoDB('mongodb://<USERNAME>:***@atlas-sql-6634be87cefd3876070caf96-98lxs.a.query.mongodb.net/sample_mflix?ssl=true&authSource=admin', 'movies');

Query

SELECT count() FROM sample_mflix_table

Response

┌─count()─┐
│   21349 │
└─────────┘

Query

-- JSONExtractString cannot be pushed down to MongoDB
SET mongodb_throw_on_unsupported_query = 0;

-- Find all 'Back to the Future' sequels with rating > 7.5
SELECT title, plot, genres, directors, released FROM sample_mflix_table
WHERE title IN ('Back to the Future', 'Back to the Future Part II', 'Back to the Future Part III')
    AND toFloat32(JSONExtractString(imdb, 'rating')) > 7.5
ORDER BY year
FORMAT Vertical;

Response

Row 1:
──────
title:     Back to the Future
plot:      A young man is accidentally sent 30 years into the past in a time-traveling DeLorean invented by his friend, Dr. Emmett Brown, and must make sure his high-school-age parents unite in order to save his own existence.
genres:    ['Adventure','Comedy','Sci-Fi']
directors: ['Robert Zemeckis']
released:  1985-07-03

Row 2:
──────
title:     Back to the Future Part II
plot:      After visiting 2015, Marty McFly must repeat his visit to 1955 to prevent disastrous changes to 1985... without interfering with his first trip.
genres:    ['Action','Adventure','Comedy']
directors: ['Robert Zemeckis']
released:  1989-11-22

Query

-- Find top 3 movies based on Cormac McCarthy's books
SELECT title, toFloat32(JSONExtractString(imdb, 'rating')) AS rating
FROM sample_mflix_table
WHERE arrayExists(x -> x LIKE 'Cormac McCarthy%', writers)
ORDER BY rating DESC
LIMIT 3;

Response

┌─title──────────────────┬─rating─┐
│ No Country for Old Men │    8.1 │
│ The Sunset Limited     │    7.4 │
│ The Road               │    7.3 │
└────────────────────────┴────────┘

문제 해결(Troubleshooting)

생성된 MongoDB 쿼리를 DEBUG 레벨 로그에서 볼 수 있어요. 구현 세부 사항은 mongocxx와 mongoc 문서에서 확인할 수 있어요.

더 알아보기 (Learn more)

MongoDB 테이블 함수