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
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
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
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"
⋮
ans=10000×29 table
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"
⋮
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
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
postgresql | databaseDatastore | preview | read | readall | reset | close
