#phpmyadmi | Explore Tumblr posts and blogs

awsexchage · 7 years ago

Photo

PySparkでMySQLからのデータ取得＆集計方法 https://ift.tt/2wdW1He

MySQLに対してSQLでよくやるようなデータの取得や集計などをPySparkのDataFrameだとどうやるのか調べてみましたので、備忘録として残しておきたいと思います。検証環境は以前紹介したDockerではじめるPySparkをベースにDockerで環境を構築しいます。こういった検証にDockerはすごく便利でいいですね

環境

PySpark 2.2

MySQL5.7

データはMySQLの公式でサンプルとして提供されているworldデータベースを利用します。

環境の構築

利用するDockerイ��ージ

cloudfish/pyspark-notebook（公式のpysapark-notebookにJDBCを同梱したイメージ）

kakakakakku/mysql-57-world-database（mysqlはworldデータベースを取り込んだイメージ。Redash を詳しく学べる「Redash ハンズオン資料」を作ったのイメージを利用させていただいています。）

phpmyadmin/phpmyadmin（データ確認用として公式のphpmyadminのイメージ）

以下の通りdocker-compose.ymlを作成します。

version: '2' services: pyspark: image: cloudfish/pyspark-notebook volumes: - LOCAL_PATH:/home/jovyan/work ports: - "8888:8888" command: bash -c "start-notebook.sh --NotebookApp.token=''" links: - dbserver environment: GRANT_SUDO: "yes" dbserver: image: kakakakakku/mysql-57-world-database environment: MYSQL_ALLOW_EMPTY_PASSWORD: "yes" phpmyadmi: image: phpmyadmin/phpmyadmin ports: - "18080:80" links: - "dbserver" environment: PMA_HOST: dbserver PMA_USER: root PMA_PASSWORD: ""

Docker起動

docker-compose up

Jupyter Notebook画面確認 http://localhost:8888

phpmyadmin画面確認 http://localhost:18080

PySparkの実行確認

早速サンプルデータベースで実行確認を進めていきます。worldデータベースは以下のようなテーブルが含まれています。これらのテーブルを使ってデータを取得してみたいと思います。

Tables_in_world city country countrylanguage

画面右端のNewボタンをクリックしPython3を選択し、開いた画面で以下を入力していきます。

以下のコードはコードセルごとに入力してください。入力後Shift + Enterでコードが実行されます。

Sparkの初期化処理

from pyspark.sql import SQLContext, Row from pyspark import SparkContext sc = SparkContext("local", "First App")

※2回実行するとエラーになります。

JDBC接続処理

JDBCに接続しています。

sqlContext = SQLContext(sc) jdbc_url="jdbc:mysql://dbserver/mysql" driver_class="com.mysql.jdbc.Driver" DB_USER="root" DB_PASSWORD="" def load_dataframe(table): df=sqlContext.read.format("jdbc").options( url =jdbc_url, driver=driver_class, dbtable=table, user=DB_USER, password=DB_PASSWORD ).load() return df

データ取得処理

データをDataFrameに取得しますテーブル指定でデータ取得する場合（countryテーブル、cityテーブルを取得）

df_country = load_dataframe("world.country") # 実行されるSQL：SELECT * FROM world.country WHERE 1=0 df_city = load_dataframe("world.city") # 実行されるSQL：SELECT * FROM world.city WHERE 1=0

SQLを指定してデータ取得する場合（cityテーブルの国コードがJPNのものだけを取得）

df_city_japan = load_dataframe("(select * from world.city where CountryCode='JPN') city_japan") # 実行されるSQL：SELECT * FROM (select * from world.city where CountryCode='JPN') city_japan WHERE 1=0

MySQL側でどのようなSQLが流れるのか見てみたところ、テーブルにセットした内容がFROM句の後にセットされるようです。

カラム指定

Nameカラムを表示

df_country.select("Name").show()

条件検索

国名がJapanのデータを抽出。

df_country.filter(df_country["Name"] == "Japan").show() df_country.where(df_country["Name"] == "Japan").select("Code","GNP").show()

isNull

独立年がNullのデータを抽出

df_country.where(df_country["IndepYear"].isNull()).show()

国名がJで始まるデータを抽出

df_country.where(df_country["Name"].like("J%")).show()

Case When式

人口が100000人より大きい場合は「Big」、小さい場合は「Small」を表示

from pyspark.sql import functions as F df_country.select(df_country["Name"], F.when(df_country["Population"] > 100000,"Big").otherwise("Small").alias("CountryDiv")).show()

substr

国名を3文字切り出して表示

df_country.select(df_country["Name"].substr(1,3)).show()

limit

5件のみ抽出

df_country.limit(5).show()

Join

countryテーブルとcityテーブルを結合し国名がJapanのものを抽出

df_join = df_country.alias('country').join(df_city.alias('city'),(df_city["countryCode"] == df_country["Code"]) & (df_country["Name"]=="Japan")).show()

OrderBy

GNPが高い国順に表示

df_country.orderBy("GNP" , ascending=False).select("Code","Name","GNP").show()

GroupBy

国ごとにグループ化し、cityの数、人口の平均と合計を集計

from pyspark.sql import functions as F df_city.groupBy("countryCode") \ .agg( \ F.count(df_city["Name"]).alias("total_count"), \ F.avg(df_city["Population"]).alias("avg_population"), \ F.sum(df_city["Population"]).alias("sum_population") \ ).show()

まとめ

書き方は少し慣れる必要がありますが、かなりSQLに近いイメージでデータ取得が可能なことが分かりました。今回はDBに対して実行しましたが、ファイルに対しても同様に実行可能です。

元記事はこちら

「PySparkでMySQLからのデータ取得＆集計方法」

August 27, 2018 at 02:00PM

#AWS #Official #Blog

0 notes

manutencaosuporteainformati-blog · 8 years ago

Text

No 3º Semestre com o professor Luis aprendemos sobre o Banco de Dados.

O que é Banco de Dados?

Banco- local para guardar coisas importantes;

Dados- informações;

Dados gera Informação

‘Dados:João’, ‘Porto Velho’, ‘Masculino’, 32

Informação: João é um homem adulto que vive em Porto Velho

Dentro dos Bancos de Dados, os dados são armazenados em formato de tabelas.

Um Banco de Dados pode conter uma ou mais tabelas

Para criar Banco de Dados e tabelas para inserir, alterar, visualizar e excluir dados, é recomendado utilizar a linguagem SQL.

Existem comandos em linguagem SQL para acessar e manipular Banco de Dados.

Quais são os principais comandos de banco de dados?

SELECT = Mostrar dados

INSERT = Inserir dados

UPDATE = Alterar dados

DELETE = Excluir dados

Como instalar o software phpMyAdmin?

1. Acessar o sistema AVA (http://virtual.ifro.edu.br/guajara/index.php?)

2. Abrir a disciplina Banco de Dados

3. Fazer download do arquivo Instalador_do_xampp-7.1.4.exe

4. Abrir a pasta de Downloads

5. Executar o arquivo Instalador_do_xampp-7.1.4.exe

6. Se aparecer algum erro, clique em OK e continue .

• Como utilizar o software phpMyAdmin?

1. Execute o XAMPP Control Panel como administrador

2. Clique em start do módulo Apache

3. Clique em start do módulo MySQL

4. Aguarde até que dois módulos estejam iniciados

5. Abra o navegador de Internet

6. Acesse o link http://localhost/phpmyadmin/ para testar se a instalação deu certo.

• Como abrir o phpMyAdmin?

1. Abra o menu Iniciar

2. Execute o XAMPP como administrador

3. Inicie o Apache e o MySQL

4. Abrir o navegador WEB

5. Acessar o link http://localhost/phpmyadmi

• Comando SQL para criar um Banco de Dados

CREATE DATABASE nomeDoBanco;

• Comando SQL para criar uma tabela

CREATE TABLE nomeDaTabela (colunas);

• Comando SQL para criar uma tabela

CREATE TABLE tabelaSimples

( nome varchar(100),

cidade varchar(50),

sexo varchar(9),

idade int

);

Comando SQL para inserir dados no Banco de Dados

INSERT INTO nomeDaTabela (colunas) VALUES (valores);

• Comando SQL para inserir dados

INSERT INTO tabelaSimples (nome, cidade, sexo, idade) VALUES ('João' , 'Porto Velho', 'Masculino' , 32);

• Comando SQL para excluir dados do Banco de Dados

DELETE FROM nomeDaTabela WHERE condição;

• Comando SQL para inserir dados

DELETE FROM tabelaSimples WHERE nome = 'João';

• Comando SQL para alterar dados do Banco de Dados

UPDATE nomeDaTabela

SET nomeDoCampo = valorDoCampo WHERE condição;

• Comando SQL para mostrar dados do Banco de Dados

SELECT Coluna1, Coluna2 FROM nomeDaTabela;

• Comando SQL para mostrar dados do Banco de Dados

SELECT * FROM nomeDaTabela;

• Comando SQL para mostrar dados do Banco de Dados

Exemplo:

SELECT Nome, Idade FROM pessoas WHERE Cidade = 'Guajará-Mirim'; •

O comando irá mostrar o Nome e a Idade de todas as pessoas onde o valor do campo Cidade for igual a Guajará-Mirim

• Métodos de comparação

Condições

= Igual a

<> Diferente de

> Maior que

>= Maior ou igual a

< Menor que

<= Menor ou igual a

• Principais tipos de dados que podem ser armazenados

Tipos de dados

INT Número inteiro

VARCHAR Texto

BOOLEAN Verdadeiro ou Falso

REAL Número real

DATE Data

TIME Hora

Principais restrições das colunas são:

Restrições

NOT NULL Não pode ser nulo

UNIQUE Não pode ter valor repetido

CHECK Verificações

DEFAULT Valor padrão

PRIMARY KEY Chave primária

• Comando SQL para contar a quantidade de linhas

SELECT COUNT (coluna) FROM nomeDaTabela WHERE condição;

• Comando SQL para calcular a média de valores numéricos

SELECT AVG(coluna) FROM nomeDaTabela WHERE condição;

• Comando SQL para calcular a soma de valores numéricos

SELECT SUM(coluna) FROM nomeDaTabela WHERE condição;

• Comando SQL para ordenar os resultados do SELECT

SELECT colunas FROM nomeDaTabela

WHERE condição ORDER BY coluna ASC/DESC;

• Comando SQL para combinar os SELECT em duas tabelas

SELECT colunas1 FROM nomeDaTabela1 WHERE condição

UNION

SELECT colunas2 FROM nomeDaTabela2 WHERE condição;

• Comando SQL para realizar buscas

SELECT colunas FROM nomeDaTabela

WHERE coluna LIKE ’padrão‘;

• Os caracteres curingas são: % substitui qualquer texto

_ substitui um caractere

Exemplo: para realizar uma busca por todos os nomes que comecem com as letras “Pe”, o padrão é “Pe%”

Exemplo 2: para realizar uma busca pelos nomes “Marcelo dos Santos” ou “Marcela dos Santos”, o padrão é “Marcel_ dos Santos”

• Comando SQL para excluir um Banco de Dados

DROP DATABASE nomeDoBanco;

• Comando SQL para excluir uma tabela

DROP TABLE nomeDaTabela;

. PRIMARY KEY

Primary key ou Chave Primária é uma restrição que garante que nenhuma linha seja igual a outra na mesma tabela. Toda tabela que possui uma chave primária nunca terá linhas repetidas. A chave primária é única e não pode ser nula, ou seja, é a combinação das restrições UNIQUE e NOT NULL.

Exemplos de chaves primárias:

Matrícula

CPF

Nº de série

IMEI

E-mail

Nº de celular

Para definir uma coluna como Chave Primária é preciso adicionar a restrição PRIMARY KEY depois do tipo da coluna.

Por exemplo:

CREATE TABLE pessoas

(

CPF VARCHAR(14),

nome VARCHAR(90),

dataNascimento DATE,

PRIMARY KEY (CPF)

);

• Comando SQL para alterar uma tabela

Adicionar uma coluna

ALTER TABLE nomeTabela ADD nomeDaNovaColuna tipo;

Remover uma coluna:

ALTER TABLE nomeTabela DROP nomeDaColuna;

Alterar o nome e/ou tipo de uma coluna:

ALTER TABLE nomeTabela CHANGE nomeDaColuna novoNome tipo;

0 notes