AnalyticDB:Importar dados do OSS usando tabelas externas

Edições Enterprise, Basic e Data Lakehouse

1. Acesse o editor de SQL Development.

   1. Acesse o console do AnalyticDB for MySQL. No canto superior esquerdo do console, selecione uma região. No painel de navegação à esquerda, clique em Clusters. Encontre o cluster que deseja gerenciar e clique em ID do cluster.

   2. No painel de navegação à esquerda, escolha Job Development > SQL Development.

2. Importe os dados.

   É possível importar dados usando a importação regular (padrão) ou a importação elástica. No modo de importação regular, o sistema lê os dados source dos nós de computação e cria índices nos nós de armazenamento, consumindo recursos de computação e armazenamento. O método de importação elástica é compatível apenas com clusters das edições Enterprise Edition, Basic Edition e Data Lakehouse Edition que executam a versão de kernel 3.1.10.0 ou posterior e possuem um grupo de recursos de job. Para mais informações, consulte Data import methods.

   Importação regular

   1. Crie um banco de dados externo.

      CREATE EXTERNAL DATABASE adb_external_db;

   2. Crie uma tabela externa. Use a instrução CREATE EXTERNAL TABLE para criar uma tabela externa do OSS no banco de dados externo adb_external_db. Este tópico usa adb_external_db.person como exemplo.

      Nota

      A tabela externa do AnalyticDB for MySQL deve ter os mesmos nomes de campo, número de campos, ordem dos campos e tipos de dados do arquivo source do OSS.

      Criar uma tabela externa do OSS não particionada

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      Criar uma tabela externa do OSS particionada

      Antes de consultar uma tabela externa do OSS particionada, é necessário criá-la e adicionar as partições necessárias.

      1. Crie uma tabela externa do OSS particionada.

         CREATE EXTERNAL TABLE adb_external_db.person
         (
          id INT,
          name VARCHAR(1023) ,
          age INT
         )
         PARTITIONED BY (dt STRING)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
         STORED AS TEXTFILE
         LOCATION  'oss://testBucketName/adb/';

      2. Use a instrução ALTER TABLE ADD PARTITION para adicionar uma partição manualmente ou a instrução MSCK REPAIR TABLE para encontrar e adicionar partições automaticamente.

         ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';

         Nota

         • Para adicionar ou remover partições de uma tabela externa do OSS, consulte ALTER TABLE (external tables).

         • Para sincronizar automaticamente as partições de uma tabela externa do OSS, consulte MSCK REPAIR TABLE.

      Para mais informações sobre a sintaxe de criação de tabelas externas do OSS, consulte CREATE EXTERNAL TABLE.

   3. Consulte os dados.

      Depois de criar a tabela externa, execute uma instrução SELECT no AnalyticDB for MySQL para consultar dados do OSS.

      SELECT * FROM adb_external_db.person;

      O resultado retornado é o seguinte:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+

   4. Crie um banco de dados no AnalyticDB for MySQL. Se já existir um banco de dados, ignore esta etapa. Exemplo de instrução:

      CREATE DATABASE adb_demo; 

   5. Crie uma tabela no AnalyticDB for MySQL para armazenar os dados importados do OSS. Exemplo de instrução:

      Nota

      A tabela interna deve corresponder à tabela externa da etapa b em nomes de campo, número de campos, ordem dos campos e tipos de dados.

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   6. Importe os dados para a tabela.

      • Método 1: Use a instrução INSERT INTO para importar dados. Se um valor de chave primária for duplicado, o sistema ignora os novos dados. Isso equivale ao uso de INSERT IGNORE INTO. Para mais informações, consulte INSERT INTO.

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Método 2: Use a instrução INSERT OVERWRITE INTO para importar dados de forma síncrona. Isso sobrescreve os dados existentes na tabela.

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • Método 3: Use a instrução INSERT OVERWRITE INTO para importar dados de forma assíncrona. Para mais informações, consulte Asynchronous write.

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   Importação elástica

   1. Crie um banco de dados. Se já existir um banco de dados, ignore esta etapa. Exemplo de instrução:

      CREATE DATABASE adb_demo; 

   2. Crie uma tabela externa.

      Nota

      • A tabela externa do AnalyticDB for MySQL deve ter os mesmos nomes de campo, número de campos, ordem dos campos e tipos de dados do arquivo source do OSS.

      • A importação elástica é compatível apenas com a criação de tabelas externas por meio da instrução CREATE TABLE.

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://testBucketName/adb/dt=2023-06-15/person.csv",
          "accessid":"accesskey_id",
          "accesskey":"accesskey_secret",
          "delimiter":","
      }';

      Importante

      Ao criar uma tabela externa, os parâmetros TABLE_PROPERTIES compatíveis variam de acordo com o formato do arquivo (CSV, Parquet ou ORC):

      • Formato CSV: são compatíveis apenas os parâmetros endpoint, url, accessid, accesskey, format, delimiter, null_value, maxlinelength e partition_column.

      • Formato Parquet: são compatíveis apenas os parâmetros endpoint, url, accessid, accesskey, format, maxlinelength e partition_column.

      • Formato ORC: são compatíveis apenas os parâmetros endpoint, url, accessid, accesskey, format, maxlinelength e partition_column.

      Para mais informações sobre os parâmetros configuráveis para tabelas externas e suas descrições, consulte OSS non-partitioned external tables e OSS partitioned external tables.

   3. Consulte os dados.

      Depois de criar a tabela externa, execute uma instrução SELECT no AnalyticDB for MySQL para consultar dados do OSS.

      SELECT * FROM oss_import_test_external_table;

      O resultado retornado é o seguinte:

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)

   4. Crie uma tabela no AnalyticDB for MySQL para armazenar os dados importados do OSS. Exemplo de instrução:

      Nota

      A tabela interna deve corresponder à tabela externa da etapa b em nomes de campo, número de campos, ordem dos campos e tipos de dados.

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   5. Importe os dados.

      Importante

      A importação elástica permite importar dados apenas por meio da instrução INSERT OVERWRITE INTO.

      • Método 1: Execute a instrução INSERT OVERWRITE INTO para importar dados elasticamente, sobrescrevendo os dados existentes na tabela. Exemplo de instrução:

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

      • Método 2: Execute a instrução INSERT OVERWRITE INTO de forma assíncrona para importar dados elasticamente. Use a instrução SUBMIT JOB para enviar uma tarefa assíncrona para agendamento em segundo plano.

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

        Importante

        Não é possível definir uma fila de prioridade ao enviar uma tarefa de importação elástica de forma assíncrona.

        O resultado retornado é o seguinte:

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 202308151719510210170190**********    |

      Depois de enviar uma tarefa assíncrona usando SUBMIT JOB, o resultado indica apenas o envio bem-sucedido da tarefa. Use o ID do job para encerrar a tarefa assíncrona ou consultar o status para determinar se a execução foi concluída com sucesso. Para mais informações, consulte Submit an asynchronous import job.

      Parâmetros de hint:

      • elastic_load: define se a importação elástica será usada. Valores válidos: true e false. Valor padrão: false.

      • elastic_load_configs: os parâmetros de configuração do recurso de importação elástica. Coloque os parâmetros entre colchetes ([ ]) e separe múltiplos parâmetros com barras verticais (|). A tabela a seguir descreve os parâmetros.

        Parâmetro	Obrigatório	Descrição
        adb.load.resource.group.name	Sim	O nome do grupo de recursos de job que executa o job de importação elástica.
        adb.load.job.max.acu	Não	A quantidade máxima de recursos para um job de importação elástica. Unidade: unidades de computação do AnalyticDB (ACUs). Valor mínimo: 5 ACUs. Valor padrão: número de shards mais 1. Execute a seguinte instrução para consultar o número de shards no cluster: SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	Não	O tipo de recurso do driver do Spark. Valor padrão: small. Para informações sobre os valores válidos, consulte a coluna Type na tabela "Spark application configuration parameters" do tópico de parâmetros de configuração Conf.
        spark.executor.resourceSpec	Não	O tipo de recurso do executor do Spark. Valor padrão: large. Para informações sobre os valores válidos, consulte a coluna Type na tabela "Spark application configuration parameters" do tópico de parâmetros de configuração Conf.
        spark.adb.executorDiskSize	Não	A capacidade de disco do executor do Spark. Valores válidos: (0.100]. Unidade: GiB. Valor padrão: 10 GiB. Para mais informações, consulte a seção "Specify driver and executor resources" do tópico de parâmetros de configuração Conf.

   6. (Opcional) Verifique se a tarefa de importação enviada é uma tarefa de importação elástica.

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      O resultado retornado é o seguinte:

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 20230815171951021017019072*********** |       1          |
      +---------------------------------------+------------------+

      Se o valor de is_elastic_load for 1, trata-se de uma tarefa de importação elástica. Se o valor for 0, trata-se de uma importação regular.

Edição Data Warehouse

1. Connect to a cluster e crie um banco de dados.

   CREATE DATABASE adb_demo;

2. Crie uma tabela externa. Use a sintaxe CREATE TABLE para criar uma tabela externa do OSS nos formatos CSV, Parquet ou ORC. Para mais informações sobre a sintaxe, consulte OSS external table syntax.

   Este tópico usa uma tabela externa não particionada no formato CSV como exemplo.

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023) 
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/dt=2023-06-15/person.csv",
       "accessid":"accesskey_id",
       "accesskey":"accesskey_secret",
       "delimiter":",",
       "skip_header_line_count":0,
       "charset":"utf-8"
   }'; 

3. Consulte os dados da tabela externa oss_import_test_external_table.

   Nota

   Para arquivos de dados CSV, Parquet ou ORC, consultar uma tabela externa grande pode causar sobrecarga significativa de desempenho. Para melhorar a eficiência da consulta, recomendamos a importação dos dados da tabela externa do OSS para o AnalyticDB for MySQL antes de executar as consultas, conforme descrito nas etapas 4 e 5.

   SELECT * FROM oss_import_test_external_table;

4. Crie uma tabela no AnalyticDB for MySQL para armazenar os dados importados da tabela externa do OSS.

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
      id INT,
      name VARCHAR(1023),
      age INT,
      dt VARCHAR(1023) 
   )
   DISTRIBUTED BY HASH(id);

5. Execute uma instrução INSERT para importar dados da tabela externa do OSS para o AnalyticDB for MySQL.

   Importante

   Por padrão, as operações de importação de dados que usam INSERT INTO ou INSERT OVERWRITE SELECT executam de forma síncrona. Para grandes conjuntos de dados, como aqueles na casa das centenas de gigabytes, o cliente precisa manter uma conexão persistente com o servidor do AnalyticDB for MySQL por um longo período. Durante esse tempo, problemas de rede podem interromper a conexão e causar falhas na importação. Portanto, para grandes volumes de dados, recomendamos usar SUBMIT JOB INSERT OVERWRITE SELECT para realizar a importação de forma assíncrona.

   • Método 1: Execute a instrução INSERT INTO para importar dados. Se uma chave primária for duplicada, a operação de gravação atual é ignorada e os dados não são atualizados. Esse comportamento equivale a INSERT IGNORE INTO. Para mais informações, consulte INSERT INTO. Exemplo de instrução:

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Método 2: Execute a instrução INSERT OVERWRITE para importar dados, sobrescrevendo os dados existentes na tabela. Exemplo de instrução:

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Método 3: Execute a instrução INSERT OVERWRITE de forma assíncrona para importar dados. Use SUBMIT JOB para enviar uma tarefa assíncrona para agendamento em segundo plano. Adicione um hint (/*+ direct_batch_load=true*/) antes da tarefa de gravação para acelerá-la. Para mais informações, consulte Asynchronous write. Exemplo de instrução:

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     O resultado retornado é o seguinte:

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

     Para mais informações sobre como enviar uma tarefa assíncrona, consulte Submit an asynchronous import job.

Enterprise, Basic e Data Lakehouse

Para informações sobre a sintaxe de criação de tabelas externas do OSS nas edições Enterprise Edition, Basic Edition e Data Lakehouse Edition, consulte OSS external tables.

Data Warehouse

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, …])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"csv|orc|parquet|text
    "delimiter|field_delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

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

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

Mapeamento de tipos de dados entre arquivos Paimon e **AnalyticDB for MySQL**