SQLImportOptions
Define import options for database data
Description
After you create an SQLImportOptions
object, you can customize
the import options for importing data from a database into MATLAB®. Import options include defining the data types and fill values for missing
data.
Creation
Create an SQLImportOptions
object with the databaseImportOptions
function.
Properties
ExcludeDuplicates
— Flag to exclude duplicates
false
(default) | true
Flag to exclude duplicates from imported data, specified as false
or true
. To exclude duplicates from the data in a database table or
the results of an SQL query, set the ExcludeDuplicates
property to
true
using dot notation.
Setting this property is the equivalent of using the DISTINCT
SQL
statement in ANSI SQL.
Data Types: logical
VariableNames
— Variable names
cell array of character vectors
Variable names, specified as a cell array of character vectors. Each character vector in the cell array indicates the name of an imported database column from an SQL query or database table.
For a table or SQL query with only one database column, the cell array contains only one character vector.
The default variable names are the names of the columns in an SQL query or database table.
Example:
{'productNumber','stockNumber'}
Data Types: cell
VariableTypes
— Variable types
cell array of character vectors
Variable types, specified as a cell array of character vectors. Each character vector in the cell array indicates the data type of an imported database column from an SQL query or database table. Each character vector must be a valid MATLAB data type.
For a table or SQL query with only one database column, the cell array contains only one character vector.
When you create the SQLImportOptions
object, the
databaseImportOptions
function automatically detects the data
type based on the data type of a database column. This table maps the data type of a
database column to the detected MATLAB data type.
Database Data Type | MATLAB Detected Data Type |
---|---|
|
|
|
|
|
|
|
|
If you are using the MySQL® native interface, this table maps the data type of a database column to the detected MATLAB data type.
MySQL Data Type | MATLAB Data Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
If you are using the PostgreSQL native interface, this table maps the data type of a database column to the detected MATLAB data type.
PostgreSQL Data Type | MATLAB Data Type |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
To update the VariableTypes
property, use the setoptions
function.
Example:
{'int64','int32'}
Data Types: cell
SelectedVariableNames
— Subset of variables to import
character vector | cell array of character vectors | numeric array
Subset of variables to import, specified as a character vector, cell array of
character vectors, or numeric array that contains indices. Use the
SelectedVariableNames
property to determine the database columns
to import into the MATLAB workspace.
The values in the SelectedVariableNames
property must be equal
to the values in the VariableNames
property or a subset of these
values. By default, the SelectedVariableNames
property contains all
variable names specified in the VariableNames
property. When the
SelectedVariableNames
property specifies all variable names, the
sqlread
,
fetch
, and import functions of the
DatabaseDatastore
object import all database
columns.
Example:
{'productNumber','stockNumber'}
Example:
[1,2,3]
Data Types: double
| char
| cell
FillValues
— Fill value for missing data
cell array
Fill value for missing data, specified as a cell array that contains one or more values. Each value can be one of these data types:
All integer classes
single
double
char
string
scalarlogical
datetime
arraycategorical
arraymissing
When you create the SQLImportOptions
object, the
databaseImportOptions
function automatically detects the fill
value for missing data based on the data type of the database column. This table maps
the data type of a database column to the detected MATLAB fill value.
Database Data Type | MATLAB Detected Fill Value |
---|---|
| '' |
| '' |
| NaN |
| false |
If you are using the MySQL native interface, this table maps the data type of a database column to the detected MATLAB data type.
MySQL Data Type | MATLAB Detected Fill Value |
---|---|
| '' (if the VariableTypes property
is char ) or <missing> (if the
VariableTypes property is
string ) |
| NaT |
| NaN |
| NaN |
| <undefined> |
If you are using the PostgreSQL native interface, this table maps the data type of a database column to the detected MATLAB data type.
PostgreSQL Data Type | MATLAB Detected Fill Value |
---|---|
| false |
| NaN |
| <missing> |
| NaT |
| NaN |
| NaN |
| undefined |
To update the FillValues
property, use the setoptions
function.
Example:
{'',NaN}
Data Types: cell
VariableOptions
— Type-specific variable import options
array of variable import options objects
Type-specific variable import options, returned as an array of variable import
options objects. The array contains an object corresponding to each variable specified
in the VariableNames
property. Each object in the array contains
properties that support the importing of data with a specific data type.
To query the current (or detected) options for a variable, use the getoptions
function.
To set and customize options for a variable, use the setoptions
function.
Example:
opts.VariableOptions
returns a collection of
SQLVariableImportOptions
objects, one corresponding to each
variable in the data.
VariableNamingRule
— Variable naming rule
"modify"
(default) | "preserve"
Variable naming rule, specified as one of these values:
"modify"
— Remove non-ASCII characters from variable names when theSQLImportOptions
function imports data."preserve"
— Preserve most variable names when theSQLImportOptions
function imports data. For details, see Limitations.
If you are using the MySQL or PostgreSQL native interface, "preserve"
is the
default value.
The VariableNamingRule
property has these limitations:
The variable names
Properties
,RowNames
, andVariableNames
are reserved identifiers for thetable
data type.The length of each variable name must be less than the number returned by
namelengthmax
.
Example: "VariableNamingRule","modify"
Data Types: string
RowFilter
— Row filter condition
<unconstrained>
(default) | matlab.io.RowFilter
object
Row filter condition, specified as a matlab.io.RowFilter
object.
Using RowFilter
you can filter by columns or other criteria not
listed in the VariableNames
property. To do this, set a new
RowFilter
property containing the new variable name. See Filter Data by Using Unique Variable Names.
Example: opt.RowFilter = opt.RowFilter.productnumber <=
5
Object Functions
getoptions | Retrieve import options for database data |
preview | Preview eight rows from database using import options |
reset | Reset to default import options for database data |
setoptions | Customize import options for database data |
Examples
Import Data from Database Table Using Import Options
Customize import options when importing data from a database table. 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 also uses a Microsoft® SQL Server® Version 11.00.2100 database and the Microsoft SQL Server Driver 11.00.5058.
Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank username and password.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','');
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: 'modify' VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'char', 'double' ... and 7 more} SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} FillValues: {'', '', NaN ... and 7 more } RowFilter: <unconstrained> VariableOptions: Show all 10 VariableOptions
Display the current import options for the variables selected 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: 'char' | 'char' | 'double' | 'char' | 'double' | 'double' | 'double' | 'double' | 'double' | 'char' MissingRule: 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' | 'fill' FillValue: '' | '' | NaN | '' | NaN | NaN | NaN | NaN | NaN | '' To access sub-properties of each variable, use getoptions
Change the data types for the Gender
, Location
, SelfAssessedHealthStatus
, and Smoker
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 logical
. Then, display the updated import options.
opts = setoptions(opts,{'Gender','Location','SelfAssessedHealthStatus'}, ... 'Type','categorical'); opts = setoptions(opts,'Smoker','Type','logical'); 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' | 'logical' | '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 false 130 91 Fair {'Gonzales' } Male 48 County General Hospital 71 174 false 123 79 Good {'Bryant' } Female 48 County General Hospital 66 134 false 129 73 Excellent {'Alexander'} Male 25 County General Hospital 69 171 true 128 99 Good {'Russell' } Male 44 VA Hospital 69 188 true 124 92 Good {'Griffin' } Male 49 County General Hospital 70 186 false 119 74 Fair {'Diaz' } Male 45 County General Hospital 68 172 true 136 93 Good {'Hayes' } Male 48 County General Hospital 66 177 false 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 cell array of character vectors 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 logical Values: True 34 False 66 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 years and not taller than 68 inches.
opts.RowFilter = opts.RowFilter.Age > 40 & opts.RowFilter.Height <= 68
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'modify' VariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} VariableTypes: {'char', 'categorical', 'double' ... and 7 more} SelectedVariableNames: {'LastName', 'Gender', 'Age' ... and 7 more} FillValues: {'', <undefined>, NaN ... and 7 more } RowFilter: Age > 40 & Height <= 68 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 cell array of character vectors 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 logical Values: True 8 False 16 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 = ['DROP TABLE ' tablename];
execute(conn,sqlquery)
Close the database connection.
close(conn)
Filter Data by Using Unique Variable Names
Filter data by columns or other criteria not listed in the VariableNames
property by using the RowFilter
property.
Create a PostgreSQL native interface database connection to a PostgreSQL database. The database contains the table productTable
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(datasource,username,password);
Import data from the database table productTable
. The sqlread
function returns a MATLAB table that contains the product data. Display the first five rows of product data.
tablename = "productTable";
data = sqlread(conn,tablename);
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"
Create an SQLImportOptions
object using the productTable
database table and the databaseImportOptions
function.
opts = databaseImportOptions(conn,tablename)
opts = SQLImportOptions with properties: ExcludeDuplicates: false VariableNamingRule: 'preserve' VariableNames: {'productnumber', 'stocknumber', 'suppliernumber' ... and 2 more} VariableTypes: {'double', 'double', 'double' ... and 2 more} SelectedVariableNames: {'productnumber', 'stocknumber', 'suppliernumber' ... and 2 more} FillValues: { NaN, NaN, NaN ... and 2 more } RowFilter: <unconstrained> VariableOptions: Show all 5 VariableOptions
Set the RowFilter
property containing the new variable name. For example, use the length
function to filter the productdescription
strings by the number of characters.
opts.RowFilter = rowfilter("length(productdescription)");
opts.RowFilter
ans = RowFilter with no constraints and no selected variables <unconstrained> VariableNames: length(productdescription), productnumber, stocknumber, suppliernumber, unitcost, productdescription
By setting RowFilter
, you added a unique VariableName
to RowFilter
. The VariableNames
property of SQLImportOptions
does not contain this variable name. Even though VariableNames
of RowFilter
does not completely match VariableNames
of SQLImportOptions
, updating VariableNames
of SQLImportOptions
still updates VariableNames
of RowFilter
.
opts.VariableNames{4} = "cost";
opts.RowFilter
ans = RowFilter with no constraints and no selected variables <unconstrained> VariableNames: length(productdescription), productnumber, stocknumber, suppliernumber, cost, productdescription
Set the filtering condition using the unique variable name, length(productdescription)
, and the new variable name, cost
.
opts.RowFilter = opts.RowFilter.("length(productdescription)") < 10 & opts.RowFilter.cost > 10;
Import data from the database table and display the first five rows of product data.
data = sqlread(conn,tablename,opts); head(data,5)
productnumber stocknumber suppliernumber cost productdescription _____________ ___________ ______________ ____ __________________ 3 4.01e+05 1009 17 "Slinky" 12 2.1046e+05 1010 22 "Hugsy" 13 4.7082e+05 1012 16.5 "Pancakes" 14 5.101e+05 1011 19 "Shawl" 15 8.9975e+05 1011 20 "Snacks"
Limitations
If you use the
"VariableNamingRule"
name-value argument with theSQLImportOptions
objectopts
, the data import functions return an error.If you set the
VariableNamingRule
name-value argument to the value"modify"
:The variable names
Properties
,RowNames
, andVariableNames
are reserved identifiers for thetable
data type.The length of each variable name must be less than the number returned by
namelengthmax
.
The
fetch
andsqlread
functions return an error if you specify theRowFilter
name-value argument with theSQLImportOptions
objectopts
. In this case, it is ambiguous which of theRowFilter
object to use, especially if the filter conditions are different.
Version History
Introduced in R2018bR2023a: Selectively import rows of data
Use the RowFilter
property of SQLImportOptions
to
define import options for database data.
See Also
Functions
External Websites
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.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- 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)