library(kableExtra)

NoSQL migration

The flights database will be migrated from MySQL to MongoDB. TO do this the tables in the flights database in MySQL will be read into r dataframes, which will then be exported to a MongoDB.

MySQL

Data is stored in MySQL is a database (schema) called flights, with 5 tables:

We connect to MySQL and load all the tables in the flights database into dataframes which are added to a list.

library(RMySQL)
## Loading required package: DBI
con <- dbConnect(MySQL(), user='root', password='123', dbname='flights', host='localhost')
tablesNamesSQL <- dbListTables(con)
tablesSQL<-list()

for(i in 1:length(tablesNamesSQL)){
  tablesSQL[[i]]<-data.frame(dbReadTable(con,tablesNamesSQL[i]))
}
names(tablesSQL)<-tablesNamesSQL

We inspect the dataframes to see if the data was loaded.

inspectDataFrameList<-function(dataFrameList) {
  cat("\n Names of data frames \n\n")
  print(names(dataFrameList))
  cat(paste("\n Summay of airlines \n\n"))
  print(summary(dataFrameList$airlines))
  cat(paste("\n Summay of airports \n\n"))
  print(summary(dataFrameList$airports))
  cat(paste("\n Summay of flights \n\n"))
  print(summary(dataFrameList$flights))
  cat(paste("\n Summay of planes \n\n"))
  print(summary(dataFrameList$planes))
  cat(paste("\n Summay of weather \n\n"))
  print(summary(dataFrameList$weather))
  cat(paste("\n Head of airlines \n\n"))
  print(kable(head(dataFrameList$airlines,n=20))) 
  cat(paste("\n\n Head of airports \n\n"))
  print(kable(head(dataFrameList$airports,n=20)))
  cat(paste("\n\n Head of flights \n\n"))
  print(kable(head(dataFrameList$flights,n=20))) 
  cat(paste("\n\n Head of planes \n\n"))
  print(kable(head(dataFrameList$planes,n=20))) 
  cat(paste("\n\n Head of weather \n\n"))
  print(kable(head(dataFrameList$weather,n=20)))  
}

inspectDataFrameList(tablesSQL)

Names of data frames

[1] “airlines” “airports” “flights” “planes” “weather”

Summay of airlines

carrier name
Length:16 Length:16
Class :character Class :character
Mode :character Mode :character

Summay of airports

 faa                name                lat             lon

Length:1397 Length:1397 Min. :19.72 Min. :-176.65
Class :character Class :character 1st Qu.:34.27 1st Qu.:-119.32
Mode :character Mode :character Median :40.14 Median : -94.91
Mean :41.75 Mean :-103.69
3rd Qu.:45.25 3rd Qu.: -82.54
Max. :72.27 Max. : 174.11
alt tz dst
Min. : -54 Min. :-11.000 Length:1397
1st Qu.: 70 1st Qu.: -8.000 Class :character
Median : 483 Median : -6.000 Mode :character
Mean :1006 Mean : -6.421
3rd Qu.:1076 3rd Qu.: -5.000
Max. :9078 Max. : 8.000

Summay of flights

  year          month             day           dep_time

Min. :2013 Min. : 1.000 Min. : 1.00 Min. : 1
1st Qu.:2013 1st Qu.: 1.000 1st Qu.: 7.00 1st Qu.: 906
Median :2013 Median :10.000 Median :14.00 Median :1357
Mean :2013 Mean : 8.062 Mean :14.64 Mean :1344
3rd Qu.:2013 3rd Qu.:11.000 3rd Qu.:22.00 3rd Qu.:1736
Max. :2013 Max. :12.000 Max. :31.00 Max. :2400
dep_delay arr_time arr_delay carrier
Min. : -43.000 Min. : 1 Min. : -70.000 Length:95114
1st Qu.: -5.000 1st Qu.:1116 1st Qu.: -16.000 Class :character
Median : -2.000 Median :1547 Median : -5.000 Mode :character
Mean : 8.356 Mean :1520 Mean : 4.105
3rd Qu.: 6.000 3rd Qu.:1943 3rd Qu.: 11.000
Max. :1301.000 Max. :2400 Max. :1272.000
tailnum flight origin dest
Length:95114 Min. : 1 Length:95114 Length:95114
Class :character 1st Qu.: 523 Class :character Class :character
Mode :character Median :1493 Mode :character Mode :character
Mean :1954
3rd Qu.:3386
Max. :8500
air_time distance hour minute
Min. : 20 Min. : 80 Min. : 0.00 Min. : 0.00
1st Qu.: 85 1st Qu.: 502 1st Qu.: 9.00 1st Qu.:17.00
Median :133 Median : 872 Median :13.00 Median :32.00
Mean :154 Mean :1039 Mean :13.12 Mean :32.09
3rd Qu.:195 3rd Qu.:1389 3rd Qu.:17.00 3rd Qu.:50.00
Max. :676 Max. :4983 Max. :24.00 Max. :59.00

Summay of planes

tailnum year type manufacturer
Length:3182 Min. : -14 Length:3182 Length:3182
Class :character 1st Qu.: 67 Class :character Class :character
Mode :character Median :1998 Mode :character Mode :character
Mean :1437
3rd Qu.:2003
Max. :2013
model engines seats speed
Length:3182 Min. : 1.0 Min. : 0.0 Length:3182
Class :character 1st Qu.: 2.0 1st Qu.: 17.0 Class :character
Mode :character Median : 2.0 Median :140.0 Mode :character
Mean : 606.9 Mean :107.3
3rd Qu.:1243.0 3rd Qu.:178.0
Max. :2351.0 Max. :450.0
engine
Length:3182
Class :character
Mode :character

Summay of weather

origin               year          month             day

Length:3972 Min. :2013 Min. : 1.0 Min. : 1.00
Class :character 1st Qu.:2013 1st Qu.: 4.0 1st Qu.: 8.00
Mode :character Median :2013 Median : 7.0 Median :16.00
Mean :2013 Mean : 122.8 Mean :15.77
3rd Qu.:2013 3rd Qu.: 11.0 3rd Qu.:23.00
Max. :2013 Max. :4963.0 Max. :31.00
hour temp dewp humid
Min. : 0.00 Min. :-16.00 Min. :-9.04 Min. : 1.00
1st Qu.: 7.00 1st Qu.: 30.02 1st Qu.:24.98 1st Qu.: 54.43
Median :12.00 Median : 48.02 Median :41.00 Median : 81.95
Mean :11.86 Mean : 45.75 Mean :40.54 Mean : 836.22
3rd Qu.:17.00 3rd Qu.: 68.00 3rd Qu.:57.02 3rd Qu.:1120.00
Max. :23.00 Max. :262.00 Max. :75.92 Max. :6181.00
wind_dir wind_speed wind_gust precip Min. : 0.0 Min. : 0.000 Min. : 0.000 Min. :0
1st Qu.:120.0 1st Qu.: 9.206 1st Qu.: 6.622 1st Qu.:0
Median :230.0 Median : 701.000 Median : 10.594 Median :0
Mean :196.6 Mean : 738.039 Mean : 11.111 Mean :0
3rd Qu.:280.0 3rd Qu.:1439.000 3rd Qu.: 14.567 3rd Qu.:0
Max. :360.0 Max. :2357.000 Max. :1206.432 Max. :0
pressure visib
Min. : 986.4 Min. : 0.000
1st Qu.:1012.7 1st Qu.:10.000
Median :1017.5 Median :10.000
Mean :1017.7 Mean : 9.565
3rd Qu.:1022.8 3rd Qu.:10.000
Max. :1041.9 Max. :10.000

Head of airlines

carrier name
9E Endeavor Air Inc.
AA American Airlines Inc.
AS Alaska Airlines Inc.
B6 JetBlue Airways
DL Delta Air Lines Inc.
EV ExpressJet Airlines Inc.
F9 Frontier Airlines Inc.
FL AirTran Airways Corporation
HA Hawaiian Airlines Inc.
MQ Envoy Air
OO SkyWest Airlines Inc.
UA United Air Lines Inc.
US US Airways Inc.
VX Virgin America
WN Southwest Airlines Co.
YV Mesa Airlines Inc.

Head of airports

faa name lat lon alt tz dst
04G Lansdowne Airport 41.13047 -80.61958 1044 -5 A
06A Moton Field Municipal Airport 32.46057 -85.68003 264 -5 A
06C Schaumburg Regional 41.98934 -88.10124 801 -6 A
06N Randall Airport 41.43191 -74.39156 523 -5 A
09J Jekyll Island Airport 31.07447 -81.42778 11 -4 A
0A9 Elizabethton Municipal Airport 36.37122 -82.17342 1593 -4 A
0G6 Williams County Airport 41.46731 -84.50678 730 -5 A
0G7 Finger Lakes Regional Airport 42.88356 -76.78123 492 -5 A
0P2 Shoestring Aviation Airfield 39.79482 -76.64719 1000 -5 U
0S9 Jefferson County Intl 48.05381 -122.81064 108 -8 A
0W3 Harford County Airport 39.56684 -76.20240 409 -5 A
10C Galt Field Airport 42.40289 -88.37511 875 -6 U
17G Port Bucyrus-Crawford County Airport 40.78156 -82.97481 1003 -5 A
19A Jackson County Airport 34.17586 -83.56160 951 -4 U
1A3 Martin Campbell Field Airport 35.01581 -84.34683 1789 -4 A
1B9 Mansfield Municipal 42.00013 -71.19677 122 -5 A
1C9 Frazier Lake Airpark 54.01333 -124.76833 152 -8 A
1CS Clow International Airport 41.69597 -88.12923 670 -6 U
1G3 Kent State Airport 41.15139 -81.41511 1134 -4 A
1OH Fortman Airport 40.55533 -84.38662 885 -5 U

Head of flights

year month day dep_time dep_delay arr_time arr_delay carrier tailnum flight origin dest air_time distance hour minute
2013 1 1 517 2 830 11 UA N14228 1545 EWR IAH 227 1400 5 17
2013 1 1 533 4 850 20 UA N24211 1714 LGA IAH 227 1416 5 33
2013 1 1 542 2 923 33 AA N619AA 1141 JFK MIA 160 1089 5 42
2013 1 1 544 -1 1004 -18 B6 N804JB 725 JFK BQN 183 1576 5 44
2013 1 1 554 -6 812 -25 DL N668DN 461 LGA ATL 116 762 5 54
2013 1 1 554 -4 740 12 UA N39463 1696 EWR ORD 150 719 5 54
2013 1 1 555 -5 913 19 B6 N516JB 507 EWR FLL 158 1065 5 55
2013 1 1 557 -3 709 -14 EV N829AS 5708 LGA IAD 53 229 5 57
2013 1 1 557 -3 838 -8 B6 N593JB 79 JFK MCO 140 944 5 57
2013 1 1 558 -2 753 8 AA N3ALAA 301 LGA ORD 138 733 5 58
2013 1 1 558 -2 849 -2 B6 N793JB 49 JFK PBI 149 1028 5 58
2013 1 1 558 -2 853 -3 B6 N657JB 71 JFK TPA 158 1005 5 58
2013 1 1 558 -2 924 7 UA N29129 194 JFK LAX 345 2475 5 58
2013 1 1 558 -2 923 -14 UA N53441 1124 EWR SFO 361 2565 5 58
2013 1 1 559 -1 941 31 AA N3DUAA 707 LGA DFW 257 1389 5 59
2013 1 1 559 0 702 -4 B6 N708JB 1806 JFK BOS 44 187 5 59
2013 1 1 559 -1 854 -8 UA N76515 1187 EWR LAS 337 2227 5 59
2013 1 1 600 0 851 -7 B6 N595JB 371 LGA FLL 152 1076 6 0
2013 1 1 600 0 837 12 MQ N542MQ 4650 LGA ATL 134 762 6 0
2013 1 1 601 1 844 -6 B6 N644JB 343 EWR PBI 147 1023 6 1

Head of planes

tailnum year type manufacturer model engines seats speed engine
N10156 2004 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N102UW 6 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 817 8 1783 JFK
N103US 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
419 -8 12 AIRBUS INDUSTRIE CLE 817 8 3272 LGA
N10575 2002 Fixed wing multi engine EMBRAER EMB-145LR 2 55 LGA
N105UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
N107US 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 819 8 1626 EWR
N108UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
228 140 Fixed wing multi engine AIRBUS INDUSTRIE IAD 820 8 5716 JFK
N110UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
N11106 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N11107 2002 Fixed wing multi engine EMBRAER EMB-145XR 821 55 3528 LGA
N11109 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
292 6 Fixed wing multi engine EMBRAER EMB-145XR 821 8 4213 LGA
N11119 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
2133 -2 12 301 PHX 821 8 1043 EWR
N11127 2003 Fixed wing multi engine EMBRAER EMB-145XR 821 55 JFK
N11137 2003 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N11140 2003 Fixed wing multi engine EMBRAER EMB-145XR 822 8 2912 JFK
N11150 2003 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan

Head of weather

origin year month day hour temp dewp humid wind_dir wind_speed wind_gust precip pressure visib
EWR 2013 1 1 0 37.04 21.92 4152.00 230 2130.00000 11.918651 0 1013.9 10
EWR 2013 1 1 1 37.04 21.92 53.97 230 13.80936 15.891535 0 1013.0 10
EWR 2013 1 1 2 37.94 21.92 52.09 230 12.65858 14.567241 0 1012.6 10
JFK 2013 1005 1 21 -3.00 28.04 425.00 240 2138.00000 13.242946 0 1012.5 10
JFK 2013 1 1 21 39.92 28.04 1183.00 250 2140.00000 14.567241 0 1012.2 10
JFK 2013 1 1 10 39.02 28.04 64.43 260 2144.00000 14.567241 0 1011.9 10
EWR 2013 1 1 11 37.94 28.04 67.21 240 11.50780 13.242946 0 1012.4 10
EWR 2013 1 1 12 39.02 28.04 64.43 240 14.96014 17.215830 0 1012.2 10
JFK 2013 1 1 19 39.02 24.08 54.68 280 13.80936 15.891535 0 1010.8 10
EWR 2013 1 1 20 37.94 24.08 57.04 290 9.20624 10.594357 0 1011.9 10
LGA 2013 431 2 22 93.00 15.08 3257.00 310 2213.00000 17.215830 0 1015.2 10
EWR 2013 1 2 2 30.02 12.92 4645.00 320 2215.00000 21.188714 0 1016.0 10
JFK 2013 1 2 3 28.94 12.02 48.69 320 18.41248 21.188714 0 1016.5 10
EWR 2013 1 2 4 28.04 10.94 48.15 310 16.11092 18.540125 0 1016.4 10
JFK 2013 1 2 22 145.00 8.96 2454.00 320 2220.00000 17.215830 0 1016.6 10
LGA 2013 1 2 9 24.08 8.96 3660.00 330 2222.00000 14.567241 0 1016.9 10
EWR 2013 1 2 10 24.08 8.96 51.93 330 6.90468 7.945768 0 1016.9 10
EWR 2013 1 2 11 24.08 8.96 51.93 310 3.45234 3.972884 0 1017.2 10
JFK 2013 2521 2 22 182.00 12.02 161.00 300 2234.00000 17.215830 0 1017.5 10
JFK 2013 1 2 22 32.00 12.92 2599.00 290 2236.00000 14.567241 0 1017.1 10

MongoDB

The databrames containing the SQL tables are stored into collections in MongoDB.

library(mongolite)
for(i in 1:length(tablesNamesSQL)){
  c<-mongo(collection = tablesNamesSQL[i],db="flights")
  c$insert(tablesSQL[[i]])
}

A flights database is created containing a collection for each dataframe.

Each row of data in the dataframes is stored as a document in the respective collection. Here is an example for airlines:

The data is read back into r. We read all the collections available in the flights database stored in MongoDB.

c<-mongo(db="flights")
p<-c$run('{"listCollections":1}')
collectionsNamesMongo <- p$cursor$firstBatch$name
collectionsMongo<-list()

for(i in 1:length(collectionsNamesMongo)){
  c<-mongo(collection = collectionsNamesMongo[i],db="flights")
  collectionsMongo[[i]]<-c$find('{}')
}
names(collectionsMongo)<-collectionsNamesMongo

We inspect the collections to see if the data was loaded.

inspectDataFrameList(collectionsMongo)

Names of data frames

[1] “planes” “flights” “airports” “airlines” “weather”

Summay of airlines

carrier name
Length:16 Length:16
Class :character Class :character
Mode :character Mode :character

Summay of airports

 faa                name                lat             lon

Length:1397 Length:1397 Min. :19.72 Min. :-176.65
Class :character Class :character 1st Qu.:34.27 1st Qu.:-119.32
Mode :character Mode :character Median :40.14 Median : -94.91
Mean :41.75 Mean :-103.69
3rd Qu.:45.25 3rd Qu.: -82.54
Max. :72.27 Max. : 174.11
alt tz dst
Min. : -54 Min. :-11.000 Length:1397
1st Qu.: 70 1st Qu.: -8.000 Class :character
Median : 483 Median : -6.000 Mode :character
Mean :1006 Mean : -6.421
3rd Qu.:1076 3rd Qu.: -5.000
Max. :9078 Max. : 8.000

Summay of flights

  year          month             day           dep_time

Min. :2013 Min. : 1.000 Min. : 1.00 Min. : 1
1st Qu.:2013 1st Qu.: 1.000 1st Qu.: 7.00 1st Qu.: 906
Median :2013 Median :10.000 Median :14.00 Median :1357
Mean :2013 Mean : 8.062 Mean :14.64 Mean :1344
3rd Qu.:2013 3rd Qu.:11.000 3rd Qu.:22.00 3rd Qu.:1736
Max. :2013 Max. :12.000 Max. :31.00 Max. :2400
dep_delay arr_time arr_delay carrier
Min. : -43.000 Min. : 1 Min. : -70.000 Length:95114
1st Qu.: -5.000 1st Qu.:1116 1st Qu.: -16.000 Class :character
Median : -2.000 Median :1547 Median : -5.000 Mode :character
Mean : 8.356 Mean :1520 Mean : 4.105
3rd Qu.: 6.000 3rd Qu.:1943 3rd Qu.: 11.000
Max. :1301.000 Max. :2400 Max. :1272.000
tailnum flight origin dest
Length:95114 Min. : 1 Length:95114 Length:95114
Class :character 1st Qu.: 523 Class :character Class :character
Mode :character Median :1493 Mode :character Mode :character
Mean :1954
3rd Qu.:3386
Max. :8500
air_time distance hour minute
Min. : 20 Min. : 80 Min. : 0.00 Min. : 0.00
1st Qu.: 85 1st Qu.: 502 1st Qu.: 9.00 1st Qu.:17.00
Median :133 Median : 872 Median :13.00 Median :32.00
Mean :154 Mean :1039 Mean :13.12 Mean :32.09
3rd Qu.:195 3rd Qu.:1389 3rd Qu.:17.00 3rd Qu.:50.00
Max. :676 Max. :4983 Max. :24.00 Max. :59.00

Summay of planes

tailnum year type manufacturer
Length:3182 Min. : -14 Length:3182 Length:3182
Class :character 1st Qu.: 67 Class :character Class :character
Mode :character Median :1998 Mode :character Mode :character
Mean :1437
3rd Qu.:2003
Max. :2013
model engines seats speed
Length:3182 Min. : 1.0 Min. : 0.0 Length:3182
Class :character 1st Qu.: 2.0 1st Qu.: 17.0 Class :character
Mode :character Median : 2.0 Median :140.0 Mode :character
Mean : 606.9 Mean :107.3
3rd Qu.:1243.0 3rd Qu.:178.0
Max. :2351.0 Max. :450.0
engine
Length:3182
Class :character
Mode :character

Summay of weather

origin               year          month             day

Length:3972 Min. :2013 Min. : 1.0 Min. : 1.00
Class :character 1st Qu.:2013 1st Qu.: 4.0 1st Qu.: 8.00
Mode :character Median :2013 Median : 7.0 Median :16.00
Mean :2013 Mean : 122.8 Mean :15.77
3rd Qu.:2013 3rd Qu.: 11.0 3rd Qu.:23.00
Max. :2013 Max. :4963.0 Max. :31.00
hour temp dewp humid
Min. : 0.00 Min. :-16.00 Min. :-9.04 Min. : 1.00
1st Qu.: 7.00 1st Qu.: 30.02 1st Qu.:24.98 1st Qu.: 54.43
Median :12.00 Median : 48.02 Median :41.00 Median : 81.95
Mean :11.86 Mean : 45.75 Mean :40.54 Mean : 836.22
3rd Qu.:17.00 3rd Qu.: 68.00 3rd Qu.:57.02 3rd Qu.:1120.00
Max. :23.00 Max. :262.00 Max. :75.92 Max. :6181.00
wind_dir wind_speed wind_gust precip Min. : 0.0 Min. : 0.000 Min. : 0.000 Min. :0
1st Qu.:120.0 1st Qu.: 9.206 1st Qu.: 6.622 1st Qu.:0
Median :230.0 Median : 701.000 Median : 10.594 Median :0
Mean :196.6 Mean : 738.039 Mean : 11.111 Mean :0
3rd Qu.:280.0 3rd Qu.:1439.000 3rd Qu.: 14.567 3rd Qu.:0
Max. :360.0 Max. :2357.000 Max. :1206.432 Max. :0
pressure visib
Min. : 986.4 Min. : 0.000
1st Qu.:1012.7 1st Qu.:10.000
Median :1017.5 Median :10.000
Mean :1017.7 Mean : 9.565
3rd Qu.:1022.8 3rd Qu.:10.000
Max. :1041.9 Max. :10.000

Head of airlines

carrier name
9E Endeavor Air Inc.
AA American Airlines Inc.
AS Alaska Airlines Inc.
B6 JetBlue Airways
DL Delta Air Lines Inc.
EV ExpressJet Airlines Inc.
F9 Frontier Airlines Inc.
FL AirTran Airways Corporation
HA Hawaiian Airlines Inc.
MQ Envoy Air
OO SkyWest Airlines Inc.
UA United Air Lines Inc.
US US Airways Inc.
VX Virgin America
WN Southwest Airlines Co.
YV Mesa Airlines Inc.

Head of airports

faa name lat lon alt tz dst
04G Lansdowne Airport 41.13047 -80.61958 1044 -5 A
06A Moton Field Municipal Airport 32.46057 -85.68003 264 -5 A
06C Schaumburg Regional 41.98934 -88.10124 801 -6 A
06N Randall Airport 41.43191 -74.39156 523 -5 A
09J Jekyll Island Airport 31.07447 -81.42778 11 -4 A
0A9 Elizabethton Municipal Airport 36.37122 -82.17342 1593 -4 A
0G6 Williams County Airport 41.46731 -84.50678 730 -5 A
0G7 Finger Lakes Regional Airport 42.88356 -76.78123 492 -5 A
0P2 Shoestring Aviation Airfield 39.79482 -76.64719 1000 -5 U
0S9 Jefferson County Intl 48.05381 -122.81064 108 -8 A
0W3 Harford County Airport 39.56684 -76.20240 409 -5 A
10C Galt Field Airport 42.40289 -88.37511 875 -6 U
17G Port Bucyrus-Crawford County Airport 40.78156 -82.97481 1003 -5 A
19A Jackson County Airport 34.17586 -83.56160 951 -4 U
1A3 Martin Campbell Field Airport 35.01581 -84.34683 1789 -4 A
1B9 Mansfield Municipal 42.00013 -71.19677 122 -5 A
1C9 Frazier Lake Airpark 54.01333 -124.76833 152 -8 A
1CS Clow International Airport 41.69597 -88.12923 670 -6 U
1G3 Kent State Airport 41.15139 -81.41511 1134 -4 A
1OH Fortman Airport 40.55533 -84.38662 885 -5 U

Head of flights

year month day dep_time dep_delay arr_time arr_delay carrier tailnum flight origin dest air_time distance hour minute
2013 1 1 517 2 830 11 UA N14228 1545 EWR IAH 227 1400 5 17
2013 1 1 533 4 850 20 UA N24211 1714 LGA IAH 227 1416 5 33
2013 1 1 542 2 923 33 AA N619AA 1141 JFK MIA 160 1089 5 42
2013 1 1 544 -1 1004 -18 B6 N804JB 725 JFK BQN 183 1576 5 44
2013 1 1 554 -6 812 -25 DL N668DN 461 LGA ATL 116 762 5 54
2013 1 1 554 -4 740 12 UA N39463 1696 EWR ORD 150 719 5 54
2013 1 1 555 -5 913 19 B6 N516JB 507 EWR FLL 158 1065 5 55
2013 1 1 557 -3 709 -14 EV N829AS 5708 LGA IAD 53 229 5 57
2013 1 1 557 -3 838 -8 B6 N593JB 79 JFK MCO 140 944 5 57
2013 1 1 558 -2 753 8 AA N3ALAA 301 LGA ORD 138 733 5 58
2013 1 1 558 -2 849 -2 B6 N793JB 49 JFK PBI 149 1028 5 58
2013 1 1 558 -2 853 -3 B6 N657JB 71 JFK TPA 158 1005 5 58
2013 1 1 558 -2 924 7 UA N29129 194 JFK LAX 345 2475 5 58
2013 1 1 558 -2 923 -14 UA N53441 1124 EWR SFO 361 2565 5 58
2013 1 1 559 -1 941 31 AA N3DUAA 707 LGA DFW 257 1389 5 59
2013 1 1 559 0 702 -4 B6 N708JB 1806 JFK BOS 44 187 5 59
2013 1 1 559 -1 854 -8 UA N76515 1187 EWR LAS 337 2227 5 59
2013 1 1 600 0 851 -7 B6 N595JB 371 LGA FLL 152 1076 6 0
2013 1 1 600 0 837 12 MQ N542MQ 4650 LGA ATL 134 762 6 0
2013 1 1 601 1 844 -6 B6 N644JB 343 EWR PBI 147 1023 6 1

Head of planes

tailnum year type manufacturer model engines seats speed engine
N10156 2004 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N102UW 6 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 817 8 1783 JFK
N103US 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
419 -8 12 AIRBUS INDUSTRIE CLE 817 8 3272 LGA
N10575 2002 Fixed wing multi engine EMBRAER EMB-145LR 2 55 LGA
N105UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
N107US 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 819 8 1626 EWR
N108UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
228 140 Fixed wing multi engine AIRBUS INDUSTRIE IAD 820 8 5716 JFK
N110UW 1999 Fixed wing multi engine AIRBUS INDUSTRIE A320-214 2 182 Turbo-fan
N11106 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N11107 2002 Fixed wing multi engine EMBRAER EMB-145XR 821 55 3528 LGA
N11109 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
292 6 Fixed wing multi engine EMBRAER EMB-145XR 821 8 4213 LGA
N11119 2002 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
2133 -2 12 301 PHX 821 8 1043 EWR
N11127 2003 Fixed wing multi engine EMBRAER EMB-145XR 821 55 JFK
N11137 2003 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan
N11140 2003 Fixed wing multi engine EMBRAER EMB-145XR 822 8 2912 JFK
N11150 2003 Fixed wing multi engine EMBRAER EMB-145XR 2 55 Turbo-fan

Head of weather

origin year month day hour temp dewp humid wind_dir wind_speed wind_gust precip pressure visib
EWR 2013 1 1 0 37.04 21.92 4152.00 230 2130.00000 11.918651 0 1013.9 10
EWR 2013 1 1 1 37.04 21.92 53.97 230 13.80936 15.891535 0 1013.0 10
EWR 2013 1 1 2 37.94 21.92 52.09 230 12.65858 14.567241 0 1012.6 10
JFK 2013 1005 1 21 -3.00 28.04 425.00 240 2138.00000 13.242946 0 1012.5 10
JFK 2013 1 1 21 39.92 28.04 1183.00 250 2140.00000 14.567241 0 1012.2 10
JFK 2013 1 1 10 39.02 28.04 64.43 260 2144.00000 14.567241 0 1011.9 10
EWR 2013 1 1 11 37.94 28.04 67.21 240 11.50780 13.242946 0 1012.4 10
EWR 2013 1 1 12 39.02 28.04 64.43 240 14.96014 17.215830 0 1012.2 10
JFK 2013 1 1 19 39.02 24.08 54.68 280 13.80936 15.891535 0 1010.8 10
EWR 2013 1 1 20 37.94 24.08 57.04 290 9.20624 10.594357 0 1011.9 10
LGA 2013 431 2 22 93.00 15.08 3257.00 310 2213.00000 17.215830 0 1015.2 10
EWR 2013 1 2 2 30.02 12.92 4645.00 320 2215.00000 21.188714 0 1016.0 10
JFK 2013 1 2 3 28.94 12.02 48.69 320 18.41248 21.188714 0 1016.5 10
EWR 2013 1 2 4 28.04 10.94 48.15 310 16.11092 18.540125 0 1016.4 10
JFK 2013 1 2 22 145.00 8.96 2454.00 320 2220.00000 17.215830 0 1016.6 10
LGA 2013 1 2 9 24.08 8.96 3660.00 330 2222.00000 14.567241 0 1016.9 10
EWR 2013 1 2 10 24.08 8.96 51.93 330 6.90468 7.945768 0 1016.9 10
EWR 2013 1 2 11 24.08 8.96 51.93 310 3.45234 3.972884 0 1017.2 10
JFK 2013 2521 2 22 182.00 12.02 161.00 300 2234.00000 17.215830 0 1017.5 10
JFK 2013 1 2 22 32.00 12.92 2599.00 290 2236.00000 14.567241 0 1017.1 10

Verification SQL vs MongoDB

if(tablesSQL$airlines==collectionsMongo$airlines && 
   tablesSQL$airports==collectionsMongo$airports &&
   tablesSQL$flights==collectionsMongo$flights &&
   tablesSQL$planes==collectionsMongo$planes &&
   tablesSQL$weather==collectionsMongo$weather) {
  print("Data in both databases is the same")
} else {
  print("Data is not the same in both databases")
}
## [1] "Data in both databases is the same"

SQL(MySQL) vs. non-SQL(MongoDB)

Data stored in both databases can be imported to equal dataframes. But the SQL database is more conducive to operations where data from different tables needs to be related. For example using primary keys in tables and performing joins. Data in Mongo sits in collections which are not natively related, although MongoDB offers a SQL abstraction. Also, rows of data in Mongo are stored as documents with an identifier for each. This seems excessive and overweight. But the idea of collections open the option of storing documents of different kinds within the collection. This means that we can potentially store all of our dataframes in Mongo under the same collection, even as each data frame contains different columns. This is not possible in SQL, where to do this we would have to add columns for all the tables, thus creating data that is very space since columns will only be used when data for the respective table is present, and will be empty otherwise. In Mongo, event with our current setup where we have different collections for the different dataframes, if data were missing for a specific column for different rows, those columns will simply not be present in the collection. This reduces sparsity compared to SQL.

The following is a comparison table (source: https://hackernoon.com/mongodb-vs-mysql-comparison-which-database-is-better-e714b699c38b)