AnalyticDB:Use external tables to import HDFS data

Pré-requisitos

• O cluster do AnalyticDB for MySQL deve executar a versão 3.1.4 ou posterior do kernel.

  Nota

  Para visualizar e atualizar a versão secundária, acesse a seção Configuration Information na página Cluster Information no console do AnalyticDB for MySQL.

• O arquivo de dados do HDFS está nos formatos CSV, Parquet ou ORC.

• Um cluster HDFS foi criado e os dados a serem importados estão preparados em uma pasta do HDFS. Este tópico usa a pasta hdfs_import_test_data.csv como exemplo.

• As seguintes portas de acesso ao service foram configuradas no cluster HDFS para o cluster AnalyticDB for MySQL:

  • namenode: Lê e grava metadados do sistema de arquivos. Configure o número da porta no parâmetro fs.defaultFS. A porta padrão é 8020.

    Para obter mais informações sobre a configuração, consulte core-default.xml.

  • datanode: Lê e grava dados. Configure o número da porta no parâmetro dfs.datanode.address. A porta padrão é 50010.

    Para obter mais informações sobre a configuração, consulte hdfs-default.xml.

• O AnalyticDB for MySQL Data Warehouse Edition no modo elástico oferece suporte ao acesso via Elastic Network Interface (ENI).

  Importante

  • Faça login no console do AnalyticDB for MySQL. Na página Cluster Information, na seção Network Information, ative a opção Elastic Network Interface (ENI).

  • Ativar ou desativar a rede ENI interrompe as conexões com o banco de dados por cerca de 2 minutos. Durante esse período, as operações de leitura e gravação ficam indisponíveis. Avalie o impacto antes de ativar ou desativar a rede ENI.

Procedimento

1. Crie um banco de dados de destino. Neste exemplo, o banco de dados de destino no cluster AnalyticDB for MySQL chama-se adb_demo.

   CREATE DATABASE IF NOT EXISTS adb_demo;    

2. No banco de dados de destino adb_demo, use a instrução CREATE TABLE para criar uma tabela externa nos formatos CSV, Parquet ou ORC.

   • Criar uma tabela externa HDFS.

   • Criar uma tabela externa HDFS particionada.

3. Crie uma tabela de destino.

   Use uma das instruções a seguir para criar uma tabela de destino no banco de dados adb_demo e armazenar os dados importados do HDFS:

   • Crie uma tabela de destino para uma tabela externa padrão. Neste exemplo, a tabela de destino chama-se adb_hdfs_import_test. A sintaxe é a seguinte:

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_test
     (
         uid string,
         other string
     )
     DISTRIBUTED BY HASH(uid);

   • Ao criar uma tabela de destino para uma tabela externa particionada, defina tanto as colunas padrão, como uid e other, quanto as colunas de chave de partição, como p1, p2 e p3. Neste exemplo, a tabela de destino chama-se adb_hdfs_import_parquet_partition. A sintaxe é a seguinte:

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition
     (
         uid string,
         other string,
         p1 date,
         p2 int,
         p3 varchar
     )
     DISTRIBUTED BY HASH(uid);

4. Importe os dados do HDFS para o cluster AnalyticDB for MySQL de destino.

   Selecione um método de importação conforme necessário. A sintaxe para importar dados em uma tabela particionada é a mesma usada para uma tabela padrão. Os exemplos a seguir usam uma tabela padrão:

   • Método 1 (Recomendado): Use INSERT OVERWRITE para importar dados. Este método oferece suporte à importação em lote e proporciona alto desempenho. Os dados tornam-se visíveis após uma importação bem-sucedida. Se a importação falhar, os dados serão revertidos. Exemplo:

     INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Método 2: Use INSERT INTO para importar dados. Os dados inseridos ficam disponíveis para consultas em tempo real. Use este método para pequenos volumes de dados. Exemplo:

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • Método 3: Execute uma tarefa assíncrona para importar dados. Exemplo:

     SUBMIT JOB INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

     O seguinte resultado é retornado:

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |
     +---------------------------------------+

     Também é possível verificar o status da tarefa assíncrona com base no job_id. Para obter mais informações, consulte Enviar uma tarefa de importação assincronamente.

Próximos passos

SELECT * FROM adb_hdfs_import_test LIMIT 100;

Criar uma tabela externa HDFS

• Criar uma tabela externa para um arquivo CSV

  A instrução é a seguinte:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"csv",
      "delimiter":",",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv"
  }';

  Parâmetro	Obrigatório	Descrição
  ENGINE='HDFS'	Obrigatório	O mecanismo de armazenamento da tabela externa. Este exemplo usa o HDFS.
  TABLE_PROPERTIES		O método que o AnalyticDB for MySQL usa para acessar os dados do HDFS.
  format		O formato do arquivo de dados. Para criar uma tabela externa para um arquivo CSV, defina este parâmetro como csv.
  delimiter		O delimitador de coluna do arquivo de dados CSV. Este exemplo usa uma vírgula (,).
  hdfs_url		O endereço absoluto do arquivo ou pasta de dados de destino no cluster HDFS. O endereço deve começar com hdfs://. Exemplo: hdfs://172.17..:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv
  partition_column	Opcional	As colunas de chave de partição da tabela externa. Separe várias colunas com vírgulas (,). Para obter informações sobre como definir colunas de chave de partição, consulte Criar uma tabela externa HDFS particionada.
  compress_type		O tipo de compactação do arquivo de dados. Arquivos CSV oferecem suporte apenas ao tipo de compactação Gzip.
  skip_header_line_count		O número de linhas de cabeçalho a serem ignoradas no início do arquivo durante a importação de dados. A primeira linha de um arquivo CSV é o cabeçalho da tabela. Se você definir este parâmetro como 1, a primeira linha será ignorada automaticamente durante a importação. O valor padrão é 0, o que significa que nenhuma linha é ignorada.
  hdfs_ha_host_port		Se o recurso de Alta Disponibilidade (HA) estiver configurado para o cluster HDFS, configure o parâmetro hdfs_ha_host_port ao criar uma tabela externa. O formato é ip1:port1,ip2:port2. Os endereços IP e portas referem-se às instâncias primária e secundária do namenode. Exemplo: 192.168.xx.xx:8020,192.168.xx.xx:8021

• Criar uma tabela externa para um arquivo Parquet ou ORC

  A instrução a seguir mostra como criar uma tabela externa para um arquivo Parquet:

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"parquet",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_parquet_data/"
  }';

  Parâmetro	Obrigatório	Descrição
  ENGINE='HDFS'	Obrigatório	O mecanismo de armazenamento da tabela externa. Este exemplo usa o HDFS.
  TABLE_PROPERTIES		O método que o AnalyticDB for MySQL usa para acessar os dados do HDFS.
  format		O formato do arquivo de dados. Para criar uma tabela externa para um arquivo Parquet, defina este parâmetro como parquet. Para criar uma tabela externa para um arquivo ORC, defina este parâmetro como orc.
  hdfs_url		O endereço absoluto do arquivo ou pasta de dados de destino no cluster HDFS. O endereço deve começar com hdfs://.
  partition_column	Opcional	As colunas de chave de partição da tabela. Separe várias colunas com vírgulas (,). Para obter informações sobre como definir colunas de chave de partição, consulte Criar uma tabela externa HDFS particionada.
  hdfs_ha_host_port		Se o recurso HA estiver configurado para o cluster HDFS, configure o parâmetro hdfs_ha_host_port ao criar uma tabela externa. O formato é ip1:port1,ip2:port2. Os endereços IP e portas referem-se às instâncias primária e secundária do namenode. Exemplo: 192.168.xx.xx:8020,192.168.xx.xx:8021

  Nota

  • Os nomes das colunas na instrução CREATE TABLE da tabela externa devem ser idênticos aos nomes das colunas no arquivo Parquet ou ORC, mas não diferenciam maiúsculas de minúsculas. A ordem das colunas também deve ser a mesma.

  • Ao criar uma tabela externa, selecione apenas algumas colunas do arquivo Parquet ou ORC para serem colunas na tabela externa. As colunas não selecionadas não são importadas.

  • Se a instrução CREATE TABLE da tabela externa incluir uma coluna que não existe no arquivo Parquet ou ORC, as consultas para essa coluna retornarão NULL.

  Mapeamento de tipos de dados entre arquivos Parquet e AnalyticDB for MySQL

  Tipo de dados primitivo do Parquet	logicalType do Parquet	Tipo de dados no AnalyticDB for MySQL
  BOOLEAN	Nenhum	BOOLEAN
  INT32	INT_8	TINYINT
  INT32	INT_16	SMALLINT
  INT32	Nenhum	INT ou INTEGER
  INT64	Nenhum	BIGINT
  FLOAT	Nenhum	FLOAT
  DOUBLE	Nenhum	DOUBLE
  FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
  BINARY	UTF-8	VARCHAR STRING JSON (se a coluna Parquet estiver no formato JSON)
  INT32	DATE	DATE
  INT64	TIMESTAMP_MILLIS	TIMESTAMP ou DATETIME
  INT96	Nenhum	TIMESTAMP ou DATETIME

  Importante

  Tabelas externas para arquivos Parquet não oferecem suporte ao tipo STRUCT. Se você usar esse tipo, a criação da tabela falhará.

  Mapeamento de tipos de dados entre arquivos ORC e AnalyticDB for MySQL

  Tipo de dados em arquivos ORC	Tipo de dados no AnalyticDB for MySQL
  BOOLEAN	BOOLEAN
  BYTE	TINYINT
  SHORT	SMALLINT
  INT	INT ou INTEGER
  LONG	BIGINT
  DECIMAL	DECIMAL
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  BINARY STRING VARCHAR	VARCHAR STRING JSON (se a coluna ORC estiver no formato JSON)
  TIMESTAMP	TIMESTAMP ou DATETIME
  DATE	DATE

  Importante

  Tabelas externas para arquivos ORC não oferecem suporte a tipos complexos como LIST, STRUCT ou UNION. Se você usar esses tipos, a criação da tabela falhará. É possível criar uma tabela externa para um arquivo ORC se uma coluna usar o tipo MAP, mas as consultas nessa tabela falharão.

Criar uma tabela externa HDFS particionada

parquet_partition_classic/
├── p1=2020-01-01
│   ├── p2=4
│   │   ├── p3=SHANGHAI
│   │   │   ├── 000000_0
│   │   │   └── 000000_1
│   │   └── p3=SHENZHEN
│   │       └── 000000_0
│   └── p2=6
│       └── p3=SHENZHEN
│           └── 000000_0
├── p1=2020-01-02
│   └── p2=8
│       ├── p3=SHANGHAI
│       │   └── 000000_0
│       └── p3=SHENZHEN
│           └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE IF NOT EXISTS hdfs_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
  "hdfs_url":"hdfs://172.17.***.**:9000/adb/parquet_partition_classic/",
  "format":"parquet",  //To create an external table for a CSV or ORC file, change the value of format to csv or orc.
  "partition_column":"p1, p2, p3"  //For partitioned HDFS data, if you want to query data by partition, you must specify the partition_column parameter in the CREATE EXTERNAL TABLE statement when you import data to AnalyticDB for MySQL.
}';

Criar tabelas externas de armazenamento em nuvem

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:Get*",
         "s3:List*",
         "s3:Describe*",
         "s3-object-lambda:Get*",
         "s3-object-lambda:List*"
       ],
       "Resource": "*"
     }
   ]
}

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:",
         "s3-object-lambda:"
       ],
       "Resource": "*"
     }
   ]
}

CREATE TABLE t2(c1 int, c2 int, p1 int)
ENGINE='hdfs'
TABLE_PROPERTIES='{
  "partition_column":"p1",
  "format" : "parquet",
  "hdfs_url" : "gs://adbtest2/tbls/table1",
  "gcs.project_id":"test-project",
  "gcs.client_email":"adbtest@test-project.iam.gserviceaccount.com",
  "gcs.token_uri":"https://oauth2.googleapis.cn/token",
  "gcs.private_key_id":"xxxx",
  "gcs.private_key":"-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFA****-----END PRIVATE KEY-----\n"
}'