AnalyticDB:Importar dados do OSS usando tabelas externas

Enterprise, basic, and data lakehouse editions

1. Acesse o editor SQL Development.

   1. Faça login no console do AnalyticDB for MySQL. No canto superior esquerdo do console, selecione uma região. No painel de navegação à esquerda, clique em Clusters. Localize o cluster que deseja gerenciar e clique no 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 de origem nos nós de computação e cria índices nos nós de armazenamento, consumindo recursos de computação e armazenamento. A importação elástica é suportada apenas em clusters Enterprise Edition, Basic Edition e Data Lakehouse Edition com versão de kernel 3.1.10.0 ou posterior que possuam um grupo de recursos do tipo job. Para mais informações, consulte Data import methods.

   Regular import

   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 campos, quantidade de campos, ordem dos campos e tipos de dados do arquivo de origem no OSS.

      Create a non-partitioned OSS external table

      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/';

      Create a partitioned OSS external table

      Antes de consultar uma tabela externa particionada do OSS, crie-a e adicione as partições necessárias.

      1. Crie uma tabela externa particionada do OSS.

         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 use a instrução MSCK REPAIR TABLE para localizar 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.

      Após 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 é:

      +------+-------+------+-----------+
      | 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 campos, quantidade 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. Se houver duplicidade de chave primária, os novos dados serão ignorados. Isso equivale a INSERT IGNORE INTO. Para mais informações, consulte INSERT INTO. Exemplo:

        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. Essa operação substitui os dados existentes na tabela. Exemplo:

        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. Exemplo:

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

   Elastic import

   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 campos, quantidade de campos, ordem dos campos e tipos de dados do arquivo de origem no OSS.

      • A importação elástica suporta apenas a criação de tabelas externas com a 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 suportados variam conforme o formato do arquivo (CSV, Parquet ou ORC):

      • Formato CSV: Apenas os parâmetros endpoint, url, accessid, accesskey, format, delimiter, null_value, maxlinelength e partition_column são suportados.

      • Formato Parquet: Somente os parâmetros endpoint, url, accessid, accesskey, format, maxlinelength e partition_column são aceitos.

      • Formato ORC: Apenas os parâmetros endpoint, url, accessid, accesskey, format, maxlinelength e partition_column têm suporte.

      Para mais detalhes sobre os parâmetros configuráveis em 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 é:

      +------+-------+------+-----------+
      | 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 campos, quantidade 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 suporta a importação de dados apenas por meio da instrução INSERT OVERWRITE INTO.

      • Método 1: Execute a instrução INSERT OVERWRITE INTO para importar dados elasticamente, substituindo 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 assincronamente a instrução INSERT OVERWRITE INTO para importar dados de forma elástica. Use a instrução SUBMIT JOB para enviar uma tarefa assíncrona que será agendada 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 é:

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

      Após enviar uma tarefa assíncrona usando SUBMIT JOB, o resultado retornado indica apenas que a tarefa foi enviada com sucesso. Use o ID do job para encerrar a tarefa assíncrona ou consultar seu status e verificar se a execução foi concluída corretamente. 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: parâmetros de configuração do recurso de importação elástica. Os parâmetros devem ser colocados entre colchetes ([ ]) e separados por barras verticais (|). A tabela a seguir descreve esses parâmetros.

        Parameter	Required	Description
        adb.load.resource.group.name	Yes	Nome do grupo de recursos de job que executa a tarefa de importação elástica.
        adb.load.job.max.acu	No	Quantidade máxima de recursos para uma tarefa de importação elástica. Unidade: AnalyticDB compute units (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	No	Tipo de recurso do driver 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	No	Tipo de recurso do executor 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	No	Capacidade de disco do executor 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 é:

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

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

Data warehouse edition

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 como exemplo uma tabela externa não particionada no formato CSV.

   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 dados na 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 importar os dados da tabela externa do OSS para o AnalyticDB for MySQL antes de executar 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 são executadas 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 AnalyticDB for MySQL por um longo período. Durante esse tempo, problemas de rede podem interromper a conexão e causar falha na importação. Portanto, para grandes volumes de dados, recomendamos o uso de 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 houver duplicidade de chave primária, a operação de gravação atual será ignorada e os dados não serã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, substituindo 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 assincronamente a instrução INSERT OVERWRITE 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 acelerar o processo. 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 é:

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

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

Enterprise, Basic, and Data Lakehouse

Para informações sobre a sintaxe de criação de tabelas externas do OSS na 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**