쿼리 서비스의 SQL 구문

Adobe Experience Platform 쿼리 서비스는 SELECT 문 및 기타 제한된 명령에 표준 ANSI SQL을 사용하는 기능을 제공합니다. 이 문서에서는 Query Service에서 지원하는 SQL 구문에 대해 설명합니다.

SELECT 쿼리

다음 구문은 Query Service에서 지원하는 SELECT 쿼리를 정의합니다.

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT [( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]
    [ WHERE condition ]
    [ GROUP BY grouping_element [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start ]

여기서 from_item은(는) 다음 옵션 중 하나일 수 있습니다.

table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
[ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

grouping_element은(는) 다음 옵션 중 하나일 수 있습니다.

( )
expression
( expression [, ...] )
ROLLUP ( { expression | ( expression [, ...] ) } [, ...] )
CUBE ( { expression | ( expression [, ...] ) } [, ...] )
GROUPING SETS ( grouping_element [, ...] )

with_query은(는) 다음과 같습니다.

 with_query_name [ ( column_name [, ...] ) ] AS ( select | values )

다음 하위 섹션은 위에서 설명한 형식을 따를 경우 쿼리에 사용할 수 있는 추가 조항의 세부 사항을 제공합니다.

SNAPSHOT 절

이 절은 스냅샷 ID를 기반으로 테이블의 데이터를 증분적으로 읽는 데 사용할 수 있습니다. 스냅샷 ID는 데이터가 기록될 때마다 데이터 레이크 테이블에 적용되는 긴 형식의 숫자로 표시되는 체크포인트 표시자입니다. SNAPSHOT 절은 다음에 사용되는 테이블 관계에 첨부됩니다.

    [ SNAPSHOT { SINCE start_snapshot_id | AS OF end_snapshot_id | BETWEEN start_snapshot_id AND end_snapshot_id } ]

SELECT * FROM Customers SNAPSHOT SINCE 123;

SELECT * FROM Customers SNAPSHOT AS OF 345;

SELECT * FROM Customers SNAPSHOT BETWEEN 123 AND 345;

SELECT * FROM Customers SNAPSHOT BETWEEN HEAD AND 123;

SELECT * FROM Customers SNAPSHOT BETWEEN 345 AND TAIL;

SELECT * FROM (SELECT id FROM CUSTOMERS BETWEEN 123 AND 345) C 

SELECT * FROM Customers SNAPSHOT SINCE 123 INNER JOIN Inventory AS OF 789 ON Customers.id = Inventory.id;

SNAPSHOT 절은 테이블 또는 테이블 별칭과 함께 사용되지만 하위 쿼리 또는 보기의 위쪽에는 적용되지 않습니다. SNAPSHOT 절은 테이블의 SELECT 쿼리를 적용할 수 있는 모든 곳에서 작동합니다.

또한 스냅샷 절을 위한 특수 오프셋 값으로 HEADTAIL을 사용할 수 있습니다. HEAD을(를) 사용하면 첫 번째 스냅샷 전의 오프셋을 참조하고, TAIL는 마지막 스냅샷 후의 오프셋을 참조합니다.

WHERE 절

기본적으로 SELECT 쿼리에서 WHERE 절로 생성된 일치 항목은 대/소문자를 구분합니다. 대소문자를 구분하지 않도록 일치시키려면 LIKE 대신 ILIKE 키워드를 사용할 수 있습니다.

    [ WHERE condition { LIKE | ILIKE | NOT LIKE | NOT ILIKE } pattern ]

LIKE 및 ILIKE 절의 논리는 다음 표에 설명되어 있습니다.

연산자
WHERE condition LIKE pattern ~~
WHERE condition NOT LIKE pattern !~~
WHERE condition ILIKE pattern ~~*
WHERE condition NOT ILIKE pattern !~~*

SELECT * FROM Customers
WHERE CustomerName ILIKE 'a%';

이 쿼리는 "A" 또는 "a"에서 시작하는 이름을 가진 고객을 반환합니다.

가입

조인을 사용하는 SELECT 쿼리에는 다음 구문이 있습니다.

SELECT statement
FROM statement
[JOIN | INNER JOIN | LEFT JOIN | LEFT OUTER JOIN | RIGHT JOIN | RIGHT OUTER JOIN | FULL JOIN | FULL OUTER JOIN]
ON join condition

결합, 교차 및 제외

UNION, INTERSECTEXCEPT 절은 둘 이상의 테이블에서 좋아요 행을 결합하거나 제외하는 데 사용됩니다.

SELECT statement 1
[UNION | UNION ALL | UNION DISTINCT | INTERSECT | EXCEPT | MINUS]
SELECT statement 2

선택한 대로 표 만들기

다음 구문은 CREATE TABLE AS SELECT(CTAS) 쿼리를 정의합니다.

CREATE TABLE table_name [ WITH (schema='target_schema_title', rowvalidation='false') ] AS (select_query)

매개 변수

  • schema:XDM 스키마의 제목입니다. CTAS 쿼리에서 만든 새 데이터 세트에 대해 기존 XDM 스키마를 사용하려는 경우에만 이 절을 사용하십시오.
  • rowvalidation:(선택 사항) 사용자가 새로 만든 데이터 세트에 대해 인제스트된 모든 새 배치의 행 수준 유효성 검사를 원하는지 여부를 지정합니다. 기본값은 true입니다.
  • select_query:설명 SELECT 을 참조하십시오. SELECT 쿼리의 구문은 SELECT 쿼리 섹션에서 찾을 수 있습니다.

CREATE TABLE Chairs AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs WITH (schema='target schema title') AS (SELECT color, count(*) AS no_of_chairs FROM Inventory i WHERE i.type=="chair" GROUP BY i.color)

CREATE TABLE Chairs AS (SELECT color FROM Inventory SNAPSHOT SINCE 123)
노트

SELECT 문에는 COUNT, SUM, MIN 등과 같은 집계 함수에 대한 별칭이 있어야 합니다. 또한 SELECT 문은 괄호()와 함께 또는 괄호 없이 제공할 수 있습니다. SNAPSHOT 절을 제공하여 증분 델타를 대상 테이블로 읽을 수 있습니다.

삽입 위치

INSERT INTO 명령은 다음과 같이 정의됩니다.

INSERT INTO table_name select_query

매개 변수

  • table_name:쿼리를 삽입할 테이블의 이름입니다.
  • select_query:설명 SELECT 을 참조하십시오. SELECT 쿼리의 구문은 SELECT 쿼리 섹션에서 찾을 수 있습니다.

INSERT INTO Customers SELECT SupplierName, City, Country FROM OnlineCustomers;

INSERT INTO Customers AS (SELECT * from OnlineCustomers SNAPSHOT AS OF 345)
노트

SELECT은 괄호()로 묶어서는 안 됩니다. 또한 SELECT 문의 결과 스키마는 INSERT INTO 문에 정의된 테이블의 스키마와 일치해야 합니다. SNAPSHOT 절을 제공하여 증분 델타를 대상 테이블로 읽을 수 있습니다.

드롭 테이블

DROP TABLE 명령은 기존 테이블을 삭제하고 외부 테이블이 아닌 경우 파일 시스템에서 테이블과 연관된 디렉토리를 삭제합니다. 테이블이 없으면 예외가 발생합니다.

DROP TABLE [IF EXISTS] [db_name.]table_name

매개 변수

  • IF EXISTS:이 값을 지정하면 테이블이 없으면 예외가 ​throw되지 않습니다.

보기 만들기

다음 구문은 CREATE VIEW 쿼리를 정의합니다.

CREATE VIEW view_name AS select_query

매개 변수

  • view_name:만들 보기의 이름입니다.
  • select_query:설명 SELECT 을 참조하십시오. SELECT 쿼리의 구문은 SELECT 쿼리 섹션에서 찾을 수 있습니다.

CREATE VIEW V1 AS SELECT color, type FROM Inventory

CREATE OR REPLACE VIEW V1 AS SELECT model, version FROM Inventory

드롭 뷰

다음 구문은 DROP VIEW 쿼리를 정의합니다.

DROP VIEW [IF EXISTS] view_name

매개 변수

  • IF EXISTS:이 값을 지정하면 뷰가 없으면 예외가 ​throw되지 않습니다.
  • view_name:삭제할 보기의 이름입니다.

DROP VIEW v1
DROP VIEW IF EXISTS v1

Spark SQL 명령

아래의 하위 섹션에서는 쿼리 서비스에서 지원하는 Spark SQL 명령에 대해 설명합니다.

SET

SET 명령은 속성을 설정하고 기존 속성의 값을 반환하거나 기존 속성을 모두 나열합니다. 기존 속성 키에 대한 값이 제공되면 이전 값이 재정의됩니다.

SET property_key = property_value

매개 변수

  • property_key:나열하거나 변경할 속성의 이름입니다.
  • property_value:속성을 설정할 값.

모든 설정에 대한 값을 반환하려면 property_value 없이 SET [property key]을 사용합니다.

PostgreSQL 명령

아래의 하위 섹션에는 쿼리 서비스에서 지원하는 PostgreSQL 명령이 포함되어 있습니다.

시작

BEGIN 명령 또는 BEGIN WORK 또는 BEGIN TRANSACTION 명령은 트랜잭션 블록을 시작합니다. 시작 명령 후에 입력되는 모든 문은 명시적 COMMIT 또는 ROLLBACK 명령이 제공될 때까지 단일 트랜잭션에서 실행됩니다. 이 명령은 START TRANSACTION과 같습니다.

BEGIN
BEGIN WORK
BEGIN TRANSACTION

닫기

CLOSE 명령은 열린 커서와 관련된 리소스를 비웁니다. 커서를 닫으면 후속 작업이 허용되지 않습니다. 커서가 더 이상 필요하지 않으면 닫아야 합니다.

CLOSE name
CLOSE ALL

CLOSE name을(를) 사용하는 경우 name은 닫아야 하는 열린 커서의 이름을 나타냅니다. CLOSE ALL을(를) 사용하는 경우 열려 있는 모든 커서가 닫힙니다.

할당 취소

DEALLOCATE 명령을 사용하면 이전에 준비한 SQL 문을 할당 해제할 수 있습니다. 준비된 문을 명시적으로 할당 해제하지 않으면 세션이 종료될 때 할당 취소됩니다. 준비된 문에 대한 자세한 내용은 PREPARE 명령 섹션에 있습니다.

DEALLOCATE name
DEALLOCATE ALL

DEALLOCATE name을(를) 사용하는 경우 name은 할당을 취소해야 하는 준비된 문의 이름을 나타냅니다. DEALLOCATE ALL을(를) 사용하는 경우 준비된 모든 명령문을 할당 해제합니다.

DECLARE

DECLARE 명령을 사용하면 사용자가 커서를 만들어 큰 쿼리에서 적은 수의 행을 검색하는 데 사용할 수 있습니다. 커서가 만들어지면 FETCH을 사용하여 행에서 행을 가져옵니다.

DECLARE name CURSOR FOR query

매개 변수

  • name:만들 커서의 이름입니다.
  • query:커서로 반환할 행을 제공하는 SELECT 또는 VALUES 명령.

실행

EXECUTE 명령은 이전에 준비한 문을 실행하는 데 사용됩니다. 준비된 문은 세션 기간 동안만 존재하므로 준비된 문은 현재 세션에서 이전에 실행된 PREPARE 문으로 만들어야 합니다. 준비된 문을 사용하는 방법에 대한 자세한 내용은 PREPARE 명령 섹션에 있습니다.

명령문을 만든 PREPARE 문이 일부 매개 변수를 지정한 경우 호환되는 매개 변수 집합을 EXECUTE 문으로 전달해야 합니다. 이러한 매개 변수가 전달되지 않으면 오류가 발생합니다.

EXECUTE name [ ( parameter ) ]

매개 변수

  • name:실행할 준비된 문의 이름입니다.
  • parameter:준비된 문에 대한 매개 변수의 실제 값입니다. 준비된 문을 만들 때 결정된 대로 이 매개 변수의 데이터 유형과 호환되는 값을 제공하는 표현식이어야 합니다. 준비된 문에 여러 매개 변수가 있을 경우 쉼표로 구분됩니다.

설명

EXPLAIN 명령은 제공된 문에 대한 실행 계획을 표시합니다. 실행 계획은 명령문에서 참조하는 테이블을 스캔하는 방법을 보여줍니다. 여러 테이블이 참조되면 각 입력 테이블에서 필요한 행을 함께 가져오는 데 사용되는 조인 알고리즘이 표시됩니다.

EXPLAIN option statement

여기서 option은(는) 다음 중 하나일 수 있습니다.

ANALYZE
FORMAT { TEXT | JSON }

매개 변수

  • ANALYZE:이 option 포함 ANALYZE이 있으면 실행 시간과 다른 통계가 표시됩니다.
  • FORMAT:가 option 포함된 FORMAT경우 출력 형식을 지정합니다. 이 형식은 TEXT 또는 JSON입니다. 텍스트가 아닌 출력은 텍스트 출력 형식과 동일한 정보를 포함하지만 프로그램에서 쉽게 구문 분석할 수 있습니다. 이 매개 변수의 기본값은 TEXT입니다.
  • statement:모든, SELECT, INSERT, UPDATE DELETE, VALUES, 실행 계획 EXECUTE DECLARE CREATE TABLE AS CREATE MATERIALIZED VIEW AS 을 보고 싶은.
중요

ANALYZE 옵션이 사용될 때 명령문이 실제로 실행된다는 점을 염두에 두십시오. EXPLAINSELECT이 반환하는 모든 출력을 삭제하지만 문의 다른 부작용이 평소대로 발생합니다.

다음 예제는 단일 integer 열과 10000개의 행이 있는 테이블의 단순 쿼리에 대한 계획을 보여줍니다.

EXPLAIN SELECT * FROM foo;
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on foo  (cost=0.00..155.00 rows=10000 width=4)
(1 row)

가져오기

FETCH 명령은 이전에 만든 커서를 사용하여 행을 검색합니다.

FETCH num_of_rows [ IN | FROM ] cursor_name

매개 변수

  • num_of_rows:가져올 행 수입니다.
  • cursor_name:정보를 검색하는 커서의 이름입니다.

준비

PREPARE 명령을 사용하여 준비된 문을 만들 수 있습니다. 준비된 문은 유사한 SQL 문을 템플릿 지정하는 데 사용할 수 있는 서버측 객체입니다.

준비된 문은 매개 변수를 사용할 수 있습니다. 매개 변수는 실행될 때 명령문에 대체되는 값입니다. 준비된 문을 사용할 때 매개 변수는 $1, $2 등을 사용하여 위치별로 참조됩니다.

매개 변수 데이터 유형 목록을 지정할 수도 있습니다(선택 사항). 매개 변수의 데이터 유형이 나열되지 않으면 컨텍스트에서 유형을 유추할 수 있습니다.

PREPARE name [ ( data_type [, ...] ) ] AS SELECT

매개 변수

  • name:준비된 문의 이름입니다.
  • data_type:준비된 문의 매개 변수의 데이터 유형입니다. 매개 변수의 데이터 유형이 나열되지 않으면 컨텍스트에서 유형을 유추할 수 있습니다. 여러 데이터 유형을 추가해야 하는 경우 쉼표로 구분된 목록에 추가할 수 있습니다.

ROLLBACK

ROLLBACK 명령은 현재 트랜잭션을 취소하고 트랜잭션에서 수행한 모든 업데이트를 삭제합니다.

ROLLBACK
ROLLBACK WORK

다음으로 선택

SELECT INTO 명령은 새 테이블을 만들고 쿼리를 통해 계산된 데이터로 채웁니다. 데이터는 일반 SELECT 명령이므로 클라이언트에 반환되지 않습니다. 새 테이블의 열에는 SELECT 명령의 출력 열과 연관된 이름 및 데이터 유형이 있습니다.

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    * | expression [ [ AS ] output_name ] [, ...]
    INTO [ TEMPORARY | TEMP | UNLOGGED ] [ TABLE ] new_table
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

매개 변수

표준 SELECT 쿼리 매개 변수에 대한 자세한 내용은 SELECT 쿼리 섹션에서 확인할 수 있습니다. 이 섹션에는 SELECT INTO 명령에만 적용되는 매개 변수만 나열됩니다.

  • TEMPORARY 또는 TEMP:선택적 매개 변수입니다. 지정하는 경우 만들어진 테이블이 임시 테이블이 됩니다.
  • UNLOGGED:선택적 매개 변수입니다. 지정된 경우, 다른 이름으로 생성되는 테이블은 기록되지 않은 테이블입니다. 기록되지 않은 테이블에 대한 자세한 내용은 PostgreSQL 설명서에서 확인할 수 있습니다.
  • new_table:만들 테이블의 이름입니다.

다음 쿼리는 films 테이블의 최근 항목만 포함하는 새 테이블 films_recent을 만듭니다.

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

표시

SHOW 명령은 런타임 매개 변수의 현재 설정을 표시합니다. 이러한 변수는 SET 문을 사용하여, postgresql.conf 구성 파일을 편집하거나, PGOPTIONS 환경 변수를 통해(libpq 또는 libpq 기반 응용 프로그램을 사용할 때) 또는 Postgres 서버를 시작할 때 명령줄 플래그를 통해 설정할 수 있습니다.

SHOW name
SHOW ALL

매개 변수

  • name:정보를 확인할 런타임 매개 변수의 이름입니다. 런타임 매개 변수에 사용할 수 있는 값에는 다음 값이 포함됩니다.
    • SERVER_VERSION:이 매개 변수는 서버의 버전 번호를 보여줍니다.

    • SERVER_ENCODING:이 매개 변수는 서버측 문자 집합 인코딩을 표시합니다.

    • LC_COLLATE:이 매개 변수는 데이터 정렬(텍스트 순서)에 대한 데이터베이스의 로케일 설정을 보여줍니다.

    • LC_CTYPE:이 매개 변수는 문자 분류에 대한 데이터베이스의 로케일 설정을 보여줍니다.

      IS_SUPERUSER:이 매개 변수는 현재 역할에 수퍼유저 권한이 있는지 여부를 표시합니다.

  • ALL:모든 구성 매개 변수의 값을 설명과 함께 표시합니다.

다음 쿼리는 DateStyle 매개 변수의 현재 설정을 보여줍니다.

SHOW DateStyle;
 DateStyle
-----------
 ISO, MDY
(1 row)

복사

COPY 명령은 SELECT 쿼리의 출력을 지정된 위치에 덤프합니다. 이 명령이 성공하려면 이 위치에 대한 액세스 권한이 있어야 합니다.

COPY query
    TO '%scratch_space%/folder_location'
    [  WITH FORMAT 'format_name']

매개 변수

  • query:복사할 쿼리
  • format_name:쿼리를 복사할 형식. format_nameparquet, csv 또는 json 중 하나일 수 있습니다. 기본적으로 이 값은 parquet입니다.
노트

전체 출력 경로는 adl://<ADLS_URI>/users/<USER_ID>/acp_foundation_queryService/folder_location/<QUERY_ID>입니다.

테이블 변경

ALTER TABLE 명령을 사용하면 테이블에 열을 추가할 수 있을 뿐만 아니라 기본 키 또는 외래 키 제약 조건을 추가하거나 삭제할 수 있습니다.

제한 추가 또는 삭제

다음 SQL 쿼리는 테이블에 제약 조건을 추가하거나 삭제하는 예를 보여 줍니다.

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY ( column_name ) REFERENCES referenced_table_name ( primary_column_name )

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY column_name NAMESPACE namespace

ALTER TABLE table_name DROP CONSTRAINT constraint_name PRIMARY KEY ( column_name )

ALTER TABLE table_name DROP CONSTRAINT constraint_name FOREIGN KEY ( column_name )

매개 변수

  • table_name:편집 중인 테이블의 이름입니다.
  • constraint_name:추가하거나 삭제할 구속 이름입니다.
  • column_name:제한을 추가할 열의 이름입니다.
  • referenced_table_name:외래 키가 참조하는 테이블의 이름입니다.
  • primary_column_name:외래 키가 참조하는 열의 이름입니다.
노트

테이블 스키마는 고유해야 하며 여러 테이블 간에 공유되어서는 안 됩니다. 또한 네임스페이스는 기본 키 제약 조건에 대해 필수입니다.

열 추가

다음 SQL 쿼리는 테이블에 열을 추가하는 예를 보여줍니다.

ALTER TABLE table_name ADD COLUMN column_name data_type

ALTER TABLE table_name ADD COLUMN column_name_1 data_type1, column_name_2 data_type2 

매개 변수

  • table_name:편집 중인 테이블의 이름입니다.
  • column_name:추가할 열의 이름입니다.
  • data_type:추가할 열의 데이터 유형입니다. 지원되는 데이터 유형은 다음과 같습니다.bigint, char, string, date, datetime, double, double precision, integer, smallint, tinyint, varchar

기본 키 표시

SHOW PRIMARY KEYS 명령은 지정된 데이터베이스의 모든 기본 키 제약 조건을 나열합니다.

SHOW PRIMARY KEYS
    tableName | columnName    | datatype | namespace
------------------+----------------------+----------+-----------
 table_name_1 | column_name1  | text     | "ECID"
 table_name_2 | column_name2  | text     | "AAID"

외래 키 표시

SHOW FOREIGN KEYS 명령은 지정된 데이터베이스에 대한 모든 외래 키 제약 조건을 나열합니다.

SHOW FOREIGN KEYS
    tableName   |     columnName      | datatype | referencedTableName | referencedColumnName | namespace 
------------------+---------------------+----------+---------------------+----------------------+-----------
 table_name_1   | column_name1        | text     | table_name_3        | column_name3         |  "ECID"
 table_name_2   | column_name2        | text     | table_name_4        | column_name4         |  "AAID"

이 페이지에서는

Adobe Summit Banner

A virtual event April 27-28.

Expand your skills and get inspired.

Register for free
Adobe Summit Banner

A virtual event April 27-28.

Expand your skills and get inspired.

Register for free