MaxCompute:FAQ about built-in functions

Does a built-in function of MaxCompute support the conversion from 2010/1/3 to 2010-01-03?

No, the built-in functions of MaxCompute do not support the conversion from 2010/1/3 to 2010-01-03. You can use to_char(to_date('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd') to convert the date value 2010/01/03 to 2010-01-03. For more information for the built-in functions, see TO_DATE and TO_CHAR.

To convert 2010/1/3 to 2010-01-03, you must write a user-defined function (UDF). For more information about how to write a UDF, see Overview.

How do I convert a UNIX timestamp of the BIGINT type to a date value of the DATETIME type?

You can use the FROM_UNIXTIME function to convert the UNIX timestamp of the BIGINT type to a date value of the DATETIME type. For more information, see FROM_UNIXTIME.

How do I obtain the current system time?

You can use the GETDATE function to obtain the current system time. For more information, see GETDATE.

What do I do if the "cannot be resolved" error message appears when I use the YEAR, QUARTER, MONTH, or DAY function?

• Problem description

  The following error message appears when I use the YEAR, QUARTER, MONTH, or DAY function:

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

• Cause

  The YEAR, QUARTER, MONTH, and DAY functions are the extension functions of MaxCompute V2.0. To use these date functions, you must enable the MaxCompute V2.0 data type edition.

• Solution

  Add set odps.sql.type.system.odps2 = true; before the SQL statement that you use to enable the MaxCompute V2.0 data type edition.

When I use the TO_DATE function, an error message appears, which indicates that the date value does not contain the minute part. What do I do?

• Problem description

  The following error message appears when an SQL statement that contains to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss') is executed:

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

• Cause

  The format of the second parameter that is included in the TO_DATE function is invalid. Both mm and MM indicate the month. mi indicates the minute.

• Solution

  Modify the function in the SQL statement to to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss').

Why is an unexpected result returned when I use the ROUND function to round data of the DOUBLE type?

select round(4.515, 2),round(125.315, 2);                   

A value of the DOUBLE type is an 8-byte double-precision floating point number, which has a difference in precision. The value of the DOUBLE type for 4.515 is 4.514999999.... As a result, the rounding result is 4.51.

What are the functions that I can use to configure an auto-increment sequence in MaxCompute?

You can use the ROW_NUMBER function to configure an auto-increment sequence. For more information, see ROW_NUMBER.

How do I concatenate values in a field?

You can use the WM_CONCAT function to concatenate values in a field. For more information, see WM_CONCAT.

Does MaxCompute support the MD5 function?

Yes, MaxCompute supports the MD5 function. For more information, see MD5.

How do I left pad a string with 0s to increase the string length to the specified length?

You can use the LPAD function to left pad a string with 0s to increase the string length to the specified length. For more information, see LPAD.

Does MaxCompute support the SUBSTRING_INDEX function of MySQL?

Yes, MaxCompute supports the SUBSTRING_INDEX function. For more information, see SUBSTRING_INDEX.

Does the pattern parameter of the REGEXP_COUNT function support nested query statements?

No, the pattern parameter of the REGEXP_COUNT function does not support nested query statements. For more information about how to use the REGEXP_COUNT function, see REGEXP_COUNT.

Does MaxCompute support the to_char (Data, FM9999.00) function of Oracle?

No, MaxCompute does not support this function. If you want to change only the display format of numbers, you can use the FORMAT_NUMBER function. Sample statement:

-- The return value is 12,332.123. 
select format_number(12332.123456, '#,###,###,###.###');

What do I do if the "Invalid function" error message appears when I use the IFNULL function?

• Problem description
  The IFNULL function is used in the following SQL statement:

  select a.id as id > , ifnull(concat('phs\xxx', a.insy, '\xxxb\xxx', ifnull()))

  The following error message appears:

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

• Cause

  MaxCompute does not support the IFNULL function.

• Solution

  Use the CASE WHEN expression or the COALESCE function. For more information, see CASE WHEN expression or COALESCE.

How do I concatenate all fields that match a specified condition in a JSON string?

MaxCompute SQL queries data that matches the specified conditions by using filter conditions, such as like. After MaxCompute SQL obtains the query results, MaxCompute SQL uses the ARRAY or MAP function to construct the data as a complex data type such as MAP or ARRAY, and then uses the TO_JSON function to aggregate the data.

How do I use each key in a JSON string as a field?

You can use the GET_JSON_OBJECT function to extract fields from a JSON string.

How do I convert a JSON string into an array?

You can use the FROM_JSON function in an SQL statement to perform a data type conversion, such as select from_json(<col_name>, "array<bigint>");.

Which function of MaxCompute is similar to the IFNULL function of MySQL?

The IFNULL function of MySQL is similar to the NVL function of MaxCompute. For more information about the mappings between MaxCompute built-in functions and Hive, MySQL, and Oracle functions, see Mappings between built-in functions of MaxCompute and built-in functions of Hive, MySQL, and Oracle.

How do I convert one row of data into multiple rows of data?

You can use the TRANS_COLS function to convert one row of data into multiple rows of data.

What do I do if the "Expression not in GROUP BY key" error message appears when I use the COALESCE function?

• Problem description

  The following error message appears when more than one expression is used in the COALESCE function:

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

  Sample statement:

  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
  , decode(sum(headcount),null,0,sum(headcount) ) as headcount
  , 'a' as pt
  from e_orders
  where pt='20170425'
  group by aid, bid;

• Cause

  A required grouping field is not specified for GROUP BY.

• Solution

  Specify the following expression after GROUP BY. The expression returns a field.

  coalesce(
  sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
  decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) as tableparty
  , decode(sum(headcount),null,0,sum(headcount) ) as headcount

What do I do if an implicit data type conversion error appears when I use a built-in function of MaxCompute?