Tutorial: Extrair, transformar e carregar dados com Interactive Query no Azure HDInsight

Neste tutorial, vai transferir um ficheiro de dados CSV não processado de dados de voo publicamente disponíveis. Importe-o para o armazenamento de clusters do HDInsight e, em seguida, transforme os dados com Interactive Query no Azure HDInsight. Assim que os dados forem transformados, carregará esses dados para uma base de dados na Base de Dados SQL do Azure com o Apache Sqoop.

Este tutorial abrange as seguintes tarefas:

• Transferir os dados de voos de exemplo
• Carregar os dados para um cluster do HDInsight
• Transformar os dados com Interactive Query
• Criar uma tabela numa base de dados na Base de Dados SQL do Azure
• Utilizar o Sqoop para exportar dados para uma base de dados na Base de Dados SQL do Azure

Pré-requisitos

• Um cluster Interactive Query no HDInsight. Veja Criar clusters do Apache Hadoop com o portal do Azure e selecione Interactive Query para Tipo de cluster.

• Uma base de dados na Base de Dados SQL do Azure. Utiliza a base de dados como um arquivo de dados de destino. Se não tiver uma base de dados na Base de Dados SQL do Azure, consulte Criar uma base de dados na Base 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 voos

1. Navegue para Research and Innovative Technology Administration, Bureau of Transportation Statistics (Administração de Investigação e Inovação Tecnológica, Instituto de Estatísticas de Transportes).

2. Na página, desmarque todos os campos e, em seguida, selecione os seguintes valores:

   Name	Valor
   Filtrar Ano	2019
   Filtrar Período	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.

3. Selecione Transferir. É transferido um ficheiro .zip com os campos de dados que selecionou.

Carregar os dados para um cluster do HDInsight

Existem muitas formas de carregar dados para o armazenamento associado a um cluster do HDInsight. Nesta secção, vai utilizar scp para carregar os dados. Para ver outras formas de carregar dados, veja Upload data to HDInsight (Carregar dados para o HDInsight).

1. Carregue o ficheiro .zip para o nó principal do cluster do HDInsight. Edite o comando abaixo ao substituir FILENAME pelo nome do ficheiro .zip e CLUSTERNAME pelo nome do cluster do HDInsight. Em seguida, abra uma linha de comandos, defina o diretório de trabalho para a localização do ficheiro e, em seguida, introduza o comando:

   scp FILENAME.zip sshuser@CLUSTERNAME-ssh.azurehdinsight.net:FILENAME.zip
   

   Introduza sim ou não para continuar, se lhe for pedido. O texto não está visível na janela à medida que escreve.

2. Depois de concluído o carregamento, utilize SSH para ligar ao cluster. Edite o comando abaixo ao substituir CLUSTERNAME pelo nome do cluster do HDInsight. Em seguida, introduza o seguinte comando:

   ssh sshuser@CLUSTERNAME-ssh.azurehdinsight.net
   

3. Configure a variável de ambiente assim que uma ligação SSH tiver sido estabelecida. Substitua FILE_NAME, SQL_SERVERNAME, SQL_DATABASE, SQL_USERe SQL_PASWORD pelos valores adequados. Em seguida, introduza o comando:

   export FILENAME=FILE_NAME
   export SQLSERVERNAME=SQL_SERVERNAME
   export DATABASE=SQL_DATABASE
   export SQLUSER=SQL_USER
   export SQLPASWORD='SQL_PASWORD'
   

4. Deszipe o ficheiro .zip ao introduzir o comando abaixo:

   unzip $FILENAME.zip
   

5. Crie um diretório no armazenamento do HDInsight e, em seguida, copie o ficheiro .csv para o diretório ao introduzir o comando abaixo:

   hdfs dfs -mkdir -p /tutorials/flightdelays/data
   hdfs dfs -put $FILENAME.csv /tutorials/flightdelays/data/
   

Utilizar uma consulta do Hive para transformar os dados

Existem muitas formas de executar um trabalho do Hive num cluster do HDInsight. Nesta secção, vai utilizar o Beeline para executar uma tarefa do Hive. Para obter informações sobre outros métodos de execução de uma tarefa do Hive, veja Utilizar o Apache Hive no HDInsight.

Como parte do trabalho do Hive, importe os dados do ficheiro .csv para uma tabela do Hive com o nome Delays (Atrasos).

1. Na linha de comandos SSH que já tem para o cluster do HDInsight, utilize o seguinte comando para criar e edite um novo ficheiro com o nome flightdelays.hql:

   nano flightdelays.hql
   

2. 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;
   

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

4. Para iniciar o Hive e executar o ficheiro flightdelays.hql, utilize o seguinte comando:

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

5. Quando a execução do script flightdelays.hql for concluída, utilize o seguinte comando para abrir uma sessão interativa do Beeline:

   beeline -u 'jdbc:hive2://localhost:10001/;transportMode=http'
   

6. 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.

7. Para sair do Beeline, introduza !quit na linha de comandos.

Criar uma 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.

1. Para instalar o FreeTDS, utilize o seguinte comando da ligação SSH aberta ao cluster:

   sudo apt-get --assume-yes install freetds-dev freetds-bin
   

2. Após a conclusão da instalação, utilize o seguinte comando para ligar ao Base 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>
   

3. 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 introduza a declaração GO, as instruções anteriores são avaliadas. Esta instrução cria uma tabela denominada atrasos, com um índice agrupado.

   Utilize 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
   

4. Introduza exit na linha de comandos 1> para sair do utilitário tsql.

Exportar dados para Base de Dados SQL com o Apache Sqoop

Nas secções anteriores, copiou os dados transformados em /tutorials/flightdelays/output. Nesta secção, vai utilizar o Sqoop para exportar os dados para /tutorials/flightdelays/output a tabela que criou na Base de Dados SQL do Azure.

1. Verifique se o Sqoop consegue ver a sua base de dados SQL ao introduzir o comando abaixo:

   sqoop list-databases --connect jdbc:sqlserver://$SQLSERVERNAME.database.windows.net:1433 --username $SQLUSER --password $SQLPASWORD
   

   Este comando devolve uma lista de bases de dados, incluindo a base de dados na qual criou a delays tabela anteriormente.

2. Exporte dados de /tutorials/flightdelays/output para a delays tabela ao introduzir 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 liga-se à base de dados que contém a delays tabela e exporta dados do /tutorials/flightdelays/output diretório para a delays tabela.

3. Após a conclusão do comando sqoop, utilize o utilitário tsql para ligar à base de dados ao introduzir o comando abaixo:

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

   Utilize as instruções seguintes 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.

   Introduza exit para sair do utilitário tsql.

Limpar os recursos

Depois de concluir o tutorial, pode pretender eliminar o cluster. Com o HDInsight, os seus dados são armazenados no Armazenamento do Azure, para que possa eliminar um cluster em segurança quando não estiver a ser utilizado. Também lhe é cobrado um cluster do HDInsight, mesmo quando não está a ser utilizado. Uma vez que os custos do cluster são muitas vezes superiores aos custos de armazenamento, faz sentido económico eliminar clusters quando não estão a ser utilizados.

Para eliminar um cluster, veja Eliminar um cluster do HDInsight com o seu browser, o PowerShell ou a CLI do Azure.

Passos seguintes

Neste tutorial, pegou num ficheiro de dados CSV não processado, importou-o para um armazenamento de clusters do HDInsight e, em seguida, transformou os dados com Interactive Query no Azure HDInsight. Avance para o próximo tutorial para saber mais sobre o Conector do Armazém do Apache Hive.

Integrar o Apache Spark e o Apache Hive com o Conector do Armazém do Hive