Import Large PostgreSQL Data Using DatabaseDatastore Object

This example shows how to use the databaseDatastore function to create a DatabaseDatastore object for accessing collections of data stored in a PostgreSQL database. 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 PostgreSQL data source to create the database connection. For more information, see the databaseConnectionOptions function.

Create DatabaseDatastore Object

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(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 data and display the first few records.

data = read(dbds);
head(data)

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 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=10000×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
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    ________    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    1997     12          10            3         1812         1730        2149         2047           "UA"           1456       "N361UA"           157                137           "139"        62          42       "OAK"     "DEN"       957       "8"       "10"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1997     12          17            3         1806         1800        1932         1933           "UA"            941       "N1844U"           146                153           "122"        -1           6       "ORD"     "DEN"       888       "7"       "17"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1997     12          27            6         1803         1805        1947         1945           "UA"           2770       "N920UA"           104                100           "86"          2          -2       "PHX"     "DEN"       602       "7"       "11"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1997     12          27            6          807          800        1138         1130           "UA"            258       "N556UA"           151                150           "112"         8           7       "SFO"     "DEN"       967       "8"       "31"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1997     12          20            6         1501         1120        1828         1432           "UA"           1164       "N991UA"           147                132           "117"       236         221       "SMF"     "DEN"       910       "18"      "12"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          18            7         1207         1211        1358         1355           "HP"           2870       "N186AW"           111                104           "85"          3          -4       "COS"     "PHX"       551       "8"       "18"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          22            4         2022            0        2151            0           "AA"           1403       "N218AA"           149                163           "130"       -18          -4       "DFW"     "PHX"       868       "4"       "15"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1           6            2          730          730         840          840           "WN"            760       "N368"              70                 70           "56"          0           0       "ELP"     "PHX"       347       "5"       "9"           0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1           5            1          730          730        1040         1100           "HP"           2009       "N633AW"           310                330           "282"       -20           0       "JFK"     "PHX"      2153       "7"       "21"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          16            5         1415         1415        1623         1615           "WN"           1657       "N355"              68                 60           "44"          8           0       "LAS"     "PHX"       256       "4"       "20"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          28            3         1017         1000        1222         1210           "WN"            924       "N371"              65                 70           "57"         12          17       "LAX"     "PHX"       370       "2"       "6"           0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1           7            3         2018         2020        2231         2242           "NW"            107       "N540US"           193                202           "164"       -11          -2       "MSP"     "PHX"      1276       "5"       "24"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1           2            5          915          915        1113         1115           "WN"            526       "N627"              58                 60           "46"         -2           0       "ONT"     "PHX"       325       "3"       "9"           0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1           4            7         1035         1030        1332         1356           "US"            163       "N573US"           297                326           "284"       -24           5       "PHL"     "PHX"      2075       "2"       "11"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          16            5         1235         1210        1436         1415           "WN"           1222       "N342"              61                 65           "50"         21          25       "SAN"     "PHX"       304       "4"       "7"           0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1998      1          13            2          802          800        1047         1053           "UA"           2903       "N384UA"           105                113           "93"         -6           2       "SFO"     "PHX"       651       "3"       "9"           0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
      ⋮

ans=10000×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
    ____    _____    __________    _________    _______    __________    _______    __________    _____________    _________    _______    _________________    ______________    _______    ________    ________    ______    _____    ________    ______    _______    _________    ________________    ________    ____________    ____________    ________    _____________    _________________

    1988      1           5            2         2149         2045        2333         2232           "UA"            202        "NA"              44                 47           "NA"         61          64       "ORD"     "GRR"       137       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          28            4         1005         1000        1137         1131           "UA"            225        "NA"             152                151           "NA"          6           5       "ORD"     "DEN"       888       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          13            3         1159         1200        1514         1518           "UA"            248        "NA"             135                138           "NA"         -4          -1       "SFO"     "DEN"       967       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          25            1          630          630         948          944           "UA"            270        "NA"             138                134           "NA"          4           0       "LAX"     "DEN"       862       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          12            2         2005         1900        2131         2036           "UA"            297        "NA"             146                156           "NA"         55          65       "ORD"     "DEN"       888       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1           3            7         1153         1155        1202         1205           "UA"            321        "NA"              69                 70           "NA"         -3          -2       "CMH"     "ORD"       296       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1           8            5         1227         1200        1413         1330           "UA"            343        "NA"             166                150           "NA"         43          27       "DEN"     "SFO"       967       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          29            5         1456         1500        1520         1526           "UA"            364        "NA"              24                 26           "NA"         -6          -4       "HSV"     "BHM"        74       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          27            3           37           40         107          115           "UA"            384        "NA"              30                 35           "NA"         -8          -3       "SMF"     "SFO"        86       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          25            1          638          640        1119         1039           "UA"            408        "NA"             221                179           "NA"         40          -2       "BOI"     "ORD"      1437       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          11            1         1313         1130        1615         1434           "UA"            432        "NA"             122                124           "NA"        101         103       "ORD"     "BOS"       867       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          19            2          901          842        1051         1036           "UA"            455        "NA"             110                114           "NA"         15          19       "DEN"     "TUS"       639       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          12            2         1737         1730        1953         1947           "UA"            475        "NA"             256                257           "NA"          6           7       "JFK"     "DEN"      1626       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          12            2         1122         1125        1357         1407           "UA"            494        "NA"              95                102           "NA"        -10          -3       "LAS"     "DEN"       629       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          13            3         1728         1720        1957         1942           "UA"            519        "NA"             269                262           "NA"         15           8       "BOS"     "DEN"      1754       "NA"      "NA"          0              "NA"             0            "NA"            "NA"          "NA"          "NA"               "NA"       
    1988      1          12            2         1559         1500        1844         1745           "UA"            541        "NA"             285                285           "NA"         59          59       "ORD"     "SFO"      1846       "NA"      "NA"          0              "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.

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)