Index: Task1_3.sql |
diff --git a/Task1_3.sql b/Task1_3.sql |
new file mode 100644 |
index 0000000000000000000000000000000000000000..b7a0fb027f01a931599b6b76c8b35638b4c56742 |
--- /dev/null |
+++ b/Task1_3.sql |
@@ -0,0 +1,34 @@ |
+ |
+#Creating the table that will hold values from tables that contains geographic information: |
+ |
+CREATE TABLE geoHops AS SELECT geoLocation.* ,GeoIp.startIpNum,GeoIp.endIpNum FROM geoLocation iNNER JOIN GeoIp ON geoLocation.locId = GeoIp.locId; |
+ |
+ |
+ |
+#Filtering the database to get geographic information for every source ip address found in table Hops. This requires to translate every ip address to their integers representation, this can be done with the help of the function INET_ATON. |
+ |
+SELECT Hops.idHop, Hops.ipSrc, geoHops.* FROM Hops, geoHops WHERE INET_ATON(ipSrc) Between startIpNum and endIpNum AND idHop BETWEEN 1 AND 53020 AND ipSrc != '*' ORDER BY idHop ASC INTO OUTFIlE '/var/lib/mysql-files/geoHopsFinal.csv'; |
+ |
+#This " INTO OUTFIlE '/var/lib/mysql-files/geoHopsFinal.csv'; " will only save the results of the output as csv file. One thing to mention is that when trying to filter from idHop 1 to 53020 takes hours to complete. I will advise to run this script for fewer idHops EX: between 1 and 5000. |
+ |
+ |
+# We used the following SQL query to create the tables that will hold the data from MaxMind GeoIp services |
+ |
+mysql> CREATE TABLE GeoIp ( |
+ -> startIpNum int(10) unsigned NOT NULL, |
+ -> endIpNum int(10) unsigned NOT NULL, |
+ -> locId int(10) unsigned NOT NULL, |
+ -> PRIMARY KEY (startIpNum, endIpNum), |
+ -> ); |
+ |
+ |
+mysql> CREATE TABLE geoLocation ( |
+ -> locId int(10) unsigned NOT NULL, |
+ -> country char(2) NOT NULL, |
+ -> region char(2) NOT NULL, |
+ -> city varchar(64) NOT NULL, |
+ -> postalCode varchar(5) NOT NULL, |
+ -> latitude float, |
+ -> logitude float, |
+ -> PRIMARY KEY (locId)); |
+ |