SQL Dialect

Starting from version 2.1, Doris can support multiple SQL dialects, such as Presto, Trino, Hive, PostgreSQL, Spark, Oracle, Clickhouse, and more. Through this feature, users can directly use the corresponding SQL dialect to query data in Doris, which facilitates users to smoothly migrate their original business to Doris.

  1. This function is currently an experimental function. If you encounter any problems during use, you are welcome to provide feedback through the mail group, GitHub issue, etc. .

  2. This function only supports query statements and does not support other DDL and DML statements including Explain.

Deploy service

  1. Download latest Doris SQL Convertor(1.0.1)

  2. On any FE node, start the service through the following command:

    nohup ./doris-sql-convertor-1.0.1-bin-x86 run --host=0.0.0.0 --port=5001 &

    SQL Dialect - 图1note

    1. This service is a stateless service and can be started and stopped at any time.

    2. 5001 is the service port and can be arbitrarily specified as an available port.

    3. It is recommended to start a separate service on each FE node.

  3. Start the Doris cluster (version 2.1 or higher)

  4. Set the URL of the SQL Dialect Conversion Service with the following command in Doris:

    MySQL> set global sql_converter_service_url = "http://127.0.0.1:5001/api/v1/convert"

    SQL Dialect - 图2note

    1. 127.0.0.1:5001 is the deployment node IP and port of the SQL dialect conversion service.

Use SQL dialect

Currently supported dialect types include:

  • presto
  • trino
  • hive
  • spark
  • postgres
  • clickhouse
  • oracle

example:

  • Presto
  1. mysql> set sql_dialect=presto;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SELECT cast(start_time as varchar(20)) as col1,
  5.     ->        array_distinct(arr_int) as col2,
  6.     ->        FILTER(arr_str, x -> x LIKE '%World%') as col3,
  7.     ->        to_date(value,'%Y-%m-%d')as col4,
  8.     ->        YEAR(start_time) as col5,
  9.     ->        date_add('month', 1, start_time) as col6,
  10.     ->        date_format(start_time, '%Y%m%d')as col7,
  11.     ->        REGEXP_EXTRACT_ALL(value, '-.') as col8,
  12.     ->        JSON_EXTRACT('{"id": "33"}', '$.id')as col9,
  13.     ->        element_at(arr_int, 1) as col10,
  14.     ->        date_trunc('day',start_time) as col11
  15.     ->     FROM test_sqlconvert
  16.     ->     where date_trunc('day',start_time)= DATE'2024-05-20'     
  17.     -> order by id; 
  18. +---------------------+-----------+-----------+-------------+------+---------------------+----------+-------------+------+-------+---------------------+
  19. | col1                | col2      | col3      | col4        | col5 | col6                | col7     | col8        | col9 | col10 | col11               |
  20. +---------------------+-----------+-----------+-------------+------+---------------------+----------+-------------+------+-------+---------------------+
  21. | 2024-05-20 13:14:52 | [1, 2, 3] | ["World"] | 2024-01-14  | 2024 | 2024-06-20 13:14:52 | 20240520 | ['-0','-1'] | "33" |     1 | 2024-05-20 00:00:00 |
  22. +---------------------+-----------+-----------+-------------+------+---------------------+----------+-------------+------+-------+---------------------+
  23. 1 row in set (0.13 sec)
  • Clickhouse
  1. mysql> set sql_dialect=clickhouse;
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> select toString(start_time) as col1,
  5.     ->        arrayCompact(arr_int) as col2,
  6.     ->        arrayFilter(x -> x like '%World%',arr_str)as col3,
  7.     ->        toDate(value) as col4,
  8.     ->        toYear(start_time)as col5,
  9.     ->        addMonths(start_time, 1)as col6,
  10.     ->        toYYYYMMDD(start_time, 'US/Eastern')as col7,
  11.     ->        extractAll(value, '-.')as co8,
  12.     ->        JSONExtractString('{"id": "33"}' , 'id')as col9,
  13.     ->        arrayElement(arr_int, 1) as col10,
  14.     ->        date_trunc('day',start_time) as col11
  15.     ->      FROM test_sqlconvert
  16.     ->      where date_trunc('day',start_time)= '2024-05-20 00:00:00'
  17.     -> order by id;
  18. +---------------------+-----------+-----------+------------+------+---------------------+----------+-------------+------+-------+---------------------+
  19. | col1                | col2      | col3      | col4       | col5 | col6                | col7     | co8         | col9 | col10 | col11               |
  20. +---------------------+-----------+-----------+------------+------+---------------------+----------+-------------+------+-------+---------------------+
  21. | 2024-05-20 13:14:52 | [1, 2, 3] | ["World"] | 2024-01-14 | 2024 | 2024-06-20 13:14:52 | 20240520 | ['-0','-1'] | "33" |     1 | 2024-05-20 00:00:00 |
  22. +---------------------+-----------+-----------+------------+------+---------------------+----------+-------------+------+-------+---------------------+
  23. 1 row in set (0.04 sec)