Learning Tabulify - Step 6 - How to transfer Data Resources
Data Transfer
In Tabulify, when you want to manipulate data, it's called a 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
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
Next
Now, that we got data, we can discover how to execute a Sql Query