Parse into VIEW

[youngandpoor]

Hi, I basically new to Vertica, and am having trouble with my VIEW script and I'd also like to parse the data fed into the VIEW.

The origin table is scripted below
Flow No. Count Source Count Destination Count Service Count
1 49728 1.9.98.110 8 15.211.146.0 49728 tcp / 6881 24887

My VIEW needs
Source Count Destination Protocol Port
1.9.98.110 8 15.211.146.0 tcp 6881

The Service column needs a parse to split protocol and port as output above.

any help would be appreciated!

[JimKnicely]

Hi,

The SPLIT_PART() function should work for you!

Example:

Code: Select all

dbadmin=> select trim(split_part('tcp / 6881', '/', 1)) as protocol, trim(split_part('tcp / 6881', '/', 2))::int as port;
 protocol | port
----------+------
 tcp      | 6881
(1 row)

See:
http://my.vertica.com/docs/7.1.x/HTML/i ... ns|_____45

[youngandpoor]

great, that works for the parse with defined data, thanks!

now how do you suggest i include that into the VIEW and parse on the delimiter '/'?

Example:
CREATE VIEW "DB"."vFlow" AS
select c.source_ip,
c.count_2,
c.destination_ip,
c.service_port (select trim(split_part(c.service_port, '/', 1))) AS protocol,
c.service_port (select trim(split_part(c.service_port, '/', 2))::int) AS port
from "DB"."Flow"

thanks again

[JimKnicely]

Something like that

Example:

Code: Select all

dbadmin=> create schema db;
CREATE SCHEMA

dbadmin=> create table db.flow (flow_no int, count_1 int, source_ip varchar(20), count_2 int, destination_ip varchar(20), count_3 int, service_port varchar(20), count_4 int);
CREATE TABLE

dbadmin=> insert into db.flow values (1, 49728, '1.9.98.110', 8, '15.211.146.0', 49728, 'tcp / 6881', 24887);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from db.flow;
 flow_no | count_1 | source_ip  | count_2 | destination_ip | count_3 | service_port | count_4
---------+---------+------------+---------+----------------+---------+--------------+---------
       1 |   49728 | 1.9.98.110 |       8 | 15.211.146.0   |   49728 | tcp / 6881   |   24887
(1 row)

dbadmin=> CREATE VIEW db.vflow AS
dbadmin-> select c.source_ip,
dbadmin-> c.count_2,
dbadmin-> c.destination_ip,
dbadmin-> trim(split_part(c.service_port, '/', 1)) AS protocol,
dbadmin-> trim(split_part(c.service_port, '/', 2))::int AS port
dbadmin-> from db.flow c;
CREATE VIEW

dbadmin=> select * from db.vflow;
 source_ip  | count_2 | destination_ip | protocol | port
------------+---------+----------------+----------+------
 1.9.98.110 |       8 | 15.211.146.0   | tcp      | 6881
(1 row)

Make sense?

[youngandpoor]

YES, makes sense now, thanks a bunch!