databaseDatastore

Create datastore for data in database

Description

MATLAB^®provides various datastores for importing and analyzing large datasets. A DatabaseDatastore object is a specialized datastore that represents data from a database table or the results of an SQL query in a relational database. For details about other datastore types, see Getting Started with Datastore.

A DatabaseDatastore object allows you to:

• Preview and read data in records or chunks.

• Reset the datastore to its initial state.

• Analyze large datasets directly in the database using tall arrays or MapReduce.

Reading data from a DatabaseDatastore object works the same way as using the fetch function. Using a DatabaseDatastore object also offers additional benefits that enable you to:

• Handle large databases efficiently without loading all data into memory.

• Use tall arrays with common MATLAB functions such mean and histogram for out-of-memory data. Create tall arrays with the tall function. For details, see Tall Arrays for Out-of-Memory Data.

• Write MapReduce algorithms to process large datasets by defining chunking and reduction using the mapreduce function. For examples, see Getting Started with MapReduce and Analyze Large Data in Database Using MapReduce.

• Create parallel workflows by using a parallel pool constant when creating the connection object (since R2024a). For more information, see parallel.pool.Constant (Parallel Computing Toolbox).

  • Use setSecret and getSecret to securely manage credentials when working with parallel pools. For more information on security best practices, see Keep Sensitive Information Out of Code.

Creation

Syntax

dbds = databaseDatastore(dataSourceInfo,source)

dbds = databaseDatastore(dataSourceInfo,source,opts)

dbds = databaseDatastore(___,Name=Value)

Description

dbds = databaseDatastore(dataSourceInfo,source) creates a DatabaseDatastore object using the data source information.

dataSourceInfo can be the data source name or any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• SQLite

• ODBC

• JDBC

source identifies the database table or SQL query used to create the datastore.

dbds = databaseDatastore(dataSourceInfo,source,opts) configures the data store using custom import options specified by the SQLImportOptions object, opts. Use this syntax when you need control over import behavior, such as importing only specific columns or setting variable names.

dbds = databaseDatastore(___,Name=Value) specifies additional options using one or more name-value arguments. For example, ReadSize=100 sets the datastore to retrieve 100 rows per read operation.

Input Arguments

dataSourceInfo — Data source information

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | SQLite connection object | ODBC connection object | JDBC connection object | string scalar | character vector

Data source information, specified as a connection object, string scalar, or character vector. Specifying data source information depends on the connection method:

• Specify dataSourceInfo as any of the following:

  • MySQL connection object created by using the mysql function.

  • PostgreSQL connection object created by using the postgresql function.

  • DuckDB connection object created by using the duckdb function.

  • SQLite connection object created by using the sqlite function.

  • ODBC connection object created by using the database function.

  • JDBC connection object created by using the database function.

  When you create the connection, retrieve your credentials using getSecret. This method assumes you have not stored your user credentials with the data source when using the Database Explorer app.

• Specify dataSourceInfo as a string scalar or character vector if you have already created your data source and saved your user credentials with the Database Explorer app.

source — Source

string scalar | character vector

Source, specified as a string scalar or character vector. 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"

opts — Database import options

SQLImportOptions object

Database import options, specified as an SQLImportOptions object that contains the following properties:

• ExcludeDuplicates

• VariableNamingRule

• VariableNames

• VariableTypes

• SelectedVariableNames

• FillValues

• RowFilter

• VariableOptions

After you create an SQLImportOptions object, you can modify an option by using setoptions. For example, opts = setoptions(opts,1,Name="MyNewName") changes the name of the first variable listed in VariableNames to "MyNewName".

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 a positive numeric scalar. Use this name-value argument to limit the number of rows for retrieval from the DatabaseDatastore object.

Example: 1000

Data Types: double

Catalog — Database catalog name

string scalar | character vector

Database catalog name, specified as a string scalar or character vector. A catalog serves as a container for schemas in a database which hold related metadata.

Use this name-value argument only if source is a database table.

Example: Catalog="toy_store"

Data Types: string | char

Schema — Database schema name

string scalar | character vector

Database schema name, specified as a string scalar or character vector. A schema defines database tables, relationships among tables, and other elements.

Use this name-value argument only if source is a database table.

Example: Schema="dbo"

Data Types: string | char

Limitations

• You can create parallelizable workflows by using the DatabaseDatastore object and specifying a parallel pool constant with the following databases:

  • MySQL

  • PostgreSQL

  • Microsoft^® SQL Server^®

  • MariaDB^®

  • Oracle^®

• If you set VariableNamingRule to "modify":

  • 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

Connection — Database connection

Read-only: connection object

This property is read-only.

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

Query — SQL query

Read-only: 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.

SelectedVariableNames must match or be a subset of VariableNames. By default, SelectedVariableNames contains all variable names specified in the VariableNames. When SelectedVariableNames lists every variable, all columns are imported.

Data Types: double | char | cell

VariableNamingRule — Variable naming rule

"modify" (default) | "preserve"

Variable naming rule, specified as one of the following:

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

Create databaseDatastore with Data Source Name

This example assumes you have already stored your username and password with your data source in the Database Explorer app.

Use the dataSourceInfo input argument to specify the name of your data source. Then, specify the query you want to execute.

dataSourceInfo = "PostgreSQL Native";
query = "SELECT * FROM airlinesmall";

Create the databaseDatastore.

dbds = databaseDatastore(dataSourceInfo,query)

dbds = 

  DatabaseDatastore with properties:

               Connection: [1×1 parallel.pool.Constant]
               Query: 'SELECT * FROM airlinesmall'
               VariableNames: {1×29 cell}
               SelectedVariableNames: {1×29 cell}
               VariableNamingRule: 'preserve'
               ReadSize: 10000
               RowFilter:<unconstrained>

Create DatabaseDatastore Object Using SQL Query Results

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";
dataSourceInfo = 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(dataSourceInfo,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

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 username and password.

datasource = "MySQL ODBC";
username = "username";
password = "password";
dataSourceInfo = 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(dataSourceInfo,tablename,flights)

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

dbds = databaseDatastore(dataSourceInfo,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
    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 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.

dataSourceInfo = 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(dataSourceInfo,query);
data = readall(dbds,UseParallel="on");

Create DatabaseDatastore Object Using Custom Import Options

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

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 username and password.

datasource = 'MS SQL Server Auth';
dataSourceInfo = 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(dataSourceInfo,tablename,flights)

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

opts = databaseImportOptions(dataSourceInfo,tablename);

Retrieve the default import options for the Cancelledvariable.

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(dataSourceInfo,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(dataSourceInfo,sqlquery)

Close the database connection.

close(dataSourceInfo)

Create DatabaseDatastore Object with Specific Record Count

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";
dataSourceInfo = 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(dataSourceInfo,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 and Database Catalog and Schema

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 previewfunction with the DatabaseDatastore object.

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 username and password.

datasource = 'MS SQL Server Auth';
dataSourceInfo = 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(dataSourceInfo,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(dataSourceInfo,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(dataSourceInfo,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(dataSourceInfo,sqlquery)

Close the database connection.

close(dataSourceInfo)

Create DatabaseDatastore Object Using Custom Import Options with DuckDB Database

This example shows how to create an DatabaseDatastore object using a DuckDB™ database file and customized import options.

Use the duckdb function to connect to the DuckDB™ database file, nyctaxi.db.

filePath = fullfile(matlabroot,"toolbox","database","dbdata","nyctaxi.db");
conn = duckdb(filePath,"ReadOnly",true)

conn = 
  connection with properties:

                    Database: "nyctaxi"

  Database Properties:

                    ReadOnly: true
                  AutoCommit: "on"

  Catalog and Schema Information:

              DefaultCatalog: "nyctaxi"
                    Catalogs: "nyctaxi"
                     Schemas: "main"

  Database and Driver Information:

         DatabaseProductName: "DuckDB"
      DatabaseProductVersion: "v1.3.2"

Create an SQLImportOptions object by using the databaseImportOptions function and specify the database table name "demo".

tableName = "demo";
opts = databaseImportOptions(conn,tableName);

Create a DatabaseDatastore object using the SQLImportOptions object, opts. Specify conn for dataSourceInfo and use tableName for source.

dataSourceInfo = conn;
source = tableName;
ds = databaseDatastore(dataSourceInfo,source,opts)

ds = 
  DatabaseDatastore with properties:

               Connection: [1×1 database.duckdb.connection]
                    Query: 'SELECT * from demo'
            VariableNames: {1×19 cell}
    SelectedVariableNames: {1×19 cell}
       VariableNamingRule: 'preserve'
                 ReadSize: 10000
                RowFilter: <unconstrained>

Display a preview of the data.

preview(ds)

ans=8×19 table
    vendorid    tpep_pickup_datetime    tpep_dropoff_datetime    passenger_count    trip_distance    pickup_longitude    pickup_latitude    ratecodeid    store_and_fwd_flag    dropoff_longitude    dropoff_latitude    payment_type    fare_amount    extra    mta_tax    tip_amount    tolls_amount    improvement_surcharge    total_amount
    ________    ____________________    _____________________    _______________    _____________    ________________    _______________    __________    __________________    _________________    ________________    ____________    ___________    _____    _______    __________    ____________    _____________________    ____________

       2        09-Jun-2015 14:58:55    09-Jun-2015 15:26:41            1                2.63            -73.983              40.73             1                "N"                 -73.977              40.759              2               18          0        0.5            0              0                 0.3                 18.8    
       2        09-Jun-2015 14:58:55    09-Jun-2015 15:02:13            1                0.32            -73.997             40.732             1                "N"                 -73.994              40.731              2                4          0        0.5            0              0                 0.3                  4.8    
       1        09-Jun-2015 14:58:56    09-Jun-2015 16:08:52            2                20.6            -73.983             40.767             2                "N"                 -73.798              40.645              1               52          0        0.5           10           5.54                 0.3                68.34    
       1        09-Jun-2015 14:58:57    09-Jun-2015 15:12:00            1                 1.2             -73.97             40.762             1                "N"                 -73.969               40.75              1                9          0        0.5         1.96              0                 0.3                11.76    
       2        09-Jun-2015 14:58:58    09-Jun-2015 15:00:49            5                0.49            -73.978             40.786             1                "N"                 -73.972              40.785              2              3.5          0        0.5            0              0                 0.3                  4.3    
       2        09-Jun-2015 14:58:59    09-Jun-2015 15:42:02            1               16.64             -73.97             40.757             2                "N"                  -73.79              40.647              1               52          0        0.5        11.67           5.54                 0.3                70.01    
       1        09-Jun-2015 14:58:59    09-Jun-2015 15:03:07            1                 0.8            -73.976             40.745             1                "N"                 -73.983              40.735              1                5          0        0.5            1              0                 0.3                  6.8    
       2        09-Jun-2015 14:59:00    09-Jun-2015 15:21:31            1                3.23            -73.982             40.767             1                "N"                 -73.994              40.736              2             16.5          0        0.5            0              0                 0.3                 17.3    

Read the first 15 records of data.

ds.ReadSize = 15;
read(ds)

ans=15×19 table
    vendorid    tpep_pickup_datetime    tpep_dropoff_datetime    passenger_count    trip_distance    pickup_longitude    pickup_latitude    ratecodeid    store_and_fwd_flag    dropoff_longitude    dropoff_latitude    payment_type    fare_amount    extra    mta_tax    tip_amount    tolls_amount    improvement_surcharge    total_amount
    ________    ____________________    _____________________    _______________    _____________    ________________    _______________    __________    __________________    _________________    ________________    ____________    ___________    _____    _______    __________    ____________    _____________________    ____________

       2        09-Jun-2015 14:58:55    09-Jun-2015 15:26:41            1                2.63            -73.983              40.73             1                "N"                 -73.977              40.759              2               18          0        0.5            0              0                 0.3                 18.8    
       2        09-Jun-2015 14:58:55    09-Jun-2015 15:02:13            1                0.32            -73.997             40.732             1                "N"                 -73.994              40.731              2                4          0        0.5            0              0                 0.3                  4.8    
       1        09-Jun-2015 14:58:56    09-Jun-2015 16:08:52            2                20.6            -73.983             40.767             2                "N"                 -73.798              40.645              1               52          0        0.5           10           5.54                 0.3                68.34    
       1        09-Jun-2015 14:58:57    09-Jun-2015 15:12:00            1                 1.2             -73.97             40.762             1                "N"                 -73.969               40.75              1                9          0        0.5         1.96              0                 0.3                11.76    
       2        09-Jun-2015 14:58:58    09-Jun-2015 15:00:49            5                0.49            -73.978             40.786             1                "N"                 -73.972              40.785              2              3.5          0        0.5            0              0                 0.3                  4.3    
       2        09-Jun-2015 14:58:59    09-Jun-2015 15:42:02            1               16.64             -73.97             40.757             2                "N"                  -73.79              40.647              1               52          0        0.5        11.67           5.54                 0.3                70.01    
       1        09-Jun-2015 14:58:59    09-Jun-2015 15:03:07            1                 0.8            -73.976             40.745             1                "N"                 -73.983              40.735              1                5          0        0.5            1              0                 0.3                  6.8    
       2        09-Jun-2015 14:59:00    09-Jun-2015 15:21:31            1                3.23            -73.982             40.767             1                "N"                 -73.994              40.736              2             16.5          0        0.5            0              0                 0.3                 17.3    
       1        09-Jun-2015 14:59:01    09-Jun-2015 15:08:17            1                 1.1            -73.949             40.788             1                "N"                 -73.954              40.775              1              7.5          0        0.5            0              0                 0.3                  8.3    
       2        09-Jun-2015 14:59:02    09-Jun-2015 15:19:34            6                1.79            -73.993             40.747             1                "N"                 -73.972               40.76              1             13.5          0        0.5         2.86              0                 0.3                17.16    
       1        09-Jun-2015 14:59:02    09-Jun-2015 15:28:12            2                 4.8            -73.984             40.756             1                "N"                 -73.916              40.764              1               22          0        0.5            5              0                 0.3                 27.8    
       2        09-Jun-2015 14:59:03    09-Jun-2015 15:09:21            1                0.96            -73.962             40.776             1                "N"                 -73.969              40.766              1                8          0        0.5          2.2              0                 0.3                   11    
       2        09-Jun-2015 14:59:03    09-Jun-2015 15:11:48            5                1.12            -73.978             40.753             1                "N"                  -73.99              40.747              2                9          0        0.5            0              0                 0.3                  9.8    
       1        09-Jun-2015 14:59:04    09-Jun-2015 15:04:12            1                 0.7            -73.978             40.767             1                "N"                 -73.981              40.774              1              5.5          0        0.5         1.55              0                 0.3                 7.85    
       2        09-Jun-2015 14:59:01    09-Jun-2015 15:21:56            1                2.06            -73.969             40.786             1                "N"                 -73.961              40.765              1               15          0        0.5         3.95              0                 0.3                19.75    

Close the databaseDatastore object.

close(ds);

Version History

Introduced in R2014b

R2026a: databaseDatastore supports DuckDB connection objects

You can now create a data store from a DuckDB database by specifying a DuckDB connection object when using the databaseDatastore function.

R2025a: Create databaseDatastore object by specifying data source information

If you save your username and password with your data source when making a connection by using the Database Explorer app, you can then create a databaseDatastore object without specifying your credentials by using the dataSourceInfo input argument.

R2024a: Parallelizable databaseDatastore Object

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