MaxCompute:FAQ about built-in functions

Como converter uma data como 2010/1/3 para 2010-01-03?

Para entradas com preenchimento de zeros (por exemplo, 2010/01/03), combine TO_DATE e TO_CHAR:

SELECT TO_CHAR(TO_DATE('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd');
-- Returns: 2010-01-03

As funções integradas não analisam diretamente entradas sem preenchimento de zeros (por exemplo, 2010/1/3). Crie uma função definida pelo usuário (UDF) para processar partes de data com comprimento variável. Este tópico descreve os tipos, cenários, processo de desenvolvimento e notas de uso das UDFs compatíveis com o MaxCompute.

Para obter mais informações, consulte TO_DATE e TO_CHAR.

Como converter um timestamp UNIX para um valor DATETIME?

Use FROM_UNIXTIME:

SELECT FROM_UNIXTIME(1609459200);
-- Returns: 2021-01-01 08:00:00

Para obter mais informações, consulte FROM_UNIXTIME.

Como obter a hora atual do sistema?

Use GETDATE:

SELECT GETDATE();
-- Returns the current date and time, e.g., 2026-02-15 10:30:00

Para obter mais informações, consulte GETDATE.

Por que recebo um erro "cannot be resolved" com YEAR, QUARTER, MONTH ou DAY?

FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function or view 'year' cannot be resolved

As funções YEAR, QUARTER, MONTH e DAY são extensões introduzidas no MaxCompute V2.0 e exigem a edição de tipos de dados V2.0.

Adicione a seguinte configuração antes da instrução SQL:

SET odps.sql.type.system.odps2 = true;
SELECT YEAR(GETDATE());

Por que o TO_DATE falha com um erro "missing minute part"?

FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn't have minute part : yyyy-MM-dd HH:mm:ss

No MaxCompute, tanto mm quanto MM representam o mês. Use mi para minutos:

-- Incorrect
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss');

-- Correct
SELECT TO_DATE('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss');

Isso difere de muitas outras plataformas SQL, nas quais mm representa minutos. No MaxCompute, use sempre mi para o componente de minuto.

Por que ROUND(4.515, 2) retorna 4,51 em vez de 4,52?

Valores DOUBLE são números de ponto flutuante de 8 bytes com precisão limitada. O valor 4.515 é armazenado internamente como aproximadamente 4.514999999.... Portanto, o arredondamento para duas casas decimais produz 4.51:

SELECT ROUND(4.515, 2), ROUND(125.315, 2);
-- Returns: 4.51, 125.32

Para arredondamento exato, use o tipo DECIMAL:

SELECT ROUND(4.515BD, 2);
-- Returns: 4.52

O tipo DECIMAL armazena valores com precisão exata e evita problemas de representação de ponto flutuante.

Como gerar uma sequência de autoincremento?

Use ROW_NUMBER como função de janela:

SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_num, *
FROM my_table;

Para obter mais informações, consulte ROW_NUMBER.

Como concatenar valores em uma coluna?

Use WM_CONCAT:

SELECT WM_CONCAT(',', name) AS all_names
FROM my_table;
-- Returns: Alice,Bob,Charlie

Para obter mais informações, consulte WM_CONCAT.

O MaxCompute oferece suporte a MD5?

Sim. MD5 calcula o hash de uma string:

SELECT MD5('hello');
-- Returns: 5d41402abc4b2a76b9719d911017c592

Para obter mais informações, consulte MD5.

Como preencher uma string com zeros à esquerda?

Use LPAD:

SELECT LPAD('42', 6, '0');
-- Returns: 000042

Para obter mais informações, consulte LPAD.

O MaxCompute oferece suporte a SUBSTRING_INDEX?

Sim. SUBSTRING_INDEX funciona da mesma forma que no MySQL:

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);
-- Returns: www.example

Para obter mais informações, consulte SUBSTRING_INDEX.

O REGEXP_COUNT aceita consultas aninhadas no parâmetro pattern?

Não. O parâmetro pattern de REGEXP_COUNT não aceita instruções de consulta aninhadas.

Para obter mais informações, consulte REGEXP_COUNT.

O MaxCompute oferece suporte à formatação de números TO_CHAR do Oracle?

O MaxCompute não aceita a sintaxe TO_CHAR(Data, FM9999.00) do Oracle. Use FORMAT_NUMBER como alternativa:

SELECT FORMAT_NUMBER(12332.123456, '#,###,###,###.###');
-- Returns: 12,332.123

Para obter mais informações, consulte FORMAT_NUMBER.

Como agregar campos JSON que correspondem a uma condição?

Filtre registros com condições SQL como LIKE e use ARRAY ou MAP para construir um tipo complexo a partir dos resultados. Em seguida, converta o resultado para uma string JSON com TO_JSON:

SELECT TO_JSON(ARRAY(col1, col2))
FROM my_table
WHERE col1 LIKE '%keyword%';

Como extrair chaves JSON como colunas separadas?

Use GET_JSON_OBJECT:

SELECT
  GET_JSON_OBJECT(json_col, '$.name') AS name,
  GET_JSON_OBJECT(json_col, '$.age') AS age
FROM my_table;

Para obter mais informações, consulte GET_JSON_OBJECT.

Como converter uma string JSON em um array?

Use FROM_JSON com o tipo de destino como string:

SELECT FROM_JSON(json_col, 'array<bigint>');
-- Converts a JSON array string like "[1, 2, 3]" to a MaxCompute ARRAY<BIGINT>

Para obter mais informações, consulte FROM_JSON.

O MaxCompute não oferece suporte a IFNULL. O que usar no lugar?

O MaxCompute não possui a função IFNULL. Se você tentar usá-la, receberá:

Semantic analysis exception - Invalid function : line 1:41 'ifnull'

Use uma destas alternativas:

Na maioria dos casos, NVL é a substituição mais simples e direta:

-- MySQL
SELECT IFNULL(col, 0) FROM my_table;

-- MaxCompute equivalent
SELECT NVL(col, 0) FROM my_table;

Para obter mais informações, consulte NVL, COALESCE, Expressão CASE WHEN e mapeamentos de funções entre MaxCompute, Hive, MySQL e Oracle.

Como dividir uma linha em várias linhas?

Use TRANS_COLS. A saída inclui uma coluna de índice seguida pelas colunas de dados transpostas. Portanto, o número de aliases deve ser igual a 1 mais o número de colunas de dados:

SELECT TRANS_COLS(2, col1, col2) AS (idx, key, value)
FROM my_table;

Para obter mais informações, consulte TRANS_COLS.

Por que recebo "Expression not in GROUP BY key" ao usar COALESCE?

FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"

Esse erro ocorre quando uma expressão não agregada dentro de COALESCE referencia uma coluna ausente em GROUP BY.

No exemplo a seguir, a consulta falha porque get_json_object(extended_x, '$.table') dentro de decode não está agregado e não consta em GROUP BY:

SELECT
  md5(concat(aid, bid)) AS id,
  aid,
  bid,
  sum(amountdue) AS amountdue,
  coalesce(
    sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
    decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) AS tableparty
FROM e_orders
WHERE pt = '20170425'
GROUP BY aid, bid;

A expressão decode(get_json_object(extended_x, '$.table'), null, 0, 1) opera em linhas individuais, mas aparece fora de qualquer função de agregação. Envolva-a em uma função de agregação ou inclua-a na cláusula GROUP BY.

Por que recebo erros de conversão implícita de tipo após ativar o MaxCompute V2.0?

Quando a edição de tipos de dados MaxCompute V2.0 está ativada (odps.sql.type.system.odps2=true), as seguintes conversões implícitas de tipo são desabilitadas:

Essas conversões podem causar perda de precisão, portanto, a versão V2.0 as bloqueia por padrão. Para resolver isso:

• Conversão explícita (recomendado): Use CAST para converter tipos explicitamente. Para obter mais informações, consulte CAST.

    SELECT CAST(string_col AS BIGINT) FROM my_table;

• Desativar tipos de dados V2.0: Defina odps.sql.type.system.odps2=false para reativar as conversões implícitas. Observe que essa configuração também pode desabilitar funções de extensão V2.0 como YEAR, QUARTER, MONTH e DAY, dependendo da configuração no nível do projeto.