SQL Proficiente

Transcript

1. SQL Proficiente SQL Proficiente Recursos de manipula¸ c˜ ao de dados Leonardo Cezar DATAPREV - PostgreSQL Brasil 31 de maio de 2010 Leonardo Cezar SQL Proficiente
2. SQL Proficiente Introdu¸ c˜ ao Conte´ udo 1 Introdu¸ c˜ ao Objetivo 2 Padr˜ ao SQL Defini¸ c˜ ao Organiza¸ c˜ ao do SQL:2008 Conformidade 3 Recursos do PostgreSQL Gerenciamento de Dados Externos Common Table Expression (CTEs) Window Functions Full Text Search Mais recursos Leonardo Cezar SQL Proficiente
3. SQL Proficiente Introdu¸ c˜ ao Objetivo Objetivo • Conhecer alguns recursos da linguagem para manipula¸ c˜ ao de dados e otimizar o acesso a esses dados atrav´ es do padr˜ ao ISO/IEC 9075 e das extens˜ oes do PostgreSQL Leonardo Cezar SQL Proficiente
4. SQL Proficiente Introdu¸ c˜ ao Objetivo Desafios • Reduzir a carga de processamento na aplica¸ c˜ ao • Diminuir o tr´ afego de rede • Migrar algoritmos complexos para o servidor de banco de dados Leonardo Cezar SQL Proficiente
5. SQL Proficiente Padr˜ ao SQL Conte´ udo 1 Introdu¸ c˜ ao Objetivo 2 Padr˜ ao SQL Defini¸ c˜ ao Organiza¸ c˜ ao do SQL:2008 Conformidade 3 Recursos do PostgreSQL Gerenciamento de Dados Externos Common Table Expression (CTEs) Window Functions Full Text Search Mais recursos Leonardo Cezar SQL Proficiente
6. SQL Proficiente Padr˜ ao SQL Defini¸ c˜ ao Estrutura • Criado nos anos 80 pelo ISO/IEC • Comitˆ e gestor JTC-1 (information technology) • Especifica uma base para os dialetos SQL • Define features opcionais e obrigat´ orios • Define packages de funcionalidades • Dividido em 9 subse¸ c˜ oes Leonardo Cezar SQL Proficiente
7. SQL Proficiente Padr˜ ao SQL Organiza¸ c˜ ao do SQL:2008 Partes do ISO/IEC SQL:2008 • 9075-1: SQL/Framework (n´ ucleo) • Implementa o conceito sistem´ atico da linguagem e processadores de resultado • 9075-2: SQL/Foundation (n´ ucleo) • Implementa a especifica¸ c˜ ao de manipuladores de dados • 9075-3: SQL/CLI (externo - ODBC) • Define camada de acesso externa (exceto java) • 9075-4: SQL/PSM (externo - PL/pgPSM) • Define a semˆ antica de instru¸ c˜ oes de linguagens procedurais Leonardo Cezar SQL Proficiente
8. SQL Proficiente Padr˜ ao SQL Organiza¸ c˜ ao do SQL:2008 Partes do ISO/IEC SQL:2008 • 9075-9: SQL/MED (n´ ucleo) • Define extens˜ oes para suportar gerenciamento de fonte de dados externa atrav´ es de datalink • 9075-11: SQL/Schemata (n´ ucleo) • Implementa a cria¸ c˜ ao e acesso aos dados • 9075-13: SQL/JRT (externo - PL/Java) • Fun¸ c˜ oes e tipos utilizando Java • 9075-14: SQL/XML (n´ ucleo) • Suporte XML Leonardo Cezar SQL Proficiente
9. SQL Proficiente Padr˜ ao SQL Organiza¸ c˜ ao do SQL:2008 E quanto as outras sequˆ encias do ISO/IEC 9075? • 9075-5: SQL/Bindings (n´ ucleo) • Migrado para 9075-2 em 2003 • 9075-6: SQL/Transaction (XA) • Cancelado em 1999 • 9075-7: SQL/Temporal • Cancelado • 9075-10: SQL/OLB • Especifica a s´ ıntaxe para incorporar SQL na linguagem Java atrav´ es de amarra¸ c˜ oes. • 9075-12: SQL/Replication (n´ ucleo) • Cancelado Leonardo Cezar SQL Proficiente
10. SQL Proficiente Padr˜ ao SQL Conformidade Crit´ erios de conformidade • Conformidade m´ ınima (Core SQL) • SQL/Foundations e SQL/Schemata • Conformidade em partes • Implementar todas as features de uma parte (ex.: ISO/IEC 9075-9 SQL/MED) • Conformidade com features • Implementar a funcionalidade e suas subfuncionalidades em sua totalidade • Conformidade com Packages • Agrupamento de features opcionais • Implementar todas as features da Package Leonardo Cezar SQL Proficiente
11. SQL Proficiente Padr˜ ao SQL Conformidade Consultando as features implementadas A vis˜ ao sql features postgres=# \d information_schema.sql_features Table "information_schema.sql_features" Column | Type | Modifiers ------------------+-----------------------------------+----------- feature_id | information_schema.character_data | feature_name | information_schema.character_data | sub_feature_id | information_schema.character_data | sub_feature_name | information_schema.character_data | is_supported | information_schema.yes_or_no | is_verified_by | information_schema.character_data | comments | information_schema.character_data | Leonardo Cezar SQL Proficiente
12. SQL Proficiente Padr˜ ao SQL Conformidade Consultando as Packages implementadas A vis˜ ao sql packages postgres=# \d information_schema.sql_packages Table "information_schema.sql_packages" Column | Type | Modifiers ----------------+-----------------------------------+----------- feature_id | information_schema.character_data | feature_name | information_schema.character_data | is_supported | information_schema.yes_or_no | is_verified_by | information_schema.character_data | comments | information_schema.character_data | Leonardo Cezar SQL Proficiente
13. SQL Proficiente Recursos do PostgreSQL Conte´ udo 1 Introdu¸ c˜ ao Objetivo 2 Padr˜ ao SQL Defini¸ c˜ ao Organiza¸ c˜ ao do SQL:2008 Conformidade 3 Recursos do PostgreSQL Gerenciamento de Dados Externos Common Table Expression (CTEs) Window Functions Full Text Search Mais recursos Leonardo Cezar SQL Proficiente
14. SQL Proficiente Recursos do PostgreSQL Gerenciamento de Dados Externos Dados Externos • ISO/IEC 9075-9:2003 SQL/MED • M´ etodos de acess a dados armazenados em fontes externas • SGBDs • MS SQLServer • MySQL • Oracle • Arquivos • XML • YAML • CSV • Outras instˆ ancias PostgreSQL • Unificar acesso externo (DBI, dblink, PLProxy) • Maior controle na administra¸ c˜ ao de links externos Leonardo Cezar SQL Proficiente
15. SQL Proficiente Recursos do PostgreSQL Gerenciamento de Dados Externos Dados Externos MED postgres=# CREATE FOREIGN DATA WRAPPER pgday VALIDATOR postgresql_fdw_validator; postgres=# CREATE SERVER srvpgday FOREIGN DATA WRAPPER pgday OPTIONS (host ’srv-pgday’, dbname ’pgday’, port ’5432’); postgres=# CREATE USER MAPPING FOR lhcezar SERVER srvpgday OPTIONS (user ’lhcezar’, password ’lhcezar’); postgres=$ SELECT dblink_connect(’pgday_c’, ’srvpgday’); postgres=$ SELECT * from dblink(’pgday_c’, ’SELECT * FROM pgday’) AS (id int, palestra varchar); id | palestra ----+----------------- 1 | SQL Proficiente Leonardo Cezar SQL Proficiente
16. SQL Proficiente Recursos do PostgreSQL Common Table Expression (CTEs) WITH RECURSIVE • ISO/IEC 9075-2:2003 • Permite reaproveitar express˜ oes pr´ eviamente definidas em uma mesma instru¸ c˜ ao • Semelhante a utiliza¸ c˜ ao de uma vis˜ ao • Permite gerar chamadas recursivas • Utilizado para percorrer uma lista finita de valores em uma express˜ ao Leonardo Cezar SQL Proficiente
17. SQL Proficiente Recursos do PostgreSQL Common Table Expression (CTEs) WITH RECURSIVE - s´ erie de Fibonacci WITH RECURSIVE t ( x , y ) AS ( VALUES(0 ,1) UNION ALL SELECT GREATEST( x , y ) , x + y AS a FROM t WHERE y < 5 ) SELECT a AS f i b o n a c c i FROM t ; +-----------+ | fibonacci | +-----------+ | 0 | | 1 | | 1 | | 2 | | 3 | +-----------+ Leonardo Cezar SQL Proficiente
18. SQL Proficiente Recursos do PostgreSQL Common Table Expression (CTEs) WITH RECURSIVE - sequˆ encias vazias postgres=# SELECT id, descricao FROM produtos; +----+-----------+ | id | descricao | +----+-----------+ | 1 | Televis~ ao | | 3 | Blue Ray | | 4 | DVD | | 10 | Bicileta | +----+-----------+ (4 rows) Leonardo Cezar SQL Proficiente
19. SQL Proficiente Recursos do PostgreSQL Common Table Expression (CTEs) WITH RECURSIVE - sequˆ encias vazias WITH RECURSIVE cte ( n ) AS ( SELECT p r o d u t o s p a i . i d + 1 FROM produtos p r o d u t o s p a i WHERE NOT EXISTS (SELECT NULL FROM produtos p r o d u t o s f i l h o WHERE p r o d u t o s f i l h o . i d = p r o d u t o s p a i . i d +1) AND p r o d u t o s p a i . i d < (SELECT MAX( i d ) FROM produtos ) UNION ALL SELECT cte . n +1 FROM cte WHERE NOT EXISTS (SELECT NULL FROM produtos r WHERE r . i d = cte . n + 1) ) SELECT n AS v a z i a s FROM cte ; Leonardo Cezar SQL Proficiente
20. SQL Proficiente Recursos do PostgreSQL Common Table Expression (CTEs) WITH RECURSIVE - sequˆ encias vazias postgres=# \i ~/Apresenta¸ c~ oes/SQLProficiente/cte.sql +--------+ | vazias | +--------+ | 2 | | 5 | | 6 | | 7 | | 8 | | 9 | +--------+ (6 rows) Leonardo Cezar SQL Proficiente
21. SQL Proficiente Recursos do PostgreSQL Window Functions Window functions • ISO/IEC 9075-2 • Efetivar c´ alculos para determinado agrupamento de linhas • N˜ ao limita a sa´ ıda das tuplas com opera¸ c˜ oes GROUP BY • Utiliza a cl´ ausula OVER e PARTITION BY (SQL ANSI) Leonardo Cezar SQL Proficiente
22. SQL Proficiente Recursos do PostgreSQL Window Functions OVER — PARTITION BY SELECT nspname AS esquema , relname AS tabela , r e l t u p l e s AS l i n h a s , −− windows frame c r i a d o para cada grupo de esquemas SUM( r e l t u p l e s ) OVER (PARTITION BY nspname ) AS t o t a l l i n h a s FROM p g c l a s s r JOIN pg namespace n ON r . relnamespace = n . oid WHERE r e l k i n d = ’ r ’ AND nspname NOT IN( ’ pg catalog ’ , ’ information schema ’ ) ; Leonardo Cezar SQL Proficiente
23. SQL Proficiente Recursos do PostgreSQL Window Functions Total de registros por esquema +---------+------------------+--------+--------------+ | esquema | tabela | linhas | total_linhas | +---------+------------------+--------+--------------+ | ator | address | 603 | 2111 | | ator | city | 600 | 2111 | | ator | country | 109 | 2111 | | ator | customer | 599 | 2111 | | ator | actor | 200 | 2111 | | filme | payment | 0 | 4587 | | filme | inventory | 4581 | 4587 | | filme | language | 6 | 4587 | | public | rental | 16044 | 39575 | | public | film_category | 1000 | 39575 | | public | film | 1000 | 39575 | | public | store | 2 | 39575 | Leonardo Cezar SQL Proficiente
24. SQL Proficiente Recursos do PostgreSQL Full Text Search FTS • Busca atrav´ es de similaridade • Classificar resultados pela relevˆ ancia • Consulta baseada em dicion´ arios • Defini¸ c˜ ao de stop words Leonardo Cezar SQL Proficiente
25. SQL Proficiente Recursos do PostgreSQL Full Text Search FTS - Exemplo de consulta SELECT t i t l e , t s h e a d l i n e ( d e s c r i p t i o n , t o t s q u e r y ( ’ f a t e&i n d i a ’ )) FROM f i l m WHERE f u l l t e x t @@ t o t s q u e r y ( ’ f a t e&i n d i a ’ ) ; +--------------------+--------------------------------------------------------- | title | ts_headline +--------------------+--------------------------------------------------------- | ANTITRUST TOMATOES | <b>Fateful</b> Yarn of a Womanizer in Ancient <b>India</ | EASY GLADIATOR | <b>Fateful</b> Story of a Monkey Ancient <b>India</b> | SLEUTH ORIENT | <b>Fateful</b> Character Study of a <b>India</b> +--------------------+--------------------------------------------------------- Leonardo Cezar SQL Proficiente
26. SQL Proficiente Recursos do PostgreSQL Mais recursos Outras ´ areas a se explorar • Particionamento • XML • Procedimentos Armazenados (CREATE FUNCTION) • Fun¸ c˜ oes anˆ onimas • Dom´ ınios (CREATE DOMAIN) • Tipo (CREATE TYPE [...]) • Agrega¸ c˜ ao (CREATE AGGREGATE [...]) • Replica¸ c˜ ao (Streaming/Hot Standby) • Contrib Leonardo Cezar SQL Proficiente
27. SQL Proficiente Recursos do PostgreSQL Mais recursos • D´ uvidas? • Sugest˜ oes? • Cr´ ıticas? Leonardo Cezar SQL Proficiente