sqlread
Import table data from database
Syntax
Description
data = sqlread(conn,tablename)SELECT * FROM tablename SQL statement in ANSI SQL.
conn can be any of the following connection objects:
MySQL®
PostgreSQL®
DuckDB™
SQLite
ODBC
JDBC
Use sqlread to read an entire database table into MATLAB as a table.
data = sqlread(___,Name=Value)Catalog="cat" to import data from a database table stored in
the "cat" catalog.
Examples
Retrieve metadata information when importing data from a database table using the MySQL® native interface. Import data using the sqlread function and explore the metadata information by using dot notation.
This example uses the outages.csv file, which contains outage data. The example also uses a MySQL database version 5.7.22 with the MySQL Connector/C++ driver version 8.0.15.
Create a MySQL® native interface database connection to a MySQL database using the data source name, username, and password.
datasource = "MySQLNative"; username = "root"; password = "matlab"; conn = mysql(datasource,username,password);
Load outage information into the MATLAB® workspace.
outages = readtable("outages.csv");Create the outages database table using the outage information. Use the 'ColumnType' name-value pair argument to specify the data types of the variables in the MATLAB® table.
tablename = "outages"; sqlwrite(conn,tablename,outages, ... 'ColumnType',["varchar(120)","datetime","numeric(38,16)", ... "numeric(38,16)","datetime","varchar(150)"])
Import the data into the MATLAB workspace and return metadata information about the imported data.
[data,metadata] = sqlread(conn,tablename);
View the names of the variables in the imported data.
metadata.Properties.RowNames
ans = 6×1 cell array
"'Region'"
"'OutageTime'"
"'Loss'"
"'Customers'"
"'RestorationTime'"
"'Cause'"
View the data type of each variable in the imported data.
metadata.VariableType
ans = 6×1 cell array
"'string'"
"'datetime'"
"'double'"
"'double'"
"'datetime'"
"'string'"
View the missing data value for each variable in the imported data.
metadata.FillValue
ans=6×1 cell array
1×1 missing
1×1 datetime
NaN
NaN
1×1 datetime
1×1 missing
View the indices of the missing data for each variable in the imported data.
metadata.MissingRows
ans=6×1 cell array
[ ]
[ ]
604×1 double
328×1 double
29×1 double
[ ]
Display the first eight rows of the imported data that contain missing restoration time values. data contains restoration time values in the fifth variable. Use the numeric indices to find the rows with missing data.
index = metadata.MissingRows{5,1};
nullrestoration = data(index,:);
head(nullrestoration)ans=8×6 table
"SouthEast" 23-Jan-2003 00:49:00 530.1399 2.1204e+05 NaT "winter storm"
"NorthEast" 18-Sep-2004 05:54:00 0 0 NaT "equipment fault"
"MidWest" 20-Apr-2002 16:46:00 2.3141e+04 NaN NaT "unknown"
"NorthEast" 16-Sep-2004 19:42:00 4.7180e+03 NaN NaT "unknown"
"SouthEast" 14-Sep-2005 15:45:00 1.8392e+03 3.4144e+05 NaT "severe storm"
"SouthEast" 17-Aug-2004 17:34:00 624.0955 1.7879e+05 NaT "severe storm"
"SouthEast" 28-Jan-2006 23:13:00 498.7777 NaN NaT "energy emergency"
"West" 20-Jun-2003 18:22:00 0 0 NaT "energy emergency"
Delete the outages database table using the execute function.
sqlstr = "DROP TABLE ";
sqlquery = strcat(sqlstr,tablename);
execute(conn,sqlquery)Close the database connection.
close(conn)
Customize import options when importing data from a database table using the PostgreSQL native interface. Control the import options by creating an SQLImportOptions object. Then, customize import options for different database columns. Import data using the sqlread function.
This example uses the patients.xls file, which contains the columns Gender, Location, SelfAssessedHealthStatus, and Smoker. The example uses a PostgreSQL database version 9.405 database and the libpq driver version 10.12.
Create a PostgreSQL native interface database connection to a PostgreSQL database.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(datasource,username,password);
Load patient information into the MATLAB® workspace.
patients = readtable("patients.xls");Create the patients database table using the patient information.
tablename = "patients";
sqlwrite(conn,tablename,patients)Create an SQLImportOptions object using the patients database table and the databaseImportOptions function.
opts = databaseImportOptions(conn,tablename)
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'lastname', 'gender', 'age' ... and 7 more}
VariableTypes: {'string', 'string', 'double' ... and 7 more}
SelectedVariableNames: {'lastname', 'gender', 'age' ... and 7 more}
FillValues: { <missing>, <missing>, NaN ... and 7 more }
RowFilter: <unconstrained>
VariableOptions: Show all 10 VariableOptions
Display the current import options for the variables in the SelectedVariableNames property of the SQLImportOptions object.
vars = opts.SelectedVariableNames; varOpts = getoptions(opts,vars)
varOpts =
1x10 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4) | (5) | (6) | (7) | (8) | (9) | (10)
Name: 'lastname' | 'gender' | 'age' | 'location' | 'height' | 'weight' | 'smoker' | 'systolic' | 'diastolic' | 'selfassessedhealthstatus'
Type: 'string' | 'string' | 'double' | 'string' | 'double' | 'double' | 'logical' | 'double' | 'double' | 'string'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <missing> | <missing> | NaN | <missing> | NaN | NaN | 0 | NaN | NaN | <missing>
To access sub-properties of each variable, use getoptions
Change the data types for the gender, location, smoker, and selfassessedhealthstatus variables using the setoptions function. Because the gender, location, and selfassessedhealthstatus variables indicate a finite set of repeating values, change their data type to categorical. Because the Smoker variable stores the values 0 and 1, change its data type to double. Then, display the updated import options.
opts = setoptions(opts,{'gender','location','selfassessedhealthstatus'}, ...
'Type','categorical');
opts = setoptions(opts,'smoker','Type','double');
varOpts = getoptions(opts,{'gender','location','smoker', ...
'selfassessedhealthstatus'})varOpts =
1x4 SQLVariableImportOptions array with properties:
Variable Options:
(1) | (2) | (3) | (4)
Name: 'gender' | 'location' | 'smoker' | 'selfassessedhealthstatus'
Type: 'categorical' | 'categorical' | 'double' | 'categorical'
MissingRule: 'fill' | 'fill' | 'fill' | 'fill'
FillValue: <undefined> | <undefined> | 0 | <undefined>
To access sub-properties of each variable, use getoptions
Import the patients database table using the sqlread function, and display the last eight rows of the table.
data = sqlread(conn,tablename,opts); tail(data)
lastname gender age location height weight smoker systolic diastolic selfassessedhealthstatus
___________ ______ ___ _________________________ ______ ______ ______ ________ _________ ________________________
"Foster" Female 30 St. Mary's Medical Center 70 124 0 130 91 Fair
"Gonzales" Male 48 County General Hospital 71 174 0 123 79 Good
"Bryant" Female 48 County General Hospital 66 134 0 129 73 Excellent
"Alexander" Male 25 County General Hospital 69 171 1 128 99 Good
"Russell" Male 44 VA Hospital 69 188 1 124 92 Good
"Griffin" Male 49 County General Hospital 70 186 0 119 74 Fair
"Diaz" Male 45 County General Hospital 68 172 1 136 93 Good
"Hayes" Male 48 County General Hospital 66 177 0 114 86 Fair
Display a summary of the imported data. The sqlread function applies the import options to the variables in the imported data.
summary(data)
Variables:
lastname: 100×1 string
gender: 100×1 categorical
Values:
Female 53
Male 47
age: 100×1 double
Values:
Min 25
Median 39
Max 50
location: 100×1 categorical
Values:
County General Hospital 39
St. Mary s Medical Center 24
VA Hospital 37
height: 100×1 double
Values:
Min 60
Median 67
Max 72
weight: 100×1 double
Values:
Min 111
Median 142.5
Max 202
smoker: 100×1 double
Values:
Min 0
Median 0
Max 1
systolic: 100×1 double
Values:
Min 109
Median 122
Max 138
diastolic: 100×1 double
Values:
Min 68
Median 81.5
Max 99
selfassessedhealthstatus: 100×1 categorical
Values:
Excellent 34
Fair 15
Good 40
Poor 11
Now set the filter condition to import only data for patients older than 40 year and not taller than 68 inches.
opts.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68
opts =
SQLImportOptions with properties:
ExcludeDuplicates: false
VariableNamingRule: 'preserve'
VariableNames: {'lastname', 'gender', 'age' ... and 7 more}
VariableTypes: {'string', 'categorical', 'double' ... and 7 more}
SelectedVariableNames: {'lastname', 'gender', 'age' ... and 7 more}
FillValues: { <missing>, <undefined>, NaN ... and 7 more }
RowFilter: height <= 68 & age > 40
VariableOptions: Show all 10 VariableOptions
Again, import the patients database table using the sqlread function, and display a summary of the imported data.
data = sqlread(conn,tablename,opts); summary(data)
Variables:
lastname: 24×1 string
gender: 24×1 categorical
Values:
Female 17
Male 7
age: 24×1 double
Values:
Min 41
Median 45.5
Max 50
location: 24×1 categorical
Values:
County General Hospital 13
St. Mary s Medical Center 5
VA Hospital 6
height: 24×1 double
Values:
Min 62
Median 66
Max 68
weight: 24×1 double
Values:
Min 119
Median 137
Max 194
smoker: 24×1 double
Values:
Min 0
Median 0
Max 1
systolic: 24×1 double
Values:
Min 114
Median 121.5
Max 138
diastolic: 24×1 double
Values:
Min 68
Median 81.5
Max 96
selfassessedhealthstatus: 24×1 categorical
Values:
Excellent 7
Fair 3
Good 10
Poor 4
Delete the patients database table using the execute function.
sqlquery = strcat("DROP TABLE ",tablename);
execute(conn,sqlquery)Close the database connection.
close(conn)
Use a DuckDB native interface database connection to import a limited number of rows of airline data from a database table into MATLAB®.
Load airline data.
airlineData = readtable("airlinesmall_subset.xlsx");Create a connection to a DuckDB database file by using the duckdb function. This example assumes you are creating a database, airlineDatabase.db, to store the airline data.
conn = duckdb("airlineDatabase.db");Export the airline data to a database table named "airlines" by using the sqlwrite function.
tablename = "airlines";
sqlwrite(conn,tablename,airlineData)Import all of the rows from the airlines table into MATLAB by using the sqlread function.
data = sqlread(conn,"airlines")data=1338×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 SDelay SecurityDelay LateAircraftDelay
____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ ________ _________________ ______________ _______ ________ ________ ______ _____ ________ ______ _______ _________ ________________ ________ ____________ ____________ ______ _____________ _________________
1996 1 18 4 2117 2120 2305 2259 "HP" 415 "N637AW" 108 99 85 6 -3 "COS" "PHX" 551 5 18 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 12 5 1252 1245 1511 1500 "HP" 610 "N905AW" 79 75 58 11 7 "LAX" "PHX" 370 3 18 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 16 2 1441 1445 1708 1721 "HP" 211 "N165AW" 87 96 74 -13 -4 "RNO" "PHX" 601 4 9 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 1 1 2258 2300 2336 2335 "HP" 1245 "N183AW" 38 35 20 1 -2 "TUS" "PHX" 110 6 12 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 4 4 1814 1814 1901 1910 "US" 683 "N963VJ" 47 56 34 -9 0 "DTW" "PIT" 201 6 7 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 31 3 1822 1820 1934 1925 "US" 757 "N912VJ" 72 65 52 9 2 "PHL" "PIT" 267 6 14 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 18 4 729 730 841 843 "US" 1564 "N941VJ" 72 73 58 -2 -1 "DCA" "PVD" 357 3 11 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 26 5 1704 1705 1829 1839 "NW" 1538 "N960N" 85 94 69 -10 -1 "DTW" "RIC" 456 3 13 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 11 4 1858 1850 1959 1956 "US" 2225 "N351US" 61 66 48 3 8 "PHL" "ROC" 257 2 11 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 7 7 2100 2100 2215 2220 "WN" 174 "N677" 75 80 65 -5 0 "OAK" "SAN" 446 3 7 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 16 2 1715 1715 1805 1810 "WN" 167 "N20" 50 55 41 -5 0 "DAL" "SAT" 248 2 7 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 30 2 1205 1210 1312 1322 "CO" 1127 "N10236" 67 72 52 -10 -5 "CLE" "SDF" 304 4 11 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 8 1 1232 1232 1520 1517 "AS" 62 "N755AS" 108 105 94 3 0 "KTN" "SEA" 680 5 9 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 30 2 1558 1600 1744 1757 "UA" 2410 "N310UA" 106 117 92 -13 -2 "SFO" "SEA" 679 3 11 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 14 7 2331 2325 616 605 "UA" 132 "N4714U" 285 280 263 11 6 "HNL" "SFO" 2398 5 17 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 7 7 1513 0 1738 0 "AA" 83 "N339AA" 265 268 246 10 13 "ORD" "SFO" 1846 2 17 0 NaN 0 NaN NaN NaN NaN NaN
⋮
Then, import only the first 5 rows from the airlines table by using the MaxRows name-value argument.
data = sqlread(conn,"airlines",MaxRows=5)data=5×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 SDelay SecurityDelay LateAircraftDelay
____ _____ __________ _________ _______ __________ _______ __________ _____________ _________ ________ _________________ ______________ _______ ________ ________ ______ _____ ________ ______ _______ _________ ________________ ________ ____________ ____________ ______ _____________ _________________
1996 1 18 4 2117 2120 2305 2259 "HP" 415 "N637AW" 108 99 85 6 -3 "COS" "PHX" 551 5 18 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 12 5 1252 1245 1511 1500 "HP" 610 "N905AW" 79 75 58 11 7 "LAX" "PHX" 370 3 18 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 16 2 1441 1445 1708 1721 "HP" 211 "N165AW" 87 96 74 -13 -4 "RNO" "PHX" 601 4 9 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 1 1 2258 2300 2336 2335 "HP" 1245 "N183AW" 38 35 20 1 -2 "TUS" "PHX" 110 6 12 0 NaN 0 NaN NaN NaN NaN NaN
1996 1 4 4 1814 1814 1901 1910 "US" 683 "N963VJ" 47 56 34 -9 0 "DTW" "PIT" 201 6 7 0 NaN 0 NaN NaN NaN NaN NaN
Import product data from an SQLite database table into MATLAB® by using the MATLAB interface to SQLite. The table contains a variable name with a non-ASCII character. When importing data, remove non-ASCII characters from the names of all the variables.
Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.
dbfile = "tutorial.db";
conn = sqlite(dbfile);Rename the unitCost column in the database table productTable to tamaño. The column name contains a non-ASCII character.
sqlquery = "ALTER TABLE productTable RENAME COLUMN unitCost TO tamaño";
execute(conn,sqlquery)Import data from the database table productTable. The sqlread function returns a MATLAB table that contains the product data. Display the first three rows of the data in the table.
tablename = "productTable";
data = sqlread(conn,tablename);
head(data,3) productNumber stockNumber supplierNumber tamaño productDescription
_____________ ___________ ______________ _______________________ __________________
9 125970 1003 13 "Victorian Doll"
8 212569 1001 5 "Train Set"
7 389123 1007 16 "Engine Kit"
The sqlread function preserves non-ASCII characters in the name of the variable by default.
Remove the non-ASCII character in the name of the variable by specifying the VariableNamingRule name-value argument. Import the data again.
data = sqlread(conn,tablename, ... VariableNamingRule="modify"); head(data,3)
productNumber stockNumber supplierNumber tama_o productDescription
_____________ ___________ ______________ ______ __________________
9 125970 1003 13 "Victorian Doll"
8 212569 1001 5 "Train Set"
7 389123 1007 16 "Engine Kit"
The sqlread function removes the non-ASCII character in the variable name.
Rename the tamaño column in the database table productTable back to unitCost.
sqlquery = "ALTER TABLE productTable RENAME COLUMN tamaño TO unitCost";
execute(conn,sqlquery)Close the SQLite connection.
close(conn)
Use an ODBC connection to import product data from a database table into MATLAB® using a Microsoft® SQL Server® database. Then, perform a simple data analysis.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username and password. The database contains the table productTable.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
Check the database connection. If the Message property is empty, then the connection is successful.
conn.Message
ans =
[]
Import data from the database table productTable. The sqlread function returns a MATLAB® table that contains the product data.
tablename = 'productTable';
data = sqlread(conn,tablename);
Display the first five products.
head(data,5)
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ __________________
9 1.2597e+05 1003 13 {'Victorian Doll'}
8 2.1257e+05 1001 5 {'Train Set' }
7 3.8912e+05 1007 16 {'Engine Kit' }
2 4.0031e+05 1002 9 {'Painting Set' }
4 4.0034e+05 1008 21 {'Space Cruiser' }
Now, import the data using a row filter. The filter condition is that unitCost must be less than 15.
rf = rowfilter("unitCost"); rf = rf.unitCost < 15; data = sqlread(conn,tablename,"RowFilter",rf);
Again, display the first five products.
head(data,5)
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ ___________________
9 1.2597e+05 1003 13 {'Victorian Doll' }
8 2.1257e+05 1001 5 {'Train Set' }
2 4.0031e+05 1002 9 {'Painting Set' }
1 4.0034e+05 1001 14 {'Building Blocks'}
5 4.0046e+05 1005 3 {'Tin Soldier' }
Close the database connection.
close(conn)
Input Arguments
Database connection, specified as any of the following:
MySQL
connectionobject created by using themysqlfunction.PostgreSQL
connectionobject created by using thepostgresqlfunction.DuckDB
connectionobject created by using theduckdbfunction.SQLite
connectionobject created by using thesqlitefunction.ODBC
connectionobject created by using thedatabasefunction.JDBC
connectionobject created by using thedatabasefunction.
Database table name, specified as a string scalar or character vector denoting the name of a table in the database.
Example: "employees"
Data Types: string | char
Database import options, specified as an SQLImportOptions object.
Note
This argument is not valid when conn is an SQLite
connection object.
Name-Value Arguments
Specify optional pairs of arguments as
Name1=Value1,...,NameN=ValueN, where Name is
the argument name and Value is the corresponding value.
Name-value arguments must appear after other arguments, but the order of the
pairs does not matter.
Before R2021a, use commas to separate each name and value, and enclose
Name in quotes.
Example: data =
sqlread(conn,"inventoryTable",Catalog="toy_store",Schema="dbo",MaxRows=5)
imports five rows of data from the database table inventoryTable
stored in the toy_store catalog and the dbo
schema.
Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have multiple catalogs.
Example: Catalog="toy_store"
Note
This argument is not valid when conn is an
SQLite connection object.
Data Types: string | char
Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.
Example: Schema="dbo"
Note
This argument is not valid when conn is
either a MySQL or an SQLite connection object.
Data Types: single
Maximum number of rows to return, specified as a positive numeric
scalar. By default, the sqlread function
returns all rows from the executed SQL query. Use this name-value
argument to limit the number of rows imported into MATLAB.
Example: MaxRows=10
Data Types: double
Variable naming rule, specified as one of the following:
"modify"— Remove non-ASCII characters from variable names when thesqlreadfunction imports data."preserve"— Preserve most variable names when thesqlreadfunction imports data.
Example: VariableNamingRule="modify"
Data Types: string
Row filter condition, specified as a
matlab.io.RowFilter object.
Example: rf = rowfilter("productnumber"); rf =
rf.productnumber <= 5;
sqlread(conn,tablename,RowFilter=rf)
Output Arguments
Imported data, returned as a table. The rows of the table correspond to
the rows in the database table tablename. The variables
in the table correspond to each column in the database table.
The following table lists how each database data type maps to a MATLAB data type.
| Database Data Type | MySQL | PostgreSQL | DuckDB | SQLite | JDBC/ODBC |
|---|---|---|---|---|---|
| N/A | logical | logical | int64 | logical |
| double | N/A | double | double | double |
| double | ||||
| N/A | N/A | |||
| N/A | N/A | double | ||
| double | double | double | ||
| N/A | N/A | N/A | ||
| |||||
| |||||
| |||||
| double | ||||
| double | double | |||
| N/A | N/A | N/A | ||
| |||||
| double | double | N/A | double | |
| N/A | N/A | |||
| |||||
| |||||
| double | double | |||
| N/A | N/A | |||
| N/A | double | |||
| double | N/A | |||
| datetime | datetime | datetime | cell array or character vectors | |
| N/A | N/A | |||
| |||||
| |||||
| datetime | N/A | |||
| |||||
| datetime | datetime (12AM on specified date) | string | ||
| N/A | N/A | N/A | ||
| double | double | double | N/A | double |
| string | string | string | cell array or character vectors | |
| logical | N/A | N/A | N/A | logical |
| N/A | string | cell array or character vectors | ||
| string | ||||
| N/A | ||||
| |||||
| |||||
| |||||
| duration | duration | duration | ||
| N/A | N/A | |||
| N/A | duration | |||
| calendarDuration | calendarDuration | |||
| N/A | double | double | ||
| |||||
| cell array of uint8 vectors | cell array of uint8 vectors |
| cell array or character vectors | |
| categorical (categories match values of ENUM type) | categorical (categories match values of ENUM type) | N/A | ||
| string | string | N/A | string | |
| N/A | N/A | |||
| calendarDuration | ||||
| char | N/A | |||
| string | ||||
| N/A | Cell array where each cell contains a vector of the corresponding type. For example, DOUBLE[] maps to a cell array of double vectors. | |||
| Each field maps to its corresponding DuckDB type. For example, STRUCT(name VARCHAR, age INTEGER) maps to a MATLAB struct, where name fields are strings and age fields are int32. | ||||
| Cell array of dictionaries. Key and value types match the DuckDB types. For example, MAP(DOUBLE, VARCHAR) maps to a cell array of dictionaries with double keys and string values. | ||||
| Cell array with each cell containing a vector of the corresponding type. For example, DOUBLE[3] maps to a cell array of double vectors. | ||||
| Cell array where each cell can contain a value matching any DuckDB type defined in the UNION. For example, UNION(t TIMESTAMP, d DOUBLE), maps to a cell array where each element is either a datetime or a double. |
Metadata information, returned as a table with these variables.
| Variable Name | Variable Description | Variable Data Type |
|---|---|---|
| Data type of each variable in the imported data | Cell array of character vectors |
| Value of missing data for each variable in the imported data | Cell array of missing data values |
| Indices for each occurrence of missing data in each variable of the imported data | Cell array of numeric indices |
By default, the sqlread function imports text
data as a character vector and numeric data as a double.
FillValue is an empty character array (for text data)
or NaN (for numeric data) by default. To change the
missing data value to another value, use the SQLImportOptions object.
The RowNames property of the
metadata table contains the names of the variables in
the imported data.
Note
This argument is not valid when conn is an SQLite
connection object.
Limitations
The
sqlreadfunction returns an error when you use theVariableNamingRulename-value argument with theSQLImportOptionsobjectopts.When the
VariableNamingRulename-value argument is set to the value"modify":The variable names
Properties,RowNames, andVariableNamesare reserved identifiers for thetabledata type.The length of each variable name must be less than the number returned by
namelengthmax.
The
sqlreadfunction returns an error if you specify theRowFiltername-value argument with theSQLImportOptionsobjectopts. It is ambiguous which of theRowFilterobjects to use in this case, especially if the filter conditions are different.
Version History
Introduced in R2018aImport table data from aDuckDB database by specifying a DuckDB
connection object when using the sqlread
function.
You can use the RowFilter name-value argument to selectively
import rows of data from a database table.
See Also
Functions
sqlfind|select|fetch|sqlinnerjoin|sqlouterjoin|database|close|databaseImportOptions|setoptions|getoptions|reset|execute|sqlupdate
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Seleccione un país/idioma
Seleccione un país/idioma para obtener contenido traducido, si está disponible, y ver eventos y ofertas de productos y servicios locales. Según su ubicación geográfica, recomendamos que seleccione: .
También puede seleccionar uno de estos países/idiomas:
Cómo obtener el mejor rendimiento
Seleccione China (en idioma chino o inglés) para obtener el mejor rendimiento. Los sitios web de otros países no están optimizados para ser accedidos desde su ubicación geográfica.
América
- América Latina (Español)
- Canada (English)
- United States (English)
Europa
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)