Baixe o app para aproveitar ainda mais
Prévia do material em texto
Tecnologias para o Ecossistema de Big Data Prof. Henrique Batista da Silva Hadoop MapReduce • Hadoop MapReduce: principal componente do Apache Hadoop. Paradigma de programação que permite a execução de programas de forma distribuída em cluster. Hadoop MapReduce • MapReduce é um estilo de computação que pode ser implementado em vários sistema, como por exemplo o Hadoop. • MapReduce é usado para gerenciar computação de larga escala, pois é uma solução tolerante a falhas de hardware. • Duas funções devem ser escritas: • Map: consome os dados de entrada, os processa e emite tuplas (key, value) • Reduce: obtém tuplas geradas pelo Map e agrupa os resultado. • O sistema se encarregará da execução paralela e tolerância a falhas. Hadoop MapReduce Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 • Sequencialmente leia os dados • Fase Map • Extrair a informação realmente importante (por exemplo, palavra e o número de ocorrência) • Agrupar pela chave (ordenar pela chave) • Fase Reduce • Agregar, sumarizar, filtrar os dados. • Escreva o resultado. Hadoop MapReduce Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 The crew of the space shuttle Endeavor recently returned to Earth as ambassadors, harbingers of a new era of space exploration. Scientists at NASA are saying that the recent assembly of the Dextre bot is the first step in a long-term space-based man/mache partnership. '"The work we're doing now -- the robotics we're doing - - is what we're going to need …………………….. Big document (The, 1) (crew, 1) (of, 1) (the, 1) (space, 1) (shuttle, 1) (Endeavor, 1) (recently, 1) …. (crew, 1) (crew, 1) (space, 1) (the, 1) (the, 1) (the, 1) (shuttle, 1) (recently, 1) … (crew, 2) (space, 1) (the, 3) (shuttle, 1) (recently, 1) … MAP: Leia a entrada e produza um conjunto de pares <chave, valor> Group by key: Colete todos os pares com a mesma chave Reduce: Coletar todos os valores pertencentes às chaves e returne (key, value) Fornecido pelo programador Fornecido pelo programador (key, value)(key, value) Hadoop MapReduce Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 • Um exemplo de MapReduce: considere a existência de cinco arquivos, cada um com duas colunas (key, value), representando a cidade e a temperatura: Hadoop MapReduce Toronto, 20 Whitby, 25 New York, 22 Rome, 32 Toronto, 4 Rome, 33 New York, 18 Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 • Precisamos encontrar a temperatura máxima para cada cidade. Com MapReduce, os cinco arquivos podem ser divididos em 5 tarefas. Cada mapper executa em um dos 5 arquivos, retornando a maior temperatura de cada arquivo. Hadoop MapReduce (Toronto, 20) (Whitby, 25) (New York, 22) (Rome, 33) Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 • Os outros quatro mapper (dos outros quatro arquivos), produzem os seguintes resultados: Hadoop MapReduce (Toronto, 18) (Whitby, 27) (New York, 32) (Rome, 37) (Toronto, 32) (Whitby, 20) (New York, 33) (Rome, 38) (Toronto, 22) (Whitby, 19) (New York, 20) (Rome, 31) (Toronto, 31) (Whitby, 22) (New York, 19) (Rome, 30) Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 • Todos estes cinco resultados podem ser combinados da tarefa de reduce, que combina os resultados de cada chave de entrada. Hadoop MapReduce (Toronto, 32) (Whitby, 27) (New York, 33) (Rome, 38) Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 Hadoop MapReduce Client Node Client Node MapReduce application master Map Task / Reduce Task Map Task / Reduce Task Map Task / Reduce Task HDFS Fonte: Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Lecture Notes for Stanford CS345A Web Mining (Vol. 67 Contador de palavras usando MapReduce • Nesta prática vamos implementar um aplicação para MapReduce. • Vamos implementar um contador de palavras usando MapReduce utilizando o Google Colab. MapReduce """The classic MapReduce job: count the frequency of words. """ from mrjob.job import MRJob import re WORD_RE = re.compile(r"[\w']+") class MRWordFreqCount(MRJob): def mapper(self, _, line): for word in WORD_RE.findall(line): yield (word.lower(), 1) def combiner(self, word, counts): yield (word, sum(counts)) def reducer(self, word, counts): yield (word, sum(counts)) if __name__ == '__main__': MRWordFreqCount.run() Contabilizando avaliação de filmes usando MapReduce • Nesta prática vamos implementar um aplicação para MapReduce. • Vamos carregar para dentro do HDFS um dataset de rating de filmes e contabilizar a quantidade de avaliações de 1 a 5 estrelas. MapReduce MapReduce User ID | Movie ID | Rating 30 25 3 20 36 3 30 45 1 40 84 2 40 43 1 3, 1 3, 1 1, 1 2, 1 1, 1 1 – 1, 1 2 – 1 3 – 1, 1 1, 2 2, 1 3, 2 Map Shuffle e sort Reduce • Download do arquivo de dados: MapReduce wget http://files.grouplens.org/datasets/movielens/ml-100k/u.data http://files.grouplens.org/datasets/movielens/ml-100k/u.data from mrjob.job import MRJob from mrjob.step import MRStep class RatingsBreakdown(MRJob): def steps(self): return [ MRStep(mapper=self.mapper_get_ratings, reducer=self.reducer_count_ratings) ] def mapper_get_ratings(self, _, line): (userID, movieID, rating, timestamp) = line.split('\t') yield rating, 1 def reducer_count_ratings(self, key, values): yield key, sum(values) if __name__ == '__main__': RatingsBreakdown.run() Exercício Resolvido I - MapReduce • Agora o objetivo é contabilizar quantas ocorrências de cada filme no banco de dados (popularidade de cada filme). Exercícios • Use o arquivo da prática anterior e altere o campo “rating” para “movieID” e execute a aplicação • Para melhorar o resultado, faça (veja próximo slide): Exercícios from mrjob.job import MRJob from mrjob.step import MRStep class RatingsBreakdown(MRJob): def steps(self): return [ MRStep(mapper=self.mapper_get_ratings, reducer=self.reducer_count_ratings), MRStep(reducer=self.reducer_sorted_output) ] def mapper_get_ratings(self, _, line): (userID, movieID, rating, timestamp) = line.split('\t') yield movieID, 1 def reducer_count_ratings(self, key, values): yield str(sum(values)).zfill(5), key def reducer_sorted_output(self, count, movies): for movie in movies: yield movie, count if __name__ == '__main__': RatingsBreakdown.run() Transforme resultado em uma string de 5 caracteres Apache Spark • Spark é uma plataforma de computação distribuída in-Memory • Desenvolvido para tarefas como ETL, Machine Learning e carga de dados no Hadoop (SQL) Introdução ao Apache Spark • Spark se caracteriza por ser um framework para processamento de grande dados (Big Data) com foco em velocidade e oferece API para processamento e análise de dados. Introdução ao Apache Spark • APIs sobre o Spark: Spark Core e APIs Introdução ao Apache Spark Spark Streaming: processamento de fluxos em tempo real Spark GraphX: processamento sobre grafos Spark SQL: processamento de consulta e carga de dados Spark MLlib: biblioteca para machine learning (clustering, redução de dimensionalidade, etc) • Spark é uma abstração para o uso de ferramentas de Data Science. • Ex.: Spark possibilidade aos algoritmos de machine learning o processamento de dataset em memória de forma distribuída, porém abstraindo toda complexidade para o programador. Introdução ao Apache Spark • Spark estende o modelo MapReduce (Hadoop) para processamento de grande volumes de dados. • Seudesempenho pode chegar a ser 100 vezes maior que o Hadoop (em atividades de regressão logística, por exemplo) Introdução ao Apache Spark • Spark permite programação de aplicações em três linguagens: Python, Java e Scala • Há também suporte para SQL e R Introdução ao Apache Spark • Python tem a vantagem por ser menos verboso e mais “fácil” de aprender • Scala é mais rápido do que Python e normalmente uma escolha padrão para Spark Introdução ao Apache Spark Arquitetura do Spark • Arquitetura do Spark: Introdução ao Apache Spark Fonte: https://spark.apache.org/docs/latest/cluster-overview.html Driver Program: aplicação principal que executa a “main() function” (veremos sobre o SparkContext em breve no código fonte) • Arquitetura do Spark: Introdução ao Apache Spark Cluster manager: serviço externo para gerenciar recursos em cluster. Worker node: qualquer nó que possa executar a aplicação em cluster. Executor: um processo criado por uma aplicação em um nó do cluster. (executa a aplicação mantendo os dados em memória) Fonte: https://spark.apache.org/docs/latest/cluster-overview.html • Arquitetura do Spark: Introdução ao Apache Spark Task: unidade (tarefa) que será enviada para um “Executor” Uma task inicia no (pelo) Driver Program e é enviada ao Worker Node. Fonte: https://spark.apache.org/docs/latest/cluster-overview.html • Resilient Distributed Datasets (RDD): toda aplicação Spark consiste de um “main program” que executa várias operações paralelas no cluster. • Assim, o RDD é a principal abstração fornecida pelo Spark, ou seja, é uma coleção de objetos distribuídos (estrutura de dados) nos nós do cluster e que são executados em paralelo. Introdução ao Apache Spark Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • RDD pode conter qualquer tipo de dados do Python, Scala ou Java, bem como objetos de classes criadas pelos usuários. Introdução ao Apache Spark Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Exemplo em Python: Introdução ao Apache Spark conf = SparkConf().setMaster("local").setAppName("FileName") sc = SparkContext(conf = conf) input = sc.textFile("file.txt") Cria um objeto RDD a partir de um SparkContext Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Outra abstração importante do Spark é o compartilhamento de variáveis em operações de paralelismo. • Quando Spark executa uma função em parelelo (tarefas em diferentes nós), uma cópia de cada variável usada na função é enviada aos nós. Introdução ao Apache Spark Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Spark suporte múltiplos formatos de dados: Json, arquivos texto, csv e tabelas de sistema Relacional • Spark também possui integração com Hadoop sendo um substituto para as funções de MapReduce do Hadoop. Introdução ao Apache Spark Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • batch vs real-time processing (processamento em tempo real) • Hadoop é baseado no conceito de processamento em batch (lote). Hadoop processo blocos de dados (usando MapReduce) que são armazenados ao longo do tempo (2005). Apache Spark vs Hadoop Fonte: https://www.edureka.co/blog/spark-tutorial/ • batch vs real-time processing (processamento em tempo real) • Spark pode processar dados em tempo real e poder até 100 vezes mais rápido (em algumas aplicações) para processamento em lote. Apache Spark vs Hadoop Fonte: https://www.edureka.co/blog/spark-tutorial/ • Para instalação do Spark o ambiente Hadoop não é necessário, apesar que de é possível usar o Spark com o Hadoop: • Instalações: Java, Scala e Spark Instalando Apache Spark Fonte: http://www.tutorialspoint.com/apache_spark/apache_spark_installation.htm http://www.tutorialspoint.com/apache_spark/apache_spark_installation.htm http://www.tutorialspoint.com/apache_spark/apache_spark_installation.htm Programação com RDD • Spark oferece suporte ao Python bem como Java e Scala. • Nosso exemplos serão apresentados na linguagem Python, porém os exemplos com outras linguagens pode ser acessados neste link: Introdução ao RDD em Python Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Ao instalar o spark, será possível executar programas em Python com o “PySpark” (não é necessária a instalação via pip). • Abra a máquina virtual fornecida para esta aula e execute “pyspark” no terminal (antes leia as instruções na pasta do usuário). Veja que será possível introduzir comandos em Python via shell. Digite “exit()” para sair. Introdução ao RDD em Python Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Para executar um script em Python é necessário executar o comando “spark-submit programa.py” no diretório em que o script está localizado. • O comando “spark-submit” irá carregar a bibliotecas do Spark e permitir que a aplicação seja submetida ao cluster. Introdução ao RDD em Python Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Dentro do script será necessário importar alguma biblioteca do Spark, como por exemplo: Introdução ao RDD em Python from pyspark import SparkContext, SparkConf Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • A primeira coisa que um programa deve fazer é criar um objeto SparkContext. Este objeto permite ao programa Spark acessar o cluster. Inicializando Spark conf = SparkConf().setAppName(appName).setMaster(master) sc = SparkContext(conf=conf) appName: nome da sua aplicação master: é a URL do cluster Spark (“local” para executar na máquina local) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Conforme vimos anteriormente, o RDD é uma coleção de objetos tolerantes a falha que podem ser executados em paralelo. • Há duas formas de criar RDDs: paralelizando uma coleção existente em seu programa (driver program) ou referenciar um dataset em um sistema de armazenamento como o HDFS, ou algum arquivo de entrada. Resilient Distributed Datasets (RDDs) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Para paralelizar um coleção, basta chamar a funação “sc.paralelize()”. Resilient Distributed Datasets (RDDs) data = [1, 2, 3, 4, 5] distData = sc.parallelize(data) Os elementos da coleção “data” serão copiados para um dataset distribuídos e poderão assim ser processados em paralelo Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Vamos a um exemplo: faça um programa no Spark para somar os números de um conjunto de dados. • Dica: expressões lambda Resilient Distributed Datasets (RDDs) data.reduce(lambda a, b: a + b) Uma expressão lambda como essa em Python funciona como uma função que recebe duas variáveis como argumento (“a” e “b”) e retorna a soma da duas (“a + b”) Agrega as funções do RDD (veremos mais em breve) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html Resilient Distributed Datasets (RDDs) from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local[*]") sc = SparkContext(conf=conf) data = [1, 2, 3, 4, 5] distData = sc.parallelize(data) result = distData.reduce(lambda a, b: a + b) print(result) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Spark pode criar um arquivo distribuído de qualquer fonte de dados suportada pelo Hadoop (HDFS, Cassandra, Hbase, arquivo texto, etc). • Arquivo de texto RDD pode ser criado com a função “sc.textFile()” Acesso a dados externos distFile = sc.textFile("data.txt") Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Podemos usar operações sobre coleções de dados sobre o RDD após carregar um arquivo de texto externo Acesso a dados externos distFile = sc.textFile("data.txt") distFile.map(lambda s: len(s)).reduce(lambda a, b: a + b). Somao tamanho de todas as linha do arquivo (veremos mais sobre as operações como map e reduce) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Exemplo: crie um arquivo data.txt e faça um programa que some o tamanho de todas as linhas em Spark Acesso a dados externos Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html Acesso a dados externos from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local[*]") sc = SparkContext(conf=conf) distFile = sc.textFile("wordcount.txt") result = distFile.map(lambda s: len(s)).reduce(lambda a, b: a + b) print(result) Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html Operações RDD • RDD suportam dois tipos de operações: • (1) Transformações: criam um novo conjunto de dados a partir de um conjunto existente; e (2) ações: executam alguma ação que retorna valor para o “driver program” • Exemplo (transformação): map é uma transformação que passar cada elemento do conjunto para uma função e retorna um novo RDD com o resultado. Operações RDD Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Exemplo map: Operações RDD distFile = sc.textFile("data.txt") distFile.map(lambda s: len(s)).reduce(lambda a, b: a + b). Cada linha “s” do arquivo “data.txt” é passado para a função (em forma de expressão lambda) e um novo valor é retornado (o tamanho da linha). Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Exemplo (ação): reduce agrega todos os elementos do RDD usando alguma função e retorna o resultado final para o seu programa (driver program) Operações RDD Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html • Exemplo reduce: Operações RDD distFile = sc.textFile("data.txt") distFile.map(lambda s: len(s)).reduce(lambda a, b: a + b). Recebe o valor de cada linha (de duas em duas de forma agregada) e retorna a soma destas duas linhas Mais sobre transformações e ações, veja: https://spark.apache.org/docs/latest/rdd-programming-guide.html from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Os dados não são carregados imediatamente do arquivo, distFile é apenas um ponteiro para o arquivo. Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Aqui, quando os dados são transformados, a operação não é executada neste momento. Vamos entender o motivo. Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Spark usa o conceito de laziness. Ou seja, uma operação sobre dados é executada apenas quando seu resultado é requisitado pelo driver program. Uma DAG (Directed Acyclic Graph) é criada para manter o controle da ordem das operações que devem ser executados posteriormente. Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html DAG (Directed Acyclic Graph) Figura: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Quando a ação reduce é chamada, Spark divide a operação em tarefas que executam em nós (máquinas) separadas e cada máquina executa ambas as operações (map e reduce) localmente, retornando sua resposta ao driver program. Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Observe que apenas neste ponto, a operação map é executada e não anteriormente (laziness). Este é um dos motivos da alta performance do Spark Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html from pyspark import SparkContext, SparkConf conf = SparkConf().setAppName("soma-data").setMaster("local") sc = SparkContext(conf=conf) distFile = sc.textFile("data.txt") lineLengths = distFile.map(lambda s: len(s)) result = lineLengths.reduce(lambda a, b: a + b) print(result) Acesso a dados externos (modificação) Se for de interesse usar lineLengths novamente, é necessário persistir os dados em memória lineLengths.persist() Fonte: https://spark.apache.org/docs/latest/rdd-programming-guide.html Exercício resolvido I - Spark • Use o arquivo de rating de filmes para contar quantas avaliações de cada estrela. Exercícios Exercícios from pyspark import SparkConf, SparkContext import collections conf = SparkConf().setMaster("local").setAppName("rating") sc = SparkContext(conf = conf) lines = sc.textFile("u.data") ratings = lines.map(lambda x: x.split()[2]) result = ratings.countByValue() sortedResults = collections.OrderedDict(sorted(result.items())) for key, value in sortedResults.items(): print("%s %i" % (key, value)) Tecnologias para o Ecossistema de Big Data Prof. Henrique Batista da Silva Python Dask • No contexto de big data, sabemos que grandes dataset (terabytes, e em alguns casos petabytes) são conjuntos de dados que não podem caber na RAM nem no armazenamento persistente de um único computador. Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Pandas, NumPy e scikit-learn não são adequados para conjuntos de dados desse tamanho, porque não foram criados inerentemente para operar em conjuntos de dados distribuídos. Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • O Dask foi lançado no final de 2014 por Matthew Rocklin com o objetivo de trazer escalabilidade nativa para o Python e superar suas restrições de máquina única. • Com o tempo, o projeto se transformou em uma das melhores estruturas de computação escalável disponíveis para desenvolvedores de Python. Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • O Dask consiste em vários componentes e APIs diferentes, que podem ser categorizados em três camadas: o planejador, APIs de baixo nível e APIs de alto nível (veja imagem no próximo slide) Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Os componentes e camadas que compõem o Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • No núcleo está o task scheduler, que coordena e monitora a execução dos cálculos nos núcleos e máquinas da CPU • Esses cálculos são representados no código como Dask Delayed objects (avaliados no momento em que os valores são necessários - lazily) ou Dask Futures objects (são avaliadosem tempo real, independentemente de o valor ser necessário imediatamente ou não) Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • As APIs de alto nível da Dask oferecem uma camada de abstração sobre Delayed e Futures objects. • As operações nesses objetos de alto nível resultam em muitas operações paralelas de baixo nível gerenciadas pelos task schedulers. Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Algumas vantagens do Dask: • O Dask é totalmente implementado em Python e escala nativamente o NumPy, Pandas e o scikit-learn. • O Dask pode ser usado efetivamente para trabalhar com datasets médios em uma única máquina e grandes datasets em um cluster. • O Dask pode ser usado como uma estrutura geral para paralelizar a maioria dos objetos Python. • O Dask possui um overhead de configuração e manutenção muito baixa. Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Essencialmente, Dask divide datasets médios e grandes em partes menores e gerencia a execução paralela de funções sobre essas partes • Dask é tão útil para trabalhar com datasets médios em uma única máquina quanto para trabalhar com grandes datasets em um cluster Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Essencialmente, Dask divide datasets médios e grandes em partes menores e gerencia a execução paralela de funções sobre essas partes • Tudo isso pode ser feito sem a necessidade de refatorar o código existente ou escrever código adicional para lidar com problemas específicos do cluster (gerenciamento de recursos e transferência de dados) Introdução ao Python Dask Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • O Spark se tornou uma estrutura muito popular para analisar grandes conjuntos de dados (e é ótimo para isto) • Spark como uma alternativa in-memory para o MapReduce Apache Hadoop e é dependente da JVM (Java Virtual Machine). • O suporte ao Python veio mais tarde com o biblioteca PySpark. No entanto, toda a interação com o cluster passa pela JVM e assim, algumas execuções ocorrem fora do contexto do Python. • Além disso, o Spark não está equipado para manipular código que não pode ser modelo com MapReduce. Dask x Spark Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Dask’s task schedulers usa o conceito de gráficos acíclicos direcionados (DAGs). • DAGs não permitem loops (o grafo não possui ciclo) Dask e DAGs Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • O Dask usa DAGs para coordenar a execução do código paralelo nos núcleos e máquinas da CPU. • Os nós upstream devem ser concluídos antes que o trabalho possa começar em qualquer nó downstream dependente. • No caso de uma falha, as etapas para alcançar um nó podem ser repetidas desde o início sem atrapalhar o restante do processo. Dask e DAGs Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Dask e DAGs Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Primeiro contato com Python Dask • Conheça o site de opendata: • A cada terceira semana do mês, o Departamento de Finanças da cidade de Nova York registra e publica um conjunto de dados de todas as citações de estacionamento emitidas ao longo do ano fiscal até agora. Datasets https://opendata.cityofnewyork.us/ • Iremos utilizar contém 2Gb de dados: Parking Violations Issued - Fiscal Year 2017 (arquivo csv) Datasets https://catalog.data.gov/dataset?tags=parking-ticket • Hello Dask: API DataFrame • Uma etapa essencial de qualquer projeto de ciência de dados é realizar análises exploratórias no conjunto de dados. • Durante a análise exploratória, convém verificar os dados em busca de valores ausentes, outliers e outros problemas de qualidade dos dados. A first look at the DataFrame API • A limpeza do conjunto de dados garante que a análise que você faz e quaisquer conclusões que você tire sobre os dados não sejam influenciadas por dados errados ou anômalos. A first look at the DataFrame API • Com o Dask DataFrames, faremos a leitura de um dataset em busca de valores ausentes e na eliminação de colunas que estão faltando muitos dados ou que não serão úteis para análise. A first look at the DataFrame API • Abra a máquina virtual fornecida para a aula. Nela, já temos o dataset que iremos utilizar. Faça Download da VM aqui: • Abra o terminal, e entre no python environment A first look at the DataFrame API source dask/bin/activate jupyter notebook https://1drv.ms/u/s!AjVQhLFLEQuFs8ofwi35IOmIv7eM2g?e=XhTZ8V User: hadoop Password: haddop Programação com Python Dask – Parte I • Neste exemplo, examinaremos apenas os dados coletados a partir de 2017. Primeiro, você precisará importar os módulos Dask e ler seus dados. Dask DataFrames # Listing 2.1 import dask.dataframe as dd from dask.diagnostics import ProgressBar from matplotlib import pyplot as plt df = dd.read_csv('nyc-parking-tickets/*2017.csv') df Dask DataFrames Veja a saída gerada (DataFrame metadata): npartitions: em quantas partições o DataFrame é dividido Dask criou um DAG com 99 nós para processar os dados. Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 • Observe que os nomes das colunas estão na parte superior e abaixo está o respectivo tipo de dados de cada coluna. • Lembre-se que, diferentemente do Pandas que executa inteiramente na RAM, Dask irá lidar com dataset que não podem ser carregados na RAM, e portanto, podem estar distribuídos. • Assim, Dask DataFrames emprega métodos de amostragem aleatória para criar um profile e inferir tipos de dados de uma pequena amostra. Dask DataFrames • Como o Dask’s scheduler decide interromper o trabalho de processar este arquivo? • O valor de “npartitions“ mostra em quantas partições o DataFrame é dividido. • Como o arquivo possui um pouco mais de 2 GB, em 33 partições, cada partição tem aproximadamente 64 MB. Isso significa que, em vez de carregar o arquivo inteiro na RAM de uma só vez, cada Dask work thread irá executar um pedaço de 64 MB do arquivo por vez. Dask DataFrames Dask DataFrames O Dask divide grandes arquivos de dados em várias partições e funciona em uma partição por vez Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Dask DataFrames Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 O processamento de cada partição (etapas 1 e 3) pode ser feito um de cada vez ou em paralelo. Espaço de memória temporário • O comportamento é apresentado na figura do slide anterior. • Em vez de carregar antecipadamente todo o DataFrame na RAM, o Dask divide o arquivo em partes menores que podem ser processadas independentemente. • No caso do Dask DataFrames, cada partição é um Pandas DataFrame relativamente pequeno. • No exemplo da figura, O DataFrame consiste em duas partições. Assim, o Dask DataFrames único é composto por dois Pandas DataFrames menores. Dask DataFrames • Cada partição pode ser carregada na memória e trabalhada uma de cada vez ou em paralelo. • Como o worker node pode trabalhar em partes menores por vez, o processamento pode ser distribuído para muitas máquinas. Ou, no caso de um único nó, o trabalho pode prosseguir em datasets muito grandes sem resultar em erros de falta de memória. Dask DataFrames • Dask criou um DAG com 99 nós para processar os dados. • O grafo consiste em 99 nós, porque cada partição requer a criação de três operações: (1) lendo os dados brutos, (2) dividindo os dados no bloco detamanho apropriado e (3) inicializando o objeto DataFrame subjacente. • Assim, 33 partições com 3 tarefas por partição resultam em 99 tarefas Dask DataFrames • Neste exemplo, se tivéssemos 33 workers (nós no cluster ou cores um uma máquina), todo o arquivo poderia ser processado simultaneamente. • Mas com apenas um worker node (ou core), o Dask percorre cada partição, uma de cada vez. Dask DataFrames Dask DataFrames Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 O Dask permite que um único Pandas DataFrame seja executado em paralelo por vários hosts. Dask DataFrames Ref.: Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Processando dados em paralelo em várias máquinas Programação com Python Dask – Parte II • Sobre o tamanho das partições: evite partições muito grandes • Os chunks de dados devem ser pequenos o suficiente para que muitos deles caibam na memória disponível de um worker de uma só vez. • O Dask provavelmente manipulará tantos chunks em paralelo em uma máquina quanto você tiver núcleos nessa máquina • Portanto, se você tiver blocos de 1 GB e dez núcleos, é provável que o Dask use pelo menos 10 GB de memória. • Além disso, é comum que o Dask tenha de duas a três vezes mais chunks disponíveis para trabalhar, para que ele sempre tenha algo em que trabalhar. Dask DataFrames • Agora, vamos tentar contar os valores ausentes em cada coluna em todo o arquivo. Dask DataFrames missing_values = df.isnull().sum() missing_values Dask Series Structure: npartitions=1 Date First Observed int64 Violation Time ... dtype: int64 Dask Name: dataframe-sum-agg, 166 tasks • Em vez de obter as contagens ausentes, o Dask retorna algumas informações de metadados sobre o resultado esperado • Observe que Dask ainda não realizou nenhum processamento porque usa lazy computation. (o Dask preparou outro DAG, que foi armazenado na variável missing_values.) • Os dados não são computados até que o grafo de tarefas seja executado explicitamente Dask DataFrames • Agora, vamos tentar contar os valores ausentes em cada coluna em todo o arquivo. Dask DataFrames missing_values = df.isnull().sum() missing_values Dask Series Structure: npartitions=1 Date First Observed int64 Violation Time ... dtype: int64 Dask Name: dataframe-sum-agg, 166 tasks Observe que a contagem de tarefas aumentou para 166 Dask realizou as 99 primeiras tarefas do DAG usadas para ler o arquivo de dados e criar o DataFrame chamado df, adicionou 66 tarefas (2 por partição) para verificar nulos e soma e, em seguida, adicionou uma etapa final para coletar todos os dados • Antes de executar o cálculo, iremos transformar esses números em porcentagens, dividindo as contagens de valores ausentes (missing_values) pelo número total de linhas no DataFrame (df.index.size) e multiplicando tudo por 100 Dask DataFrames missing_count = ((missing_values / df.index.size) * 100) missing_count • Observe que o número de tarefas aumentou novamente e o tipo de dados da série resultante mudou de int64 to float64 (divisão resulta em uma resta de ponto flutuante) Dask DataFrames Dask Series Structure: npartitions=1 Date First Observed float64 Violation Time ... dtype: float64 Dask Name: mul, 235 tasks • Executando cálculos com o método compute() Dask DataFrames with ProgressBar(): missing_count_pct = missing_count.compute() missing_count_pct Método do DataFrame chamado para que o Dask calcule o resultado (utilizando a DAG criada anteriormente). imprimirá uma barra de progresso baseada em texto, mostrando a porcentagem estimada de conclusão e o tempo decorrido para o cálculo. • Pela saída de nosso cálculo de valores ausentes, podemos chegar a algumas conclusões: • Não há dados sobre “No Standing or Stopping Violation”, “Hydrant Violation”, “Double Parking Violation”. Estão completamente vazios. Não faz sentido manter estas colunas. • Eliminaremos qualquer coluna que esteja com mais de 60% de seus valores ausentes. Dask DataFrames Dask DataFrames columns_to_drop = missing_count_pct[missing_count_pct > 60].index with ProgressBar(): df_dropped = df.drop(columns_to_drop, axis=1).persist() obtivemos o índice da série filtrada, que é uma lista de nomes de colunas. usamos esse índice para descartar colunas no Dask DataFrame com o mesmo nome Agora o objeto é disponibilizado para todos os threads. No caso de execução em um cluster, o objeto será serializado e transmitido para todos os workes nodes. Evita que Dask descarte os dados filtrados criados tão logo termine a execução. persist() permite que Dask mantenha em memória o resultado Paralelizando código com Dask Delayed • Para entender melhor como o paralelismo funciona, paralelizamos código simples de estilo for-loop com Dask e dask.delayed. • Essa é uma maneira simples de usar o dask para paralelizar bases de código existentes ou criar sistemas complexos. Dask Delayed Fonte: https://docs.dask.org/en/latest/best-practices.html Dask Delayed from dask.distributed import Client client = Client(n_workers=4) from time import sleep def inc(x): sleep(1) return x + 1 def add(x, y): sleep(1) return x + y Primeiro, vamos criar algumas funções inc e add que dormem um pouco para simular algum processamento. Em seguida, executaremos essas funções normalmente. Fonte: https://docs.dask.org/en/latest/best-practices.html • Cronometramos a execução desse código normal usando %%time, que é uma função especial do Jupyter Notebook. Dask Delayed %%time # This takes three seconds to run because we call each # function sequentially, one after the other x = inc(1) y = inc(2) z = add(x, y) CPU times: user 436 ms, sys: 133 ms, total: 568 ms Wall time: 3 s Observe o tempo de execução de 3 segundos. Este código é sequencial.Fonte: https://docs.dask.org/en/latest/best-practices.html • Paralelizando o código: Observe que estas duas chamadas de incremento (inc functions) podem ser chamadas em paralelo, porque são totalmente independentes uma da outra. • Transformaremos o inc e add usando a função dask.delayed. Quando a chamamos a versão delayed passando os argumentos, exatamente como antes, a função original ainda não é chamada (por isso que a execução termina muito rapidamente). Em vez disso, é criado um objeto delayed, que monitora a função a ser chamada e os argumentos a serem transmitidos. Dask Delayed Fonte: https://docs.dask.org/en/latest/best-practices.html Dask Delayed from dask import delayed %%time # This runs immediately, all it does is build a graph x = delayed(inc)(1) y = delayed(inc)(2) z = delayed(add)(x, y) CPU times: user 766 µs, sys: 207 µs, total: 973 µs Wall time: 733 µs Foi executado imediatamente, já que nada realmente aconteceu ainda. Fonte: https://docs.dask.org/en/latest/best-practices.html • Para obter o resultado, chamamos agora a função compute() Dask Delayed %%time # This actually runs our computation using a local process pool z.compute() CPU times: user 333 ms, sys: 85 ms, total: 418 ms Wall time: 2.04 s Observe que foi executado mais rapidamente que o código original. Fonte: https://docs.dask.org/en/latest/best-practices.html • O que aconteceu neste processamento: o objeto “z” é um lazy Delayed object. Ele contém tudo o que precisamos para calcular o resultado final, incluindo referências a todas as funções necessárias. Podemos avaliar o resultado com .compute () ou podemos visualizar o gráfico de tarefas para esse valor com .visualize (). Dask Delayed z Delayed('add-197e820c-5b37-4da4-827c-0931d6f717ea') Fonte: https://docs.dask.org/en/latest/best-practices.html Dask Delayed Observe o grafo gerado (Dask DAG). Observe o fluxo de execução que foi criado. # Look at the task graph for `z` z.visualize() Fonte: https://docs.dask.org/en/latest/best-practices.html • Analisando o resultado: • Por que passamos de 3 para 2?Por que não conseguimos paralelizar até 1s? Dask Delayed Fonte: https://docs.dask.org/en/latest/best-practices.html Exercício resolvido: Dask Delayed • Exercícios: Paralelize o código do loop abaixo: veja o arquivo “Parallelize a for loop.ipynb” • Mostre a DAG gerada Dask Delayed Fonte: https://docs.dask.org/en/latest/best-practices.html Resposta Fonte: https://docs.dask.org/en/latest/best-practices.html %%time # Your parallel code here... from dask import delayed results = [] for x in data: y = delayed(inc)(x) results.append(y) total = delayed(sum)(results) print("Before computing:", total) # Let's see what type of thing total is result = total.compute() print("After computing :", result) # After it's computed total.visualize() Referências Bibliográficas Jesse C. Daniel . Data Science with Python and Dask. July 2019 ISBN 9781617295607 Considerações Finais Tecnologias para Ecossistema Big Data Prof. Henrique Batista da Silva Introdução linguagem SQL Introdução Junto com o modelo Relacional de Codd, foi posposto uma linguagem chamada DSL/Alpha para manipulação dos dados no modelo. A IBM criou uma simplificação desta linguagem, chamada Square. Aprimoramentos na Square levaram a uma linguagem chamada SEQUEL, posteriormente renomeada para SQL. Introdução Linguagem padrão para lidar com Banco de Dados (BD). Praticamente todos SGBDs (Oracle, SQLServer, MySQL, etc.) que estão no mercado suportam a linguagem SQL. Muitos bancos de dados NoSQL suportam o padrão SQL Like SQL tem uma ligação com o modelo relacional pois o resultado de uma consulta SQL é uma tabela (também chamada de conjunto resultado). Classes de Comandos SQL • A linguagem SQL se divide em três subgrupos: • Linguagem de Definição de Dados (DDL - Data Denition Language): • Usada para definição dos esquemas • Linguagem de Manipulação de Dados (DML - Data Manipulation Language): • Utilizada pelos usuários para manipulação dos dados ( inclusão, alteração e exclusão). Classes de Comandos SQL • Linguagem de Controle de Dados (DCL - Data Control Language): • Utilizada para conceder e retirar privilégios de usuários de BD em objetos de BD • Linguagem de consulta de Dados (DQL - Data Query Language): • Usada para recuperação de dados • Linguagem de controle de transação (TCL - transaction Control Language): • Utilizada para lidar com transações no banco de dados. Classes de Comandos SQL Por exemplo, para criar uma nova tabela é necessário utilizar o comando CREATE TABLE (definição de dados). Essa instrução cria uma tabela com duas colunas, matrícula (chave primária da tabela) e nome CREATE TABLE Aluno ( matricula INT, nome VARCHAR(30), CONSTRAINT pk_matricula PRIMARY KEY (matricula) ); Classes de Comandos SQL Para popular o banco de dados é necessário o comando INSERT. Essa instrução adiciona um linha à tabela Aluno com o valor 10 na coluna matricula e o valor ‘Maria’ na coluna nome INSERT INTO Aluno (matricula, nome) VALUES (10, 'Maria'); Classes de Comandos SQL Com o banco populado é possível recuperar valores com o comando SELECT (manipulação de dados): Nome Maria SELECT nome FROM Aluno WHERE matricula = 10; DDL (Linguagem de Definição de Dados) Criando Tabelas no Banco DDL (Linguagem de Definição de Dados) • É a linguagem que define a ESTRUTURA do BD. • Seus comandos permitem a criação, alteração e exclusão de objetos em um banco de dados. • Seus principais comandos são: Comando Definição Exemplos CREATE Cria objetos do BD. Criação da tabela CLIENTE ALTER Altera objetos do BD. Alteração da tabela CLIENTE incluindo a coluna TELEFONE DROP Remove objetos do BD. Exclusão da tabela CLIENTE Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando Create table • Comando utilizado para criar uma nova relação (tabela), dando a ela: • Nome • Especificando seus atributos (suas colunas) • Nome e tipo de dado (domínio). • Restrições do atributo (not null) • Especificando suas restrições (Tipo, chave). • Especificado depois que os atributos forem declarados. • Ou com o comando ALTER TABLE. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep Create table Empregado ( MatEmp smallint not null, NomEmp char(30) not null, EndEmp char(80) null, CidEmp char(20) null, Constraint PK_EMP primary Key(MatEmp) ); Comando Create table Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando Create table Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep Constraint é uma restrição de chave primária, criada sobre a coluna MatEmp e recebe o nome de PK_EMP (são definidas com a cláusula PRIMARY KEY). Create table Empregado ( MatEmp smallint not null, NomEmp char(30) not null, EndEmp char(80) null, CidEmp char(20) null, Constraint PK_EMP primary Key(MatEmp) ); Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando Create table Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep Create table Empregado ( MatEmp smallint not null, NomEmp char(30) not null, EndEmp char(80) null, CidEmp char(20) null, Constraint PK_EMP primary Key(MatEmp) ); Não é necessário especificar Atributo Chave Estrangeira • Similar a instrução para criar a tabela Empregado, mas com algumas exceções: • Como empregados diferentes podem ter dependentes com o mesmo nome, é preciso mais do que a coluna MatEmp para garantir a unicidade, portanto a tabela tem chave primária composta de duas colunas. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Atributo Chave Estrangeira • A tabela dependente contém outro tipo de restrição, denominada restrição de chave estrangeira (definida com a cláusula FOREIGN KEY). • Restringe os valores da coluna MatEmp na tabela Dependente para que sejam incluídos apenas valores existentes na tabela Empregado. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Atributo Chave Estrangeira Create table Dependente ( NomDep char(30), DatNasDep date, MatEmp smallint, Constraint PK_DEP primary Key (MatEmp, NomDep), Constraint FK_EMP_DEP foreign Key (MatEmp) References Empregado (MatEmp) ); Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep Modificando e excluindo tabelas Comandos para Alterações no Esquema SQL • Alter table • Altera a definição de uma tabela no banco de dados • Ações: • Adicionar uma coluna • Retirar uma coluna • Adicionar restrições de tabela • Retirar restrições de tabela Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comandos para Alterações no Esquema SQL Adicionar uma Coluna Exemplo Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE <nome da tabela> ADD <nome da coluna> <tipo da coluna> [<restrição do atributo>] ; ALTER TABLE EMPREGADO ADD UF_EMP CHAR(2) NULL Alter Table (Adicionar Coluna) É necessário entrar com um valor para o novo atributo UF_EMP de cada tupla de EMPREGADO. Pode ser feito pela especificação da cláusula default ou pelo comando UPDATE. Neste caso, se não for especificado nenhuma cláusula default, o novo atributo será inserido como NULL automaticamente, a restrição NOT NULL não é permitida neste caso. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Alter Table (Remover Coluna) Remover uma coluna: Para remover uma coluna, deve-se optar por CASCADE ou RESTRICT em termos de comportamento para eliminação. Exemplo Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE <nome da tabela> DROP COLUMN <nome da coluna> ALTER TABLE EMPREGADO DROP COLUMN UF_EMP Alguns SGBDs exigem a COLUMN, após DROP Alter Table (ModificarRestrições) Ainda é possível modificar as restrições especificadas em uma tabela, adicionando ou eliminando uma restrição. Eliminando uma restrição: Para ser eliminada, a restrição precisa ter recebido um nome quando foi especificada. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE <nome da tabela> DROP CONSTRAINT <nome> ; Alter Table (Modificar Restrições) Excluindo uma Primary Key: Excluindo uma Foreign Key: Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE DEPENDENTE DROP CONSTRAINT PK_DEP ALTER TABLE DEPENDENTE DROP CONSTRAINT FK_EMP_DEP Alter Table (Modificar Restrições) Adicionando uma Restrição: É realizada pela palavra chave ADD, seguida da nova restrição: Se for uma Primary Key: Exemplo Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE <nome da tabela> ADD CONSTRAINT <nome> PRIMARY KEY (<nomes da colunas>); ALTER TABLE EMPREGADO ADD CONSTRAINT PK_EMP PRIMARY KEY (MATEMP) Alter Table (Modificar Restrições) Adicionando uma Restrição: Se for uma Foreign Key: Exemplo Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. ALTER TABLE <nome da tabela> ADD CONSTRAINT <nome> FOREIGN KEY (<nome coluna>) REFERENCES <nome tabela>; ALTER TABLE DEPENDENTE ADD CONSTRAINT FK_EMP_DEP FOREIGN KEY (MATEMP) REFERENCES EMPREGADO (MatEmp) Obs.: para criar uma restrição, a coluna que será PK, já tem que ter sido criada anteriormente como uma coluna normal DROP Table Pode ser usado para eliminar uma tabela de um banco de dados Sintaxe: Exemplo Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. DROP TABLE <nome da tabela> ; DROP TABLE DEPENDENTE DML (Linguagem de Manipulação de Dados) DML (Linguagem de Manipulação de Dados) Linguagem que manipula os dados no banco. Seus comandos permitem a recuperação, inserção, alteração e exclusão de dados. Seus principais comandos são: Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando Definição Exemplos INSERT Adiciona dados a uma tabela. Inserir o João como novo cliente na tabela CLIENTE UPDATE Atualiza os dados de uma tabela. Alterar o número do telefone do cliente João na tabela CLIENTE DELETE Exclui dados de uma tabela. Exclui o cliente Geraldo da tabela CLIENTE Comando INSERT • É usado para adicionar uma única tupla (linha) em uma relação (tabela). • Existem três componentes principais no comando INSERT: • O nome da tabela na qual serão adicionado os dados. • Os nomes da colunas que serão populadas dentro da tabela. • Os valores que serão usados para popular as colunas. Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando INSERT • Os valores devem ser relacionados na mesma ordem em que as colunas foram especificados no comando CREATE TABLE, caso o nome das colunas não seja explicitados. • Sintaxe: Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. INSERT INTO <nome da tabela>[(<nome da coluna 1>, <nome da coluna 2>, <nome da coluna n>)] VALUES (<valor constante 1>, < valor constante 2>, < valor constante n>) Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando INSERT INSERT INTO Empregado (MatEmp, NomEmp, EndEmp , CidEmp ) VALUES (10, 'Marcos Ramos', 'Rua X', 'Itabira'); INSERT INTO Empregado VALUES (11, 'Marcos Ramos',null,'SP'); INSERT INTO Empregado (NomEmp, EndEmp , MatEmp, CidEmp ) VALUES ('Mario Andrade', 'Rua 4', 12, 'Belo Horizonte'); INSERT INTO Dependente (NomDep, DatNasDep, MatEmp) VALUES ('Carlos Ramos', '2003-01-10',10); INSERT INTO Dependente (NomDep, DatNasDep, MatEmp) VALUES ('Joao Andrade', '2003-10-01',11); Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep Comando DELETE • Remove tuplas (linhas) de uma relação (tabela). • Possui a cláusula WHERE para filtrar as linhas a serem excluídas. • Poderão se excluídas nenhuma, uma ou várias linhas em um único comando. • As remoções poderão propagar-se nas tuplas de outras relações. • Cláusula WHERE omitida indica que todas as linhas na tabela devem ser excluídas (resulta em tabela vazia). • Sintaxe: Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. DELETE FROM <nome da tabela> [WHERE <condição da seleção>]; Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando DELETE Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep DELETE FROM EMPREGADO WHERE MatEmp = 12; DELETE FROM EMPREGADO; --Apaga Todas as linhas da relação empregado (tabela empregado se torna vazia). Comando UPDATE • Colunas de linhas existentes em uma tabela podem ser modificadas. • Possui a cláusula WHERE para filtrar as linhas a serem alteradas. • Uma cláusula adicional SET dentro do comando UPDATE, especifica os atributos que serão modificados e seus novos valores. • Sintaxe: Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. UPDATE <nome da tabela> SET <nome da coluna 1> = <expressão do valor 1>, <nome da coluna n> = <expressão do valor n> [WHERE <condição da seleção>]; Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Comando DELETE Empregado Tem Dependente 1 N @MatEmp NomEmp EndEmp CidEmp @MatEmp(FK) @NomDep DatNasDep UPDATE Empregado SET EndEmp = 'Rua Agua Santa' WHERE MatEmp = 10; Prática Abra o banco de dados e apague e atualize alguns registros nas tabelas Empregado e Dependente. Novamente, fique atento com as restrições (Domínio, Chave, Entidade, Integridade referencial) Observe o que acontece ao tentar apagar registros que violem a integridade referencial. Prática Agora altere a tabela dependente para propagar a exclusão de um registro em empregado a todos os seus dependentes. Use o comando: Alter TABLE Dependente DROP CONSTRAINT FK_EMP_DEP Alter table Dependente ADD constraint FK_EMP_DEP foreign key (MatEmp) references EMPREGADO (MatEmp) On delete Cascade Lembre-se que antes você deve excluir os dependentes. Agora repita o procedimento anterior para apagar um empregado que contém dependentes e veja o resultado. DQL (Linguagem de consulta de Dados) Estrutura do comando SELECT Comando SELECT Comando SELECT é a instrução Básica para recuperar informações. O formato básico da declaração SELECT é composto por três cláusulas (SELECT, FROM e WHERE): Sintaxe: SELECT <lista atributos> FROM <lista de tabelas> WHERE <condição> Comando SELECT <lista atributos> é uma lista de nomes de atributos cujo valores são recuperados. <lista de tabelas> é uma lista dos nomes da relações necessárias para o processamento de consulta. <condição> é uma expressão condicional que identifica as tuplas que serão recuperadas. Comando SELECT Exemplo: Esta consulta seleciona todas as colunas da tabela EMPREGADO. Esta consulta seleciona as colunas NomEmp e cidEmp da tabela EMPREGADO, cujo valor do atributo MatEmp seja igual a 11. SELECT * FROM Empregado; SELECT NomEmp, CidEmp FROM Empregado WHERE MatEmp = 11; Comando SELECT Esta consulta seleciona as colunas NomEmp e cidEmp da tabela EMPREGADO, cujo valor do atributo MatEmp seja igual a 11. Obs.: Como a coluna MatEmp é atributo chave a consulta irá retornar apenas uma linha, a linha do empregado com MatEmp igual a 11 (pois não há outra linha com valor 11 para MatEmp). Comando SELECT É uma das ultimas cláusulas a ser avaliada pelo SGBD. Antes de gerar o resultado, é necessário conhecer todas as colunas que podem fazer parte do resultado. A cláusula SELECT determina quais entre todas as colunas possíveis deverão ser incluídas no conjunto resultado da consulta. Comando SELECT A cláusula FROM define as tabelas usadas em uma consulta. SELECT * FROM Empregado Prática Altere a tabela Empregado e adicione o campo salário. Atualize os registros existentes de Empregado para adicionar o valor dos salários. ALTER TABLE Empregado ADD Salario FLOAT NULL UPDATE Empregado SET Salario = 2000 WHEREMatEmp = 10; UPDATE Empregado SET Salario = 1000 WHERE MatEmp = 11; UPDATE Empregado SET Salario = 3000 WHERE MatEmp = 12; Expressões aritméticas, alias (atributo e relação) e uso do asterisco Expressões aritméticas Expressões com dados numéricos e de data usando operadores aritméticos: SELECT NomEmp, Salario, Salario+300 FROM EMPREGADO Expressões aritméticas • A multiplicação e a divisão têm prioridade sobre a adição e a subtração. • Os operadores com a mesma prioridade são avaliados da esquerda para a direita. • Parênteses são usados para forçar a avaliação priorizada e para esclarecer as instruções. • Exemplo: SELECT NomEmp, Salario, 12*Salario+100 FROM EMPREGADO SELECT NomEmp, Salario, 12*(Salario+100) FROM EMPREGADO Aliases de Colunas Cada coluna retornada no comando SELECT, o nome do atributo é apresentado como resultado. Porém, é possível atribuir rótulos próprios adicionando um alias (apelido) de coluna após cada elemento da cláusula SELECT. É útil para cálculos, especialmente se o nome da coluna for longo. SELECT NomEmp AS NOME_EMPREGADO, Salario AS SAL_EMPREGADO FROM EMPREGADO EMP SELECT NomEmp, Salario, 12*(Salario) AS SALARIO_ANUAL FROM EMPREGADO Aliases de Colunas Quando múltiplas tabelas são utilizadas em uma única consulta, é necessário uma maneira para identificar a qual tabela a coluna pertence. Principalmente quando há colunas de tabelas diferentes com o mesmo nome. Desta forma, define-se um alias a cada tabela para utilizá-lo ao longo da consulta. SELECT NomEmp AS NOME_EMPREGADO, Salario AS SAL_EMPREGADO FROM EMPREGADO AS EMP Definindo Alias de Relações Em SQL é possível dar nomes alternativos pela adição do qualificador AS (alias). O alias pode ser usado tanto nos atributos quantos nas relações e em ambas as cláusulas SELECT e FROM. O alias de relações é utilizado principalmente quando é necessário realizar junção de duas ou mais relações. Neste caso, o alias é importante para a explicitar a distinção dos atributos de cada uma das relações durante toda a consulta. Definindo Alias de Relações Exemplo: Recupere o nome e o endereço de todos os empregados. O Uso do AS não é obrigatório para pseudônimo de relações. SELECT E.NomEmp, E.EndEmp FROM Empregado E Uso do asterisco Para recuperar todos os valores dos atributos da tuplas selecionadas, não é necessário especificar todos os seus nomes. Utiliza-se apenas um asterisco (*), que significa selecionar todos os atributos. SELECT * FROM Empregado SELECT * FROM Empregado WHERE MatEmp = 11; Distinct e Cláusula Where Prática Execute o script “modelo_relacional.sql” fornecido junto ao material de aula. Verifique as alterações realizados pelo script. Removendo Linhas Duplicadas Uma consulta pode retornar linhas de dados duplicadas. Por exemplo, ao selecionar todos os códigos do clientes que possuem conta: Alguns clientes podem ter mais de uma conta, o mesmo código do cliente será exibido para cada conta de cada cliente. SELECT CODCLIENTE FROM CONTA; Removendo Linhas Duplicadas O objetivo é obter o conjunto específico de cliente que possuem conta (independente de quantas contas ele possui). É necessário utilizar a cláusula DISTINCT, diretamente após a cláusula SELECT: SELECT DISTINCT CODCLIENTE FROM CONTA; Cláusula WHERE A cláusula WHERE é utilizada para restringir as linhas selecionadas. A cláusula WHERE segue a cláusula FROM. SELECT NOMEMP, CARGO FROM EMPREGADO WHERE BAIRRO = 'SAVASSI' Cláusula WHERE Exemplo: • Esta consulta envolve somente a relação EMPREGADO relacionada na cláusula FROM • A consulta seleciona as tuplas de EMPREGADO que satisfazem a condição da cláusula WHERE • Então, projeta o resultado dos atributos NomEmp e CidEmp relacionados nos cláusula SELECT. SELECT NomEmp, CidEmp FROM Empregado WHERE MatEmp = 11; Cláusula WHERE • A cláusula SELECT, em SQL, especifica atributos para projeção. • A cláusula WHERE especifica a condição de seleção. • Somente aquelas tuplas que satisfazem a condição serão selecionadas. Cláusula WHERE A ausência da cláusula Where significa que não há nenhuma condição para seleção de tuplas. Assim, todas as tuplas da relação da cláusula FROM serão retornadas no resultado da consulta. SELECT NomEmp FROM Empregado; Cláusula WHERE Se for especificada mais de uma relação na cláusula FROM, então o produto cartesiano dessas relações será obtido. SELECT NomEmp, EndEmp FROM Empregado, Departamento É extremamente importante especificar todas as condições de seleção e junção (abordaremos este tópico mais tarde) na cláusula WHERE para evitar resultados incorretos ou muito grandes. Operadores de Comparação Operadores de Comparação Em SQL, os operadores lógicos básicos de comparação usado são: =, <, <=, >, >=, e <> Operadores de: igualdade, menor, menor ou igual, maior, maior ou igual e diferente. Operadores de Comparação • Operadores lógicos de comparação: • Operado AND retorna verdadeiro (true), se todas as condições forem verdadeiras. • Operador OR retorna verdadeiro (true), se pelo menos umas das condições forem verdadeiras. • Operador NOT retorna verdadeiro (true), se condição for falsa. AND, OR e NOT LIKE • Em SQL é possível criar condição para comparação de partes de uma cadeia de caracteres por meio do operador de comparação LIKE. • Esse operador pode ser usado para comparação de padrões de cadeia. • As partes das cadeias podem ser especificadas usando dois caracteres: • % : substitui um número arbitrário de caracteres. • Underscore (_): substitui um único caracter. LIKE Exemplo: Recupere todos os empregados cujos endereços sejam de Belo Horizonte: Recupere todos os empregados cujo a segunda letra do nome seja ‘a’: SELECT NomEmp FROM Empregado WHERE CidEmp LIKE '%Belo Horizonte%'; SELECT NomEmp FROM Empregado WHERE NomEmp LIKE '_a%'; BETWEEN • Em SQL também é possível comparar valores dentro de um determinado intervalo: • Exemplo: • Recupere todos os empregados do departamento 1 que ganham entre 30 mil e 40 mil. SELECT * FROM Empregado WHERE (Salario BETWEEN 30000 AND 40000) AND CodDepto = 1; NOT IN • Também é possível realizar comparações com uma lista de valores ao invés de apenas um único valor. • O operador IN (lista), pode ser utilizados para comparação com uma lista de valores. • Exemplo: • Recupere os empregados cujo o cargo não seja nem de vendedor e nem de presidente. SELECT NomEmp FROM EMPREGADO WHERE CARGO NOT IN ('Vendedor', 'Presidente') IS NULL • Utilizando o operador IS NULL é possível comparar e verificar se o valor do atributo é nulo. • Exemplo: • Recupere os empregados que trabalham no departamento 10 e que não receberam nenhuma comissão. SELECT NomEmp FROM EMPREGADO WHERE COMISSAO IS NULL AND CodDepto = 1 SELECT NomEmp FROM EMPREGADO WHERE COMISSAO IS NOT NULL AND CodDepto = 1 Funções Agregadas Funções Agregadas em SQL O SQL tem funcionalidades que incorporam conceitos de funções agregadas. Há diversas funções para este fim: Max() Retorna o valor máximo dentro de um conjunto. Min() Retorna o valor mínimo dentro de um conjunto. Avg() Retorna o valor médio de um conjunto. Sum() Retorna a soma dos valores de um conjunto. Count() Retorna a quantidade de valores de um conjunto. Funções Agregadas em SQL Exemplo 1: Encontre a soma dos salários, o maior salário, o menor salário, e a média salarial de todos os empregados. SELECT SUM (salario), MAX (salario), MIN (salario), AVG (salario) FROM Empregado; Funções Agregadas em SQL Exemplo 3 Recupera o número total de empregados da empresa: Neste exemplo, * refere-se às linhas (tuplas), logo COUNT devolverá o número de linhas do resultado da consulta. SELECT COUNT (*) FROM Empregado; Order By, Group By e Having Ordenando o resultado das consultas SQL permite que o usuário ordene as tuplas do resultado de uma consulta pelos valores de seus atributos, utilizando a cláusula ORDER BY. A ordenaçãopadrão é de forma ascendente. Pode-se também especificar a palavra-chave DESC (sempre depois do nome da coluna) para ordenar os valores de forma descendente. SELECT NomEmp FROM EMPREGADO ORDER BY NomEmp SELECT NomEmp FROM EMPREGADO ORDER BY NomEmp DESC Agrupamento Em muitos casos, é necessário aplicar as funções agregadas para os subgrupos de tuplas de uma relação. No qual os subgrupos são escolhidos com base em alguns atributos. SQL possui a cláusula Group By para esse fim. Esta especifica os atributos de agrupamento, que também poderiam aparecer na cláusula Select. Agrupamento Exemplo: Para cada departamento, recupere seu número, número de empregados que nele trabalham e a média de seus salários. SELECT CodDepto, COUNT (*) as 'qtd', AVG (Salario) as 'sal' FROM Empregado GROUP BY CodDepto; As tuplas de Empregado serão particionadas em grupos, cada grupo tendo o mesmo valor para o atributo do agrupamento. A função COUNT() e AVG (), serão aplicada em cada grupo de tuplas Agrupamento Exemplo: CPF Nome Sal CodDepto 123 José 1000 1 223 Maria 2000 1 323 João 2000 2 123 Alberto 4000 2 121 Carla 3000 3 CodDepto COUNT(*) AVG(Sal) 1 2 1500 2 2 3000 3 1 3000 Agrupamento das tuplas de Empregado por meio do valor do CodDepto Resultado da consulta Agrupamento Algumas vezes é necessário recuperar os valores das funções agregadas apenas para os grupos que satisfazem certas condições. Como a cláusula GROUP BY é executada após a avaliação da cláusula WHERE, você não pode adicionar condição de filtro à cláusula WHERE para este propósito. Por exemplo: Suponha que seja necessário recuperar, para cada projeto, o seu numero, seu nome e numero de empregados que nele trabalham. Mas somente os projetos com mais de dois empregados. Agrupamento Exemplo: Suponha que seja necessário recuperar, para cada projeto, o seu numero, seu nome e numero de empregados que nele trabalham. SELECT P.CodProjeto, P.ProjNome, COUNT (*) FROM Projeto P, Trabalha_Em T WHERE P.CodProjeto = T.CodProjeto GROUP BY P.CodProjeto, P.ProjNome Agrupamento Exemplo: Suponha que seja necessário recuperar, para cada projeto, o seu numero, seu nome e numero de empregados que nele trabalham. Mas somente os projetos com mais de dois empregados. SELECT P.CodProjeto, P.ProjNome, COUNT (*) FROM Projeto P, Trabalha_Em T WHERE P.CodProjeto = T.CodProjeto AND COUNT (*) > 2 GROUP BY P.CodProjeto, P.ProjNome Agrupamento Exemplo: Suponha que seja necessário recuperar, para cada projeto, o seu numero, seu nome e numero de empregados que nele trabalham. Mas somente os projetos com mais de dois empregados. SELECT P.CodProjeto, P.ProjNome, COUNT (*) FROM Projeto P, Trabalha_Em T WHERE P.CodProjeto = T.CodProjeto AND COUNT (*) > 2 GROUP BY P.CodProjeto, P.ProjNome Incorreto, pois os grupos ainda não foram gerados no momento em que a cláusula WHERE é avaliada. Agrupamento Exemplo: Suponha que seja necessário recuperar, para cada projeto, o seu numero, seu nome e numero de empregados que nele trabalham. Mas somente os projetos com mais de dois empregados. SELECT P.CodProjeto, P.ProjNome, COUNT (*) FROM Projeto P, Trabalha_Em T WHERE P.CodProjeto = T.CodProjeto GROUP BY P.CodProjeto, P.ProjNome HAVING COUNT (*) > 2 Agrupamento Enquanto as condições de seleção da cláusula WHERE limitam as tuplas nas quais as funções serão aplicadas, a cláusula HAVING serve para escolher grupos inteiros. Consultas Aninhadas Consultas Aninhadas Algumas consultas dependem da busca de valores presentes no banco de dados para, então, usá-lo na condição de comparação. Essas consultas podem ser formuladas por meio das consultas aninhadas. Um bloco completo de select-from-where dentro da cláusula WHERE da consulta externa. Consultas Aninhadas Exemplo: Recupere o Nome do Funcionário que tem o maior salário Bruto da empresa: Recupera o maior salário da tabela funcionário Pesquisa todos os salários da tabela funcionário e compara com o valor MAX. Quando encontrar, projeta o nome do funcionário (campo NOME da tupla com SALARIO = MAX) SELECT NomEmp FROM EMPREGADO WHERE SALARIOBRUTO = (SELECT MAX(SALARIOBRUTO) FROM EMPREGADO) Consultas Aninhadas Exemplo: Recupere o CPF de todos os empregados que trabalham a mesma quantidade de horas em algum dos projetos em que o empregado ‘John’ (CPF = ‘123’) trabalhe. SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Recupera todas as horas trabalhadas nos projetos do CPF 123. O resultado é um conjunto Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Trabalha_Em Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Trabalha_Em Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Resultado Horas 20 15 Conj_Resultado Trabalha_Em Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Trabalha_Em Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 Trabalha_Em IN Horas 20 15 Conj_Resultado SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 Trabalha_Em IN Horas 20 15 Conj_Resultado SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 Trabalha_Em IN Horas 20 15 Conj_Resultado Resultado MatEmp 123 123 121 112 332 333 SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); Referência: Navathe. Sistemas de Banco de Dados – 2011, 6ª Ed. Consultas Aninhadas CPF CodProj Horas 123 1 20 223 1 10 323 2 10 123 2 15 121 3 20 112 3 15 332 3 20 333 4 20 Trabalha_Em IN Horas 20 15 Conj_Resultado DISTINCT SELECT DISTINCT MatEmp FROM Trabalha_Em WHERE Horas IN (SELECT Horas FROM Trabalha_Em WHERE MatEmp = ‘123’); MatEmp 123 121 112 332 333 Consultas Aninhadas A palavras ALL pode ser combinada com algum dos operadores de comparação. Exemplo: Recupere o nome dos empregados cujo salários são maiores que os salários de todos os empregados do departamento 2. SELECT NomEmp FROM Empregado WHERESalario > ALL (SELECT Salario FROM Empregado WHERE CodDepto = 2); Consultas Aninhadas Exemplo (Alias): Recupere o nome de cada um dos empregados que tenham dependentes cujo o sexo seja o mesmo do empregado em questão: Atributo Sexo da tabela Empregado SELECT NomEmp FROM Empregado AS E WHERE E.MatEmp IN (SELECT MatEmp FROM Dependente WHERE E.Sexo = Sexo); Função EXISTS É usada para verificar se o resultadode uma consulta aninhada é vazio (não contém nenhuma tupla) ou não. Exemplo: Recupere o nome dos empregados que possuem dependentes: SELECT NomEmp FROM Empregado E WHERE Exists ( SELECT * FROM Dependente D WHERE D.MatEmp = E.MatEmp) Se existir qualquer tupla no conjunto resultado da consulta interna, a tupla Empregado será selecionada. Função EXISTS Pode-se também utilizar NOT EXISTS: Exemplo: Recupere o nome dos empregados que não possuem dependentes: Se não existir nenhuma tupla no conjunto resultado da consulta interna, a tupla Empregado será selecionada. SELECT NomEmp FROM Empregado E WHERE NOT Exists (SELECT * FROM Dependente D WHERE D.MatEmp = E.MatEmp) JOINS Vínculos de tabelas Em SQL é possível realizar vínculos entre duas ou mais relações do banco de dados. Para tanto, todas as relações envolvidas devem estar presentes na cláusula FROM. Quando mais de um tabela aparece na cláusula FROM, as condições para vincular as tabelas devem ser incluídas (condição de junção). Vínculos de tabelas A operação de junção é usada para combinar as tuplas relacionadas em duas relações dentro de uma tupla única. Importante pois permite processar os relacionamentos entre as relações. Exemplo: Recuperar o nome do gerente de cada departamento. É necessário avaliar cada tupla de departamento com a tupla empregado na qual o valor do CPF case com o valor do CPFGerente da tupla departamento. Vínculos de tabelas O resultado da junção é uma relação Q, em que há uma tupla (em Q) para cada combinação de tuplas (uma de R e uma de S), quando a combinação satisfazer a condição de junção. Na junção, apenas as combinações de tuplas que satisfazem a condição de junção aparecerão no resultado. Vínculos de tabelas O conceito de junção de tabelas foi incorporado à SQL para especificar uma tabela que fosse resultado da junção das tabelas na cláusula FROM. Exemplo: Recupere o nome e endereço de todos os empregados que trabalham no departamento ‘Pesquisa’: Vínculos de tabelas Resposta 1: O mecanismo de vinculação das duas relações é a afiliação do funcionário e um departamento armazenada na tabela Empregado (pelo atributo FK). Condição de junção junto com a condição de seleção. SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.deptoNome = 'Pesquisa' AND D.codDepto = E.CodDepto; Vínculos de tabelas O SGBD é instruído a usar o valor da coluna “CodDepto” na relação Empregado para procurar o nome do departamento associado na relação departamento. A condição de junção é realizada na cláusula Where (D. codDepto = E. CodDepto). Vínculos de tabelas CPF Nome End CodDepto 123 José Rua 3 1 223 Maria Rua 10 2 323 João Rua 10 2 123 Alberto Rua 15 3 121 Carla Rua 20 1 Codigo Nome 1 Pesquisa 2 Vendas 3 Administrativo Empregado Departamento SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.CodDepto = E.CodDepto AND D.deptoNome = ‘Pesquisa’ Vínculos de tabelas CPF Nome End CodDepto 123 José Rua 3 1 223 Maria Rua 10 2 323 João Rua 10 2 123 Alberto Rua 15 3 121 Carla Rua 20 1 Codigo Nome 1 Pesquisa 2 Vendas 3 Administrativo Empregado Departamento SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.CodDepto = E.CodDepto AND D.deptoNome = ‘Pesquisa’ Vínculos de tabelas CPF Nome End CodDepto 123 José Rua 3 1 223 Maria Rua 10 2 323 João Rua 10 2 125 Alberto Rua 15 3 121 Carla Rua 20 1 Codigo Nome 1 Pesquisa 2 Vendas 3 Administrativo Empregado Departamento FK para a tabela Departamento SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.CodDepto = E.CodDepto AND D.deptoNome = ‘Pesquisa’ Vínculos de tabelas CPF Nome End CodDepto 123 José Rua 3 1 223 Maria Rua 10 2 323 João Rua 10 2 125 Alberto Rua 15 3 121 Carla Rua 20 1 Empregado Codigo Nome 1 Pesquisa 2 Vendas 3 Administrativo Departamento SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.CodDepto = E.CodDepto AND D.deptoNome = ‘Pesquisa’ Vínculos de tabelas CPF Nome End CodDepto 123 José Rua 3 1 223 Maria Rua 10 2 323 João Rua 10 2 125 Alberto Rua 15 3 121 Carla Rua 20 1 Empregado Codigo Nome 1 Pesquisa 2 Vendas 3 Administrativo Departamento Nome End José Rua 3 Carla Rua 20 Conj. Resultado SELECT E.NomEmp, E.EndEmp FROM Empregado E, Departamento D WHERE D.CodDepto = E.CodDepto AND D.deptoNome = ‘Pesquisa’ JOIN Resposta 2: Condição de seleção separada da condição de junção. A cláusula FROM contém uma única tabela juntada (joined table). SELECT E.NomEmp, E.EndEmp FROM Empregado E JOIN Departamento D ON E.CodDepto = D.CodDepto WHERE D.DeptoNome = 'Pesquisa'; JOIN Execute as duas consultas e veja o resultado: select E.MatEmp as 'Matrícula Empregado', E.NomEmp as 'Nome Empregado', D.NomDep as 'Nome Dependente' from Empregado E, Dependente D WHERE D.MatEmp = E.MatEmp select E.MatEmp as 'Matrícula Empregado', E.NomEmp as 'Nome Empregado', D.NomDep as 'Nome Dependente' from Empregado E Join Dependente D ON D.MatEmp = E.MatEmp Tips de JOINS (conteúdo extra) Tipos de JOINS O tipo de junção padrão entre duas tabelas é a interseção entre as linhas das duas tabelas: Execute a consulta e veja o resultado Vamos conhecer todos os tipos de Joins entre múltiplas tabelas SELECT * FROM Empregado E JOIN Departamento D ON E.CodDepto = D.CodDepto Tipos de JOINS • Inner Join • Tipo de junção que retorna somente as linhas que satisfazem a condição de junção entre ambas as tabelas (se omitir o a palavra INNER, por default o resultado será o mesmo) SELECT * FROM Empregado E INNER JOIN Departamento D ON E.CodDepto = D.CodDepto Tipos de JOINS • Outer Join • Left Outer Join: retorna todas as linhas da tabela da esquerda e somente as linhas da tabela da direta que satisfazem a condição de junção (preenche como NULL campos das linhas da tabela da esquerda que não satisfazem a condição de junção) SELECT * FROM Empregado E LEFT OUTER JOIN Departamento D ON E.CodDepto = D.CodDepto Tipos de JOINS • Outer Join • Right Outer Join: retorna todas as linhas da tabela da direita e somente as linhas da tabela da esquerda que satisfazem a condição de junção (preenche como NULL campos das linhas da tabela da direita que não satisfazem a condição de junção) SELECT * FROM Empregado E RIGHT OUTER JOIN Departamento D ON E.CodDepto = D.CodDepto Tipos de JOINS • Outer Join • Full Outer Join: retorna todas as linhas de ambas as tabelas (preenche como NULL campos das linhas que não satisfazem a condição de junção) SELECT * FROM Empregado E FULL OUTER JOIN Departamento D ON E.CodDepto = D.CodDepto Tipos de JOINS • Cross Join • Retorna o produto cartesiano de todas as tabelas participantes da junção. Não requer a condição de junção. A quantidade de linhas retornadas é 𝑛 × 𝑚, sendo 𝑛 e 𝑚 a quantidade de linhas das tabelas da esquerda e da direita, respectivamente. SELECT * FROM Empregado E CROSS JOIN Departamento D Tipos de JOINS • Self Join • Usado para a junção (Inner, Outer ou Cross) de uma tabela com ela mesma, quando a tabela possui um campo de chave estrangeira referenciando sua própria chave primária. Porém, não existe uma cláusula “self” no SQL. SELECT * FROM Empregado E INNER JOIN Empregado Emp ON E.CodSupervisor = Emp.MatEmp Observe que está sendo feito um join com a própria tabela Junção (Join) em mais de duas Tabelas É possível fazer junção entre várias tabelas: retorne o nome dos empregados que trabalham em algum projeto e o nome de todos os projetos em que eles trabalham SELECT E.NomEmp, P.ProjNome FROM Empregado E INNER JOIN Trabalha_Em T ON E.MatEmp = T.MatEmp INNER JOIN Projeto P ON T.CodProjeto = P.CodProjeto Dicas! Links para saber mais: Microsoft T-SQL https://docs.microsoft.com/pt-br/sql/t-sql/lesson-1-creating-database- objects?view=sql-server-ver15 Oracle PL/SQL https://www.oracle.com/br/database/technologies/appdev/plsql.html https://docs.microsoft.com/pt-br/sql/t-sql/lesson-1-creating-database-objects?view=sql-server-ver15
Compartilhar