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
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum     ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    NASDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    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
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum     ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    NASDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    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
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum     ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    NASDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    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
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum    ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    NASDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    _______    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    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
    Year    Month    DayofMonth    DayOfWeek    DepTime    CRSDepTime    ArrTime    CRSArrTime    UniqueCarrier    FlightNum    TailNum    ActualElapsedTime    CRSElapsedTime    AirTime    ArrDelay    DepDelay    Origin    Dest     Distance    TaxiIn    TaxiOut    Cancelled    CancellationCode    Diverted    CarrierDelay    WeatherDelay    NASDelay    SecurityDelay    LateAircraftDelay
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    _______    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    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)