Tutorial: Extrair, transformar e carregar dados usando o Azure HDInsight

Neste tutorial, você executa uma operação ETL: extrair, transformar e carregar dados. Você pega um arquivo de dados CSV bruto, importa-o para um cluster do Azure HDInsight, transforma-o com o Apache Hive e carrega-o no Banco de Dados SQL do Azure com o Apache Sqoop.

Neste tutorial, irá aprender a:

• Extraia e carregue os dados para um cluster HDInsight.
• Transforme os dados usando o Apache Hive.
• Carregue os dados no Banco de Dados SQL do Azure usando o Sqoop.

Se não tiver uma subscrição do Azure, crie uma conta gratuita antes de começar.

Pré-requisitos

• Uma conta de armazenamento que tem um namespace hierárquico (Armazenamento do Azure Data Lake) configurado para o HDInsight

  Consulte Usar o Armazenamento do Azure Data Lake com clusters do Azure HDInsight.

• Um cluster Hadoop baseado em Linux no HDInsight

  Consulte Guia de início rápido: introdução ao Apache Hadoop e ao Apache Hive no Azure HDInsight usando o portal do Azure.

• Base de Dados SQL do Azure

  Você usa o Banco de Dados SQL do Azure como um armazenamento de dados de destino. Se você não tiver um banco de dados no Banco de Dados SQL, consulte Criar um banco de dados no Banco de Dados SQL do Azure no portal do Azure.

• CLI do Azure

  Se você ainda não instalou a CLI do Azure, consulte Instalar a CLI do Azure.

• Um cliente Secure Shell (SSH)

  Para obter mais informações, consulte Conectar-se ao HDInsight (Hadoop) usando SSH.

Transferir, extrair e, em seguida, carregar os dados

Nesta seção, você baixa dados de voo de exemplo. Em seguida, carregue esses dados para o cluster HDInsight e, em seguida, copie esses dados para a sua conta de Armazenamento Data Lake.

1. Transfira o ficheiro On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip . Este ficheiro contém os dados do voo.

2. Abra um prompt de comando e use o seguinte comando Secure Copy (Scp) para carregar o arquivo .zip no nó principal do cluster HDInsight:

   scp On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net:
   

   • Substitua o espaço reservado <ssh-user-name> pelo nome de usuário SSH do cluster HDInsight.
   • Substitua o espaço reservado <cluster-name> pelo nome do cluster HDInsight.

   Se utilizar uma palavra-passe para autenticar o seu nome de utilizador SSH, ser-lhe-á solicitada a palavra-passe.

   Se utilizar uma chave pública, poderá ter de utilizar o parâmetro -i e especificar o caminho para a chave privada correspondente. Por exemplo, scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Depois de concluído o carregamento, utilize SSH para ligar ao cluster. Na linha de comandos, introduza o seguinte comando:

   ssh <ssh-user-name>@<cluster-name>-ssh.azurehdinsight.net
   

4. Utilize o seguinte comando para descomprimir o ficheiro .zip:

   unzip <file-name>.zip
   

   O comando extrai um arquivo .csv .

5. Use o comando a seguir para criar o contêiner de Armazenamento Data Lake.

   hadoop fs -D "fs.azure.createRemoteFileSystemDuringInitialization=true" -ls abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/
   

   Substitua o espaço reservado <container-name> pelo nome que você deseja dar ao contêiner.

   Substitua o espaço reservado <storage-account-name> pelo nome da sua conta de armazenamento.

6. Use o comando a seguir para criar um diretório.

   hdfs dfs -mkdir -p abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data
   

7. Use o seguinte comando para copiar o arquivo .csv para o diretório:

   hdfs dfs -put "On_Time_Reporting_Carrier_On_Time_Performance_(1987_present)_2016_1.csv" abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/data/
   

   Use aspas ao redor do nome do arquivo se o nome do arquivo contiver espaços ou caracteres especiais.

Transformar os dados

Nesta seção, você usa o Beeline para executar um trabalho do Apache Hive.

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

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

   nano flightdelays.hql
   

2. Modifique o texto a seguir substituindo os espaços reservados e <storage-account-name> pelo nome da <container-name> conta de contêiner e armazenamento. Em seguida, copie e cole o texto no nano console pressionando a tecla SHIFT junto com o botão de seleção do mouse direito.

     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 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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
     LOCATION 'abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/processed'
     AS
     SELECT YEAR AS year,
        FL_DATE AS FlightDate, 
        substring(UNIQUE_CARRIER, 2, length(UNIQUE_CARRIER) -1) AS IATA_CODE_Reporting_Airline,
        substring(CARRIER, 2, length(CARRIER) -1) AS Reporting_Airline, 
        substring(FL_NUM, 2, length(FL_NUM) -1) AS Flight_Number_Reporting_Airline,
        ORIGIN_AIRPORT_ID AS OriginAirportID,
        substring(ORIGIN, 2, length(ORIGIN) -1) AS OriginAirportSeqID,
        substring(ORIGIN_CITY_NAME, 2) AS OriginCityName,
        substring(ORIGIN_STATE_ABR, 2, length(ORIGIN_STATE_ABR) -1)  AS OriginState,
        DEST_AIRPORT_ID AS DestAirportID,
        substring(DEST, 2, length(DEST) -1) AS DestAirportSeqID,
        substring(DEST_CITY_NAME,2) AS DestCityName,
        substring(DEST_STATE_ABR, 2, length(DEST_STATE_ABR) -1) AS DestState,
        DEP_DELAY_NEW AS DepDelay,
        ARR_DELAY_NEW AS ArrDelay,
        CARRIER_DELAY AS CarrierDelay,
        WEATHER_DELAY AS WeatherDelay,
        NAS_DELAY AS NASDelay,
        SECURITY_DELAY AS SecurityDelay,
        LATE_AIRCRAFT_DELAY AS LateAircraftDelay
     FROM delays_raw;
   

3. Salve o arquivo digitando CTRL+X e digitando Y quando solicitado.

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

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

5. Depois que o script terminar de flightdelays.hql ser executado, use o seguinte comando para abrir uma sessão Beeline interativa:

   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(OriginCityName, '''', ''),
     avg(WeatherDelay)
   FROM delays
   WHERE WeatherDelay IS NOT NULL
   GROUP BY OriginCityName;
   

   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 abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/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

Você precisa do nome do servidor do Banco de dados SQL para esta operação. Conclua estas etapas para encontrar o nome do servidor.

1. Aceda ao portal do Azure.

2. Selecione Bancos de dados SQL.

3. Filtre o nome do banco de dados que você escolher usar. O nome do servidor é mostrado na coluna Nome do servidor.

4. Filtre o nome do banco de dados que você deseja usar. O nome do servidor é mostrado na coluna Nome do servidor.

   

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

5. Para instalar o FreeTDS, utilize o seguinte comando a partir de uma ligação SSH ao cluster:

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

6. 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 '<server-name>.database.windows.net' -U '<admin-login>' -p 1433 -D '<database-name>'
   

   • Substitua o espaço reservado <server-name> pelo nome lógico do servidor SQL.

   • Substitua o espaço reservado <admin-login> pelo nome de usuário admin do Banco de dados SQL.

   • Substitua o espaço reservado <database-name> pelo nome do banco de dados

   Quando lhe for pedido, introduza a palavra-passe para o nome de utilizador de administrador da Base de Dados SQL.

   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 sqooptest
   1>
   

7. 1> No prompt, insira as seguintes instruções:

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

8. Quando for introduza a declaração GO, as instruções anteriores são avaliadas.

   A consulta cria uma tabela chamada delays, que tem um índice clusterizado.

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

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

Exportar e carregar os dados

Nas seções anteriores, você copiou os dados transformados no local abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Nesta seção, você usa o Sqoop para exportar os dados da tabela criada no Banco de Dados SQL do abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output Azure.

1. Utilize o seguinte comando para verificar se o Sqoop pode ver a sua base de dados SQL:

   sqoop list-databases --connect jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433 --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD>
   

   O comando retorna uma lista de bancos de dados, incluindo o banco de dados no qual você criou a tabela de atrasos.

2. Use o seguinte comando para exportar dados da tabela hivesampletable para a tabela delays :

   sqoop export --connect 'jdbc:sqlserver://<SERVER_NAME>.database.windows.net:1433;database=<DATABASE_NAME>' --username <ADMIN_LOGIN> --password <ADMIN_PASSWORD> --table 'delays' --export-dir 'abfs://<container-name>@.dfs.core.windows.net/tutorials/flightdelays/output' --fields-terminated-by '\t' -m 1
   

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

3. Depois que o sqoop comando terminar, use o utilitário tsql para se conectar ao banco de dados:

   TDSVER=8.0 tsql -H <SERVER_NAME>.database.windows.net -U <ADMIN_LOGIN> -P <ADMIN_PASSWORD> -p 1433 -D <DATABASE_NAME>
   

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

5. Entre exit para sair do utilitário tsql.

Clean up resources (Limpar recursos)

Todos os recursos usados neste tutorial são preexistentes. Nenhuma limpeza é necessária.

Próximos passos

Para saber mais formas de trabalhar com dados no HDInsight, consulte o seguinte artigo:

Usar o Armazenamento do Azure Data Lake com clusters do Azure HDInsight