Tutorial: Extrair, transformar e carregar dados usando a Consulta Interativa no Azure HDInsight

Neste tutorial, você baixa um arquivo de dados CSV brutos de dados de voo disponíveis publicamente. Importe-o para o armazenamento de cluster do HDInsight e transforme os dados usando a Consulta Interativa no Azure HDInsight. Depois que os dados são transformados, você carrega esses dados em um banco de dados no Banco de Dados SQL do Azure usando o Apache Sqoop.

Este tutorial abrange as seguintes tarefas:

Faça o download dos dados de voo de exemplo
Carregar dados para um cluster HDInsight
Transformar os dados usando a Consulta Interativa
Criar uma tabela em um banco de dados no Banco de Dados SQL do Azure
Usar o Sqoop para exportar dados para um banco de dados no Banco de Dados SQL do Azure

Pré-requisitos

Um cluster de Consulta Interativa no HDInsight. Consulte Criar clusters Apache Hadoop usando o portal do Azure e selecione Consulta interativa para tipo de cluster.
Um banco de dados no Banco de Dados SQL do Azure. Use o banco de dados como um armazenamento de dados de destino. Se você não tiver um banco de dados no Banco de Dados SQL do Azure, consulte Criar um banco de dados no Banco de Dados SQL do Azure no portal do Azure.
Um cliente SSH. Para obter mais informações, veja Ligar ao HDInsight (Apache Hadoop) através de SSH.

Transferir os dados de voo

Navegue até Administração de Pesquisa e Tecnologia Inovadora, Bureau of Transportation Statistics.

Na página, desmarque todos os campos e selecione os seguintes valores:

Nome	Valor
Ano do filtro	2019
Período de Filtro	Janeiro
Campos	`Year, FlightDate, Reporting_Airline, DOT_ID_Reporting_Airline, Flight_Number_Reporting_Airline, OriginAirportID, Origin, OriginCityName, OriginState, DestAirportID, Dest, DestCityName, DestState, DepDelayMinutes, ArrDelay, ArrDelayMinutes, CarrierDelay, WeatherDelay, NASDelay, SecurityDelay, LateAircraftDelay`.

Selecione Download. Um arquivo .zip é baixado com os campos de dados selecionados.

Carregar dados para um cluster HDInsight

Há muitas maneiras de carregar dados para o armazenamento associado a um cluster HDInsight. Nesta seção, você usa scp para carregar dados. Para saber mais sobre outras formas de carregar dados, consulte Carregar dados para o HDInsight.

Carregue o arquivo .zip para o nó principal do cluster HDInsight. Edite o comando abaixo substituindo FILENAME pelo nome do arquivo .zip e CLUSTERNAME pelo nome do cluster HDInsight. Em seguida, abra um prompt de comando, defina o diretório de trabalho para o local do arquivo e digite o comando:
```
scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
```
Digite sim ou não para continuar, se solicitado. O texto não fica visível na janela enquanto você digita.
Depois de concluído o carregamento, utilize SSH para ligar ao cluster. Edite o comando abaixo substituindo CLUSTERNAME pelo nome do cluster HDInsight. Em seguida, introduza o seguinte comando:
```
ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
```
Configure a variável de ambiente assim que uma conexão SSH for estabelecida. Substitua FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USER, e SQL_PASWORD pelos valores apropriados. Em seguida, digite o comando:
```
export FILENAME=FILE_NAME
export SQLSERVERNAME=SQL_SERVERNAME
export DATABASE=SQL_DATABASE
export SQLUSER=SQL_USER
export SQLPASWORD='SQL_PASWORD'
```
Descompacte o arquivo .zip digitando o comando abaixo:
```
unzip $FILENAME.zip
```
Crie um diretório no armazenamento do HDInsight e copie o arquivo .csv para o diretório digitando o comando abaixo:
```
hdfs dfs -mkdir -p /tutorials/flightdelays/data
hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
```

Transformar dados usando uma consulta do Hive

Há muitas maneiras de executar uma tarefa do Hive num cluster HDInsight. Nesta secção, tu usas o Beeline para executar uma tarefa do Hive. Para obter informações sobre outros métodos de execução de um trabalho do Hive, consulte Usar o Apache Hive no HDInsight.

Como parte do trabalho do Hive, você importa os dados do arquivo .csv para uma tabela do Hive chamada Delays.

No prompt SSH que você já tem para o cluster HDInsight, use o seguinte comando para criar e editar um novo arquivo chamado flightdelays.hql:
```
nano flightdelays.hql
```

Utilize o seguinte texto como o conteúdo desse ficheiro:

DROP TABLE delays_raw;
-- Creates an external table over the csv file
CREATE EXTERNAL TABLE delays_raw (
    YEAR string,
    FL_DATE string,
    UNIQUE_CARRIER string,
    CARRIER string,
    FL_NUM string,
    ORIGIN_AIRPORT_ID string,
    ORIGIN string,
    ORIGIN_CITY_NAME string,
    ORIGIN_CITY_NAME_TEMP string,
    ORIGIN_STATE_ABR string,
    DEST_AIRPORT_ID string,
    DEST string,
    DEST_CITY_NAME string,
    DEST_CITY_NAME_TEMP string,
    DEST_STATE_ABR string,
    DEP_DELAY_NEW float,
    ARR_DELAY_NEW float,
    CARRIER_DELAY float,
    WEATHER_DELAY float,
    NAS_DELAY float,
    SECURITY_DELAY float,
    LATE_AIRCRAFT_DELAY float)
-- The following lines describe the format and location of the file
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/tutorials/flightdelays/data';

-- Drop the delays table if it exists
DROP TABLE delays;
-- Create the delays table and populate it with data
-- pulled in from the CSV file (via the external table defined previously)
CREATE TABLE delays AS
SELECT YEAR AS year,
    FL_DATE AS flight_date,
    substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS unique_carrier,
    substring(CARRIER, 2, length(CARRIER) -1) AS carrier,
    substring(FL_NUM, 2, length(FL_NUM) -1) AS flight_num,
    ORIGIN_AIRPORT_ID AS origin_airport_id,
    substring(ORIGIN, 2, length(ORIGIN) -1) AS origin_airport_code,
    substring(ORIGIN_CITY_NAME, 2) AS origin_city_name,
    substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS origin_state_abr,
    DEST_AIRPORT_ID AS dest_airport_id,
    substring(DEST, 2, length(DEST) -1) AS dest_airport_code,
    substring(DEST_CITY_NAME,2) AS dest_city_name,
    substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS dest_state_abr,
    DEP_DELAY_NEW AS dep_delay_new,
    ARR_DELAY_NEW AS arr_delay_new,
    CARRIER_DELAY AS carrier_delay,
    WEATHER_DELAY AS weather_delay,
    NAS_DELAY AS nas_delay,
    SECURITY_DELAY AS security_delay,
    LATE_AIRCRAFT_DELAY AS late_aircraft_delay
FROM delays_raw;

Para guardar o ficheiro, prima Ctrl + X, depois y e, em seguida, introduza.

Para iniciar o Hive e executar o arquivo flightdelays.hql , use o seguinte comando:

beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http' -f flightdelays.hql

Depois que o script flightdelays.hql terminar de ser executado, use o seguinte comando para abrir uma sessão Beeline interativa:
```
beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
```
Quando receber a linha de comandos jdbc:hive2://localhost:10001/>, utilize a seguinte consulta para obter dados a partir dos dados importados de atrasos de voos:
```
INSERT OVERWRITE DIRECTORY '/tutorials/flightdelays/output'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
SELECT regexp_replace(origin_city_name, '''', ''),
    avg(weather_delay)
FROM delays
WHERE weather_delay IS NOT NULL
GROUP BY origin_city_name;
```
Esta consulta obtém uma lista de cidades em que os voos se atrasaram devido às condições atmosféricas, juntamente com o tempo de atraso médio e guarda-a em /tutorials/flightdelays/output. Depois, o Sqoop lê os dados a partir dessa localização e exporta-os para a Base de Dados SQL do Azure.
Para sair do Beeline, introduza !quit na linha de comandos.

Criar uma tabela de base de dados SQL

Existem muitas formas de ligar à Base de Dados SQL e criar uma tabela. Os passos seguintes utilizam FreeTDS do cluster do HDInsight.

Para instalar o FreeTDS, use o seguinte comando da conexão SSH aberta para o cluster:
```
sudo apt-get --assume-yes install freetds-dev freetds-bin
```

Após a conclusão da instalação, use o seguinte comando para se conectar ao Banco de dados SQL:

TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD

Receberá um resultado semelhante ao seguinte texto:

locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Default database being set to <yourdatabase>
1>

Na linha de comandos 1>, introduza as seguintes linhas:

CREATE TABLE [dbo].[delays](
[origin_city_name] [nvarchar](50) NOT NULL,
[weather_delay] float,
CONSTRAINT [PK_delays] PRIMARY KEY CLUSTERED
([origin_city_name] ASC))
GO

Quando for introduzida a declaração GO, as instruções anteriores são avaliadas. Esta instrução cria uma tabela chamada delays, com um índice clusterizado.

Use a seguinte consulta para verificar se a tabela foi criada:

SELECT * FROM information_schema.tables
GO

O resultado é semelhante ao seguinte texto:

TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
databaseName       dbo             delays        BASE TABLE

Introduza exit no prompt 1> para sair do utilitário tsql.

Exportar dados para o Banco de dados SQL usando o Apache Sqoop

Nas seções anteriores, você copiou os dados transformados em /tutorials/flightdelays/output. Nesta secção, você usa o Sqoop para exportar os dados de /tutorials/flightdelays/output para a tabela que você criou no Banco de Dados SQL do Azure.

Verifique se o Sqoop pode ver seu banco de dados SQL digitando o comando abaixo:
```
sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
```
Este comando retorna uma lista de bancos de dados, incluindo o banco de dados no qual você criou a delays tabela anteriormente.

Exporte dados da /tutorials/flightdelays/output para a tabela delays introduzindo o comando abaixo:

sqoop export --connect "jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433;database=$DATABASE" --username $SQLUSER --password $SQLPASWORD --table 'delays' --export-dir '/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1

O Sqoop se conecta ao banco de dados que contém a delays tabela e exporta dados do /tutorials/flightdelays/output diretório para a delays tabela.

Depois que o comando sqoop terminar, use o utilitário tsql para se conectar ao banco de dados inserindo o comando abaixo:
```
TDSVER=8.0 tsql -H $SQLSERVERNAME.database.windows.net -U $SQLUSER -p 1433 -D $DATABASE -P $SQLPASWORD
```
Use as instruções a seguir para verificar se os dados foram exportados para a tabela de atrasos:
```
SELECT * FROM delays
GO
```
Deverá ver uma lista dos dados na tabela. A tabela inclui o nome da cidade e o tempo médio dos atrasos dos voos.

Digite exit para sair do utilitário tsql.

Limpar recursos

Depois de concluir o tutorial, pode pretender eliminar o cluster. Com o HDInsight, seus dados são armazenados no Armazenamento do Azure, para que você possa excluir com segurança um cluster quando ele não estiver em uso. Você também é cobrado por um cluster HDInsight, mesmo quando ele não está em uso. Como as cobranças para o cluster são muitas vezes mais do que as taxas para armazenamento, faz sentido econômico excluir clusters quando eles não estão em uso.

Para excluir um cluster, consulte Excluir um cluster HDInsight usando seu navegador, PowerShell ou a CLI do Azure.

Próximos passos

Neste tutorial, você pegou um arquivo de dados CSV bruto, importou-o para um armazenamento de cluster HDInsight e transformou os dados usando a Consulta Interativa no Azure HDInsight. Avance para o próximo tutorial para saber mais sobre o Apache Hive Warehouse Connector.

Integre o Apache Spark e o Apache Hive com o Hive Warehouse Connector

Feedback

Esta página foi útil?

Last updated on 2025-04-03