Import Large Data Using DatabaseDatastore Object and MySQL Native Interface
This example shows how to use the databaseDatastore function to create a DatabaseDatastore object for accessing collections of data stored in a MySQL® database using the MySQL native interface. After creating a DatabaseDatastore object, you can preview data, read data in chunks, and read every record in the data set.
To analyze large data, you can run algorithms on large data sets using a tall array. Alternatively, you can write a MapReduce algorithm that defines the chunking and reduction of the data.
This example uses a preconfigured MySQL data source to create the database connection. For more information, see the databaseConnectionOptions function.
Create DatabaseDatastore Object
Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password.
datasource = "MySQLDataSource"; username = "root"; password = "matlab"; conn = mysql(datasource,username,password);
Create a DatabaseDatastore object using the database connection and an SQL query. This query retrieves all data from the airlinesmall table.
sqlquery = "select * from airlinesmall";
dbds = databaseDatastore(conn,sqlquery); Preview Data in DatabaseDatastore Object
Preview the first eight records in the data set returned by executing the SQL query.
preview(dbds)
ans=8×29 table
1990 9 11 2 1810 1812 1939 1930 "AA" 1426 "NA" 89 78 "NA" 9 -2 "RST" "ORD" 268 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 27 6 1353 1355 1634 1640 "US" 112 "NA" 161 165 "NA" -6 -2 "TPA" "SYR" 1104 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 23 2 1057 1055 1205 1155 "US" 1621 "NA" 68 60 "NA" 10 2 "ROC" "EWR" 246 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 8 1 1515 1440 1609 1535 "NW" 749 "NA" 54 55 "NA" 34 35 "MSP" "FSD" 197 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 19 5 1130 1120 1203 1154 "UA" 369 "NA" 93 94 "NA" 9 10 "BUF" "ORD" 473 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 12 5 1755 1733 1858 1820 "DL" 590 "NA" 63 47 "NA" 38 22 "BOS" "BGR" 201 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 11 22 4 1345 1355 1530 1549 "MQ" 4982 "#NAME?" 105 114 "90" -19 -10 "JAX" "MIA" 334 "8" "7" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 11 26 1 2105 2110 2209 2237 "AA" 1947 "N3BäA1" 64 87 "47" -28 -5 "SFO" "LAX" 337 "6" "11" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Read Data in DatabaseDatastore Object
Read the first 10 records.
dbds.ReadSize = 10; read(dbds)
ans=10×29 table
1990 9 11 2 1810 1812 1939 1930 "AA" 1426 "NA" 89 78 "NA" 9 -2 "RST" "ORD" 268 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 27 6 1353 1355 1634 1640 "US" 112 "NA" 161 165 "NA" -6 -2 "TPA" "SYR" 1104 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 23 2 1057 1055 1205 1155 "US" 1621 "NA" 68 60 "NA" 10 2 "ROC" "EWR" 246 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 8 1 1515 1440 1609 1535 "NW" 749 "NA" 54 55 "NA" 34 35 "MSP" "FSD" 197 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 19 5 1130 1120 1203 1154 "UA" 369 "NA" 93 94 "NA" 9 10 "BUF" "ORD" 473 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 12 5 1755 1733 1858 1820 "DL" 590 "NA" 63 47 "NA" 38 22 "BOS" "BGR" 201 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 11 22 4 1345 1355 1530 1549 "MQ" 4982 "#NAME?" 105 114 "90" -19 -10 "JAX" "MIA" 334 "8" "7" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 11 26 1 2105 2110 2209 2237 "AA" 1947 "N3BäA1" 64 87 "47" -28 -5 "SFO" "LAX" 337 "6" "11" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 11 18 7 1407 1415 1442 1457 "DL" 628 "N521D1" 35 42 "22" -15 -8 "OKC" "TUL" 111 "3" "10" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 12 23 7 1327 1310 1530 1530 "WN" 658 "N789@@" 63 80 "48" 0 17 "SNA" "PHX" 338 "5" "10" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Read the DatabaseDatastore object two more times by using the counter n. Read 10 records at a time.
n = 0; while(hasdata(dbds) && n~=2) read(dbds) n = n+1; end
ans=10×29 table
2001 12 8 6 1402 1410 1642 1626 "NW" 809 "N324N2" 220 196 "199" 16 -8 "BOS" "MEM" 1139 "4" "17" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 12 22 6 1707 1715 1823 1821 "UA" 725 "N361ä1" 136 126 "104" 2 -8 "RIC" "ORD" 642 "20" "12" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2001 12 9 7 656 650 824 823 "DL" 1224 "N37438" 88 93 "73" 1 6 "ATL" "BWI" 576 "4" "11" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 19 3 632 640 748 756 "NW" 126 "N773NC" 76 76 "58" -8 -8 "BIS" "MSP" 386 "9" "9" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 10 1 927 930 1031 1031 "UA" 501 "N304UA" 124 121 "101" 0 -3 "DFW" "DEN" 641 "5" "18" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 15 6 1120 1115 1401 1413 "DL" 406 "N1402A" 161 178 "132" -12 5 "FLL" "LGA" 1076 "7" "22" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 8 6 1557 1600 1703 1711 "HP" 660 "N334AW" 66 71 "52" -8 -3 "PHX" "SNA" 338 "4" "10" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 26 3 2026 1840 47 2257 "AA" 636 "N420AA" 141 137 "115" 110 106 "PHX" "DFW" 868 "6" "20" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 6 5 3 2032 2031 2233 2248 "AA" 3163 "N4WFAA" 241 257 "220" -15 1 "STL" "SMF" 1679 "8" "13" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
2002 7 25 4 1032 1035 1853 1852 "US" 180 "N174UW" 321 317 "289" 1 -3 "SEA" "PHL" 2378 "9" "23" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
ans=10×29 table
1987 10 30 5 1329 1329 1434 1436 "US" 683 "NA" 65 67 "NA" -2 0 "LGA" "BUF" 292 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 7 6 1316 1315 1713 1647 "TW" 810 "NA" 177 152 "NA" 26 1 "STL" "BOS" 1046 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 28 6 815 815 1015 1015 "WN" 441 "NA" 120 120 "NA" 0 0 "BNA" "HOU" 670 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 2 1 700 700 800 800 "NW" 790 "NA" 60 60 "NA" 0 0 "BTR" "MEM" 319 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 14 6 840 840 1127 1120 "CO" 733 "NA" 167 160 "NA" 7 0 "EWR" "MCO" 938 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 1 7 1625 1625 1823 1758 "DL" 957 "NA" 118 93 "NA" 25 0 "EWR" "CVG" 569 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1987 11 26 4 1314 1315 1538 1542 "AA" 398 "NA" 84 87 "NA" -4 -1 "BNA" "RDU" 443 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1992 7 18 6 1538 1540 1703 1720 "NW" 199 "NA" 145 160 "NA" -17 -2 "DCA" "MSP" 931 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1992 7 19 7 932 932 1130 1052 "AA" 1433 "NA" 118 80 "NA" 38 0 "DFW" "HRL" 461 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1992 8 4 2 NaN 1815 NaN 1940 "US" 127 "NA" NaN 85 "NA" NaN NaN "EWR" "PIT" 319 "NA" "NA" 1 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Reset DatabaseDatastore Object
Reset the DatabaseDatastore object to its original state, where no data has been read from it. Resetting allows you to reread from the same DatabaseDatastore object.
reset(dbds)
Read Every Record in DatabaseDatastore Object
Read every record in the DatabaseDatastore object in increments of 50,000 records at a time.
dbds.ReadSize = 50000; data = readall(dbds);
Display the first three records of the full data set.
head(data,3)
ans=3×29 table
1990 9 11 2 1810 1812 1939 1930 "AA" 1426 "NA" 89 78 "NA" 9 -2 "RST" "ORD" 268 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 27 6 1353 1355 1634 1640 "US" 112 "NA" 161 165 "NA" -6 -2 "TPA" "SYR" 1104 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
1990 10 23 2 1057 1055 1205 1155 "US" 1621 "NA" 68 60 "NA" 10 2 "ROC" "EWR" 246 "NA" "NA" 0 "NA" 0 "NA" "NA" "NA" "NA" "NA"
Close DatabaseDatastore Object and Database Connection
close(dbds)
See Also
mysql | close | hasdata | preview | read | readall | databaseDatastore
