... | ... | @@ -77,7 +77,7 @@ CREATE TABLE dnscol.queries LIKE dns.queries STORED AS parquet; |
|
|
## 5. Copy the data from `csv_import` to `queries`
|
|
|
|
|
|
```sql
|
|
|
INSERT INTO dns.queries (id, unixtime, time, qname, domainname, len, frag, ttl, ipv, prot, src, srcp, dst, dstp, udp_sum, dns_len,
|
|
|
INSERT INTO dnscol.queries (id, unixtime, time, qname, domainname, len, frag, ttl, ipv, prot, src, srcp, dst, dstp, udp_sum, dns_len,
|
|
|
aa, tc, rd, ra, z, ad, cd, ancount, arcount, nscount, qdcount, opcode, rcode, qtype, qclass, country, asn, edns_udp, edns_version,
|
|
|
edns_do, edns_ping, edns_nsid, edns_dnssec_dau, edns_dnssec_dhu, edns_dnssec_n3u, edns_client_subnet, edns_other,
|
|
|
edns_client_subnet_asn, edns_client_subnet_country, labels, res_len, time_micro, resp_frag, proc_time, is_google, is_opendns,
|
... | ... | @@ -134,8 +134,8 @@ SELECT |
|
|
delay_us, -- Entrada: time_micro BIGINT
|
|
|
NULL, -- Entrada: resp_frag INT
|
|
|
NULL, -- Entrada: proc_time INT
|
|
|
find_in_set(client_addr, "8.8.8.8,8.8.4.4,2001:4860:4860::8888,2001:4860:4860::8844") > 0, -- Entrada: is_google BOOLEAN
|
|
|
find_in_set(client_addr, "208.67.222.222,208.67.220.220") > 0, -- Entrada: is_opendns BOOLEAN
|
|
|
find_in_set(client_addr, "8.8.8.8,8.8.4.4,2001:4860:4860::8888,2001:4860:4860::8844") > 0, -- Entrada: is_google BOOLEAN, TODO: update IP set, see https://developers.google.com/speed/public-dns/faq#locations
|
|
|
find_in_set(client_addr, "208.67.222.222,208.67.220.220") > 0, -- Entrada: is_opendns BOOLEAN, TODO: update IP set
|
|
|
resp_dns_len, -- Entrada: dns_res_len INT
|
|
|
NULL, -- Entrada: server_location STRING
|
|
|
extract(year from cast(time as timestamp)), -- Entrada: year INT
|
... | ... | |