SnowConvert: Instruções Select do Oracle¶
Seleção¶
Nota
Algumas partes dos códigos de saída foram omitidas por motivos de clareza.
Conversão geral de Select¶
Select simples¶
Código de entrada:¶
select * from table1;
select col1 from schema1.table1;
Código de saída:¶
select * from
table1;
select col1 from
schema1.table1;
Cláusula Where¶
Código de entrada:¶
select col1 from schema1.table1 WHERE col1 = 1 and id > 0 or id < 1;
Código de saída:¶
select col1 from
schema1.table1
WHERE col1 = 1 and id > 0 or id < 1;
Cláusula Order by¶
Código de entrada:¶
select col1 from schema1.table1 order by id ASC;
Código de saída:¶
select col1 from
schema1.table1
order by id ASC;
Group by¶
Código de entrada:¶
select col1 from schema1.table1 GROUP BY id;
Código de saída:¶
select col1 from
schema1.table1
GROUP BY id;
Cláusula Model¶
A cláusula Model ainda não é compatível.
Cláusula Row Limiting¶
Código de entrada:¶
-- 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;
Código de saída:¶
-- 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;
Nota
No Oracle, FETCH
/ OFFSET WITH TIES
é ignorado quando nenhum ORDER BY
é especificado em SELECT
. Esse caso será transformado em um FETCH
/ OFFSET
com a palavra-chave ONLY no Snowflake. Observe que no Snowflake a palavra-chave ONLY
não tem efeito nos resultados e é usada apenas para facilitar a leitura.
Pivô¶
O Snowflake não é compatível com as seguintes instruções:\ - Rename columns \ - Multiple Columns
Código de entrada:¶
select * from schema1.table1
PIVOT(count(*) as count1 FOR (column1, column2) IN (row1 as rowName));
Código de saída:¶
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¶
O Snowflake não é compatível com as seguintes instruções:\ - INCLUDE / EXCLUDE NULLS
Código de entrada:¶
select * from schema1.table1
UNPIVOT INCLUDE NULLS (column1 FOR column2 IN (ANY, ANY));
Código de saída:¶
select * from
schema1.table1
!!!RESOLVE EWI!!! /*** SSC-EWI-0015 - PIVOT/UNPIVOT INCLUDE NULLS NOT SUPPORTED ***/!!!
UNPIVOT ( column1 FOR column2 IN (
ANY,
ANY));
Transformação de JOIN (+) para sintaxe ANSI¶
Essa conversão está desativada no momento e serve apenas como referência para conversões feitas com versões anteriores do SnowConvert. Para obter a conversão atual, consulte a seção acima.
O SnowConvert converte a sintaxe de junção externa especial (+) de NON-ANSI para a sintaxe de junção externa de ANSI. Esta subseção mostra alguns exemplos:
Para LEFTOUTERJOIN
Exemplo 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;
Exemplo 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;
Exemplo 3: Junção múltipla
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;
Exemplo 4: Junção com outros tipos de condicionais
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;
Exemplo 5: Junção com (+) dentro de uma função
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;
Aviso
Observe que alguns dos padrões que foram convertidos para LEFT OUTER JOIN podem recuperar as linhas em uma ordem diferente.
Para CROSSJOIN¶
Exemplo 6: Caso complexo que requer o uso de CROSS JOIN
Código de entrada:¶
-- 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;
Código de saída:¶
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;
Consultas hierárquicas¶
As consultas hierárquicas no Snowflake permitem organizar e recuperar dados em uma estrutura semelhante a uma árvore, normalmente usando a cláusula CONNECT BY
. Essa cláusula une uma tabela a ela mesma para processar dados hierárquicos na tabela.
Código de entrada:¶
SELECT employee_ID, manager_ID, title
FROM employees
START WITH manager_ID = 1
CONNECT BY manager_ID = PRIOR employee_id;
Código de saída:¶
SELECT employee_ID, manager_ID, title
FROM
employees
START WITH manager_ID = 1
CONNECT BY
manager_ID = PRIOR employee_id;
Consulta Select Flashback¶
Consulta Select Flashback¶
Descrição¶
Oracle
A cláusula de Flashback Query no Oracle recupera dados anteriores de uma tabela, visualização ou exibição materializada. No Oracle, os usos podem incluir:
Restaurar dados excluídos ou desfazer um commit incorreto, comparar os dados atuais com os dados correspondentes em um momento anterior, verificar o estado dos dados transacionais em um determinado momento e gerar ferramentas de geração de relatórios para dados passados, entre outros. (Documentação de consulta Flashback Query do Oracle).
Snowflake
O mecanismo equivalente no Snowflake para consultar dados do passado é a consulta AT | BEGIN
. Observe que o único equivalente é para as instruções AS OF
.
Além disso, o Snowflake tem uma documentação completa de «Time Travel» que permite consultar dados para clonar objetos, como tabelas, exibições e esquemas. Há limitações quanto aos dias de acesso aos dados passados ou excluídos (90 dias antes de passar para o status Fail-safe). Para obter mais informações, consulte a Documentação de Time Travel do Snowflake.
Sintaxe do Oracle
{ VERSIONS BETWEEN
{ SCN | TIMESTAMP }
{ expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}
Sintaxe do Snowflake
SELECT ...
FROM ...
{
AT( { TIMESTAMP => <timestamp> | OFFSET => <time_difference> | STATEMENT => <id> | STREAM => '<name>' } ) |
BEFORE( STATEMENT => <id> )
}
[ ... ]
Nota
Observe que a ID da consulta deve fazer referência a uma consulta executada dentro de 14 dias. Se a ID fizer referência a uma consulta com mais de 14 dias, o seguinte erro será retornado: Erro: statement <query_id> not found
. Para contornar essa limitação, use o registro de data e hora da consulta referenciada. (Snowflake AT | Antes da documentação)
Amostra de padrões da origem¶
Os dados a seguir são usados nos exemplos a seguir para gerar os resultados da consulta.
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';
Problemas conhecidos¶
1. The option when it is using SCN is not supported.¶
2. The VERSION statement is not supported in Snowflake.¶
EWIS relacionados¶
SSC-EWI-0040: Instrução não suportada.
SSC-EWI-OR0135: A cláusula Current of não é compatível com o Snowflake.
SSC-FDM-0006: A coluna de tipo de número pode não se comportar de forma semelhante no Snowflake.
SSC-FDM-OR0042: O tipo de data transformado em carimbo de data/hora tem um comportamento diferente.