The vertica functions V6_ATON() converts the IPV6 string to varbinary(16) and reversal (varbinary to IPV6) is achieved by function V6_NTOA().
The customer requirement is converting IPV6 to numeric and it's reverse as well , numeric to IPV6.
IPv6 is achieved by them by following:
dbadmin=> select ('0x'||to_hex(V6_ATON('2601:603:4f81:1b30:a99e:8090:6f23:94e')))::Numeric(39,0);
?column?
50515978093432843058966403658954246478
(1 row)
Now, need your thoughts on how to make reversal work (numeric to IPV6). I have tried various ways as below:
What I have tried:
I thought of to start the reversal of above numeric number by following:
First converting it to hexadecimal, then varbinary and then back to IPV6.
- However, the function to_hex doesn't accept numeric.
select to_hex(50515978093432843058966403658954246478);
ERROR 3457: Function to_hex(numeric) does not exist, or permission is denied for to_hex(numeric)
- Casting into integer first before converting to hexadecimal, was also not possible because the number is of Numeric(39,0) more than the integer can hold.
dbadmin=> select 50515978093432843058966403658954246478::INTEGER;
ERROR 5411: Value exceeds range of type numeric(18,0)
- Casting it into varbinary not possible
dbadmin=> select 50515978093432843058966403658954246478::varbinary(16);
ERROR 2366: Cannot cast type numeric to varbinary
I could achieve with below workaround, by using the linux shell for conversion of hexadecimal to decimal as below.
dbadmin=> \set a echo \'$(echo "obase=16; 50515978093432843058966403658954246478" | bc)\'
dbadmin=> \echo :a
'260106034F811B30A99E80906F23094E'
dbadmin=> select V6_NTOA(hex_to_binary(:a));
V6_NTOA
2601:603:4f81:1b30:a99e:8090:6f23:94e
(1 row)
However, not acceptable by customer. Customer's doesn't want to use Linux shell.
Customer'requirement in his words below. They basically want some way to convert numeric to IPV6 or a way to develop a function in vertica, which transforms numeric to IPV6 string.
"
Today we are joining IPv4's numeric value (result of INET_ATON) to range of IPs from third party data and that way we find various metrics about them.
In order to transform it back we are using INET_NTOA function.
In case of IPv6 we use the technique I shared with you, but unfortunately I can't find a way to make the opposite operation.
We need this operation for our production system.
If the option isn't exist today, what needed to develop such a function in Vertica? "
