Apache Drill SQL Data Type Conversion

Apache Drill supports the data type conversion functions that are used to convert a value from one type to another type. For example, we can cast the numeric or string value to a decimal value, and the string that has the hexadecimal encoding of a sequence of bytes can be converted to VARBINARY and so on.

Apache Drill supports the following list of Data Casting functions.

  1. TO_CHAR(expression, format)
  2. TO_DATE(expression, format)
  3. TO_NUMBER(VARCHAR, format)
  4. TO_TIME (expression [, 'format'])
  5. CAST (expression AS data_type
  6. STRING_BINARY(expression)
  7. BINARY_STRING(expression)

Let us see the Apache Drill Data type conversion function in detail.


1. Apache Drill TO_CHAR Function

Apache Drill TO_CHAR function is used to convert the date, time, number in the character string.

Let us see the syntax of the Apache Drill TO_CHAR function.

TO_CHAR Function Syntax:

The below is the syntax of the Apache Drill TO_CHAR function. We can pass the date, integer, time, double, float data in expression.

TO_CHAR (expression, 'format')

TO_CHAR Examples

In the following example, we will convert the FLOAT/integer/date/time/timestamp data types into a character string in a sequence.

Command:

apache drill> SELECT TO_CHAR(12345.347654, '#,###.###') FROM (VALUES(1));
apache drill> SELECT TO_CHAR(123456.54567, '#,###.###') FROM (VALUES(1));
apache drill> SELECT TO_CHAR((CAST('1990-6-23' AS DATE)), 'yyyy-MMM-dd') FROM (VALUES(1));
apache drill> SELECT TO_CHAR(CAST('11:10:30' AS TIME), 'HH mm ss') FROM (VALUES(1));
apache drill> SELECT TO_CHAR(CAST('2020-1-07 11:00:00' AS TIMESTAMP), 'yyyy MMM dd HH:mm:ss') FROM (VALUES(1));

Output:

drill to char function cloudduggu


2. Apache Drill TO_DATE Function

Apache Drill TO_DATE function is used to convert a character string into a date.

Let us see the syntax of the Apache Drill TO_DATE function.

TO_DATE Function Syntax:

The below is the syntax of the Apache Drill TO_DATE function. In the expression, we can pass the character string in a single quotation.

TO_DATE (expression [, 'format'])

TO_DATE Examples

In the below example, the first command will convert a character to a date format and the second command will extract the year.

Command:

apache drill> SELECT TO_DATE('2020-MAY-25', 'yyyy-MMM-dd') FROM (VALUES(1));
apache drill> SELECT EXTRACT(year from my_date) `selected year` FROM (SELECT TO_DATE('2020-MAY-25', 'yyyy-MMM-dd') AS my_date FROM (VALUES(1)));

Output:

drill to date function cloudduggu


3. Apache Drill TO_NUMBER Function

Apache Drill TO_NUMBER function is used to convert the character string using a format specification.

Let us see the syntax of the Apache Drill TO_NUMBER function.

TO_NUMBER Function Syntax:

The below is the syntax of the Apache Drill TO_NUMBER function in which a string represents the character and the format represent the value such as "#" represent the Digit placeholder, "0" represent the Digit placeholder if the string has a Digit placeholder, on the place of "0" then that will appear otherwise "0" will appear, "." represents the Decimal point, "," represents the Comma grouping separator and the "E" represents the Exponent.

TO_NUMBER ('string', 'format')

TO_NUMBER Examples

The following are some of the examples of the Drill TO_NUMBER function.

Command:

apache drill> SELECT TO_NUMBER('234,675', '######') FROM (VALUES(1));
apache drill> SELECT TO_NUMBER('543.675', '###.###') FROM (VALUES(1));
apache drill> SELECT TO_NUMBER('7654321', '##0.##E0') FROM (VALUES(1));

Output:

drill to number function cloudduggu


4. Apache Drill TO_TIME Function

Apache Drill TO_TIME function is used to convert the character to time data format.

Let us see the syntax of the Apache Drill TO_TIME function.

TO_TIME Function Syntax:

The below is the syntax of the Drill TO_TIME function in which expression is a character string that is enclosed with the single quotation and the format represents the formatting that will be done on an input expression.

TO_TIME (expression [, 'format'])

TO_TIME Examples

The following are some of the examples of the Drill TO_TIME function in which the first command is converting the character to a time format and the second command is converting 81675000 milliseconds to the time.

Command:

apache drill> SELECT TO_TIME('11:30:30', 'HH:mm:ss') FROM (VALUES(1));
apache drill> SELECT to_time(81675000) FROM (VALUES(1));

Output:

drill to time function cloudduggu


5. Apache Drill CAST Function

Apache Drill CAST function is used to convert an expression from one type to another type.

Let us see the syntax of the Apache Drill CAST function.

CAST Function Syntax:

Drill CAST function has the below syntax in which the expression represents the combination of value, SQL functions operators, etc, and the data_type represents the type in which the CAST function will convert the expression.

CAST (<expression> AS <data_type>)

CAST Data Type Conversion Examples

Let us see the example to Cast a number to string data type and a string data type to the numeric data type.


Cast a Character String to a Number Data Type

We can Cast a Character String to a Number Data using the CAST function.

In the following example, we will cast a character to the DECIMAL with two decimal places.

Command:

apache drill> SELECT CAST('1' as DECIMAL(24, 2)) FROM (VALUES(1));

Output:

drill cast function cloudduggu

Cast a Number to a Character Data Type

We can Cast a Number to a Character string using the CAST function.

In the below example we see that the Drill is casting the number to a VARCHAR/CHAR that has 3 bytes. After processing it is showing three characters of the string.

Command:

apache drill> SELECT CAST(578 as VARCHAR(3)) FROM (VALUES(1));

apache drill> SELECT CAST(578 as CHAR(3)) FROM (VALUES(1));

Output:

drill cast char varchar function cloudduggu


6. Apache Drill STRING_BINARY Function

Apache Drill STRING_BINARY function is used to print the bytes. It prints the hexadecimal representation for a byte that is not printable.

Let us see the syntax of the Apache Drill STRING_BINARY function.

STRING_BINARY Function Syntax:

Apache Drill STRING_BINARY Function has the below system in which the expression represents the byte array such as {(byte)0xca, (byte)0xfe, (byte)0xba, (byte)0xbe}.

STRING_BINARY(expression)

STRING_BINARY Example

We will use the STRING_BINARY function with CONVERT_TO function for casting as mentioned below example.

Command:

apache drill> SELECT
STRING_BINARY(CONVERT_TO(1, 'INT')) as a,
STRING_BINARY(CONVERT_TO(1, 'INT_BE')) as a_be,
STRING_BINARY(CONVERT_TO(1, 'BIGINT')) as b,
STRING_BINARY(CONVERT_TO(1, 'BIGINT')) as b_be,
STRING_BINARY(CONVERT_TO(1, 'INT_HADOOPV')) as b_be
FROM (VALUES (1));

Output:

drill string binary function cloudduggu


7. Apache Drill BINARY_STRING Function

Apache Drill BINARY_STRING function is used to convert hexadecimal encoding of a sequence of bytes into the VARBINARY value.

The following is the syntax of the Apache Drill BINARY_STRING function.

BINARY_STRING Function Syntax:

The below is the syntax of the Drill BINARY_STRING function in which the expression represents the hexadecimal string for example "\xca\xfe\xba\xbe"

BINARY_STRING(expression)

BINARY_STRING Example

In the below example we will convert the hexadecimal string \x00\x00\x00\xC8 into an integer equivalent. After processing the below output will be shown.

Command:

apache drill> SELECT CONVERT_FROM(BINARY_STRING('\x00\x00\x00\xC8'), 'INT_BE') AS convert_from_binary_to_string
FROM (VALUES (1));

Output:

drill binary to string function cloudduggu