Insert Table into Relational Database for Data Reporting

R2026b

This example shows how to import an existing table from a relational database, process the data in MATLAB®, and export the results to a new database.

Connect to Database

Connect to the DuckDB™ database file nyctaxi.db in the matlabroot/toolbox/database/dbdata folder by using the duckdb function. Because nyctaxi.db is read-only, open the file by specifying ReadOnly=true. The database contains the table demo used in this workflow.

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

Import Database Table

Import the demo table into MATLAB by using the fetch function.

sqlQuery = 'SELECT * FROM main.demo';
data = fetch(conn,sqlQuery);
head(data)

    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    

close(conn)

Compute Statistics Table

Calculate a set of statistics from the taxi data by estimating the mean of the fare_amount and tip_amount variables in data and store these statistics in a new table.

colNames =["average_fare" "average_tip"];
meanFare = mean(data{:,"fare_amount"});
meanTip = mean(data{:,"tip_amount"});
fareStats = table(meanFare,meanTip,VariableNames=colNames)

fareStats = 1×2 table
    average_fare    average_tip
    ____________    ___________

       12.755         1.6948   

Save Statistics Table in New Database

Create a connection to a new database file, nycTaxiStatistics.db. Insert the taxi statistics table into this file by using the sqlwrite function with the Catalog and Schema name value arguments, and specify a name for the new table.

conn = duckdb("nycTaxiStatistics.db");
tablename = "fareStatistics";
execute(conn, "DROP TABLE IF EXISTS " + tablename);
sqlwrite(conn,tablename,fareStats,Catalog="nycTaxiStatistics",Schema="main");

Verify that the database contains the new table fareStatistics using the sqlfind function.

pattern = "";
sqlfind(conn,pattern)

ans = 1×5 table
          Catalog          Schema         Table                        Columns                      Type    
    ___________________    ______    ________________    ___________________________________    ____________

    "nycTaxiStatistics"    "main"    "fareStatistics"    {["average_fare"    "average_tip"]}    "BASE TABLE"

Close the database connection.

close(conn)