Differences

This shows you the differences between two versions of the page.

sqldata [December 19, 2017 9:03 pm]
sqlines
sqldata [April 28, 2021 6:52 am] (current)
sqlines
Line 1: Line 1:
====== SQLines Data - Database Migration and ETL ====== ====== SQLines Data - Database Migration and ETL ======
-SQLines Data is an open source (Apache License 2.0), scalable, parallel high performance data transfer and schema conversion tool that you can use for database migrations and ETL processes.+SQLines Data is a high performance data transfer, schema conversion and migration validation tool that supports major enterprise databases:
-  * Oracle and Oracle Exadata¬†+  * Oracle¬†
-  * Microsoft SQL Server and Microsoft Azure SQL+  * Microsoft SQL Server
  * MySQL   * MySQL
  * MariaDB   * MariaDB
  * PostgreSQL   * PostgreSQL
-  * Amazon Redshift, Amazon Aurora, Amazon RDS and Amazon Athena 
  * IBM DB2 LUW, iSeries (AS/400) and zSeries (S/390)   * IBM DB2 LUW, iSeries (AS/400) and zSeries (S/390)
  * Sybase Adaptive Server Enterprise, Sybase SQL Anywhere, Sybase IQ and Sybase Advantage   * Sybase Adaptive Server Enterprise, Sybase SQL Anywhere, Sybase IQ and Sybase Advantage
  * Informix   * Informix
-  * Teradata 
-  * Netezza 
-  * Greenplum 
-  * Vertica 
-  * SAP HANA 
SQLines Data is written in C/C++ and uses native low-level in-memory bulk loader APIs to transfer data. It is available for Linux, Windows, both 64-bit and 32-bit platforms. SQLines Data is written in C/C++ and uses native low-level in-memory bulk loader APIs to transfer data. It is available for Linux, Windows, both 64-bit and 32-bit platforms.
-SQLines Data guides for specific database migrations: 
-| **MariaDB** | **Oracle** | **SQL Server** | **MySQL** |  
-| [[/sqldata/oracle-to-mariadb|Oracle to MariaDB]] | [[/sqldata/db2-to-oracle|IBM DB2 to Oracle]] | [[/sqldata/oracle-to-sql-server|Oracle to SQL Server]] | [[/sqldata/oracle-to-mysql|Oracle to MySQL]] |  
-| [[/sqldata/sql-server-to-mariadb|SQL Server to MariaDB]] | [[/sqldata/sybase-to-oracle|Sybase ASE to Oracle]] | || 
-| [[/sqldata/sybase-to-mariadb|Sybase ASE to MariaDB]] | | || 
===== Database Migration ===== ===== Database Migration =====
Line 38: Line 27:
===== ETL ===== ===== ETL =====
-You can use SQLines Data in your ETL processes as a data extact-load tool. +You can use SQLines Data in your ETL processes as a data extract-load tool.
**ETL Features:** **ETL Features:**
  * Parallel high performance data transfer to target databases   * Parallel high performance data transfer to target databases
-  * Parallel high performance export to flat files, S3 or HDFS supporting compression+  * Parallel high performance export to flat files
  * Choosing columns to transfer   * Choosing columns to transfer
  * Defining SELECT expressions including computed columns   * Defining SELECT expressions including computed columns
Line 52: Line 41:
===== How To ===== ===== How To =====
 +  * [[/sqldata#how-to-map-database-schemas|How to Map Database Schemas]]
 +  * [[/sqldata#how-to-map-table-names|How to Map Table Names]]
 +  * [[/sqldata#how-to-map-data-types|How to Map Data Types]]
  * [[/sqldata#how-to-transfer-tables-without-constraints-and-indexes|How to Transfer Tables without Constraints and Indexes]]   * [[/sqldata#how-to-transfer-tables-without-constraints-and-indexes|How to Transfer Tables without Constraints and Indexes]]
 +  * [[/sqldata#how-to-specify-filters-where-conditions-on-tables|How to Specify Filters (WHERE Conditions) on Tables]]
 +  * [[/sqldata#how-to-transform-column-data|How to Transform Column Data]]
 +  * [[/sqldata#how-to-restart-transfer-of-failed-tables|How to Restart Transfer of Failed Tables]]
 +  * [[/sqldata#how-to-specify-queries-to-transfer-data|How to Specify Queries to Transfer Data]]
 +  * [[/sqldata#how-to-generate-dump-files|How to Generate Dump Files]]
 +  * [[/sqldata#how-to-skip-data-transfer|How to Skip Data Transfer]]
 +
 +==== How to Map Database Schemas ====
 +
 +Often you need to change the database schemas during the migration, see [[http://www.sqlines.com/sqldata_cmd#smap-option|-smap]] option how to do this.
 +
 +==== How to Map Table Names ====
 +
 +Often you also need to change the table names during the migration, you can use it modifying //sqlines_tmap.txt// supplied with the tool.
 +
 +Every line of this file contains a single table name mapping:
 +
 +| //source_name, target_name// |
 +| //source_name2, target_name2// |
 +| ... |
 +
 +Use fully qualified table name to specify source tables, and for Oracle, IBM DB2 it should be in the upper case.
 +
 +==== How to Map Data Types ====
 +
 +Sometimes you need to change the default data type mapping provided by SQLines Data tool. You can use //sqlines_dtmap.txt// file to define your own mapping.
 +
 +For example, by default SQLines Data converts Oracle VARCHAR2(4000) to VARCHAR(4000) in MariaDB. You can change mapping to TEXT by defining:
 +
 +| VARCHAR2(4000), TEXT |
 +
 +in the //sqlines_dtmap.txt//. For more details, see [[/sqldata_cmd#dtmapf-option|-dtmapf]] configuration option.
 +
 +This configuration file provides the global data type mapping i.e. this mapping will be used for all tables.
==== How to Transfer Tables without Constraints and Indexes ==== ==== How to Transfer Tables without Constraints and Indexes ====
Line 59: Line 85:
Edit [[/sqldata_cfg|sqldata.cfg]] configuration file, and set //-constraints=no// and //-indexes=no//. Edit [[/sqldata_cfg|sqldata.cfg]] configuration file, and set //-constraints=no// and //-indexes=no//.
 +
 +==== How to Specify Filters (WHERE Conditions) on Tables ====
 +
 +Edit //sqlines_twhere.txt// file and specify individual filter for any source table. If a filter is not specified for the table all its data are transferred.
 +
 +==== How to Transform Column Data ====
 +
 +Edit //sqlines_tsel.txt// file and specify column expressions (using SQL dialect of the source database), add/remove columns, define computed columns and so on.
 +
 +==== How to Restart Transfer of Failed Tables ====
 +
 +During the database migration some tables may fail to transfer. SQLines Data generates //sqldata_failed_tables.txt// file with the list of failed tables. Using //-tf=sqldata_failed_tables.txt// command line option you can re-run the transfer for the failed tables only.
 +
 +==== How to Specify Queries to Transfer Data ====
 +
 +In addition to specify tables, you can also specify any ad-hoc queries to define which data to transfer. Use [[/sqldata_cmd#qf-option|-qf]] option to define your queries.
 +
 +==== How to Generate Dump Files ====
 +
 +When importing data into MySQL, MariaDB or PostgreSQL you can add command line option //-trace_data=yes// to generate the dump files i.e. data that were sent to MySQL, MariaDB LOAD DATA INFILE and PostgreSQL COPY commands. It can help debug data transfer issues if any.
 +
 +==== How to Skip Data Transfer ====
 +
 +If you need to transfer DDL (tables, constraints, indexes etc.) without transferring data, set //-data=no// in the command line or //sqldata.cfg// configuration file.
 +
 +If you have any questions, please [[/contact-us|contact us]].