====== 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 [[docs:op:move|move]] data, you [[docs:op:transfer|transfer]]
* You want to [[docs:op:copy|copy]] data, you [[docs:op:transfer|transfer]]
* You want to [[docs:op:insert|insert]] data, you [[docs:op:transfer|transfer]]
* You want to [[docs:op:update|update]] data, you [[docs:op:transfer|transfer]]
* You want to [[docs:op:upsert|upsert]] data, you [[docs:op:transfer|transfer]]
* You want to [[docs:op:delete|delete]] data, you [[docs:op:transfer|transfer]]
For data manipulation, the [[docs:tabli:data:transfer|transfer command]] is the only command you need.
The [[docs:tabli:data:transfer|transfer command]] is the most powerfull command and we will introduce it first with a [[docs:op:copy|copy]].
===== Sqlite HowTo Connection =====
''Tabulify'' comes pre-installed with [[docs:connection:connection|connections]] used in the [[howto:howto|HowTo]]. We call this connections, the [[docs:connection:howtos|Howto's connections]].
In this learning guide, we will use as target connection, the [[docs:connection:howtos|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 [[docs:op:copy|copy]] the ''sales'' data from [[docs:system:tpcds|tpcds]] to the [[docs:connection:howtos|sqlite connection]] with the [[docs:tabli:data:copy|copy command]] that is an alias of the [[docs:tabli:data:transfer|transfer command]].
tabli data copy --with-dependencies *sales@tpcds @sqlite
where:
* ''tabli data copy'' points to the [[docs:tabli:data:copy|copy command]]
* ''*sales@tpcds'' is a [[docs:resource:data_selector|data selector]] that select all table that ends with sales in the [[docs:system:tpcds|tpcds connection]] (We have covered this subject in the [[4_select_data_resources|how to select data resource page]])
* ''%%--with-dependencies%%'' select the [[docs:resource:dependency|dependencies]] of the selected [[docs:resource:resource|data resources]]
* ''@sqlite'' defines the [[docs:connection:howtos|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 [[docs:resource:table|tables]] in the [[docs:connection:howtos|sqlite connection]] with the [[docs:tabli:data:list|list command]] and the [[docs:resource:count|count attribute]] to see the number of rows by [[docs:resource:table|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
===== Next =====
Now, that we got data, we can discover how to execute a [[docs:resource:select|Sql Query]]
[[7_query|How to execute a Sql Query]]