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

Neste tutorial, você executa uma operação de ETL: extrair, transformar e carregar dados. Você vai selecionar um arquivo de dados CSV bruto, importá-lo para um cluster do Azure HDInsight, transformá-lo com o Apache Hive e carregá-lo no Banco de Dados SQL do Azure com o Apache Sqoop.

Neste tutorial, você aprenderá como:

• extrair e carregar os dados em um cluster do HDInsight.
• transformar os dados usando o Apache Hive.
• Carregar os dados no Banco de Dados SQL do Azure usando o Sqoop.

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

Pré-requisitos

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

  Consulte Usar o Azure Data Lake Storage Gen2 com clusters de HDInsight do Azure.

• Um cluster Hadoop baseado em Linux no HDInsight

  Confira Início Rápido: Introdução ao Apache Hadoop e ao Apache Hive no Azure HDInsight usando o portal do Azure.

• Banco de Dados SQL do Azure

  use 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, confira 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, confira Instalar a CLI do Azure.

• Um cliente SSH (Secure Shell)

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

Baixar, extrair e carregar os dados

Nesta seção, você vai baixar dados de voo de exemplo. Depois, você vai carregar esses dados no cluster do HDInsight e copiá-los na sua conta do Data Lake Storage Gen2.

1. Baixe o arquivo On_Time_Reporting_Carrier_On_Time_Performance_1987_present_2016_1.zip. Ele tem os dados de voo.

2. Abra um prompt de comando e use o seguinte comando Secure Copy (Scp) para carregar o arquivo .zip para o nó de cabeçalho 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 do HDInsight.
   • Substitua o espaço reservado <cluster-name> pelo nome do cluster HDInsight.

   Se você usa uma senha para autenticar o nome de usuário SSH, a senha será solicitada.

   Se você tiver usado uma chave pública, talvez precise usar o parâmetro -i e especificar a chave privada correspondente. Por exemplo, scp -i ~/.ssh/id_rsa <file_name>.zip <user-name>@<cluster-name>-ssh.azurehdinsight.net:.

3. Após o upload ser concluído, conecte-se ao cluster usando SSH. Insira o seguinte comando no prompt de comando:

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

4. Use o comando a seguir para descompactar o arquivo .zip:

   unzip <file-name>.zip
   

   O comando extrai um arquivo .csv.

5. Use o seguinte comando para criar o contêiner do Data Lake Storage Gen2.

   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 deseje fornecer ao contêiner.

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

6. Use o seguinte comando 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 em torno do nome do arquivo se ele 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, importe os dados do arquivo .csv para uma tabela do Apache Hive chamada delays.

1. No prompt de SSH já existente para o cluster HDInsight, use o seguinte comando para criar e editar um novo arquivo denominado flightdelays.hql:

   nano flightdelays.hql
   

2. Modifique o seguinte texto substituindo os espaços reservados <container-name> e <storage-account-name> pelo nome do contêiner e da conta de armazenamento. Depois, copie o texto e cole-o no console nano pressionando a tecla SHIFT e clicando com o botão direito do mouse ao mesmo tempo.

     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 depois Y, quando solicitado.

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

5. Após o término da execução do script flightdelays.hql, use o seguinte comando para abrir uma sessão interativa de Beeline:

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

6. Quando você receber o prompt do jdbc:hive2://localhost:10001/>, use a consulta a seguir para recuperar dados usando os dados importados de voos atrasados:

   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;
   

   Essa consulta recupera uma lista de cidades em que houve atrasos causados pelo clima, além do tempo médio de atrasos e a salva em abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output. Posteriormente, o Sqoop lê os dados desse local e os exporta para o Banco de Dados SQL do Azure.

7. Para sair do Beeline, digite !quit no prompt.

Criar uma tabela do Banco de Dados SQL

Você precisará do nome do servidor do Banco de Dados SQL para essa operação. Conclua estas etapas para localizar o nome do servidor.

1. Vá para o Portal do Azure.

2. Selecione Bancos de dados SQL.

3. Filtre pelo nome do banco de dados que você escolher usar. O nome do servidor está listado na coluna Nome do servidor.

4. Filtre pelo nome do banco de dados que você deseja usar. O nome do servidor está listado na coluna Nome do servidor.

   

   Há várias maneiras de se conectar ao Banco de Dados SQL e criar uma tabela. As seguintes etapas usam FreeTDS do cluster HDInsight.

5. Para instalar FreeTDS, utilize o seguinte comando de uma conexão SSH para o cluster:

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

6. Após a conclusão da instalação, use o comando a seguir 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 do servidor SQL lógico.

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

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

   Quando solicitado, insira a senha do nome de usuário administrador do Banco de Dados SQL.

   Você receberá saídas semelhantes 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. No prompt 1>, 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 a instrução GO for inserida, as instruções anteriores serão avaliadas.

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

9. Use a consulta a seguir para verificar se a tabela foi criada:

   SELECT * FROM information_schema.tables
   GO
   

   A saída é semelhante ao texto a seguir:

   TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE
   databaseName       dbo             delays        BASE TABLE
   

10. Enter exit at the 1>.

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ê usará o Sqoop para exportar os dados de abfs://<container-name>@<storage-account-name>.dfs.core.windows.net/tutorials/flightdelays/output para a tabela criada no Banco de Dados SQL do Azure.

1. Use o comando a seguir para verificar se o Sqoop pode ver seu Banco 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 em que você criou a tabela delays.

2. Use o seguinte comando para exportar os 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 conecta-se ao banco de dados que contém a tabela delays e exporta os dados do diretório /tutorials/flightdelays/output para a tabela delays.

3. Depois que o comando sqoop for concluído, use o utilitário tsql para conectar-se 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 delays:

   SELECT * FROM delays
   GO
   

   Você deve ver uma listagem dos dados na tabela. A tabela inclui o nome da cidade e o tempo de atraso de voo médio dessa cidade.

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

Limpar os recursos

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

Próximas etapas

Para saber mais maneiras de trabalhar usando dados no HDInsight, confira o artigo a seguir:

Usar Gen2 de armazenamento do Azure Data Lake com clusters de HDInsight do Azure