Databricks SQL의 2023년 리뷰(1부): AI로 최적화된 성능과 서버리스 컴퓨팅
이 글은 2023년 Databricks SQ 의 주요 발전 영역을 되돌아보는 블로그 시리즈의 1부이며, 첫 번째 글에서는 성능에 초점을 맞추고 있습니다. 컴퓨팅 시간이 비용을...
데이터브릭스에서 제공하는 서버리스 데이터 웨어하우스인 Databricks SQL의 2023년 제품 개선 사항을 다루는 블로그 시리즈에 오신 것을 환영합니다. 이번 2부에서는 2023년에 추가된 여러 새로운 SQL 프로그래밍 기능을 중점적으로 소개합니다. 당연하게도, 모든 SQL 개발자는 생산성을 높이고 더욱 복잡한 시나리오를 쉽게 처리하기를 원합니다. 이와 같은 SQL 기능을 추가함으로써 개발자와 고객이 Databricks SQL 웨어하우스를 최대한 활용할 수 있습니다. 이 모든 것이 데이터 웨어하우징과 데이터 레이크의 장점을 결합한 레이크하우스 아키텍처를 기반으로 구축된 데이터브릭스 데이터 인텔리전스 플랫폼의 일부이며, 최고의 데이터 웨어하우스가 레이크하우스인 이유이기도 합니다.
자 그럼 바로 2023년의 주요 SQL 프로그래밍 기능 소개로 들어가 보시죠.
측면 열 별칭(Lateral Column Alias) 지원
커피가 몸에 좋지 않다면 왜 모두가 커피를 마실까요? 측면 열 지원이 바로 그런 이유입니다. SQL의 원칙에는 어긋나지만, 이 기능을 사용하면 select 목록에 있는 SQL 표현식의 결과를 동일한 select 목록의 다음 표현식에서 참조할 수 있기 때문에 분명 유용합니다. SQL 순수성이라는 명목으로 그토록 오랫동안 표현식을 공유하기 위해 어쩔 수 없이 sub query를 밀어 넣어야 했는지 되돌아보게 될 것입니다.
이전:
SELECT fullname,
upper(fullname),
lower(fullname)
FROM (SELECT name || firstname
FROM persons) AS T(fullname);이후 (Lateral Column Alias 사용):
SELECT name || firstname AS fullname,
upper(fullname),
lower(fullname)
FROM persons;자세한 내용은 측면 열 별칭 소개를 참조하세요.
Error 클래스와 SQLSTATE
오랜 시간이 걸렸지만, 데이터브릭스에서 발생하는 대부분의 오류 조건은 가독성있는 오류 분류(error classification)와 SQL 표준 기반 SQLSTATE로 표시됩니다. 이러한 오류 메시지는 문서화되어 있으며, Python과 Scala의 경우 오류 메시지 텍스트에 대한 종속성을 지정하지 않고도 오류 조건을 프로그래밍 방식으로 처리할 수 있는 메서드도 제공합니다.
예:
from pyspark.errors import PySparkException
try:
spark.sql("SELECT * FROM does_not_exist").show()
except PySparkException as ex:
if (ex.getErrorClass() == "TABLE_OR_VIEW_NOT_FOUND"):
print("I'm so sorry, but I cannot find: " + ex.getMessageParameters()['relationName'])
else:
raise자세한 내용은 데이터브릭스에서의 에러 처리를 참조하세요.
일반 테이블 값 함수(table-valued function) 지원
2023년에는 테이블 값 함수 지원 영역에서 많은 개선이 이루어졌습니다. 먼저 테이블 함수 호출을 일반화하고 표준화하여 이제 쿼리의 FROM 절에서 모든 테이블 함수를 호출할 수 있으며, 심지어 explode()와 같은 generator 함수도 호출할 수 있고, 더 이상 LATERAL VIEW 구문이 필요하지 않습니다.
이전:
SELECT *,
explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS (z1)
FROM VALUES('a'), ('b') AS X(c1)
LATERAL VIEW explode(ARRAY(1, 2)) Y AS y1;
a 1 x1
a 1 y1
a 2 x2
a 2 y2
b 1 x1
b 1 y1
b 2 x2
b 2 y2이후:
SELECT *
FROM VALUES('a'), ('b') AS X(c1),
explode(ARRAY(1, 2)) AS Y(y1),
LATERAL explode(ARRAY('x' || Y.y1, 'y' || Y.y1)) AS Z(z1);
a 1 x1
a 1 y1
a 2 x2
a 2 y2
b 1 x1
b 1 y1
b 2 x2
b 2 y2자세한 내용은 테이블 값 함수 호출을 참조하세요.
다형성을 지원하는 Python UDF와 UDTF
SQL UDF는 데이터브릭스 9에 도입되어 큰 성공을 거두었지만, Python 커뮤니티에서 질투를 느낀 나머지 더 높은 수준으로 끌어올렸습니다! 이제 다음의 기능도 사용할 수 있습니다:
- Python UDF를 생성하고 그 안에 멋진 로직을 넣을 수 있습니다.
- SQL 표준 TABLE 구문을 사용하여 Python 테이블 UDF에 테이블을 전달할 수 있습니다. 이를 다형성이라고 하며, 전달된 테이블의 시그니처에 따라 UDF가 다르게 작동할 수 있습니다.
예:
from pyspark.sql.functions import udtf
from pyspark.sql.types import Row
@udtf(returnType="id: int")
class FilterUDTF:
def eval(self, row: Row):
if row["id"] > 5:
yield row["id"],
spark.udtf.register("filter_udtf", FilterUDTF)
SELECT * FROM filter_udtf(TABLE(SELECT * FROM range(10)));
6
7
8
9더 자세한 내용은 Python 사용자 정의 테이블 함수 소개, 함수 호출 그리고 python_udtf.rst 테이블 입력 인수를 참조하세요.
이름 없는 매개변수 마커(Unnamed Parameter Markers)
2022년에는 SQL 쿼리가 spark.sql() API 등을 사용해 SQL에 전달된 placeholder 변수를 참조할 수 있는 매개변수 마커를 도입했습니다. 초기 지원은 명명된 매개변수 마커(named parameters markers)로 구성되었습니다. 즉, key가 매개변수 마커의 이름과 일치하는 map을 사용해 Python, Java 또는 Scala 값이 SQL로 전달되는 방식이었죠. 이 방식은 동일한 인수를 순서 없이 반복해서 참조할 수 있다는 점에서 매우 유용합니다.
2023년에는 이름 없는 매개변수 마커(unnamed parameter markers)에 대한 지원을 확대했습니다. 이제 값의 배열을 전달할 수 있으며, 값은 나열 순서대로 할당됩니다.
예:
spark.sql("SELECT ? * ? * ? AS volume", args = { 3, 4, 5 }).show()
+------+
|volume|
+------+
| 60|
+------+자세한 내용은 이름 없는 매개변수 마커를 참조하세요.
SQL 세션 변수
매개변수 마커는 훌륭합니다. 우리도 이를 좋아합니다. 하지만 SQL의 결과를 데이터 프레임을 통해 다시 전달하지 않고 매개변수 마커를 통해 다시 SQL로 전달할 수 있다면 더욱 좋을 것입니다. SQL 세션 변수가 바로 그것입니다. SQL 세션 변수는 스칼라(테이블이 아닌) 객체로서, 이 변수의 정의(definition)와 값(value) 모두 SQL 세션에 종속된 (private) 변수입니다. 다음과 같이 활용할 수 있습니다:
- 타입과 초기 기본값이 있는 세션 변수를 선언할 수 있습니다.
- SQL 표현식 또는 쿼리 결과에 따라 하나 이상의 변수를 설정할 수 있습니다.
- 쿼리 내 또는 DML 문 내에서 변수를 참조할 수 있습니다.
이렇게 하면 쿼리를 분할하고 한 쿼리에서 다음 쿼리로 상태를 전달할 수 있는 좋은 방법이 됩니다.
예:
DECLARE var INTEGER DEFAULT 5;
SELECT var;
5
SET VAR var = (SELECT max(c1) * var FROM VALUES(1), (2), (3) AS T(c1));
SELECT var;
15자세한 내용은 변수 참고문서를 참조하세요
IDENTIFIER 구문
앞의 두 가지 하이라이트에서는 애플리케이션이나 노트북에서 전달받은 값으로 쿼리를 매개변수화하거나 테이블에서 조회한 세션 변수를 사용해 쿼리를 매개변수화하는 방법을 보여드렸습니다. 하지만 테이블 이름, 함수 이름 등 식별자를 매개변수화하고 싶 지 않으신가요? SQL 주입에 대한 XKCD 농담의 대상이 되지 않으면서도요? IDENTIFIER 절을 사용하면 바로 그렇게 할 수 있습니다. 세션 변수의 문자열 값이나 매개변수 마커를 사용하여 제공된 문자열 값을 함수, 테이블 또는 열 참조로 사용할 수 있는 SQL 이름으로 마술처럼 바꿔줍니다.
예:
DECLARE agg = 'max';
DECLARE col = 'c1';
DECLARE tab = 'T';
CREATE TEMPORARY VIEW IDENTIFIER(tab)(c1, c2) AS (VALUES ('a', 'b'), ('c', 'd'));
SELECT IDENTIFIER(agg)(IDENTIFIER(col)) FROM IDENTIFIER(tab);
c자세한 내용은 IDENTIFIER 구문을 참조하세요.
INSERT BY NAME
INSERT BY NAME은 왜 SQL이 넓은 테이블(즉, 컬럼이 많은 테이블)을 처리하기 위해 그런 식으로 탄생하지 않았는지 의문이 들게 하는 멋진 사용성 기능입니다. 많은 컬럼을 처리할 때 INSERT가 제공하는 select 목록에서 컬럼 순서 찾는 것을 좋아하시는 분은 손을 들어 보세요. 아니면 INSERT 대상의 긴 컬럼 목록을 타이핑하는 것을 선호하시나요? 그런 사람은 없습니다.
이제 컬럼 목록을 나열하고 select 목록 순서를 확인 또 확인하는 대신, 데이터브릭스에 이 작업을 대신 수행하도록 지시할 수 있습니다. INSERT BY NAME만 입력하면 데이터브릭스가 select 목록을 테이블 컬럼에 맞춰 정렬해 줍니다.
예:
CREATE TABLE T(c1 INT, c2 INT);
INSERT INTO T BY NAME SELECT 1 AS c2, 2 AS c1;
SELECT * FROM T;
2 1자세한 내용은 INSERT INTO 문서를 참조하세요.
명명된 매개변수 호출
30개의 인수를 받는 함수를 작성했는데 대부분의 인수가 합리적인 기본값을 가지고 있다고 가정해 보겠습니다. 하지만 이제 기본값이 아닌 마지막 인수를 사용하여 함수를 호출해야 한다면 어떻게 할까요? 그냥 "건너뛰고" 그 매개변수 하나만 설정하고 인수의 순서는 걱정하지 마세요! 인수가 어떤 매개변수를 위한 것인지 알려주기만 하면 됩니다.
예:
CREATE FUNCTION my_tan(sin FLOAT, cos FLOAT) RETURN sin / cos;
SELECT my_tan(cos => 0.4, sin => 0.1);
0.25자세한 내용은 명명된 매개변수 호출 문서를 참조하세요.
timezone 없는 TIMESTAMP
기본적으로 데이터브릭스의 timestamp는 "현지 시간대 (local timezone) 사용"입니다. timestamp 값을 넣으면 데이터브릭스는 해당 timestamp가 사용자의 표준 시간대(locale timezone)라고 가정하고 UTC로 정규화된 상태로 저장합니다. 이 값을 다시 읽으면 이 번역이 해제되어 정상적으로 보입니다. 그러나 다른 사용자가 다른 시간대에서 timestamp를 다시 읽으면 정규화된 타임스탬프가 자신의 시간대로 번역된 것을 볼 수 있습니다.
TIMESTAMP_NTZ는 시간을 액면 그대로 받아들이는 새로운 유형입니다. 2024년 1월 4일 오후 2시를 입력하면 이를 저장합니다.
예:
SET TIME ZONE 'America/Los_Angeles';
DECLARE local_time TIMESTAMP_LTZ = TIMESTAMP'2023-12-01 12:13:14';
DECLARE any_time TIMESTAMP_NTZ = TIMESTAMP'2023-12-01 12:13:14';
SELECT local_time, any_time;
2023-12-01 12:13:14 2023-12-01 12:13:14
SET TIME ZONE 'America/New_York';
SELECT local_time, any_time;
2023-12-01 15:13:14 2023-12-01 12:13:14자세한 내용은 TIMESTAMP_NTZ 소개를 참조하세요.
페더레이션 쿼리 지원
물론 당신의 모든 데이터가 이미 레이크하우스에 있다는 것을 알고 있습니다. 하지만 아직 다른 곳에 데이터를 가지고 있는 친구가 있더라도 걱정하지 마세요. 데이터브릭스를 떠나지 않고도 외부 테이블을 데이터브릭스 Unity Catalog에 등록하고 모든 SQL 쿼리를 실행하여 데이터브릭스에서 이 데이터에 액세스할 수 있습니다. 원격 시스템에 대한 연결을 등록하고 원격 카탈로그(일명 데이터베이스)를 연결한 다음 컨텐츠를 쿼리하기만 하면 됩니다. 물론 동일한 쿼리에서 로컬 테이블과 외부 테이블을 혼합하여 사용할 수 있습니다.
예:
CREATE CONNECTION postgresql_connection
TYPE POSTGRESQL
OPTIONS (
host 'qf-postgresql-demo.xxxxxx.us-west-2.rds.amazonaws.com',
port '5432',
user 'postgresql_user',
password 'password123');
CREATE FOREIGN CATALOG pg
USING CONNECTION postgresql_connection
OPTIONS (database 'postgresdb');
SELECT * FROM pg.myschema.t;자세한 내용은 Federated Queries 를 참조하세요,
행 수준 보안(Row-level Security)과 열 마스킹(Column Masking)
비밀 유지가 필요하신가요? 일부 사용자에게 테이블에 대한 액세스 권한을 부여해야 하지만 모든 비밀을 표시하고 싶지 않으신가요? 그렇다면 행 수준 보안 및 열 마스킹이 필요할 수 있겠군요. 다른 사용자와 그룹에게 테이블에 대한 액세스 권한을 부여하되, 어떤 행을 볼 수 있는지에 대한 맞춤형 규칙을 설정할 수 있습니다. 신용카드 번호의 마지막 세 자리를 제외한 모든 숫자를 별표로 대체하는 등 PII(개인 식별 정보)를 공백으로 처리하거나 난독화할 수도 있습니다.
행 필터를 추가하려면 함수 인수를 기반으로 사용자가 행을 볼 수 있는지 여부를 결정하는 UDF를 만듭니다. 그런 다음 ALTER TABLE을 사용하여 테이블에 행 필터를 추가하거나 CREATE TABLE을 실행할 때 행 필터를 추가합니다.
예:
CREATE FUNCTION us_filter(region STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');
CREATE TABLE sales (region STRING, id INT);
ALTER TABLE sales SET ROW FILTER us_filter ON (region);열 마스크를 추가하려면:
특정 타입의 데이터를 가져와서 사용자에 따라 수정하고 결과를 반환하는 UDF를 만듭니다. 그런 다음 테이블을 만들 때 또는 ALTER TABLE을 사용하여 마스크를 열에 첨부합니다.
예:
CREATE FUNCTION ssn_mask(ssn STRING)
RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;
CREATE TABLE users (
name STRING,
ssn STRING MASK ssn_mask);
SELECT * FROM users;
Jack ***-**-***자세한 내용은 Row Filters and Column Masks 문서를 참조하세요.
GROUP BY ALL 과 ORDER BY ALL
자, 당신이 멋진 리포팅 쿼리를 작성했는데, GROUP BY 절에서 이미 앞에 나열한 모든 그룹화 열을 다시 나열해야 하는 SQL 문법 때문에 "MISSING_AGGREGATION" 오류가 발생했습니다.
"목록을 만드세요! 그리고 두 번 확인하세요!"는 어떤 사람들에게는 좋은 조언입니다. 하지만 다른 사람들에게는 그리 좋지 않습니다.
이제 데이터브릭스에게 이 작업을 대신 하도록 지시하고 그룹화할 모든 열을 수집하면 됩니다.
또한, 원하는 경우 반환된 모든 열을 기준으로 결과 집합의 순서를 지정할 수도 있습니다.
예:
SELECT name, firstname, level, sum(comp) as totalcomp
FROM VALUES('The Cricket', 'Jimmy' , 'Principal Conscience', 2),
('Geppetto' , 'Signore', 'Woodcarver' , 1)
AS emp(name, firstname, level, empid)
NATURAL JOIN VALUES(1, 200, 'salary'),
(1, 100, 'spot' ),
(2, 250, 'salary'),
(2, 120, 'spot' )
AS pay(empid, comp, reason)
GROUP BY ALL
ORDER BY ALL;
Geppetto Signore Woodcarver 300
The Cricket Jimmy Principal Conscience 370자세한 내용은 GROUP BY, ORDER BY 문서를 참조하세요.
더 많은 SQL 내장 펑션 지원
개발자의 삶에는 두 가지 확실한 것이 있습니다: 버블티는 절대 충분하지 않다는 것과 내장 함수는 절대 충분하지 않다는 것입니다. 날짜/시간 유형에 대한 to_char 및 to_varchar와 같이 다른 제품과의 호환성을 향상시키는 다양한 함수 외에도 배열 처리 함수 집합과 비트맵 및 hll_sketch 함수 라이브러리를 대폭 확장하는 데 중점을 두었습니다. 비트맵 함수는 각각 정수에 대한 고유 값 계산 유형(count distinct)의 쿼리 속도를 높일 수 있습니다. 반면 데이터스케치는 다양한 확률적 카운팅 기능을 지원합니다.
예:
SELECT mask('AaBb123-&^ % 서울 Ä', lowerchar => 'z', otherchar => 'X');
AzBz123XXXXXXXXXÄ
SELECT sum(num_distinct) AS num_distinct
FROM(SELECT bitmap_bucket_number(val),
bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) FROM VALUES(1), (2), (1), (-1), (5), (0), (5) AS t(val) GROUP BY ALL) AS distinct_vals_by_bucket(bucket, num_distinct);
5
SELECT hll_sketch_estimate(
hll_sketch_agg(col))
FROM VALUES('abc'), ('def'), ('abc'), ('ghi'), ('abc') AS tab(col);
3자세한 내용은 Mask function, bitmap_count function, to_varchar function, sketch based approximate distinct counting 을 참조하세요.
Databricks ❤️ SQL
데이터브릭스는 데이터 웨어하우스의 이름도 SQL의 이름을 따서 지을 정도로 SQL을 사랑합니다! 그리고 최고의 데이터 웨어하우스는 레이크하우스이기 때문에, SQL과 Python은 전체 Databricks Intelligent Data Platform에서 모두 최고 수준의 경험을 제공합니다. 고객이 프로젝트에 SQL을 사용할 수 있도록 위와 같은 새로운 기능을 추가하게 되어 기쁘게 생각하며, 이미 더 많은 기능을 추가하기 위해 다시 작업 중입니다.
SQL 개발자를 위한 훌륭한 환경을 갖춘 고성능 서버리스 데이터 웨어하우스로 SQL 워크로드를 마이그레이션하고 싶으시다면, Databricks SQL이 바로 그 해결책이 될 것입니다.
Databricks 무료로 시작하기
