Learning Tabulify - Step 6 - How to transfer Data Resources

Data Transfer

In Tabulify, when you want to manipulate data, it's called a transfer.

You want to move data, you transfer
You want to copy data, you transfer
You want to insert data, you transfer
You want to update data, you transfer
You want to upsert data, you transfer
You want to delete data, you transfer

For data manipulation, the transfer command is the only command you need.

The transfer command is the most powerfull command and we will introduce it first with a copy.

Sqlite HowTo Connection

Tabulify comes pre-installed with connections used in the HowTo. We call this connections, the Howto's connections.

In this learning guide, we will use as target connection, the local sqlite how-to connection

tabli connection info sqlite

Information about the connection (sqlite)
Attribute                Value                                                              Description
----------------------   ----------------------------------------------------------------   --------------------------------------------------------------------
DatabaseMajorVersion     3                                                                  The major version number of the database
DatabaseMinorVersion     30                                                                 The minor version number of the database
DatabaseProductName      SQLite                                                             The name of the database
DatabaseProductVersion   3.30.1                                                             The version of the database
Driver                   org.sqlite.JDBC                                                    The driver class
Driver                   org.sqlite.JDBC                                                    The driver class
DriverName               SQLite JDBC                                                        The driver name
DriverVersion            3.30.1                                                             The driver version
JdbcMajorVersion         2                                                                  The major version number of JDBC
JdbcMinorVersion         1                                                                  The minor version number of JDBC
MaxConcurrentThread      1                                                                  The maximum number of threads that can be created against the system
MaxNameInPath            1                                                                  The maximum number of names in a path
Name                     sqlite                                                             The name of the connection
SupportBatchUpdates      true                                                               If the system supports batch SQL updates
SupportNamedParameters   true                                                               If the system supports named parameters in the SQL statement
TabBooleanDataType       Binary                                                             Boolean data type used to store boolean values
TabDateDataType          SqlLiteral                                                         Date data type used to store date values
TabTimeDataType          SqlLiteral                                                         Time format data type to store time values
TabTimestampDataType     SqlLiteral                                                         Timestamp data type used to store timestamp values
Url                      jdbc:sqlite:///C:/Users/gerardnico/AppData/Local/tabli/sqlite.db   The url of the connection

Copy

Just drop all tables

tabli data drop --not-strict *@sqlite

For the purpose of this learning guide, we will copy the sales data from tpcds to the sqlite connection with the copy command that is an alias of the transfer command.

tabli data copy --with-dependencies *sales@tpcds @sqlite

where:

tabli data copy points to the copy command
*sales@tpcds is a data selector that select all table that ends with sales in the tpcds connection (We have covered this subject in the how to select data resource page)
--with-dependencies select the dependencies of the selected data resources
@sqlite defines the sqlite connection as the target connection

At the end of the process, you get a list of all tables where data were loaded and the number of row that were inserted.

Source                         Target                          Latency (ms)   Row Count   Error   Message
----------------------------   -----------------------------   ------------   ---------   -----   -------
call_center@tpcds              call_center@sqlite              28             2
catalog_page@tpcds             catalog_page@sqlite             289            11718
catalog_sales@tpcds            catalog_sales@sqlite            9080           89807
customer@tpcds                 customer@sqlite                 79             1000
customer_address@tpcds         customer_address@sqlite         66             1000
customer_demographics@tpcds    customer_demographics@sqlite    24253          1920800
date_dim@tpcds                 date_dim@sqlite                 5800           73049
household_demographics@tpcds   household_demographics@sqlite   85             7200
income_band@tpcds              income_band@sqlite              39             20
item@tpcds                     item@sqlite                     453            2000
promotion@tpcds                promotion@sqlite                29             3
ship_mode@tpcds                ship_mode@sqlite                24             20
store@tpcds                    store@sqlite                    26             2
store_sales@tpcds              store_sales@sqlite              6726           120527
time_dim@tpcds                 time_dim@sqlite                 1302           86400
warehouse@tpcds                warehouse@sqlite                63             1
web_page@tpcds                 web_page@sqlite                 37             2
web_sales@tpcds                web_sales@sqlite                1308           11876
web_site@tpcds                 web_site@sqlite                 32             2

Copy Verification

To check the copy, we will list the tables in the sqlite connection with the list command and the count attribute to see the number of rows by table.

tabli data list -a name --attribute count  *@sqlite

path                       count
----------------------   -------
call_center                    2
catalog_page               11718
catalog_sales              89807
customer                    1000
customer_address            1000
customer_demographics    1920800
date_dim                   73049
household_demographics      7200
income_band                   20
item                        2000
promotion                      3
ship_mode                     20
store                          2
store_sales               120527
time_dim                   86400
warehouse                      1
web_page                       2
web_sales                  11876
web_site                       2

Now, that we got data, we can discover how to execute a Sql Query

How to execute a Sql Query

Learning Tabulify - Step 6 - How to transfer Data Resources

Data Transfer

Sqlite HowTo Connection

Copy

Copy Verification

Next