You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spot.apache.org by na...@apache.org on 2018/03/19 19:28:29 UTC
[20/42] incubator-spot git commit: [SPOT-213][SPOT-216] [setup] moved
script files to support additional engines such as beeline, impala
[SPOT-213][SPOT-216] [setup] moved script files to support additional engines such as beeline, impala
Project: http://git-wip-us.apache.org/repos/asf/incubator-spot/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-spot/commit/3383c07c
Tree: http://git-wip-us.apache.org/repos/asf/incubator-spot/tree/3383c07c
Diff: http://git-wip-us.apache.org/repos/asf/incubator-spot/diff/3383c07c
Branch: refs/heads/SPOT-181_ODM
Commit: 3383c07cbaf695953facdc3c269c01af992abaae
Parents: 8b600c8
Author: natedogs911 <na...@gmail.com>
Authored: Thu Jan 18 12:23:24 2018 -0800
Committer: natedogs911 <na...@gmail.com>
Committed: Thu Jan 18 12:23:24 2018 -0800
----------------------------------------------------------------------
spot-setup/beeline/create_dns_parquet.hql | 162 +++++++++++++++++++
spot-setup/beeline/create_flow_parquet.hql | 194 ++++++++++++++++++++++
spot-setup/beeline/create_proxy_parquet.hql | 179 ++++++++++++++++++++
spot-setup/create_dns_parquet.hql | 163 -------------------
spot-setup/create_flow_parquet.hql | 195 ----------------------
spot-setup/create_proxy_parquet.hql | 177 --------------------
spot-setup/hive/create_dns_parquet.hql | 165 +++++++++++++++++++
spot-setup/hive/create_flow_parquet.hql | 197 +++++++++++++++++++++++
spot-setup/hive/create_proxy_parquet.hql | 179 ++++++++++++++++++++
spot-setup/impala/create_dns_parquet.hql | 163 +++++++++++++++++++
spot-setup/impala/create_flow_parquet.hql | 195 ++++++++++++++++++++++
spot-setup/impala/create_proxy_parquet.hql | 177 ++++++++++++++++++++
12 files changed, 1611 insertions(+), 535 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/beeline/create_dns_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/beeline/create_dns_parquet.hql b/spot-setup/beeline/create_dns_parquet.hql
new file mode 100755
index 0000000..b9be108
--- /dev/null
+++ b/spot-setup/beeline/create_dns_parquet.hql
@@ -0,0 +1,162 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len INT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_dendro (
+unix_tstamp BIGINT,
+dns_a STRING,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_edge (
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_scores (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+ml_score FLOAT,
+tld STRING,
+query_rep STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_storyboard (
+ip_threat STRING,
+dns_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_threat_dendro (
+anchor STRING,
+total BIGINT,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/threat_dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.dns_threat_investigation (
+unix_tstamp BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+ip_sev INT,
+dns_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/dns/hive/oa/threat_investigation';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/beeline/create_flow_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/beeline/create_flow_parquet.hql b/spot-setup/beeline/create_flow_parquet.hql
new file mode 100755
index 0000000..25e860a
--- /dev/null
+++ b/spot-setup/beeline/create_flow_parquet.hql
@@ -0,0 +1,194 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow (
+treceived STRING,
+unix_tstamp BIGINT,
+tryear INT,
+trmonth INT,
+trday INT,
+trhour INT,
+trminute INT,
+trsec INT,
+tdur FLOAT,
+sip STRING,
+dip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flag STRING,
+fwd INT,
+stos INT,
+ipkt BIGINT,
+ibyt BIGINT,
+opkt BIGINT,
+obyt BIGINT,
+input INT,
+output INT,
+sas INT,
+das INT,
+dtos INT,
+dir INT,
+rip STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_chords (
+ip_threat STRING,
+srcip STRING,
+dstip STRING,
+ibyt BIGINT,
+ipkt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/chords';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_edge (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flags STRING,
+tos INT,
+ibyt BIGINT,
+ipkt BIGINT,
+input BIGINT,
+output BIGINT,
+rip STRING,
+obyt BIGINT,
+opkt BIGINT,
+hh INT,
+mn INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_scores (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+ipkt INT,
+ibyt INT,
+opkt INT,
+obyt INT,
+ml_score FLOAT,
+rank INT,
+srcip_INTernal INT,
+dstip_INTernal INT,
+src_geoloc STRING,
+dst_geoloc STRING,
+src_domain STRING,
+dst_domain STRING,
+src_rep STRING,
+dst_rep STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_storyboard (
+ip_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_threat_investigation (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+srcport INT,
+dstport INT,
+score INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.flow_timeline (
+ip_threat STRING,
+tstart STRING,
+tend STRING,
+srcip STRING,
+dstip STRING,
+proto STRING,
+sport INT,
+dport INT,
+ipkt BIGINT,
+ibyt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/flow/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/beeline/create_proxy_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/beeline/create_proxy_parquet.hql b/spot-setup/beeline/create_proxy_parquet.hql
new file mode 100755
index 0000000..d9cd79f
--- /dev/null
+++ b/spot-setup/beeline/create_proxy_parquet.hql
@@ -0,0 +1,179 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+SET huser;
+SET dbname;
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy (
+p_date STRING,
+p_time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+authgroup STRING,
+exceptionid STRING,
+filterresult STRING,
+webcat STRING,
+referer STRING,
+respcode STRING,
+action STRING,
+urischeme STRING,
+uriport STRING,
+uripath STRING,
+uriquery STRING,
+uriextension STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+virusid STRING,
+bcappname STRING,
+bcappoper STRING,
+fulluri STRING
+)
+PARTITIONED BY (
+y STRING,
+m STRING,
+d STRING,
+h STRING
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_edge (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+webcat STRING,
+respcode STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+referer STRING,
+uriport STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+hh INT,
+respcode_name STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_scores (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+webcat STRING,
+referer STRING,
+respcode INT,
+uriport INT,
+uripath STRING,
+uriquery STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+word STRING,
+ml_score FLOAT,
+uri_rep STRING,
+respcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_storyboard (
+p_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_threat_investigation (
+tdate STRING,
+fulluri STRING,
+uri_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${dbname}.proxy_timeline (
+p_threat STRING,
+tstart STRING,
+tend STRING,
+duration BIGINT,
+clientip STRING,
+respcode STRING,
+respcodename STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${huser}/proxy/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/create_dns_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_dns_parquet.hql b/spot-setup/create_dns_parquet.hql
deleted file mode 100755
index 38025c6..0000000
--- a/spot-setup/create_dns_parquet.hql
+++ /dev/null
@@ -1,163 +0,0 @@
-
--- Licensed to the Apache Software Foundation (ASF) under one or more
--- contributor license agreements. See the NOTICE file distributed with
--- this work for additional information regarding copyright ownership.
--- The ASF licenses this file to You under the Apache License, Version 2.0
--- (the "License"); you may not use this file except in compliance with
--- the License. You may obtain a copy of the License at
-
--- http://www.apache.org/licenses/LICENSE-2.0
-
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-
-
-CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns (
-frame_time STRING,
-unix_tstamp BIGINT,
-frame_len INT,
-ip_dst STRING,
-ip_src STRING,
-dns_qry_name STRING,
-dns_qry_class STRING,
-dns_qry_type INT,
-dns_qry_rcode INT,
-dns_a STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT,
-h TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_dendro (
-unix_tstamp BIGINT,
-dns_a STRING,
-dns_qry_name STRING,
-ip_dst STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/dendro';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_edge (
-unix_tstamp BIGINT,
-frame_len BIGINT,
-ip_dst STRING,
-ip_src STRING,
-dns_qry_name STRING,
-dns_qry_class STRING,
-dns_qry_type INT,
-dns_qry_rcode INT,
-dns_a STRING,
-hh INT,
-dns_qry_class_name STRING,
-dns_qry_type_name STRING,
-dns_qry_rcode_name STRING,
-network_context STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/edge';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_ingest_summary (
-tdate STRING,
-total BIGINT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/summary';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_scores (
-frame_time STRING,
-unix_tstamp BIGINT,
-frame_len BIGINT,
-ip_dst STRING,
-dns_qry_name STRING,
-dns_qry_class STRING,
-dns_qry_type INT,
-dns_qry_rcode INT,
-ml_score FLOAT,
-tld STRING,
-query_rep STRING,
-hh INT,
-dns_qry_class_name STRING,
-dns_qry_type_name STRING,
-dns_qry_rcode_name STRING,
-network_context STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/suspicious';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_storyboard (
-ip_threat STRING,
-dns_threat STRING,
-title STRING,
-text STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/storyboard';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_threat_dendro (
-anchor STRING,
-total BIGINT,
-dns_qry_name STRING,
-ip_dst STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/threat_dendro';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.dns_threat_investigation (
-unix_tstamp BIGINT,
-ip_dst STRING,
-dns_qry_name STRING,
-ip_sev INT,
-dns_sev INT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/dns/hive/oa/threat_investigation';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/create_flow_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_flow_parquet.hql b/spot-setup/create_flow_parquet.hql
deleted file mode 100755
index 41c4819..0000000
--- a/spot-setup/create_flow_parquet.hql
+++ /dev/null
@@ -1,195 +0,0 @@
-
--- Licensed to the Apache Software Foundation (ASF) under one or more
--- contributor license agreements. See the NOTICE file distributed with
--- this work for additional information regarding copyright ownership.
--- The ASF licenses this file to You under the Apache License, Version 2.0
--- (the "License"); you may not use this file except in compliance with
--- the License. You may obtain a copy of the License at
-
--- http://www.apache.org/licenses/LICENSE-2.0
-
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-
-
-CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow (
-treceived STRING,
-unix_tstamp BIGINT,
-tryear INT,
-trmonth INT,
-trday INT,
-trhour INT,
-trminute INT,
-trsec INT,
-tdur FLOAT,
-sip STRING,
-dip STRING,
-sport INT,
-dport INT,
-proto STRING,
-flag STRING,
-fwd INT,
-stos INT,
-ipkt BIGINT,
-ibyt BIGINT,
-opkt BIGINT,
-obyt BIGINT,
-input INT,
-output INT,
-sas INT,
-das INT,
-dtos INT,
-dir INT,
-rip STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT,
-h TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_chords (
-ip_threat STRING,
-srcip STRING,
-dstip STRING,
-ibyt BIGINT,
-ipkt BIGINT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/chords';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_edge (
-tstart STRING,
-srcip STRING,
-dstip STRING,
-sport INT,
-dport INT,
-proto STRING,
-flags STRING,
-tos INT,
-ibyt BIGINT,
-ipkt BIGINT,
-input BIGINT,
-output BIGINT,
-rip STRING,
-obyt BIGINT,
-opkt BIGINT,
-hh INT,
-mn INT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/edge';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_ingest_summary (
-tdate STRING,
-total BIGINT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/summary';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_scores (
-tstart STRING,
-srcip STRING,
-dstip STRING,
-sport INT,
-dport INT,
-proto STRING,
-ipkt INT,
-ibyt INT,
-opkt INT,
-obyt INT,
-ml_score FLOAT,
-rank INT,
-srcip_INTernal INT,
-dstip_INTernal INT,
-src_geoloc STRING,
-dst_geoloc STRING,
-src_domain STRING,
-dst_domain STRING,
-src_rep STRING,
-dst_rep STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/suspicious';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_storyboard (
-ip_threat STRING,
-title STRING,
-text STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/storyboard';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_threat_investigation (
-tstart STRING,
-srcip STRING,
-dstip STRING,
-srcport INT,
-dstport INT,
-score INT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/threat_investigation';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.flow_timeline (
-ip_threat STRING,
-tstart STRING,
-tend STRING,
-srcip STRING,
-dstip STRING,
-proto STRING,
-sport INT,
-dport INT,
-ipkt BIGINT,
-ibyt BIGINT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/flow/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/create_proxy_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/create_proxy_parquet.hql b/spot-setup/create_proxy_parquet.hql
deleted file mode 100755
index f665dc2..0000000
--- a/spot-setup/create_proxy_parquet.hql
+++ /dev/null
@@ -1,177 +0,0 @@
-
--- Licensed to the Apache Software Foundation (ASF) under one or more
--- contributor license agreements. See the NOTICE file distributed with
--- this work for additional information regarding copyright ownership.
--- The ASF licenses this file to You under the Apache License, Version 2.0
--- (the "License"); you may not use this file except in compliance with
--- the License. You may obtain a copy of the License at
-
--- http://www.apache.org/licenses/LICENSE-2.0
-
--- Unless required by applicable law or agreed to in writing, software
--- distributed under the License is distributed on an "AS IS" BASIS,
--- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--- See the License for the specific language governing permissions and
--- limitations under the License.
-
-
-CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy (
-p_date STRING,
-p_time STRING,
-clientip STRING,
-host STRING,
-reqmethod STRING,
-useragent STRING,
-resconttype STRING,
-duration INT,
-username STRING,
-authgroup STRING,
-exceptionid STRING,
-filterresult STRING,
-webcat STRING,
-referer STRING,
-respcode STRING,
-action STRING,
-urischeme STRING,
-uriport STRING,
-uripath STRING,
-uriquery STRING,
-uriextension STRING,
-serverip STRING,
-scbytes INT,
-csbytes INT,
-virusid STRING,
-bcappname STRING,
-bcappoper STRING,
-fulluri STRING
-)
-PARTITIONED BY (
-y STRING,
-m STRING,
-d STRING,
-h STRING
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_edge (
-tdate STRING,
-time STRING,
-clientip STRING,
-host STRING,
-webcat STRING,
-respcode STRING,
-reqmethod STRING,
-useragent STRING,
-resconttype STRING,
-referer STRING,
-uriport STRING,
-serverip STRING,
-scbytes INT,
-csbytes INT,
-fulluri STRING,
-hh INT,
-respcode_name STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/edge';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_ingest_summary (
-tdate STRING,
-total BIGINT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/summary';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_scores (
-tdate STRING,
-time STRING,
-clientip STRING,
-host STRING,
-reqmethod STRING,
-useragent STRING,
-resconttype STRING,
-duration INT,
-username STRING,
-webcat STRING,
-referer STRING,
-respcode INT,
-uriport INT,
-uripath STRING,
-uriquery STRING,
-serverip STRING,
-scbytes INT,
-csbytes INT,
-fulluri STRING,
-word STRING,
-ml_score FLOAT,
-uri_rep STRING,
-respcode_name STRING,
-network_context STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/suspicious';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_storyboard (
-p_threat STRING,
-title STRING,
-text STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/storyboard';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_threat_investigation (
-tdate STRING,
-fulluri STRING,
-uri_sev INT
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/threat_investigation';
-
-
-CREATE EXTERNAL TABLE ${var:dbname}.proxy_timeline (
-p_threat STRING,
-tstart STRING,
-tend STRING,
-duration BIGINT,
-clientip STRING,
-respcode STRING,
-respcodename STRING
-)
-PARTITIONED BY (
-y SMALLINT,
-m TINYINT,
-d TINYINT
-)
-STORED AS PARQUET
-LOCATION '${var:huser}/proxy/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/hive/create_dns_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/hive/create_dns_parquet.hql b/spot-setup/hive/create_dns_parquet.hql
new file mode 100755
index 0000000..8e31ed3
--- /dev/null
+++ b/spot-setup/hive/create_dns_parquet.hql
@@ -0,0 +1,165 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+SET hiveconf:huser;
+SET hiveconf:dbname;
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len INT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_dendro (
+unix_tstamp BIGINT,
+dns_a STRING,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_edge (
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_scores (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+ml_score FLOAT,
+tld STRING,
+query_rep STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_storyboard (
+ip_threat STRING,
+dns_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_threat_dendro (
+anchor STRING,
+total BIGINT,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/threat_dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.dns_threat_investigation (
+unix_tstamp BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+ip_sev INT,
+dns_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/dns/hive/oa/threat_investigation';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/hive/create_flow_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/hive/create_flow_parquet.hql b/spot-setup/hive/create_flow_parquet.hql
new file mode 100755
index 0000000..034e194
--- /dev/null
+++ b/spot-setup/hive/create_flow_parquet.hql
@@ -0,0 +1,197 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+SET hiveconf:huser;
+SET hiveconf:dbname;
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow (
+treceived STRING,
+unix_tstamp BIGINT,
+tryear INT,
+trmonth INT,
+trday INT,
+trhour INT,
+trminute INT,
+trsec INT,
+tdur FLOAT,
+sip STRING,
+dip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flag STRING,
+fwd INT,
+stos INT,
+ipkt BIGINT,
+ibyt BIGINT,
+opkt BIGINT,
+obyt BIGINT,
+input INT,
+output INT,
+sas INT,
+das INT,
+dtos INT,
+dir INT,
+rip STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_chords (
+ip_threat STRING,
+srcip STRING,
+dstip STRING,
+ibyt BIGINT,
+ipkt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/chords';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_edge (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flags STRING,
+tos INT,
+ibyt BIGINT,
+ipkt BIGINT,
+input BIGINT,
+output BIGINT,
+rip STRING,
+obyt BIGINT,
+opkt BIGINT,
+hh INT,
+mn INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_scores (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+ipkt INT,
+ibyt INT,
+opkt INT,
+obyt INT,
+ml_score FLOAT,
+rank INT,
+srcip_INTernal INT,
+dstip_INTernal INT,
+src_geoloc STRING,
+dst_geoloc STRING,
+src_domain STRING,
+dst_domain STRING,
+src_rep STRING,
+dst_rep STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_storyboard (
+ip_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_threat_investigation (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+srcport INT,
+dstport INT,
+score INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.flow_timeline (
+ip_threat STRING,
+tstart STRING,
+tend STRING,
+srcip STRING,
+dstip STRING,
+proto STRING,
+sport INT,
+dport INT,
+ipkt BIGINT,
+ibyt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/flow/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/hive/create_proxy_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/hive/create_proxy_parquet.hql b/spot-setup/hive/create_proxy_parquet.hql
new file mode 100755
index 0000000..16d90c0
--- /dev/null
+++ b/spot-setup/hive/create_proxy_parquet.hql
@@ -0,0 +1,179 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+SET hiveconf:huser;
+SET hiveconf:dbname;
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy (
+p_date STRING,
+p_time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+authgroup STRING,
+exceptionid STRING,
+filterresult STRING,
+webcat STRING,
+referer STRING,
+respcode STRING,
+action STRING,
+urischeme STRING,
+uriport STRING,
+uripath STRING,
+uriquery STRING,
+uriextension STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+virusid STRING,
+bcappname STRING,
+bcappoper STRING,
+fulluri STRING
+)
+PARTITIONED BY (
+y STRING,
+m STRING,
+d STRING,
+h STRING
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_edge (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+webcat STRING,
+respcode STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+referer STRING,
+uriport STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+hh INT,
+respcode_name STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_scores (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+webcat STRING,
+referer STRING,
+respcode INT,
+uriport INT,
+uripath STRING,
+uriquery STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+word STRING,
+ml_score FLOAT,
+uri_rep STRING,
+respcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_storyboard (
+p_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_threat_investigation (
+tdate STRING,
+fulluri STRING,
+uri_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${hiveconf:dbname}.proxy_timeline (
+p_threat STRING,
+tstart STRING,
+tend STRING,
+duration BIGINT,
+clientip STRING,
+respcode STRING,
+respcodename STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${hiveconf:huser}/proxy/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/impala/create_dns_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/impala/create_dns_parquet.hql b/spot-setup/impala/create_dns_parquet.hql
new file mode 100755
index 0000000..274ea9d
--- /dev/null
+++ b/spot-setup/impala/create_dns_parquet.hql
@@ -0,0 +1,163 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len INT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_dendro (
+unix_tstamp BIGINT,
+dns_a STRING,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_edge (
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+ip_src STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+dns_a STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_scores (
+frame_time STRING,
+unix_tstamp BIGINT,
+frame_len BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+dns_qry_class STRING,
+dns_qry_type INT,
+dns_qry_rcode INT,
+ml_score FLOAT,
+tld STRING,
+query_rep STRING,
+hh INT,
+dns_qry_class_name STRING,
+dns_qry_type_name STRING,
+dns_qry_rcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_storyboard (
+ip_threat STRING,
+dns_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_threat_dendro (
+anchor STRING,
+total BIGINT,
+dns_qry_name STRING,
+ip_dst STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/threat_dendro';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.dns_threat_investigation (
+unix_tstamp BIGINT,
+ip_dst STRING,
+dns_qry_name STRING,
+ip_sev INT,
+dns_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/dns/hive/oa/threat_investigation';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/impala/create_flow_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/impala/create_flow_parquet.hql b/spot-setup/impala/create_flow_parquet.hql
new file mode 100755
index 0000000..c8d3481
--- /dev/null
+++ b/spot-setup/impala/create_flow_parquet.hql
@@ -0,0 +1,195 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow (
+treceived STRING,
+unix_tstamp BIGINT,
+tryear INT,
+trmonth INT,
+trday INT,
+trhour INT,
+trminute INT,
+trsec INT,
+tdur FLOAT,
+sip STRING,
+dip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flag STRING,
+fwd INT,
+stos INT,
+ipkt BIGINT,
+ibyt BIGINT,
+opkt BIGINT,
+obyt BIGINT,
+input INT,
+output INT,
+sas INT,
+das INT,
+dtos INT,
+dir INT,
+rip STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT,
+h TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_chords (
+ip_threat STRING,
+srcip STRING,
+dstip STRING,
+ibyt BIGINT,
+ipkt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/chords';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_edge (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+flags STRING,
+tos INT,
+ibyt BIGINT,
+ipkt BIGINT,
+input BIGINT,
+output BIGINT,
+rip STRING,
+obyt BIGINT,
+opkt BIGINT,
+hh INT,
+mn INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_scores (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+sport INT,
+dport INT,
+proto STRING,
+ipkt INT,
+ibyt INT,
+opkt INT,
+obyt INT,
+ml_score FLOAT,
+rank INT,
+srcip_INTernal INT,
+dstip_INTernal INT,
+src_geoloc STRING,
+dst_geoloc STRING,
+src_domain STRING,
+dst_domain STRING,
+src_rep STRING,
+dst_rep STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_storyboard (
+ip_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_threat_investigation (
+tstart STRING,
+srcip STRING,
+dstip STRING,
+srcport INT,
+dstport INT,
+score INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.flow_timeline (
+ip_threat STRING,
+tstart STRING,
+tend STRING,
+srcip STRING,
+dstip STRING,
+proto STRING,
+sport INT,
+dport INT,
+ipkt BIGINT,
+ibyt BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/flow/hive/oa/timeline';
http://git-wip-us.apache.org/repos/asf/incubator-spot/blob/3383c07c/spot-setup/impala/create_proxy_parquet.hql
----------------------------------------------------------------------
diff --git a/spot-setup/impala/create_proxy_parquet.hql b/spot-setup/impala/create_proxy_parquet.hql
new file mode 100755
index 0000000..ddf3283
--- /dev/null
+++ b/spot-setup/impala/create_proxy_parquet.hql
@@ -0,0 +1,177 @@
+
+-- Licensed to the Apache Software Foundation (ASF) under one or more
+-- contributor license agreements. See the NOTICE file distributed with
+-- this work for additional information regarding copyright ownership.
+-- The ASF licenses this file to You under the Apache License, Version 2.0
+-- (the "License"); you may not use this file except in compliance with
+-- the License. You may obtain a copy of the License at
+
+-- http://www.apache.org/licenses/LICENSE-2.0
+
+-- Unless required by applicable law or agreed to in writing, software
+-- distributed under the License is distributed on an "AS IS" BASIS,
+-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+-- See the License for the specific language governing permissions and
+-- limitations under the License.
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy (
+p_date STRING,
+p_time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+authgroup STRING,
+exceptionid STRING,
+filterresult STRING,
+webcat STRING,
+referer STRING,
+respcode STRING,
+action STRING,
+urischeme STRING,
+uriport STRING,
+uripath STRING,
+uriquery STRING,
+uriextension STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+virusid STRING,
+bcappname STRING,
+bcappoper STRING,
+fulluri STRING
+)
+PARTITIONED BY (
+y STRING,
+m STRING,
+d STRING,
+h STRING
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_edge (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+webcat STRING,
+respcode STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+referer STRING,
+uriport STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+hh INT,
+respcode_name STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/edge';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_ingest_summary (
+tdate STRING,
+total BIGINT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/summary';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_scores (
+tdate STRING,
+time STRING,
+clientip STRING,
+host STRING,
+reqmethod STRING,
+useragent STRING,
+resconttype STRING,
+duration INT,
+username STRING,
+webcat STRING,
+referer STRING,
+respcode INT,
+uriport INT,
+uripath STRING,
+uriquery STRING,
+serverip STRING,
+scbytes INT,
+csbytes INT,
+fulluri STRING,
+word STRING,
+ml_score FLOAT,
+uri_rep STRING,
+respcode_name STRING,
+network_context STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/suspicious';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_storyboard (
+p_threat STRING,
+title STRING,
+text STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/storyboard';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_threat_investigation (
+tdate STRING,
+fulluri STRING,
+uri_sev INT
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/threat_investigation';
+
+
+CREATE EXTERNAL TABLE IF NOT EXISTS ${var:dbname}.proxy_timeline (
+p_threat STRING,
+tstart STRING,
+tend STRING,
+duration BIGINT,
+clientip STRING,
+respcode STRING,
+respcodename STRING
+)
+PARTITIONED BY (
+y SMALLINT,
+m TINYINT,
+d TINYINT
+)
+STORED AS PARQUET
+LOCATION '${var:huser}/proxy/hive/oa/timeline';