PolarDB:Acesse dados do OSS usando tabelas externas do OSS

Pré-requisitos

Seu cluster PolarDB deve atender a um dos seguintes requisitos:

• A versão do mecanismo é MySQL 8.0.1 com revisão 8.0.1.1.25.4 ou posterior.

• A versão do mecanismo é MySQL 8.0.2 com revisão 8.0.2.2.1 ou posterior.

Para verificar a versão do mecanismo, consulte Consultar a versão do mecanismo.

Como funciona

Uma tabela externa do OSS permite armazenar dados formatados em CSV e pouco consultados (conhecidos como dados frios) no OSS, além de possibilitar sua consulta e análise.

Limitações

• Tabelas externas do OSS consultam dados apenas de arquivos CSV.

• Essas tabelas suportam somente as instruções CREATE, SELECT e DROP.

  Nota

  A operação DROP remove apenas os metadados da tabela no PolarDB, mas não exclui os arquivos de dados no OSS.

• Não há suporte para índices, particionamento ou transações em tabelas externas do OSS.

• Os tipos de dados compatíveis para informações em formato CSV incluem tipos numéricos, tipos de data e hora, tipos de string e valores NULL.

  Nota

  • Tipos de dados geoespaciais não são suportados.

  • Arquivos CSV compactados não podem ser consultados.

  • Valores NULL são aceitos apenas se uma das condições abaixo for atendida:

    • A versão do kernel é MySQL 8.0.1 e a revisão é 8.0.1.1.28 ou posterior.

    • A versão do kernel é MySQL 8.0.2 e a revisão é 8.0.2.2.5 ou posterior.

  Tipos numéricos

  Tipo	Tamanho	Intervalo com sinal	Intervalo sem sinal	Descrição
  TINYINT	1 byte	-128 a 127	0 a 255	Um inteiro muito pequeno.
  SMALLINT	2 bytes	-32768 a 32767	0 a 65535	Um inteiro pequeno.
  MEDIUMINT	3 bytes	-8388608 a 8388607	0 a 16777215	Um inteiro de tamanho médio.
  INT ou INTEGER	4 bytes	-2147483648 a 2147483647	0 a 4294967295	Um inteiro padrão.
  BIGINT	8 bytes	-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807	0 a 18.446.744.073.709.551.615	Um inteiro grande.
  FLOAT	4 bytes	-3,402823466E+38 a -1,175494351E-38; 0; 1,175494351E-38 a 3,402823466E+38	0; 1,175494351E-38 a 3,402823466E+38	Um número de ponto flutuante de precisão simples.
  DOUBLE	8 bytes	-1,7976931348623157E+308 a -2,2250738585072014E-308; 0; 2,2250738585072014E-308 a 1,7976931348623157E+308	0; 2,2250738585072014E-308 a 1,7976931348623157E+308	Um número de ponto flutuante de precisão dupla.
  DECIMAL	Para DECIMAL(M,D), o tamanho é M+2 bytes se M > D, ou D+2 bytes caso contrário.	Depende dos valores de M e D.	Depende dos valores de M e D.	Um número decimal.

  Tipos de data e hora

  Tipo	Tamanho	Intervalo	Formato	Descrição
  DATE	3 bytes	1000-01-01 a 9999-12-31	AAAA-MM-DD	Um valor de data.
  TIME	3 bytes	-838:59:59 a 838:59:59	HH:MM:SS	Um valor de hora ou duração.
  YEAR	1 byte	1901 a 2155	AAAA	Um valor de ano.
  DATETIME	8 bytes	1000-01-01 00:00:00 a 9999-12-31 23:59:59	AAAA-MM-DD HH:MM:SS	Um valor combinado de data e hora. Nota Para este tipo de dado, o mês e o dia devem ter dois dígitos. Por exemplo, especifique 2020-01-01 em vez de 2020-1-1. Caso contrário, a consulta falhará ao ser enviada para o OSS.
  TIMESTAMP	4 bytes	1970-01-01 00:00:00 a 2038-01-19 03:14:07	AAAA-MM-DD HH:MM:SS	Um carimbo de data/hora, que é um valor combinado de data e hora. Nota Para este tipo de dado, o mês e o dia devem ter dois dígitos. Por exemplo, especifique 2020-01-01 em vez de 2020-1-1. Caso contrário, a consulta falhará ao ser enviada para o OSS.

  Tipos de string

  Tipo	Tamanho	Descrição
  CHAR	0 a 255 bytes	Uma string de comprimento fixo.
  VARCHAR	0 a 65.535 bytes	Uma string de comprimento variável.
  TINYBLOB	0 a 255 bytes	Uma string binária com comprimento máximo de 255 caracteres.
  TINYTEXT	0 a 255 bytes	Uma string de texto curta.
  BLOB	0 a 65.535 bytes	Dados de texto longo em formato binário.
  TEXT	0 a 65.535 bytes	Dados de texto longo.
  MEDIUMBLOB	0 a 16.777.215 bytes	Dados de texto de comprimento médio em formato binário.
  MEDIUMTEXT	0 a 16.777.215 bytes	Dados de texto de comprimento médio.
  LONGBLOB	0 a 4.294.967.295 bytes	Dados de texto extra grandes em formato binário.
  LONGTEXT	0 a 4.294.967.295 bytes	Dados de texto extra grandes.

  Valores NULL

  Inserção

  • Insira um valor NULL em uma tabela externa do OSS.

    Para inserir um valor NULL em uma tabela externa do OSS, especifique o marcador de valor NULL correspondente, NULL_MARKER, ao criar a tabela. O NULL_MARKER de uma tabela externa do OSS tem como padrão NULL. Execute a instrução SHOW CREATE TABLE para visualizar o marcador de valor NULL:

    SHOW CREATE TABLE t1;

    O seguinte resultado é retornado:

    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                      |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | t1    | CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL
    ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci /*!99990 800020204 NULL_MARKER='NULL' */ CONNECTION='server_name' |
    +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

  • Insira um valor NULL em um arquivo CSV.

    Se você inserir NULL_MARKER em um campo de um arquivo CSV e NULL_MARKER não estiver entre aspas duplas, o PolarDB reconhecerá o valor como NULL.

    Nota

    • Caso adicione aspas duplas ao redor de NULL_MARKER, o PolarDB o reconhece como uma string. Consequentemente, não é possível usar a instrução is_null para encontrar valores NULL, e um erro será reportado se o parâmetro atribuído ao valor NULL no arquivo CSV não corresponder ao tipo de dado do parâmetro correspondente na tabela externa do OSS.

    • O NULL_MARKER não pode ser um valor numérico, uma string vazia ou conter qualquer um dos seguintes caracteres:

      ", \n, \r e ,

    Exemplo

    Use a seguinte instrução para criar uma tabela externa do OSS:

    CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `name` varchar(20) DEFAULT NULL,
      `time` timestamp NULL DEFAULT NULL
    ) ENGINE=CSV NULL_MARKER='NULL' CONNECTION='server_name';

    Suponha que o arquivo de dados contenha o seguinte conteúdo:

    1,"xiaohong","2022-01-01 00:00:00"
    NULL,"xiaoming","2022-02-01 00:00:00"
    3,NULL,"2022-03-01 00:00:00"
    4,"xiaowang",NULL

    A consulta à tabela externa do OSS retorna os seguintes dados:

    SELECT * FROM t1;
    +------+----------+---------------------+
    | id   | name     | time                |
    +------+----------+---------------------+
    |    1 | xiaohong | 2022-01-01 00:00:00 |
    | NULL | xiaoming | 2022-02-01 00:00:00 |
    |    3 | NULL     | 2022-03-01 00:00:00 |
    |    4 | xiaowang | NULL                |
    +------+----------+---------------------+

  Leitura

  • Ao ler dados de um arquivo CSV, se um valor no arquivo for NULL e a coluna correspondente na tabela externa do OSS aceitar NULL, a coluna será definida como NULL.

  • Se um valor NULL for lido de um arquivo CSV para uma coluna definida como NOT NULL, ocorrerá um conflito. O resultado depende do modo SQL.

    • Se o sql_mode estiver definido como STRICT_TRANS_TABLES, um erro será reportado.

    • Se o sql_mode estiver definido como um modo diferente de STRICT_TRANS_TABLES, a coluna assumirá seu valor padrão definido. Se nenhum valor padrão estiver definido, ela assumirá o padrão do MySQL para seu tipo de dado. Para mais informações, consulte Valores padrão de tipos de dados. Um aviso também é retornado. Use o comando SHOW WARNINGS; para visualizar os detalhes do aviso.

  Nota

  Use o comando SHOW VARIABLES LIKE "sql_mode"; para visualizar o modo SQL atual. Você também pode acessar o console do PolarDB e modifique o parâmetro sql_mode na página Settings and Management > Parameters. Para mais informações, consulte Modificar parâmetros.

  Exemplo

  Crie uma tabela externa do OSS chamada t onde a coluna id esteja definida como NOT NULL e não tenha valor padrão.

  CREATE TABLE `t` (
    `id` int(11) NOT NULL
  ) ENGINE=CSV 
  CONNECTION="server_name";

  Suponha que o arquivo CSV t.CSV contenha o seguinte conteúdo:

  NULL
  2

  Dois cenários podem ocorrer ao ler dados do arquivo CSV usando a tabela externa do OSS:

  • Se sql_mode estiver definido como STRICT_TRANS_TABLES, execute o seguinte comando para consultar dados do arquivo CSV:

    SELECT * FROM t;

    A seguinte mensagem de erro é reportada:

    ERROR 1364 (HY000): Field 'id' doesn't have a default value

  • Se sql_mode estiver definido como um modo diferente de STRICT_TRANS_TABLES, execute o seguinte comando para consultar dados do arquivo CSV:

    SELECT * FROM t;

    O seguinte resultado é retornado:

    +----+
    | id |
    +----+
    |  0 |
    |  2 |
    +----+
    2 rows in set, 1 warning (0.00 sec)

    No resultado, 0 é o valor padrão do MySQL. Execute o seguinte comando para visualizar a mensagem de aviso:

    SHOW WARNINGS;

    O seguinte resultado é retornado:

    +---------+------+-----------------------------------------+
    | Level   | Code | Message                                 |
    +---------+------+-----------------------------------------+
    | Warning | 1364 | Field 'id' doesn't have a default value |
    +---------+------+-----------------------------------------+
    1 row in set (0.00 sec)

Parâmetros

Visualize ou modifique os seguintes parâmetros na página Settings and Management > Parameters no console do PolarDB:

A memória total máxima para o recurso do OSS é calculada como: loose_csv_max_oss_threads * loose_csv_oss_buff_size.

Procedimento

CREATE SERVER <server_name> 
FOREIGN DATA WRAPPER oss OPTIONS 
(
  [DATABASE '<my_database_name>',]
  EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id": "<my_oss_access_key_id>","oss_access_key_secret": "<my_oss_access_key_secret>","oss_prefix":"<my_oss_prefix>","oss_sts_token":"<my_oss_sts_token>"}'
);

CREATE SERVER <server_name>
FOREIGN DATA WRAPPER oss OPTIONS
(
  [DATABASE '<my_database_name>',]
  EXTRA_SERVER_INFO '{"oss_endpoint": "<my_oss_endpoint>","oss_bucket": "<my_oss_bucket>","oss_access_key_id":"<my_oss_access_key_id>","oss_access_key_secret":"<my_oss_access_key_secret>"}'
);                  

CREATE TABLE <table_name> (create_definition,...) engine=csv connection="<connection_string>";

CREATE TABLE t1 (id int) engine=csv connection="server_name/a/b/c/d/t1";

CREATE TABLE t1 (id int) engine=csv connection="server_name/t2";

-- Count rows in the t1 table.
SELECT count(*) FROM t1;

-- Range query.
SELECT id FROM t1 WHERE id < 10 AND id > 1;

-- Point query.
SELECT id FROM t1 where id = 3;

-- Multi-table join.
SELECT id FROM t1 left join t2 on t1.id = t2.id WHERE t2.name like "%er%";

Otimização de consulta

OSS error: The current query does not support engine condition pushdown. You need to use NO_ECP() hint or set optimizer_switch = 'engine_condition_pushdown=OFF' to turn off the condition push down function.

Sincronizar informações do servidor OSS

O nó primário e os nós somente leitura em um cluster PolarDB compartilham um único servidor OSS, permitindo o acesso aos dados a partir de todos os nós. Essa sincronização sem bloqueios garante que as operações em cada nó permaneçam independentes.

Ao modificar as informações do servidor OSS, as alterações são sincronizadas com os nós somente leitura sem bloqueios. Se uma thread em um nó somente leitura mantiver um bloqueio no servidor OSS, essa sincronização poderá ser atrasada. Nesse caso, execute /*force_node='pi-bpxxxxxxxx'*/ flush privileges; ou /*force_node='pi-bpxxxxxxxx'*/flush table oss_foreign_table; para atualizar manualmente as informações do servidor OSS do nó somente leitura.