SnowConvert: Oracle Select 문¶
선택¶
참고
출력 코드의 일부 부분은 명확성을 위해 생략되었습니다.
전체 Select 변환¶
단순 select¶
입력 코드:¶
select * from table1;
select col1 from schema1.table1;
출력 코드:¶
select * from
table1;
select col1 from
schema1.table1;
Where 절¶
입력 코드:¶
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;
출력 코드:¶
select col1 from
schema1.table1
WHERE col1 = 1 and id > 0 or id < 1;
Order By 절¶
입력 코드:¶
select col1 from schema1.table1 order by id ASC;
출력 코드:¶
select col1 from
schema1.table1
order by id ASC;
Group by¶
입력 코드:¶
select col1 from schema1.table1 GROUP BY id;
출력 코드:¶
select col1 from
schema1.table1
GROUP BY id;
Model 절¶
model 절은 아직 지원되지 않습니다.
Row Limiting 절¶
입력 코드:¶
-- Using ONLY
select * from TableFetch1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS with ties;
-- Using OFFSET clause
select * from TableFetch1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent rows ONLY;
select * from TableFetch1
order by col1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1
order by col1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;
-- Using WITH TIES clause
select * from TableFetch1 FETCH FIRST 2 ROWS with ties;
select * from TableFetch1 FETCH FIRST 20 percent ROWS with ties;
select * from TableFetch1 offset 2 rows FETCH NEXT 2 ROWs with ties;
select * from TableFetch1 offset 2 rows FETCH FIRST 60 percent ROWs with ties;
-- Using ORDER BY clause
select * from TableFetch1 order by col1 FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1 order by col1 FETCH FIRST 20 percent ROWS ONLY;
select * from TableFetch1 order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from TableFetch1
order by col1 offset 2 rows FETCH FIRST 60 percent ROWS ONLY;
select * from TableFetch1 FETCH FIRST ROWS ONLY;
select * from TableFetch1 offset 2 rows;
출력 코드:¶
-- Using ONLY
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) <= 2;
select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;
-- Using OFFSET clause
select * from
TableFetch1
offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
select * from
TableFetch1
QUALIFY
RANK() OVER (
order by col1) - 2 <= 2
LIMIT NULL OFFSET 2;
select * from
TableFetch1
QUALIFY
(RANK() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
-- Using WITH TIES clause
select * from
TableFetch1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
offset 2 rows FETCH NEXT 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
ORDER BY
NULL) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
-- Using ORDER BY clause
select * from
TableFetch1
order by col1
FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1) / COUNT(*) OVER () < 20 / 100;
select * from
TableFetch1
order by col1 offset 2 rows FETCH FIRST 2 ROWS ONLY;
select * from
TableFetch1
QUALIFY
(ROW_NUMBER() OVER (
order by col1) - 1 - 2) / COUNT(*) OVER () < 60 / 100
LIMIT NULL OFFSET 2;
select * from
TableFetch1
FETCH FIRST 1 ROWS ONLY;
select * from
TableFetch1
LIMIT NULL OFFSET 2;
참고
Oracle에서는SELECT 에 ORDER BY 가 지정되지 않은 경우 FETCH/OFFSET WITH TIES 가 무시됩니다. 이 경우 ONLY 키워드가 포함된 FETCH/OFFSET 으로 변환되고, Snowflake에서 ONLY 키워드는 결과에 영향을 미치지 않으며 가독성을 위해서만 사용됩니다.
피벗¶
Snowflake는 다음 문을 지원하지 않음:\ - 열 이름 바꾸기\ - 다중 열
입력 코드:¶
select * from schema1.table1
PIVOT(count(*) as count1 FOR (column1, column2) IN (row1 as rowName));
출력 코드:¶
select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
PIVOT (count(*)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT MULTIPLE COLUMN NOT SUPPORTED ***/!!!
FOR (column1, column2)
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT RENAME COLUMN NOT SUPPORTED ***/!!!
IN (row1 as rowName));
Unpivot¶
Snowflake는 다음 문을 지원하지 않음:\ - INCLUDE / EXCLUDE NULLS
입력 코드:¶
select * from schema1.table1
UNPIVOT INCLUDE NULLS (column1 FOR column2 IN (ANY, ANY));
출력 코드:¶
select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR column2 IN (
ANY,
ANY));
JOIN (+)를 ANSI 구문으로 변환¶
이 변환은 현재 비활성화되어 있으며 이전 버전의 SnowConvert 로 수행한 변환에 대한 참조 용도로만 제공됩니다. 현재 변환은 위 섹션에서 확인하십시오.
SnowConvert 는 NON-ANSI 특수 외부 조인 (+) 구문을 ANSI 외부 조인 구문으로 변환합니다. 이 하위 섹션에서는 몇 가지 예를 보여줍니다.
LEFT OUTER JOIN 으로
예 1:
Input Code:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id = e.department_id (+)
AND d.department_id >= 30;
Output Code:
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON d.department_id = e.department_id
WHERE
d.department_id >= 30;
예 2:
Input Code:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id(+) = e.department_id
AND d.department_id >= 30;
Output Code:
SELECT d.department_name,
e.employee_name
FROM
employees e
LEFT OUTER JOIN
departments d
ON d.department_id = e.department_id
WHERE
d.department_id >= 30;
예 3: 다중 조인
Input Code:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e, projects p
WHERE e.department_id(+) = d.department_id
AND p.department_id(+) = d.department_id
AND d.department_id >= 30;
Output Code:
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON e.department_id = d.department_id
LEFT OUTER JOIN
projects p
ON p.department_id = d.department_id
WHERE
d.department_id >= 30;
예 4: 다른 종류의 조건부와 조인하기
Input Code:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE d.department_id(+) = e.department_id
AND d.location(+) IN ('CHICAGO', 'BOSTON', 'NEW YORK')
AND d.department_id >= 30;
Output Code:
SELECT d.department_name,
e.employee_name
FROM
employees e
LEFT OUTER JOIN
departments d
ON d.department_id = e.department_id
AND d.location IN ('CHICAGO', 'BOSTON', 'NEW YORK')
WHERE
d.department_id >= 30;
예 5: 함수 내에서 (+)로 조인하기
Input Code:
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name
FROM departments d, employees e
WHERE SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name(+);
Output Code:
SELECT d.department_name,
e.employee_name
FROM
departments d
LEFT OUTER JOIN
employees e
ON SUBSTR(d.department_name, 1, NVL(e.department_id, 1) ) = e.employee_name;
경고
LEFT OUTER JOIN 으로 변환된 일부 패턴은 행을 다른 순서로 검색할 수 있다는 점에 유의하십시오.
CROSS JOIN 으로¶
예 6: CROSS JOIN 을 사용해야 하는 복잡한 경우
입력 코드:¶
-- Additional Params: --OuterJoinsToOnlyAnsiSyntax
SELECT d.department_name,
e.employee_name,
p.project_name,
c.course_name
FROM departments d, employees e, projects p, courses c
WHERE
e.salary (+) >= 2000 AND
d.department_id = e.department_id (+)
AND p.department_id = e.department_id(+)
AND c.course_id = e.department_id(+)
AND d.department_id >= 30;
출력 코드:¶
SELECT d.department_name,
e.employee_name,
p.project_name,
c.course_name
FROM
departments d
CROSS JOIN projects p
CROSS JOIN courses c
LEFT OUTER JOIN
employees e
ON
e.salary >= 2000
AND
d.department_id = e.department_id
AND p.department_id = e.department_id
AND c.course_id = e.department_id
WHERE
d.department_id >= 30;
계층 쿼리¶
Snowflake의 계층적 쿼리는 일반적으로 CONNECT BY 절을 사용하여 트리와 같은 구조로 데이터를 조직하고 검색할 수 있습니다. 이 절은 테이블을 테이블 자체에 조인하여 테이블의 계층적 데이터를 처리합니다.
입력 코드:¶
SELECT employee_ID, manager_ID, title
FROM employees
START WITH manager_ID = 1
CONNECT BY manager_ID = PRIOR employee_id;
출력 코드:¶
SELECT employee_ID, manager_ID, title
FROM
employees
START WITH manager_ID = 1
CONNECT BY
manager_ID = PRIOR employee_id;
플래시백 쿼리 선택¶
플래시백 쿼리 선택¶
설명¶
Oracle
Oracle에서 플래시백 쿼리 절을 사용하면 테이블, 뷰 또는 구체화된 뷰에서 과거 데이터를 검색할 수 있습니다. Oracle에서는 다음과 같은 용도로 사용할 수 있습니다.
삭제된 데이터 복원 또는 잘못된 커밋 실행 취소, 현재 데이터와 이전 시점의 해당 데이터 비교, 특정 시점의 트랜잭션 데이터 상태 확인, 과거 데이터에 대한 보고서 생성 도구 등을 사용할 수 있습니다. (Oracle 플래시백 쿼리 설명서).
Snowflake
과거 데이터를 쿼리하는 Snowflake의 동일한 메커니즘은 AT | BEGIN 쿼리입니다. 유일한 동등한 문은 AS OF 문입니다.
또한, Snowflake에는 데이터를 쿼리하여 테이블, 뷰, 스키마와 같은 오브젝트를 복제할 수 있는 완전한 “Time Travel” 설명서가 있습니다. 과거 또는 삭제된 데이터에 액세스할 수 있는 일수에는 제한이 있습니다(Fail-safe 상태로 넘어가기 전 90일). 자세한 내용은 Snowflake Time Travel 설명서 를 참조하십시오.
Oracle 구문
{ VERSIONS BETWEEN
{ SCN | TIMESTAMP }
{ expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}
Snowflake 구문
SELECT ...
FROM ...
{
AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
BEFORE( STATEMENT => <id> )
}
[ ... ]
참고
쿼리 ID 는 14일 이내에 실행된 쿼리를 참조해야 합니다. 쿼리 ID 가 14일이 지난 쿼리를 참조하는 경우 Error: statement <query_id> not found 오류가 반환됩니다. 이 제한을 해결하려면 참조된 쿼리에 타임스탬프를 사용하십시오. (Snowflake AT | Before 설명서)
샘플 소스 패턴¶
다음 예제에서는 쿼리 출력을 생성하는 데 다음 데이터가 사용됩니다.
CREATE TABLE Employee (
EmployeeID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
EmailAddress VARCHAR2(100),
HireDate DATE,
SalaryAmount NUMBER(10, 2)
);
INSERT INTO Employee VALUES (1, 'Bob', 'SampleNameA', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (2, 'Bob', 'SampleNameB', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (3, 'Bob', 'SampleNameC', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (4, 'Bob', 'SampleNameD', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee VALUES (5, 'Bob', 'SampleNameE', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
CREATE OR REPLACE TABLE Employee (
EmployeeID NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/ PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
EmailAddress VARCHAR(100),
HireDate TIMESTAMP /*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,
SalaryAmount NUMBER(10, 2) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'
;
INSERT INTO Employee
VALUES (1, 'Bob', 'SampleNameA', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee
VALUES (2, 'Bob', 'SampleNameB', 'sample@example.com', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee
VALUES (3, 'Bob', 'SampleNameC', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee
VALUES (4, 'Bob', 'SampleNameD', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
INSERT INTO Employee
VALUES (5, 'Bob', 'SampleNameE', 'sample@example.com', TO_DATE('2022-03-10', 'YYYY-MM-DD'), 11111.00);
1. AS OF with TIMESTAMP case¶
Oracle
SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Snowflake
SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-OR0135 - DATA RETENTION PERIOD MAY PRODUCE NO RESULTS ***/!!!
AT (TIMESTAMP =>
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS'))
WHERE last_name = 'SampleName';
2. AS OF with SCN case¶
Oracle
SELECT * FROM employees
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Snowflake
SELECT * FROM
employees
!!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
AS OF SCN
TO_TIMESTAMP('2023-09-27 07:00:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'SampleName';
Known Issues¶
1. The option when it is using SCN is not supported.¶
2. The VERSION statement is not supported in Snowflake.¶
관련 EWIS¶
SSC-EWI-0040: 문은 지원되지 않습니다.
SSC-EWI-OR0135: 절의 Current는 Snowflake에서 지원되지 않습니다.
SSC-FDM-0006: 숫자 유형 열은 Snowflake에서 유사하게 작동하지 않을 수 있습니다.
SSC-FDM-OR0042: 타임스탬프로 변환된 날짜 유형은 동작이 다릅니다.