databaseDatastore

Datastore for data in database

Description

MATLAB^® has various datastores that let you import large data sets into MATLAB for analysis. A DatabaseDatastore object is a type of datastore that contains data from a database table or the results from executing an SQL query in a relational database. For details about other datastores, see Getting Started with Datastore.

With a DatabaseDatastore object, you can preview and read records or chunks in a data set and reset the DatabaseDatastore to its initial state. Also, you can analyze a large data set in a database using tall arrays or MapReduce.

Reading data from DatabaseDatastore objects is the same as executing the fetch function on the data set. Using DatabaseDatastore objects provides advantages that enable you to:

Work with databases containing large amounts of data.
Analyze large amounts of data using tall arrays with common MATLAB functions, such as mean and histogram. Create a tall array using the tall function. For details, see Tall Arrays for Out-of-Memory Data.
Write MapReduce algorithms that define the chunking and reduction of large amounts of data by using the mapreduce function. For details, see Getting Started with MapReduce. For an example, see Analyze Large Data in Database Using MapReduce.
Create parallelizable workflows by using a parallel pool constant when you create the connection for your databaseDatastore object (since R2024a). For more information, see parallel.pool.Constant (Parallel Computing Toolbox). You can use the setSecret and getSecret functions to add and retrieve your user credentials when you create a parallel pool constant. For more information on security consideration topics, see Keep Sensitive Information Out of Code.

Creation

Syntax

dbds = databaseDatastore(conn,source)

dbds = databaseDatastore(conn,source,Name,Value)

dbds = databaseDatastore(conn,source,opts)

dbds = databaseDatastore(conn,source,opts,Name,Value)

Description

dbds = databaseDatastore(conn,source) creates a DatabaseDatastore object using the database connection. This datastore contains data from a database table or the results from an executed SQL query.

example

dbds = databaseDatastore(conn,source,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'ReadSize',100 retrieves 100 rows of data from the DatabaseDatastore object.

example

dbds = databaseDatastore(conn,source,opts) customizes the options for importing a large data set from a database using the SQLImportOptions object.

example

dbds = databaseDatastore(conn,source,opts,Name,Value) specifies additional options using one or more name-value pair arguments. For example, 'Catalog','toy_store' retrieves data from the toy_store database catalog.

Input Arguments

expand all

`conn` — Database connection
`connection` object

Database connection, specified as a connection object created with the database function, connection object created with the mysql function, connection object created with the postgresql function, or sqlite object.

Create a parallelizable databaseDatastore object by first creating a parallel pool constant. You can use the getSecret function to retrieve your user credentials when you create this constant.

Example: conn = parallel.pool.Constant(@()postgresql(getSecret("PostgreSQL.username"),getSecret("Postgresql.password"),"Server","localhost","DatabaseName","toy_store"),@close);

`source` — Source
character vector | string scalar

Source, specified as a character vector or string scalar. The source indicates whether the DatabaseDatastore object stores data from a database table or the results from an executed SQL query.

Example: 'inventorytable'

Example: "SELECT productnumber,productname FROM producttable"

Data Types: char | string

`opts` — Database import options
`SQLImportOptions` object

Database import options, specified as an SQLImportOptions 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: databaseDatastore(conn,source,'ReadSize',100,'Catalog','toy_store') creates a DatabaseDatastore object and stores 100 rows of data from a table or SQL query using the toy_store database catalog.

`ReadSize` — Number of rows to return
numeric scalar

Number of rows to return, specified as the comma-separated pair consisting of 'ReadSize' and a positive numeric scalar. Use this name-value pair argument to limit the number of rows for retrieval from the DatabaseDatastore object.

Example: 1000

Data Types: double

`Catalog` — Database catalog name
character vector | string scalar

Database catalog name, specified as the comma-separated pair consisting of 'Catalog' and a character vector or string scalar. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have numerous catalogs.

Use the 'Catalog' name-value pair argument only when source is a database table.

Example: 'Catalog','toy_store'

Data Types: char | string

`Schema` — Database schema name
character vector | string scalar

Database schema name, specified as the comma-separated pair consisting of 'Schema' and a character vector or string scalar. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.

Use the 'Schema' name-value pair argument only when source is a database table.

Example: 'Schema','dbo'

Data Types: char | string

Limitations

You can create parallelizable workflows by using the DatabaseDatastore object and specifying a parallel pool constant with the following database vendors:
- MySQL^®
- PostgreSQL
- Microsoft^® SQL Server^®
- MariaDB^®
- Oracle^®
If you set the VariableNamingRule name-value argument to the value "modify":
- The variable names Properties, RowNames, and VariableNames are reserved identifiers for the table data type.
- The length of each variable name must be less than the number returned by namelengthmax.
If you are using Microsoft SQL Server, the DatabaseDatastore object supports versions 2012 and later.

Properties

expand all

`Connection` — Database connection
`connection` object

This property is read-only.

Database connection, specified as a connection object created using database.

`Query` — SQL query
character vector

This property is read-only.

SQL query, specified as a character vector that specifies the SQL query to execute in the database.

Data Types: char

`VariableNames` — Column names of retrieved data table
cell array of character vectors

Column names of the retrieved data table, specified as a cell array of one or more character vectors.

Data Types: char

`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 import functions of the DatabaseDatastore object import all database columns.

Data Types: double | char | cell

`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 the DatabaseDatastore function imports data.
"preserve" — Preserve most variable names when the DatabaseDatastore 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, and VariableNames are reserved identifiers for the table data type.
The length of each variable name must be less than the number returned by namelengthmax.

Data Types: string

`ReadSize` — Number of rows to read
10000 (default) | numeric scalar

Number of rows to read from the retrieved data table, specified as a nonnegative numeric scalar. To specify the number of rows to read, set the ReadSize property.

Example: dbds.ReadSize = 5000;

Data Types: double

`RowFilter` — Filter to select rows to import
`matlab.io.RowFilter` object

Filter to select rows to import, specified as a matlab.io.RowFilter object. This filter specifies the conditions each row must satisfy when importing data.

Example: ds = databaseDatastore(conn,"producttable"); rf = rowfilter("producttable"); rf = rf.productnumber > 10; ds.RowFilter = rf

Object Functions

`hasdata`	Determine if data in `DatabaseDatastore` is available to read
`preview`	Return subset of data from `DatabaseDatastore`
`read`	Read data in `DatabaseDatastore`
`readall`	Read all data in `DatabaseDatastore`
`reset`	Reset `DatabaseDatastore` to initial state
`close`	Close and invalidate database and driver resource utilizer
`isPartitionable`	Determine whether datastore is partitionable
`isShuffleable`	Determine whether datastore is shuffleable
`partition`	Partition a datastore

Examples

collapse all

Create `DatabaseDatastore` Object Using SQL Query Results

Open Script

Create a database connection to a MySQL (R) database using an ODBC driver. Then, create a DatabaseDatastore object using the results from an SQL query and preview a large data set.

Create a database connection to the ODBC data source MySQL ODBC. Specify the user name and password.

datasource = "MySQL ODBC";
username = "username";
password = "password";
conn = database(datasource,username,password);

Create a DatabaseDatastore object using a database connection and an SQL query. This SQL query retrieves all flight data from the airlinesmall table. databaseDatastore executes the SQL query.

sqlquery = 'select * from airlinesmall';

dbds = databaseDatastore(conn,sqlquery)

dbds = 

  DatabaseDatastore with properties:

               Connection: [1×1 database.odbc.connection]
                    Query: 'select * from airlinesmall'
            VariableNames: {1×29 cell}
    SelectedVariableNames: {1×29 cell}
       VariableNamingRule: 'modify'
                 ReadSize: 10000

dbds is a DatabaseDatastore object with these properties:

Connection -- Database connection object
Query -- Executed SQL query
VariableNames -- List of column names from the executed SQL query
ReadSize -- Maximum number of records to read from the executed SQL query

Display the database connection property.

dbds.Connection

ans = 

  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: 'root'
                     Message: ''
                        Type: 'ODBC Connection Object'
  Database Properties:

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'information_schema', 'mysql', 'performance_schema' ... and 3 more}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '8.0.3-rc-log'
                  DriverName: 'myodbc8a.dll'
               DriverVersion: '08.00.0016'

The Message property is blank when the database connection is successful.

Preview the first eight records in the large data set returned by executing the SQL query in the DatabaseDatastore object.

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          22            6         1801         1750        2005         1938          {'NW'}           209       {'NA'}            124                108          {'NA'}        27          11       {'PHL'}    {'DTW'}       453      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          11            2          908          910        1613         1554          {'NW'}           248       {'NA'}            245                224          {'NA'}        19          -2       {'PHX'}    {'DTW'}      1671      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           2            7          NaN         1805         NaN         1900          {'NW'}           284       {'NA'}            NaN                 55          {'NA'}       NaN         NaN       {'JAN'}    {'MEM'}       189      {'NA'}    {'NA'}         1             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          29            6         1434         1435        1615         1630          {'NW'}           305       {'NA'}            221                235          {'NA'}       -15          -1       {'MSP'}    {'LAX'}      1536      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           3            1          925          755        1258         1144          {'NW'}           350       {'NA'}            153                169          {'NA'}        74          90       {'MSP'}    {'BOS'}      1124      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          22            6          900          900        1241         1222          {'AA'}            11       {'NA'}            401                382          {'NA'}        19           0       {'BOS'}    {'LAX'}      2611      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          20            4         1338         1335        1853         1907          {'AA'}            62       {'NA'}            255                272          {'NA'}       -14           3       {'ORD'}    {'SJU'}      2072      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           3            1          710          711         837          847          {'AA'}           101       {'NA'}            147                156          {'NA'}       -10          -1       {'DTW'}    {'DFW'}       987      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}

Close the DatabaseDatastore object and the database connection.

close(dbds)

Create `DatabaseDatastore` Object Using Database Table

Open Live Script

Retrieve a large data set from a database table by creating a DatabaseDatastore object. This example uses a MySQL® database.

Create a database connection to a MySQL database with the user name and password.

datasource = "MySQL ODBC";
username = "username";
password = "password";
conn = database(datasource,username,password);

Load flight information in the MATLAB® workspace.

flights = readtable('airlinesmall_subset.xlsx');

Create the flights database table using the flight information.

tablename = 'flights';
sqlwrite(conn,tablename,flights)

Create a DatabaseDatastore object using a database connection and the flights database table.

dbds = databaseDatastore(conn,tablename)

dbds = 
  DatabaseDatastore with properties:

               Connection: [1×1 database.odbc.connection]
                    Query: 'SELECT * from flights'
            VariableNames: {1×29 cell}
    SelectedVariableNames: {1×29 cell}
       VariableNamingRule: 'modify'
                 ReadSize: 10000

dbds is a DatabaseDatastore object with these properties:

Connection — Database connection object
Query — Executed SQL query
VariableNames — List of column names from the executed SQL query
ReadSize — Maximum number of records to read from the executed SQL query

Display the database connection property.

dbds.Connection

ans = 
  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: 'root'
                     Message: ''
                        Type: 'ODBC Connection Object'
  Database Properties:

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'information_schema', 'mysql', 'performance_schema' ... and 3 more}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '8.0.3-rc-log'
                  DriverName: 'myodbc8a.dll'
               DriverVersion: '08.00.0016'

The Message property is blank when the database connection is successful.

Preview the first eight records in the data set returned by executing the SQL query in the DatabaseDatastore object.

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    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

Close the DatabaseDatastore object and the database connection.

close(dbds)

Create `DatabaseDatastore` Object with Specific Record Count

Open Script

Create a database connection using an ODBC driver. Then, create a DatabaseDatastore object by setting the ReadSize property, and preview a large data set.

Create a database connection to the ODBC data source MySQL ODBC. Specify the user name and password.

datasource = "MySQL ODBC";
username = "username";
password = "password";
conn = database(datasource,username,password);

Create a DatabaseDatastore object using a database connection and an SQL query. This SQL query retrieves all flight data from the airlinesmall table. Specify reading a maximum of 1000 records from the executed SQL query. databaseDatastore executes the SQL query.

sqlquery = 'select * from airlinesmall';

dbds = databaseDatastore(conn,sqlquery,'ReadSize',1000)

dbds = 

  DatabaseDatastore with properties:

               Connection: [1×1 database.odbc.connection]
                    Query: 'select * from airlinesmall'
            VariableNames: {1×29 cell}
    SelectedVariableNames: {1×29 cell}
       VariableNamingRule: 'modify'
                 ReadSize: 1000

dbds is a DatabaseDatastore object with these properties:

Connection -- Database connection object
Query -- Executed SQL query
VariableNames -- List of column names from the executed SQL query
ReadSize -- Maximum number of records to read from the executed SQL query

Display the database connection property.

dbds.Connection

ans = 

  connection with properties:

                  DataSource: 'MySQL ODBC'
                    UserName: 'root'
                     Message: ''
                        Type: 'ODBC Connection Object'
  Database Properties:

                  AutoCommit: 'on'
                    ReadOnly: 'off'
                LoginTimeout: 0
      MaxDatabaseConnections: 0

  Catalog and Schema Information:

              DefaultCatalog: 'toy_store'
                    Catalogs: {'information_schema', 'mysql', 'performance_schema' ... and 3 more}
                     Schemas: {}

  Database and Driver Information:

         DatabaseProductName: 'MySQL'
      DatabaseProductVersion: '8.0.3-rc-log'
                  DriverName: 'myodbc8a.dll'
               DriverVersion: '08.00.0016'

The Message property is blank when the database connection is successful.

Preview the first eight records in the large data set returned by executing the SQL query in the DatabaseDatastore object.

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          22            6         1801         1750        2005         1938          {'NW'}           209       {'NA'}            124                108          {'NA'}        27          11       {'PHL'}    {'DTW'}       453      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          11            2          908          910        1613         1554          {'NW'}           248       {'NA'}            245                224          {'NA'}        19          -2       {'PHX'}    {'DTW'}      1671      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           2            7          NaN         1805         NaN         1900          {'NW'}           284       {'NA'}            NaN                 55          {'NA'}       NaN         NaN       {'JAN'}    {'MEM'}       189      {'NA'}    {'NA'}         1             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          29            6         1434         1435        1615         1630          {'NW'}           305       {'NA'}            221                235          {'NA'}       -15          -1       {'MSP'}    {'LAX'}      1536      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           3            1          925          755        1258         1144          {'NW'}           350       {'NA'}            153                169          {'NA'}        74          90       {'MSP'}    {'BOS'}      1124      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          22            6          900          900        1241         1222          {'AA'}            11       {'NA'}            401                382          {'NA'}        19           0       {'BOS'}    {'LAX'}      2611      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9          20            4         1338         1335        1853         1907          {'AA'}            62       {'NA'}            255                272          {'NA'}       -14           3       {'ORD'}    {'SJU'}      2072      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}      
    1990      9           3            1          710          711         837          847          {'AA'}           101       {'NA'}            147                156          {'NA'}       -10          -1       {'DTW'}    {'DFW'}       987      {'NA'}    {'NA'}         0             {'NA'}            0           {'NA'}          {'NA'}        {'NA'}        {'NA'}             {'NA'}

Close the DatabaseDatastore object and the database connection.

close(dbds)

Create `DatabaseDatastore` Object Using Custom Import Options

Open Live Script

Customize import options when importing a large data set from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for database columns that contain logical data. Import and preview the data by creating a DatabaseDatastore object and using the preview function.

This example uses the airlinesmall_subset.xls spreadsheet, which contains the column Cancelled. Also, the example 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 user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load flight information into the MATLAB® workspace.

flights = readtable('airlinesmall_subset.xlsx');

Create the flights database table using the flight information.

tablename = 'flights';
sqlwrite(conn,tablename,flights)

Create an SQLImportOptions object using the flights database table with the databaseImportOptions function.

opts = databaseImportOptions(conn,tablename);

Retrieve the default import options for the Cancelled variable.

varnames = 'Cancelled';
varOpts = getoptions(opts,varnames)

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'Cancelled'
               Type: 'double'
          FillValue: NaN

Set the import options for the data type of the specified variable to logical. Also, set the import options to replace missing data in the specified variable with the fill value true.

opts = setoptions(opts,varnames,'Type','logical', ...
    'FillValue',true);

Create the DatabaseDatastore object to import a large data set using the import options.

dbds = databaseDatastore(conn,tablename,opts);

Import the logical data in the selected variable and display a preview of the data. The imported data shows that the variable has the logical data type.

opts.SelectedVariableNames = varnames;
data = preview(dbds);
cancelled = data.Cancelled

cancelled = 8×1 logical array

   0
   0
   0
   0
   0
   0
   0
   0

Delete the flights database table using the execute function.

sqlquery = ['DROP TABLE ' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Create `DatabaseDatastore` Object Using Custom Import Options and Database Catalog and Schema

Open Live Script

Customize import options when importing a large data set from a database table. Control the import options by creating an SQLImportOptions object. Then, customize the import options for database columns that contain logical data. Create a DatabaseDatastore object using the specified database catalog and schema. Import the database data and preview it by using the preview function with the DatabaseDatastore object.

Create a database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Load flight information into the MATLAB® workspace.

flights = readtable('airlinesmall_subset.xlsx');

Create the flights database table using the flight information and the toy_store database catalog and dbo database schema.

tablename = 'flights';
sqlwrite(conn,tablename,flights, ...
    'Catalog','toy_store','Schema','dbo')

Create an SQLImportOptions object using the flights database table and the databaseImportOptions function. Specify the toy_store database catalog and dbo database schema.

opts = databaseImportOptions(conn,tablename, ...
    'Catalog','toy_store','Schema','dbo');

Retrieve the default import options for the Cancelled variable.

varnames = 'Cancelled';
varOpts = getoptions(opts,varnames)

varOpts = 
  SQLVariableImportOptions with properties:

  Variable Properties :
               Name: 'Cancelled'
               Type: 'double'
          FillValue: NaN

Set the import options for the data type of the specified variable to logical. Also, set the import options to replace missing data in the specified variable with the fill value true.

opts = setoptions(opts,varnames,'Type','logical', ...
    'FillValue',true);

Create the DatabaseDatastore object to import a large data set using import options, the toy_store database catalog, and the dbo database schema.

dbds = databaseDatastore(conn,tablename,opts, ...
    'Catalog','toy_store','Schema','dbo');

Import the logical data in the selected variable and display a preview of the data. The imported data shows that the variable has the logical data type.

opts.SelectedVariableNames = varnames;
data = preview(dbds);
cancelled = data.Cancelled

cancelled = 8×1 logical array

   0
   0
   0
   0
   0
   0
   0
   0

Delete the flights database table from the toy_store database catalog and the dbo database schema by using the execute function.

sqlquery = ['DROP TABLE toy_store.dbo.' tablename];
execute(conn,sqlquery)

Close the database connection.

close(conn)

Create a Parallelizable `databaseDatastore` Object

Create a parallelizable databaseDatastore object by using a parallel.pool.Constant (Parallel Computing Toolbox) object. You can use the setSecret and getSecret functions to store and retrieve your user credentials.

Create a query to use on your data set.

query = "SELECT col1, col2, col3 from table where col1 > ____ & col1 < ____";

Store your user credentials.

setSecret("PostgreSQL.username");
setSecret("PostgreSQL.password");

Create a parallel pool constant and specify your user credentials by using the getSecret function.

conn = parallel.pool.Constant(@()postgresql(getSecret("PostgreSQL.username"),getSecret("PostgreSQL.password"), ...
"Server","localhost","DatabaseName","toy_store"),@close);

Create a databaseDatastore object and read in your data in parallel.

dbds = databaseDatastore(conn,query);
data = readall(dbds,UseParallel=true);

Version History

Introduced in R2014b

expand all

R2024a: Parallelizable `databaseDatastore` Object

Read in large data sets by using a parallelizable databaseDatastore object.

databaseDatastore

Description

Creation

Syntax

Description

Input Arguments

`conn` — Database connection
`connection` object

`source` — Source
character vector | string scalar

`opts` — Database import options
`SQLImportOptions` object

`ReadSize` — Number of rows to return
numeric scalar

`Catalog` — Database catalog name
character vector | string scalar

`Schema` — Database schema name
character vector | string scalar

Limitations

Properties

`Connection` — Database connection
`connection` object

`Query` — SQL query
character vector

`VariableNames` — Column names of retrieved data table
cell array of character vectors

`SelectedVariableNames` — Subset of variables to import
character vector | cell array of character vectors | numeric array

`VariableNamingRule` — Variable naming rule
`"modify"` (default) | `"preserve"`

`ReadSize` — Number of rows to read
10000 (default) | numeric scalar

`RowFilter` — Filter to select rows to import
`matlab.io.RowFilter` object

Object Functions

Examples

Create `DatabaseDatastore` Object Using SQL Query Results

Create `DatabaseDatastore` Object Using Database Table

Create `DatabaseDatastore` Object with Specific Record Count

Create `DatabaseDatastore` Object Using Custom Import Options

Create `DatabaseDatastore` Object Using Custom Import Options and Database Catalog and Schema

Create a Parallelizable `databaseDatastore` Object

Version History

R2024a: Parallelizable `databaseDatastore` Object

See Also

Topics

External Websites

databaseDatastore

Description

Creation

Syntax

Description

Input Arguments

conn — Database connection connection object

source — Source character vector | string scalar

opts — Database import options SQLImportOptions object

ReadSize — Number of rows to return numeric scalar

Catalog — Database catalog name character vector | string scalar

Schema — Database schema name character vector | string scalar

Limitations

Properties

Connection — Database connection connection object

Query — SQL query character vector

VariableNames — Column names of retrieved data table cell array of character vectors

SelectedVariableNames — Subset of variables to import character vector | cell array of character vectors | numeric array

VariableNamingRule — Variable naming rule "modify" (default) | "preserve"

ReadSize — Number of rows to read 10000 (default) | numeric scalar

RowFilter — Filter to select rows to import matlab.io.RowFilter object

Object Functions

Examples

Create DatabaseDatastore Object Using SQL Query Results

Create DatabaseDatastore Object Using Database Table

Create DatabaseDatastore Object with Specific Record Count

Create DatabaseDatastore Object Using Custom Import Options

Create DatabaseDatastore Object Using Custom Import Options and Database Catalog and Schema

Create a Parallelizable databaseDatastore Object

Version History

R2024a: Parallelizable databaseDatastore Object

See Also

Topics

External Websites

`conn` — Database connection
`connection` object

`source` — Source
character vector | string scalar

`opts` — Database import options
`SQLImportOptions` object

`ReadSize` — Number of rows to return
numeric scalar

`Catalog` — Database catalog name
character vector | string scalar

`Schema` — Database schema name
character vector | string scalar

`Connection` — Database connection
`connection` object

`Query` — SQL query
character vector

`VariableNames` — Column names of retrieved data table
cell array of character vectors

`SelectedVariableNames` — Subset of variables to import
character vector | cell array of character vectors | numeric array

`VariableNamingRule` — Variable naming rule
`"modify"` (default) | `"preserve"`

`ReadSize` — Number of rows to read
10000 (default) | numeric scalar

`RowFilter` — Filter to select rows to import
`matlab.io.RowFilter` object

Create `DatabaseDatastore` Object Using SQL Query Results

Create `DatabaseDatastore` Object Using Database Table

Create `DatabaseDatastore` Object with Specific Record Count

Create `DatabaseDatastore` Object Using Custom Import Options

Create `DatabaseDatastore` Object Using Custom Import Options and Database Catalog and Schema

Create a Parallelizable `databaseDatastore` Object

R2024a: Parallelizable `databaseDatastore` Object